oracle+exec+sql+游标,oracle plsql 游标的使用

set serveroutput on;

DECLARE

str_tab_name VARCHAR2(100);

str_tab_owner VARCHAR2(20);

num_contain_QT_loc number;

str_exec_grant varchar2(200);

cursor cur is

SELECT distinct(tb_pri.table_name) as TB_NAME, tb_pri.owner as TB_OWNER FROM dba_tab_privs tb_pri inner join

(select owner, table_name from dba_tables where owner in ('BUAT_META','BUAT_AUDIT','BUAT_MAINT','BUAT_METRICS','BUAT_TRANS')) tb_info

on tb_pri.table_name = tb_info.table_name and tb_pri.owner = tb_info.owner;

BEGIN

open cur;

loop

fetch cur into str_tab_name,str_tab_owner;

exit when cur%notfound;

select length(str_tab_name) - INSTR(str_tab_name, '_QT') into num_contain_QT_loc from dual;

if num_contain_QT_loc > 2 then

str_exec_grant := 'GRANT DELETE,INSERT,SELECT,UPDATE ON '|| str_tab_owner ||'.'||str_tab_name||' TO GCE1_DMM_UPDATE_ROLE';

dbms_output.put_line('str_tab_name :'||str_tab_name || ', str_tab_owner : ' || str_tab_owner || ' , grant 4.' );

else

str_exec_grant := 'GRANT SELECT ON '|| str_tab_owner ||'.'||str_tab_name||' TO R2_DMM_READONLY_ROLE';

dbms_output.put_line('str_tab_name :'||str_tab_name || ', str_tab_owner : ' || str_tab_owner || ' , grant select.');

end if;

dbms_output.put_line('sql :' || str_exec_grant);

execute immediate str_exec_grant;

end loop;

close cur;

exception

when others

then

if cur%isopen

then

close cur;

end if;

END;

/