PL/SQL-->函数

写在前面:-->周末也不能停，写的好慢啊。才复习到函数了还是要先回顾下存储过程啊好了看下函数吧。注意：所有资料来自互联网！参考文献：《Oracle完全学习手册》

函数与存储过程的差异

存储过程 　　　　　　　　　　　　　　　 函数

----------------------------------            -------------------------------

不能被作为表达式调用                                 只能作为表达式被调用

声明头部关键字为procedure                        声明头部关键字为function

声明头部不包含return关键字来描述返回类型      头部必须包含return关键字,且PL/SQL块中至少包含一个有效的return语句

可以通过out,in out返回零个或多个值          通过return语句返回一个与头部声明中类型一致的值,也可使用in,in out返回值

SQL语句中不可调用存储过程                       SQL语句可以调用函数

多用于数据库中完成特定的操作,如删除,更新,插入等DML操作     多用于特定的数据如选择等

1.定义

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

注：函数包含RETURN子句，用来进行数据操作，并返回一个单独的函数值，函数的调用只能在一个表达式中。

再讲讲函数的优点吧

• 1.增加了代码的灵活性，可以完成一些较为复杂的任务，以及仅仅通过SQL无法完成的任务


• 2.可以直接将函数使用到where子句中来过滤数据


• 3.可以作为存储过程的参数使用，是存储过程的一种补充

2.创建及修改函数

建立函数的几点注意事项

• 1.指定参数数据类型时(argument)，不能指定其长度


• 2.函数头部必须指定return子句，函数体内至少要包含一条return语句


• 3.可以指定in参数，也可以指定out参数，以及in out 参数


• 4.可以为参数指定缺省值。指定缺省值时使用default关键字。如arg1 varchar2 default 'SCOTT'

建立函数的语法：

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 提取用户名：

create or replace function get_user
return varchar2
is
v_user varchar2(20)
begin
	select ename into v_user from emp;
	return v_user; 
end;

案例2 带有in 参数的函数：

create or replace function raise_sal(
name in varchar2)
return number
as
v_sal emp.sal%type;
begin
select sal*1.2 into v_sal from emp
where upper(ename)=upper(name)
return v_sal;
exception
  when no_data_found then
    raise_application_error(-2000,'Current Employee is not exists ');
end;

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参数的函数：

create or replace function get_info(
name varchar2,title out varchar3)
return varchar2
as
d_name detption.name%type;
begin   
select e.job,d.dname  into title,name from emp e inner join
deption d on e.deptno=d.deptno
where upper(e.name)=upper(name)
return dname;
exception
  when no_data_found then
   raise.appliction_error(-20000,'Current Employee is not exists ');
end;

--调用
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 

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

最后一个案例 带有 in out参数的函数：

CREATE OR REPLACE FUNCTION test_function(name IN VARCHAR2 default 'Tim',age IN NUMBER :=20,sex IN OUT VARCHAR2,realname OUT VARCHAR2)
RETURN VARCHAR2
IS
  rn VARCHAR2(10) default 'Tim Leung';
BEGIN
  IF name='Tim' THEN
    realname:=rn;
  END IF;
  IF sex='1' THEN
    sex:='男';
  ELSE
    sex:='女';
  END IF;
  RETURN (name || ' age:'||age||' sex:'||sex||' realname:'||realname);
END test_function;

查看函数的源码

 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;

更多参考:

PL/SQL –> PL/SQL基本知识

PL/SQL--游标

PL/SQL –> 存储过程