oracal存储过程与存储函数

存储过程概述

存储过程是子程序的一种类型，能够完成一些任务，作为schema对象存储于数据库。是一个有名字的PL/SQL代码块，支持接收或不接受参数，同时也支持参数输出。一个存储过程通常包含定义部分，执行部分，Exception部分，可以被其他子程序调用，也可以被重用。
过程定义

CREATE ［OR REPLACE］PROCEDURE procedure_name
[(argument_name[IN | OUT | IN OUT] argument_type)]
AS | IS
BEGIN
    procedure_body;
END[procedure_name];

存储过程中参数的类型
IN ：表示是一个输入参数，可以指定缺省值。如省略参数类型，则缺省为 in 类型
OUT ：表示是一个输出参数
IN OUT ：既可以作为一个输入参数，也可以作为一个输出参数来输出结果
过程调用

EXECUTE ｜CALL procedure_name [(argument_list)]

例：定义一个过程，以JOB为参数，查询该JOB的最高工资、最低工资、平均工资。

create or replace procedurequery_sal(v_job in emp.job%type)
   as
   v_min_salemp.sal%type;
   v_max_salemp.sal%type;
   v_avg_salemp.sal%type;
begin
   selectmin(sal) into v_min_sal from emp where job = v_job;
   selectmax(sal) into v_max_sal from emp where job = v_job;
   selectavg(sal) into v_avg_sal from emp where job = v_job;
   dbms_output.put_line('Thisjob is minimum salary is ' || v_min_sal);
   dbms_output.put_line('Thisjob is maximum salary is ' || v_max_sal);
   dbms_output.put_line('Thisjob is average salary is ' || v_avg_sal);
exception
   whenno_data_found then
     dbms_output.put_line('NotRecord Found');
end;
SQL> set serveroutput on
SQL> execquery_sal('SALESMAN');
This job isminimum salary is 1250
This job ismaximum salary is 1600
This job is averagesalary is 1400
PL/SQL proceduresuccessfully completed.

参数及其传递方式
在建立过程时，传递的参数为可选项，如果省略参数选项，则过程为无参过程（定义时不指定参数，调用时也不需要参数）。如果指定参数选项，则过程为有参过程（定义时需要指定参数名字、模式、数据类型，调时时需要给出对应的参数值），定义时的参数，称为形参，调用时的参数称为实参。

无参过程

create or replaceprocedure display_systime
as
begin
  dbms_output.put_line('CurrentTime is ' || sysdate);
end;
SQL> execdisplay_systime;
Current Time is24-FEB-13

有参过程
定义时需要指定参数的名字、模式、数据类型
例：定义一个添加记录的过程（全部为输入参数）

create or replaceprocedure add_emp
(
v_no inemp.empno%type,
v_name inemp.ename%type,
v_dept inemp.deptno%type default 20   --缺省的部门号
)
as
begin
  insertinto emp (empno,ename,deptno) values(v_no,v_name,v_dept);
exception
  whendup_val_on_index then
    dbms_output.put_line('RecordExists');
end ;
SQL> exec add_emp(7369,'TEST',20);    --调用
Record Exists

例：定义一个输入员工编号，修改记录，再返回修改后的结果（姓名和工资）。

create or replace procedure ed_emp
 (
 v_no inemp.empno%type,    --定义了一个in类型，二个out类型的参数
 v_name outemp.ename%type,
 v_sal outemp.sal%type)
 as
 begin
   updateemp set sal = sal + 100 where empno = v_no;
   selectename,sal into v_name,v_sal from emp where empno = v_no;
 exception
   whenno_data_found then
     dbms_output.put_line('NotData Found');
 end;
 /
Procedure created.
SQL>VARIABLE t_name varchar2(20);
SQL>VARIABLEt_sal number;
SQL> execed_emp(7369,:t_name,:t_sal);
PL/SQL proceduresuccessfully completed.
SQL> printt_name
T_NAME
--------------------------------------------------------------------------------
SMITH
SQL> printt_sal
     T_SAL
----------
       900

例：IN OUT类型参数的使用

create or replace procedure comp
(num1 in outnumber,num2 in out number)
as
  v1number;
  v2number;
begin
  v1 :=num1 + num2;
  v2 :=num1 * num2;
  num1:= v1;
  num2:= v2;
end;
SQL> var v1number
SQL> var v2number
SQL> exec :v1:= 3
PL/SQL proceduresuccessfully completed.
SQL> exec :v2:= 5
PL/SQL proceduresuccessfully completed.
SQL> execcomp(:v1,:v2);
SQL> print v1v2
        V1
----------
         8
        V2
----------
        15
SQL> execcomp(:v1,:v2);
PL/SQL proceduresuccessfully completed.
SQL> print v1v2
        V1
----------
        23
        V2
----------
       120

可以看到in out类型的参数既作为输入参数又作为输出参数。

存储过程参数的传递方式：
按位置传递：
实参按顺序将值传给形参

EXECUTEED_EMP(7900,:t_name,:t_sal);
EXECUTEED_EMP(8000,'TEST2',20);

按名字传递

