Oracle的存储过程

　　一、什么是存储过程？

　　　　存储过程就是一组为了完成特定功能的SQL语句集，存储在数据库中；这样经过第一次编译后再次调用不需要再次编译，直接调用或者通过java掉用（就是个SQL语句集）

　　　　在Oracle中存储过程是procedure

　　　　优势：

　　　　　　1. 相比普通的sql语句，每次都要先编译在执行，相对而言存储过程效率更高

　　　　　　2. 降低网络流量（存储过程编译好后直接存在数据库中，远程调用时，不会传输大量的字符串类型的sql语句）

　　　　　　3. 复用性高：一次编译后，以后直接调用

　　　　　　4. 可维护性更高：修改比较容易

　　　　　　5. 安全性高：可以指定用户进行存储过程的调用

　　二、存储过程的创建方式：

　　2.1 无参

CREATE OR REPLACE PROCEDURE 存储过程名称
AS/IS
    变量2 DATE;
    变量3 NUMBER;
BEGIN
    --要处理的业务逻辑
    EXCEPTION    --存储过程异常（可写可不写）
END

　　2.2 有参

　　2.2.1 带参数的存储过程（输入参数：id  ； 输出参数：name）

1 CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE)
2 AS/IS
3 name student.name%TYPE;
4 age number :=20;
5 BEGIN
6   --业务处理.....
7 END

上面脚本中，

第1行：param1 是参数，类型和student表id字段的类型一样。

第3行：声明变量name，类型是student表name字段的类型（同上）。

第4行：声明变量age，类型数数字，初始化为20

　　

　　2.2.2 带参数的存储过程并且进行赋值

 1 CREATE OR REPLACE PROCEDURE 存储过程名称(
 2        s_no in varchar,
 3        s_name out varchar,
 4        s_age number) AS
 5 total NUMBER := 0;
 6 BEGIN
 7   SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
 8   dbms_output.put_line('符合该年龄的学生有'||total||'人');
 9   EXCEPTION
10     WHEN too_many_rows THEN 
11     DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
12 END

上面脚本中：

其中参数IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去

第7行：查询语句，把参数s_age作为过滤条件，INTO关键字，把查到的结果赋给total变量。

第8行：输出查询结果，在数据库中“||”用来连接字符串

第9—11行：做异常处理

 

　　三、存储过程的语法

　　3.1 将结果放入一个或多个变量中：

 1 CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS
 2 s_name VARCHAR2;   --学生名称
 3 s_age NUMBER;      --学生年龄
 4 s_address VARCHAR2; --学生籍贯
 5 BEGIN
 6   --给单个变量赋值
 7   SELECT student_address INTO s_address
 8   FROM student where student_grade=100;
 9    --给多个变量赋值
10   SELECT student_name,student_age INTO s_name,s_age
11   FROM student where student_grade=100;
12   --输出成绩为100分的那个学生信息
13   dbms_output.put_line('姓名：'||s_name||',年龄：'||s_age||',籍贯：'||s_address);
14 END

　　3.2 选择语句：

IF s_sex=1 THEN
  dbms_output.put_line('这个学生是男生');
END IF


IF s_sex=1 THEN
  dbms_output.put_line('这个学生是男生');
ELSE
  dbms_output.put_line('这个学生是女生');
END IF

　　3.3 循环语句

 1 -- 基本循环
 2 LOOP
 3   IF 表达式 THEN
 4     EXIT;
 5   END IF
 6 END LOOP;
 7 
 8 -- while循环
 9 WHILE 表达式 LOOP
10   dbms_output.put_line('haha');
11 END LOOP;
12 
13 -- for循环
14 FOR a in 10 .. 20 LOOP
15   dbms_output.put_line('value of a: ' || a);
16 END LOOP;

 

练习：

有表student(s_no, s_name, s_age, s_grade)，其中s_no-学号，也是主键，是从1开始向上排的（例如：第一个学生学号是1，第二个是2，一次类推）；s_name-学生姓名；s_age-学生年龄；s_grade-年级；这张表的数据量有几千万甚至上亿。一个学年结束了，我要让这些学生全部升一年级，即，让s_grade字段加1。
这条sql，写出来如下：

update student set s_grade=s_grade+1

分析：

如果我们直接运行运行这条sql，因数据量太大会把数据库undo表空间撑爆，从而发生异常。那我们来写个存储过程，进行批量更新，我们每10万条提交一次。

CREATE OR REPLACE PROCEDURE process_student is
total NUMBER := 0;
i NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student;
  WHILE i<=total LOOP
    UPDATE student SET grade=grade+1 WHERE s_no=i;
    i := i + 1;
    IF i >= 100000 THEN
      COMMIT;
    END IF;
  END LOOP;
  dbms_output.put_line('finished!');
END;