PLSQL练习题一.doc

PL/SQL练习题在Oracle中所有的过程都是以PL/SQL块开始的，掌握PL/SQL对于过程的开发是非常用用处的。PL/SQL块的定义格式：DECLARE声明一些变量BEGIN程序的开发EXCEPTION程序中有可能出现异常END ;/范例：使用以上的语句，创建一个简单的程序块DECLAREiNUMBER;BEGINi:=30 ;- 将变量i的内容设置成30DBMS_OUTPUT.PUT_LINE(i的内容是： | i) ;END ;/以上一个基本的语句块已经写完了，但是里面并没有任何的输出，之所以这样，主要原因在于SQLPLUSW中的系统输出没有打开，需要通过以下的命令完成：SET SERVEROUTPUT ON ;以上是一个简单的语句块，但是在PLSQL块中也可以直接进行数据库的查询操作。范例：要求输入一个雇员的编号，之后显示出此编号雇员的姓名 如果要想输入雇员编号的话，第八章存在一个替代变量“&”DECLAREenoNUMBER ;- 接收传入的编号nameVARCHAR2(8) ;- 保存查询出来的雇员姓名BEGINDBMS_OUTPUT.PUT_LINE(请输入雇员编号：) ;eno:=&empnonumber ;SELECT ename INTO name FROM emp WHERE empno=eno ;DBMS_OUTPUT.PUT_LINE(编号为 | eno |的雇员姓名是： | name) ;END ;/此时已经可以查询出来了，但是以上的代码是否会存在其他问题呢？如果现在输入的编号不存在则有可能返回错误信息，那么为了让代码在出现错误之后依然可以正确的使用，则可以加入异常处理的操作机制。DECLAREenoNUMBER ;- 接收传入的编号nameVARCHAR2(8) ;- 保存查询出来的雇员姓名BEGINDBMS_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 THENDBMS_OUTPUT.PUT_LINE(没有这个员工！) ;END ;/但是，从以上的程序中可以发现，在SQLPLUSW中输出实际上并没有真正的意义，所以显示的格式也就没有必要做任何的调整。在PL/SQL块中也可以编写循环、判断等语句。可以使用循环操作：LOOP，循环DECLAREiNUMBER ;BEGINi := 1 ;- 给变量i一个初始值LOOPDBMS_OUTPUT.PUT_LINE(i = | i) ;EXIT WHEN i=10 ;i := i + 1 ;END LOOP ;END ;/这个时候一个循环语句就已经编写完成了。此循环属于先执行后判断，所以可以使用另外一种循环语句：WHILELOOP。DECLAREiNUMBER ;BEGINi := 1 ;- 给变量i一个初始值WHILE(i10 THENDBMS_OUTPUT.PUT_LINE(记录大于10条) ;END IF ;END ;/但是，以上的内容都属于固定好的结果，现在要求可以将emp表中的记录数进行验证。DECLAREcounNUMBER ;BEGINSELECT COUNT(empno) INTO coun FROM emp ;IF coun10 THENDBMS_OUTPUT.PUT_LINE(记录大于10条) ;END IF ;END ;/也可以使用if.else语句DECLAREcounNUMBER ;BEGINSELECT COUNT(empno) INTO coun FROM emp ;coun := 3 ;IF coun10 THENDBMS_OUTPUT.PUT_LINE(记录大于10条) ;ELSEDBMS_OUTPUT.PUT_LINE(记录不大于10条) ;END IF ;END ;/If语句上还能继续扩展，ifelseif.else.但是需要注意的是，在Oracle中的else if要换成elsifDECLAREcounNUMBER ;BEGINSELECT COUNT(empno) INTO coun FROM emp ;coun := 3 ;IF coun10 THENDBMS_OUTPUT.PUT_LINE(记录大于10条) ;ELSIF coun5000 THENUPDATE emp SET sal=5000 WHERE empno=eno ;ELSEUPDATE emp SET sal=sal*1.1 WHERE empno=eno ;END IF ;ELSIF dno=20 THENIF esal*1.25000 THENUPDATE emp SET sal=5000 WHERE empno=eno ;ELSEUPDATE emp SET sal=sal*1.2 WHERE empno=eno ;END IF ;ELSIF dno=30 THENIF esal*1.35000 THENUPDATE emp SET sal=5000 WHERE empno=eno ;ELSEUPDATE emp SET sal=sal*1.3 WHERE empno=eno ;END IF ;ELSEnull;- 如果都不满足，则什么也不操作END IF ;END ;/3.2、游标游标：是内存中用于装载记录的一个区域。如果要想使用游标，需要按照以下的步骤进行处理：1、声明游标2、打开游标3、取得游标进行PL/SQL的使用，将内容放到变量之中4、关闭游标问题：对于各个基本类型：NUMBER、VARCHAR2都可以直接直接使，但是现在需要有一种类型，此种类型可以直接装下一行数据。如果要想装下一行数据的话要使用ROWTYPE表示类型。每一个ROWTYPE表示一行数据DECLAREerowemp%ROWTYPE ;- 此变量可以装下一行的emp记录enoemp.empno%TYPE ;- 使用emp表中的empno字段的类型定义eno变量BEGINeno := 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之后，下面就可以使用游标进行操作了，游标需要依次取出每一行的记录。范例：使用游标依次输出雇员表中的编号和雇员姓名DECLAREerowemp%ROWTYPE ;- 此变量可以装下一行的emp记录CURSOR mycur IS SELECT * FROM emp ;- 声明一个游标，游标的名称是mycurBEGINOPEN 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循环的方式操作游标。DECLAREerowemp%ROWTYPE ;- 此变量可以装下一行的emp记录CURSOR mycur IS SELECT * FROM emp ;- 声明一个游标，游标的名称是mycurBEGINOPEN mycur ;- 打开游标LOOPFETCH mycur INTO erow ;- 移动游标，并将移动后的内容放到erow之中EXIT WHEN mycur%NOTFOUND ;- 输出之后还需要继续移动游标DBMS_OUTPUT.PUT_LINE(erow.empno | - | erow.ename) ;END LOOP ;CLOSE mycur ;- 关闭游标END ;/此时，结果同样出来了，但是使用以上的循环操作最麻烦的地方在于需要手工打开游标并且手工关闭游标，手工移动游标，那么在Oracle中为了简化这种操作，对于for循环提供新的支持。DECLAREerowemp%ROWTYPE ;- 此变量可以装下一行的emp记录CURSOR mycur IS SELECT * FROM emp ;- 声明一个游标，游标的名称是mycurBEGINFOR erow IN mycur LOOP- 自动打开、关闭、移动游标DBMS_OUTPUT.PUT_LINE(erow.empno | - | erow.ename) ;END LOOP ;END ;/实际上，所谓的游标就是可以将数据一条条的进行处理操作。练习：一次性上涨所有雇员的工资，增长工资按照以下的原则进行： 10部门人员工资上涨10% 20部门人员工资上涨20% 30部门人员工资上涨30%DECLAREerowemp%ROWTYPE ;- 此变量可以装下一行的emp记录CURSOR mycur IS SELECT * FROM emp ;- 声明一个游标，游标的名称是mycurBEGINFOR erow IN mycur LOOP- 自动打开、关闭、移动游标IF erow.deptno=10 THENIF erow.sal*1.15000 THENUPDATE emp SET sal=5000 WHERE empno=erow.empno ;ELSEUPDATE emp SET sal=sal*1.1 WHERE empno=erow.empno ;END IF ;ELSIF erow.deptno=20 THENIF erow.sal5000 THENUPDATE emp SET sal=5000 WHERE empno=erow.empno ;ELSEUPDATE emp SET sal=sal*1.2 WHERE empno=erow.empno ;END IF ;ELSIF erow.deptno=30 THENIF erow.sal5000 THENUPDATE emp SET sal=5000 WHERE empno=erow.empno ;ELSEUPDATE emp SET sal=sal*1.3 WHERE empno=erow.empno ;END IF ;ELSEnull;- 如果都不满足，则什么也不操作END IF ;END LOOP ;END ;/对于此种复杂的方式只能通过游标进行依次的操作。游标可以增加参数，这种游标称为参数游标，例如：要求输入一个部门编号，查询出部门的雇员信息。DECLAREerowemp%ROWTYPE ;- 此变量可以装下一行的emp记录CURSOR mycur(dno emp.deptno%TYPE) IS SELECT * FROM emp WHERE deptno=dno ;- 声明一个游标，游标的名称是mycurBEGINFOR erow IN mycur(&deptnumber) LOOP- 自动打开、关闭、移动游标DBMS_OUTPUT.PUT_LINE(erow.empno | - | erow.ename) ;END LOOP ;END ;/之前可以发现在游标中可以使用%FOUND、%NOTFOUND、%ROWCOUNT、%ISOPEN。DECLAREerowemp%ROWTYPE ;- 此变量可以装下一行的emp记录CURSOR mycur IS SELECT * FROM emp ;- 声明一个游标，游标的名称是mycurBEGINFOR erow IN mycur LOOP- 自动打开、关闭、移动游标DBMS_OUTPUT.PUT_LINE(mycur%ROWCOUNT | 、 |erow.empno | - | erow.ename) ;END LOOP ;END ;/可以发现ROWCOUNT与ROWNUM功能是一样的，可以通过此功能求出行号。3.3、过程过程 = 过程的声明 + PLSQL块范例：创建一个过程CREATE OR REPLACE PROCEDURE myprocASBEGINnull ;END ;/范例：定义一个完整的过程，可以接收参数，并且根据雇员的编号找出其工资CREATE OR REPLACE PROCEDURE myproc(eno emp.empno%TYPE)ASesalemp.sal%TYPE ;BEGINSELECT sal INTO esal FROM emp WHERE empno=eno ;DBMS_OUTPUT.PUT_LINE(工资是： | esal) ;END ;/此时，过程已经成功的创建了。过程是需要调用的，使用exec 过程(参数)的形式调用。exec myproc(7369)此时，一个过程已经正常的执行完毕。范例：定义一个过程，通过此过程可以自动加入部门信息CREATE OR REPLACE PROCEDURE myproc(dno dept.deptno%TYPE,name dept.dname%TYPE,lo dept.loc%TYPE)ASBEGININSERT INTO dept(deptno,dname,loc) VALUES (dno,name,lo) ;END ;/exec myproc(50,技术部,北京) ;范例：但是进一步思考的话，会发现，部门的编号不应该存在重复，如果重复的话不应该执行插入CREATE OR REPLACE PROCEDURE myproc(dno dept.deptno%TYPE,name dept.dname%TYPE,lo dept.loc%TYPE)AScounNUMBER ;BEGINSELECT COUNT(deptno) INTO coun FROM dept WHERE deptno=dno ;IF coun0 THENDBMS_OUTPUT.PUT_LINE(此部门信息已经存在！) ;ELSEINSERT INTO dept(deptno,dname,loc) VALUES (dno,name,lo) ;END IF ;END ;/exec mypro