CREATE GLOBAL TEMPORARY TABLE temp_1 (a INT);CREATE OR REPLACE PROCEDURE p_test IS l_i INT; BEGIN INSERT INTO temp_1 VALUES(2); SELECT COUNT(*) INTO l_i FROM temp_1; dbms_output.put_line(l_i); END; /BEGIN p_test; END; /输出: 1
/--给个例子,试一下:create table SSS ( ID NUMBER, VALUE1 NUMBER )insert into sss values(1,2);CREATE OR REPLACE PROCEDURE BBBB(PO_RESULT OUT SYS_REFCURSOR) IS BEGIN execute immediate 'CREATE GLOBAL TEMPORARY TABLE AAA (NUM VARCHAR2(10),QTY INTEGER) ON COMMIT DELETE ROWS '; execute immediate 'insert into AAA SELECT * FROM sss'; OPEN PO_RESULT FOR 'select * from AAA'; END BBBB;
CREATE OR REPLACE PROCEDURE BBBB(num OUT integer) IS BEGIN execute immediate 'CREATE GLOBAL TEMPORARY TABLE AAA (NUM VARCHAR2(10),QTY INTEGER) ON COMMIT DELETE ROWS '; execute immediate 'insert into AAA SELECT count(*) FROM sss'; select a.num into num from AAA a; commit; END BBBB;我是这样写的,不过是不对的。 另外,这段代码要实现的功能我已经用别的方法实现了,但我不明白上面这段代码为什么错。
l_i INT;
BEGIN
INSERT INTO temp_1 VALUES(2);
SELECT COUNT(*) INTO l_i FROM temp_1;
dbms_output.put_line(l_i);
END;
/BEGIN
p_test;
END;
/输出:
1
/--给个例子,试一下:create table SSS
(
ID NUMBER,
VALUE1 NUMBER
)insert into sss values(1,2);CREATE OR REPLACE PROCEDURE BBBB(PO_RESULT OUT SYS_REFCURSOR) IS
BEGIN
execute immediate 'CREATE GLOBAL TEMPORARY TABLE AAA (NUM VARCHAR2(10),QTY INTEGER) ON COMMIT DELETE ROWS '; execute immediate 'insert into AAA SELECT * FROM sss'; OPEN PO_RESULT FOR 'select * from AAA';
END BBBB;
CREATE OR REPLACE PROCEDURE BBBB(num OUT integer) IS
BEGIN
execute immediate 'CREATE GLOBAL TEMPORARY TABLE AAA (NUM VARCHAR2(10),QTY INTEGER) ON COMMIT DELETE ROWS '; execute immediate 'insert into AAA SELECT count(*) FROM sss';
select a.num into num from AAA a;
commit;
END BBBB;我是这样写的,不过是不对的。
另外,这段代码要实现的功能我已经用别的方法实现了,但我不明白上面这段代码为什么错。
没有drop table, 这个BBBB只能正确的执行一次。第二次再创建表,就出错了