SQL写法规范（创建，更新等）

SQL写法规范

一、创建表

declare
 cnt integer;
begin
    select count(0)
    into cnt  
    from user_all_tables
   where table_name = upper('Student_Info');
   
    if cnt = 0 then
	   execute immediate 'CREATE TABLE STUDENT_INFO
		( ID VARCHAR2(12) NOT NULL)';
	end if;						 
end;
/

二、增加约束

declare
 cnt integer;
begin
    select count(0)
    into cnt  
    from user_constraints a
   where a.constraint_name = upper('STUDENT_UK');
    		
    if cnt = 0 then
        execute immediate 'ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_UK UNIQUE(ID) ENABLE';
	end if;						 
end;

三、更新表字段大小

declare cnt integer;
begin
  select count(0)
    into cnt
    from user_tab_columns a
   where a.Table_name = upper('STUDENT')
     and a.COLUMN_NAME = upper('ID')
     and a.DATA_LENGTH < 32;
 
  if cnt = 1 then
    execute immediate 'ALTER TABLE STUDENT modify ID VARCHAR2(64)';
  end if;
end;

四、创建序列

declare
 cnt integer;
begin
    select count(0)
    into cnt  
    from user_sequences a
   where a.sequence_name = upper('STUDENT_SEQ');
   
    if cnt = 0 then
     execute immediate 'create sequence STUDENT_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE';
  end if;             
end;
/

五、创建索引

declare
 cnt integer;
begin
    select count(0)
    into cnt  
    from user_indexes a
   where a.index_name = upper('UK_ID') and a.table_name = upper('STUDENT');		
    if cnt = 0 then
        execute immediate 'create unique index UK_ID on STUDENT (
	ID ASC)';
	end if;						 
end;
/

六、增加列

declare
  cnt integer;
begin
	select count(0)
      into cnt	
	  from user_tab_columns a
	 where a.Table_name = upper('STUDENT')
	   and a.COLUMN_NAME = upper('NAME');
	   
    if cnt = 0 then
	   execute immediate 'ALTER TABLE STUDENT ADD NAME varchar2(22)';
	end if;
end;
/

七、删除序列和索引

declare
  cnt integer;
begin
  select count(0)
    into cnt
    from user_constraints a
    where a.constraint_name = upper('PK_STUDENT');
   
  if (cnt > 0) then
    execute immediate 'alter table cnaps drop constraint PK_STUDENT';
  end if;
  
  select count(0)
    into cnt 
    from user_indexes a
    where a.index_name=upper('PK_STUDENT');  
    
  if (cnt > 0) then
    execute immediate 'drop index PK_STUDENT';
  end if;

end;
/

命名规则：

update: *.SQL

create table: *.TAB

create sequence: *SEQ.PDC

create procedure: *.PRC

create view: VM_*.VM

批量执行sql文件：

文件内容（*.pdc)：

@目录\sql1.PDC;

@目录\sql2.PDC;

通过pl/sql打开command script 然后找到批量执行文件*.pdc，然后选择“执行”