PL/SQL编程基础

实践目的

　　(1) 掌握PL/SQL块结构、PL/SQL的基本语法、PL/SQL的控制结构。

　　(2) 掌握PL/SQL块中使用复合数据类型和游标的方法。

实践内容

任务中涉及的数据表是scott用户给出的表。建议将下面所做的练习内容保存起来，以便在实践7中在此基础上进行修改，注意比较未命名的PL/SQL与命名的PL/SQL的差别。

（1）输出1-1000能被3和5整除的数

（2）求1-100奇数之和

（3）输出10号部门的员工号，员工名和公资。

（4）EMP表中职工号7788的职工，如果工资小于3000那么把工资更改为3000：

（5）查询工资前3名员工的姓名、工资信息

（6）根据emp表新建一个emp0表，并向表里添加10条数据empno列的值在最大员工号基础上每次加5，ename为’Unknown’

实验步骤：

任务中涉及的数据表是scott用户给出的表。建议将下面所做的练习内容保存起来，以便在实践7中在此基础上进行修改，注意比较未命名的PL/SQL与命名的PL/SQL的差别。

（1） 输出1-1000能被3和5整除的数

SQL> conn scott/tiger;

已连接。

SQL>  set serveroutput on;

SQL>  declare

  2   a int:=1;

  3   begin  for a in 1..1000

  4      loop

  5          if mod(a,3)=0 and mod(a,5)=0

  6      then

  7           dbms_output.put_line(‘a:‘||a);

  8          end if;

  9      end loop;

 10   end;

 11  /

a:15

a:30

a:45

a:60

a:75

a:90

a:105

a:120

a:135

a:150

a:165

a:180

a:195

a:210

a:225

a:240

a:255

a:270

a:285

a:300

a:315

a:330

a:345

a:360

a:375

a:390

a:405

a:420

a:435

a:450

a:465

a:480

a:495

a:510

a:525

a:540

a:555

a:570

a:585

a:600

a:615

a:630

a:645

a:660

a:675

a:690

a:705

a:720

a:735

a:750

a:765

a:780

a:795

a:810

a:825

a:840

a:855

a:870

a:885

a:900

a:915

a:930

a:945

a:960

a:975

a:990

PL/SQL 过程已成功完成。

（2） 求1-100奇数之和

SQL> declare

  2  a int:=0;

  3  sm int:=0;

  4  begin

  5     for i in 1..100

  6     loop

  7         a:=i;

  8         if mod(a,2)=1

  9         then

 10         sm:=sm+a;

 11         end if;

 12      end loop;

 13  dbms_output.put_line(‘sum:‘||sm);

 14  end;

 15  /

sum:2500

PL/SQL 过程已成功完成。

（3） 输出10号部门的员工号，员工名和公资。

第一种方法：（SQL语句）

SQL> select empno,ename,sal from emp where deptno=‘10‘;

     EMPNO ENAME             SAL

---------- ---------- ----------

      7782 CLARK            2450

      7839 KING             5000

      7934 MILLER           1300

第二种方法：（plsql）

（4） EMP表中职工号7788的职工，如果工资小于3000那么把工资更改为3000：

第一种方法：

SQL> DECLARE

  2  v_empno emp.empno%TYPE :=&empno;

  3  v_sal emp.sal%TYPE;

  4  BEGIN

  5  SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;

  6  IF v_sal<=3000 THEN

  7  UPDATE emp SET sal=3000 WHERE empno=v_empno;

  8  DBMS_OUTPUT.PUT_LINE(‘编码为‘||v_empno||‘员工工资已更新!‘);

  9  ELSE

 10  DBMS_OUTPUT.PUT_LINE(‘编码为‘||v_empno||‘员工工资已经超过规定值!‘);

 11  END IF;

 12  EXCEPTION

 13  WHEN NO_DATA_FOUND THEN

 14  DBMS_OUTPUT.PUT_LINE(‘数据库中没有编码为‘||v_empno||‘的员工‘);

 15  WHEN TOO_MANY_ROWS THEN

 16  DBMS_OUTPUT.PUT_LINE(‘程序运行错误!请使用游标‘);

 17  WHEN OTHERS THEN

 18  DBMS_OUTPUT.PUT_LINE(‘发生其它错误!‘);

 19  END;

 20

 21  /

输入 empno 的值:  7788

