动态SQL能执行,非动态SQL执行不了??

我叫P民 2010-09-28 01:25:08
create or replace procedure test(years in varchar2,corps in varchar2,ctrlprjcodes in varchar2,jukong in varchar2)
is
begin
insert into bdgt_formation(tagfrom,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode,
projectname,pk_formation,def7)
select biaoji,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode,projectname
,generatepk(pk_corp)pk_formation,def7 from (
select distinct 10 as biaoji,f.pk_downsend_b,to_char(to_number(f.m_year)+1) m_year,f.m_dept,f.pk_corp,f.dr,f.projectcode,f.projectname
,f.pk_formation,f.pk_formation def7
from bdgt_formation_b b,bdgt_formation f,bd_deptdoc bdd
where b.dr='0' and f.dr='0' and b.childprjname is not null and b.pk_formation=f.pk_formation
and f.m_dept=bdd.pk_deptdoc(+) and b.def4 is not null and nvl(b.closeflag,'N')!='Y' and f.pk_corp=corps and f.m_year
=years and f.projectcode=ctrlprjcodes and b.def4=jukong)base;
end;

不用动态SQL
create or replace procedure test(years in varchar2,corps in varchar2,ctrlprjcodes in varchar2,jukong in varchar2)
is
sqls varchar2(2000):='';
begin
sqls:='insert into bdgt_formation(tagfrom,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode, projectname,pk_formation,def7)
select biaoji,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode,projectname
,generatepk(pk_corp)pk_formation,def7 from (select distinct 10 as biaoji,f.pk_downsend_b,
to_char(to_number(f.m_year)+1) m_year,f.m_dept,f.pk_corp,f.dr,f.projectcode,f.projectname,
f.pk_formation,f.pk_formation def7 from bdgt_formation_b b,bdgt_formation f,bd_deptdoc bdd
where b.dr=''0'' and f.dr=''0'' and b.childprjname is not null
and b.pk_formation=f.pk_formation and f.m_dept=bdd.pk_deptdoc(+)
and b.def4 is not null and nvl(b.closeflag,''N'')!=''Y'' and f.pk_corp='||corps||'
and f.m_year='||years||' and f.projectcode='||ctrlprjcodes||' and b.def4='||jukong||')base';
execute immediate sqls;
end;
用动态SQL,两个语句要达到的目的都一样,可我在测试时发现不用动态SQL的过程并没有执行插入操作,动态SQL那个则是正确的,我把非动态SQL的存储过程给拷出来自己赋值也能插入,实在郁闷了,求解
...全文
82 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
我叫P民 2010-09-28
  • 打赏
  • 举报
回复
郁闷做表的人设计字段成char型的,N多空格,截取了后正常了,谢谢各位了
minitoy 2010-09-28
  • 打赏
  • 举报
回复
检查下sql语句吧.
Phoenix_99 2010-09-28
  • 打赏
  • 举报
回复
怎么查看的,虽然是同一用户,但是登录不同,也不可以显示,
例如:

打开一个cmd
SQL> create table t(id number);

表已创建。

SQL> insert into t(1);
insert into t(1)
*
第 1 行出现错误:
ORA-00928: 缺失 SELECT 关键字


SQL> insert into t values(1);

已创建 1 行。

SQL>

打开另一个cmd,用同样的用户名、密码登录
C:\Documents and Settings\Administrator>sqlplus bms/bms

SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 9月 28 14:03:37 2010

Copyright (c) 1982, 2004, Oracle. All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from t;

未选定行

SQL>

查询结果是不一样的
我叫P民 2010-09-28
  • 打赏
  • 举报
回复
你说是加commit吧,我是方便自己测试才没加,可以回滚,不用删数据,我实在找不出来第一个SQL为什么就执行不了...郁闷啊
心中的彩虹 2010-09-28
  • 打赏
  • 举报
回复
[Quote=引用楼主 zhaoguo1987 的回复:]
SQL code
create or replace procedure test(years in varchar2,corps in varchar2,ctrlprjcodes in varchar2,jukong in varchar2)
is
begin
insert into bdgt_formation(tagfrom,pk_downsend_b,m_year,m_dept,p……
[/Quote]

语法没问题的话
刚看了下 有些地方的注意下 在试试看

create or replace procedure test(years in varchar2,corps in varchar2,ctrlprjcodes in varchar2,jukong in varchar2)
is
begin
insert into bdgt_formation(tagfrom,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode,projectname,pk_formation,def7)
select biaoji,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode,projectname,generatepk(pk_corp) pk_formation,def7
from ( select distinct 10 as biaoji,f.pk_downsend_b,to_char(to_number(f.m_year)+1) m_year,f.m_dept,f.pk_corp,f.dr,f.projectcode,f.projectname,f.pk_formation,f.pk_formation def7
from bdgt_formation_b b,bdgt_formation f,bd_deptdoc bdd
where b.dr='0' and f.dr='0' and b.childprjname is not null and b.pk_formation=f.pk_formation
and f.m_dept=bdd.pk_deptdoc(+) and b.def4 is not null and b.closeflag='Y' and f.pk_corp=corps and f.m_year=years and f.projectcode=ctrlprjcodes and b.def4=jukong ) base;
commit;
end;

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