PL/SQL 存储过程 异常处理 函数 包 视图
目录
- 1 存储过程（procedure）
- 2 异常处理（exception）
- 3 函数（function）
- 4 包（package）
- 5 视图
1 存储过程（procedure）
是一个可以编程的方式来操纵sql的集合，其实就是一个带名字的PL/SQL块。
优点：
- 执行效率很高，因为存储过程是预编译的，即创建时编译，而sql语句是执行一次，编译一次。
- 调用存储过程，可以大大减少跟数据库的交互工程。
- 降低网络通信量，因为存储过程执行的时候，只需要call存储过程名，不需要传递大量的sql语句。
- 有利于复用
缺点：
- 移植性非常差，如果在Oracle上编写的存储过程，移植到mysql需要进行修改。
- 代码可读性差，实现一个简单逻辑，代码都有点长。
语法：
create or replace procedure 过程名 [(参数..)]
is/as
局部变量声明
begin
要执行的语句
[exception]
[异常处理]
end;
参数类型
in：用于接收程序调用的值（默认的参数默认）
out：用于向调用程序返回值
int out：用于接受程序调用的值，并且向调用程序返回更新的值。
注意：参数类型要写在变量类型之前
参数默认值：在变量类型后面加上 default 变量值即可。形参列表加上了默认值，可以选择不传参。
执行过程：
-
begin 过程名(参数名=>值); end; -
execute 过程名(参数名=>值);
删除存储过程语法：
drop procedure 过程名
示例：
-- 案例1 有参无默认值
create or replace procedure gc(a int,b int)
is c int;
begin
c:=a+b;
dbms_output.put_line(c);
end;
begin
gc(a=>10,b=>20);-- 结果输出30；
end;
-- 案例2 有参有默认值
create or replace procedure gc(a int default 20,b int)
is c int;
begin
c:=a+b;
dbms_output.put_line(c);
end;
begin
gc(b=>20);-- 结果输出40；
end;
-- 案例3 有参无默认值 且 设置了out参数类型（用于向调用程序返回值）
create or replace procedure gc(a int,b int,d out int)
is c int;
begin
c:=a+b;
d:=c;
dbms_output.put_line(c);
end;
declare
k int;
begin
gc(a=>10,b=>20,d=>k);-- 结果输出30；
dbms_output.put_line(k);-- 结果输出30
end;
-- 案例四 有参有默认值 且 设置了 in c参数类型（用于接收程序调用的值（默认的参数默认））
create or replace procedure gc(a int,b int,d in int)
is c int;
begin
dbms_output.put_line(d);-- 结果输出10；
c:=a+b;
dbms_output.put_line(c);-- 结果输出30
end;
declare
k int:=10;
begin
gc(a=>10,b=>20,d=>k);
dbms_output.put_line(k);-- 结果输出10
end;
-- 删除过程
drop procedure gc
存储过程注意事项：
- 数据量大的时候（10万+），一定要做压力测试，有些存储过程在大数据量的情况下才会出现问题。
- 如果插入或更新的次数比较多，为了提高效率，可以先执同样的操纵一万次，再commit一次。
- 如果先插入记录，没有commit，再对这条记录进行更新，会引起死锁。如果先后对同一笔记录进行更新，又没有commit，也会引起死锁。因为后一条语句会等待前一条一句提交。如果出现这种情况，则需要用一条一条commit。
- 不要忘记存储过程里写commit。
2 异常处理（exception）
在存储过程的时候，要处理异常，Oracle中绝大多数异常只有错误信息编号，与相关描述。
异常处理的类型：
-
预定义异常处理
在PL/SQL中已经定义好并给予异常名称的异常，已命名的有21个。
Oracle 21个预定义异常示例：
ACCESS_INTO_NULL -- 未定义对象 CASE_NOT_FOUND -- CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时 COLLECTION_IS_NULL -- 集合元素未初始化 CURSER_ALREADY_OPEN -- 游标已经打开 DUP_VAL_ON_INDEX -- 唯一索引对应的列上有重复的值 INVALID_CURSOR -- 在不合法的游标上进行操作 INVALID_NUMBER -- 内嵌的 SQL 语句不能将字符转换为数字 NO_DATA_FOUND -- 使用 select into 未返回行，或应用索引表未初始化的元素时 TOO_MANY_ROWS -- 执行 select into 时，结果集超过一行 ZERO_DIVIDE -- 除数为 0 SUBSCRIPT_BEYOND_COUNT -- 元素下标超过嵌套表或 VARRAY 的最大值 SUBSCRIPT_OUTSIDE_LIMIT -- 使用嵌套表或 VARRAY 时，将下标指定为负数 VALUE_ERROR -- 赋值时，变量长度不足以容纳实际数据 LOGIN_DENIED -- PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码 NOT_LOGGED_ON -- PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据 PROGRAM_ERROR -- PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包 ROWTYPE_MISMATCH -- 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容 SELF_IS_NULL -- 使用对象类型时，在 null 对象上调用对象方法 STORAGE_ERROR -- 运行 PL/SQL 时，超出内存空间 SYS_INVALID_ID -- 无效的 ROWID 字符串 TIMEOUT_ON_RESOURCE -- Oracle 在等待资源时超时预定义异常处理案例：
-- 案例 除数不能为0的异常 create or replace procedure gc is c int; begin c:=1/0; exception when ZERO_DIVIDE then dbms_output.put_line('除数不能为0！'); end; begin gc; end; -- 结果输出：除数不能为0; -
非预定义异常处理
Oracle中除了那21个命名异常外，其它异常都是非预定义异常。
如果要处理未命名的内部异常，必须使用others异常处理器或pragma exception_init。
pragma exception_init告诉编译器异常名与Oracle错误码结合起来，这样就可以通过名字引用任意的内部异常，并且可以通过名字为异常编写一些适当的异常处理器。
示例：
-- 案例1 往部门表里面新增一条数据； create or replace procedure gc is wyys exception; pragma exception_init(wyys,-1);-- 在Oracle里面，-1错误编号对应的错误描述是：违反唯一约束条件 () begin insert into dept values(10,'aa','aaa'); commit; exception when wyys then dbms_output.put_line('主键重复'); rollback; end; begin gc; end; -
自定义异常
根据自身业务而定义的异常
使用raIse关键字引发异常
使用exception处理异常
示例：
-- 案例1 查询用户表里面是否有张三并且年龄为18的人 declare yc exception; iscz int; begin select count(*) into iscz from yh where name='张三' and age=18; if iscz =0 then raise yc; end if; dbms_output.put_line('该用户存在！'); exception when yc then dbms_output.put_line('该用户不存在'); end; -- 如果满足if条件 就会直接抛出异常，不会继续执行下面的代码了，直接被exption捕获处理。
3 函数（function）
与过程类似，是一组sql语句或者pl/sql语句块的集合，同时能够返回结果集。
函数的结构：
- 声明部分：包括类型，变量，游标。
- 执行部分：完成功能而编写的SQL语句或则是PL/SQL代码块
- 异常处理部分
函数定义语法：
create or replace function 函数名(参数)-- 函数也支持in out in out 用法跟过程一样。
return 返回类型 is/as
方法体
注意：
- 创建函数不使用declare关键字
- 在创建函数时可指定参数
删除函数：
drop function 函数名
示例：
-- 判断一个数字是否为素色
create or replace function js3(a int) return boolean is
begin
for y in 2..a-1 loop
if a mod y = 0 then
return true;
end if;
end loop;
return false;
end;
declare
jg boolean;
begin
jg:=js3(&请输入);
if jg then
dbms_output.put_line('不是素数');
else
dbms_output.put_line('是素数');
end if;
end;
4 包（package）
通过包就可以分类管理过程和函数
包的结构：
-
包规范
用于定义常量，变量，游标，过程和函数等用户与程序的接口
可以在包内引用，也可以被外部调用
-
包主体
是包规范的实现，包括变量，游标，过程和函数等
包主体内的内容不能被外部引用
示例：
-- 定义包规范
create or replace package myb is
function add(a int,b int) return int;
procedure gc(c out int);
type c is ref cursor;-- 返回游标，定义引用游标类型
procedure gc2(c2 out c);
end;
-- 定义包主体
create or replace package body myb is
function add(a int,b int) return int is
begin
return a+b;
end;
procedure gc(c out int) is
begin
select count(*) into c from dept;
end;
procedure gc2(c2 out c) is
begin
open c for select ename from emp;-- 返回游标
end;
end;
-- 调用
declare
jg int;
jg2 myb.c;
jg3 emp%rowtype;
begin
jg:=myb.add(a=>10,b=>20);
dbms_output.put_line(jg);
myb.gc(c=>jg);
dbms_output.put_line(jg);
myb.gc2(c2=>jg2)
fetch jg2 into jg3;
while jg2%found loop
dbms_output.put_line(jg3.ename);
fetch jg2 into jg3;
end loop;
close jg2;
end;
如果不想自己定义引用游标，可以直接使用Oracle定义的引用游标
SYS_REFCURSOR
示例：
-- 定义包规范
create or replace myb is
type cur is ref cursor;--引用游标
procedure fetchcur(c out cur);
procedure fetchcur1(c2 out SYS_REFCURSOR);
end;
-- c和c2都是引用游标
5 视图
- 是一个数据中虚拟的表
- 经过查询操纵形成的结果
- 具有普通表的结果
- 不能实现数据的存储
- 对视图的修改将会影响实际的数据表
如果没有权限，此时需要授权，以sys身份登录
grant create any view to scott;
创建视图语法：
create or replace view 视图名 as 查询语句
删除视图：
drop view 视图名
示例：
-- 创建一个视图
create or replace view myview as select * from emp where deptno<30;
-- 删除数据
drop view myview;