原值    2: v_empno emp.empno%TYPE :=&empno;

新值    2: v_empno emp.empno%TYPE :=7788;

PL/SQL 过程已成功完成。

第二种方法：

PL/SQL 过程已成功完成。

SQL> DECLARE

  2  v_empno emp.empno%TYPE :=&empno;

  3  no_result EXCEPTION;

  4  BEGIN

  5  UPDATE emp SET sal=3000 WHERE sal<3000 and empno=v_empno;

  6  IF SQL%NOTFOUND THEN

  7  RAISE no_result;

  8  END IF;

  9  EXCEPTION

 10  WHEN no_result THEN

 11  DBMS_OUTPUT.PUT_LINE(‘你的数据更新语句失败了!‘);

 12  WHEN OTHERS THEN

 13  DBMS_OUTPUT.PUT_LINE(‘发生其它错误!‘);

 14  END;

 15  /

输入 empno 的值:  7788

原值    2: v_empno emp.empno%TYPE :=&empno;

新值    2: v_empno emp.empno%TYPE :=7788;

PL/SQL 过程已成功完成。

（5） 查询工资前3名员工的姓名、工资信息

第一种方法：利用SQL方法实现语句：

SQL> select ename,sal from emp where

  2     empno in (select empno from (select empno,sal from emp order by sal desc

  3     where rownum<4);

ENAME             SAL

---------- ----------

SCOTT            3000

KING             5000

FORD             3000

第二种方法：用plsql：

SQL> declare

  2    v_name varchar2(10);

  3    v_sal number(5);

  4    cursor emp_cursor is select ename,sal from emp order by sal desc;

  5    begin

  6    open emp_cursor;

  7    for i in 1..3

  8    loop

  9    fetch emp_cursor into v_name,v_sal;

 10    dbms_output.put_line(v_name||‘,‘||v_sal);

 11    end loop;

 12    close emp_cursor;

 13    end;

 14  /

PL/SQL 过程已成功完成。

（6）根据emp表新建一个emp0表，并向表里添加10条数据empno列的值在最大员工号基础上每次加5，ename为’Unknown’

SQL> declare

  2  var emp.empno%type;

  3  begin

  4     select empno into var from(select emp.empno,rownum as rno

  5     from emp order by emp.empno desc ) where rno<2;

  6     for i in 1..10

  7     loop

  8             var:=var+5;

  9             insert into  emp0 values (var,‘Unknow‘,‘IT‘,‘8888‘,to_date(‘2013-11-22‘,‘yyyy-

 10     mm-dd‘),9000,10,20);

 11     end loop;

 12  Commit;

 13  end;

 14  /

PL/SQL 过程已成功完成。

SQL> select * from emp0;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

      7939 Unknow     IT              8888 22-11月-13           9000         10         20

      7944 Unknow     IT              8888 22-11月-13           9000         10         20

      7949 Unknow     IT              8888 22-11月-13           9000         10         20

      7954 Unknow     IT              8888 22-11月-13           9000         10         20

      7959 Unknow     IT              8888 22-11月-13           9000         10         20

      7964 Unknow     IT              8888 22-11月-13           9000         10         20

      7969 Unknow     IT              8888 22-11月-13           9000         10         20

      7974 Unknow     IT              8888 22-11月-13           9000         10         20

      7979 Unknow     IT              8888 22-11月-13           9000         10         20

      7984 Unknow     IT              8888 22-11月-13           9000         10         20

已选择10行。

实验小结：

1. PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。PL/SQL块可以分为三类：1. 无名块：动态构造，只能执行一次。2. 子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。3. 触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

2. PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。在PL/SQL中可以使用的SQL语句有：INSERT，UPDATE，DELETE，SELECT INTO，COMMIT，ROLLBACK，SAVEPOINT。

3. pl/sql（procedural language/sql）是Oracle在标准的sql语言上的扩展，pl/sql不仅允许嵌入Sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。

4. 块（block）是pl/sql的基本程序单元，编写pl/sql程序实际上 就是编写pl/sql块。要完成相对简单的应用功能，可能只需要编写一个pl/sql快；但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其他的pl/sql快。

5. 在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现。显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。

6. 定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。使用%TYPE特性的优点在于： 1.所引用的数据库列的数据类型可以不必知道；2.所引用的数据库列的数据类型可以实时改变。