PLSQL——07、函数

函数特性：一定要返回值并且只能返回一个值。

练习1：

CREATE OR REPLACE FUNCTION get_sal
(p_id IN emp.empno%TYPE)
RETURN NUMBER    --形式参数
IS | AS
  v_salary emp.sal%TYPE :=0;
BEGIN
  SELECT sal
   INTO v_salary
  FROM emp
  WHERE empno = p_id;
  RETURN v_salary;
END get_sal;
/

------function调用
----方法1：
VARIABLE g_salary NUMBER
EXECUTE :g_salary := get_sal(7369);
PRINT g_salary

----方法2：
declare
  v_sal number;
begin
  v_sal:=get_sal(7788);
  dbms_output.put_line(v_sal);
end;
/

----方法3：
select get_sal(7369) from dual;

练习2：

CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER)
RETURN NUMBER 
IS
BEGIN
  RETURN (p_value * 0.08);
END tax;
/

function调用
SELECT empno, ename, sal, tax(sal)
FROM emp
WHERE deptno = 10;

确定返回值函数与效率

首先准备数据

----1、准备实验用表:
create table t1 (id varchar2(10));

begin
  for i in 1..10 loop
   insert into t1 values (‘A‘);
  end loop;
  commit;
end;
/

----2、创建包声明全局变量，用来做函数调用次数的计数器
create or replace package p as
g_no number default 0;
end;
/

----3、显示全局变量的值
exec dbms_output.put_line(chr(10)||p.g_no);

创建一个普通函数

create or replace function wait_row(i_v   in varchar2,
                                    i_sec in number default 5)
  return varchar2
  parallel_enable 
as
begin
  p.g_no := p.g_no 1;--函数每调用一次计数器加1
  sys.dbms_lock.sleep(i_sec);--休眠的时间值
  return i_v;
end;
/

----调用函数:
select wait_row(id,0.1) from t1;

通过显示全局变量的当前值查看普通函数的工作次数

exec dbms_output.put_line(chr(10)||p.g_no); 

10 --> 函数调用10次

将全局变量归零: exec p.g_no := 0;

创建确定返回值函数

create or replace function wait_row(i_v   in varchar2,
                                    i_sec in number default 5)
  return varchar2 deterministic
  parallel_enable 
as
begin
  p.g_no := p.g_no 1;
  sys.dbms_lock.sleep(i_sec);
  return i_v;
end;
/

----再次调用函数:
select wait_row(id,0.1) from t1;

通过显示全局变量的当前值查看确定返回值函数的工作次数

exec dbms_output.put_line(chr(10)||p.g_no); 

2 --> 函数调用2次

总结：说明在形式参数取值相同的时候，确定返回值函数的调用次数比普通函数少！意味着确定返回值函数的效率会高于普通函数！确定性函数的性能好处是如果使用相同的输入调用函数两次，Oracle可以记住第一个调用的结果因此避免在第二次执行时再次调用。在建立函数索引的时候,自定义的函数需要加Deterministic。