一个项目引出的对ORACLE中带参视图、存储过程、函数、程序包的使用

公司做一个项目，因为公司尽量希望能够在前台处理事件，领导的心思，你永远不懂，于是尽可能的把事件放到数据库处理。。。反正我是这样干的

1、自定义函数

-----------------------------------------------------------------------------官方

定义：

CREATE OR REPLACE FUNCTION function_name /*函数名称*/

(
Parameter_name1,mode1 datatype1,　　　　　　　　　　　 /*参数定义部分*/
Parameter_name2,mode2 datatype2,
Parameter_name3,mode3 datatype3
…
)
RETURN return_datatype　　　　　　　　　　　　　　　　/*定义返回值类型*/
IS/AS
BEGIN
       Function_body　　　　　　　　　　　　　　　　　 /*函数体部分*/
      RETURN scalar_expression                        /*返回语句*/
END function_name;

说明：

function_name:：用户定义的函数名。函数名必须符合标示符的定义规则，对其所有者来说，该名在数据库中是唯一的。

parameter：用户定义的参数。用户可以定义一个或多个参数。

mode：参数类型。

datatype：用户定义参数的数据类型。

return_type:：用户返回值的数据类型。

函数返回scalar_expression表达式的值，function_body函数体由pl/sql语句构成。

---------------------------------------------------------------------------------------------------------------------项目实例

目的：查出数据库某个表的最大id，然后生成小于此id的值，譬如max(id)=8，那么生成一列为1,2,3,4,5,6,7

    主要是为了解决缺失编号问题，因为项目需要，暂时想起这么一个办法。

CREATE OR REPLACE FUNCTION BHBL--(ajh in integer)--不带参数的函数
return varchar
IS
       i number;--声明变量
       k number;
       ajh_ varchar(20);
BEGIN
select max(to_number(ajh)) into k from da_xzdabh 
where SF_FLH=p_view_param.get_FLH() and SF_sszy=p_view_param.get_SSZY()--这里调用了包
and MLH=p_view_param.get_MLH() and del=0;                              --这个SQL的作用是把查出的最大ajh赋值给k          
       i:=0;
       --k:=ajh;
    while i<k loop<span style="white-space:pre">	</span>--while循环
    i:=i+1;
    ajh_:=ajh_||i||',';--拼凑ajh_,
--select count(*) into ajh_ from DA_XZDABH;
end loop;
    ajh_:=substr(ajh_,1,length(ajh_)-1);
       return(ajh_);
END BHBL;

2、存储过程

--------------------------------------------------------------------------网络

CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN

END 存储过程名字

--------------------------------------------------------------------------项目实例

目的：因为在新增数据的时候需要两个表数据同步，后台调存储过程是写好的方法，自己是不能写后台的，所以有了这个存储过程，同时插入三个表的数据

CREATE OR REPLACE PROCEDURE DA_proAddBH(oid  integer,qajh_s in integer,zajh_s in integer)--res_num out integer)--in为输入参数，out为输出参数
as
  qzh_ NVARCHAR2(300);--声明变量
  mlh_ varchar2(20);
  sf_flh_ varchar2(20);
  sf_sszy_ varchar2(20);
  qajh_ varchar2(20);
  zajh_ varchar2(20);
  zlsj_ varchar2(20);
  dazjsj_ varchar2(20);
  sf_dazlr_ varchar2(20);
  sf_dazjy_ varchar2(20);
  ajh_ varchar2(20);
    czy_ varchar2(20);
  dabh_ varchar2(20);
  j number;
  i number;
  k number;
  l number;
  m number;
  n number;
  w number;
  y number;
  z number;
  oid_ number;
begin
   oid_:=oid;
   --更新新加入的数据
  update Da_Xzdabh A set ajh=qajh,dabh=(select (p.qzh||'-'||p.mlh||'-'||substr(q.name,0,INSTR(q.name,'|',1,1)-1)||'-'||p.qajh) as dabh  from DA_XZDABH p
  left join sf_flh Q on p.sf_flh=q.no where p.oid=oid_ and rownum=1),btnbc=(select (p.qzh||'-'||p.mlh||'-'||substr(q.name,0,INSTR(q.name,'|',1,1)-1)||'-') as btnbc  from DA_XZDABH p
  left join sf_flh Q on p.sf_flh=q.no where p.oid=oid_ and rownum=1)
  where A.oid=oid_ ;
  commit;
  --给定义的变量赋值
  select  qzh,mlh,sf_flh,sf_sszy,qajh,zajh,zlsj,dazjsj,sf_dazlr,sf_dazjy,ajh,btnbc,czy
  into qzh_,mlh_,sf_flh_,sf_sszy_,qajh_,zajh_,zlsj_,dazjsj_,sf_dazlr_,sf_dazjy_,ajh_,dabh_,czy_
  from Da_Xzdabh where oid=oid_ and rownum=1;
  --select Dabh into dabh_ from

  --res_num:=qzh_;
  --------------------------------
  j:=qajh_s;
  i:=zajh_s-qajh_s;
  k:=0;
  if i<=200 then
  --循环插入数据新增档案编号
 while k<i loop
     j:=j+1;
     k:=k+1;
		 insert into Da_Xzdabh(rdt,oid,qzh,mlh,Sf_Flh,Sf_Sszy,Qajh,zajh,zlsj,dazjsj,Sf_Dazlr,Sf_Dazjy,Ajh,Dabh,Czy)values
    (to_char(sysdate,'yyyy-mm-dd'),DA_XZDABH_OID.nextval,qzh_,mlh_,sf_flh_,sf_sszy_,qajh_s,zajh_s,to_char(sysdate,'yyyy-mm-dd'),to_char(sysdate,'yyyy-mm-dd'),sf_dazlr_,sf_dazjy_,j,dabh_||j,czy_);
    commit;
