Oracle 视图，存储过程，定时器开发,触发器

Oracle 视图

drop view view_TextASumm;
create or replace view view_TextASumm as
select i.companyno,i.wpid,i.stpbag,i.bagno,c.stonetype,c.stoneshape,c.stonesize,s.desc2,ri.price,i.iqty,i.iwt,i.iwt*ri.price as itprice,i.oqty,i.owt,i.owt*ri.price as otprice,i.wqty,i.wwt,i.wwt*ri.price as wtprice,i.trwt,i.trqty,i.trwt*ri.price as trprice from textA i,textB c,textC s,textD ri
         where i.bagno=c.bagno and i.stpbag=c.stpbag and c.stoneid=s.stoneid  and i.stpbag=ri.stpbag and (i.iqty<>0 or i.iwt<>0);
select * from view_TextASumm;

 

Oracle 存储过程

//创建表结构

create table testa(
 myid number(12) primary key,
 myname varchar2(20),
 myremark varchar2(200)
);

//删除存储

drop procedure tsprd;

//创建存储过程 语法：create or replace procedure   存储过程名称(参数名，参数类型，参数数据类型，.....)   is

//eg:创建一个存储过程，带两个输入参数，实现数据插入功能，自动获取最大id并且加1。
drop procedure tsprd;
create or replace procedure tsprd(pid in number,pname in VARCHAR2) is
maxid number;
begin
select max(myid) into maxid from testa;
INSERT INTO testa(myid,myname) VALUES((maxid+1),pname);
commit;
end;

//执行存储过程.

begin
tsprd(1,'aoli');
end;

//查询数据

select * from testa;

sqlplus 执行存储过程。

SQL> begin
  2  tsprd(3,'lijian');
  3  end;
  4  /

sqlplus 查看存储过程

SQL> select text from all_source where name='TSPRD';

 

Oracle 定时器   ---每一分钟执行一次。

declare jobno number;
begin
   sys.dbms_job.submit(jobno,
    what => 'tsprd(1, ''ni hao '');',
    Interval => 'TRUNC(sysdate,''mi'') + 1 / (24*60)'
  );
  commit;
end;

 

每小时执行

Interval => TRUNC(sysdate,'mi') + 1 / 24

每天的凌晨2点执行

Interval => TRUNC(sysdate) + 1 +2 / (24)

每周一凌晨2点执行

Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一(一周的第二天)

每周六凌晨2点执行

Interval => TRUNC(next_day(sysdate,7))+2/24 --星期六(一周的第七天)

每月1日凌晨2点执行

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24

每月5日上午10点执行

Interval =>TRUNC(LAST_DAY(SYSDATE))+5+10/24

每季度的第一天凌晨2点执行

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24

每年7月1日和1月1日凌晨2点

Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24

每年1月1日凌晨2点执行

Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24

 

NEXT_DAY(d,number) 

      -- 时间点d开始，下一个星期几的日期  

      -- 星期日 = 1 ， 星期一 = 2  ，星期二 = 3  ，星期三 = 4  ，星期四 = 5  ，星期五 = 6  ，星期六 = 7 

2、ADD_MONTHS(d,n)   --返回时间点d再加上n个月

 

3、LAST_DAY(d)             --时间点d当月份最后一天

 

触发器

 

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]table_name | [schema.]view_name 
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
BEGIN

[WHEN condition]

END;

 

create sequence seq_alti_stid;
create table altistdet(
  stid number primary key,
  stname varchar2(200),
  stremark varchar2(200),
  stdate date
);

//表中插入数据时，执行触发器。

create or replace trigger testa_trg_insert after insert on testa
 for each row
 declare
 begin
     insert into altistdet(stid,stname,stremark) values(seq_alti_stid.nextval,:new.myid,:new.myname); 
 end;
 

//表中数据更新时，记录更新信息。
create or replace trigger testa_trg_update after update on testa
 for each row
 declare
  v_num number;
  v_date date;
 begin
     select seq_alti_stid.nextval into v_num from dual;
     select sysdate into v_date from dual;
     insert into altistdet(stid,stname,stremark,stdate) values(v_num,:new.myid,:old.myname ||'-->'|| :new.myname,v_date); 
 end;

    触发器不接受参数。

l         一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

l         在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

l        触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

l         在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。

l         触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

l         在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

l         在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能是表中的任何long和blob列。

l         不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。