PLSQL实验

  PL/SQL编程

一、实验目的及要求

　　(1) 掌握PL/SQL语句块、PL/SQL的基本语法、PL/SQL的控制结构。

　　(2) 掌握PL/SQL块中使用复合数据类型和游标的方法。

(3) 掌握PL/SQL异常处理技术。

(4) 掌握存储过程、存储函数、触发器高级数据库对象的基本作用。

(5) 掌握存储过程、存储函数、触发器的建立、修改、查看、删除操作。

二、实验主要内容

　(1) 记录执行命令和操作过程中遇到的问题及解决方法，注意从原理上解释原因。

(2) 记录利用SQL*Plus编写、执行PL/SQL程序的命令。

(3) 记录执行命令和操作过程中遇到的问题及解决方法，注意从原理上解释原因。

　　(4) 记录利用企业管理器管理存储过程、存储函数、触发器的方法。

(5) 记录利用SQL*Plus管理存储过程、存储函数、触发器的命令。

三、实验仪器设备

在局域网环境下，有一台服务器和若干台客户机。服务器成功安装Oracle 11g数据库服务器（企业版），客户机成功安装Oracle 11g客户端软件，网络服务配置正确，数据库和客户端正常工作。

四、实验步骤

1、PL/SQL语句块

定义一个包含声明、执行和异常处理的语句块

查询EMP表中职工号7788的工资，输出工资的值并且如果工资小于3000那么把工资更改为3000，异常部分对NO_DATA_FOUND异常进行处理，输出没有该员工。

 

如果想运行缓冲区的内容，那么可以用RUN命令或者/命令；serveroutput需要设置为on

SQL> setserveroutput on

SQL> declare

  2  psalemp.sal%type;

  3 

  4 begin

  5 select sal into psal from emp where empno=7788;

  6 if(psal<3000) then

  7 update emp set sal=3000 where empno=7788;

  8  endif;

  9 dbms_output.put_line('工资为'|| psal);

 10 exception

 11  whenNO_DATA_FOUND then

 12      dbms_output.put_line('没有该员工');

 13  end;

 14 

 15  /

 

工资为3000

 

PL/SQL procedure successfullycompleted

2、变量、数据类型和系统函数的使用

1） 定义三个变量，一个变量的初始值为字符串‘你好，’第二个字符串赋值为‘我是XXX’，这里写上自己的名字，第三个变量为前两个变量中的字符串连接之后的结果。输出第三个变量的值。（选做）

SQL> set serveroutput on

SQL> declare

  2  str1 char(10):='你好';

  3  str2 char(10):='我是XX';

  4  str3 char(20);

  5  begin

  6               str3:=str1||str2;

  7               dbms_output.put_line(str3);

  8 

  9  end;

 10  /

 

你好      我是张杰 

 

2）  将自己的名字作为字符串求出其长度，将长度与数字2进行比较，如果不大于2，输出‘我的名字是两个字’。（选做）

SQL> setserveroutput on

SQL> declare

  2 mname char(10):='张杰';

  3 

  4 begin

  5 

  6 if(length(mname)<=2) then

  7        dbms_output.put_line('我的名字是两个字');

  8  endif;

  9  end;

 10  /

 

PL/SQL proceduresuccessfully completed

3）  定义三个变量，分别用来存储emp表中的ename，sal，job三个列的值，并对其进行输出；要求用两种方式实现（%type类型和非%type类型）。

SQL> declare

  2 pname emp.ename%type;

  3  psalemp.sal%type;

  4  pjobemp.job%type;

  5 

  6 cursor pemp is select ename,sal,job from emp;

  7 

  8 begin

  9  openpemp;

 10  loop

 11    fetch pemp into pname,psal,pjob;

 12    exit when pemp%notfound;

 13 dbms_output.put_line(pname||' '||psal||' '||pjob);

 14 

 15  endloop;

 16 close pemp;

 17 

 18  end;

 19  /

 

SMITH 800 CLERK

ALLEN 1600SALESMAN

WARD 1250SALESMAN

JONES 2975MANAGER

MARTIN 1250SALESMAN

BLAKE 2850MANAGER

CLARK 2450 MANAGER

SCOTT 3000ANALYST

