PL/SQL块结构和组成元素

PL/SQL块

PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。

DECLARE
/* 声明部分：在此声明PL/SQL用到的变量，类型及游标，以及局部的存储过程和函数*/

BEGIN
/* 执行部分：过程及SQL语句，即程序的主要部分 */

EXCEPTION
/* 执行异常部分： 错误处理*/
END;

其中，执行部分是必须的。

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

标识符

PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同。要求和限制有：

• 标识符名不能超过30字符
• 第一个字符必须为字母
• 不区分大小写
• 不能使用‘-’
• 不能使用SQL保留字

注意，一般不能把变量名声明与表中字段名完全一样，如果这样可能得到不正确的结果。例如：

DECLARE
   Ename varchar2(20):='KING';
BEGIN
   DELETE FROM emp WHERE ename=ename;
END;

执行后，会删除所有的记录，而不是KING的记录。

记录类型

记录类型是把逻辑相关的数据作为一个单元存储起来，称作PL/SQL RECORD 的域(FIFLD)，其作用是存放互不相同但逻辑相关的信息。

定义记录类型语法如下：

TYPE record_type IS RECORD(
  Field1 type1 [NOT NULL] [:=exp1],
  Field2 type2 [NOT NULL] [:=exp2],
  ......
  Fieldn typen [NOT NULL] [:=expn]
);

例如

declare 
   type test_rec is record(
        l_name varchar2(30),
        d_id number(4));
   v_emp test_rec;
begin
   v_emp.l_name :='Tom';
   v_emp.d_id :=1234;
   dbms_output.put_line(v_emp.l_name|| ',' || v_emp.d_id);
end;

或者

declare
    type test_rec is record(
         l_name varchar2(30),
         d_id number(4));
    v_emp test_rec;
begin
    select last_name,department_id into v_emp
    from employees
    where employee_id = 200;

    dbms_output.put_line(v_emp.l_name|| ',' || v_emp.d_id);
end;

DBMS_OUTPUT.PUT_LINE过程的功能类似于Java中的System.out.println()直接将输出结果送到标准输出中
在使用上述过程之前必须将SQL * PLUS的环境参数SERVEROUTPUT设置为ON，否则将看不到输出结果：set serveroutput on

使用%TYPE
定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表中的某个列的数据类型相同，这时可以使用%TYPE。

使用%TYPE特性的优点在于：

• 所引用的数据库列的数据类型可以不必知道
• 所引用的数据列的数据类型可以实时改变。

declare
   type test_rec is record(
       l_name employees.last_name%type,
       d_id employees.department_id%type);
   v_emp test_rec;
begin
   select last_name,
   department_id into v_emp
   from employees where employee_id = 200;
   dbms_output.put_line(v_emp.l_name|| ',' || v_emp.d_id);
end;

使用%ROWTYPE
PL/SQL提供%ROWTYPE操作符，返回一个记录类型，其数据类型和数据表的数据结构一致。

使用%ROWTYPE特性的特点在于：

• 所引用的数据库中列的个数和数据类型可以不必知道
• 所引用的数据库中列的个数和数据类型可以实时改变

declare
    v_emp employees%rowtype;
begin
    select * into v_emp
    from employees where employee_id = 200;
    dbms_output.put_line(v_emp.last_name||',' ||v_emp.department_id||','||v_emp.hire_date);

end;

简单数据插入案例

DECLARE
   v_ename  VARCHAR2(20):='Bill';
   v_sal    NUMBER(7,2):=1234.56;
   v_deptno NUMBER(2):=10;
   v_empno  NUMBER(4):=8888;
BEGIN
   INSERT INTO emp(empno,ename,JOB,sal,deptno,hiredate)
       VALUES(v_empno,v_ename,'Manager',v_sal,v_deptno,
       TO_DATE('1954-06-09','yyyy-mm-dd'));
   COMMIT;

END;

简单数据删除

DECLARE
    v_empno number(4):=8888;
BEGIN
    DELETE FROM emp WHERE empnp=v_empno;
    COMMIT;
END;