Oracle学习 第13天
—— PL/SQL编程(函数 & 包)
函数
函数用于返回特定的数据。
当建立函数时,在函数头部必须包含return子句,而在函数体内必须包含return语句返回的数据。
我们可以使用create function来建立函数。
基本语法
CREATE [OR REPLACE] FUNCTION 函数名(参数 参数类型)
RETURN 数据类型 IS
变量;
BEGIN
执行语句;
END;
示例:
创建函数,传入员工姓名作为参数,返回员工年薪((月薪+奖金)* 12) ,以emp表为例。
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL> create or replace function fun_test(v_name varchar2)
2 return number is
3 v_annual_sal number;
4 begin
5 select (sal + nvl(comm, 0)) * 12 into v_annual_sal from emp where ename = v_name;
6 return v_annual_sal;
7 end;
8 /
Function created
SQL> SELECT fun_test('MARTIN') from dual;
FUN_TEST('MARTIN')
------------------
31800
包
包用于在逻辑上组合过程和函数。
包由包规范和包体两部分组成。
包规范
包规范即包定义。是用来定义方法、过程等的。
基本语法
CREATE [OR REPLACE] PACKAGE 包名 IS
PROCEDURE 过程名(变量名 变量类型);
FUNCTION 函数名(变量名 变量类型) RETURN 数据类型;
END;
示例:
SQL> create or replace package package_test is
2 procedure pro_test(v_in_name varchar2, v_in_newsal number);
3 function fun_test(v_in_name varchar2) return number;
4 end;
5 /
Package created
★ 注意:
这里的 pro_test 与 fun_test 或许与之前的例子名称有重复,但之间是没有关系的,这是一个全新的例子。
包体
包体是用来实现包中定义的方法和过程的。
包规范定义完成后,便可以调用。
但是没有包体的时候,定义的方法和过程是没有内容的。直接调用会报错(如下)
SQL> exec package_test.pro_test('SMITH', 2000);
begin package_test.pro_test('SMITH', 2000); end;
ORA-04067: 未执行, package body "SCOTT.PACKAGE_TEST" 不存在
ORA-06508: PL/SQL: 无法找到正在调用 : "SCOTT.PACKAGE_TEST" 的程序单元
ORA-06512: 在 line 1
基本语法
CREATE OR REPLACE PACKAGE BODY 包名 IS
PROCEDURE 过程名(变量名 变量类型) IS
BEGIN
执行语句
END;
FUNCTION 函数名(变量名 变量类型)
RETURN 数据类型 IS
定义变量;
BEGIN
执行语句;
END;
END;
示例:
实现刚才创建的包。实现过程修改指定员工的月薪,函数返回指定员工的年薪。
SQL> create or replace package body package_test is
2 procedure pro_test(v_in_name varchar2, v_in_newsal number) is
3 begin
4 update emp set sal = v_in_newsal where ename = v_in_name;
5 end;
6 function fun_test(v_in_name varchar2) return number is
7 v_annual_sal number;
8 begin
9 select (sal + nvl(comm, 0)) * 12 into v_annual_sal from emp where ename = v_in_name;
10 return v_annual_sal;
11 end;
12 end;
13 /
Package body created
调用包的基本语法
方案名.包名.过程名或函数名(参数值);
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL> exec package_test.pro_test('SMITH',1800);
PL/SQL procedure successfully completed
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 1800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL> select package_test.fun_test('SMITH') from dual;
PACKAGE_TEST.FUN_TEST('SMITH')
------------------------------
21600