Oracle存储过程及函数
示例所要用到的表结构及相关数据：
1 SQL> desc emp;
2 Name Type Nullable Default Comments
3 -------- ------------ -------- ------- --------
4 EMPNO NUMBER(4)
5 ENAME VARCHAR2(10) Y
6 JOB VARCHAR2(9) Y
7 MGR NUMBER(4) Y
8 HIREDATE DATE Y
9 SAL NUMBER(7,2) Y
10 COMM NUMBER(7,2) Y
11 DEPTNO NUMBER(2) Y
12
13 SQL> select * from emp;
14
15 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
16 ----- ---------- --------- ----- ----------- --------- --------- ------
17 7369 SMITH CLERK 7902 1980/12/17 880.00 20
18 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
19 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
20 7566 JONES MANAGER 7839 1981/4/2 2975.00 20
21 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
22 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
23 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
24 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
25 7839 KING PRESIDENT 1981/11/17 5000.00 10
26 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
27 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
28 7900 JAMES CLERK 7698 1981/12/3 950.00 30
29 7902 FORD ANALYST 7566 1981/12/3 3000.00 20
30 7934 MILLER CLERK 7782 1982/1/23 1300.00 10
31
32 14 rows selected
2 Name Type Nullable Default Comments
3 -------- ------------ -------- ------- --------
4 EMPNO NUMBER(4)
5 ENAME VARCHAR2(10) Y
6 JOB VARCHAR2(9) Y
7 MGR NUMBER(4) Y
8 HIREDATE DATE Y
9 SAL NUMBER(7,2) Y
10 COMM NUMBER(7,2) Y
11 DEPTNO NUMBER(2) Y
12
13 SQL> select * from emp;
14
15 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
16 ----- ---------- --------- ----- ----------- --------- --------- ------
17 7369 SMITH CLERK 7902 1980/12/17 880.00 20
18 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
19 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
20 7566 JONES MANAGER 7839 1981/4/2 2975.00 20
21 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
22 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
23 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
24 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
25 7839 KING PRESIDENT 1981/11/17 5000.00 10
26 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
27 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
28 7900 JAMES CLERK 7698 1981/12/3 950.00 30
29 7902 FORD ANALYST 7566 1981/12/3 3000.00 20
30 7934 MILLER CLERK 7782 1982/1/23 1300.00 10
31
32 14 rows selected
创建过程语句如下：
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
procedure_body
END procedure_name;
- OR REPLACE 说明如果过程已经存在，则替换已有的过程
- procedure_name 指定过程名。
- parameter_name 指定传递给过程的函数名。可以向一个过程传递多个参数。
- IN | OUT | IN OUT 定义了参数的模式。每一个参数都可以选择下列模式之一:
- IN 是参数的默认模式。这种模式定义的参数在程序运行的时候已经具有值，在过程体中不能改变IN参数的值。
- OUT模式定义的参数只在过程体内部赋值。
- IN OUT模式定义的参数当过程运行是可能已经具有值，但是在过程体中也可以修改此值。
- type指定参数的类型
- procedure_body包含过程的实际代码。
eg: 根据传入的员工姓名将对应员工的薪水进行调整，调整规则为如果有传入薪水值则以传入值为标准，如果没有传入薪水值则在原来的幅度上上涨10%，最后返回员工的boss的姓名。
1 create or replace procedure procedure_demo (
2 vname in varchar2,
3 vbossname out varchar2,
4 vsal in out number
5 )
6 as
7 v_count number;
8 begin
9 select count(1) into v_count from emp t where t.ename=vname;
10
11 /*如果输入姓名有误则返回*/
12 if v_count = 0 then
13 dbms_output.put_line('输入姓名有误.');
14 return;
15 end if;
16
17 /*如果提供了salary则将对应员工的薪水改为传入的薪水值否则在原来的基础上添加10%*/
18 if vsal is null then
19 update emp t set t.sal=t.sal*1.1 where t.ename=vname;
20 else
21 update emp t set t.sal=vsal where t.ename=vname;
22 end if;
23
24 select case when t2.ename is null then '自己' else t2.ename end , t1.sal into vbossname, vsal from emp t1, emp t2 where t1.mgr=t2.empno(+) and t1.ename=vname ;
25
26 dbms_output.put_line('员工' || vname || '薪水是' || vsal || '他的boss是' || vbossname);
27
28 commit;
29
30 end procedure_demo;
2 vname in varchar2,
3 vbossname out varchar2,
4 vsal in out number
5 )
6 as
7 v_count number;
8 begin
9 select count(1) into v_count from emp t where t.ename=vname;
10
11 /*如果输入姓名有误则返回*/
12 if v_count = 0 then
13 dbms_output.put_line('输入姓名有误.');
14 return;
15 end if;
16
17 /*如果提供了salary则将对应员工的薪水改为传入的薪水值否则在原来的基础上添加10%*/
18 if vsal is null then
19 update emp t set t.sal=t.sal*1.1 where t.ename=vname;
20 else
21 update emp t set t.sal=vsal where t.ename=vname;
22 end if;
23
24 select case when t2.ename is null then '自己' else t2.ename end , t1.sal into vbossname, vsal from emp t1, emp t2 where t1.mgr=t2.empno(+) and t1.ename=vname ;
25
26 dbms_output.put_line('员工' || vname || '薪水是' || vsal || '他的boss是' || vbossname);
27
28 commit;
29
30 end procedure_demo;
调用过程
CALL procure_name(parameter1, parameter2,...);
1 SQL> var ina varchar2;
2 SQL> var outb varchar2;
3 SQL> var inoutc number;
4 SQL> exec :ina := 'SMITH';
5
6 PL/SQL procedure successfully completed
7 ina
8 ---------
9 SMITH
10
11 SQL> exec :inoutc := 800;
12
13 PL/SQL procedure successfully completed
14 inoutc
15 ---------
16 800
17
18 SQL> call procedure_demo(vname => :ina, vbossname => :outb, vsal => :inoutc);
19
20 Method called
21 ina
22 ---------
23 SMITH
24 outb
25 ---------
26 FORD
27 inoutc
28 ---------
29 800
30
31 SQL> select * from emp t where t.ename='SMITH';
32
33 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
34 ----- ---------- --------- ----- ----------- --------- --------- ------
35 7369 SMITH CLERK 7902 1980/12/17 800.00 20
36
2 SQL> var outb varchar2;
3 SQL> var inoutc number;
4 SQL> exec :ina := 'SMITH';
5
6 PL/SQL procedure successfully completed
7 ina
8 ---------
9 SMITH
10
11 SQL> exec :inoutc := 800;
12
13 PL/SQL procedure successfully completed
14 inoutc
15 ---------
16 800
17
18 SQL> call procedure_demo(vname => :ina, vbossname => :outb, vsal => :inoutc);
19
20 Method called
21 ina
22 ---------
23 SMITH
24 outb
25 ---------
26 FORD
27 inoutc
28 ---------
29 800
30
31 SQL> select * from emp t where t.ename='SMITH';
32
33 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
34 ----- ---------- --------- ----- ----------- --------- --------- ------
35 7369 SMITH CLERK 7902 1980/12/17 800.00 20
36
删除过程
DROP PROCEDURE procedure_name;
eg:
1
2 SQL> drop procedure procedure_demo;
3
4 Procedure dropped
5
6 SQL> view procedure_demo;
7 Unknown object: procedure_demo
8
9 SQL>
2 SQL> drop procedure procedure_demo;
3
4 Procedure dropped
5
6 SQL> view procedure_demo;
7 Unknown object: procedure_demo
8
9 SQL>
创建函数语句如下：
CREATE [OR REPLACE] FUNCTION fuction_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN type
{IS | AS}
BEGIN
function_body
END function_name;
- OR REPLACE表示如果函数已经存在，则替换现有的函数。
- function_name指定函数名。
- parameter_name指定传递给函数的参数名。
- IN | OUT | IN OUT指定参数的模式。
- type 指定参数的类型。
- function_body 包含函数的实际代码。函数体不像过程体，它必须有返回值，其类型在RETURN子句中指定。
eg:查出emp表中的总人数。
1 create or replace function tarbitrary return number
2
3 as
4 v_count number;
5 begin
6 select count(1) into v_count from emp;
7
8 return v_count;
9 end;
10 /
2
3 as
4 v_count number;
5 begin
6 select count(1) into v_count from emp;
7
8 return v_count;
9 end;
10 /
调用函数
select function_name([parmater1, parameter2, ...]) from dual;
1 SQL> select tarbitrary() from dual;
2
3 TARBITRARY()
4 ------------
5 14
2
3 TARBITRARY()
4 ------------
5 14
删除函数
DROP FUNCTION function_name;
1 SQL> drop function tarbitrary;
2
3 Function dropped
2
3 Function dropped
