Oracle

Oracle基本PLSQL的使用实例详解

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

组成：

1 2 3 4 5

DECLARE： 声明部分  BEGIN 编写主题 EXCEPTION 捕获异常  END;

1.一个简单的PL/SQL块

1 2 3 4 5 6

DECLARE    i number; BEGIN   I:=30;   DBMS_OUTPUT.put_line('I的内容为:'||i); END;

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

执行之后发现没有任何的输出。因为 Oracle 在系统设置中默认设置了输出不显示，如果要 显示的话，输入以下命令：
set serveroutput on;如果是使用Toad，则可以在Desktop中选择PLSQL，并打开DBMS OUTPUT。

带异常的语句块

1 2 3 4 5 6 7 8

DECLARE   i number; BEGIN   i:=1/0; EXCEPTION   when ZERO_DIVIDE then   DBMS_OUTPUT.put_line("There is a Exception"); END;

注意：输出字符串应该使用单引号''。

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13

DECLARE   eno number;   en varchar(20); BEGIN   --输入的信息保存在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('没有此雇员'); END;

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

DECLARE   eno emp.empno%TYPE ;    en emp.ename%TYPE ;    mn emp.ename%TYPE ;    dn dept.dname%TYPE ;    dept dept %rowtype ;  BEGIN   -- 输入的信息保存在 eno 里   eno := &no ;   -- 之后根据 eno 的值，对数据库进行查询操作   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||'雇员的姓名为：'||en) ;   DBMS_OUTPUT.put_line('编号为：'||eno||'雇员的上级姓名为：'||mn) ;    DBMS_OUTPUT.put_line('编号为：'||eno||'雇员所在的部门：'||dn) ;    DBMS_OUTPUT.put_line(dept.deptno) ; EXCEPTION   WHEN no_data_found THEN    DBMS_OUTPUT.put_line('没有此雇员') ; END ;

说明：

• no_data_found 一种异常类型：没有发现数据
• emp.empno%TYPE ; ：表示以 emp 表中的 empno 字段的类型定义变量
• e.ename,m.ename,d.dname INTO en,mn,dn：一次可以同时放进去多个值
dept dept %rowtype ;  表示 dept 是一行数据
PL/SQL 之中也包含了：循环、分支等条件控制语句

Loop 循环（类似do…while）

格式：

1 2 3 4

LOOP   循环的语句 ; EXIT WHEN 终止条件 ;   循环条件必须更改 ; END LOOP ;

例如：循环输出 1~10。

1 2 3 4 5 6 7 8 9 10 11

DECLARE   countNum NUMBER ;  BEGIN   --必须赋初值   countNum := 1 ;    LOOP     DBMS_OUTPUT.put_line('countNum = '||countNum) ;    EXIT WHEN countNum>10 ;     countNum := countNum + 1 ;    END LOOP ;  END ;

注意：count关键字只能在sql语句中使用

此循环是先执行一次之后再进行判断，执行结果到11结束循环

5.while 循环

格式：

1 2 3 4

WHILE(判断循环的条件)  LOOP 循环的语句 ;    循环条件的改变 ; END LOOP ;

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

1 2 3 4 5 6 7 8 9 10 11

DECLARE   countNum NUMBER ;  BEGIN   --必须赋初值   countNum := 1 ;    WHILE(countNum<10)    LOOP     DBMS_OUTPUT.put_line('countNum = '||countNum) ;     countNum := countNum + 1 ;    END LOOP ;  END ;

此语句，是先判断，如果满足条件则继续执行循环体，执行结果到9结束循环

6.for 循环

1 2 3 4 5 6 7 8 9 10 11 12 13 14

格式： FOR 变量名称 in 变量的初始值..结束值    LOOP   循环语句 ;    END LOOP ;  修改上例： DECLARE   countNum NUMBER ;  BEGIN   FOR countNum IN 1..10    LOOP      DBMS_OUTPUT.put_line('countNum = '||countNum) ;   END LOOP ;  END ;

此语句countNum大于等于1小于等于10，最后输出1-10

7.if语句

条件判断

格式：

1 2 3

IF 条件 THEN    满足条件时，执行此语句  END IF ;

例：

1 2 3 4 5 6 7 8

DECLARE   countNum NUMBER ;  BEGIN   countNum := 11 ;   IF countNum>10 THEN      DBMS_OUTPUT.put_line('countNum = '||countNum) ;    END IF ; END ;

条件语句，满足条件，执行一次

8.IF…ELSE 语句

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

例：

1 2 3 4 5 6 7 8 9 10

DECLARE   countNum NUMBER ; BEGIN   countNum := 1 ;   IF countNum>10 THEN      DBMS_OUTPUT.put_line('countNum = '||countNum) ;    ELSE     DBMS_OUTPUT.put_line('条件不不成立') ;   END IF ;  END ;

条件不满足执行else后面的语句

9.if…elsif…else 语句

例：

1 2 3 4 5 6 7 8 9 10 11 12

DECLARE   countNum NUMBER ; BEGIN   countNum := 1 ;   IF countNum>10 THEN      DBMS_OUTPUT.put_line('countNum = '||countNum);    ELSIF countNum<5 THEN     DBMS_OUTPUT.put_line('值小于 5');   ELSE      DBMS_OUTPUT.put_line('条件不满足');    END IF ; END ;

多重判断语句，满足条件执行对应的跳转

10.goto语句

无条件跳转语句

用于程序控制非条件跳至指定标签<<???>>。不易控制和维护，慎用！

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

DECLARE   eno emp.empno%TYPE ;    sal emp.sal%TYPE ;  BEGIN   eno := &en ;   SELECT sal INTO sal FROM emp WHERE empno=eno ;    IF sal>3500 THEN     goto po1 ;   ELSIF sal>2000 THEN     goto po2 ;    ELSE     goto po3 ;   END IF;   --标签   <<po1>> DBMS_OUTPUT.put_line('高工资。。。') ;   <<po2>> DBMS_OUTPUT.put_line('中等工资。。') ;   <<po3>> DBMS_OUTPUT.put_line('低工资。。。') ;  END;