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博客 ” ，链接：https://blog.itpub.net/1768615/viewspace-980457/ ，如需转载，请注明出处，否则将追究法律责任。
