一个简单copy table 的stored procedure 代码

CREATE OR REPLACE PROCEDURE copy_tab
AUTHID current_user
as
coun number;
BEGIN
select count(*) into coun from user_tables where table_name='NEW_TABLE';
if coun > 0 then
execute immediate 'drop table new_table';
end if;
execute immediate 'create table new_table as select * from test';

END;
/

[@more@]

tips:

1. AUTHID current_user 确保本用户有使用此procedure的权限

2. select count(*) into coun from user_tables where table_name='NEW_TABLE';
if coun > 0 then
execute immediate 'drop table new_table';
end if;
如果要创建的表名已经存在，drop it.

as
coun number; 定义一个参数coun，在procedure block里使用。

3. execute immediate，ddl语句不能在procedure里直接使用，需要加上此。

4. over.

CREATE OR REPLACE PROCEDURE copy_tab
AUTHID current_user
as
coun number;
BEGIN
select count(*) into coun from user_tables where table_name='NEW_TABLE';
if coun > 0 then
execute immediate 'drop table new_table';
end if;
execute immediate 'create table new_table as select * from test';

END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1768615/viewspace-980457/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/1768615/viewspace-980457/