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块还可以接收用户的输入信息，例如：现在要求用户输入一个雇员编号，之后根据输入的内容进行查询，查询雇员的姓名。
 • 用户的输入信息使用“&”完成。
DECLARE
 eno NUMBER ;
 en VARCHAR(30) ;
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 ;
/

在以上的查询中再进一步：可以根据雇员的编号查出姓名及其领导的姓名和所在的部门，进行显示。
DECLARE
 eno emp.empno%TYPE ;
 en emp.ename%TYPE ;
 mn emp.ename%TYPE ;
 dn dept.dname%TYPE ;
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) ;
EXCEPTION
 WHEN no_data_found THEN
  DBMS_OUTPUT.put_line('没有此雇员') ;
END ;
/

说明：
 • 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。
DECLARE
 cou NUMBER ;
BEGIN
 -- 必须给一个初始值
 cou := 1 ;
 LOOP 
  DBMS_OUTPUT.put_line('cou = '||cou) ;
  EXIT WHEN cou>10 ;
  cou := cou + 1 ;
 END LOOP ;
END ;
/
 此循环是先执行一次之后再进行判断

2、 while循环
格式：
 while(判断循环的条件) loop
  循环的语句 ;
  循环条件的改变 ;
 End loop ;

使用此语句修改上面的程序：
DECLARE
 cou NUMBER ;
BEGIN
 -- 必须给一个初始值
 cou := 1 ;
 WHILE(cou<10) LOOP
  DBMS_OUTPUT.put_line('cou = '||cou) ;
  cou := cou + 1 ;
 END LOOP ;
END ;
/
 此语句，是先判断，之后如果条件满足则执行，与while循环类似。

3、 for循环
格式：
 FOR 变量名称 in 变量的初始值..结束值 LOOP
  循环语句 ;
 END LOOP ;
DECLARE
 cou NUMBER ;
BEGIN
 FOR cou IN 1..10 LOOP
  DBMS_OUTPUT.put_line('cou = '||cou) ;
 END LOOP ;
END ;
/

4、 IF语句
 条件判断
格式：
 IF 条件 THEN
  满足条件时，执行此语句
 END IF ;
DECLARE
 cou NUMBER ;
BEGIN
 cou := 11 ;
 IF cou>10 THEN
  DBMS_OUTPUT.put_line('cou = '||cou) ;
 END IF ;
END ;
/

5、 IF…ELSE语句
 如果IF满足了，则执行，否则执行ELSE
DECLARE
 cou NUMBER ;
BEGIN
 cou := 1 ;
 IF cou>10 THEN
  DBMS_OUTPUT.put_line('cou = '||cou) ;
 ELSE
  DBMS_OUTPUT.put_line('条件不满足') ;
 END IF ;
END ;
/

6、 IF…ELSIF…ELSE语句
DECLARE
 cou NUMBER ;
BEGIN
 cou := 1 ;
 IF cou>10 THEN
  DBMS_OUTPUT.put_line('cou = '||cou) ;
 ELSIF cou<5 THEN
  DBMS_OUTPUT.put_line('值小于5') ;
 ELSE
  DBMS_OUTPUT.put_line('条件不满足') ;
 END IF ;
END ;
/

问题1：
 输入一个雇员的编号，如果其工资高于3500，则显示高工资，工资大于2000，则显示中等工资，工资小于2000的则认为是低等工资。
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
  DBMS_OUTPUT.put_line('高工资。。。') ;
 ELSIF sal>2000 THEN
  DBMS_OUTPUT.put_line('中等工资。。') ;
 ELSE
  DBMS_OUTPUT.put_line('底工资。。。') ;
 END IF ;
END ;
/

问题2：
 输入一个雇员编号，根据它所在的部门涨工资，规则：
 • 10部门上涨10%
 • 20部门上涨20%
 • 30部门上涨30%
 所有部门的上涨工资，最不能超过5000，如果超过5000，则工资就为5000。
