PL/SQL Basic Example

1.作用域

变量的作用域是指变量的有效作用范围，它从变量声明开始，直到当前块结束。只有在其作用域范围之内，程序才能使用该变量，否则将导致编译错误。

<<block>>
DECLARE
   v_1   VARCHAR2 (30) := 'block中的全局v1';
BEGIN
   DBMS_OUTPUT.put_line (v_1);

  <<subblock_1>>
   DECLARE
      v_1   VARCHAR2 (30) := 'subblock_1中的局部v1';
   BEGIN
      DBMS_OUTPUT.put_line (v_1);
      DBMS_OUTPUT.put_line (block.v_1);
   END;

  <<subblock_2>>
   DECLARE
      v_1   VARCHAR2 (30) := 'subblock_2中的局部v1';
   BEGIN
      DBMS_OUTPUT.put_line (v_1);
      DBMS_OUTPUT.put_line (block.v_1);
   END;
END;
/

 

2.用SELECT INTO语句给变量赋值

除了用常量给变量赋值之外，还可以从数据库表中查询出值来赋予变量。

DECLARE
   v_bonus   NUMBER (8, 2);
   v_empno   NUMBER (6) := 7369;
BEGIN
   SELECT sal * 0.10
     INTO v_bonus
     FROM emp
    WHERE empno = v_empno;

   DBMS_OUTPUT.put_line (
      '雇员' || TO_CHAR (v_empno) || '的奖金是:' || TO_CHAR (v_bonus));
END;
/

 

3.TIMESTAMP(s)

TIMESTAMP是DATE数据类型的扩展。存储了年、月、日、时、分、秒，秒带有小数，还包括了上午、下午、时区。其中后面的(s)是可选的，s表示秒部分的小数位位数，s的取值范围是s=0~9。默认的TIMESTAMP格式是由初始化参数NLS_TIMESTAMP_FORMAT来设置的。

DECLARE
   v_datetime   TIMESTAMP (9);
BEGIN
   v_datetime := SYSTIMESTAMP;
   DBMS_OUTPUT.put_line (v_datetime);
END;
/

 

4.%TYPE

为了使一个变量的数据类型与另一个已经定义了的变量（尤其是表的某一列）的数据类型相一致，Oracle提供了%TYPE定义方式。这样，当被参照的那个变量的数据类型改变了之后，这个新定义的变量的数据类型也自动跟随其改变，容易保持一致，也不用修改PL/SQL程序了。当不能确切地知道被参照的那个变量的数据类型时，只能采用这种方法定义变量的数据类型。

DECLARE
   v_ename               emp.ename%TYPE;
   v_sal                 emp.sal%TYPE;
   c_tax_rate   CONSTANT NUMBER (3, 2) := 0.03;
   v_sal_tax             v_sal%TYPE;
BEGIN
   SELECT ename, sal
     INTO v_ename, v_sal
     FROM emp
    WHERE empno = &eno;

   v_sal_tax := v_sal * c_tax_rate;
   DBMS_OUTPUT.put_line ('雇员名称：' || v_ename);
   DBMS_OUTPUT.put_line ('工资：' || v_sal);
   DBMS_OUTPUT.put_line ('所得税：' || v_sal_tax);
END;
/

 

5.%ROWTYPE

如果一个表有较多的列，使用%ROWTYPE来定义一个表示表中一行记录的变量，比分别使用%TYPE来定义表中的各列的变量要简捷得多，并且不容易遗漏、出错。这会增加程序的可维护性。

DECLARE
   v_emp   emp%ROWTYPE;
BEGIN
   SELECT *
     INTO v_emp
     FROM emp
    WHERE empno = &eno;

   DBMS_OUTPUT.put_line ('雇员名称：' || v_emp.ename);
   DBMS_OUTPUT.put_line ('工资：' || v_emp.sal);
   DBMS_OUTPUT.put_line ('岗位：' || v_emp.job);
END;
/

 

6.RECORD

定义记录数据类型。它类似于C语言中的结构数据类型（STRUCTURE），PL/SQL提供了将几个相关的、分离的、基本数据类型的变量组成一个整体的方法，即RECORD符合数据类型。在使用记录数据类型变量时，需要在声明部分先定义记录的组成、记录的变量，然后在执行部分引用这记录变量本身或其中的的成员。

DECLARE
   TYPE emp_record_type IS RECORD
   (
      name     emp.ename%TYPE,
      salary   emp.sal%TYPE,
      job      emp.job%TYPE
   );

   v_emp_record   emp_record_type;
BEGIN
   SELECT ename, sal, job
     INTO v_emp_record
     FROM emp
    WHERE empno = &eno;

   DBMS_OUTPUT.put_line (
         '雇员名称：'
      || v_emp_record.name
      || '工资：'
      || v_emp_record.salary
      || '岗位：'
      || v_emp_record.job);
END;
/

 

7.TABLE

定义记录表（或索引表）数据类型。它与记录类型相似，但它是对记录类型的扩展。它可以处理多行记录，类似于C语言中的二维数组，使得可以在PL/SQL中模仿数据库中的表。关键字INDEX BY表示创建一个主键索引，以便引用记录表变量中的特定的行。

DECLARE
   TYPE emp_table_type IS TABLE OF emp%ROWTYPE
                             INDEX BY BINARY_INTEGER;

   v_emp_table   emp_table_type;
BEGIN
   SELECT ename, sal
     INTO v_emp_table (1).ename, v_emp_table (1).sal
     FROM emp
    WHERE empno = 7369;

   SELECT ename, sal
     INTO v_emp_table (2).ename, v_emp_table (2).sal
     FROM emp
    WHERE empno = 7788;

   DBMS_OUTPUT.put_line (
         '7369雇员名称：'
      || v_emp_table (1).ename
      || ' 工资：'
      || v_emp_table (1).sal);
   DBMS_OUTPUT.put_line (
         '7788雇员名称：'
      || v_emp_table (2).ename
      || ' 工资：'
      || v_emp_table (2).sal);
END;
/

 可以看出，记录表可以存储多行、多列的数据，就好像在PL/SQL中创建的一个表，所以也被称为PL/SQL表。