PL/SQL练习题

在Oracle中所有的过程都是以PL/SQL块开始的，掌握PL/SQL对于过程的开发是非常用用处的。

PL/SQL块的定义格式：

DECLARE 声明一些变量 BEGIN 程序的开发 EXCEPTION 程序中有可能出现异常 END ; / 范例：使用以上的语句，创建一个简单的程序块

DECLARE i i:=30 ; NUMBER ; BEGIN -- 将变量i的内容设置成30 END ; / 以上一个基本的语句块已经写完了，但是里面并没有任何的输出，之所以这样，主要原因在于SQLPLUSW中的系统输出没有打开，需要通过以下

的命令完成：

DBMS_OUTPUT.PUT_LINE('i的内容是：' || i) ; SET SERVEROUTPUT ON ; 以上是一个简单的语句块，但是在PLSQL块中也可以直接进行数据库的查询操作。

范例：要求输入一个雇员的编号，之后显示出此编号雇员的姓名 · 如果要想输入雇员编号的话，第八章存在一个替代变量“&”

DECLARE eno NUMBER ; name VARCHAR2(8) ; BEGIN DBMS_OUTPUT.PUT_LINE('请输入雇员编号：') ; eno:=&empnonumber ; SELECT ename INTO name FROM emp WHERE empno=eno ; DBMS_OUTPUT.PUT_LINE('编号为' || eno ||'的雇员姓名是：' || name) ; END ; / 此时已经可以查询出来了，但是以上的代码是否会存在其他问题呢？如果现在输入的编号不存在则有可能返回错误信息，那么为了让代码在出现错

误之后依然可以正确的使用，则可以加入异常处理的操作机制。

-- 接收传入的编号 -- 保存查询出来的雇员姓名 DECLARE eno NUMBER ; name VARCHAR2(8) ; BEGIN DBMS_OUTPUT.PUT_LINE('请输入雇员编号：') ; -- 接收传入的编号 -- 保存查询出来的雇员姓名 eno:=&empnonumber ; SELECT ename INTO name FROM emp WHERE empno=eno ; DBMS_OUTPUT.PUT_LINE('编号为' || eno ||'的雇员姓名是：' || name) ; EXCEPTION -- 当程序出现错误的时候执行此语句语句 WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('没有这个员工！！！') ; END ; / 但是，从以上的程序中可以发现，在SQLPLUSW中输出实际上并没有真正的意义，所以显示的格式也就没有必要做任何的调整。

在PL/SQL块中也可以编写循环、判断等语句。

可以使用循环操作：LOOP，循环

DECLARE i i := 1 ; NUMBER ; BEGIN -- 给变量i一个初始值 LOOP DBMS_OUTPUT.PUT_LINE('i = ' || i) ; EXIT WHEN i>=10 ; i := i + 1 ; END LOOP ; END ; / 这个时候一个循环语句就已经编写完成了。此循环属于先执行后判断，所以可以使用另外一种循环语句：WHILE…LOOP。

DECLARE i i := 1 ; NUMBER ; BEGIN -- 给变量i一个初始值 WHILE(i<=10) LOOP DBMS_OUTPUT.PUT_LINE('i = ' || i) ; i := i + 1 ; END LOOP ; END ; / 那么，此种循环操作属于先判断后执行。 在PL/SQL中也可以使用FOR循环方式

DECLARE i NUMBER ; BEGIN FOR i IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE('i = ' || i) ; END LOOP ; END ; / PLSQL中同样存在IF语句，执行条件的分支操作。

范例：给定一个值，要求判断其是否大于10，如果大于，则显示记录大于10条

DECLARE coun NUMBER ; BEGIN coun := 11 ; IF coun>10 THEN DBMS_OUTPUT.PUT_LINE('记录大于10条') ; END IF ; END ; / 但是，以上的内容都属于固定好的结果，现在要求可以将emp表中的记录数进行验证。

DECLARE coun NUMBER ; BEGIN SELECT COUNT(empno) INTO coun FROM emp ; IF coun>10 THEN DBMS_OUTPUT.PUT_LINE('记录大于10条') ; END IF ; END ; / 也可以使用if..else语句

DECLARE coun NUMBER ; BEGIN SELECT COUNT(empno) INTO coun FROM emp ; coun := 3 ; IF coun>10 THEN DBMS_OUTPUT.PUT_LINE('记录大于10条') ; ELSE DBMS_OUTPUT.PUT_LINE('记录不大于10条') ; END IF ; END ; / If语句上还能继续扩展，if…elseif….else….但是需要注意的是，在Oracle中的else if要换成elsif