end loop;
-----------------------------------
  l:=qajh_s;
  m:=zajh_s-qajh_s;
  n:=0;
  --循环插入数据著录索引表
 while n<=m loop
     l:=l+1;
     n:=n+1;
      insert into Da_ZLSY(rdt,oid,qzh,mlh,Sf_SYFlh,Sf_SYSszy,zlsj,dazhijiansj,dazlr,dazjy,Ajh,Dabh,Czy)values--,Dabh
    (to_char(sysdate,'yyyy-mm-dd'),da_ZLSY_oid.nextval,qzh_,mlh_,sf_flh_,sf_sszy_,to_char(sysdate,'yyyy-mm-dd'),to_char(sysdate,'yyyy-mm-dd'),sf_dazlr_,sf_dazjy_,l-1,dabh_||(l-1),czy_);
    commit;
     end loop;
-----------------------------------
  w:=qajh_s;
  y:=zajh_s-qajh_s;
  z:=0;
  --循环插入ND101节点模拟发送数据
 while z<=y loop
     w:=w+1;
     z:=z+1;
      insert into ND101(oid,FID,RDT,CDT,REC,MYNUM,FK_DEPT,TITLE,PRI,FK_NY,EMPS)values--,Dabh
    (DA_NO101_OID.nextval,0,to_char(sysdate,'yyyy-mm-dd'),to_char(sysdate,'yyyy-mm-dd'),czy_,1,'1',to_char(sysdate,'yyyy-mm-dd')||'发起',2,'',czy_);
    commit;
     end loop;
end if;
END ;

3、程序包

目的：为了写带参数的视图而建的，类似于C#中的读写属性，set,get；

注意：程序包包含声明和程序包体两部分；

声明

create or replace package p_view_param  is
       --参数一
   function set_FLH(flh varchar2) return varchar2;
   function get_FLH  return varchar2;
   --参数二
   function set_SSZY(sszy varchar2) return varchar2;
   function get_SSZY  return varchar2;
   --参数三
    --参数二
   function set_MLH(MLH varchar2) return varchar2;
   function get_MLH  return varchar2;
   end p_view_param;

程序包Body

create or replace package body p_view_param is
       paramFLH varchar2(20);
       paramSSZY varchar2(20);
       paramMLH varchar2(20);
       -- Param
       function set_FLH(flh varchar2) return varchar2 is
       begin
         paramFLH:=flh;
         return flh;
        end;

       function get_FLH return varchar2 is
       begin
         return paramFLH;
       end;
       -- Type
       function set_SSZY(sszy varchar2) return varchar2 is
       begin
         paramSSZY:=sszy;
         return sszy;
        end;

       function get_SSZY return varchar2 is
       begin
         return paramSSZY;
       end;
        function set_MLH(MLH varchar2) return varchar2 is
       begin
         paramMLH:=MLH;
         return MLH;
        end;

       function get_MLH return varchar2 is
       begin
         return paramMLH;
       end;
   end p_view_param;

4、带参数的视图

目的：需要通过传过来的查询条件动态生成视图，

用的时候这么用SQL:

select * from v_qsajhlb where 1=1 and  p_view_param.set_MLH('1')='1' and  p_view_param.set_FLH('01')='01' and p_view_param.set_SSZY('02')='02';

这样写的目的是通过p_view_param.set_MLH('1')让p_view_param.get_MLH()=‘1’;

create or replace view v_qsajhlb as
select rownum as XH,'B063' as QZH,A."AJH"
,l.name as sf_flh,
m.name as sf_sszy
,p_view_param.get_MLH() as MLH,
('B063-'||p_view_param.get_MLH()||'-'||substr(l.name,0,INSTR(l.name,'|',1,1)-1)||'-'||a.ajh) as dabh,
'?FK_Flow='||'001'||'&'||'FK_Node='||101||'&'||'FID='||0
  ||'&'||'WorkID='||0  --注释：OID目前没有赋予值
  ||'&'||'IsEdit=1'||'&'||'IsRead=0'|| '&'||'Fk_Frmsort=09'|| '&'||'TableName=da_xzdabh' AS Args
from
(select distinct to_number(regexp_substr(bhbl,'[^,]+',1,level)) as ajh from dual --注意看:这里的bhbl是调的起始的函数
connect by level<=length(bhbl)-length(replace(bhbl,',',''))+1
minus
select to_number(ajh) from da_xzdabh where
sf_flh=p_view_param.get_FLH() and sf_sszy=p_view_param.get_SSZY()
and MLH=p_view_param.get_MLH() and del=0
) A
 left join sf_flh l on p_view_param.get_FLH()=l.NO
 left join sf_sszy m on p_view_param.get_SSZY()=m.no;

5、序号Sequence

目的:在存储过程中进行了调用，来生成唯一的id

sequence
-- Create sequence 
create sequence DA_XZDABH_OID
minvalue 1
maxvalue 999999999999999999999999999
start with 41
increment by 1
cache 20;