如题,我创建如下存储过程:
CREATE OR REPLACE PROCEDURE PRO_USER_TABLE_ROWS_COUNT IS
CURSOR MY_CUR IS
SELECT * FROM USER_TABLES;
ROWS_COUNT NUMBER := 0;
BEGIN
FOR R IN MY_CUR LOOP
EXECUTE IMMEDIATE ' SELECT COUNT(*) FROM ' || R.TABLE_NAME;
INSERT INTO IFNEW1.TEMP_TABLE VALUES (R.TABLE_NAME, ROWS_COUNT);
EXIT WHEN MY_CUR%NOTFOUND;
END LOOP;
COMMIT;
END PRO_USER_TABLE_ROWS_COUNT;我想把语句:' SELECT COUNT(*) FROM ' || R.TABLE_NAME的执行结果存入变量 ROWS_COUNT,如果获得该语句执行结果?
CREATE OR REPLACE PROCEDURE PRO_USER_TABLE_ROWS_COUNT IS
CURSOR MY_CUR IS
SELECT * FROM USER_TABLES;
ROWS_COUNT NUMBER := 0;
BEGIN
FOR R IN MY_CUR LOOP
EXECUTE IMMEDIATE ' SELECT COUNT(*) FROM ' || R.TABLE_NAME;
INSERT INTO IFNEW1.TEMP_TABLE VALUES (R.TABLE_NAME, ROWS_COUNT);
EXIT WHEN MY_CUR%NOTFOUND;
END LOOP;
COMMIT;
END PRO_USER_TABLE_ROWS_COUNT;我想把语句:' SELECT COUNT(*) FROM ' || R.TABLE_NAME的执行结果存入变量 ROWS_COUNT,如果获得该语句执行结果?
CURSOR MY_CUR IS
SELECT * FROM USER_TABLES;
ROWS_COUNT NUMBER := 0;
BEGIN
FOR R IN MY_CUR LOOP
EXECUTE IMMEDIATE ' SELECT COUNT(*) FROM ' || R.TABLE_NAME INTO rows_count;
INSERT INTO TEMP_TABLE VALUES (R.TABLE_NAME, ROWS_COUNT);
END LOOP;
COMMIT;
END PRO_USER_TABLE_ROWS_COUNT;
/
EXECUTE IMMEDIATE ' SELECT * FROM ' || R.TABLE_NAME;
那我是不是应该先定义一个游标?这个游标应该怎么定义啊?然后怎么取得这个游标的值啊,也是循环游标吗?谢谢啦
CREATE OR REPLACE PROCEDURE PRO_USER_TABLE_ROWS_COUNT IS
CURSOR MY_CUR IS
SELECT * FROM USER_TABLES;
ROWS_COUNT NUMBER := 0;
v_sql varchar2(4000);
BEGIN
FOR R IN MY_CUR LOOP
v_sql:=' SELECT COUNT(*) FROM ' || R.TABLE_NAME;
EXECUTE IMMEDIATE v_sql into ROWS_COUNT ;
INSERT INTO IFNEW1.TEMP_TABLE VALUES (R.TABLE_NAME, ROWS_COUNT);
EXIT WHEN MY_CUR%NOTFOUND;
END LOOP;
COMMIT;
END PRO_USER_TABLE_ROWS_COUNT; pl/sql 工具的调试功能你会不会 会的话你可以在调试的时候就可以知道 对应变量在对应位置的值PL/SQL还是很强大的只不过很多能只掌握了一点点(够用了)没深研究 肯定对你有帮助
只是分太少了我说这么多已经不错了
对于你说的这种情况,我觉得表不能用动态的,否则返回的结果集字段不断变化,无法将单条记录放入到行变量中,也无法取得每一行的值,
下面是一个固定表名的例子,表名是user_objects,仅供参考CREATE OR REPLACE PROCEDURE PRO_USER_TABLE_ROWS_COUNT IS
TYPE TabCurTyp IS REF CURSOR;
tab_cv TabCurTyp ;
tab_row USER_OBJECTS%ROWTYPE;
BEGIN
OPEN tab_cv FOR 'SELECT * FROM user_objects';
loop
FETCH tab_cv INTO tab_row;
EXIT WHEN tab_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(tab_row.object_name);
end loop;
END PRO_USER_TABLE_ROWS_COUNT;
/