一、存储过程和函数

　　存储过程: 对一个模块的封装
　　函数: 功能与存储过程几乎一样

　　区别:
　　　　函数必须通过return 关键字返回一个值
　　存储过程:
　　　　不需要return返回值

　　参数:
　　　　输入型参数
　　　　输出型参数
　　　　输入(输出)型参数

　　什么时候用存储过程, 什么时候用函数
　　　　一般来讲, 当只有一个返回值的时候用函数,
　　　　当没有返回值或者需要多个返回值的时候, 用存储过程

二.首先看一下SQL基础

先看要举例的表的内容：

下面开始操作：

首先建一个新的SQL Window：

DECLARE

  --声明变量

  /*

    引用型变量--当前用户下表中的某一列

    记录型变量--当前用户下表中的某一行

  */

  I NUMBER;

  A STUDENT.SNAME%TYPE; --引用型变量，指student表中的sname列

  B STUDENT%ROWTYPE; --记录型变量，当前用户下表中的某一行，根据附加条件选出是哪一行

BEGIN

  --开始

  I := 107;

  SELECT S.SNAME INTO A FROM STUDENT S WHERE S.SNO = I;

  DBMS_OUTPUT.PUT_LINE('查询结果，a的值为：' || A); --SOL中字符串的拼接用 ||

  SELECT * INTO B FROM STUDENT S WHERE S.SNO = I;

  DBMS_OUTPUT.PUT_LINE('查询结果，b的值为：' || B.SNAME); --通过b.~的形式把b中的某一列取出来

END; --结尾

三、游标（cursor）

学会使用游标遍历student表中的学生姓名

--游标（可以看作集合，里面装的是一个个记录型变量），有四个属性，

/*

ISOPEN --游标是否打开

NOTFOUND --bolean值，返回true 或者 false

FOUND --bolean值，返回true 或者 false

ROWCOUNT --已经取出的记录的行数,相当于改变几行就输出几

*/

DECLARE

  STU STUDENT%ROWTYPE;

  CURSOR STUS IS

    SELECT * FROM STUDENT;

BEGIN

  OPEN STUS;

  LOOP

    FETCH STUS

      INTO STU;

    EXIT WHEN STUS%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(STU.SNAME);

  END LOOP;

  CLOSE STUS;

END;

四、存储过程（procedure）

输入student表中的班级号，输出学生姓名

第一步：新建SQL Window，编译一段SQL语句，把编译好的语句放到了procedures文件目录下（这是一个查询的存储过程）

CREATE OR REPLACE PROCEDURE HANQI(SCLA IN NUMBER) AS

  CURSOR STUS IS

    SELECT * FROM STUDENT S WHERE S.CLASS = SCLA;

  STU STUDENT%ROWTYPE;

BEGIN

  OPEN STUS;

  LOOP

    FETCH STUS

      INTO STU;

    EXIT WHEN STUS%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(STU.SNAME);

  END LOOP;

  CLOSE STUS;

END;

第二步、重新建立一个Test Window，调用一下存储过程

-- Created on 2017/8/18 by ASUS

declare

  -- Local variables here

  i integer;

begin

  -- Test statements here

  hanqi(95031);--在Test窗口中，直接调用就可以了

end;

输出信息为：

第三、看一下命令窗口 Command Window，也可以调用存储过程

Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0

Connected as test@XE

SQL> select * from student;

SNO SNAME  SSEX SBIRTHDAY        CLASS

--- ------ ---- ----------- ----------

108 曾华   男   1977/9/1         95033

105 匡明   男   1975/10/2        95031

107 王丽   女   1976/1/23        95033

101 李军   男   1976/2/20        95033

109 王芳   男   1975/2/10        95031

103 陆君   男   1974/6/3         95031

6 rows selected

SQL> set serveroutput on;

SQL> execute hanqi(95031);

匡明

王芳

陆君

PL/SQL procedure successfully completed

五、函数（function）

函数的构造方法和存储过程类似

CREATE OR REPLACE FUNCTION CAL_ADD(A IN NUMBER, B IN NUMBER) RETURN NUMBER AS

  C NUMBER;

BEGIN

  C := A + B;

  RETURN C;

END;

直接调用函数：

六、输出型参数（可以用来当作一个存储过程的返回值，可以是一个，也可以是多个）

新建Sql Window：

CREATE OR REPLACE PROCEDURE HANQI3(SCLA IN NUMBER, VARI OUT NUMBER) AS

BEGIN

  UPDATE STUDENT S SET S.SSEX = '女' WHERE S.CLASS = SCLA;

  SELECT COUNT(*) INTO VARI FROM STUDENT S WHERE S.CLASS = SCLA;

END;

新建Test Window：

-- Created on 2017/8/18 by ASUS

DECLARE

  -- Local variables here

  I INTEGER;

BEGIN

  -- Test statements here

  HANQI3(95031, I);

  DBMS_OUTPUT.PUT_LINE('被修改的记录条数：' || I);--有输出型参数的得在Text Window下调用，并打印

END;

执行结果为：

可以通过输出型参数，让存储过程有返回值

巴特西

Oracle数据库基础--存储过程和函数