oracle sql 子游标_Oracle_Sql_游标的使用

/*例子：得到一个用户下所有表的数据条数*/

Declare

v_tableName VARCHAR2(256);/*存取游标获取的变量*/

v_count NUMBER;

Cursor c_tableName is Select Distinct table_name from dba_tables Where owner='GWBN';/*定义游标*/

BEGIN

OPEN c_tableName;/*打开游标*/

LOOP

FETCH c_tableName INTO v_tableName;/*通过游标获取值，记录到变量中*/

EXIT WHEN(c_tableName%NOTFOUND);/*如果游标没有获取到值，退出循环*/

IF(c_tableName%FOUND) THEN/*遍历游标，执行操作*/

EXECUTE IMMEDIATE 'Insert Into gj.gl_tmp_ck_table_count(exec_sql,table_name,is_necessary)

Values(''select count(1) from gwbn.'||v_tableName||''','''||v_tableName||''',Null)';

COMMIT;

EXECUTE IMMEDIATE 'UPDATE gj.gl_tmp_ck_table_count t

SET t.row_count = (SELECT COUNT(1) FROM gwbn.'||v_tableName||' WHERE t.table_name = '''||v_tableName||''')

WHERE t.table_name = '''||v_tableName||'''';

COMMIT;

END IF;

END LOOP;

CLOSE c_tableName;/*关闭游标*/

End;