/**
ORACLE的SQL规范，目的避免SQL执行错误，提高SQL脚本的质量；
一般执行SQL在command模式下，所以每个完整的语句需要在后面加上斜杠("/")，特别是过程或函数必须在其后加上斜杠("/")
多条非过程SQL语句在最后面一条语句后加上斜杠("/")，如insert、update、delete。在执行完，做下commit；
例子以表名TEST1说明，注意：create_str使用的varchar2类型，长度5000，如果SQL太长，要调整长度。
*/
--建表语句的规范
declare
create_str varchar2(5000) := 'create table TEST1(ID int,REALNAME varchar2(32))';
count_flag number;
begin
select count(*)
into count_flag
from user_tables
where table_name = 'TEST1';
if count_flag < 1 then
execute immediate create_str;
else
execute immediate 'drop table TEST1';
execute immediate create_str;
--字段注解规范
execute immediate 'COMMENT ON COLUMN "TEST1"."ID" IS ''编号''';
execute immediate 'COMMENT ON COLUMN "TEST1"."REALNAME" IS ''姓名''';
end if;
end;
/
--新增字段的规范
declare
add_str varchar2(5000) := 'alter table TEST1 add NICKNAME varchar(32)';
count_flag number;
begin
select count(*)
into count_flag
from user_tab_columns
where table_name = 'TEST1'
and column_name = 'NICKNAME';
if count_flag < 1 then
execute immediate add_str;
--字段注解规范
execute immediate 'COMMENT ON COLUMN "TEST1"."NICKNAME" IS ''昵称''';
end if;
end;
/
--修改字段的规范
declare
add_str varchar2(5000) := 'alter table TEST1 modify NICKNAME varchar(100)';
count_flag number;
begin
select count(*)
into count_flag
from user_tab_columns
where table_name = 'TEST1'
and column_name = 'NICKNAME';
if count_flag = 1 then
execute immediate add_str;
--字段注解规范
execute immediate 'COMMENT ON COLUMN "TEST1"."NICKNAME" IS ''昵称''';
end if;
end;
/
--删除字段的规范
declare
add_str varchar2(5000) := 'alter table TEST1 drop column NICKNAME';
count_flag number;
begin
select count(*)
into count_flag
from user_tab_columns
where table_name = 'TEST1'
and column_name = 'NICKNAME';
if count_flag = 1 then
execute immediate add_str;
end if;
end;
/
--创建索引的规范
declare
create_str varchar2(5000) := 'create index IDX_TEST1_ID on TEST1(id)';
count_flag number;
begin
select count(*) into count_flag from user_indexes where table_name='TEST1' and index_name='IDX_TEST1_ID';
if count_flag < 1 then
execute immediate create_str;
else
execute immediate 'drop index IDX_TEST1_ID';
execute immediate create_str;
end if;
end;
/
--创建序列的规范
declare
create_str varchar2(5000) := 'create sequence SEQ_TEST1 minvalue 1 maxvalue 999999999999 start with 1 increment by 1';
count_flag number;
begin
select count(*) into count_flag from user_sequences where sequence_name='SEQ_TEST1';
if count_flag < 1 then
execute immediate create_str;
else
execute immediate 'drop sequence SEQ_TEST1';
execute immediate create_str;
end if;
end;
/
--插入数据的SQL规范
insert into TEST1
(id, realname, nickname)
select 1, '网名', '网虫'
from dual
where not exists (select * from TEST1 where id = 1);
/
--更新数据的SQL规范
update TEST1 set realname='网名', nickname='网虫' where id=1;
/
--删除数据的SQL规范
delete from TEST1 t where t.id=1;
/
