oracle实验40：编写函数

函数

是命名的PL/SQL块
有返回值
以编译后的形式存放在数据库中用来重复执行
作为表达式的一部分被调用

创建函数的语法

CREATE [OR REPLACE] FUNCTION
[schema.] function_name[(argument [in|out|inout] type…)]RETURN returning_datatype
IS | AS
[本地变量声明]
BEGIN
-执行语句部分
[EXCEPTION]
-错误处理部分
END[function_name];

调用函数

可以在SQL语句中或Sql*Plus中调用函数

在SQL语句中调用函数好处

- 可以实现用简单的SQL语句不能实现的计算
- 提高查询的效率

在SQL语句中调用函数使用规则

- 只能使用函数，而不是过程
- 从SQL语句中调用的函数中不允许DML语句
- 形参必须为IN
- 必须返回Oracle支持数据类型，不能使用PL/SQL数据类型
- 必须有EXECUTE权限

可以在任何有效的SQL子句中中调用函数

- SELECT命令的选择列表
- WHERE和HAVING条件子句
- ORDER BY, 和GROUP BY子句
- INSERT命令的VALUES 子句
- UPDATE 命令的SET 子句

实验40：编写函数

SQL> conn scott/scott

已连接。

创建函数

SQL> create or replace function get_sal
     (v_id in emp.empno%type)
     return number
     is
     v_salary emp.sal%type :=0;
     begin
     select sal into v_salary from emp where empno=v_id;
     return(v_salary);
     end get_sal;
   /

函数已创建。

验证对象

在user_source数据字典中查询

SQL> col object_name for a50;

SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE

------------------------------------------ ---------------
DEPT                                                     TABLE
PK_DEPT                                                INDEX
EMP                                                       TABLE
PK_EMP                                                 INDEX
BONUS                                                  TABLE
SALGRADE                                            TABLE
GET_SAL                                               FUNCTION
T3                                                        TABLE
EMP2                                                    TABLE
T4                                                        TABLE
DEPT2                                                  TABLE

T2                                                       TABLE
TRG1                                                   TRIGGER
E                                                         TABLE
D_UPDATE                                           TRIGGER
TRIGGER_INSTEAD_OF                          TRIGGER
E2                                                       TABLE
D                                                        TABLE
V2                                                       VIEW
EMPVU20                                             VIEW
EMPVU10                                             VIEW
E_UPDATE                                            TRIGGER

BIN$rLjPu5EOS+61wSmunaaMcg==$0    TABLE
T1                                                      TABLE
BIN$qkEJzOqSSfGWtMsfdkcPXA==$0     TABLE
E1                                                       TABLE
V1                                                       VIEW
LOGIN_TABLE                                       TABLE

已选择28行。

SQL> set linesize 2000

查看原程序

SQL> select text from user_source;

TEXT
-------------------------------------------------------------------------------------------------
function get_sal
(v_id in emp.empno%type)
return number
is
v_salary emp.sal%type :=0;
begin
select sal into v_salary from emp where empno=v_id;
return(v_salary);
end get_sal;
trigger trg1
before insert or update of sal on emp

TEXT
-------------------------------------------------------------------------------------------------
for each row
begin
insert into t1 values(:old.sal,:new.sal);
end;
trigger d_update
after delete or update of deptno on d
for each row
begin
if (updating and :old.deptno != :new.deptno)
then update e
set deptno =:new.deptno

TEXT
-------------------------------------------------------------------------------------------------
where deptno=:old.deptno;
end if;
if deleting then
delete e where deptno=:old.deptno;
end if;
end;
trigger trigger_instead_of
instead of insert or update or delete on v1
begin
if updating then
update e1 set deptno=:new.deptno where deptno=:old.deptno;

TEXT
-------------------------------------------------------------------------------------------------
end if;
end;
trigger e_update
before update of sal on e
begin
if updating then
raise_application_error(-20001,'工资不能被改动');
end if;
end;

已选择42行。

调用函数

SQL> select get_sal(7839) from dual;

GET_SAL(7839)
-------------
         5000

删除函数

SQL> drop function get_sal;

函数已删除。

加密函数

将建立函数的文本存储为F:ora_learncreat_fun.TXT

warp iname=F:ora_learncreat_fun.TXT oname=F:ora_learncreat_fun2.TXT

SQL> @F:ora_learncreat_fun2.TXT

函数已创建。

SQL> select * from user_source where type='FUNCTION' and name='GET_SAL;

NAME TYPE LINE TEXT

----------- ----------- ----------------- --------------------------
GET_SAL FUNCTION ########## function get_sal wrapped

                                                          a000000
                                                          354
                                                          abcd
                                                          abcd
                                                          abcd
                                                          abcd
                                                          abcd
                                                          abcd
                                                          abcd
                                                          abcd
                                                          abcd
                                                          abcd
                                                          abcd
                                                          abcd
                                                          abcd
                                                          abcd
                                                          abcd
8
b7 df
UckvEIf5L+ZKc3hgtBmqbeO1pUcwg+nwmJ5qfHTp2vjVuQlapyW+G4Qc8ifjQ4+A8hu3a/Up
PPkkDbJr1mdxkAqCObntZV+xTv0k+LoOo4yrDgQanAyr3xedYsOZVJuvZVd9StipwOH29c1o
gzvd3mIWSNdcnvUhPm+4IV6790opJni1lxHw/wB6uZG96T2RvnrDPa88PmYcfPdy9HpzpkpG
fKQ=

建立索引用的函数

SQL> create or replace function f_sal
    (v1 in number)
    return number deterministic --确定性，定义中指明
    as
    begin
    if v1<1000 then return 1;
    elsif v1<2000 then return 2;
    else return 3;
    end if;
end;
   /

函数已创建。

创建索引

SQL> drop index i1;

索引已删除。

SQL> create index i1 on emp(f_sal(sal));

索引已创建。

SQL> select * from emp where f_sal(sal)=1;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM   DEPTNO
-------- ---------- --------- ---------- -------------- ---------- ---------- --------
      7369 SMITH      CLERK           7902 17-12月-80            800                  20
      7900 JAMES      CLERK           7698 03-12月-81            950                  30