实验四 过程、函数、触发器和程序包.doc
实验四 过程、函数、触发器和程序包实验目的：（1）了解存储过程的作用；（2）了解函数的作用；（3） 掌握存储过程的创建；（4）掌握存储过程的调用；（5）掌握函数的建立；（6）掌握函数的调用。 (7) 掌握触发器的基本作用及使用方法。 (8) 掌握触发器的建立、修改、查看、删除操作。实验内容：一、过程、歪数（1）无参数的存储过程创建一个存储过程 proc_execution，要求能够将empno为9010的员工姓名更改为'yourname',并调用执行这个存储过程。（2）带输入参数的存储过程创建一个存储过程，参数为员工编号，该存储过程能够删除指定的员工信息。请编写并调用该存储过程。（3）带输入输出的存储过程创建一个存储过程，要求能够查询并返回EMP中给定职工号的姓名、工资和佣金。请编写并调用该存储过程。（4）带输入参数的函数：创建一个函数get_sal,要求能够查询并返回EMP中给定职工号的工资，请编写该函数并正确调用。（5）用异常处理完善程序请将第任务1中的程序完善，如果输入的员工号不存在，则执行异常处理，显示“此员工不存在”。（6）创建函数，实现功能为：在scott.emp表和scott.dept表中查询出任意给定职工号的职工姓名及职工所在部门的名称。（7）对存储过程、函数及触发器实现查看、修改、删除等基本操作。二、触发器1. 以SCOTT帐号登录，完成以下操作：(1)利用以下SQL语句，创建emp_copy表和emp_bak表(2)在emp表中创建一个触发器tg_insert_emp，当向emp表中添加一条记录时，自动向emp_copy表也自动添加一条记录。当向emp表中更新一条记录时，自动更新emp_copy表中相关记录。(3)当删除emp表中一条记录时，自动删除emp_copy表中相关记录，并且将删除的记录自动添加到emp_bak表。.请编写相关触发器，完成下列操作：利用下面的命令创建一个表OP_LOGCREATE OR REPLACE TABLE op_log(ID VARCHAR2(20) PRIMARY KEY, op_dae date, op_type varchar2(200), op_user varhcar2(50);(1)编写一个触发器，当系统关闭时，记录系统关闭的时间及操作用户。(2)编写一个触发器，当系统启动时，记录系统启动的时间及操作用户。(3)编写一个触发器，当用户登录时，记录用户登录的时间，及用户名称。实验步骤与调试过程：1.1创建一个存储过程 proc_execution，将empno为9010的员工姓名更改为'yourname',并调用执行这个存储过程。1.2创建一个存储过程，参数为员工编号，存储过程能够删除指定的员工信息1.3创建一个存储过程，查询并返回EMP中给定职工号的姓名、工资和佣金1.4创建一个函数get_sal，查询并返回EMP中给定职工号的工资1.5创建一个存储过程proc_execution，将empno为9010的员工姓名更改为'yourname'，使用if语句判断，如果输入的员工号不存在，则执行异常处理，显示“此员工不存在”。1.6创建函数，在scott.emp表和scott.dept表中查询出任意给定职工号的职工姓名及职工所在部门的名称。1.7在存储过程、函数及触发器实现查看、修改、删除等基本操作。1.利用SQL语句，创建emp_copy表和emp_bak表2.在emp表中创建一个触发器tg_insert_emp，当向emp表中添加一条记录时，自动向emp_copy表也自动添加一条记录。当向emp表中更新一条记录时，自动更新emp_copy表中相关记录。3.当删除emp表中一条记录时，自动删除emp_copy表中相关记录，并且将删除的记录自动添加到emp_bak表。4.编写一个触发器，当系统关闭时，记录系统关闭的时间及操作用户，当系统启动时，记录系统启动的时间及操作用户，以及当用户登录时，记录用户登录的时间，及用户名称。实验结果：通过创建一个存储过程，了解在存储过程中函数的作用，对数据表的数据进行修改、删除操作，并对存储过程进行调用，了解触发器的基本作用及使用方法，进行触发器的建立、修改、查看、删除操作。疑难小结：1. 存储过程中函数的使用2. 职工信息的修改删除操作3. 触发器的记录事件4. 创建触发器的登录和退出触发器主要算法和程序清单：(1)无参数的存储过程创建一个存储过程 proc_execution，要求能够将empno为9010的员工姓名更改为'yourname',并调用执行这个存储过程。Create Procedure proc_execution Select S.empno, S.sname From S.empno 9010 where S.empno=yourname END proc_execution (2)带输入参数的存储过程创建一个存储过程，参数为员工编号，该存储过程能够删除指定的员工信息。请编写并调用该存储过程。CREATE OR REPLACE PROCEDURE proc_execution（in_id IN VARCHAR2,out_wage OUT VARCHAR2)ASBEGINSELECT wage S.empnointo out_wage DELETE S.empnoEND proc_execution；（3）带输入输出的存储过程创建一个存储过程，要求能够查询并返回EMP中给定职工号的姓名、工资和佣金。请编写并调用该存储过程。CREATE OR REPLACE PROCEDURE proc_execution（in_id IN VARCHAR2,out_wage OUT VARCHAR2)ASBEGINSELECT wagerrrinto out_wage FROM s.wageWHERE id s.name=in_id;END proc_execution；（4）带输入参数的函数：创建一个函数get_sal,要求能够查询并返回EMP中给定职工号的工资，请编写该函数并正确调用。create or replace function get_sal (v_emp_no in emp.wage) return numberisv_emp_deptno emp.wage;beginselect get_sal(v_emp_no) from dual;where empno=v_emp_wage;return (v_emp.wage);end get_sal;（5）用异常处理完善程序创建一个存储过程proc_execution，要求能够将empno为9010的员工姓名更改为'yourname'，如果输入的员工号不存在，则执行异常处理，显示“此员工不存在”。Create Procedure proc_execution Select S.empno, S.sname From S.empno 9010 where S.empno=yourname BEGIN IF S.empno=NULL THEN RAISE 'no data found' error; END proc_execution; （6）创建函数，实现功能为：在scott.emp表和scott.dept表中查询出任意给定职工号的职工姓名及职工所在部门的名称。CREATE OR REPLACE PROCEDURE select_emp(v_emp_no IN emp.empno%type)ISv_emp_name emp.ename%type;v_dept_name dept.dname%type;BEGINSELECT EMP.ENAME,DEPT.DNAMEINTO v_emp_name, v_dept_nameFROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMPNO = v_emp_no;DBMS_OUTPUT.PUT_LINE(v_emp_name|' '|v_dept_name);END select_emp;EXECUTE select_emp(7654);CREATE OR REPLACE TRIGGER update_dept_to_empAFTER UPDATE ON DEPT FOR EACH ROWBEGINIF UPDATINGTHENUPDATE EMP SET DEPTNO = :new.DEPTNOWHERE DEPTNO=:old.DEPTNO;END IF;END update_dept_to_emp;（7）对存储过程、函数及触发器实现查看、修改、删除等基本操作。CREATE TRIGGER scott.emp, scott.dept ON S.empno AFTER INSERT AS BEGIN select S.empno=scott.empEND proc_execution 触发器用SCOTT帐号登录，完成以下操作：(1)利用以下SQL语句，创建emp_copy表和emp_bak表create table emp_copyinsert into emp_copycreate table emp_bakinsert into emp_bak(2)在emp表中创建一个触发器tg_insert_emp，当向emp表中添加一条记录时，自动向emp_copy表也自动添加一条记录。当向emp表中更新一条记录时，自动更新emp_copy表中相关记录。create or replace tg_insert_emp before insert or update on emp for each rowdeclare v_max number(6);begin insert into emp_recondend;(3)当删除emp表中一条记录时，自动删除emp_copy表中相关记录，并且将删除的记录自动添加到emp_bak表。CREATE TABLE emp_bak AS SELECT * FROM EMP CREATE OR REPLACE TRIGGER tr_del_emp BEFORE DELETE ON scott.emp FOR EACH ROWBEGIN INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )DROP TRIGGER emp_bak ;END;.请编写相关触发器，完成下列操作：利用下面的命令创建一个表OP_LOGCREATE OR REPLACE TABLE op_log(ID VARCHAR2(20) PRIMARY KEY, op_dae date, op_type varchar2(200), op_user varhcar2(50);(1) 编写一个触发器，当系统关闭时，记录系统关闭的时间及操作用户。CREATE OR REPLACE TRIGGER tr_logonAFTER LOGON ON DATABASEBEGIN INSERT INTO log_event (user_name, address, logon_date) VALUES (ora_login_user, ora_client_ip_address, systimestamp);END tr_logon;(2) 编写一个触发器，当系统启动时，记录系统启动的时间及操作用户。CREATE OR REPLACE TRIGGER tr_logoffBEFORE LOGOFF ON DATABASEBEGIN INSERT INTO log_event (user_name, address, logoff_date) VALUES (ora_login_user, ora_client_ip_address, systimestamp);END tr_logoff;(3) 编写一个触发器，当用户登录时，记录用户登录的时间，及用户名称。CREATE TABLE ddl_event(crt_date timestamp PRIMARY KEY, event_name VARCHAR2(20), user_name VARCHAR2(10), obj_type VARCHAR2(20), obj_name VARCHAR2(20);