EXECUTEED_EMP(v_name=>'ABCDE',v_dept=>10,v_no=>8003); 

混合传递

EXECUTEED_EMP(8005,v_dept=>20,v_name=>'TEST5');

过程管理
查看系统过程信息
DBA_OBJECTS
DBA_PROCEDURES
DBA_SOURCE
使用descprocedure_name 查看存储过程的参数信息

SQL>desc ed_emp;
PROCEDURE ed_emp
 ArgumentName                  Type                    In/OutDefault?
 ------------------------------ ----------------------- ------ --------
 V_NO                           NUMBER(4)               IN
 V_NAME                         VARCHAR2(10)            OUT
 V_SAL                          NUMBER(7,2)             OUT 

从dba_objects获得存储过程的信息

SQL>select owner,object_name,object_type,status from dba_objects whereobject_name = 'ED_EMP';
OWNER                          OBJECT_NAME          OBJECT_TYPE     STATUS
------------------------------ -------------------- --------------- -------
SCOTT                          ED_EMP               PROCEDURE       VALID 
SQL>select object_name,procedure_name,interface,authid from user_procedures;
OBJECT_NAME          PROCEDURE_NAME                 INTAUTHID
-------------------- ------------------------------ --- ------------
DISPLAY_SAL                                         NO  DEFINER
ED_EMP                                              NO  DEFINER

查看存储过程的源代码

SQL>select owner,object_name,object_type,status from dba_objects whereobject_name = 'ED_EMP';
OWNER                          OBJECT_NAME          OBJECT_TYPE     STATUS
------------------------------ -------------------- --------------- -------
SCOTT                          ED_EMP               PROCEDURE       VALID 
SQL>select object_name,procedure_name,interface,authid from user_procedures;
OBJECT_NAME          PROCEDURE_NAME                 INTAUTHID
-------------------- ------------------------------ --- ------------
DISPLAY_SAL                                         NO  DEFINER
ED_EMP                                              NO  DEFINER

查看错误信息
SHOW ERRORS

函数概述

函数通常用于返回特定的数据。其实质是一个有名字的PL/SQL块，作为一个schema对象存储于数据库，可以被反复执行。函数通常被作为一个表达式来调用或存储过程的一个参数，具有返回值。
一、建立函数的语法

CREATE [ ORREPLACE ] FUNCTION function_name
    (argument1[mode1] datatype1,
     argument2[mode2] datetype2,
     ...)
RETURN datatype
IS | AS
    [local_variable_declarations;...]
BEGIN
    --actions;
    RETURNexpression;
END[function_name];

建立函数的几点注意事项
1. 指定参数数据类型时 (argument) ，不能指定其长度
2. 函数头部必须指定 return 子句，函数体内至少要包含一条 return 语句
3. 可以指定 in 参数，也可以指定 out 参数，以及 in out 参数
4. 可以为参数指定缺省值。指定缺省值时使用 default 关键字。如 arg1 varchar2 default 'SCOTT'
使用函数的优点 :
1. 增加了代码的灵活性，可以完成一些较为复杂的任务，以及仅仅通过 SQL 无法完成的任务
2. 可以直接将函数使用到 where 子句中来过滤数据
3. 可以作为存储过程的参数使用，是存储过程的一种补充

建立函数
建立不带参数的函数

create or replace function get_user
return  varchar2
as
  v_user varchar2(20);
begin
  select username into v_user from user_users;
  return v_user;
end;

使用全局变量接收函数的返回值

SQL> var v1 varchar2(20);
SQL> exec :v1 := get_user;
PL/SQL procedure successfully completed.
SQL> print v1
V1
--------------------------------------
SCOTT

使用本地变量接收函数的返回值

SQL> declare user_namevarchar2(20);                    
  2   begin
  3   user_name := get_user();
  4   dbms_output.put_line('Current User: ' ||user_name);
  5  end;
  6  /
Current User: SCOTT
PL/SQL procedure successfully completed.

在SQL语句中直接调用函数

SQL> select get_user from dual;
GET_USER
------------------------------------
SCOTT

使用dbms_output调用函数(此调用作为存储过程的一个参数来进行调用)   

SQL> set serveroutput on;
SQL> exec dbms_output.put_line('Current user: '||get_user);
Current user: SCOTT  

建立带有in参数的函数

create or replace function raise_sal(namein varchar2)
return number
as
  new_salemp.sal%type;
begin
  selectsal * 1.2 into new_sal from emp 
  whereupper(ename) = upper(name);
  returnnew_sal;
exception
  whenno_data_found then
    raise_application_error(-20000,'CurrentEmployee does not exists');
end;
 
SQL>  selectsal,raise_sal('SCOTT') from emp where ename='SCOTT';
       SAL RAISE_SAL('SCOTT')
---------- ------------------
      3000               3600
 
SQL>  selectsal,raise_sal('SCOTTT') from emp where ename='SCOTT';
 select sal,raise_sal('SCOTTT') from emp where ename='SCOTT'
            *
ERROR at line 1:
ORA-20000: Current Employee does not exists
ORA-06512: at "SCOTT.RAISE_SAL", line 11

建立带有out参数的函数

