实验5 过程 函数 和程序包

大型数据库技术实验报告实验课程：大型数据库技术（Oracle）专业：班级：姓名：学号：同组人：实验日期：实验项目实验八过程、函数和和程序包实验类型设计性实验目的要求⏹掌握过程的创建与调用⏹掌握PL/SQL函数的编写与调用⏹熟悉程序包的使用（实验内容及步骤）【实验步骤】8.0．实验准备工作:PL/SQL程序文件的编辑与执行1．使用文档编辑器编辑以下文件，并保存为aa.sql：2．以scott身份登录，在SQ L Plus中执行＠aa命令运行程序：成绩给出运行结果：8.1．存储过程1．最简单的存储过程(1)创建测试表drop table empl;create table empl(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 number) isbegininsert into empl values (v_id,v_name,v_salary);commit;dbms_output.put_line('数据插入成功');end;/(3) 执行(调用)存储过程exec insert_salary(6,'g',2000);(4)查询执行结果select * from empl;给出执行的最后结果：2．存储过程应用实例：列车购票与退票过程简单模拟drop table ticket;create table ticket(trainno varchar2(10),ticketnum varchar2(10),primary key (trainno,ticketnum));-- 存储过程声明create or replace procedure initisbeginfor i in 1..100loopinsert into ticket values ('1111', to_char(i, '0000')); end loop;for i in 1..100loopinsert into ticket values ('2222', to_char(i, '0000')); end loop;for i in 1..100loopinsert into ticket values ('3333', to_char(i, '0000')); end loop;isv_error_code NUMBER;v_error_message V ARCHAR2(255);beginif to_number(ticketno)<1 or to_number(ticketno)>100 thenDBMS_OUTPUT.PUT_LINE('票号不正确');return;end if;if not(trainno = '1111' or trainno = '2222' or trainno = '3333') thenDBMS_OUTPUT.PUT_LINE('车次不正确');return;end if;insert into ticket values (trainno, to_char(ticketno,'0000'));commit;DBMS_OUTPUT.PUT_LINE('退票成功,但是要扣除你%20的手续费用(嘿嘿嘿嘿...)'); exceptionwhen others thenv_error_code := SQLCODE;v_error_message := SQLERRM;DBMS_OUTPUT.PUT_LINE('退票失败,失败的原因是:' || v_error_message);end;/set serveroutput on;beginreturnTicket ('1111','00001');end;给出执行的最后结果：2．参数的使用：in/out/in out参数(1) 用两个参数：in ，out 传入一个姓名，输出：某某人你好：create or replace procedure mp(v_in varchar2,v_out out varchar2)isbeginv_out:=v_in||'你好';end;declarev_name varchar2(10);beginmp('scott',v_name);dbms_output.put_line(v_name);end;--输出：scott你好(2)-- in out类型参数create or replace procedure mp(name_in in varchar2,name_out out varchar2,name_in_out in out varchar2) isbegindbms_output.put_line(name_in);name_out:= '返回的参数name_out是' || name_in;name_in_out := 'name_in_out是' || name_in || name_in_out;end;(3)定义一个返回多个值的存储过程。

create or replace procedure p_test(name out varchar2,age out number,sex out varchar2,sal out number)isbeginname:='scott';age:=26;sex:='男';sal:=8000;end;declarev_name varchar2(20);v_age number(10);v_sex varchar2(5);v_sal number(10);beginp_test(v_name,v_age,v_sex,v_sal);dbms_output.put_line(v_name);dbms_output.put_line(v_age);dbms_output.put_line(v_sex);dbms_output.put_line(v_sal);end;给出运行结果：3.练习：根据测试表完成下列程序的编写，并给出测试结果：在过程中更新数据create table departments(DEPARTMENT_ID NUMBER(4) primary key,DEPARTMENT_NAME VARCHAR2(30),MANAGER_ID NUMBER(6),LOCATION_ID NUMBER(4));insert into departments values(1,'技术部',1,1);insert into departments values(2,'人事部',2,2);insert into departments values(3,'市场部',3,3);insert into departments values(4,'财务部',4,4);(1)根据指定的部门更新指定部门的管理者id//--参数不能指定规模大小，否测出错(2) 删除指定部门8.2．函数1.最简单的函数：--简单函数(1)创建函数create function f(name in varchar2)return varchar2 isbeginreturn name;end;(2)调用函数declarev_name varchar2(10);beginv_name:=f('scott');dbms_output.put_line(v_name);end;结果为：2．稍微复杂的函数create or replace function salarylevel(salary number) return varchar2 isbeginif salary < 1000 then return '工资太低了,要加油了';elsif salary <3000 then return '还可以,但是也要努力啊';elsif salary <5000 then return '这个还可以';else return '你的工资现在来说,是比较高了';end if;end;declarev_salarylevel varchar2(50);beginv_salarylevel := salarylevel(1000);dbms_output.put_line(v_salarylevel);end;结果为：3．定义一个函数接收三个参数算出最大值。

create or replace function f_test(num1 in number,num2 in number,num3 in number)return numberismaxnum number(20,3);beginif num1>=num2 thenif num1>=num3 thenmaxnum:=num1;elsemaxnum:=num3;end if;elsif num2>=num3 thenmaxnum:=num2;elsemaxnum:=num3;end if;return maxnum;end;匿名块测试：declarev_max number(10,2);beginv_max:=f_test(10.2,34.4,34.6);dbms_output.put_line(v_max);end;结果为：3．函数练习：建立房屋表(房屋名称，长，宽，建筑面积，使用面积)，使用find_area函数求其面积：无关的：8.3．包的声明和使用-----------包的声明和使用1-------------------------END validate_comm;---------在包体中定义的局部函数:结束------------------------完成在包体中声明的过程--------------PROCEDURE reset_comm (p_comm IN NUMBER)ISBEGINIF validate_comm(p_comm) THENg_comm:=p_comm;ELSERAISE_APPLICATION_ERROR(-20210, '不合理的表达式');END IF;END reset_comm;----------完成在包体中声明的过程:结束------------END comm_package;--测试包EXECUTE comm_package.reset_comm(1);EXECUTE comm_package.reset_comm(.33);begincomm_package.reset_comm(0.15);dbms_output.put_line('g_comm = ' || comm_package.g_comm ); end;/--以scott登陆,测试包EXECUTE comm_package.reset_comm(0.15);begincomm_package.reset_comm(0.15);dbms_output.put_line('g_comm = ' ||comm_package.g_comm ); end;/授权-----------包的声明和使用2-------------------------drop table employee;create table employee(id number (5),name varchar2(30),salary number(8,2));insert into employee values(1,'张一',3000);insert into employee values(2,'张二',3400);insert into employee values(3,'张三',5600);commit;create or replace package tax_pkg asfunction tax(v_value in number) return number; end tax_pkg;/create or replace package body tax_pkgas-------------包体中的函数执行部分--------------- function tax(v_value in number) return number isbeginif v_value < 1000 thenreturn (v_value * 0);elsif v_value < 5000 thenreturn (v_value * 0.10);elsif v_value <10000 thenreturn (v_value * 0.15);elsereturn (v_value * 0.20);end if;end tax;-------------包体中的函数:结束----------------- end tax_pkg;/-- 测试包中定义的函数select salary,tax_pkg.tax(salary) from employee;作业与思考练习题1．PL/SQL语句块的分类及构成有哪些？2．完成教材第8章实例；3．完成教材P219实验指导;4．完成以下练习：(1)．无参数的存储过程CReATE OR REPLACE PROCEDURE log_execution ISBEGININSERT INTO log_table (user_id, log_date)。