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

存储过程(procedure)

有具体的实例来学习

实例1

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

as

v_totalnumber(10);

begin

select count(*) into v_total fromdept;

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

end;

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

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

<1>

setserveroutput on

executedept_count;

<2>

setserveroutput 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_enameemp.ename%type;

v_salemp.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_salemp.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_phonevarchar2(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_dnamedept.dname%type;

begin

select dname into v_dname from dept where deptno=p_deptno;

return(v_dname);

end;

调用函数

set serveroutput on

declare

v_nodept.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)

returnnumber

as

v_salemp.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_empnoemp.empno%type;

v_salemp.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;