--包: CREATE OR REPLACE PACKAGE BODY CRMSPL.PACK_BACK IS TYPE r_RetDataSet IS REF CURSOR; PROCEDURE RP_BACK(year1 in varchar2, month1 in varchar2, c_back out PACK_BACK.r_RetDataSet); END PACK_BACK;--包体如下: CREATE OR REPLACE PACKAGE BODY CRMSPL.PACK_BACK IS PROCEDURE RP_BACK(year1 in varchar2, month1 in varchar2, c_back out PACK_BACK.r_RetDataSet) IS strsql varchar2(4000); strsql2 varchar2(4000); s1 NUMBER(10,2); s2 NUMBER(10,2); s3 NUMBER(10,2); s4 NUMBER(10,2); y varchar2(30); m varchar2(30);BEGIN strsql := 'CREATE TABLE temp01(y varchar2(30),m varchar2(30),s1 number,s2 number,s3 number,s4 number)'; EXECUTE IMMEDIATE strsql;SELECT EXTRACT(YEAR FROM IN_FINISH_DATE) INTO y FROM V_BACK; SELECT EXTRACT(MONTH FROM IN_FINISH_DATE) INTO m FROM V_BACK; SELECT COUNT(1) INTO s1 FROM V_BACK WHERE IN_FINISH_DATE - IN_SALE_DATE <='90'; SELECT COUNT(1) INTO s2 FROM V_BACK WHERE IN_FINISH_DATE - IN_SALE_DATE <='30'; SELECT COUNT(1) INTO s3 FROM V_BACK WHERE IN_FINISH_DATE - IN_SALE_DATE > '90'; SELECT COUNT(1) INTO s4 FROM V_BACK WHERE IN_FINISH_DATE - IN_SALE_DATE > '30';
EXECUTE IMMEDIATE 'INSERT INTO temp01 VALUES(y,m,s1,s2,s3,s4)'; strsql2:='SELECT * FROM temp01 WHERE y = year1 AND m = month1 '; OPEN c_back FOR strsql2 USING year1,month1;END RP_BACK; END PACK_BACK;如果使用下列语句创建临时表,又该怎么放呢,又该如何往里面插入数据呢: CREATE GLOBAL TEMPORARY TABLE temp01( y varchar2,m varchar2,s1 number,s2 number,s3 number,s4 number) ON COMMIT PRESERVE ROW;
答后面的问题: strsql2:='SELECT * FROM temp01 WHERE y = year1 AND m = month1 '; 写错了,正确如下: strsql2:='SELECT * FROM temp01 WHERE y = '''||year1||''' AND m = '''||month1||''' ';另外你写的游标很怪,看不懂。
如果要在一个会话中多次使用同一个临时表就要在必要的时候先delete from temp_table; 不commit也可以:)
CREATE OR REPLACE PACKAGE BODY CRMSPL.PACK_BACK
IS
TYPE r_RetDataSet IS REF CURSOR;
PROCEDURE RP_BACK(year1 in varchar2,
month1 in varchar2,
c_back out PACK_BACK.r_RetDataSet);
END PACK_BACK;--包体如下:
CREATE OR REPLACE PACKAGE BODY CRMSPL.PACK_BACK
IS
PROCEDURE RP_BACK(year1 in varchar2,
month1 in varchar2,
c_back out PACK_BACK.r_RetDataSet)
IS
strsql varchar2(4000);
strsql2 varchar2(4000);
s1 NUMBER(10,2);
s2 NUMBER(10,2);
s3 NUMBER(10,2);
s4 NUMBER(10,2);
y varchar2(30);
m varchar2(30);BEGIN
strsql := 'CREATE TABLE temp01(y varchar2(30),m varchar2(30),s1 number,s2 number,s3 number,s4 number)';
EXECUTE IMMEDIATE strsql;SELECT EXTRACT(YEAR FROM IN_FINISH_DATE) INTO y FROM V_BACK;
SELECT EXTRACT(MONTH FROM IN_FINISH_DATE) INTO m FROM V_BACK;
SELECT COUNT(1) INTO s1 FROM V_BACK WHERE IN_FINISH_DATE - IN_SALE_DATE <='90';
SELECT COUNT(1) INTO s2 FROM V_BACK WHERE IN_FINISH_DATE - IN_SALE_DATE <='30';
SELECT COUNT(1) INTO s3 FROM V_BACK WHERE IN_FINISH_DATE - IN_SALE_DATE > '90';
SELECT COUNT(1) INTO s4 FROM V_BACK WHERE IN_FINISH_DATE - IN_SALE_DATE > '30';
EXECUTE IMMEDIATE 'INSERT INTO temp01 VALUES(y,m,s1,s2,s3,s4)';
strsql2:='SELECT * FROM temp01 WHERE y = year1 AND m = month1 ';
OPEN c_back FOR strsql2 USING year1,month1;END RP_BACK;
END PACK_BACK;如果使用下列语句创建临时表,又该怎么放呢,又该如何往里面插入数据呢:
CREATE GLOBAL TEMPORARY TABLE temp01(
y varchar2,m varchar2,s1 number,s2 number,s3 number,s4 number)
ON COMMIT PRESERVE ROW;
不用在存储过程里面创建。不用担心并发访问的问题,自己的数据自己查看,当执行commit后,自动进行清空。
delete from temp01;
commit;
亦或是,直接在创建临时表时,带上on commit delete rows;就ok了?
嘿嘿,再问一个,为何在“CREATE GLOBAL TEMPORARY TABLE temp01(
y varchar2(30),m varchar2(30),,s1 number,s2 number,s3 number,s4 number)
ON COMMIT PRESERVE ROWS;”
创建好临时表后,之后查看临时表的源代码却和一般表无异,那不是没区别了吗?
若没关系,那可能是哪里出的问题了?
strsql2:='SELECT * FROM temp01 WHERE y = year1 AND m = month1 ';
写错了,正确如下:
strsql2:='SELECT * FROM temp01 WHERE y = '''||year1||''' AND m = '''||month1||''' ';另外你写的游标很怪,看不懂。
不commit也可以:)