Oracle存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

优点：
1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)， 可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量
4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

缺点
1：调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
2：移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
3：重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
4： 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

存储过程使用有感：
1.封装在数据库中的pl/sql 可以像pl/sql一样被调用
2.可以通过添加或者修改参数 来达到动态的使用

实例一：
create or replace procedure pro_wyh(zhuJian_ in varchar2,restr out varchar2) as
–in代表是传入参数 varchar2是传入的参数的类型但是此时在括号内部是不用给出类型的长度的，out代表是出参
zhuJian varchar2(32) := zhuJian_; –将传入的参数通过赋值的形式传递给zhuanJian 这个参数
shuLiang number := 0; –此时自定义一个参数shuLiang并为其初始化赋值给0
begin
select count(*)into shuLiang from T_USER where USER_ID=zhuJian; –将查询后的结果用into装入到shuLiang 这个参数中
shuLiang := shuLiang + 1; –对数量进行加一

restr:=shuLiang –’ 是加一后的结果’;
end; –结束

–注意：此时并没有declare这个关键字

–调用的方法一：这是在pl/sql中的调用

declare
  v_restr varchar2(40);
begin
  pro_wyh('2',v_restr);
   dbms_output.put_line(v_restr);
end;

–调用的方法二：用java代码调用

package com.turing.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;



public class wyh {
@SuppressWarnings("unused")
public static void main(String[] args) {


  // 1.加载mysql驱动程序
  try {
        Class.forName("oracle.jdbc.driver.OracleDriver");

    // 2.指定url
    Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:PPMSDB", "ppmsdb", "ppmsdb"); 
    if (!con.isClosed()) {
        System.out.println("连接成功");
    }   


     CallableStatement cst = con.prepareCall("{call pro_wyh( ?,? ) }"); 
     cst.setString(1, "2"); //1 代表第一个问号 "2"代表是给第一个问号赋值 也就是传入的主键值
     cst.registerOutParameter(2, java.sql.Types.VARCHAR); // 2代表第二个参数 ，java.sql.Types.VARCHAR代表是参数的类型 因为咱们的本条存储过程的出参是varchar2也就是相当于java中的String

     cst.execute(); //执行此存储过程

     System.out.println(cst.getString(2)); //将执行完的结果 也就第二个问号 的值给打印出来

     cst.close(); 
     con.close();
    } 
     catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
}
}

实例二：

create or replace procedure pro_wyh002(biaoMing_ in varchar2,restr out varchar2) as    --in代表是传入参数  varchar2是传入的参数的类型但是此时在括号内部是不用给出类型的长度的，out代表是出参 
 biaoMing varchar2(32) := biaoMing_;--将传入的参数通过赋值的形式传递给zhuanJian 这个参数
 shuLiang number := 0;                --此时自定义一个参数shuLiang并为其初始化赋值给0

 v_sql varchar2(4000);
begin


  v_sql := 'select count(*)  from '||biaoMing||'';
  execute immediate v_sql into shuLiang;  
  shuLiang := shuLiang + 1;


 restr:=shuLiang||'是总共有多少条';
end;    --结束

--注意：此时并没有declare这个关键字

调用的方法一：这是在pl/sql中的调用

declare
  v_restr varchar2(40);
begin
  pro_wyh002('T_user',v_restr);
   dbms_output.put_line(v_restr);
end;

实例三：

create or replace procedure pro_0001_demo3(p_name in varchar2,p_sql out varchar2) as
   v_nld varchar2(100):=p_name;
   v_temp_sql varchar2(1000);
   v_count number(10);
   v_pos1 varchar2(40);
   v_pos2 varchar2(40);
   v_row2colstr varchar2(100);
   v_total_sql varchar2(4000);
begin
   v_nld := '0,'||v_nld||',999';
   --'0,40,45,55,60,999'
   v_count := length(regexp_replace(v_nld,'[0-9]+'));

   for i in 1..v_count loop
      v_pos1 := regexp_substr(v_nld,'[0-9]+',1,i);
      v_pos2 := regexp_substr(v_nld,'[0-9]+',1,i+1);

      v_row2colstr := 'sum(decode(sign(nl-'||v_pos1||')+sign(nl-'||v_pos2||'),0,1,0)) d'||i||',';
      --dbms_output.put_line(v_row2colstr);
      v_temp_sql :=  v_temp_sql||v_row2colstr;
   end loop;

  v_total_sql :=
