Question

oracle 存储过程中查询结果无法赋值到变量，请指教

现有一表格local_xx，内有信息若干，要统计总数并且赋值到一个新的表格TEST中，代码如下，但新表格内没有数据，请问该如何修改？

CREATE OR REPLACE PROCEDURE test1
IS
tmpVar NUMBER;
strSQL VARCHAR2 (2000);
BEGIN

strSQL := 'drop table test';

EXECUTE IMMEDIATE strSQL;
strSQL := 'CREATE TABLE DDCXD.test(bh NUMBER)';

EXECUTE IMMEDIATE strSQL;

strSQL := 'select count(*) from local_xx';

EXECUTE IMMEDIATE strSQL into tmpVar ;

strSQL := 'insert into test values(' || tmpVar || ')';

EXECUTE IMMEDIATE strSQL;
END test1;

Answer 1

你最后没commit

CREATE OR REPLACE PROCEDURE test1
IS
tmpVar NUMBER;
strSQL VARCHAR2 (2000);
BEGIN

strSQL := 'drop table test';

EXECUTE IMMEDIATE strSQL;
strSQL := 'CREATE TABLE DDCXD.test(bh NUMBER)';

EXECUTE IMMEDIATE strSQL;

strSQL := 'select count(*) from local_xx';

EXECUTE IMMEDIATE strSQL into tmpVar ;

strSQL := 'insert into test values(' || tmpVar || ')';

EXECUTE IMMEDIATE strSQL;
commit;
END test1;

这样就行了