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;