存储过程

简介

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，中间再加点逻辑控制。

优点：

1.存储过程只在创建时编译一次，而程序中的sql语句调用都要重新编译（不绝对），可提高执行速度

2.对数据库进行复杂操作时，存储过程将多条语句组合起来，因此程序只需进行一次数据库连接，节省时间，同时也可与数据库提供的事务处理结合使用

3.存储过程可重复使用，减少数据库开发人员的工作量

4.安全性高，可设定用户使用权限

缺点：

1.调试麻烦，不过可以使用plsql进行调试，还算方便

2.如果一个项目中大量使用存储过程，而一旦数据结构发生变化，那维护起来就非常麻烦

 

调用与测试

可以用call 存储过程名字（参数..）或者exec 存储过程名字（参数..），来对存储过程进行调用。如果存储过程没有参数调用时也需要在最后加上括号‘（）’；

在plsql中找到要测试的存储过程，右键-》测试，即可进行测试。在输入参数值后，可进行单步调试，最后可得到返回值。同时可在存储过程中加入DBMS_OUTPUT.PUT_LINE();语句打印信息。

 

基本语法

1.基本结构

CREATE OR REPLACE PROCEDURE  存储过程名字
//创建或替换已有存储过程，PROCEDURE可缩写为PROC
(
 参数1 IN NUMBER,
 返回值 OUT VARCHAR2
)
//定义参数及返回值，in表示参数，out表示返回值，后面跟的类型可以是oracle中任意合法类型，且
不带取值范围。如果没有参数与返回值，则连外面的括号一起省略
IS
//IS关键字 也可用AS
变量1 NUMBER;
变量2 VARCHAR2(100) := 'ALL';
//在这个位置定义变量，变量定义的类型带取值范围，后面带封号。给变量赋值用:=符号
BEGIN
//BEGIN关键字表示sql开始
NULL
//这部分就是sql语句主体，如果什么都没有至少写个NULL才行
EXCEPTION
//EXCEPTION关键字表示异常处理的开始，可以在语句中用RAISE关键字显式抛出异常
WHEN 异常名称 THEN
 DO SOMETHING
//捕获特定异常并进行相应处理 这个语句可以有多个
WHEN OTHERS THEN
 DO SOMETHING
//捕获其他异常
END;
//END关键字表示sql结束

 

 2.SELECT INTO STATEMENT

将select查询结果存入到变量中：SELECT  COL1,COL2 INTO 变量1，变量2 FROM TABLE_NAME;

可以同时存多个变量，但是注意如果没有记录，将抛出NO_DATA_FOUND异常；

另外可以用%rowtype定义一个跟表相同结构的变量，然后查出一条记录存入

 

3.IF

IF V_TEST IS NULL THEN

do something

ELSIF V_NUM = 1 THEN

do something

ELSE

do something

END IF;

 

4.WHILE

WHILE V_TEST = 1 LOOP

do something

END LOOP;

 

5.FOR IN 使用CURSOR

CURSOR cur IS SELECT * FROM TABLE_NAME;

//游标定义在is后面也就是定义变量的地方

FOR cur_result IN cur LOOP

do something

END LOOP;

 

6.带参数的游标

CURSOR c_user(c_id NUMBER) IS SELECT NAME FROM USER WHERE ID = c_id

//同意定义在is后

OPEN c_user（变量值）

LOOP

FETCH c_user INTO V_NAME;

EXIT FETCH c_user%NOTFOUND;

do somthing

END LOOP;

CLOSE c_user;

 

7.动态SQL

execute immediate  动态SQL语句 using 参数 returing into 返回值对象

如：execute immediate 'select name from person where id = :1 and sex = :2'  using p_id,p_sex returning into v_name;

这里在动态语句中:1表示第一个参数位置，p_id就是参数值，然后将查询语句的返回值赋值给v_name变量

 

另外如果动态语句是调用一个存储过程，且存储过程有返回值就要指明参数类型为out

如：execute immediate 'proc_get_name(:1,:2)'  using in v_id,out v_name;

这里的存储过程参数1是，2是返回值