1、Oracle存储过程案例

一、完整的PLSQL过程示例

CREATE OR REPLACE PROCEDURE PTYZH_CXJTZHXX_XB(o_result     OUT SYS_REFCURSOR,
                                          i_pageno     IN NUMBER,
                                          i_pagelength IN NUMBER,
                                          i_totalrows  OUT NUMBER,
                                          i_ywxt       IN VARCHAR2, --业务系统
                                          i_khh        IN VARCHAR2, --客户号
                                                              i_yyb        IN NUMBER,   --营业部
                                          i_ssdw       IN VARCHAR2, --所属单位
                                          i_sj         IN VARCHAR2, --认证手机
                                          i_email      IN VARCHAR2, --认证邮箱
                                          i_rzrq_start IN NUMBER,  --认证日期
                                                              i_rzrq_end   IN NUMBER,  --认证日期
                                          i_bdrq_start IN NUMBER,  --绑定日期
                                                              i_bdrq_end   IN NUMBER,  --绑定日期
                                                              i_yhlx       IN NUMBER,  --用户类型
                                          i_yhqx       IN VARCHAR2, --是否中航证券员工
                                          i_cid        IN VARCHAR2 --是否绑定交易账号
                                          ) AS
  l_collist VARCHAR2(32767);
  l_code    NUMBER(10);
  l_note    VARCHAR2(100);
  l_sort    VARCHAR2(100);
  l_sql    VARCHAR2(3000); 
  l_sql1   VARCHAR2(1500); 
  l_where  VARCHAR2(2000); 
BEGIN
  l_sort    := ' a.khh ';
  l_collist := ' * ';
  l_sql    := 'select a.cid,a.khh,a.khmc,case when a.ywxt=1000 then ''集中交易'' else ''融资融券'' end as ywxt,a.yyb,a.sj,a.ssdw,a.rzksrq,a.bdrq,case when a.yhlx=1 then ''集团户'' else ''亲友户'' end as yhlx,case when a.yhqx=1 then ''是'' else ''否'' end as yhqx,a.email,a.tjr,a.fid from ';
  l_sql1     := '(select c.cid,a.khh,c.khmc,a.ywxt,a.yyb,c.sj,c.ssdw,c.rzksrq,c.bdrq,c.yhlx,c.yhqx,c.email,c.tjr,c.fid
from tywzh a,(select b.khh,a.khmc,a.sj,a.rzksrq,a.bdrq,a.yhlx,a.ssdw,a.yhqx,a.email,a.tjr,a.cid,a.fid from tjtzhxx a,tkhxx b where (a.cid=b.cid and  b.khzt=0 and b.ygtcid is not null) or (a.sj=b.sj and b.khmc=a.khmc  and  a.cid is null and b.khzt=0 and b.ygtcid is not null)) c
where a.khh=c.khh) a'; 
  l_where   := ' where a.ywxt in (1000,1001) ';
   --业务系统做为查询条件
  IF i_ywxt IS NOT NULL THEN
    l_where := l_where || ' and a.ywxt = ' || i_ywxt;
  END IF;
  --客户号做为查询条件
  IF i_khh IS NOT NULL THEN
    l_where := l_where || ' and a.khh = ''' || i_khh || '''';
  END IF;
  --认证所属单位作为查询条件
  IF i_ssdw IS NOT NULL THEN
    l_where := l_where || ' and a.ssdw like ''%' || i_ssdw || '%''';
  END IF;
  --认证手机号作为查询条件
  IF i_sj IS NOT NULL THEN
    l_where := l_where || ' and a.sj = ' || i_sj;
  END IF;
  --营业部作为查询条件
  IF i_yyb IS NOT NULL THEN
    l_where := l_where || ' and a.yyb = ' || i_yyb;
  END IF;
 --认证邮箱作为查询条件
  IF i_email IS NOT NULL THEN
    l_where := l_where || ' and a.email like ''%' || i_email || '%''';
  END IF;
  --用户类型做为查询条件
  IF i_yhlx IS NOT NULL THEN
    l_where := l_where || ' and a.yhlx = ' || i_yhlx;
  END IF;
   --是否中航证券员工做为查询条件
  IF i_yhqx IS NOT NULL THEN
    l_where := l_where || ' and a.yhqx = ' || i_yhqx;
  END IF;
 --认证日期作为查询条件
  IF i_rzrq_start IS NOT NULL AND i_rzrq_end IS NULL THEN
    l_where := l_where || ' and a.rzksrq >= ' || i_rzrq_start;
  END IF;
  IF i_rzrq_start IS NULL AND i_rzrq_end IS NOT NULL THEN
    l_where := l_where || ' and a.rzksrq <= ' || i_rzrq_end;
  END IF;
  IF i_rzrq_start IS NOT NULL AND i_rzrq_end IS NOT NULL THEN
    l_where := l_where || ' and a.rzksrq >= ' || i_rzrq_start ||
               ' and a.rzksrq <= ' || i_rzrq_end;
  END IF;
  --绑定日期作为查询条件
  IF i_bdrq_start IS NOT NULL AND i_bdrq_end IS NULL THEN
    l_where := l_where || ' and a.bdrq >= ' || i_bdrq_start;
  END IF;
  IF i_bdrq_start IS NULL AND i_bdrq_end IS NOT NULL THEN
    l_where := l_where || ' and a.bdrq <= ' || i_bdrq_end;
  END IF;
  IF i_bdrq_start IS NOT NULL AND i_bdrq_end IS NOT NULL THEN
    l_where := l_where || ' and a.bdrq >= ' || i_bdrq_start ||
               ' and a.bdrq <= ' || i_bdrq_end;
  END IF;
  --是否绑定交易账号作为查询条件1
  IF i_cid IS NOT NULL AND i_cid = 0 THEN
    l_where := l_where || ' and a.cid is null ';
  END IF;
  --是否绑定交易账号作为查询条件2
  IF i_cid IS NOT NULL AND i_cid = 1 THEN
    l_where := l_where || ' and a.cid is not null ';
  END IF;
  l_sql := l_sql || l_sql1 || l_where;
  dbms_output.put_line(l_sql);
  pcx_paging_query(l_code,
                   l_note,
                   o_result,
                   i_pageno,
                   i_pagelength,
                   i_totalrows,
                   sqls         => l_sql,
                   collist      => l_collist,
                   haswhere     => TRUE,
                   groupislast  => CASE
                                     WHEN l_sort IS NULL THEN
                                      FALSE
                                     ELSE
                                      TRUE
                                   END,
                   i_sort       => l_sort,
                   i_haswith    => FALSE,
                   i_recount    => TRUE);
END PTYZH_CXJTZHXX_XB;

二、过程示例解析

1、创建存储过程的语句

CREATE OR REPLACE PROCEDURE 存储过程名称(参数1 参数类型,参数2 参数类型,参数3 参数类型...)

AS关键字：
AS —- 存储过程语法的一部分，AS之前是存储过程参数和属性定义，AS后面表示存储过程内容的定义。

2、存储过程的执行语句

1、过程变量赋值

BEGIN关键字：作为过程开始执行的标志；
END关键字：过程结束执行的标志；

2、sql语句条件变量赋值

3、最终拼接完整的sql语句

“||”符号：字符串连接符号，相当于“+”，将两个字符或字符串连接起来。
常见其作用于过程变量前后。