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;
/
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/