Oracle 存储过程 动态SQL 语句 返回结果集

 ----------------------------------准备测试表和数据CREATE TABLE TUZHEN_TEST001 (  ID NUMBER , XINGMING VARCHAR2(100) , KECHENG VARCHAR2(100) , FENSHU NUMBER );INSERT INTO TUZHEN_TEST001 VALUES(1,'tuzhen001','YU_WEN','87');INSERT INTO TUZHEN_TEST001 VALUES(2,'tuzhen001','SHU_XUE','88');INSERT INTO TUZHEN_TEST001 VALUES(3,'tuzhen001','YING_YU','89');INSERT INTO TUZHEN_TEST001 VALUES(4,'tuzhen002','YU_WEN','90');INSERT INTO TUZHEN_TEST001 VALUES(5,'tuzhen002','SHU_XUE','91');INSERT INTO TUZHEN_TEST001 VALUES(6,'tuzhen002','YING_YU','92');INSERT INTO TUZHEN_TEST001 VALUES(7,'tuzhen003','YU_WEN','30');INSERT INTO TUZHEN_TEST001 VALUES(8,'tuzhen003','SHU_XUE','41');INSERT INTO TUZHEN_TEST001 VALUES(9,'tuzhen004','YING_YU','60');INSERT INTO TUZHEN_TEST001 VALUES(10,'tuzhen003','LI_SHI','30');INSERT INTO TUZHEN_TEST001 VALUES(11,'tuzhen005','DI_LI','41');INSERT INTO TUZHEN_TEST001 VALUES(12,'tuzhen004','WU_LI','60');SELECT * FROM TUZHEN_TEST001;----------------------------------创建存储过程create or replace procedure PRO_tuzhen_test001 (param1 in varchar2,param2 in out sys_refcursor)ISSQLCommand varchar2(4000);CURSOR cur_kecheng IS   select distinct kecheng  from tuzhen_test001;BEGIN  SQLCommand := 'SELECT xingming,';  for i in cur_kecheng loop    SQLCommand := SQLCommand ||'SUM(decode(a.kecheng,'''||i.kecheng||''',FENSHU,0)) "'||i.kecheng||'",';  end loop;  SQLCommand := SQLCommand || 'count(a.xingming) totalPeple    FROM tuzhen_test001 a     GROUP BY a.xingming';  open param2 for SQLCommand;  --SQLString := SQLCommand;  --dbms_output.put_line(SQLString);  --execute immediate SQLString;END PRO_tuzhen_test001;----------------------------------调用存储过程variable cur refcursor;exec PRO_tuzhen_test001('1',:cur);print :cur;