接05篇,本篇讲解存储过程。
简单介绍。存储过程的存在就如同函数的存在,特点在于仅编译一次,高效率
同样的方法先来看个例子了解一下
一、举例
--找出某学号学生选课数量以及平均成绩
--定义存储过程
create or replace procedure p1(
pid in Persons.id % type,cn out number,cav out number(4,2))
is
begin
select count(sc.class_id),avg(total_s) into cn,cav
from sc,sc_score s
where sc.id=s.id
and s.id=pid
and sc.class_id-s.class_id;
end;
--执行存储过程
set serveroutput on; --(命令窗口中)
declare
v_pid sc.id % type := &sno;
v_cn number;
v_cav number(4,2);
begin
p1(v_pid,v_cn,v_cav);
dbms_output.put_line('学生'||v_pid||'所选科目数为'||v_cn||',平均成绩为'||v_cav);
end;
或
set serveroutput on; --(都在命令窗口中)
var cn number;
var cav number;
execute p1(10001,:cn,:cav);
--exec p1(10001,:cn,:cav);
对于一个语句集,分为定义和执行两部分,定义分为create、is/as(begin-end) ,执行主要讲在sql窗口中的写法,分为declare、begin-end
二、存储过程详细说明
1、定义存储过程
类似函数讲解,框架就是记
create [or replace] procedure p_name
[v in/out v_type,……]
is/as
[说明部分]
begin
[操作语句]
[exception 特殊处理]
end;
--[]的内容可不要,/左右需选择
- create or replace procedure:定义存储过程标识,定义了过程名p_name
or replace的意义: 如果已经存在,则替换
- [参数]
- is/as:两个一般可随便换
- [说明部分]:一般用于定义一些变量或游标,定义完全属于存储过程内部的量
- begin-end: 中间是具体操作
- [操作语句]:就是执行动作,参考游标里的部分讲解
- [异常处理]
(1)参数
参数就是函数中需要接收传入值和发送传出值的变量,是与外界联系的变量
v_name in/out v_type,……
其中标识in为传入变量,out为传出变量,就是函数的传入值和返回值。in参数需要在执行前赋值(在执行存储过程时给输入值或确定的初值),out参数需要在执行过程中赋值(在定义存储过程时的具体操作内赋值),返回给调用过程时给定的参数
还是以上面的例子为例
存储过程参数调用情况
v_type部分只写类型,不写长度
(2)异常处理
在每一种语言学习的中期阶段都会学到异常处理,这是用来处理程序出现错误或是你不想要的异常的情况,接下来看看语法结构
- 预定异常
查看某异常是否出现,出现做什么操作
exception
when exc_name then statement1;
when others then statement2;
exc_name是要查看的异常情况,others是其余异常情况
列举一些常用异常名称及编号:
编码 | 名称 | 含义 |
ORA-06530(-06530) | ACCESS_INTO_NULL | 直接引用未初始化的对象属性时,触发异常 |
ORA-06592 | CASE_NOT_FOUND | CASE语句的WHEN子句没有包含必须条件分支或者ELSE子句时,触发异常 |
ORA-06531 | COLLECTION_IS_NULL | 没有初始化集合变量,触发异常 |
ORA-06511 | CURSOR_ALREADY_OPEN | 在已打开游标上执行OPEN操作时,触发异常 |
ORA-01001 | INVALID_CURSOR | 视图从未打开游标提取数据,或者关闭未打开游标时,触发异常 |
ORA-01722 | INVALID_NUMBER | 内嵌SQL语句不能将字符转变成数字时,触发异常 |
ORA-01017 | LOGIN_DENIED | 连接到Oracle数据库时,如果提供了不正确的用户名或口令,触发异常 |
ORA-01403 | NO_DATA_FOUND | 执行SELECT INTO未返回行,或者引用了未初始化的PL/SQL表元素时,触发异常 |
ORA-01012 | NOT_LOGGED_ON | 未连接到Oracle数据库,当执行内嵌SQL语句时,触发异常 |
ORA-06501 | PROGRAM_ERROR | 表示存在PL/SQL内部问题,在这种情况下需要重新安装数据字典视图和PL/SQL包。 |
ORA-016504 | ROWTYPE_MISMATCH | 执行赋值操作时,两变量返回类型不兼容,触发异常 |
ORA-30625 | SELF_IS_NULL | 使用对象类型时,如果在NULL实例上调用成员方法,触发异常 |
ORA-06500 | STORAGE_ERROR | 执行PL/SQL块时,如果超出内存空间或者内存被破坏,触发异常 |
ORA-06533 | SUBSCRIPT_BEYOND_COUNT | 使用嵌套表或者VARRAY元素时,如果下标超出了嵌套表或者VARRAY元素的范围,触发异常 |
ORA-06532 | SUBSCRIPT_OUTSIDE_LIMIT | 使用嵌套表或者VARRAY元素时,如果下标为负值,触发异常 |
ORA-01410 | SYS_INVALID_ROWID | 将字符串转变为ROWID时,使用无效字符串,触发异常 |
ORA-00051 | TIMEOUT_ON_RESOURCE | 等待资源时超时,触发异常 |
ORA-01422 | TOO_MANY_ROWS | 执行SELECT INTO语句时,返回超过一行,触发异常 |
ORA-06502 | VALUE_ERROR | 执行赋值操作时,如果实际数据超出变量长度,触发异常 |
ORA-01476 | ZERO_DIVIDE | 数字除以0,触发异常 |
- 非预定异常
也是查看某异常,只是在数据库中有些异常是只有错误编号和相关的错误描述,并没有名称,于是非预定异常可以为已有异常命名,解决了这个问题
create or replace procedure p1(
v1 in char,v2 out char)
is
--定义异常,并分配给某个已有异常
my_exception exception;
pragma exception_init(my_exception,-12899);
begin
insert into P values(10000002,null,null);
--异常处理
exception
when my_exception then
dbms_output.put_line('内容超出存储范围');
end;
- 自定义异常
有时程序员们需要调试程序或者不希望出现某些有但不合理的值,可以通过自定义异常来处理
create or replace procedure p1(
v1 in char,v2 out char)
is
--定义异常
my_exception exception;
pragma procedure_init(my_exception,-20000);
begin
if v1='1' then v2:='Y';
elsif v1='0' then v2:='N';
--抛出异常
else raise my_exception;
end if;
--处理异常
exception
when my_exception then
dbms_output.put_line('v1值错误');
end;
有一步抛出异常,其意义在于赋予该异常情况的具体含义(告诉别人你的异常在什么情况下算是异常)
有些小拓展知识:
- if语句完整结构:if-elsif-else-end if
- 赋值符号:':='
2、执行存储过程
也就是所谓的调用函数该如何调用,执行存储过程该如何执行的问题
(1)sql窗口中
declare
[v v_type;……]
begin
[具体操作(包含过程调用)]
end;
- declare:定义声明
- [变量说明]:变量名 变量类型 (选择赋值);存储过程的参数都需要有变量与他们在这里联系
- begin-end:具体操作
- [具体操作]
执行存储过程(调用):pro_name(v,……);
(2)命令窗口中
set serveroutput on;
var v_name v_type;
exec pro_name(value,:v_name,……);
- set serveroutput on:打开输出权限,如果有输出操作则可以在命令窗口中看到结果
- var:定义变量,用来接收输出参数
- exec或execute:执行存储过程(注意参数前面加冒号:)
现在再返回看举例应该ok了
06结