ORACLE的SQL书写规范

ORACLE的SQL书写规范

/**

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;

/