存储过程

公司的项目使用到了存储过程，由于此前在学校从未接触到，因此记录下学习笔记。

什么是存储过程

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

使用存储过程的优点

存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
存储过程可以重复使用,可减少数据库开发人员的工作量。
安全性高,可设定只有某些用户才具有对指定存储过程的使用权。

简单例子

基本语法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
--------------创建存储过程-----------------
CREATE [OR REPLACE] PROCEDURE procedure_name(参数列表) 
AS -- as不可以省略，也可以用is

--PL/SQL体部分

END procedure_name;

--------------调用存储过程-----------------

EXECUTE Procedure_name '' --存储过程如果有参数，后面加参数格式为：@参数名=value，也可直接为参数值value

--------------删除存储过程-----------------

drop procedure procedure_name    --在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程

存储过程或者存储函数，只能创建或者替换。
参数可以带也可以不带。

不带参数的存储过程

1
2
3
4
5
CREATE OR REPLACE
procedure test as
    begin
	    dbms_output.put_line('HelloWorld');
    end;

带参数的存储过程

给指定的员工涨100元工资,并且打印涨前和涨后的薪水:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
create or replace procedure test(pempno in emp.empno%type)
as
    pename emp.ename%type;
    beforesal emp.sal%type;
    aftersal emp.sal%type;
begin
    select ename,sal into pename,beforesal from emp where empno=pempno;
    aftersal:=beforesal+100;
    update emp set sal=aftersal where empno=pempno; 
    dbms_output.put_line('姓名: '||pename||' 涨前工资:'||beforesal||'涨后工资:'||aftersal);
end;

注意

要明确说明参数是输入参数（in)还是输出参数（out)
为保证调用多个存储过程中处在同一个事务中，所以一般不在存储过程或者存储函数中，commit或rollback

调用执行方式

exec命令 exec是SQLPlus命令，只能在SQLPlus中使用，exec可以直接跟存储过程名（可以省略括号）

1
2
SQL> set serveroutput on;
SQL> exec test;

使用call命令在SQL中使用call调用存储过程时，需要带上括号，不限制使用场景

1
2
SQL> set serveroutput on;
SQL> call test();

使用PLSQL语句调用

1
2
3
4
SQL> set serveroutput on;
SQL> begin
         test;
     end;