DECLARE
 eno emp.empno%TYPE ;
 dno emp.deptno%TYPE ;
 sal emp.sal%TYPE ;
BEGIN
 eno := &en ;
 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=5000 WHERE empno=eno ;
  ELSE
   UPDATE emp SET sal=sal*1.2 WHERE empno=eno ;
  END IF ;
 ELSIF dno=30 THEN
  IF sal*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 ;
/

7、 GOTO语句
 无条件跳转语句
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 ;
/

游标
 操作步骤：
 • 声明游标
 • 打开游标
 • 取出结果，此时的结果取出的是一行数据
 • 关闭游标

到底那种类型可以把一行的数据都装进来
 • 此时使用ROWTYPE类型，此类型表示可以把一行的数据都装进来。
例如：查询雇员编号为7369的信息（肯定是一行信息）。
DECLARE
 eno emp.empno%TYPE ;
 empInfo emp%ROWTYPE ;
BEGIN
 eno := &en ;
 SELECT * INTO empInfo FROM emp WHERE empno=eno ;
 DBMS_OUTPUT.put_line('雇员编号：'||empInfo.empno) ;
 DBMS_OUTPUT.put_line('雇员姓名：'||empInfo.ename) ;
END ;
/

编写第一个游标，输出全部的信息。
DECLARE
 -- 声明游标
 CURSOR mycur IS SELECT * FROM emp ;
 empInfo emp%ROWTYPE ;
BEGIN
 -- 游标操作使用循环，但是在操作之前必须先将游标打开
 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 ;
 END LOOP ;
END ;
/

也可以使用另外一种方式循环游标：LOOP…END LOOP;
DECLARE
 -- 声明游标
 CURSOR mycur IS SELECT * FROM emp ;
 empInfo emp%ROWTYPE ;
BEGIN
 -- 游标操作使用循环，但是在操作之前必须先将游标打开
 OPEN mycur ;
 LOOP
  -- 使游标向下一行
  FETCH mycur INTO empInfo ;
  EXIT WHEN mycur%NOTFOUND ;
  DBMS_OUTPUT.put_line('雇员编号：'||empInfo.empno) ;
  DBMS_OUTPUT.put_line('雇员姓名：'||empInfo.ename) ;
 END LOOP ;
END ;
/

注意1：
 在打开游标之前最好先判断游标是否已经是打开的。
 通过ISOPEN判断，格式：游标%ISOPEN
 IF mycur%ISOPEN THEN
  null ;
 ELSE
  OPEN mycur ;
 END IF ;

注意2：
 可以使用ROWCOUNT对游标所操作的行数进行记录。
DECLARE
 -- 声明游标
 CURSOR mycur IS SELECT * FROM emp ;
 empInfo emp%ROWTYPE ;
 cou NUMBER ;
BEGIN
 -- 游标操作使用循环，但是在操作之前必须先将游标打开
 IF mycur%ISOPEN THEN
  null ;
 ELSE
  OPEN mycur ;
 END IF ;
 LOOP
  -- 使游标向下一行
  FETCH mycur INTO empInfo ;
  EXIT WHEN mycur%NOTFOUND ;
  cou := mycur%ROWCOUNT ;
  DBMS_OUTPUT.put_line(cou||'雇员编号：'||empInfo.empno) ;
  DBMS_OUTPUT.put_line(cou||'雇员姓名：'||empInfo.ename) ;
 END LOOP ;
END ;
/

使用for循环操作游标（比较常用）
DECLARE
 -- 声明游标
 CURSOR mycur IS SELECT * FROM emp ;
 empInfo emp%ROWTYPE ;
 cou NUMBER ;
