实验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.
DECLARE
v_empno emp.empno%TYPE:=7788;
v_sal emp.sal%TYPE;
v_add emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;
IF v_sal<3000
THEN v_add:=3000;
ELSE
dbms_output.put_line('sal>3000');
END IF;
UPDATE emp SET sal=v_add WHERE empno=v_empno;
END;
/
2．
declare
type v_record is record (
name emp.ename%type,
salary emp.sal%type,
job emp.job%type,
deptno emp.deptno%type
);
empinfo v_record;--定义变量
begin
select ename,sal,job,deptno
into empinfo
from emp
where empno = 7788;
dbms_output.put_line('雇员'||||'的职务是：'||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_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.
begin
update scott.dept
set loc = 'BEIJING' where deptno=50;
if sql%notfound then
insert into scott.dept(deptno,loc) values(50,'BEIJING');
dbms_output.put_line('插入成功!');
ELSE
dbms_output.put_line('更新成共');
end if;
end;
6.
create or replace procedure emp_count
as v_total number;
begin
select count(*) into v_total from scott.emp;
dbms_output.put_line('雇员总数：'||v_total); end;
/
SQL> execute emp_count;
雇员总数：15
PL/SQL 过程已成功完成。

SQL> begin
2 emp_count;
3 end;
4 /
雇员总数：15
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 PROCEDURE 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);
12 END select_emp;
13 /
过程已创建。

SQL> EXECUTE select_emp(7844);
TURNER SALES
PL/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 /
触发器已创建
9.
CREATE OR REPLACE TRIGGER tr_reg_dep
AFTER update OF deptno
ON dept
FOR EACH ROW
BEGIN
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='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_YearSal
create or replace package myPackage is
procedure update_sal(name varchar2,newsal number);
function get_YearSal(name varchar2) return number;
end;
create or replace package body myPackage is
procedure update_sal(name varchar2,newsal number) is
begin
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，按顺序产生序列值；第二步创建一个触发器，向用户表中插入数据的时候触发触发器，在触发器内部调用序列并生成一个序列值赋值给表的id列。

表结构如下:
UserInfo(id ,username,userPass)
--用户表（用户编号number类型，用户名，用户密码）
create sequence seq_user_id
start with 1 --从1开始
increment by 1;--每次增量为1
第二步创建一个触发器给id列赋值
create trigger tr_user_id
before insert on user
for each row
begin
select seq_user_id.nextval into :new.id from dual; end;
主要算法:
1.
DECLARE
v_empno emp.empno%TYPE:=7788;
v_sal emp.sal%TYPE;
v_add emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;
IF v_sal<3000
THEN v_add:=3000;
ELSE
dbms_output.put_line('sal>3000');
END IF;
UPDATE emp SET sal=v_add WHERE empno=v_empno;
END;
/
2．
declare
type v_record is record (
name emp.ename%type,
salary emp.sal%type,
job emp.job%type,
deptno emp.deptno%type
);
empinfo v_record;--定义变量
begin
select ename,sal,job,deptno
into empinfo
from emp
where empno = 7788;
dbms_output.put_line('雇员'||||'的职务是：'||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_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.
begin
update scott.dept
set loc = 'BEIJING' where deptno=50;
if sql%notfound then
insert into scott.dept(deptno,loc) values(50,'BEIJING');
dbms_output.put_line('插入成功!');
ELSE
dbms_output.put_line('更新成共');
end if;
end;
6.
create or replace procedure emp_count
as v_total number;
begin
select count(*) into v_total from scott.emp;
dbms_output.put_line('雇员总数：'||v_total); end;
/
SQL> execute emp_count;
雇员总数：15
PL/SQL 过程已成功完成。

SQL> begin
2 emp_count;
3 end;
4 /
雇员总数：15
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 PROCEDURE 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);
12 END select_emp;
13 /
过程已创建。

SQL> EXECUTE select_emp(7844);
TURNER SALES
PL/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 /
触发器已创建
9.
CREATE OR REPLACE TRIGGER tr_reg_dep
AFTER update OF deptno
ON dept
FOR EACH ROW
BEGIN
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='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_YearSal create or replace package myPackage is
procedure update_sal(name varchar2,newsal number);
function get_YearSal(name varchar2) return number;
end;
create or replace package body myPackage is
procedure update_sal(name varchar2,newsal number) is
begin
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;
疑难小结：
在创建过程时，还是对基本知识掌握不牢固，在使用循环时有时会迷惑循环过程，追根揭底是对循环过程的不熟悉，还要勤加锻炼，对游标的声明及使用还是不熟悉，还有就是练习过程中会敲错代码，今后的学习过程中一点要结合课本多加练习吃透基本知识。