大型数据库技术实验

1、实验7（2） 过程、函数和和程序包 0910012115 号林丹萍 ：学姓名： 班级： 09信计1班 专业： 信息与计算科学 2012-5-2 期：组人： 实验无 日同 【实验目的与要求】 掌握过程的创建与调用 ? 函数的编写与调用掌握PL/SQL ? 熟悉程序包的使用 ? 【实验内容与步骤】 程序文件的编辑与执行实验准备工作:PL/SQL7.0 ：使用文档编辑器编辑以下文件，并保存为aa.sql1 scott用户下1） 建立一个表在Create table errors (error_text varchar2(20) 下面bin将其保存在oracle目录2）建立一个aa.sql decla

2、re 。empno%typeperson_no emp. 。%typeperson_name emp.enamebegin person_name person_no, empno,ename intoselect emp from 。>7788where empnoexception then no_data_found when 。)(insert into errors values'no data found'then too_many_rows when 。)insert into errors values('too many rows' 。e

3、nd select * from errors3）。 aa命令运行程序：L Plusscott2以身份登录，在SQ中执行 给出运行结果： 7.1存储过程 最简单的存储过程编写与执行1 创建测试表(1) 。drop table Exam_Tablecreate table Exam_Table( e_id number(5), e_name varchar2(20), e_salary number(8,2) )。 (2)创建存储过程create or replace procedure insert_salary (v_id number,v_name varchar2,v_salary nu

4、mber) is begin insert into Exam_Table values (v_id,v_name,v_salary)。 。 commit 。 dbms_output.put_line('数据插入成功') end。/ )存储过程调用(3) 执行( exec insert_salary(6,'g',2000)。 查询执行结果(4) select * from Exam_Table。 给出执行的最后结果： 参数2参数的使用：in/out/in out 阅读以下程序，理解不同类型参数使用的不同，运行程序，给出运行结果。 传入一个姓名，输出：某某人你好：

5、in ,out (1) 用两个参数mp 创建存储过程-create or replace procedure mp(v_in varchar2,v_out out varchar2) is begin '。 v_out:=v_in|'你好 end。mp -调用存储过程set serveroutput on declare v_name varchar2(10)。begin 。 mp('scott',v_name) 。 dbms_output.put_line(v_name) 你好输出：scottend。- 另一种方式调用存储过程- 或者： 命令调用过程使用中 e

6、xecute在sqlplus命令绑定参数值，定义一个接受返回值的变variable -用Variable g_name varchar2(10)。 量 ,:g_name)'。helloExecute mp( 给出运行结果： (2)- in out类型参数 create or replace procedure mp(name_in in varchar2, name_out out varchar2, name_in_out in out varchar2) is begin dbms_output.put_line(name_in)。 name_out := '返回的参数na

7、me_out是' | name_in。 name_in_out := 'name_in_out是' | name_in | name_in_out。 end。 -调用存储过程 variable g_name_out varchar2(80)。 variable g_name_in_out varchar2(80)。 execute mp('scott2',:g_name_out,:g_name_in_out)。 -给出运行结果 Print g_name_out。 Print g_name_in_out。 给出运行结果： 7.2函数 1.最简单的函数： -

8、简单函数 (1)创建函数 create function f(name in varchar2)return varchar2 is begin return name。 end。 (2)调用函数 Set serveroutput on declare v_name varchar2(10)。 begin v_name:=f('scott')。 dbms_output.put_line(v_name)。 end。 给出运行结果： 2稍微复杂的函数 -编写函数 create or replace function salarylevel(salary number) return

9、 varchar2 is begin if salary < 1000 then return 工资太低了,要加油了'。 -elsif salary <3000 then return '还可以,但是也要努力啊'。 -elsif salary <5000 then return '这个还可以'。 else return '你的工资现在来说,是比较高了'。 end if。 end。 -调用函数 declare v_salarylevel varchar2(50)。 begin v_salarylevel := salary

10、level(1000)。 dbms_output.put_line(v_salarylevel)。 end。 给出运行结果： 7.3包的声明和使用 阅读以下程序，理解包的声明和使用。 -包的声明和使用1- DROP TABLE Exam_Emps。 CREATE TABLE Exam_Emps( id number(5) primary key, name varchar2(30), commission_pct number(3,2 ) )。 insert into Exam_Emps values (1,'张一',0.13)。 insert into Exam_Emps v

11、alues (2,'张二',0.23)。 insert into Exam_Emps values (3,'张三',0.33)。 insert into Exam_Emps values (4,'张四',0.43)。 。commit select * from Exam_Emps。 创建包头-CREATE OR REPLACE PACKAGE comm_package IS 。 g_comm NUMBER := 0.10 。 PROCEDURE reset_comm (p_comm IN NUMBER) 。END comm_package/ -创

12、建包体 CREATE OR REPLACE PACKAGE BODY comm_package IS -在包体中定义的局部函数- /* 如果输入的参数p_comm大于Exam_Emps表中最大的commission_pct 字段,则函数返回FALSE,否则函数返回TRUE */ FUNCTION validate_comm (p_comm IN NUMBER) RETURN BOOLEAN IS v_max_comm NUMBER。 BEGIN SELECT MAX(commission_pct) INTO v_max_comm FROM Exam_Emps。 IF p_comm > v

13、_max_comm THEN RETURN FALSE 。 ELSE RETURN TRUE 。 END IF。 END validate_comm。 -在包体中定义的局部函数:结束- -完成在包体中声明的过程- PROCEDURE reset_comm (p_comm IN NUMBER) IS BEGIN IF validate_comm(p_comm) THEN g_comm:=p_comm。 ELSE RAISE_APPLICATION_ERROR(-20210, '不合理的表达式')。 END IF。 END reset_comm。 -完成在包体中声明的过程:结束-

14、END comm_package。 -测试包 EXECUTE comm_package.reset_comm(1)。 EXECUTE comm_package.reset_comm(.33)。 给出运行结果： begin comm_package.reset_comm(0.15)。 。 dbms_output.put_line('g_comm = ' | comm_package.g_comm ) 。end/ 给出运行结果： 2- -包的声明和使用 drop table Exam_Emp。create table Exam_Emp( id number (5), name va

15、rchar2(30), salary number(8,2) )。 。张一insert into Exam_Emp values(1,'',3000) 。insert into Exam_Emp values(2,'张二',3400) 张三',5600)。insert into Exam_Emp values(3,' commit。 create or replace package tax_pkg as 。 function tax(v_value in number) return number end tax_pkg。/ create or replace package body tax_pkg as - -包体中的函数执行部分 function tax(v_value in number) return number is begin if v_value < 1000 then 。 return (v_value * 0)elsif v