ORACLE存储过程入门

一.定义

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。

二.优点

1.效率高
存储过程编译一次后，就会存到数据库，每次调用时都直接执行。而普通的sql语句我们要保存到其他地方（例如：记事本  上），都要先分析编译才会执行。所以想对而言存储过程效率更高。

2.降低网络流量
存储过程编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的sql语句。

3.复用性高
存储过程往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该存储过程。

4.可维护性高
当功能要求发生小的变化时，修改之前的存储过程比较容易，花费精力少。

5.安全性高
完成某个特定功能的存储过程一般只有特定的用户可以使用，具有使用身份限制，更安全。

三.存储过程结构

(1).基本结构

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常（可写可不写，要增强脚本的容错性和调试的方便性那就写上异常处理）

(2).无参存储过程

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

这里的as和is一样任选一个，在这里没有区别，其中demo是存储过程名称。

(3).有参存储过程

a.带参数的存储过程

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

上面脚本中，

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

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

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

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

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

上面脚本中：

其中参数IN表示输入参数，是参数的默认模式。

OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。

OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程。

IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。

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

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

第9—11行：做异常处理。

四.存储过程语法

(1).SELECT INTO STATEMENT语句

该语句将select到的结果赋值给一个或多个变量，例如：

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

上面脚本中：

存储过程名称：DEMO_CDD1, student是学生表，要求查出成绩为100分的那个学生的姓名，年龄，籍贯

(2).选择语句

a.IF..END IF

学生表的sex字段：1-男生；0-女生

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

b.IF..ELSE..END IF

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

(3).循环语句

a.基本循环

LOOP
  IF 表达式 THEN
    EXIT;
  END IF
END LOOP;

b.while循环

WHILE 表达式 LOOP
  dbms_output.put_line('haha');
END LOOP;

c.for循环

FOR a in 10 .. 20 LOOP
  dbms_output.put_line('value of a: ' || a);
END LOOP;

(4).游标

    Oracle会创建一个存储区域，被称为上下文区域，用于处理SQL语句，其中包含需要处理的语句，例如所有的信息，行数处理，等等。

    游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)由SQL语句返回。行集合光标保持的被称为活动集合。

a.下表是常用的游标属性：

属性	描述
%FOUND	如果DML语句执行后影响有数据被更新或DQL查到了结果，返回true。否则，返回false。
%NOTFOUND	如果DML语句执行后影响有数据被更新或DQL查到了结果，返回false。否则，返回true。
%ISOPEN	游标打开时返回true，反之，返回false。
%ROWCOUNT	返回DML执行后影响的行数。

b.使用游标

声明游标定义游标的名称和相关的SELECT语句：

CURSOR cur_cdd IS SELECT s_id, s_name FROM student;

打开游标游标分配内存，使得它准备取的SQL语句转换成它返回的行：

OPEN cur_cdd;

抓取游标中的数据，可用LIMIT关键字来限制条数，如果没有默认每次抓取一条：

FETCH cur_cdd INTO id, name ;

关闭游标来释放分配的内存:

CLOSE cur_cdd;

五. 调试

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

六.实例

场景：

有表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;

七.问题备忘

1.在oracle中，数据表别名不能加as，如：

select a.appname from appinfo a;-- 正确
select a.appname from appinfo as a;-- 错误

2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。

select af.keynode into kn from APPFOUNDATION af 
   where af.appid=aid and af.foundationid=fid;-- 有into，正确编译
select af.keynode from APPFOUNDATION af 
 where af.appid=aid and af.foundationid=fid;-- 没有into，编译报错，提示：Compilation 
 
Error: PLS-00428: an INTO clause is expected in this SELECT statement

3.在利用select…into…语法时，必须先确保数据库中有该条记录，否则会报出”no data found”异常。

可以在该语法之前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用select…into…

select af.keynode into kn from APPFOUNDATION af 
   where af.appid=aid and af.foundationid=fid;-- 有into，正确编译
select af.keynode from APPFOUNDATION af 
 where af.appid=aid and af.foundationid=fid;-- 没有into，编译报错，提示：Compilation 

Error: PLS-00428: an INTO clause is expected in this SELECT statement

4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

 --正确
select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;
 
--错误
select af.keynode into kn from APPFOUNDATION af 
 where af.appid=appid and af.foundationid=foundationid;
-- 运行阶段报错，提示ORA-01422:exact fetch returns more than requested number of rows

5.在存储过程中，关于出现null的问题 

假设有一个表A，定义如下：

create table A(
id varchar2(50) primary key not null,
vcount number(8) not null,
bid varchar2(50) not null -- 外键 
);

如果在存储过程中，使用如下语句：

select sum(vcount) into fcount from A where bid='xxxxxx';

如果A表中不存在bid=”xxxxxx”的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：

if fcount is null then
    fcount:=0;
end if;

这样就一切ok了。