目录
五、动态SQL和异常
1、动态SQL
注:DDL语句命令和会话控制语句不能在PL/SQL 中直接使用
DDL:create alter drop truncate
语法:execute immediate 字符串参数 [into] 变量 using 参数;
into 变量:是把sql语句的执行结果保存到变量中。
2、异常
(1)异常概述
ORACLE 异常分为两种异常:系统异常和自定义异常。其中系统异常又分为预定义异常和非预定义异常。
(2)预定义
有错误编号 和异常名字
有很多,比如no_data_found较常见。
语法:
begin
exception
when 异常名称 then
异常处理代码;
when 异常名称 then
异常处理代码;
......
when others then
异常处理代码;
end;
注:exception 之后不能有除 异常处理代码 外的其他代码。
SQLCODE 和 SQLERRM 为异常处理函数。
sqlcode用于取得错误号,sqlerrm 用于取得和对应号对应的错误消息。
(3)非预定义异常
有错误编号,但并没有定义异常的名字。通过伪过程 pragma exception_init(自定义的异常名,编号)。
(4)自定义异常
declare
--定义一个自己的异常
myexc exception;
m number(10):=&m;
n number(10):=&n;
begin
if n=0 then
dbms_output.put_line('除数不能为0');
raise myexc;--抛出异常 需要处理异常
end if;
exception
when myexc then
dbms_output.put_line('出错啦!');
end;
结果是:
(5)引发应用程序错误
RAISE_APPLICATION_ERROR 过程,错误编号必须在 - 20000和 -20999 之间。
引发应用程序的语法:
RAISE_APPLICATION_ERROR(error_number,error_message);
六、存储过程和函数
存储过程和函数的唯一区别就是函数总向调用者返回数据,而过程则不返回数据。
1、存储过程
(1)创建语法:
create [or replace] procedure p1[(参数 in|out|in out 参数数据类型...)]
is | as
begin
plsql语句;
exception
异常处理;
end;
输入参数用in,输出参数用out,既作输入又作输出参数用 in out 。没有参数就不写括号了。
(2)调用
1、在plsql块中调用
declare
begin
end;
2、call 命令调用 call p1();
(3)删除存储过程
drop procedure p1;
(4)参数的三种模式
in
out
in out :用于接收传进来的参数值,并返回更新的值。
注:将过程的执行权限授予其他用户
---grant execute on 存储过程名 to 用户名;
如: grant execute on p1 to scott;
2、函数
(1)创建语法:
create [or replace] function f1[(形参 参数类型,......)]
return 返回值类型
is
声明变量;
begin
plsql代码块;
return 返回值;
end;
(2)调用函数的方式
(1)使用sql语句
select 函数名字() from dual;
(2)使用plsql块
七、触发器
在事件发生时隐式地自动执行的pl/sql 块,不能接受参数,不能被显式调用。
1、触发器类型
(1)DML 触发器:其中包含语句级(表级)触发器、行级触发器,instead of (替换) 触发器。
(2)DDL(模式 触发器):在模式中执行DDL语句时执行。
(3)数据库级触发器:在发生打开、关闭、登录和退出数据库等操作时执行。
2、创建触发器语法
create [or replace] trigger 触发器名字
before | after 触发器事件 on 表名
[for each row] --行级触发器要写上这行,表级则不用
[when trigger_condition] --添加的触发条件
trigger_body :触发体,是标准的PL/SQL语句块,即declare begin end;
3、表级触发器(语句级触发器)
创建时 没有 for each row这行,(:new,:old 仅限于使用 for each row时使用)
updating 如果触发这个触发器的是update语句,它的值就是true
deleting inserting 都是这样
4、行级触发器
对DML语句修改的每个行都会执行一次(如果没有限制条件的话),有 for each row 语句。在 begin 代码中可以使用 :new 和 :old 。
如:update dept set dname='财务部' where empno=7369;
这里的 财务部 就是 :new.dname 7369 就是 :old.empno。
5、替换触发器
只能使用在视图上,而且是行级的触发器。
6、模式触发器
在模式中执行DDL语句时执行
DDL:create,truncate(有表结构),drop(没有表结构),alter
7、数据库级触发器
在发生打开、关闭、登录和登出数据库等系统事件时才会执行。
startup 打开 shutdown 关闭 logon 登录 logoff 退出
启用、禁用和删除触发器
禁用 alter trigger 触发器名字 disable;
启用 alter trigger 触发器名字 enable;
删除 drop trigger 触发器名字;
八、程序包
程序包是对相关过程、函数、变量、游标和异常对象的封装,程序包由包头(规范)和包体(主体)两部分组成。
1、创建程序包的语法
包头和包体名字一致
包头语法:
create [or replace] package 名字 is | as
[public item declaration] 公共声明部分
[subprogram specification] 声明 PL/SQL 子程序
end [package_name];
create or replace package pack1 is
end;
包体语法:
create or replace package body 名字 is | as
[private item declarations] 私有声明部分
[subprogram bodies] 子程序体
[begin initialization]
end [包名];
create or replace package body pack1 is
end;
--定义包头(全是声明的部分,具体实现都在包体里)
create or replace package pack1
is
aa number:=9;
procedure insert_dept(v_dept in dept%rowtype);
function fun(v1 number,v2 number) return number;
end;
--包体
create or replace package body pack1
is
bb number:=10;
--存储过程的实现部分
procedure insert_dept(v_dept in dept%rowtype)
is
begin
insert into dept values(v_dept.deptno,v_dept.dname,v_dept.loc);
end;
--函数的实现部分
function fun(v1 number,v2 number)
return number
is
begin
return v1+v2;
end;
调用:包名.元素名
declare
n number;
begin
n:=pack1.fun(3,4);
dbms_output.put_line(n);
end;
2、程序包中的游标
程序包里的游标的定义分为游标规范和游标主体两部分
声明游标规范时必须使用 return 子句指定游标的返回类型 (return 数据类型)
return 子句 指定的数据类型可以是:%rowtype , 程序员定义的记录类型 record ,不可以是 number , varchar2 ,%type 等类型。
--显式游标(包头)
create or replace package pack2 is
cursor mycursor return emp%rowtype;
procedure mycursor_use;
end;
--(包体)
create or replace package body pack2 is
cursor mycursor return emp%rowtype is select * from emp;
procedure mycursor_use
is
v_emp emp%rowtype;
begin
open mycursor;
fetch mycursor into v_emp;
while mycursor%found loop
dbms_out.put_line(v_emp.ename);
fetch mycursor into v_emp;
end loop;
close mycursor;
end;
end;
--ref游标 这个游标是用在存储过程里的
--包头
create or replace package pack3 is
type refcur is ref cursor; --作为公共部分声明在这里,ref不需要写返回值,因为它是动态的
procedure mycursor_use;
end;
--包体
create or replace package body pack3 is
procedure mycursor_use is
mycursor refcur;
v_emp emp%rowtype;
begin
open mycursor for select * from emp; --关键词for
fetch mycursor into v_emp;
while mycursor%found loop
dbms_output.put_line(v_emp.ename);
fetch mycursor into v_emp;
end loop;
close mycursor;
end;
end;
3、内置程序包
可以由用户任意访问,可以扩展数据库的功能,用户sys拥有所有的程序包。