ORACLE-存储过程

概念

存储过程(Stored Procedure)：

一组为了完成某功能的一段sql语句集（一种PL/SQL块），存储在数据库中，经过第一次编译后再次调用就无需再次编译，用户通过调用指定存储过程名称和给定参数来调用。当数据库启动时,会自动加载到数据库内存中,执行效率高。

当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

函数和存储过程的区别

https://www.php.cn/oracle/489142.html#/

存储过程 函数
用于在数据库中完成特定的操作或任务（如插入、删除等） 用于特定数据（如查询返回值）
程序头部声明用PROCEDURE 程序头部声明用FUNCTION
程序头部声明时不需要返回类型 程序头部声明时要描述返回类型，而且PL/SQL块至少要包括一个有效的RETURN语句
可以使用IN/OUT/IN OUT3种参数模式 可以使用IN/OUT/IN OUT3种参数模式
可以作为独立的PL/SQL语句执行 不能独立执行，必须作为表达式的一部分调用
可以通过OUT/IN OUT返回零个或多个值 对过RETURN语句返回一个值，且该值与声明部分一致，也可以通过OUT类型的参数带出变量
SQL语句（DML或SELECT）中不可以调用存储过程 SQL语句（DML或SELECT）中可以调用函数

优点

  • 更快（无需再次编译）
  • 模块性
  • 可维护性高
  • 节省网络传输（存储在数据库中）
  • 更安全（存储过程控制执行权限）

存储过程创建语法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE [OR REPLACE] PROCEDURE pro_name
[(p1 in|out 数据格式,
 p2 in|out 数据格式,
 ...)]
is|as
	声明部分
begin
	plsql代码块
	exception
		异常处理部分
end;

注意：

  1. OR REPLACE表示的是若已存在同名存储过程，则替换原内容

  2. 形参定义中：

    参数不区分大小写，不需要DECLARE关键字

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

  3. 声明部分：用于存储过程使用到的变量声明 https://www.cnblogs.com/caiguangbi-53/p/11398696.html#/

    声明的三种方法

    • 直接声明数据类型，如p1 varchar2(60)或p2 number(2)
    • 沿用数据表中某数据字段类型%TYPE，如p1 table1.filed1%TYPE
    • 沿用数据表中的所有数据类型%ROWTYPE，如p1 table1%ROWTYPE

  4. 存储过程中的字段赋值

    赋值的三种方法

    • 直接赋值:=，如v_sql varchar2(100) := 'SELECT STUDENT_NAME FROM STUDENT where orgcode=to_char(410621101233)';

    • select 字段名 into 变量 from 表名

      1、查询某字段：

      1
2
3

      SELECT ORGSEQ INTO V_ORGSEQ FROM BASE_ORG_INFO
 WHERE ORGID =
       (SELECT PARENTORGID FROM BASE_ORG_INFO WHERE ORGID = V_ORGID);

      2、查询表所有字段：

      1

      SELECT * INTO V_ROW_VIRTUAL_CARD FROM VIRTUAL_CARD T where t.id = 5120;

      注意：*查询结果只能返回一条记录

    • execute immediate sql into 变量

      1
2
3
4
5

      begin
v_sql := 'SELECT ORGSEQ FROM BASE_ORG_INFO where orgcode=to_char(410621101233)';
execute immediate v_sql INTO V_ORGSEQ;
DBMS_OUTPUT.put_line(V_ORGSEQ);
END;

  5. 异常处理：Oralce有3种异常处理方式（待学 TODO）

存储过程创建举例（https://www.cnblogs.com/diandixingyun/p/12986482.html#/）：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

create or replace  procedure sp_score_pm(
p_in_stuid in varchar2,   --学号    
p_in_courseid in  varchar2, --课程ID
p_out_pm out number      --排名
)
is
ls_score number:=0;
ls_pm number:=0;
begin
  --获取该学生的成绩
  select t.score into ls_score from score t
   where t.stuid = p_in_stuid
    and t.courseid = p_in_courseid;
  --获取成绩比该学生高的人数
  select count(1) into ls_pm from score t
   where t.courseid = p_in_courseid
   and  t.score>ls_score;
   --得到该学生的成绩排名
   p_out_pm:=ls_pm+1;
exception
  when no_data_found  then
     dbms_output.put_line('该学生的课程：'||p_in_courseid|| '的成绩在成绩表中找不到');
end;

调用存储过程

exec

仅支持在sqlplus中使用

call

在所有工具中都可以使用

在函数中直接调用

1
2
3

begin
	fun(); -- 调用存储过程
end;

游标的使用

TODO

存储过程的事务处理

参考 https://www.cnblogs.com/linjiqin/archive/2011/04/18/2019990.html#/

概念

事务：确保数据的一致性，由一些DML语句组成，要么都执行，要么都不执行

命令：

  • COMMIT
  • SAVEPOINT
  • ROLLBACK
  • ROLLBACK TO SAVEPOINT

例子

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

create or replace procedure stu_proc
(
  v_id in number,
  v_name in varchar2,
  v_age in number,
  v_msg out varchar2
) as
begin
  insert into student(id, sname, age) values (v_id, v_name, v_age);
  commit;
  v_msg:='添加成功';
exception
  when others then
    rollback;
    v_msg:='失败成功';
    RAISE_APPLICATION_ERROR(-20010, 'ERROR：违反唯一索引约束！');
end;

非存储过程的事务处理例子 https://www.cnblogs.com/zengyu1234/p/15391621.html#/ ：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

--使用事务控制银行转账业务
declare

mc string(64):='转账成功';
mf string(64):='转账失败';
err_exception exception;
pragma exception_init(err_exception,-2290);

begin

   --向转入账户的交易记录表中插入一条交易记录
   INSERT INTO business VALUES(seq_emp.nextval,10000,1,systimestamp,288);
   --向转出账户的交易记录表中插入一条交易记录
   INSERT INTO business VALUES(seq_emp.nextval,10000,0,systimestamp,287);
   --更新转入方账户余额（+20000）
   update account set balance = balance+10000 where id = 288;
   --更新转出方账户余额（-20000）
   update account set balance = balance-10000 where id = 287;
   
    commit;-- 提交事务
    dbms_output.put_line(mc);
    exception when err_exception then
    rollback; --撤销所有事务操作
    dbms_output.put_line(mf);
end;

select * from aaa;

--事务执行中部分撤销
declare
 me string(64):='0不能做除数';
 zer_err exception;
 pragma exception_init(zer_err,-1476);
begin
  INSERT INTO aaa VALUES('呼伦贝尔');
  SAVEPOINT insert_point;--设置一个保存点
  dbms_output.put_line(56/0);
  commit;
  exception when zer_err then
  rollback to insert_point;--撤销到保存点
  dbms_output.put_line(me);
end;

select 56/0 from dual;--查看错误代码

参考链接

https://blog.csdn.net/weixin_41968788/article/details/83659164#/

https://www.cnblogs.com/inmeditation/p/12090574.html#/

https://www.cnblogs.com/diandixingyun/p/12986482.html#/

https://www.cnblogs.com/taiguyiba/p/7809310.html#/

https://www.cnblogs.com/dersome/p/14304205.html#/