[DB Script] Pl/Sql 脚本汇总

         最近做的项目，需要自己建db schema，从头建起，正好学习一下这方面的知识。以下SQL，都是基于Oracle数据库。

         建表schema

 

-- Create table create table ACCTR_ACCTR_ENV ( ACCTR_ENV_ID VARCHAR2(15) not null, ENV_ID CHAR(15) not null, TICKET_ID CHAR(15) not null ) tablespace DATA01 pctfree 10 pctused 40 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Create/Recreate primary, unique and foreign key constraints alter table ACCTR_ACCTR_ENV add constraint ACCTR_ACCTR_ENV_PK primary key (ACCTR_ENV_ID) using index tablespace DATA01 pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table ACCTR_ACCTR_ENV add constraint ACCTR_ACCTR_ENV_ID_FK foreign key (ENV_ID) references ACCTR_ENV (ENV_ID); alter table ACCTR_ACCTR_ENV add constraint ACCTR_ACCTR_TICKET_ID_FK foreign key (TICKET_ID) references ACCTR_ACCTR (TICKET_ID);

         创建Trigger，为表acctr_acctr_env加上ACCTR_ACCTR_ENV_ID_SEQ序列。

 

CREATE TRIGGER Acctr_Acctr_Env_ID_Increase BEFORE insert ON acctr_acctr_env FOR EACH ROW begin select ACCTR_ACCTR_ENV_ID_SEQ.nextval into:New.acctr_env_id from dual; end;

 

         目前开发的lombardi框架很难将多个dml操作封装在一个transaction里，所以迫使我只能用sql拼接多个dml操作。代码如下：begin insert into ACCTR_ACCTR(ticket_id,status,priority,title,assignee,requester,description,date_submitted,notified_type,request_due_date,shell_type,account_action,biz_unit) values ('ACCTR0000001164','InProgress','Standard','Grant new Account for Xu Nigel','fenwang','jianhxu','Hi I''d like to apply for the new account granted',to_date('2010-12-13','yyyy-MM-dd'),'Every Update',to_date('2010-12-13','yyyy-MM-dd'),'csh','Grant Access','Other'); insert into acctr_acctr_env(acctr_env_id,env_id,ticket_id) values('4','6','ACCTR0000001041'); commit; exception when others then rollback; end;

 

如果想rollback之后还要throw exception

 

DECLARE commit_error EXCEPTION; begin 【insert_or_Update SQL】 commit; exception when others then rollback; raise commit_error; end;

或者利用 raise_application_erro函数。

raise_application_error(-20000, 'Fatal error, call support');

        另外，还有一个小技术点利用类似use_hash来确定优化策略。代码如下

select /*+ use_hash(t, s)*/distinct(t.user_name) from workflow.acctr_login_env t, workflow.acctr_env s where t.env_id=s.env_id and t.user_name='jianhxu'

 

如果想insert 数据之后，返回主键值，则可以如下：

declare id_ char(15); begin insert into acctr_env(name,env_type) values('Sox Team',1) returning env_id into id_; commit; end;