create or replace function get_info
(name varchar2,titile out varchar2)
return varchar2
as
  deptname dept.dname%type;
begin
  select e.job,d.dname into titile,deptname
  from emp e,dept d
  where e.deptno = d.deptno
  and upper(e.ename) = upper(name);
  return deptname;
exception
  when no_data_found then
    raise_application_error(-20000,'Current Employee doesnot exists');
end;
/

注意对于使用out参数的函数，不能使用SQL语句来调用。而必须定义变量接收out参数和函数的返回值。
调用如下

create or replace function get_info
(name varchar2,titile out varchar2)
return varchar2
as
  deptname dept.dname%type;
begin
  select e.job,d.dname into titile,deptname
  from emp e,dept d
  where e.deptno = d.deptno
  and upper(e.ename) = upper(name);
  return deptname;
exception
  when no_data_found then
    raise_application_error(-20000,'Current Employee doesnot exists');
end;
/

建立带有in out参数的函数

create or replace function get_info
(name varchar2,titile out varchar2)
return varchar2
as
  deptname dept.dname%type;
begin
  select e.job,d.dname into titile,deptname
  from emp e,dept d
  where e.deptno = d.deptno
  and upper(e.ename) = upper(name);
  return deptname;
exception
  when no_data_found then
    raise_application_error(-20000,'Current Employee doesnot exists');
end;
/

函数的调用及限制
1.函数的调用(其具体调用方法参照上面的演示)
a.使用全局变量接收函数的返回值
b.使用本地变量接受函数的返回值
c.在SQL语句中直接调用函数
d.使用dbms_output调用函数
注：函数在调用的时候需要按位置指定参数，没有存储过程参数传递灵活必须具有execute函数的权限
2.函数在SQL中调用的主要场合
由于函数必须要返回数据，因此只能作为表达式的一部分调用。此外函数可以在SQL语句的以下部分调用
a. select 命令的选择列表或子查询中
b. 条件表达式where, having子句中
c. connect by ,start with ,order by 以及group by子句中
d. insert 命令的values子句中
f. update 命令的set子句中
3.函数在SQL中调用的限制
a. SQL语句中只能调用存储在服务器端的函数，而不能调用存储于客户端的函数
b. SQL语句中调用的函数只能带有输入参数IN，而不能带有输出参数OUT以及输入输出参数IN OUT
c. SQL语句中调用的函数只能使用SQL支持的标准数据类型，不能使用PL/SQL特有的类型，如boolean,table,record等
d. SQL语句中调用的函数不能包含insert ,update和delete 语句
创建一张表tb_emp

SQL> create table tb_emp as select *from emp;
Table created.

 

创建一个函数，用于删除tb_emp表中指定的empno号的雇员信息，并返回其薪资

SQL> create orreplace function del_emp
  2  (nonumber)
  3  returnnumber
  4  as
  5    v_salemp.sal%type;
  6  begin
  7    selectsal into v_sal from emp where empno = no;
  8    deletetb_emp where empno = no;
  9    returnv_sal;
 10  end;
 11  /
Function created.

使用SQL语句调用时，收到了错误信息，在内部查询内不能完成DML操作

SQL> selectdel_emp(7788) from dual;
selectdel_emp(7788) from dual
       *
ERROR at line 1:
ORA-14551: cannotperform a DML operation inside a query
ORA-06512: at"SCOTT.DEL_EMP", line 8

使用exec执行时函数被成功执行

SQL> var n1number
SQL> exec :n1:= del_emp(7788);
PL/SQL proceduresuccessfully completed.
SQL> print n1
        N1
----------
      3000

函数的管理 
函数使用了与存储过程相关的视图，可以从系统视图中获得函数的相关信息
DBA_OBJECTS
DBA_SOURCE
USER_OBJECTS
USER_SOURCE

查看函数的源码

SQL>select line,text from user_sourcewhere name='DEL_EMP' order by line
      LINETEXT
----------------------------------------------------------------------
         1function del_emp
         2(no number)
         3return number
         4as
         5   v_salemp.sal%type;
         6begin
         7   selectsal into v_sal from emp where empno = no;
         8   deletetb_emp where empno = no;
         9   returnv_sal;
        10end;
10 rows selected.

查看函数的参数信息

SQL> desc del_emp;
FUNCTION del_empRETURNS NUMBER
 ArgumentName                  Type                    In/OutDefault?
 ----------------------------------------------------- ------ --------
 NO                             NUMBER                  IN

函数与存储过程的差异
存储过程                                                           函数
----------------------------------                               -------------------------------
不能被作为表达式调用                                     只能作为表达式被调用
声明头部关键字为procedure                            声明头部关键字为function
声明头部不包含return关键字来描述返回类型         头部必须包含return关键字,且PL/SQL块中至少包含一个有效的return语句
可以通过out,in out返回零个或多个值               通过return语句返回一个与头部声明中类型一致的值,也可使用in,in out返回值
SQL语句中不可调用存储过程                        SQL语句可以调用函数
多用于数据库中完成特定的操作,如删除,更新,插入等DML操作     多用于特定的数据如选择等