KING 5000PRESIDENT

TURNER 1500SALESMAN

ADAMS 1100 CLERK

JAMES 950 CLERK

FORD 3000ANALYST

MILLER 1300CLERK

 

PL/SQL proceduresuccessfully completed

 

 

       SQL> declare

  2  pempemp%rowtype;

  3 cursor c_emp is select * from emp;

  4 begin

  5  openc_emp;

  6  loop

  7      fetch c_emp into pemp;

  8      exit when c_emp%notfound;

  9 dbms_output.put_line(pemp.ename||' '||pemp.sal||' '||pemp.job);

 10  endloop;

 11 

 12 close c_emp;

 13 

 14  end;

 15  /

 

SMITH 800 CLERK

ALLEN 1600SALESMAN

WARD 1250SALESMAN

JONES 2975MANAGER

MARTIN 1250SALESMAN

BLAKE 2850MANAGER

CLARK 2450MANAGER

SCOTT 3000ANALYST

KING 5000PRESIDENT

TURNER 1500SALESMAN

ADAMS 1100 CLERK

JAMES 950 CLERK

FORD 3000ANALYST

MILLER 1300CLERK

 

PL/SQL proceduresuccessfully completed

 

4）  创建一个记录类型v_record，类型包含v_name，v_salary，v_job，v_deptno等分量，要求记录类型中各个分量的数据类型和emp表中ename，sal，job，deptno列的数据类型一致（%type实现）。创建一个变量，变量类型为v_ record，读取EMP表中职工号为7788的ename，sal，job，deptno为该变量赋值，输出该变量的值。

SQL> declare

  2 

  3  typev_record is record (

  4 

  5  nameemp.ename%type,

  6 

  7 salary emp.sal%type,

  8 

  9  jobemp.job%type,

 10 

 11 deptno emp.deptno%type

 12 

 13  );

 14 

 15 empinfo v_record;--定义变量

 16 

 17 begin

 18 

 19 select ename,sal,job,deptno

 20 

 21  intoempinfo

 22 

 23  fromemp

 24 

 25 where empno = 7788;

 26 

 27 dbms_output.put_line('雇员'||empinfo.name||'的职务是：'||empinfo.job||'工资是：'||empinfo.salary||'部门号是：'||empinfo.deptno);

 28 

 29  end;

 30 

 31  /

 

雇员SCOTT的职务是：ANALYST工资是：3000部门号是：20

 

PL/SQL proceduresuccessfully completed

 

SQL>

 

3、条件语句的使用

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

 

SQL>

SQL> declare

  2  cursor c_emp is select * from emp for update;

  3  v_increment number;

  4  begin

  5  for v_emp in c_emp loop

  6  case v_emp.deptno

  7       when 10 then v_increment:=100;

  8       when 20 then v_increment:=160;

  9       when 30 then v_increment:=200;

 10 

 11        else v_increment:=300;

 12        end case;

 13        update emp set sal=sal+v_increment wherecurrent of c_emp;

 14 

 15  end loop;

 16  end;

 17  /

 

PL/SQL procedure successfully completed

 

 

 

SQL>

SQL> declare

  2 cursor c_emp is select * from emp for update;

  3 v_increment number;

  4 begin

  5  forv_emp in c_emp loop

  6 

  7 if(v_emp.deptno=10) then v_increment:=100;

  8 elsif(v_emp.deptno=20) then v_increment:=160;

  9 elsif(v_emp.deptno=30) then v_increment:=200;

 10 else v_increment:=300;

 11 end if;

 12 

 13 

 14 update emp set sal=sal+v_increment where current of c_emp;

 15 

 16 end loop;

 17 end;

 18 /

 

PL/SQL procedure successfully completed

 

SQL>

4、循环的使用

1）使用循环实现，输出100以内所有个位上是7的整数(7,17,27...)。

       SQL> setserveroutput on

SQL> declare

  2 

  3 pnum number;

  4 begin

  5 for pnum in 1..100 loop

  6 if(mod(pnum,10)=7) then dbms_output.put_line(pnum);

  7 end if;

  8 end loop;

  9 end;

 10 /

 

7

17

27

37

47

57

67

77

87

97

 

PL/SQL procedure successfully completed

 

