在PL/SQL程序块中,可以定义子程序(过程和函数),以使代码更加模块化。
子程序是命名的PL/SQL块,它可带参数,可被调用。一般过程
子程序执行一个动作, 函数用于计算一个值。
1.定义局部过程的语法形式：
DECLARE -- PL/SQL块的说明关键字
PROCEDURE 过程名[(参数表)] IS --过程说明
说明部分
BEGIN
执行部分 /* 过程体 */
EXCEPTION
例外处理部分
END;
其中参数说明形式:
参数名[ IN, OUT, INOUT] 类型
[:= | DEFAULT 值]
IN 参数可将值传送给被调用的子程序;
OUT参数将值返回给子程序的调用者
INOUT参数可将初始值传送给被调用的子程序,并将修改的
值返回调用者。即既可向过程传递值,也可从过程中返回值
在过程内,一个IN参数起象常量一样的作用,它不能被赋值
当过程被调用时,必须为该参数指定值
一个OUT参数起像一个未被初始化的变量的作用,它的值不可赋给其它变量或重新赋给自己
在退出过程前,要显示地将值赋给全部OUT形式参数
一个IN OUT 参数,它起象初始化的变量的作用,可被赋值,它的值可以赋给其它变量
对IN参数可初始化为缺省值
在支持PL/SQL的任何工具中可定义过程。如果要使它通用,过程必须用CREATE命令建立, 并存储在ORACLE数据库中
使用参数应注意以下原则:
1). 参数类型可以是ORACLE允许的任一类型,还可以用
%TYPE 或 %ROWTYPE 来指定参数类型。
2). 指定参数类型时不能加长度限制。
3). 所有输出参数(OUT)在过程体中只能出现在SELECT语句的 INTO子句中, 或出现在赋值号左边
4). 应尽量减少IN参数个数
PROCEDURE raise_sal (emp_id integer, increase real) IS
cur_salary real ;
salary_mis EXCEPTION ;
BEGIN
select sal into cur_salary
from emp
where empno=emp_id;
IF cur_salary IS NULL THEN
RAISE salary_mis;
ELSE
update emp
set sal=sal+increase
where empno=emp_id;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
insert into emp_audit
values( emp_id, ‘NO such number’);
WHEN salary_mis THEN
insert into emp_audit
values (emp_id,’SALARY IS NULL’);
END;( end raise_sal ;)
用一个PL/SQL语句调用其过程,调用语句形式为：
raise_sal(1001, 500);
或用： raise_sal(increase=>500, emp_id=>1001);
带有in和in out参数的过程:
Create or replace procedure multi_params(mymesg1 in varchar2,mymesg2 out varchar2，mymesg3 in out varchar2) is
Begin
mymesg2:=mymesg1||’ parameter as the out’;
mymesg3:=mymesg3||’ returned’;
End multi_params;
调用过程multi_params
Declare
Inparm varchar2(50):=‘this is the in’;
Outparm varchar2(50);
Inoutparm varchar2(50):=‘and this is the in out’;
begin
multi_params(inparm,outparm,inoutparm);
dbms_output.put_line(outparm||’ ‘||inoutparm);
End;
带游标的存储过程:
1.显示定义游标的方法
Create or replace procedure emp_cur_proc is
Cursor emp_cur is select ename from emp;
Begin
for emp_rec in emp_cur loop
Dbms_output.put_line(emp_rec.ename);
End loop;
End emp_cur_proc;
2.隐式定义游标
Create or replace procedure imp_curpro
Is
begin
For emp_rec in (select ename from emp) loop
Null; --可以执行任何需要的语句
End loop;
End imp_curpro;
3带批量绑定的存储过程
Create or replace procedure bulk_bind_proc
Is
Type bbp is table of emp.ename%type index by binary_integer;
Temp_bulk bbp;
Begin
Select ename bulk collect into tmp_bulk from emp;
End bulk_bind_proc;
有关Oracle存储过程的相关问题:
1.在oracle中，数据表别名是不能加as的，例如：
select a.appname from appinfo a；—— 正确
select a.appname from appinfo as a；—— 错误
2.在存储过程中，select某一字段时，后面必须紧跟into，假如select整个记录，利用游标的话就另当别论了。
3.在我们利用select……into……语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。
可以在该语法之前，先利用select count（*） from 查看数据库中是否存在该记录，如果存在，再利用select……into……
4.请注意，在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行的阶段会报错
5.在存储过程中，关于出现null的问题
假如有一个表X，定义如下
create table X(
id varchar2(50) primary key not null;
vcount number(8) not null;
bid varchar2(50) not null
);
假如在存储过程中，使用如下语句：
select sum（vcount） into fcount from X where bid='xxxxxx'；
如果X表中不存在bid="xxxxxx"的记录，则fcount=null
即使fcount定义时设置了默认值，例如：fcount number（8）：=0依然无效，fcount还是会变成null
这样以后使用fcount时就可能会出现问题
所以在这里我们最好先判断一下:
if fcount is null then fcount:=0;
end if;
函数子程序
FUNCTION sal_ok(salary real, title char)
RETURN boolean is /* 函数返回值是逻辑值 */
max_sal real;
min_sal real;
BEGIN
select max(sal), min(sal) INTO max_sal, min_sal
from sals
where job=title;
RETURN (salary>=min_sal) AND
(salary<=max_sal);
END;
函数调用是以表达式的形式,通过调用返回一个计算值
if sal_ok(new_sal, new_ title) then
…
end if ;
也可以将函数调用返回值赋值给一个布尔变量
调用用户定义的函数可在过程性语句中使用,可以在表达式中
调用该函数,但不能在SQL语句中使用
函数的调用必须作为表
达式的一部分
函数中使用RETURN语句,该语句的作用是立即完成子程序的
执行,并将控制返回给调用者
RETURN PL\SQL 表达式 ;
一个子程序可包含多个RETURN语句
对于过程, RETURN语
句不能包含表达式,
对于函数, RETURN必须包含一个表达式
在PL\SQL块中创建函数子程序。
declare
function sepl(dept_no in number)
return varchar2 is
tmp varchar2(30);
begin
if dept_no>=100 or dept_no<1 then
return null ;
else
select to_char( to_date( lpad(to_char(dept_no), 4,‘0’),‘YYYY’), ‘YEAR’) into tmp
from dual;
return tmp;
end if ;
end;
begin
dbms_output.put_line(sepl(22)); /* 输出为: TWENTY_TWO */
dbms_output.put_line(sepl(15)); /* 输出为: FIFTEEN */
end;
在一个PL\SQL块中,说明多个子程序。
declare
function hello
return varchar2 is
begin
return ‘world’;
end;
procedure call_hello is
begin
dbms_output.put_line(hello);
end;
begin
call_hello ;
end ;
在PL/SQL程序中, 定义的过程,函数子程序,主模块可以调用子程序模块
后定义的子程序可以调用先定义的子程序,即在PL/SQL程序中,使用一个标识符之前必须先说明
若先定义的子程序调用后定义的子程序可进行向前说明
向前说明是:
被向前调用的子程序的说明部分放在调用它的子程序说明之前, 用“;”表示结束。
存储子程序(内嵌子程序）：
创建内嵌子程序就必须用CREATE命令建立
创建存储(内嵌)过程:
CREATE [OR REPLACE] PROCEDURE 过程名[(参数表)] IS | AS
说明部分 ――无 DECLARE 命令
BEGIN
执行部分
EXCEPTION
例外处理部分
END;
用OR REPLACE可选项,如果过程已存在,则重建过程,利用该选项可修改已存在的过程的定义。
创建内嵌过程除用CREATE命令外,其它同PL/SQL块内定义的过程结构相同
内嵌过程一旦被创建后,过程的拥有者或对该过程被授执行特权的用户就可以执行它,即有权访问的任何用户的应用程序或任意ORACLE工具中调用
SQL> create or replace procedure chp1 (x in varchar2, y out varchar2 ,n in number:=7) is
local1 varchar2(50);
begin
local1:=x|| ‘ ’||to_char(n);
y:=local1;
end;
运行该命令,创建内嵌过程
SQL> declare
x1 varchar2(30);
begin
chp1(‘hello world’,x1);
dbms_output.put_line(x1);
chp1(y=>x1,x=>‘ hello world’ ,n=>12);
dbms_output.put_line(x1);
end;
从一应用程序调用, 其调用形式：
EXEC SQL EXECUTE
BEGIN
CHP1( :str, :n); －－实参str, n 为宿主变量。
END;
END_EXEC;
删除内嵌过程命令：
DROR PROCEDURE 过程名
作用：从数据库中删除一独立过程。操作者必须有权限
重新编译一独立存储的过程命令
ALTER PROCEDURE 过程名 COMPILE
创建内嵌函数：
CREATE [OR REPLACE] FUNCTION 函数名(参数表)
RETURN 数据类型 IS |AS
说明部分 ――无 DECLARE 语句
BEGIN
执行部分 ――致少有一条RETURN语句
EXCEPTION
例外处理部分
END；
函数参数一般采用IN参数。
SQL> create of replace function chp2( x varchar2 )
return varchar2 is
begin
return ( x|| ‘ is the parameter ’);
end;
函数一旦被创建后, 在当前用户的PL/SQL摸块中, 就可以在表达式中使用该函数, 或对该函数授有EXECUTE特权的其它用户也可以使用。
declare
value_retu varchar2(80);
begin
value_retu:=chp2 (‘hello word’);
dbms_output.put_line(value_retu);
end;
内嵌函数必须在表达式中调用
删除内嵌函数命令：
DROP FUNCTION 函数名
重新编译一独立的内嵌函数：
ALTER FUNCTION 函数名 COMPILE
过程与函数的异同：
过程：
作为 PL/SQL 语句执行；
在规范中不包含 RETURN 子句；
不返回任何值（只有输入/输出参数，结果集）；
可以包含 RETURN 语句，但是与函数不同，它不能用于返回值。
函数
作为表达式的一部分调用；
必须在规范中包含 RETURN 子句；
必须返回单个值；
必须包含至少一条 RETURN 语句。