我以前曾经做过的,给你了,如下:CREATE OR REPLACE PACKAGE dems AS TYPE tp_user IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER; TYPE tp_table IS TABLE OF VARCHAR2( 30 ) INDEX BY BINARY_INTEGER; PROCEDURE get_user( usr_grant OUT tp_user ); PROCEDURE get_all_table( str_user IN VARCHAR2 , all_tablename OUT tp_table ); PROCEDURE grant_user( object_user IN VARCHAR2 ); PROCEDURE grant_all_user; END dems; /CREATE OR REPLACE PACKAGE BODY dems AS ---------------------------------------------- --进 程 名:get_user() --功 能:取得所有非系统用户,在参数表usr_grant里返回. --返回类型:varchar2(30) --入口参数:usr_grant --作 者:王建忠 --时 间:2001、10、16 ---------------------------------------------- PROCEDURE get_user( usr_grant OUT tp_user ) IS n_loop INT := 1; CURSOR c_user IS SELECT username FROM sys.all_users WHERE ( user_id >= 40 ); BEGIN OPEN c_user; LOOP FETCH c_user INTO usr_grant( n_loop ); n_loop := n_loop + 1; EXIT WHEN c_user%NOTFOUND; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE( '数据未发现' ); END get_user; ---------------------------------------------- --进 程 名:get_all_table() --功 能:取得指定用户的所有表名,在参数表all_tablename里返回. --返回类型:varchar2(30) tp_table --入口参数:1、str_user 要返回表的用户名; -- 2、all_tablename返回的表。 --作 者:王建忠 --时 间:2001、10、16 ---------------------------------------------- PROCEDURE get_all_table( str_user IN VARCHAR2 , all_tablename OUT tp_table ) IS n_loop INT := 1; CURSOR c_table IS SELECT table_name FROM sys.all_tables WHERE owner=str_user; BEGIN OPEN c_table; LOOP FETCH c_table INTO all_tablename( n_loop ); n_loop := n_loop + 1; EXIT WHEN c_table%NOTFOUND; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE( '数据未发现' ); END; ---------------------------------------------- --进 程 名:grant_user() --功 能:将当前用户操作自己表的所有权限赋指定的一个用户 --返回类型:无 --入口参数:object_user 要赋予权限的用户名; --作 者:王建忠 --时 间:2001、10、16 ---------------------------------------------- PROCEDURE grant_user( object_user IN VARCHAR2 ) IS str_sql VARCHAR2( 1024 ); str_user VARCHAR2( 30 ); all_tablename tp_table; n_loop INT := 0; BEGIN SELECT USER INTO str_user FROM DUAL; --DBMS_OUTPUT.PUT_LINE( str_user ); get_all_table( str_user , all_tablename ); FOR n_loop IN 1..all_tablename.COUNT LOOP str_sql :='GRANT ALL ON ' || all_tablename( n_loop ) || ' TO ' || object_user; --在此执行授权语句; --DBMS_OUTPUT.PUT_LINE( str_sql ); EXECUTE IMMEDIATE( str_sql ); END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE( '授权失败' ); END grant_user; ---------------------------------------------- --进 程 名:grant_all_user --功 能:将当前用户操作自己表的所有权限赋所有非系统用户 --返回类型:无 --入口参数:无 --作 者:王建忠 --时 间:2001、10、16 ---------------------------------------------- PROCEDURE grant_all_user IS --TYPE tp_user IS TABLE OF VARCHAR2( 30 ) INDEX BY BINARY_INTEGER; --TYPE tp_table IS TABLE OF VARCHAR2( 30 ) INDEX BY BINARY_INTEGER; grntuser tp_user; all_tablename tp_table; str_sql VARCHAR2( 1024 ) := ' '; str_user VARCHAR2( 30 ) := ' '; n_loop_user int := 0; n_loop_table int := 0; BEGIN SELECT USER INTO str_user FROM DUAL; --DBMS_OUTPUT.PUT_LINE( 'It is:' || str_user || 'Finish.' ); get_user( grntuser ); --DBMS_OUTPUT.PUT_LINE( grntuser(1) ); FOR n_loop_user IN 1..grntuser.COUNT LOOP IF ( grntuser( n_loop_user ) != str_user ) THEN get_all_table( str_user , all_tablename ); FOR n_loop_table IN 1..all_tablename.COUNT LOOP str_sql :='GRANT ALL ON ' || all_tablename( n_loop_table ) || ' TO ' || grntuser( n_loop_user ) ; --DBMS_OUTPUT.PUT_LINE( str_sql ); --执行此动态语句; EXECUTE IMMEDIATE( str_sql ); END LOOP; END IF; END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE( '授权失败' ); END grant_all_user; END dems; /
1、如果仅仅是输出文本,那就很容易,用dbms_output.put_line()就可以了: CREATE OR REPLACE procedure myproc(p_tbl_name in varchar2) as w_tbl_name varchar2(30); w_col_name varchar2(30); cursor cur_tbl_col is select table_name,column_name from user_tab_columns where data_type = 'NUMBER' and data_precision = 1 and table_name = p_tbl_name; begin dbms_output.put_line(rpad('TABLE NAME',' ')||rpad('COLUMN_NAME',30,' ')); open cur_tbl_col; loop fetch cur_tbl_col into w_tbl_name, w_col_name; if cur_tbl_col%notfound then exit; end if; dbms_output.put_line(rpad(w_tbl_name,30,' ')||rpad(w_col_name,30,' ')); end loop; end myproc; /2、oracle的存储过程中是不允许用select... from...这样的语法的,必须用select ...into...from...。我想你的问题大概是用存储过程返回一个记录集吧?我回答了相当多这类的问题,请参考: http://www.csdn.net/expert/topic/338/338638.shtm http://www.vbip.com/books/1861001789/chapter_1789_09.asp 或者你可以去论坛里找一下。
TYPE tp_user IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER;
TYPE tp_table IS TABLE OF VARCHAR2( 30 ) INDEX BY BINARY_INTEGER; PROCEDURE get_user( usr_grant OUT tp_user );
PROCEDURE get_all_table( str_user IN VARCHAR2 , all_tablename OUT tp_table );
PROCEDURE grant_user( object_user IN VARCHAR2 );
PROCEDURE grant_all_user;
END dems;
/CREATE OR REPLACE PACKAGE BODY dems AS ----------------------------------------------
--进 程 名:get_user()
--功 能:取得所有非系统用户,在参数表usr_grant里返回.
--返回类型:varchar2(30)
--入口参数:usr_grant
--作 者:王建忠
--时 间:2001、10、16
----------------------------------------------
PROCEDURE get_user( usr_grant OUT tp_user ) IS
n_loop INT := 1;
CURSOR c_user IS SELECT username FROM sys.all_users WHERE ( user_id >= 40 );
BEGIN
OPEN c_user;
LOOP
FETCH c_user INTO usr_grant( n_loop );
n_loop := n_loop + 1;
EXIT WHEN c_user%NOTFOUND;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE( '数据未发现' );
END get_user; ----------------------------------------------
--进 程 名:get_all_table()
--功 能:取得指定用户的所有表名,在参数表all_tablename里返回.
--返回类型:varchar2(30) tp_table
--入口参数:1、str_user 要返回表的用户名;
-- 2、all_tablename返回的表。
--作 者:王建忠
--时 间:2001、10、16
----------------------------------------------
PROCEDURE get_all_table( str_user IN VARCHAR2 , all_tablename OUT tp_table ) IS
n_loop INT := 1;
CURSOR c_table IS SELECT table_name FROM sys.all_tables WHERE owner=str_user;
BEGIN
OPEN c_table;
LOOP
FETCH c_table INTO all_tablename( n_loop );
n_loop := n_loop + 1;
EXIT WHEN c_table%NOTFOUND;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE( '数据未发现' );
END; ----------------------------------------------
--进 程 名:grant_user()
--功 能:将当前用户操作自己表的所有权限赋指定的一个用户
--返回类型:无
--入口参数:object_user 要赋予权限的用户名;
--作 者:王建忠
--时 间:2001、10、16
----------------------------------------------
PROCEDURE grant_user( object_user IN VARCHAR2 ) IS
str_sql VARCHAR2( 1024 );
str_user VARCHAR2( 30 );
all_tablename tp_table;
n_loop INT := 0;
BEGIN
SELECT USER INTO str_user FROM DUAL;
--DBMS_OUTPUT.PUT_LINE( str_user );
get_all_table( str_user , all_tablename );
FOR n_loop IN 1..all_tablename.COUNT LOOP
str_sql :='GRANT ALL ON ' || all_tablename( n_loop ) || ' TO ' || object_user;
--在此执行授权语句;
--DBMS_OUTPUT.PUT_LINE( str_sql );
EXECUTE IMMEDIATE( str_sql );
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE( '授权失败' );
END grant_user; ----------------------------------------------
--进 程 名:grant_all_user
--功 能:将当前用户操作自己表的所有权限赋所有非系统用户
--返回类型:无
--入口参数:无
--作 者:王建忠
--时 间:2001、10、16
----------------------------------------------
PROCEDURE grant_all_user IS
--TYPE tp_user IS TABLE OF VARCHAR2( 30 ) INDEX BY BINARY_INTEGER;
--TYPE tp_table IS TABLE OF VARCHAR2( 30 ) INDEX BY BINARY_INTEGER;
grntuser tp_user;
all_tablename tp_table; str_sql VARCHAR2( 1024 ) := ' ';
str_user VARCHAR2( 30 ) := ' ';
n_loop_user int := 0;
n_loop_table int := 0;
BEGIN
SELECT USER INTO str_user FROM DUAL;
--DBMS_OUTPUT.PUT_LINE( 'It is:' || str_user || 'Finish.' );
get_user( grntuser );
--DBMS_OUTPUT.PUT_LINE( grntuser(1) );
FOR n_loop_user IN 1..grntuser.COUNT LOOP
IF ( grntuser( n_loop_user ) != str_user ) THEN
get_all_table( str_user , all_tablename );
FOR n_loop_table IN 1..all_tablename.COUNT LOOP
str_sql :='GRANT ALL ON ' || all_tablename( n_loop_table ) || ' TO ' || grntuser( n_loop_user ) ;
--DBMS_OUTPUT.PUT_LINE( str_sql );
--执行此动态语句;
EXECUTE IMMEDIATE( str_sql );
END LOOP;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE( '授权失败' );
END grant_all_user;
END dems;
/
CREATE OR REPLACE
procedure myproc(p_tbl_name in varchar2) as
w_tbl_name varchar2(30);
w_col_name varchar2(30);
cursor cur_tbl_col is
select table_name,column_name
from user_tab_columns
where data_type = 'NUMBER'
and data_precision = 1
and table_name = p_tbl_name;
begin
dbms_output.put_line(rpad('TABLE NAME',' ')||rpad('COLUMN_NAME',30,' '));
open cur_tbl_col;
loop
fetch cur_tbl_col into w_tbl_name, w_col_name;
if cur_tbl_col%notfound then
exit;
end if;
dbms_output.put_line(rpad(w_tbl_name,30,' ')||rpad(w_col_name,30,' '));
end loop;
end myproc;
/2、oracle的存储过程中是不允许用select... from...这样的语法的,必须用select ...into...from...。我想你的问题大概是用存储过程返回一个记录集吧?我回答了相当多这类的问题,请参考:
http://www.csdn.net/expert/topic/338/338638.shtm
http://www.vbip.com/books/1861001789/chapter_1789_09.asp
或者你可以去论坛里找一下。