参考网址:http://hi.baidu.com/lichangzai/blog/item/ccf30bec6bb451d52e2e2131.html
PROCEDURE GRANT_SQL
AS
THE_C1 NUMBER;/*©w¸q¥ú¼Ð*/
THE_RESULT NUMBER;
V_TABLE VARCHAR2(80);
V_SQL VARCHAR2(400);
BEGIN
/*1.為處理打開光標*/
THE_C1 :=DBMS_SQL.OPEN_CURSOR;
/*2.分析語句*/
DBMS_SQL.PARSE(THE_C1,'SELECT TABLE_NAME FROM USER_TABLES',DBMS_SQL.NATIVE);
/*3.定義相關字段*/
DBMS_SQL.DEFINE_COLUMN(THE_C1,1,V_TABLE,80);
/*4.執行語句*/
THE_RESULT := DBMS_SQL.EXECUTE(THE_C1);
LOOP
IF DBMS_SQL.FETCH_ROWS(THE_C1) > 0 THEN
/*取出字段值*/
DBMS_SQL.COLUMN_VALUE(THE_C1,1,V_TABLE);
/*組成動態SQL語句*/
V_SQL := 'GRANT SELECT ON '||V_TABLE||' TO NEW_USER';
/*執行SQL語句*/
EXECUTE IMMEDIATE V_SQL;
ELSE
GOTO EEND;
END IF;
END LOOP;
<<EEND>>
/*關閉游標*/
DBMS_SQL.CLOSE_CURSOR(THE_C1);
END;