(Oracle)存储过程、存储函数和包的相关知识与实例

存储过程(procedure)

有具体的实例来学习

实例1

create or replace procedure  dept_count   --创建存储过程dept_count来统计部门个数

as

v_total number(10);

begin

 select count(*) into v_total from dept;

 dbms_output.put_line('总共有'||v_total||'个部门。');

end;

将以上代码在sql*plus中编译，一旦编译成功，就被存储在数据库中，可以被其他有权限的用户或程序来调用执行。

存储过程的执行有两种方法：

<1>

set serveroutput  on

execute dept_count;

<2>

set serveroutput  on

begin

 dept_count;

end;

实例2

create or replace procedure dept_list   --创建存储过程dept_list

as

cursor dept_coursor is select  deptno,dname from dept;

v_deptno  dept.deptno%type;

v_dname   dept.dname%type;

begin

 open dept_cursor;

 loop

  fetch dept_cursor into v_deptno,v_dname;

  exit when dept_cursor%notfound;

  dbms_output.put_line(v_deptno||'----'||v_dname);

 end loop;

 close dept_cursor;

 dept_count;                      --在存储过程dept_list中调用存储过程dept_count

end;

实例3(带in 类型的参数)

create or replace procedure  change_sal(p_empno in number default 7788,p_raise in number default 50) --创建存储过程

as

v_ename emp.ename%type;

v_sal   emp.sal%type;

begin

 update emp set sal=sal+p_raise where empno=p_empno;

 select ename,sal into v_ename,v_sal from emp where empno=p_empno;

 dbms_output.put_line('雇员'||v_ename||'的工资被改为'||v_sal);

 commit;

end;

实例4(带out 类型的参数)

创建并编译存储过程

create or replace procedure check_sal(p_empno in number,p_sal out number)

as

begin

 select sal into p_sal from emp where empno=p_empno;

end;

执行存储过程

set serveroutput on

declare

v_sal emp.sal%type;

v_empno emp.empno%type;

begin

 check_sal(7788,v_sal);         --调用存储过程check_sal

 v_empno:=7788;

 dbms_output.put_line('雇员'||v_empno||'的工资为'||v_sal);

end;

实例5(带in out 类型的参数)

创建并编译存储过程

create or replace procedure  change_phone(p_phone in out varchar2)

as

begin

p_phone:='010-'||p_phone;

end;

执行存储过程

set serveroutput on

declare

 v_phone varchar2(20);

begin

 v_phone:=88886666;

 change_phone(v_phone);        --调用存储过程change_phone

 dbms_output.put_line('修改后的电话号码为：'||v_phone);

end;

其他知识(以存储过程dept_list为例)

重新编译存储过程的用法：alter  procedure dept_list compile;

查看存储过程的参数：describe dept_list

通过数据字典user_source查询存储过程的脚本：select text from user_source where name='DEPT_LIST';

通过数据字典user_objects查询存储过程的有效性：select status from user_objects where object_name='DEPT_LIST';

通过数据字典user_dependencies查询存储过程的依赖性：select * from user_dependencies where name='DEPT_LIST';

发生编译错误时用show errors; 指令可以显示错误的详细信息。

删除存储过程：drop proceduredept_list;

存储函数(function)

实例

创建并编译函数

create or replace function get_dept_dname(P_deptno in number default 10) --存储函数的参数类型只有in 类型

return varchar2                                                          --返回值是必须的                

as

v_dname dept.dname%type;

begin

 select dname into v_dname from dept where deptno=p_deptno;

 return(v_dname);

end;

调用函数

set serveroutput on

declare

 v_no dept.deptno%type;

 v_name dept.dname%type;

begin

 v_no:=20;

 v_name:=get_dept_dname(v_no);             --调用存储函数

 dbms_output.put_line(v_no||'号部门的名称是：'||v_name);

end;

其他知识

其他的知识和存储过程是很类似的，自己根据存储过程的知识做一下尝试。

包(package)

包分为包头和包体，包头和包体可以分开编译，也可以一起编译；当分开编译时，要先编译包头，后编译包体；一起编译时，包头在前，包体在后，它们中间用”/”隔开。

实例

创建和编译package

create or replace package emp_package       --创建包头

is

procedure  emp_count;

function  get_sal(p_empno in number) return number;

end emp_package;

/                                         

create or replace package body  emp_package   --创建包体

is

/*存储过程*/

procedure  emp_count

as

v_count number(10);

begin

 select count(*) into v_count from emp;

 dbms_output.put_line('雇员表中的总人数是：'||v_count);

end emp_count;

/*存储函数*/

function  get_sal(p_empno in number)

return number

as

v_sal emp.sal%type;

begin

 select sal into v_sal from emp where empno=p_empno;

 return(v_sal);

end get_sal;

end emp_package;

执行package

set serveroutput on

declare

v_empno emp.empno%type;

v_sal   emp.sal%type;

begin

 v_empno:=7788;

 v_sal:=emp_package.get_sal(v_empno);     --调用存储函数

 dbms_output.put_line('雇员'||v_empno||'的工资为'||v_sal);

 emp_package.emp_count;                    --调用存储过程

end;