Oracle存储过程（10 级学员 庞丽课堂总结）

Oracle存储过程

         今天我们学习了存储过程和存储函数也是一种PL/SQL块，是存入数据库的PL/SQL块，它和PL/SQL程序相比起来，

一、存储过程和它有很多优点，具体归纳一下，如下：

（1）       存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码。

（2）       存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数。

（3）       存储过程和函数的信息是写入数据字典的，所以存储过程可以看做是一个公用模块，用户编写的PL/SQL程序或其他存储过程都可以调用它（但存储过程和函数不能调用PL/SQL程序）。一个重复使用的功能，可以设计成为存储过程，比如：显示一张工作统计表，可以设计成为存储过程；一个经常调用的计算，可以设计成为存数函数；根据雇员编号返回雇员的姓名，可以设计成存储函数。

（4）       像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递也有多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名的调用返回函数值。

（5）       存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能调用。      

二、存储过程的建立及执行

         创建存储过程的基本语句：

（1）       用create producedure命令建立存储过程和存储函数。

（2）       语法：

Create [or replace] producedure过程名（参数列表）as

                     PL/SQL子程序体;

三、调用存储过程的方法

         （1）方法一：

                            Execute 模式名.存储过程名[(参数…)];

（3）       方法二：

Begin

         模式名.存储过程名[(参数…)];

End;

传递的参数必须与定义的参数类型、个数和顺序一致（如果参数定义了默认值，则调用时可以省略参数）。参数可以是变量、常量。

如果是调用本账户下的存储过程，则模式名可以省略。要调用其他账户编写的存储过程，则模式名必须要添加。

四、存储过程的实例：

（1）题目：为指定的职员在原工资的基础上涨10%的工资

         create or replace procedure updateSal(emp_no number)

as

 ---declare声明

 emp_sal emp.sal%type;

--开始

Begin

         --查询该员工的工资

   select sal into emp_sal from emp where empno=emp_no;

         --给该员工涨工资

   update emp set sal = emp_sal*1.1 where empno=emp_no;

--结束标识

 end;

 （2）在Java程序中使用存储过程

         代码如下：

首先我们来创建一个包体，

createorreplacepackage emppackage is

  -- Author  : ADMINISTRATOR

  -- Created : 2012-3-22 下午 02:05:43

  -- Purpose :

  -- Public type declarations

  type empcursor isrefcursor;

  -- Public function and procedure declarations

  procedure selectEmps(emplist out empcursor);

end emppackage;

并且要实现这个包体。

Java代码如下：

publicstaticvoid testProcedures() throws SQLException{

       //参数 in out 参数     通过占位符

       CallableStatement cas = conn.prepareCall("{call emppackage.selectEmps(?)}");

       //从1开始

       int index=1;

       //为占位符赋值

       //输出参数  cas.registerOutParameter(index++,oracle.jdbc.OracleTypes.CURSOR);

       boolean flag = cas.execute();

       System.out.println(flag);

       //OracleCallableStatement强制造型为oracle中的存储过程的对象

       //调用里边的getCursor()方法获取返回的ResultSet的结果集

       ResultSet rs = ((OracleCallableStatement)cas).getCursor(1);

       while(rs.next()){

           System.out.println(rs.getInt(1));

       }

 

五、带参数的存储过程

         参数的作用是向存储过程传递数据，或从存储过程获得返回结果。正确的使用参数可以大大增加存储过程的灵活性和通用性。

         参数的类型有三种，如下所示：

                   In  定义一个输入参数变量，用于传递参数给存储过程。

                   OUT   定义一个输出参数变量，用于从存储过程获取数据。

                   IN OUT    定义一个输入、输出参数变量，兼有以上两者的功能。

 参数的定义形式和作业如下：

                   参数名   in   数据类型 default值

                   参数名   out  数据类型

                   参数名  inout   数据类型  default值

六、删除存储过程

         一个存储过程在不需要时可以删除。删除存储过程的人是过程的创建者或者拥有drop any procedure系统权限的人。

         删除存储过程的语法如下：

                   Drop procedure  存储过程名;