一、什么是存储过程?

    存储过程就是一组为了完成特定功能的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;