Oracle之存储过程

PL/SQL块：

     PL/SQL块都用于解决特定问题，PL/SQL块可以有参数，也可以相互调用。

     PL/SQL块可以作为模式对象在Oracle数据库中保存，根据用途可以分为两类：

         1、存储过程：用于特定目的，执行一系列操作的PL/SQL块

         2、自定义函数：通过计算返回调用者一个值的PL/SQL块

存储过程：

    存储过程是由流控制和SQL语句组成的，经编译和优化后存储在数据库服务器中，使用时只需调用即可。

    

    存储过程类似于高级程序设计语言中模块的概念，它将一些内部联系的命令组成一个个存储过程，通过参数在存储过程之间传递数据是模块化设计思想的主要内容。

    SQL语句执行的时候要先编译，然后执行。存储过程就是编译好了的一些SQL语句。可以直接执行，所以执行效率较高。

存储过程的优点： 

     1、存储过程大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

     2、可保证数据的安全性和完整性：

                  a：通过存储过程可以使没有使用权限的用户间接地存取数据，从而保证数据的完全。

                   b : 通过存储过程可以使相关的动作在一起发生，从而维护数据库的完整性。

     3、在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案，这种已经编译好的存储过程极大地改善SQL语句的性能。

     4、使用存储过程可以降低网络的通信量。

     5、使体现应用需要的运算程序放入数据库服务器中，以便集中控制。

存储过程定义：

 存储过程的创建：

    1、创建存储过程的语法结构，完整的存储过程结构如下：

            create [or replace]  procedure  存储过程名称

             as             声明语句     

             begin       执行语句段；

                            exception

                            异常处理语句段

             end；

    2、存储过程示例：

        create  or  replace  procedure  scott.tempprocedure   as

            tempdate  scott.emp.hiredate%type;

        begin   select  hiredate  into tempdate

                       from  scott.emp

                       where   scott.emp.SAL =  1500;

                       dbms_output.put_line('存储过程生成的变量值：'||tempdate);

        end;

 

存储过程的修改：

     存储过程的修改有两个方法：

        方法一：如果以前已经写了存储过程的全部或部分，现在需要修改或者继续完成，可以用SLQPlus  Worksheet打开以前保存的文件，方法是在SLQPlus  Worksheet的菜单栏选择“文件”-->“ 打开”命令，将创建存储过程的SQL文件调出进行修改，修改完毕后保存并重新执行创建存储过程即可。

         方法二：1、在企业管理器里选中要修改的存储过程，用鼠标右键点击，在出现的快捷菜单里选择“查看/编辑详细资料”菜单项；2、在弹出“编辑过程”窗口中的文本域中修改存储过程之后点击窗口中的“应用”按钮即可完成对存储过程的修改。

存储过程的执行：

    创建存储过程的最终目的是为了执行，所以创建存储过程后，需要调用它，才会让它执行。要执行创建的存储过程，必须通过主程序来调用存储过程，如果我们要调用前面写好的存储过程，可以在SQLPlus Worksheet中执行下列PL/SQL程序：

    

带参数的存储过程：

    创建带参数的存储过程的语法如下：

        create  [or replace]  procedure   过程名

            参数1  [in|out|in out]  数据类型

             [,参数2  [in|out|in out]  数据类型] ...

               {is|as}  pl/sql  语句

    1、参数类型

          在PL/SQL存储过程中，可以有3种类型的参数，其功能简要概述如下：

            in参数：读入参数，主程序向存储过程传递参数。

            out参数：读出参数，存储过程向主程序传递参数值。    

            in  out参数：双向参数，存储过程与主程序双向交流数据。

        定义带参数的存储过程：

                create  or  replace  procedure  scott.tempprocedure1(

                            tempdeptno   in   scott.dept.deptno%type,

                            tempdname    out   scott.dept.dname%type,

                            temploc           in out   scott.dept.loc%type)

                as        loc1  scott.dept.loc%type;

                            dname1  scott.dept.dname%type;

                begin   select  loc  into loc1 

                             from  scott.dept

                             where deptno=tempdeptno;

                            select dname into  dname1

                             from  scott.dept

                             where   deptno = tempdeptno;

                             temploc:='地址：'||loc1;

                             tempdname:='姓名'||dname1;

                   end；

该存储过程的功能是：从数据表scott.dept中寻找deptno字段等于一个变量（即调用参数，这里我们命名为tempdeptno的dname和loc字段，并将这两个字段的值赋给新的变量）

