create or replace procedure pr_zhaozhenlong_strsql
/*
名称:在存储过程中执行动态sql
功能:动态创建、删除表;
动态修改表结构;
动态修改表主键;
判断某表、某列是否存在
调用:
begin
-- Call the procedure
pr_zhaozhenlong_strsql;
end;
创建人:赵振龙
创建时间:2007-01-03
*/
is
v_rows integer;
v_sqlstr varchar2(1000);
v_tablename varchar2(50);
v_pkname varchar2(50);
begin
--1、判断是否存在某表,删除、创建表
--select * from tabs where table_name = 'TB_ZHAOZHENLONG'
select count(1) into v_rows from tabs where table_name = 'TB_ZHAOZHENLONG';
if v_rows >0 then
execute immediate 'drop table tb_zhaozhenlong';
end if;
v_sqlstr := 'create table tb_zhaozhenlong("id" integer,rpt_date date,dept_id varchar2
(20),item varchar2(20), qty float)';
execute immediate(v_sqlstr);
select count(1) into v_rows from tabs where table_name = 'TB_TEMP_ZHAOZHENLONG';
if v_rows >0 then
execute immediate 'drop table tb_temp_zhaozhenlong';
end if;
--创建,基于会话的临时表
v_sqlstr := 'create global temporary table tb_temp_zhaozhenlong(rpt_date date,dept_id
varchar2(20),item varchar2(20), qty float, memo varchar(200))'
||' on commit preserve rows';
execute immediate(v_sqlstr);
--2、判断是否存在某表,删除、创建表
--select * from cols where table_name = 'TB_ZHAOZHENLONG' and column_name = 'MEMO';
select count(1) into v_rows from cols where table_name = 'TB_ZHAOZHENLONG' and
column_name = 'MEMO';
if v_rows <=0 then
v_tablename :='tb_zhaozhenlong';
v_sqlstr := 'alter table ' ||v_tablename ||' add "demo" varchar2(100)';
execute immediate(v_sqlstr);
else
execute immediate 'alter table tb_zhaozhenlong modify demo varchar2(200)';
end if;
--3、修改主键
v_tablename :='TB_ZHAOZHENLONG';
--第一步:增加列key_no
v_sqlstr :='alter table '||v_tablename||' add key_no int';
execute immediate(v_sqlstr);
--第二部:给key_no更新值
v_sqlstr :='update '||v_tablename||' set key_no =rownum';
execute immediate(v_sqlstr);
commit;
--第三步:将key_no置为非空
v_sqlstr :='alter table '||v_tablename||' modify key_no int not null';
execute immediate(v_sqlstr);
--第四步:查找主键
v_sqlstr :='select count(1)'
||' from user_constraints'
||' where constraint_type=''P'' and owner=user and
table_name='''||v_tablename ||'''' ;
execute immediate(v_sqlstr) into v_rows;
if v_rows >=1 then
v_sqlstr :='select constraint_name'
||' from user_constraints'
||' where constraint_type=''P'' and owner=user and
table_name='''||v_tablename ||'''' ;
execute immediate(v_sqlstr) into v_pkname;
end if;
--第五步:删除主键
if v_pkname is not null then
v_sqlstr := 'ALTER TABLE ' ||v_tablename ||' DROP CONSTRAINT '|| v_pkname ||'
CASCADE';
execute immediate(v_sqlstr);
else
v_pkname := 'pk_' ||v_tablename;
end if;
--第六步:增加主键
v_sqlstr := 'ALTER TABLE ' ||v_tablename ||' ADD (CONSTRAINT '|| v_pkname ||' PRIMARY
KEY(rpt_date,dept_id,item,key_no))';
execute immediate(v_sqlstr);
end pr_zhaozhenlong_strsql;
/*
名称:在存储过程中执行动态sql
功能:动态创建、删除表;
动态修改表结构;
动态修改表主键;
判断某表、某列是否存在
调用:
begin
-- Call the procedure
pr_zhaozhenlong_strsql;
end;
创建人:赵振龙
创建时间:2007-01-03
*/
is
v_rows integer;
v_sqlstr varchar2(1000);
v_tablename varchar2(50);
v_pkname varchar2(50);
begin
--1、判断是否存在某表,删除、创建表
--select * from tabs where table_name = 'TB_ZHAOZHENLONG'
select count(1) into v_rows from tabs where table_name = 'TB_ZHAOZHENLONG';
if v_rows >0 then
execute immediate 'drop table tb_zhaozhenlong';
end if;
v_sqlstr := 'create table tb_zhaozhenlong("id" integer,rpt_date date,dept_id varchar2
(20),item varchar2(20), qty float)';
execute immediate(v_sqlstr);
select count(1) into v_rows from tabs where table_name = 'TB_TEMP_ZHAOZHENLONG';
if v_rows >0 then
execute immediate 'drop table tb_temp_zhaozhenlong';
end if;
--创建,基于会话的临时表
v_sqlstr := 'create global temporary table tb_temp_zhaozhenlong(rpt_date date,dept_id
varchar2(20),item varchar2(20), qty float, memo varchar(200))'
||' on commit preserve rows';
execute immediate(v_sqlstr);
--2、判断是否存在某表,删除、创建表
--select * from cols where table_name = 'TB_ZHAOZHENLONG' and column_name = 'MEMO';
select count(1) into v_rows from cols where table_name = 'TB_ZHAOZHENLONG' and
column_name = 'MEMO';
if v_rows <=0 then
v_tablename :='tb_zhaozhenlong';
v_sqlstr := 'alter table ' ||v_tablename ||' add "demo" varchar2(100)';
execute immediate(v_sqlstr);
else
execute immediate 'alter table tb_zhaozhenlong modify demo varchar2(200)';
end if;
--3、修改主键
v_tablename :='TB_ZHAOZHENLONG';
--第一步:增加列key_no
v_sqlstr :='alter table '||v_tablename||' add key_no int';
execute immediate(v_sqlstr);
--第二部:给key_no更新值
v_sqlstr :='update '||v_tablename||' set key_no =rownum';
execute immediate(v_sqlstr);
commit;
--第三步:将key_no置为非空
v_sqlstr :='alter table '||v_tablename||' modify key_no int not null';
execute immediate(v_sqlstr);
--第四步:查找主键
v_sqlstr :='select count(1)'
||' from user_constraints'
||' where constraint_type=''P'' and owner=user and
table_name='''||v_tablename ||'''' ;
execute immediate(v_sqlstr) into v_rows;
if v_rows >=1 then
v_sqlstr :='select constraint_name'
||' from user_constraints'
||' where constraint_type=''P'' and owner=user and
table_name='''||v_tablename ||'''' ;
execute immediate(v_sqlstr) into v_pkname;
end if;
--第五步:删除主键
if v_pkname is not null then
v_sqlstr := 'ALTER TABLE ' ||v_tablename ||' DROP CONSTRAINT '|| v_pkname ||'
CASCADE';
execute immediate(v_sqlstr);
else
v_pkname := 'pk_' ||v_tablename;
end if;
--第六步:增加主键
v_sqlstr := 'ALTER TABLE ' ||v_tablename ||' ADD (CONSTRAINT '|| v_pkname ||' PRIMARY
KEY(rpt_date,dept_id,item,key_no))';
execute immediate(v_sqlstr);
end pr_zhaozhenlong_strsql;
/
转载自:http://blog.163.com/jiateng_zhi/blog/static/6616697220081041532223/