BEGIN
 -- 游标操作使用循环，但是在操作之前必须先将游标打开
 FOR empInfo IN mycur LOOP
  cou := mycur%ROWCOUNT ;
  DBMS_OUTPUT.put_line(cou||'雇员编号：'||empInfo.empno) ;
  DBMS_OUTPUT.put_line(cou||'雇员姓名：'||empInfo.ename) ;
 END LOOP ;
END ;
/

练习：
 一次性上涨全部雇员的工资。根据它所在的部门涨工资，规则：
 • 10部门上涨10%
 • 20部门上涨20%
 • 30部门上涨30%
 所有部门的上涨工资，最不能超过5000，如果超过5000，则工资就为5000。

DECLARE
 -- 声明游标
 CURSOR mycur IS SELECT * FROM emp ;
 empInfo emp%ROWTYPE ;
 cou NUMBER ;
BEGIN
 -- 游标操作使用循环，但是在操作之前必须先将游标打开
 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=5000 WHERE empno=empInfo.empno ;
   ELSE
    UPDATE emp SET sal=sal*1.2 WHERE empno=empInfo.empno ;
   END IF ;
  ELSIF empInfo.deptno=30 THEN
   IF empInfo.sal*1.3>5000 THEN
    UPDATE emp SET sal=5000 WHERE empno=empInfo.empno ;
   ELSE
    UPDATE emp SET sal=sal*1.3 WHERE empno=empInfo.empno ;
   END IF ;
  ELSE
   null ;
  END IF ;
 END LOOP ;
END ;
/

过程（存储过程）：
 过程 = 过程的声明 + PL/SQL块

现在定义一个简单的过程，就是打印一个数字
CREATE OR REPLACE PROCEDURE myproc
AS
 i NUMBER ;
BEGIN
 i := 100 ;
 DBMS_OUTPUT.put_line('i = '||i) ;
END ;
/

执行过程：
 exec 过程名字

下面编写一个过程，要求，可以传入部门的编号，部门的名称，部门的位置，之后调用此过程就可以完成部门的增加操作。
CREATE OR REPLACE PROCEDURE myproc(dno dept.deptno%TYPE,name dept.dname%TYPE,dl dept.loc%TYPE)
AS
 cou NUMBER ;
BEGIN
 -- 判断插入的部门编号是否存在，如果存在则不能插入
 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('部门插入成功！') ;
 ELSE
  DBMS_OUTPUT.put_line('部门已存在，无法插入！') ;
 END IF ; 
END ;
/

过程的参数类型：
 • 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)
AS
 cou NUMBER ;
BEGIN
 -- 判断插入的部门编号是否存在，如果存在则不能插入
 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('部门插入成功！') ;
  -- 修改dno的值
  dno := 1 ;
 ELSE
  DBMS_OUTPUT.put_line('部门已存在，无法插入！') ;
  dno := -1 ;
 END IF ;
END ;
/

编写PL/SQL块验证过程：
DECLARE
 deptno dept.deptno%TYPE ;
BEGIN 
 deptno := 12 ;
 myproc(deptno,'开发','南京') ;
 DBMS_OUTPUT.put_line(deptno) ;
END ;
/

OUT类型
 不带任何值进，只把值带出来。
CREATE OR REPLACE PROCEDURE myproc(dno OUT dept.deptno%TYPE)
AS

BEGIN
 dno := 10 ;
END ;
/
DECLARE
 deptno dept.deptno%TYPE ;
BEGIN 
 myproc(deptno) ;
 DBMS_OUTPUT.put_line(deptno) ;
END ;
/

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

定义一个函数：此函数可以根据雇员的编号查询出雇员的年薪
CREATE OR REPLACE FUNCTION myfun(eno emp.empno%TYPE) RETURN NUMBER
AS
 rsal NUMBER ;
BEGIN
 SELECT (sal+nvl(comm,0))*12 INTO rsal FROM emp WHERE empno=eno ;
 RETURN rsal ;
END ;
/

直接写SQL语句，调用此函数：
 SELECT myfun(7369) FROM dual ;