Oracle数据库知识——存储过程篇

一、存储过程是什么？

存储过程（Stored Procedure ）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。存储过程是由流控制和SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。在ORACLE 中，若干个有联系的过程可以组合在一起构成程序包。

简单来说：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

优点：

1、存储过程可以使得程序执行效率更高、安全性更好，因为过程建立之后 已经编译并且储存到数据库，直接写sql就需要先分析再执行因此过程效率更高，直接写sql语句会带来安全性问题，如：sql注入

2、建立过程不会很耗系统资源，因为过程只是在调用才执行。

3、存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量。

4、使用存储过程使你能够增强对执行计划的重复使用，由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能。RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。

5、可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。

6、代码精简一致，一个存储过程可以用于应用程序代码的不同位置。

7、更好的版本控制，通过使用 Microsoft Visual SourceSafe 或某个其他源代码控制工具，可以轻松地恢复到或引用旧版本的存储过程。

8、增强安全性：

    a、通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问；

    b、提高代码安全，防止 SQL注入（但未彻底解决，例如，将数据操作语言－－DML，附加到输入参数）；

    c、SqlParameter 类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证）。

缺点：

1、如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则你仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。

2.可移植性差 
由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在你的环境中非常重要，则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。

3、大量的利用过程，会对服务器压力比较大。

二、存储过程的使用

--创建存储过程

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

END 存储过程名字

--if else

1、只有一个if

    if ....then

    end if;

2、两种选择

    if .....then

    else

    ...

end if;

3、多种选择

    if .... then

    elsif....then

    else....end if;

注意多重判断中是elsif不是elseif

--代码没截取完全，只复制了当前用到的


 if v_isclx > 0 then
    select zt into v_zt from cl_clxda t where t.clxsbm = v_code;
    if v_zt = '1' then
      v_result := '你';
    elsif v_zt = '3' then
      v_result := '好';
      elsif v_zt = '4' then
      v_result := '吗';
    else

--变量赋值

    V_TEST := 123;

--带参数的cursor

 cursor kcxx is 
  select t.clbm wzbm,
	     t.clmc,
       t.ggxh,
       t.jldw,
       t.desx - t.dexx xqsl,
       t.dj,
       t.dj * (t.desx - t.dexx) je
  from (select t.clbm, count(clsl) total_ck from CL_KCMX t group by clbm) k,
       zd_clxx t
 where k.clbm = t.clbm
   and k.total_ck < t.dexx;
begin
  open kcxx;
  loop
    fetch kcxx
      into V_wzbm,V_clmc, V_ggxh, V_jldw, V_xqsl, V_dj,V_je;
    exit when kcxx%notfound;
  end loop;
  close kcxx;
  commit; 

--循环

    1、第一种循环

  FOR CUR_EMP_OBJ IN CURSOR_EMP LOOP
    DBMS_OUTPUT.PUT_LINE('循环的第一种写法-FOR IN:' || CUR_EMP_OBJ.EMPNO || ' ' ||
                         CUR_EMP_OBJ.ENAME);
  END LOOP;

    2、第二种循环

  OPEN CURSOR_EMP;
  LOOP
    FETCH CURSOR_EMP
      INTO CUR_EMP_OBJ;
    EXIT WHEN CURSOR_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('循环的第二种写法-FETCH INTO :' || CUR_EMP_OBJ.EMPNO || ' ' ||
                         CUR_EMP_OBJ.ENAME);
  END LOOP;
  CLOSE CURSOR_EMP;

三、实例

create or replace procedure PRC_CREATE_XQDH(v_user in varchar2) is
  V_djbh   varchar2(20);
	V_wzbm   varchar2(20);
	V_clmc   varchar2(20);
	V_ggxh   varchar2(20);
	V_jldw   varchar2(20);
	V_xqsl   number;
	V_dj     number;
	V_je     number;
  cursor kcxx is 
  select t.clbm wzbm,
	     t.clmc,
       t.ggxh,
       t.jldw,
       t.desx - t.dexx xqsl,
       t.dj,
       t.dj * (t.desx - t.dexx) je
  from (select t.clbm, count(clsl) total_ck from CL_KCMX t group by clbm) k,
       zd_clxx t
 where k.clbm = t.clbm
   and k.total_ck < t.dexx;
begin
  open kcxx;
  loop
    fetch kcxx
      into V_wzbm,V_clmc, V_ggxh, V_jldw, V_xqsl, V_dj,V_je;
    exit when kcxx%notfound; 
		
	  V_djbh := F_BMSCQ('', '', '', '', 'DJBH');
  
		insert into CL_JCWZXQD
    (djbh, bzrq, bzr, zt, spr, spsj, zfr, zfsj)
  values
    (V_djbh,
     sysdate,
     v_user,
     '01',
     v_user,
     sysdate,
     v_user,
     sysdate);
     
		 insert into CL_JCWZXQMX
    (id,djbh, wzbm,clmc, jldw, ggxh, xqsl, dj, je)
  values
    (sys_guid(),
		 V_djbh,
     V_wzbm,
		 V_clmc,
     V_jldw,
     V_ggxh,
     V_xqsl,
     V_dj,
     V_je);
    
  end loop;
  close kcxx;
  commit;
  
end PRC_CREATE_XQDH;

四、java代码调用

    @Override
	public String bzxqd(final String userName) {
		// TODO Auto-generated method stub
		final String sql = "call clps.PRC_CREATE_XQDH(?)";
		final StringBuffer res = new StringBuffer();
		this.getSession().doWork(new Work() {

			@Override
			public void execute(Connection conn) throws SQLException {
				// TODO Auto-generated method stub
				CallableStatement cs = conn.prepareCall(sql);
				cs.setObject(1, userName);
				cs.execute();
				cs.close();
			}
		});
		return res.toString();
	}