procedure procunit
as
sqlstr varchar2(2000):='create or replace view v_tmp as select "d.drugdesc"';
begin
for cur1 in (select distinct u.class from unit u)
loop
sqlstr:=sqlstr||',count(decode(u.class,'||cur1.class||',1))"'||cur1.class||'"';
end loop;
sqlstr:=sqlstr||' from casecrime_drug c left join cddrug d on c.drugid=d.drugid
left join casesecurity y on y.caseno=c.caseno
left join unit u on u.unitid=y.inputunitid
left join suspect s on s.caseno=y.caseno
group by d.drugdesc';
DBMS_OUTPUT.PUT_LINE(sqlstr);
execute immediate sqlstr;
end;
这个存储过程怎么执行不了的,谁懂帮我修改下,我在sql navigator 5中执行不了这是怎么回事!急死人了。
as
sqlstr varchar2(2000):='create or replace view v_tmp as select "d.drugdesc"';
begin
for cur1 in (select distinct u.class from unit u)
loop
sqlstr:=sqlstr||',count(decode(u.class,'||cur1.class||',1))"'||cur1.class||'"';
end loop;
sqlstr:=sqlstr||' from casecrime_drug c left join cddrug d on c.drugid=d.drugid
left join casesecurity y on y.caseno=c.caseno
left join unit u on u.unitid=y.inputunitid
left join suspect s on s.caseno=y.caseno
group by d.drugdesc';
DBMS_OUTPUT.PUT_LINE(sqlstr);
execute immediate sqlstr;
end;
这个存储过程怎么执行不了的,谁懂帮我修改下,我在sql navigator 5中执行不了这是怎么回事!急死人了。
解决方案 »
- 求教,关于oracle登录问题,使用自己创建的用户被无限延迟登录,好像在等待进程
- 错误提示:FROM keyword not found where expected不知问题何在
- 数据导出
- 关于ORACLE转化为XML文档的问题
- 网上的sqlplus程序有问题
- 在win2000环境下安装developer6.0出错(也许是developer2000)
- 高手帮我看看这个SQL语句吧
- 为什么不能通过数据库连接串访问另一个数据库中的表?
- oracel安装问题,急
- 将一个用户的数据移到另一个表空间上,出现一些奇怪的问题!
- java调用Oracle 存储过程日期参数问题
- 请教使用jdbc在西文字符集oracle中存取中文的乱码问题
2、Oracle中两个单引号为一个“'”,因此你过程中的动态SQL拼接有问题,你可以看你DBMS_OUTPUT.PUT_LINE(sqlstr);输出的SQL语句是不是你想要的,你就知道了
sqlstr VARCHAR2(2000) := 'create or replace view v_tmp as select d.drugdesc';
BEGIN
FOR cur1 IN (SELECT DISTINCT u.class FROM unit u) LOOP
sqlstr := sqlstr || ',count(decode(u.class,' || cur1.class || ',1)) ' || cur1.class;
END LOOP;
sqlstr := sqlstr || ' from casecrime_drug c left join cddrug d on c.drugid=d.drugid
left join casesecurity y on y.caseno=c.caseno
left join unit u on u.unitid=y.inputunitid
left join suspect s on s.caseno=y.caseno
group by d.drugdesc';
DBMS_OUTPUT.PUT_LINE(sqlstr);
EXECUTE IMMEDIATE sqlstr;
END;
CREATE OR REPLACE PROCEDURE procunit AS
sqlstr VARCHAR2(2000) := 'create or replace view v_tmp as select d.drugdesc';
BEGIN
FOR cur1 IN (SELECT DISTINCT u.class FROM unit u) LOOP
sqlstr := sqlstr || ',count(decode(u.class,''' || cur1.class || ''',1)) ' || cur1.class;
END LOOP;
sqlstr := sqlstr || ' from casecrime_drug c left join cddrug d on c.drugid=d.drugid
left join casesecurity y on y.caseno=c.caseno
left join unit u on u.unitid=y.inputunitid
left join suspect s on s.caseno=y.caseno
group by d.drugdesc';
DBMS_OUTPUT.PUT_LINE(sqlstr);
EXECUTE IMMEDIATE sqlstr;
END;
我这样exec procunit; 执行调试查看结果集不得,这是为什么呢?是怎么执行的呀
真是急死人了