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;