带参数的存储过程的调用：

       在主程序中的实际参数和过程中的形式参数的传递有很多中方法，这里推荐采取——对应的办法，按对应的位置传递参数。要求实际参数和形式参数在数据类型和位置排列上做到完全一致。

         set  serveroutput  on

         declare  myno  scott.dept.deptno%type;

                       mydname  scott.dept.dname%type;

                       myloc   scott.dept.loc%type;

           begin   myno:=10;

                       mydname:='';

                       myloc:='';

                       scott.tempprocedure1(myno,myname,myloc);

                         dbms_output.put_line(myno);

                         dbms_output.put_line(myname);

                         dbms_output.put_line(myloc);

        end;

自定义函数：

         创建自定义函数语法如下：

             create  or  replace  function  函数名（参数名    参数类型，......）

                  return  返回值类型

              is

                  begin

                    ......

                  end   函数名；

例：

         create  or replace  function 

                get_sal(empname  in  varchar2)  return

                number  is

                    Result   number;

                    begin

                    select sal  into  Result  from  emp where

                  ename  = empname;

                    return(Result);

                   end  get_sal;

        执行：SQL>var sal  number

                  SQL>exec:sal:=get_sal('scott');

使用包：

        包可以看做是过程和函数的集合，对过程和函数进行更好的封装。

       包的作用：包可以将任何出现在块声明的语句（过程，函数，游标，类型，变量）放于包中，相当于一个容器。将声明语句放入包中的好处是：用户可以从其他PL/SQL块中对其进行引用，因此包为PL/SQL提供了全程变量。

        包的包括包头和包体两部分，需要分别定义。

    定义包头：

        create  or  replace  package   包名

                    is      

                        过程名/函数名；        --申明了该包中的过程及函数

        end  包名；

    定义包体：

          create  or  replace  package  body  包名

                        is

                            过程/函数定义实现；

                end   包名；

    例：

            创建包头：

                create  or  replace package  fitpackage

                        is

                            procedure  myproc1(num in number);

                            procedure  myproc2;

                            function  myfunc(stuno  varchar2)  return  number;

                      end     fitpackage;

            创建包体：包名以及包内的函数，过程名以及参数列表都要和包头定义中的内容一致

                create  or   replace  package  body   fitpackage

                    is

                        procedure  myproc1(num in number)

                            as 

                            ......

                            end    myproc1;

                        procedure  myproc2

                            as

                            ......

                            end  myproc2;

                        function  myfunc(stuno  varchar2)

                            return   number

                            as

                             ......

                            end   myfunc;

                        end fitpackage;

JAVA程序调用存储过程：

    JDBC通过CallableStatement接口提供对此类功能的支持。可以通过Connection对象的prepareCall()方法来，该方法的参数是一个String，是一种SQL转义语法：

    例如：

    

过程的参数：

    in参数的占位符为？，使用CallableStatement接口的setXXX（）方法来为参数设置值。

   out参数的占位符为？，使用的时候必须先使用CallableStatement接口的registerOutParameter()方法注册此参数，最后通过getXXX()方法来取值。

    对于存储过程的返回值，除索引号为1外，其他与out参数用法一样。

    

带有输出参数：

    创建存储过程：

    create or replace procedure P_myPro_ConnOracle(empId in int,empName out String)

      as 

        begin

            select e.ename

            into empName

            from emp  e

            where  e.empno = empId;

            dbms_output.put_line('empId='||empId);

            dbms_output.put_line('empName='||empName);

        end;

    JDBC操作：

        //获得CallableStatement对象

            CallableStatement ctmt = conn.prepareCall("{call proc_forvalue(?,?)}");

       //设置输入参数

        ctmt.setString(1,type);

       //注册输出参数及数据类型

        ctmt.registerOutParameter(2,java.sql.Types.INTEGER);

       //调用存储过程，并查询输出参数的值

        ctmt.execute();

        result = ctmt.getInt(2);

使用游标返回结果集：

    创建存储过程：

        create or replace  package my_package

        as

        type my_cursor is ref cursor;

        end my_package;

        create or replace procedure my_test(test_result

                out  my_pakcage.my_cursor)

        as

        begin

        open  test_result  for

        select * from  dept;

        end;

使用游标返回结果集：

    JDBC操作:

           //获得CallableStatement对象

            CallableStatement proc= conn.prepareCall("{call my_test(?)}");

       //设置输入参数

        proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

       //调用存储过程，并获得第一个产生的结果集

        proc.execute();

        rs = (ResultSet)proc.getObject(1);//1指的是参数1

                while(rs.next()){

                System.out.println(rs.getString(1)+","+rs.getString(2));

        }