Oracle存储过程的设计及应用

实验五Oracle存储过程的设计及应用

实验目的：

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。存储过程分为两类：1.系统提供的存储过程；2.用户自定义存储过程。

存储过程具有的优点：1.存储过程允许标准组件式编程；2.存储过程能够实现较快的执行速度；3.存储过程能够减少网络流量；4.存储过程可被作为一种安全机制来充分利用。

本次实验了解Oracle存储过程的创建、修改和删除的方法和步骤，掌握在Oracle SQL Developer中对存储过程的进行创建、修改和删除，掌握在SQL*plus 中调用带参数和不带参数的存储过程。

实验步骤：

1.在SQL Developer中运行FTP服务器上下载的employees.sql，建立雇员表，并向表中插入测试数据。

2．Sqlplus中调用存储过程：

首先将文件夹“sqlplus连接配置文件”中的两个文件复制到

C:\app\Administrator\product\11.1.0\client_2\network\admin，覆盖已有件。

在命令行输入sqlplus B8120108/***@orcl,如图5-6所示(用户名和密码均为test)：

在Sqlplus中执行set serveroutput on;把打印缓冲区打开，使得dbms_output.put_line输出的文字可以显示在屏幕上。

Sqlplus中执行execute 存储过程名字;调用前面建立的FIRSTPROC,如execute FIRSTPROC;结果如图5-7所示:

2．使用带参数的存储过程。

(1).在SQL Worksheet中打开employees.sql,如图5-8所示：

然后点击(Run Script)按钮，运行创建表EMPLOYEES和向表中插入数据的SQL语句，如图5-9所示：

在SQL Worksheet中输入以下PL/SQL语句创建带参数的存储过程:

create or replace procedure withpara ( fname in varchar2,ret out

varchar2 )

as

begin

select LAST_NAME into ret from EMPLOYEES where

EMPLOYEES.FIRST_NAME=fname;

DBMS_OUTPUT.PUT_LINE(ret);

EXCEPTION WHEN NO_DATA_FOUND THEN

ret:='查无此人。';

DBMS_OUTPUT.PUT_LINE(ret);

end;

此存储过程的作用是通过输入frist name,查询last name,并显示last name。然后点击(Run Script)按钮，运行脚本。如图5-10所示。

(2)Sqlplus中调用存储过程：

在Sqlplus中执行set serveroutput on;把打印缓冲区打开，使得dbms_output.put_line输出的文字可以显示在屏幕上。

在Sqlplus中执行varoutput_var varchar2(20);定义一个输出变量inputvar。

Sqlplus中执行execute 存储过程名字(参数); 输入在employees表中不存在的First name，调用前面建立的存储过程Withpara,如execute withpara('NO',:output_var);结果如图5-11所示: