实验3PLSQL编程

实验三PL/SQL编程1．PL/SQL块处理定义一个包含声明、执行和异常处理的块查询EMP表中职工号7788的工资，输出工资的值并且如果工资小于3000那么把工资更改为3000，异常部分对NO_DATA_FOUND异常进行处理，输出没有该员工。

如果想运行缓冲区的内容，那么可以用RUN命令或者/命令；serveroutput需要设置为on 2．记录类型的使用创建一个记录类型v_record，类型包含name，salary，job，deptno等分量，要求记录类型的分量的数据类型和emp表中列的数据类型一致（%type实现）。

创建一个变量，变量类型为v_record，读取EMP表中职工号为7788的ename，sal，job，deptno为该变量赋值，输出变量的分量。

3．条件语句的使用分别用IF语句和CASE语句实现以下要求：输入一个员工号，修改该员工的工资，如果该员工职位是CLERK，工资增加100；若为SALESMAN，工资增加160；若为ANALYST，工资增加200；否则增加300。

4. 循环和显示游标的使用分别用简单循环、WHILE循环、FOR循环以及显示游标统计并输出各个部门的人数以及平均工资5．用隐式游标实现以下要求：修改部门号为50的部门地址为‘BEIJING’。

如果该部门不存在，则向dept表中插入一个部门号为50，地址为‘BEIJING’的记录。

6．创建一个显示雇员总人数的存储过程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 INTO v_sal FROM emp WHERE empno=v_empno;IF v_sal<3000THEN v_add:=3000;ELSEdbms_output.put_line('sal>3000');END IF;UPDATE emp SET sal=v_add WHERE empno=v_empno;END;/2．declaretype 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 empwhere empno = 7788;dbms_output.put_line('雇员'||||'的职务是：'||empinfo.job||'工资是：'||empinfo.salary||'部门号是：'||empinfo.deptno);end;/3.declarecursor c_emp is select * from scott.emp for update;v_increment number;beginfor v_emp in c_emp loopcase v_emp.deptnowhen 10 then v_increment:=100;when 20 then v_increment:=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.beginupdate scott.deptset loc = 'BEIJING' where deptno=50;if sql%notfound theninsert into scott.dept(deptno,loc) values(50,'BEIJING');dbms_output.put_line('插入成功!');ELSEdbms_output.put_line('更新成共');end if;end;6.create or replace procedure emp_countas v_total number;beginselect count(*) into v_total from scott.emp;dbms_output.put_line('雇员总数：'||v_total); end;/SQL> execute emp_count;雇员总数：15PL/SQL 过程已成功完成。

SQL> begin2 emp_count;3 end;4 /雇员总数：15PL/SQL 过程已成功完成。

7.1 CREATE OR REPLACE PROCEDURE EMP_LIST2 AS3 CURSOR emp_cursor IS4 SELECT empno,ename FROM scott.emp;5 BEGIN6 FOR Emp_record IN emp_cursor LOOP7 DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);8 END LOOP;9 EMP_COUNT;10* END;SQL> /过程已创建。

8.SQL> CREATE OR REPLACE PROCEDURE select_emp2 (v_emp_no IN emp.empno%type)3 IS4 v_emp_name emp.ename%type;5 v_dept_name dept.dname%type;6 BEGIN7 SELECT EMP.ENAME,DEPT.DNAME8 INTO v_emp_name, v_dept_name9 FROM EMP,DEPT10 WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMPNO = v_emp_no;11 DBMS_OUTPUT.PUT_LINE(v_emp_name||' '||v_dept_name);12 END select_emp;13 /过程已创建。

SQL> EXECUTE select_emp(7844);TURNER SALESPL/SQL 过程已成功完成。

SQL> CREATE OR REPLACE TRIGGER update_dept_to_emp2 AFTER UPDATE ON DEPT FOR EACH ROW3 BEGIN4 IF UPDATING THEN5 UPDATE EMP SET DEPTNO = :new.DEPTNO6 WHERE DEPTNO=:old.DEPTNO;7 END IF;8 END update_dept_to_emp;9 /触发器已创建9.CREATE OR REPLACE TRIGGER tr_reg_depAFTER update OF deptnoON deptFOR EACH ROWBEGINDBMS_OUTPUT.PUT_LINE('旧的deptno值是'||:old.deptno||'、新的deptno值是'||:new.deptno); UPDATE emp SET deptno = :new.deptnoWHERE deptno = :old.deptno;END;10.select object_name,status from user_objects where object_type='FUNCTION'; 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 myPackage 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) isbeginupdate emp set sal=newSal where ename=name;end;function get_YearSal(name varchar2) return number isv_sal number(7,2);beginselect sal*12+nvl(comm,0) into v_sal from emp where ename=name;return v_sal;end;end;调用执行包中的存储过程或函数我们现在有这样一张用户表表结构如下，希望向表中增加数据时，表中id列的数字自动生成。

第一步创建序列，要求开始的数字为1，每次递增1，按顺序产生序列值；第二步创建一个触发器，向用户表中插入数据的时候触发触发器，在触发器内部调用序列并生成一个序列值赋值给表的id列。

表结构如下:UserInfo(id ,username,userPass)--用户表（用户编号number类型，用户名，用户密码）create sequence seq_user_idstart with 1 --从1开始increment by 1;--每次增量为1第二步创建一个触发器给id列赋值create trigger tr_user_idbefore insert on userfor each rowbeginselect seq_user_id.nextval into :new.id from dual; end;主要算法:1.DECLAREv_empno emp.empno%TYPE:=7788;v_sal emp.sal%TYPE;v_add emp.sal%TYPE;BEGINSELECT sal INTO v_sal FROM emp WHERE empno=v_empno;IF v_sal<3000THEN v_add:=3000;ELSEdbms_output.put_line('sal>3000');END IF;UPDATE emp SET sal=v_add WHERE empno=v_empno;END;/2．declaretype 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 empwhere empno = 7788;dbms_output.put_line('雇员'||||'的职务是：'||empinfo.job||'工资是：'||empinfo.salary||'部门号是：'||empinfo.deptno);end;/3.declarecursor c_emp is select * from scott.emp for update;v_increment number;beginfor v_emp in c_emp loopcase v_emp.deptnowhen 10 then v_increment:=100;when 20 then v_increment:=160;when 30 then v_increment:=200;else v_increment:=300;end case;update scott.emp set sal = sal+v_increment wherecurrent of c_emp;end loop;end;4.5.beginupdate scott.deptset loc = 'BEIJING' where deptno=50;if sql%notfound theninsert into scott.dept(deptno,loc) values(50,'BEIJING');dbms_output.put_line('插入成功!');ELSEdbms_output.put_line('更新成共');end if;end;6.create or replace procedure emp_countas v_total number;beginselect count(*) into v_total from scott.emp;dbms_output.put_line('雇员总数：'||v_total); end;/SQL> execute emp_count;雇员总数：15PL/SQL 过程已成功完成。