PLSQL

PL/SQL块

 PL/SQL块是在SQL语言之上发展起来的一种应用，可以集中的处理各种复杂的SQL操作。

组成：

 DECLARE：

  声明部分

 BEGIN

  编写趁许的主题

 EXCEPTION

  捕获异常

 END ;

 /

看一个简单的PL/SQL块

DECLARE

 i NUMBER ;

BEGIN

 i := 30 ;

 DBMS_OUTPUT.put_line('I的内容为：'||i) ;

END ;

/

此时，直接执行程序即可。

执行之后发现没有任何的输出。因为Oracle在系统设置中默认设置了输出不显示，如果要显示的话，输入以下命令：

 set serveroutput on

PL/SQL块还可以接收用户的输入信息，例如：现在要求用户输入一个雇员编号，之后根据输入的内容进行查询，查询雇员的姓名。

 • 用户的输入信息使用“&”完成。

 eno NUMBER ;

 en VARCHAR(30) ;

 -- 输入的信息保存在eno里

 eno := &no ;

 -- 之后根据eno的值，对数据库进行查询操作

 SELECT ename INTO en FROM emp WHERE empno=eno ;

 DBMS_OUTPUT.put_line('编号为：'||eno||'雇员的姓名为：'||en) ;

EXCEPTION

 WHEN no_data_found THEN

  DBMS_OUTPUT.put_line('没有此雇员') ;

在以上的查询中再进一步：可以根据雇员的编号查出姓名及其领导的姓名和所在的部门，进行显示。

 eno emp.empno%TYPE ;

 en emp.ename%TYPE ;

 mn emp.ename%TYPE ;

 dn dept.dname%TYPE ;

 SELECT e.ename,m.ename,d.dname INTO en,mn,dn FROM emp e,dept d,emp m WHERE e.empno=7369 AND e.mgr=m.empno AND e.deptno=d.deptno ;

 DBMS_OUTPUT.put_line('编号为：'||eno||'雇员的上级姓名为：'||mn) ;

 DBMS_OUTPUT.put_line('编号为：'||eno||'雇员所在的部门：'||dn) ;

说明：

 • emp.empno%TYPE ;：表示以emp表中的empno字段的类型定义变量

 • e.ename,m.ename,d.dname INTO en,mn,dn：一次可以同时放进去多个值

PL/SQL之中也包含了：循环、分支等条件控制语句

1、 Loop循环

格式：

 LOOP

  循环的语句 ;

  EXIT WHEN 终止条件 ;

  循环条件必须更改 ;

 END LOOP ;

循环输出1~10。

 cou NUMBER ;

 -- 必须给一个初始值

 cou := 1 ;

 LOOP 

  DBMS_OUTPUT.put_line('cou = '||cou) ;

  EXIT WHEN cou>10 ;

  cou := cou + 1 ;

 此循环是先执行一次之后再进行判断

2、 while循环

 while(判断循环的条件) loop

  循环条件的改变 ;

 End loop ;

使用此语句修改上面的程序：

 WHILE(cou<10) LOOP

 此语句，是先判断，之后如果条件满足则执行，与while循环类似。

3、 for循环

 FOR 变量名称 in 变量的初始值..结束值 LOOP

  循环语句 ;

 FOR cou IN 1..10 LOOP

4、 IF语句

 条件判断

 IF 条件 THEN

  满足条件时，执行此语句

 END IF ;

 cou := 11 ;

 IF cou>10 THEN

5、 IF…ELSE语句

 如果IF满足了，则执行，否则执行ELSE

 ELSE

  DBMS_OUTPUT.put_line('条件不满足') ;

6、 IF…ELSIF…ELSE语句

 ELSIF cou<5 THEN

  DBMS_OUTPUT.put_line('值小于5') ;

问题1：

 输入一个雇员的编号，如果其工资高于3500，则显示高工资，工资大于2000，则显示中等工资，工资小于2000的则认为是低等工资。

 sal emp.sal%TYPE ;

 eno := &en ;

 SELECT sal INTO sal FROM emp WHERE empno=eno ;

 IF sal>3500 THEN

  DBMS_OUTPUT.put_line('高工资。。。') ;

 ELSIF sal>2000 THEN

  DBMS_OUTPUT.put_line('中等工资。。') ;

  DBMS_OUTPUT.put_line('底工资。。。') ;

问题2：

 输入一个雇员编号，根据它所在的部门涨工资，规则：

 • 10部门上涨10%

 • 20部门上涨20%

 • 30部门上涨30%

 所有部门的上涨工资，最不能超过5000，如果超过5000，则工资就为5000。

 dno emp.deptno%TYPE ;

 SELECT deptno,sal INTO dno,sal FROM emp WHERE empno=eno ;

 IF dno=10 THEN

  IF sal*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 sal*1.2>5000 THEN

   UPDATE emp SET sal=sal*1.2 WHERE empno=eno ;

 ELSIF dno=30 THEN

  IF sal*1.3>5000 THEN

   UPDATE emp SET sal=sal*1.3 WHERE empno=eno ;

  null ;

7、 GOTO语句

 无条件跳转语句

  goto po1 ;

  goto po2 ;

  goto po3 ;

 <<po1>>

 <<po2>>

 <<po3>>

游标

 操作步骤：

 • 声明游标

 • 打开游标

 • 取出结果，此时的结果取出的是一行数据

 • 关闭游标