DECLARE coun NUMBER ; BEGIN SELECT COUNT(empno) INTO coun FROM emp ; coun := 3 ; IF coun>10 THEN DBMS_OUTPUT.PUT_LINE('记录大于10条') ; ELSIF coun<10 THEN DBMS_OUTPUT.PUT_LINE('记录小于10条') ; ELSE DBMS_OUTPUT.PUT_LINE('记录等于10条') ; END IF ; END ; / 那么，下面就可以利用以上的语句完成一些的复杂功能。

要求：要求输入一个雇员编号，为此雇员增长工资，增长工作按照以下的原则进行：

· 10部门人员工资上涨10% · 20部门人员工资上涨20% · 30部门人员工资上涨30% 但是所有的工资最高不超过5000。

DECLARE eno NUMBER ; dno NUMBER ; esal NUMBER ; BEGIN DBMS_OUTPUT.PUT_LINE('请输入雇员的编号：') ; eno := &empnumber ; SELECT deptno,sal INTO dno,esal FROM emp WHERE empno=eno ; IF dno=10 THEN IF esal*1.1>5000 THEN UPDATE emp SET sal=5000 WHERE empno=eno ; ELSE UPDATE emp SET sal=sal*1.1 WHERE empno=eno ; END IF ; ELSIF dno=20 THEN IF esal*1.2>5000 THEN UPDATE emp SET sal=5000 WHERE empno=eno ; ELSE UPDATE emp SET sal=sal*1.2 WHERE empno=eno ; END IF ; ELSIF dno=30 THEN IF esal*1.3>5000 THEN UPDATE emp SET sal=5000 WHERE empno=eno ; ELSE UPDATE emp SET sal=sal*1.3 WHERE empno=eno ; END IF ; ELSE null; -- 如果都不满足，则什么也不操作 END IF ; END ; / 3.2、游标

游标：是内存中用于装载记录的一个区域。 如果要想使用游标，需要按照以下的步骤进行处理：

1、 2、

3、

声明游标 打开游标

取得游标进行PL/SQL的使用，将内容放到变量之中

4、

关闭游标 问题：

对于各个基本类型：NUMBER、VARCHAR2都可以直接直接使，但是现在需要有一种类型，此种类型可以直接装下一行数据。

如果要想装下一行数据的话要使用ROWTYPE表示类型。每一个ROWTYPE表示一行数据

DECLARE erow emp%ROWTYPE ; -- 此变量可以装下一行的emp记录 BEGIN eno := 7369 ; SELECT * INTO erow FROM emp WHERE empno=eno ; DBMS_OUTPUT.PUT_LINE('姓名：' || erow.ename) ; DBMS_OUTPUT.PUT_LINE('姓名：' || erow.sal) ; END ; / 可以发现ROWTYPE确实可以装下一条完整的记录，之后使用ROWTYPE定义的变量，通过列名称进行访问。

掌握了ROWTYPE之后，下面就可以使用游标进行操作了，游标需要依次取出每一行的记录。

范例：使用游标依次输出雇员表中的编号和雇员姓名

eno emp.empno%TYPE ; -- 使用emp表中的empno字段的类型定义eno变量 DECLARE erow emp%ROWTYPE ; -- 此变量可以装下一行的emp记录 BEGIN OPEN mycur ; -- 打开游标 FETCH mycur INTO erow ; -- 移动游标，并将移动后的内容放到erow之中 WHILE (mycur%FOUND) LOOP -- 如果游标的内容存在 DBMS_OUTPUT.PUT_LINE(erow.empno || ' --> ' || erow.ename) ; FETCH mycur INTO erow ; -- 输出之后还需要继续移动游标 END LOOP ; CLOSE mycur ; -- 关闭游标 END ; / 以上可以发现，在循环之前需要先将游标的指针向下移动，同时把内容给erow，之后判断是否还存在记录，如果存在了记录，则循环输出，输出之

后继续向下移动游标，直到没有任何内容为止。 也可以使用LOOP循环的方式操作游标。

CURSOR mycur IS SELECT * FROM emp ; -- 声明一个游标，游标的名称是mycur DECLARE erow emp%ROWTYPE ; -- 此变量可以装下一行的emp记录 BEGIN OPEN mycur ; -- 打开游标 LOOP CURSOR mycur IS SELECT * FROM emp ; -- 声明一个游标，游标的名称是mycur