Oracle学习笔记(7) 开发PLSQL子程序和包

 这是第七章的学习笔记，学习完第六章的动态SQL之后，开始要学习子程序和包的使用了……，希望大家能多给俺一些支持啊！ 
    编程时使用的工具是PLSQL Developer 7.1.4 

在CMD下执行PL/SQL： 
可以用exec或者call调用存储过程 
定义变量时要用var，如：var username varchar2(20); 
在调用变量时必须在变量名的前面加上双引号，如：exec :username := 'user1'; 
在调用存储过程时也要在变量名的前面加上双引号，如exec compute(:n1,:n2); 
可以使用show error输出当前错误 
查看存储过程的源代码: 

Sql代码 

1. select text from user_source where name='COMPUTE';  

创建带输入、输出参数的存储过程: 
在创建存储过程时可以定义in(输入参数，默认)，out(输出参数)和in out(输入输出参数)三种参数 

Sql代码 

1. create or replace procedure compute(num1 in out number,num2 in out number) is  
2.    n1 number(10,2);  
3.    n2 number(10,2);  
4. begin  
5.    n1 := num1/num2;  
6.    n2 := mod(num1,num2);  
7.    -- 给要返回的变量赋值  
8.    num1 := n1;  
9.    num2 := n2;  
10. end;  
11. /  

开发函数: 
在指定函数的参数类型时，不能指定其长度；Return子句用于指定函数返回值的数据类型 
IS或AS用于开始一个PL/SQL块(替代了declare) 
在函数头部必须有Return子句，在函数体内至少要包含一个Return子句 
在创函数时可以指定in(输入参数，默认)，out(输出参数)和in out(输入输出参数)三种参数 

Sql代码 

1. -- 创建带输入输出参数的函数  
2. create or replace function get_result(num1 number,num2 in out number)  
3. return number is  
4.    n_result number(6);  
5.    n_remainder number;  
6. begin  
7.    n_result := num1/num2;  
8.    n_remainder := mod(num1,num2);  
9.    num2 := n_remainder;  
10.    return n_result;  
11. end;  
12. /  

开发包: 
创建包规范，相当于Java中定义接口，在这里定义的变量、函数和子程序都是公有的 

Sql代码 

1. create or replace package emp_package is  
2.    -- 定义公共变量  
3.    n_temp number(10,2) := 888.888;  
4.    -- 定义添加员工的存储过程  
5.    procedure add_emp(empno number,name varchar2,job varchar2,manager varchar2,hiredate varchar2,salary number,commision varchar2,deptno number);  
6.    -- 定义解雇员工的存储过程  
7.    procedure fire_emp(n_empno number);  
8.    -- 定义获得指定员工编号的员工工资的函数  
9.    function get_salary(n_empno number) return number;  
10. end emp_package;  
11. /  
12. -- 创建包体，相当于Java中的接口实现类  
13. create or replace package body emp_package is  
14.    -- 定义一个用来验证员工编号是否存在的私有函数  
15.    function validate_empno(n_empno number) return boolean is  
16.       n_temp employee.empno%type;  
17.    begin  
18.       select empno into n_temp from employee where empno = n_empno;  
19.       return true;  
20.    exception  
21.       when no_data_found then return false;  
22.       when others then return false;        
23.    end;  
24.    -- 实现添加员工的存储过程  
25.    procedure add_emp(empno number,name varchar2,job varchar2,manager varchar2,hiredate varchar2,  
26.                      salary number,commision varchar2,deptno number) is  
27.    begin   
28.       if validate_empno(empno) then  
29.          raise_application_error(-20001,'编号为' || empno || '的员工已经存在！');  
30.       else  
31.          insert into employee values(empno,name,job,manager,hiredate,salary,commision,deptno);  
32.          commit;  
33.       end if;  
34.     end;  
35.     -- 实现解雇员工的存储过程  
36.     procedure fire_emp(n_empno number) is  
37.     begin  
38.        if validate_empno(n_empno) then  
39.           delete from employee where empno = n_empno;  
40.           commit;  
41.        else  
42.           raise_application_error(-20003,'编号为' || n_empno || '的员工不存在！');  
43.        end if;  
44.     end;  
45.     -- 实现获得指定员工编号的员工工资的函数  
46.     function get_salary(n_empno number) return number is  
47.        n_salary employee.salary%type;  
48.     begin  
49.        if validate_empno(n_empno) then  
50.           select salary into n_salary from employee where empno = n_empno;  
51.           return n_salary;  
52.        else          
53.           raise_application_error(-20004,'编号为' || n_empno || '的员工不存在！');  
54.        end if;  
55.     end;     
56. end emp_package;  
57. /  

测试块: 

Sql代码 

1. declare  
2.    n_1 number(10,2);  
3.    n_2 number(10,2);  
4. begin  
5.    -- 测试存储过程compute  
6.    n_1 := 20;  
7.    n_2 := 8;  
8.    -- 位置传递  
9.    --compute(n_1,n_2);  
10.    -- 名称传递  
11.    compute(num1 => n_1,num2 => n_2);  
12.    dbms_output.put_line('n_1=' || n_1 || '   n_2=' || n_2);  
13.    -- 测试函数get_result  
14.    n_1 := 20;  
15.    n_2 := 8;  
16.    n_2 := get_result(num1 => 100,num2 => n_1);  
17.    dbms_output.put_line('n_1=' || n_1 || '   n_2=' || n_2);  
18.    -- 测试包  
19.    dbms_output.put_line('包中定义的公共变量：' || emp_package.n_temp);  
20.    emp_package.add_emp(22,'李明','人事助理',0,'2006-08-15',4200,'人事',1);  
21.    emp_package.fire_emp(2);  
22.    dbms_output.put_line('编号为1的员工工资是：' || emp_package.get_salary(1));  
23. end;  
24. /  

存储过程 VS 函数: 
  
存储与过程相同点: 
都有输入、输出、输入输出参数 

不同点: 
函数必须有返回值 
函数不能修改数据 

用途: 
存储过程：主要用数据修改和业务处理 
函    数：只能用于数据计算 


重载子程序： 
在一个包中定义的两个子程序名称相同，而参数不同。 
在调用时Oracle将自动根据参数类型调用对应子程序。 

限制： 
如果两个子程序仅参数名称不同，则不算重载 
参数类型相同，而返回类型不同，不算重载 
重载的参数必须是基本类型。