子程序
1. PL/SQL命名程序块的定义在PL/SQL程序块的声明部分,用户称之为子程序。
2. Oracle提供了4中类型的子程序:过程,函数,包,触发器。
3. 子程序的优点:
(1) 模块性:子程序可以将程序划分成易于管理,定义明确的逻辑模块,它支持自顶向下的设计方法。
(2) 可维护性:简化维护操作。
(3) 可重性:子程序一旦被定义可以用于许多个应用程序。
(4) 安全性:通过设置权限,使数据更安全。
过程
1. 过程是存储在数据库中的一段存储程序,有时也定义为存储过程。当创建过程时,系统会对其进行编译,并将执行代码存储到数据库中。
2. 存储过程的优点:
(1) 性能好
(2) 可重用PL/SQL
(3) 安全性好
(4) 解耦应用程序与物理表结构。
(5) 模块化,维护性好。
3. 定义过程语法:
create [ or replace] procedure<procedure_name>
(<arg1 [mode] datatype>,…………)
is|as
[declaration]
begin
[exception]
end[procedure_name];
其中model有三种形式,分别是in,out和in out.
4. 用户可以通过两种方式调用过程:
(1) 在SQL>提示符下调用过程,execute过程名(参数……)
(2) 在PL/SQL块中调用过程,过程名(参数……)
5. 举例说明:
(1)参数模式in举例(in表示输入参数,可不写,默认参数为in模式)
create or replace procedure emp_prc
(emp_no in emp.empno%type)
as
empRecord emp%rowtype;
begin
select * into empRecord from emp where empno=emp_no;
dbms_output.put_line(empRecord.ename);
exception
when too_many_rows then
dbms_output.put_line('too many record');
end emp_prc;
---执行 execute emp_prc(7369);
---------------------结果---------------------------------
(2)参数模式out举例
create or replace procedure get_name
(
v_empno emp.empno%type,
v_ename out emp.ename%type
)
as
cursor c_emp is select empno,ename from emp where
empno=v_empno;
v_emp c_emp%rowtype;
begin
open c_emp;
fetch c_emp into v_emp;
v_ename:=v_emp.ename;
close c_emp;
end get_name;
--------------------------------
declare
name emp.ename%type;
begin
get_name(7369,name);
dbms_output.put_line(name);
end;
---------------------------------
创建图:
执行图:
(3)参数模式in out举例
create or replace procedure hi_proc
(
mes in out nvarchar2
)
as
begin
mes:='你好'||mes||'!';
end hi_proc;
创建图
执行图:
1. 删除过程
drop procedure <procedure_name>
函数
1. 函数和过程都以编译后的形式放在数据库中,函数可以没有参数,也可以有多个参数,并有一个返回值。
2. 函数和过程的区别
过 程 | 函 数 |
作为 PL/SQL 语句执行 | 作为表达式的一部分调用 |
在规格说明中不包含 RETURN 子句 | 必须在规格说明中包含 RETURN 子句 |
不返回任何值 | 必须返回单个值 |
可以包含 RETURN 语句,但是与函数不同,它不能用于返回值 | 必须包含至少一条 RETURN |
3. 创建函数的语法
CREATE [OR REPLACE] FUNCTION
<function name> [(param1,param2)]
RETURN <datatype> IS|AS
[local declarations]
BEGIN
Executable Statements;
RETURN result;
EXCEPTION
Exception handlers;
END;
4. 举例说明:
例1:
例2:
自主事务处理
1.自主事务处理
q 主事务处理启动独立事务处理
q 然后主事务处理被暂停
q 自主事务处理子程序内的 SQL 操作
q 然后终止自主事务处理
q 恢复主事务处理
2.PRAGMA AUTONOMOUS_TRANSACTION 用于标记子程序为自主事务处理
3. 自主事务处理的特征:
q 与主事务处理的状态无关
q 提交或回滚操作不影响主事务处理
q 自主事务处理的结果对其他事务是可见的
q 能够启动其他自主事务处理
1. 举例说明:
例1:两个过程p1,p2都不为自主事务处理。
过程p1:
create or replace procedure p1
as
emp_name emp.ename%type;
begin
update emp set ename='fxe' where empno=7369;
select ename into emp_name from emp
where empno=7788;
dbms_output.put_line(emp_name);
rollback;
end p1;
过程p2:
create or replace procedure p2
as
begin
update emp set ename='lxt008' where empno=7788;
p1;
rollback;
end p2;
----执行过程p2 :execute p2;
结果为:
例2:p1属于自主事务处理
过程p1:
create or replace procedure p1
as
PRAGMA AUTONOMOUS_TRANSACTION;
emp_name emp.ename%type;
begin
update emp set ename='fxe' where empno=7369;
select ename into emp_name from emp
where empno=7788;
dbms_output.put_line(emp_name);
rollback;
end p1;
过程p2:
create or replace procedure p2
as
begin
update emp set ename='lxt008' where empno=7788;
p1;
rollback;
end p2;
----执行过程p2 :execute p2;
结果为:
根据例1和例2对比,仔细体会两个例子的区别,并了解自主事务处理