在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) isBegin mymesg2:=mymesg1||’ parameter as the out’; mymesg3:=mymesg3||’ returned’;End multi_params;调用过程multi_paramsDeclare 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 语句。