'select deptname,count(ryzt) z0,
'||v_temp_sql||'
    sum(decode(ryzt,''1'',1,0)) zt1,
    sum(decode(ryzt,''2'',1,0)) zt2,
    sum(decode(ryzt,''3'',1,0)) zt3,
    sum(decode(ryzt,''4'',1,0)) zt4,
    sum(decode(ryzt,''5'',1,0)) zt5,
    sum(decode(ryzt,''6'',1,0)) zt6 from
  (select months_between(sysdate ,to_date(csrq,''yyyy-mm-dd''))/12 nl,d.deptid,d.deptno,d.deptname,ryzt
  from gsbx_dwxxb d left join gsbx_ryxxb r on d.deptid=r.deptid
)
  group by deptid,deptno,deptname';

  p_sql:=v_total_sql;

end;

实例四：

create or replace procedure pro_0001_demo4(p_ages in varchar2,p_th out varchar2) as

  p varchar2(999):= p_ages;
  v_main_th varchar2(9999);
  v_shi number;
  form1 varchar2(99) :='<td align="center" class="td_title">';
  to1 varchar2(99) :='</td>';
  v_start varchar2(99);
  v_end varchar2(99);
begin
  p := '0,'||p||',999';
  v_shi  := length(regexp_replace(p,'\d',''));

  v_main_th := '<tr align="center" >
    <td class="td_title" align="center" rowspan="2">序号</td>
    <td class="td_title" align="center" rowspan="2">单位名称</td>
    <td class="td_title" align="center" rowspan="2">总人数</td>
    <td class="td_title" align="center" colspan="'||v_shi||'">按年龄段统计正常状态参保人数</td>
    <td class="td_title" align="center" colspan="5">按人员状态统计参保人数</td>
  </tr>
  <tr align="center" >';

  for i in 1..v_shi loop
     v_start := regexp_substr(p,'[0-9]+',1,i);
     v_end := regexp_substr(p,'[0-9]+',1,i+1);
     if i = 1 then
        v_main_th := v_main_th|| form1||v_end||'以下'||to1||chr(10);
     elsif i = v_shi then
        v_main_th := v_main_th|| form1||v_start||'以上'||to1||chr(10);
     else
        v_main_th := v_main_th|| form1||v_start||'-'||v_end||to1||chr(10);
     end if;
  end loop;

  v_main_th := v_main_th||'<td class="td_title" align="center">正常</td>
    <td class="td_title" align="center">解除</td>
    <td class="td_title" align="center">调出</td>
    <td class="td_title" align="center">退休</td>
    <td class="td_title" align="center">删除</td>
  </tr>';
  p_th := v_main_th;

end;

实例五：

       pro_0001_ages2page(p_ages in varchar2,
                         p_from in number,
                         p_to in number,
                         p_rowcount out number,
                         p_list out sys_refcursor,
                         p_html out varchar2) as
   v_main_sql varchar2(4000);
   v_paged_str1 varchar2(100):='select * from (select rownum rn,innert.* from (';
   v_paged_str2 varchar2(100):=')innert where rownum <='||p_to||') where rn >'||p_from||'';

begin
   pro_0001_demo3(p_ages,v_main_sql);
   --拼接主SQL
   execute immediate 'select count(*) from ('||v_main_sql||')' into p_rowcount;
   --查询分页总条数
   open p_list for v_paged_str1||v_main_sql||v_paged_str2;
   --查询分页内容
   pro_0001_demo4(p_ages,p_html);
   --拼接HTML
end;

实例六：

create or replace procedure pro_0001_demo1(p_name in varchar2,p_next_id out varchar2) as
   v_max_id varchar2(40);
   v_next_id varchar2(40);
   v_left varchar2(40);
   v_right varchar2(40);
   v_len varchar2(40);
   v_tbname varchar2(40):= trim(p_name);
   v_sql varchar2(4000);
begin
   v_sql := 'select max(ct_id)  from '||v_tbname||'';
     execute immediate v_sql into v_max_id;    ---简单来说 就是你一个存储过程当中 创建了一个表 table_a 然后要用insert into将其他的数据插入到这个table_a当中，但是因为你在创建过程的时候 table_a还不存在，过程就会显示有编译错误，因为table_a不存在必然导致过程无法执行，所以无法编译成功，而把insert into语句加如到 execute immediate之后 则oracle不会再去理会这个对象是否存在，因此可以成功编译和执行。
     v_left := regexp_substr(v_max_id,'[A-Za-z]+',1,1);
     v_right := regexp_substr(v_max_id,'[0-9]+',1,1);
     v_len := length(v_right);
     v_right := v_right + 1;
     v_right := lpad(v_right,v_len,'0');
     v_next_id := v_left||v_right;
     p_next_id:=v_next_id;
end;