一、存储过程
-
存储过程简介
a、 在Oracle中的存储过程又名子程序;
b、 命名的PL/SQL块,编译并存储在数据库中;
c、 存储过程的各个部分:声明部分; 可执行部分; 异常处理部分(可选)。
d、存储过程的分类:
过程-执行某些操作; 函数-执行操作并返回值;
-
存储过程的优点
a、模块化: 将程序分解为逻辑模块;
b、可重用性: 可以被任意数目的程序调用;
c、可维护性: 简化维护操作;
d、安全性: 通过设置权限,使数据更安全。 -
创建存储过程的语法
--创建存储过程:
create [or replace] procedure 存储过程名称 [(参数名称 参数类型,参数名称 参数类型)]
is|as
局部变量声明区
begin
[可执行SQL语句;]
[exception
异常处理语句]
end 过程名称;
--调用存储过程
begin
过程名称[(参数名称 参数类型,参数名称 参数类型)];
end;
例子:
in参数存储过程:
--3. 创建一个带参数的存储过程,根据制定的参数删除一个员工。
--创建存储过程:
create or replace procedure emp2(theEmpNo emp.empNo%type)
as
begin
delete from emp where empNo = theEmpNo;
dbms_output.put_line('删除成功!');
end;
--调用存储过程:
declare
theEmpNo emp.empNo%type;
begin
theEmpNo:='&请输入员工编号';
emp2(theEmpNo);
end;
out参数存储过程:
--4.创建一个带输出参数的存储过程,在这个过程中首先调用上面的那个存储过程,然后用输出参数返回剩下的员工人数
--创建存储过程:
create or replace procedure emp3(
theDeptNo emp.deptNo%type, --员工编号
peopleNumber out number --人数
)
as
begin
emp2(theDeptNo);
select count(*) into peopleNumber from emp;
dbms_output.put_line(peopleNumber);
end;
--调用存储过程:
declare
theDeptNo emp.empNo%type; --员工编号
peopleNumber number; --人数
begin
theDeptNo:='&请输入员工编号';
emp3(theDeptNo,peopleNumber);
end;
-
自主事务处理
a、自主事务处理主事务处理启动独立事务处理; 主事务处理启动独立事务处理; 自主事务处理存储过程内的SQL操作; 然后终止自主事务处理; 然后终止自主事务处理。
b、PRAGMA AUTO NO MOUS TRANSACTION 用于标记存储过程为自主事务处理
c、自主事务处理的特征:与主事务处理的状态无关; 提交或回滚操作不影响主事务处理; 自主事务处理的结果对其他事务是可见的; 能够启动其他自主事务处理。
二、函数
- 创建函数语法
grant or replace function 函数名称 [(参数名称 参数类型,参数名称 参数类型)] return 返回类型
is|as
[局部变量声明]
begin
执行语句;
return 返回值;
[exception
异常处理;]
end;
例子:
--2.定义一个函数,用来求一个数的N次幂的结果并返回,底数和幂数在调用函数的时候作为参数传递过来
--创建函数:
create or replace function power(
a int, --变量值
b int --次幂
)
return int
as
c int;
begin
for i in 1..b
loop
c:=c*a;
end loop;
return c;
end;
--调用函数:
declare
a int;
b int;
c int;
begin
a:='&请输入一个数字';
b:='&请输入次幂';
c:=power(a,b)
dbms_output.put_line(c);
end;
-
定义函数和访问函数
a、定义函数的限制:形参不能是PL/SQL类型 函数的返回类型也必须是数据库类型
b、访问函数的两种方式:
使用PL/SQL块 使用SQL语句
-
存储过程和函数
过程 | 函数 |
---|---|
作为PL/SQL语句执行 | 作为表达式的一部分调用 |
在规格说明中不包含RETURN子句 | 必须在规格说明中包含RETURN子句 |
不返回任何值 | 必须返回单个值 |
可以包含RETURN语句,但是与函数不同,它不能用于返回值 | 必须包含至少一条RETURN语句 |
三、程序包
- 创建包头语法
create or replace package 包名称
is|as
[定义存储过程;]
[定义函数;]
end 包名;
- 程序包主体定义
create or replace package body 包名
is|as
实现包头中定义的过程或函数:
end 包名;
- 例子
--创建包头
create or replace package power_package
is
procedure addPower(powerName varchar2,powerPic varchar2,del number); --向表中添加数据
function getSum(beginNumber int,endNumber int) return int; --求数字a到数字b的和
end power_package;
--程序包主体定义
create or replace package body power_package
as
procedure addPower(powerName varchar2,powerPic varchar2,del number)
as
begin
insert into tb_power values(zb.nextval,powerName,powerPic,del);
end addPower;
function getSum(beginNumber int,endNumber int) return int
as
b int:=0; --存储结果
begin
for i in beginNumber..endNumber
loop
b:=b+i;
end loop;
return b;
end getSum;
end power_package;
/* 添加权限数据 */
begin
power_package.addPower('VIP','sac/casca/aaa.png',null);
end;
/*求beginNumber到endNumber的和并返回*/
--调用包中的函数:
declare
beginNumber int;
endNumber int;
a int;
begin
beginNumber:='&请输入一个起始数字';
endNumber:='&请输入一个结束数字';
a:=power_package.getSum(beginNumber,endNumber);
dbms_output.put_line(a);
end;
- 程序包的优点
a、模块化
b、更轻松的应用程序设计
c、信息隐藏
d、新增功能
e、性能更佳
四、内置程序包
程序包名称 | 说明 |
---|---|
DBMS_ OUTPUT | 处理PL/SQL块和子程序输出调试信息 |
DBMS_ LOB | 提供对LOB数据类型进行操作的功能 |
DBMS_ XMLQUERY | 提供将数据转换为XML类型的功能 |
DBMS RANDOM | 提供随机数生成器 |
UTL_ FILE | 用PL/SQL程序来读写操作系统文本文件 |
- DBMS_ OUTPUT
- DBMS_ LOB
- DBMS_ XMLQUERY
- DBMS RANDOM
- UTL_ FILE