2）创建一张包含两个列的表，使用循环向该表添加10行数据，第一列添加从10000开始递增100的编号，第二列添加字符串‘hello world!’。（选做）

5、游标的使用

1）分别用简单循环、WHILE循环、FOR循环以及显式游标统计并输出各个部门的人数以及平均工资；

SQL> declare

  2 cursor asa is select deptno,sum(deptno),avg(sal) from emp group bydeptno;

  3 pdeptno emp.deptno%type;

  4 pnum1 number;

  5 pnum2 number;

  6 begin

  7 open asa;

  8       loop

  9       fetch asa into pdeptno,pnum1,pnum2;

 10       exit when asa%notfound;

 11       dbms_output.put_line(pdeptno||pnum1||pnum2);

 12       end loop;

 13 

 14 close asa;

 15 

 16 

 17 end;

 18 /

 

301801966.666666666666666666666666666666666667

201002495

10303116.666666666666666666666666666666666667

 

PL/SQL proceduresuccessfully completed

 

 

declare

cursor asa isselect deptno,sum(deptno),avg(sal) from emp groupby deptno;

pdeptno emp.deptno%type;

pnum1 number;

pnum2 number;

begin

open asa;

     while(asa%notfound) loop

     fetch asa into pdeptno,pnum1,pnum2;

     dbms_output.put_line(pdeptno||pnum1||pnum2);

     endloop;

 

close asa;

2）使用循环和游标实现，查询部门编号为10的员工信息，将查询结果按照员工编号从大到小的顺序排列，输出倒数第二行记录。（选做）

SQL> declare

  2 empler emp%rowtype;

  3 i number:=0;

  4 n number;

  5 cursor emp_2 is select * from emp where deptno=10 order by empno desc;

  6 begin

  7 select count(*) into n from emp where deptno=10;

  8 open emp_2;

  9 loop

 10 fetch emp_2 into empler;

 11 exit when emp_2%notfound;

 12 i:=i+1;

 13 if i=n-1

 14 then

 15 dbms_output.put_line(empler.empno||empler.ename);

 16 end if;

 17 -- fetch emp_2 into empler;

 18 

 19 end loop;

 20 close emp_2;

 21 end;

 22 /

 

7839KING

 

PL/SQL procedure successfully completed

 

3）使用隐式游标实现以下要求：修改部门号为50的部门地址为‘BEIJING’。如果该部门不存在，则向dept表中插入一个部门号为50，地址为‘BEIJING’的记录。 （选做）

SQL> begin

  2 

  3 update scott.dept

  4 

  5 set loc = 'BEIJING' where deptno=50;

  6 

  7 if sql%notfound then

  8 

  9 insert into scott.dept(deptno,loc) values(50,'BEIJING');dbms_output.put_line('插入成功!');

 10 

 11 ELSE

 12 

 13 dbms_output.put_line('更新成功');

 14 

 15 end if;

 16 

 17 end;

 18 /

 

插入成功!

 

PL/SQL procedure successfullycompleted

6、创建一个显示雇员总人数的存储过程emp_count,并执行该存储过程

       SQL> create orreplace procedure emp_count

  2 as num number;

  3 begin

  4     select count(*) into num from emp;

  5     dbms_output.put_line(num);

  6     end;

  7 /

 

Procedure created

 

SQL> execute emp_count

 

14

 

PL/SQL procedure successfully completed

 

SQL>

 

 

7、编写显示雇员信息的存储过程EMP_LIST，并引用EMP_COUNT存储过程

       SQL> create orreplace procedure emp_list

  2 as cursor emp_cursor is select empno,ename from emp ;

  3 begin

  4 

  5 for pemp in emp_cursor loop

  6 dbms_output.put_line(pemp.empno||pemp.ename);

  7 end loop;

  8 end;

  9 /

 

Procedurecreated

8、创建函数，实现功能为：在scott.emp表和scott.dept表中查询出任意给定职工号的职工

