《实验3PLSQL编程》由会员分享，可在线阅读，更多相关《实验3PLSQL编程（16页珍藏版）》请在金锄头文库上搜索。
1、实验三 PL/SQL编程1PL/SQL块处理定义一个包含声明、执行和异常处理的块查询EMP表中职工号7788的工资，输出工资的值并且如果工资小于3000那么把工资更改为3000，异常部分对NO_DATA_FOUND异常进行处理，输出没有该员工。如果想运行缓冲区的内容，那么可以用RUN命令或者/命令；serveroutput需要设置为on2记录类型的使用创建一个记录类型v_record，类型包含name，salary，job，deptno等分量，要求记录类型的分量的数据类型和emp表中列的数据类型一致（%type实现）。创建一个变量，变量类型为v_ record，读取EMP表中职工号为7788的
2、ename，sal，job，deptno为该变量赋值，输出变量的分量。3条件语句的使用分别用IF语句和CASE语句实现以下要求：输入一个员工号，修改该员工的工资，如果该员工职位是CLERK，工资增加100；若为SALESMAN，工资增加160；若为ANALYST，工资增加200；否则增加300。4. 循环和显示游标的使用分别用简单循环、WHILE循环、FOR循环以及显示游标统计并输出各个部门的人数以及平均工资5用隐式游标实现以下要求：修改部门号为50的部门地址为BEIJING。如果该部门不存在，则向dept表中插入一个部门号为50，地址为BEIJING的记录。 6创建一个显示雇员总人数的存储过
3、程emp_count,并执行该存储过程7. 编写显示雇员信息的存储过程EMP_LIST，并引用EMP_COUNT存储过程8. 创建函数，实现功能为：在scott.emp表和scott.dept表中查询出任意给定职工号的职工姓名及职工所在部门的名称。9. 创建触发器，实现更新dept表中的deptno值，级联更新emp表中相应值。10. 对存储过程、函数及触发器实现查看、修改、删除等基本操作。主要算法和程序清单：1.DECLAREv_empno emp.empno%TYPE:=7788;v_sal emp.sal%TYPE;v_add emp.sal%TYPE;BEGINSELECT sal I
4、NTO v_sal FROM emp WHERE empno=v_empno;IF v_sal3000);END IF;UPDATE emp SET sal=v_add WHERE empno=v_empno;END;/2declaretype v_record is record (name emp.ename%type,salary emp.sal%type,job emp.job%type,deptno emp.deptno%type);empinfo v_record;-定义变量beginselect ename,sal,job,deptnointo empinfofrom empwh
5、ere empno = 7788;dbms_output.put_line(雇员|empinfo.name|的职务是：|empinfo.job|工资是：|empinfo.salary|部门号是：|empinfo.deptno);end;/3.declare cursor c_emp is select * from scott.emp for update; v_increment number;begin for v_emp in c_emp loop case v_emp.deptno when 10 then v_increment:=100; when 20 then v_increm
6、ent:=160; when 30 then v_increment:=200; else v_increment:=300; end case; update scott.emp set sal = sal+v_increment where current of c_emp; end loop;end;4.5.begin update scott.dept set loc = BEIJING where deptno=50; if sql%notfound then insert into scott.dept(deptno,loc) values(50,BEIJING); dbms_ou
7、tput.put_line(插入成功!); ELSE dbms_output.put_line(更新成共); end if; end;6.create or replace procedure emp_countas v_total number;begin select count(*) into v_total from scott.emp; dbms_output.put_line(雇员总数：|v_total);end;/SQL execute emp_count;雇员总数：15PL/SQL 过程已成功完成。SQL begin 2 emp_count; 3 end; 4 /雇员总数：15
8、PL/SQL 过程已成功完成。7. 1 CREATE OR REPLACE PROCEDURE EMP_LIST 2 AS 3 CURSOR emp_cursor IS 4 SELECT empno,ename FROM scott.emp; 5 BEGIN 6 FOR Emp_record IN emp_cursor LOOP 7 DBMS_OUTPUT.PUT_LINE(Emp_record.empno|Emp_record.ename); 8 END LOOP; 9 EMP_COUNT;10* END;SQL /过程已创建。8.SQL CREATE OR REPLACE PROCEDUR
9、E select_emp 2 (v_emp_no IN emp.empno%type) 3 IS 4 v_emp_name emp.ename%type; 5 v_dept_name dept.dname%type; 6 BEGIN 7 SELECT EMP.ENAME,DEPT.DNAME 8 INTO v_emp_name, v_dept_name 9 FROM EMP,DEPT 10 WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMPNO = v_emp_no; 11 DBMS_OUTPUT.PUT_LINE(v_emp_name| |v_dept_name); 1
10、2 END select_emp; 13 /过程已创建。SQL EXECUTE select_emp(7844);TURNER SALESPL/SQL 过程已成功完成。SQL CREATE OR REPLACE TRIGGER update_dept_to_emp 2 AFTER UPDATE ON DEPT FOR EACH ROW 3 BEGIN 4 IF UPDATING THEN 5 UPDATE EMP SET DEPTNO = :new.DEPTNO 6 WHERE DEPTNO=:old.DEPTNO; 7 END IF; 8 END update_dept_to_emp; 9
11、/触发器已创建9.CREATE OR REPLACE TRIGGER tr_reg_dep AFTER update OF deptnoON deptFOR EACH ROWBEGIN DBMS_OUTPUT.PUT_LINE(旧的deptno值是|:old.deptno |、新的deptno值是|:new.deptno); UPDATE emp SET deptno = :new.deptno WHERE deptno = :old.deptno;END;10.select object_name,status from user_objects where object_type=FUNC
12、TION;select object_name,status from user_objects where object_type=PROCEDURE;SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE=TRIGGER; drop procedure select_emp;DROP FUNCTION GET_AVG_PAY;DROP TRIGGER TR_REG_DEP;五、拓展题-创建一个包mypackage，声明该包有一个过程update_sal和一个函数get_YearSalcreate or replace package my
13、Package isprocedure update_sal(name varchar2,newsal number);function get_YearSal(name varchar2) return number;end;create or replace package body myPackage isprocedure update_sal(name varchar2,newsal number) isbegin update emp set sal=newSal where ename=name;end; function get_YearSal(name varchar2) return number is v_sal number(7,2); begin select sal*12+nvl(comm,0) into v_sal from emp where ename=name; return v_sal; end; end;调用执行包中的存储过程或函数我们现在有这样一张用户表表结构如下，希望向表中增加数据时，表中id列的数字自动生成。第一步创建序列，要求开始的数字为1，每次递增1，按顺序产生序列值；第二步创建一个触发器，向用户表中插入数据的时
