Oracle 存储过程

定义

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

优 点

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

语法

1.基本结构

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

END 存储过程名字;

IN 表示输入参数，OUT表示输出参数，IN OUT表示既是输入参数，也是输出参数；其实输出参数相当于返回值。
例：
创建存储过程：

CREATE OR REPLACE PROCEDURE P_TEST2(PARA1 IN INTEGER,       --输入参数
                                    PARA2 OUT INTEGER,      --输出参数
                                    PARA3 IN OUT INTEGER) IS--输入输出参数
  PAR1 INTEGER; --自定义变量
BEGIN
  PAR1  := PARA1;    --将输入参数PARA1赋值给PAR1
  PARA2 := PARA1;    --将输入参数PARA1赋值给输入参数PARA2
  PARA3 := PARA3 + 1;--将输入输出参数PARA3自加1
  DBMS_OUTPUT.PUT_LINE('PAR1:' || PAR1); --打印PAR1
END;

执行存储过程P_TEST2：

DECLARE
  PARA2 INTEGER;      --声明变量
  PARA3 INTEGER := 20;--声明变量并赋值
BEGIN
  P_TEST2(1, PARA2, PARA3);
  DBMS_OUTPUT.PUT_LINE('PARA2:'||PARA2);--打印P_TEST2的输出参数PARA2
  DBMS_OUTPUT.PUT_LINE('PARA3:'||PARA3);--打印P_TEST2的输入输出参数PARA3
END;

执行结果：

2.SELECT INTO STATEMENT

将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)。

例：
建立TEST表：

CREATE TABLE TEST (ID INT,NAME VARCHAR2(10),GRADE INT);
INSERT INTO TEST (ID, NAME, GRADE) VALUES (1, '张三', 80);
INSERT INTO TEST (ID, NAME, GRADE) VALUES (2, '李四', 90);
INSERT INTO TEST (ID, NAME, GRADE) VALUES (3, '王五', 70);
COMMIT;

创建存储过程计算平均成绩：

CREATE OR REPLACE PROCEDURE P_TEST IS 
  AVE_GRADE TEST.GRADE%TYPE;--声明AVE_GRADE的类型为TEST表的GRADE字段的类型
BEGIN
  SELECT AVG(GRADE) INTO AVE_GRADE FROM TEST;--将统计到的平均成绩赋值给AVE_GRADE
  DBMS_OUTPUT.PUT_LINE('AVE_GRADE:'||AVE_GRADE);
END;

执行该存储过程：

BEGIN
  P_TEST();
END;

结果：

3.IF判断

  IF V_TEST=1 THEN
    BEGIN 
       do something
    END;
  END IF;

4.WHILE循环

WHILE V_TEST=1 LOOP
    BEGIN
      do something
    END;
  END LOOP;

5.FOR循环

  FOR ... IN ... LOOP
    do something
  END LOOP;

6.用pl/sql developer debug

连接数据库后建立一个Test WINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试