PL/SQL-->函数

PL/SQL-->函数

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

一、建立函数的语法

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

      建立函数的几点注意事项
              1.指定参数数据类型时(argument)，不能指定其长度
              2.函数头部必须指定return子句，函数体内至少要包含一条return语句
              3.可以指定in参数，也可以指定out参数，以及in out 参数
              4.可以为参数指定缺省值。指定缺省值时使用default关键字。如arg1 varchar2 default 'SCOTT'

二、使用函数的优点

      1.增加了代码的灵活性，可以完成一些较为复杂的任务，以及仅仅通过SQL无法完成的任务
      2.可以直接将函数使用到where子句中来过滤数据
      3.可以作为存储过程的参数使用，是存储过程的一种补充

三、建立函数

      1.建立不带参数的函数

              scott@ORCL> create or replace function get_user
                  2  return varchar2
                  3  is
                  4      v_user varchar2(20);
                  5  begin
                  6      select username into v_user from user_users;
                  7      return v_user;
                  8  end;
                  9  /

              Function created.

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

                      scott@ORCL> var v1 varchar2(20)  
                      scott@ORCL> exec :v1:=get_user

                      PL/SQL procedure successfully completed.

                      scott@ORCL> print v1;

                      V1
                      --------------------------------
                      SCOTT

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

                      scott@ORCL> set serveroutput on;

                      scott@ORCL> declare user_name varchar2(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语句中直接调用函数   

                      scott@ORCL> select get_user from dual;

                      GET_USER
                      ---------------------
                      SCOTT   

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

                      scott@ORCL> set serveroutput on;
                      scott@ORCL> exec dbms_output.put_line('Current user: '||get_user);

                      Current user: SCOTT       

      2.建立带有in 参数的函数

              scott@ORCL> create or replace function raise_sal(name in varchar2)      --注意此处定义时参数并未指定类型的长度
                  2  return number
                  3  as
                  4      v_sal emp.sal%type;
                  5  begin
                  6      select sal*1.2 into v_sal from emp
                  7      where upper(ename)=upper(name);
                  8      return v_sal;
                  9  exception
                10      when no_data_found then
                11      raise_application_error(-20000,'Current Employee is not exists');
                12  end;
                13  /

              Function created.   

              scott@ORCL> select sal,raise_sal('SCOTT') from emp where ename='SCOTT';

                            SAL RAISE_SAL('SCOTT')
              ---------- ------------------
                          3100                            3720   

              scott@ORCL> select raise_sal('Robinson') from dual;

              select raise_sal('Robinson') from dual
                            *
              ERROR at line 1:
              ORA-20000: Current Employee is not exists
              ORA-06512: at "SCOTT.GET_SAL", line 11         

      3.建立带有out参数的函数

              scott@ORCL> create or replace function get_info
                  2  (name varchar2,title out varchar2)
                  3  return varchar2
                  4  as
                  5      deptname dept.dname%type;
                  6  begin
                  7      select e.job,d.dname into title,deptname
                  8      from emp e inner join dept d
                  9          on e.deptno=d.deptno
                10      where upper(e.ename)=upper(name);
                11      return deptname;
                12  exception
                13      when no_data_found then
                14          raise_application_error(-20000,'Current Employee is not exists');
                15  end;
                16  /

              Function created.

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

              调用如下

              scott@ORCL> var job varchar2(20);
              scott@ORCL> var dname varchar2(20);
              scott@ORCL> exec :dname:=get_info('scott',:job);

              PL/SQL procedure successfully completed.

              scott@ORCL> print dname job;

              DNAME
              --------------------------------
              RESEARCH

              JOB
              --------------------------------
              ANALYST

      3.建立带有in out参数的函数

              scott@ORCL> create or replace function comp
                  2  (num1 number,num2 in out number)
                  3  return number
                  4  as
                  5      v_result number(6);
                  6      v_remainder number;
                  7  begin
                  8      v_result:=num1*num2;
                  9      v_remainder:=mod(num1,num2);
                10      num2:=v_remainder;
                11      return v_result;
                12  exception
                13      when zero_divide then
                14          raise_application_error(-20000,'Divison by zero');
                15  end;
                16  /

              Function created.
           
              scott@ORCL> var result1 number;
              scott@ORCL> var result2 number;
              scott@ORCL> exec :result2:=10
           
              PL/SQL procedure successfully completed.

              scott@ORCL> exec :result1:=comp(16,:result2);

              PL/SQL procedure successfully completed.

              scott@ORCL> print result1 result2;

                    RESULT1
              ----------
                            160

                    RESULT2
              ----------
                                6

四、函数的调用及限制

      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 语句 (注意理解,这里的DML指的是函数中使用DML，非外部调用SQL语句)

              下面演示SQL调用时不能完成DML操作示例

              --创建一张表tb_emp

                      scott@ORCL> create table tb_emp as select * from emp;

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

                      scott@ORCL> create or replace function delete_oper(no number)
                          2  return number
                          3  as
                          4      v_sal emp.sal%type;
                          5      begin
                          6      select sal into v_sal from tb_emp where empno=no;
                          7      delete from tb_emp where empno=no;
                          8      return v_sal;
                          9      end;
                        10  /

                      Function created.

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

                      scott@ORCL> select delete_oper(7788) from dual;

                      select delete_oper(7788) from dual
                                    *
                      ERROR at line 1:
                      ORA-14551: cannot perform a DML operation inside a query
                      ORA-06512: at "SCOTT.DELETE_OPER", line 7               

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

                      scott@ORCL> var v_no number;

                      scott@ORCL> exec :v_no:=delete_oper(7788);

                      PL/SQL procedure successfully completed.

                      scott@ORCL> print v_no;

                                  V_NO
                      ----------
                                  3100

                      scott@ORCL> select * from tb_emp where empno=7788;
                      no rows selected     

              --下面的演示表明，不能使用DML语句来调用函数

                      scott@ORCL> update emp set sal=raise_sal('SCOTT') where ename='SCOTT';
                      update emp set sal=raise_sal('SCOTT') where ename='SCOTT'
                                                            *
                      ERROR at line 1:
                      ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
                      ORA-06512: at "SCOTT.RAISE_SAL", line 6       

                     

五、函数的管理  

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

              DBA_OBJECTS
              DBA_SOURCE
              USER_OBJECTS
              USER_SOURCE

      --查看函数的源码

              scott@ORCL> select text from user_source where name='DELETE_OPER' order by line;

              TEXT
              ------------------------------------------------------------
              function delete_oper(no number)
                  return number
                as
                  v_sal emp.sal%type;
                  begin
                  select sal into v_sal from tb_emp where empno=no;
                  delete from tb_emp where empno=no;
                commit;
                    return v_sal;
                  end;

      --查看函数的参数信息

              scott@ORCL> desc delete_oper;

              FUNCTION delete_oper RETURNS NUMBER

                Argument Name                                  Type                                      In/Out Default?
                ------------------------------ ----------------------- ------ --------
                NO                                                        NUMBER                                  IN  

六、函数与存储过程的差异

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

转自：http://blog.csdn.net/robinson_0612/article/details/6069531