到底那种类型可以把一行的数据都装进来

 • 此时使用ROWTYPE类型，此类型表示可以把一行的数据都装进来。

例如：查询雇员编号为7369的信息（肯定是一行信息）。

 empInfo emp%ROWTYPE ;

 SELECT * INTO empInfo FROM emp WHERE empno=eno ;

 DBMS_OUTPUT.put_line('雇员编号：'||empInfo.empno) ;

 DBMS_OUTPUT.put_line('雇员姓名：'||empInfo.ename) ;

编写第一个游标，输出全部的信息。

 -- 声明游标

 CURSOR mycur IS SELECT * FROM emp ;

 -- 游标操作使用循环，但是在操作之前必须先将游标打开

 OPEN mycur ;

 -- 使游标向下一行

 FETCH mycur INTO empInfo ;

 -- 判断此行是否有数据被发现

 WHILE (mycur%FOUND) LOOP

  DBMS_OUTPUT.put_line('雇员编号：'||empInfo.empno) ;

  DBMS_OUTPUT.put_line('雇员姓名：'||empInfo.ename) ;

  -- 修改游标，继续向下

  FETCH mycur INTO empInfo ;

也可以使用另外一种方式循环游标：LOOP…END LOOP;

  -- 使游标向下一行

  EXIT WHEN mycur%NOTFOUND ;

注意1：

 在打开游标之前最好先判断游标是否已经是打开的。

 通过ISOPEN判断，格式：游标%ISOPEN

 IF mycur%ISOPEN THEN

  OPEN mycur ;

注意2：

 可以使用ROWCOUNT对游标所操作的行数进行记录。

  cou := mycur%ROWCOUNT ;

  DBMS_OUTPUT.put_line(cou||'雇员编号：'||empInfo.empno) ;

  DBMS_OUTPUT.put_line(cou||'雇员姓名：'||empInfo.ename) ;

使用for循环操作游标（比较常用）

 FOR empInfo IN mycur LOOP

练习：

 一次性上涨全部雇员的工资。根据它所在的部门涨工资，规则：

  IF empInfo.deptno=10 THEN

   IF empInfo.sal*1.1>5000 THEN

    UPDATE emp SET sal=5000 WHERE empno=empInfo.empno ;

   ELSE

    UPDATE emp SET sal=sal*1.1 WHERE empno=empInfo.empno ;

   END IF ;

  ELSIF empInfo.deptno=20 THEN

   IF empInfo.sal*1.2>5000 THEN

    UPDATE emp SET sal=sal*1.2 WHERE empno=empInfo.empno ;

  ELSIF empInfo.deptno=30 THEN

   IF empInfo.sal*1.3>5000 THEN

    UPDATE emp SET sal=sal*1.3 WHERE empno=empInfo.empno ;

   null ;

过程（存储过程）：

 过程 = 过程的声明 + PL/SQL块

现在定义一个简单的过程，就是打印一个数字

CREATE OR REPLACE PROCEDURE myproc

AS

 i := 100 ;

 DBMS_OUTPUT.put_line('i = '||i) ;

执行过程：

 exec 过程名字

下面编写一个过程，要求，可以传入部门的编号，部门的名称，部门的位置，之后调用此过程就可以完成部门的增加操作。

CREATE OR REPLACE PROCEDURE myproc(dno dept.deptno%TYPE,name dept.dname%TYPE,dl dept.loc%TYPE)

 -- 判断插入的部门编号是否存在，如果存在则不能插入

 SELECT COUNT(deptno) INTO cou FROM dept WHERE deptno=dno ;

 IF cou=0 THEN

  -- 可以增加新的部门

  INSERT INTO dept(deptno,dname,loc) VALUES(dno,name,dl) ;

  DBMS_OUTPUT.put_line('部门插入成功！') ;

  DBMS_OUTPUT.put_line('部门已存在，无法插入！') ;

 END IF ; 

过程的参数类型：

 • IN：值传递，默认的

 • IN OUT：带值进，带值出

 • OUT：不带值进，带值出

IN OUT类型：

CREATE OR REPLACE PROCEDURE myproc(dno IN OUT dept.deptno%TYPE,name dept.dname%TYPE,dl dept.loc%TYPE)

  -- 修改dno的值

  dno := 1 ;

  dno := -1 ;

编写PL/SQL块验证过程：

 deptno dept.deptno%TYPE ;

BEGIN 

 deptno := 12 ;

 myproc(deptno,'开发','南京') ;

 DBMS_OUTPUT.put_line(deptno) ;

OUT类型

 不带任何值进，只把值带出来。

CREATE OR REPLACE PROCEDURE myproc(dno OUT dept.deptno%TYPE)

 dno := 10 ;

 myproc(deptno) ;

函数：

 函数就是一个有返回值的过程。

定义一个函数：此函数可以根据雇员的编号查询出雇员的年薪

CREATE OR REPLACE FUNCTION myfun(eno emp.empno%TYPE) RETURN NUMBER

 rsal NUMBER ;

 SELECT (sal+nvl(comm,0))*12 INTO rsal FROM emp WHERE empno=eno ;

 RETURN rsal ;

直接写SQL语句，调用此函数：

 SELECT myfun(7369) FROM dual ;

本文转自 李兴华 51CTO博客，原文链接：http://blog.51cto.com/lixinghua/91221，如需转载请自行联系原作者