姓名及职工所在部门的名称。

       SQL> CREATE ORREPLACE PROCEDURE select_emp

  2 (v_emp_no IN emp.empno%type)

  3 

  4 IS

  5 

  6 v_emp_name emp.ename%type;

  7 

  8 v_dept_name dept.dname%type;

  9 

 10 BEGIN

 11 

 12 SELECT EMP.ENAME,DEPT.DNAME

 13 

 14 INTO v_emp_name, v_dept_name

 15 

 16 FROM EMP,DEPT

 17 

 18 WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMPNO = v_emp_no;

 19 

 20 DBMS_OUTPUT.PUT_LINE(v_emp_name||' '||v_dept_name);

 21 

 22 END select_emp;

 23 /

 

Procedurecreated

 

SQL> execute select_emp(7788);

 

SCOTT RESEARCH

 

PL/SQLprocedure successfully completed

9、创建触发器，实现更新dept表中的deptno值，级联更新emp表中相应值。

SQL> CREATE OR REPLACE TRIGGER tr_reg_dep

  2 

  3 AFTER update OF deptno

  4 

  5 ON dept

  6 

  7 FOR EACH ROW

  8 

  9 BEGIN

 10 

 11 DBMS_OUTPUT.PUT_LINE('旧的deptno值是'||:old.deptno

 12 

 13 ||'、新的deptno值是'||:new.deptno);

 14 

 15 UPDATE emp SET deptno = :new.deptno

 16 

 17 WHERE deptno = :old.deptno;

 18 

 19 END;

 20 /

 

Triggercreated

10、对存储过程、函数及触发器实现查看、修改、删除等基本操作。

SQL> select object_name,status from user_objects whereobject_type='FUNCTION';

 

OBJECT_NAME                                                                     STATUS

---------------------------------------------------------------------------------------

SQL> select object_name,status from user_objects whereobject_type='PROCEDURE';

 

OBJECT_NAME                                                                     STATUS

---------------------------------------------------------------------------------------

SELECT_EMP                                                                      VALID

EMP_LIST                                                                        VALID

EMP_COUNT                                                                       VAL

SQL> drop procedure select_emp;

 

Proceduredropped

SQL> DROP TRIGGER TR_REG_DEP;

 

Triggerdropped

11、创建一个包mypackage，声明该包有一个过程update_sal和一个函数get_YearSal

create or replacepackage myPackage is

       procedure update_sal(name varchar2,newsalnumber);

       function get_YearSal(name varchar2)return number;

end;

create orreplace package body myPackage is

       procedure update_sal(name varchar2,newsalnumber) is

       begin

        update emp set sal=newSal whereename=name;

       end;

  function get_YearSal(name varchar2) returnnumber is

  v_sal number(7,2);

  begin

        select sal*12+nvl(comm,0) into v_salfrom emp where ename=name;

        return v_sal;

  end;

  end;

调用执行包中的存储过程或函数

 

 

 

SQL>

SQL> create or replace package myPackageis

 2       procedure update_sal(name varchar2,newsal number);

 3       function get_YearSal(name varchar2) return number;

 4  end;

 5  /

 

 

SQL> create or replace package body myPackage is

  2    procedure update_sal(namevarchar2,newsal number) is

  3    begin

  4          update emp set sal=newSal whereename=name;

  5    end;

  6    function get_YearSal(name varchar2) returnnumber is

  7    v_sal number(7,2);

  8    begin

  9          select sal*12+nvl(comm,0) into v_salfrom emp where ename=name;

 10          return v_sal;

 11    end;

 12    end;

 13 /

 

Packagebody created

Package created

12、假设有这样一张用户表表结构如下：UserInfo(id ,username,userPass)，希望向表中增加数据时，表中id列的数字自动生成。（选做）

1）第一步创建序列，要求开始的数字为1，每次递增1，按顺序产生序列值；

 

SQL> create table Userinfo(

  2 id number,

  3 username varchar2(10),

  4 userPass varchar2(10)

  5 );

 

Table created

 

SQL> create sequence sql_user_id

  2 start with 1

  3 increment by 1;

 

Sequence created

2）第二步创建一个触发器，向用户表中插入数据的时候触发触发器，在触发器内部调用序列并生成一个序列值赋值给表的id列。

3）SQL> createtrigger tr_user_id

4）  2 before insert on Userinfo

5）  3  foreach row begin

6）  4 select sql_user_id.nextval into :new.id from dual;

7）  5  end;

8）  6  /

9）

10）Trigger created