PL/SQL Step By Step（一）

最新推荐文章于 2024-11-11 08:41:40 发布

weixin_38171245

最新推荐文章于 2024-11-11 08:41:40 发布

阅读量95

点赞数

文章标签：数据库 c/c++ 操作系统

原文链接：http://www.cnblogs.com/wawlian/archive/2012/03/01/2375078.html

版权

1.概述

PL/SQL（Procedural Language/Structured Query Language）是Oracle对标准数据库语言SQL的过程化扩充，它将数据库技术和过程化程序设计语言联系起来,是一种应用开发语言，可使用循环，分支处理数据，将SQL的数据操纵功能与过程化语言数据处理功能结合起来。 PL/SQL的使用,使SQL成为一种高级程序设计语言，支持高级语言的块操作，条件判断，循环语句，嵌套等，与数据库核心的数据类型集成，使SQL 的程序设计效率更高。（更加详细的介绍，大家可以Google一下）

PL/SQL程序主要分为两大类：匿名程序和命名程序。下面先讲匿名程序。一个典型的匿名块结构分为以下几个部分：

DECLARE--可选部分
    --变量、常量、游标、用户定义异常的声明
BEGIN--必要部分
    --SQL语句和PL/SQL语句构成的执行程序
EXCEPTION--可选部分
    --程序出现异常时，捕捉异常并处理异常
END；--必须部分，注意END后面的分号

按照上面的格式，我们先写一个简单完整的匿名块：

DECLARE
  v_counter NUMBER(3);
  v_user ALL_USERS.USERNAME%TYPE;
  v_today DATE;
BEGIN
SELECT  SYSDATE,USER
INTO    v_today,v_user
FROM DUAL;

  DBMS_OUTPUT.PUT_LINE(
'today:' ||
       TO_CHAR(v_today,'YYYY-MM-DD'));
  DBMS_OUTPUT.PUT_LINE('Schema:' || v_user);

  v_counter:=0;
  LOOP
    v_counter:=v_counter + 1;
EXIT WHEN v_counter > 10;
    DBMS_OUTPUT.PUT_LINE('line:' || v_counter);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
--Do log operations  
    --COMMIT;
END;

下面逐一解释：

声明部分

定义了三个变量：v_counter，number类型；v_user，ALL_USERS视图中USERNAME字段的类型（XX%TYPE的意思就是表示XX的类型）；v_today，DATE类型。

执行部分

将当前日期和当前用户查询出来分别赋值给v_today和v_user变量，并将二者打印输出；

打印从0到9这10个数字。v_counter充当迭代用的变量。

异常处理

有异常出现时怎么处理。

结束

结束部分就是按格式写上就好，没什么好说的。

上面的例子中还出现了单行注释，也就是以--开头的一行都会被视为注释。此外，PL/SQL还支持多行注视/* */。此外，对于PL/SQL的标识符来说，必须以字符开头，并且最大不超过30个字符，这一点与很多语言略有不同。还要特别说的一点是，PL/SQL的字符串字面量是用单引号'括起来的。

2.常量与变量

PL/SQL的变量定义的格式如下：

VARIABLE_NAME DATATYPE;

变量的类型必须给出，可以是所有PL/SQL支持的类型，具体有哪些我们后面会继续讲。此外，在定义变量的时候可以给变量给出初始值，像下面这样：

VARIABLE_NAME DATATYPE := INIT_VALUE;

PL/SQL的赋值操作符是:= ，如果没有给出初始值，那么变量默认的初始值是NULL。

定义常量的方法与定义变量的方法基本类似，只是需要加上关键字CONSTANT，并且必须给定初始值，如下：

VARIABLE_NAME CONSTANT DATATYPE := INIT_VALUE;

定义常量时必须给出常量值，并且一旦赋值，之后就不允许再改变了。下面是一个例子：

--常量值不可变例子
DECLARE
  v_name CONSTANT VARCHAR2(8) := 'Tom';
BEGIN
  v_name := 'Jerry';
  DBMS_OUTPUT.put_line(v_name);
END;
/

运行上面的程序，Oracle会报错：

ORA-06550: 第 5 行, 第 3 列: 
PLS-00363: 表达式 'V_NAME' 不能用作赋值目标
ORA-06550: 第 5 行, 第 3 列: 
PL/SQL: Statement ignored

PL/SQL中，常量、变量支持的所有类型以及详细的解释建议大家结合你使用的Oracle版本参考Oracle官方文档，版本之间会有一些微小调整。这里简要的列举一些：

CHAR(N)：N位字符，不足N位，自动用空格补全。N必须要指定。
VARCHAR2(N)：最多N位字符，N可以不指定，默认为1。不足N位时，不会用空格补全N位。因此，用的比较多。
DATE:日期时间类型，默认按照数据库要求的格式显示。
NUMBER(N,M)：数字型。N位数字，其中M位为小数的位数。
BOOLEAN：SQL中不支持这个类型，但是PL/SQL支持，有TRUE和FALSE两种。
LONG：可以接收很长的字符数据，多达32760字节。
RAW：接收二进制数据，长度达到32767字符。PL/SQL无法解析其内容。
LONG RAW：比RAW更大而已。
MLSLABEL：安全操作系统标签，Trusted Oracle中使用。
ROWID：用来唯一表示记录在数据库中的物理存储地址的一种类型。Oracle会自动为每条记录添加这个伪列。
BLOB：最大4GB的二进制数据。
CLOB：最大4GB的文本数据。
NCLOB：多字节字符的BLOB。比如存储汉字。
BFILE：一个指向OS文件的指针。
BINARY_INTEGER
INT/INTEGER：整数
SMALLINT：小一点的整数。
POSITIVE：正数
NATURAL：自然数
NUMERIC/DEC/DECIMAL：类似于NUMBER
REAL/FLOAT：浮点数
TABLE：符合类型，类似于数组
RECORD：复合类型，表示一条记录

在声明部分，不仅可以定义像上面那种的常量或者变量，还可以声明显式游标或者用户自定义的异常。关于游标和异常后面的文章还会讨论。

3.执行部分

这个部分实际上是我们真正处理任务的一些操作，可以说是PL/SQL块中最重要的部分。主要包括表达式、赋值语句、条件语句、比较语句、循环、游标控制语句：

表达式

表达式并不是单独的语句，而是包含在单独语句中的小的代码单元。表达式一般主要用在赋值语句和条件语句中。表达式又分为算术表达式和比较表达式。这个和很多第三代语言是类似的，这里就不赘述。

几乎所有的SQL函数都可以以用作表达式，例如函数SUBSTR：

SUBSTR(v_str,m,n);

这就是一个合法的表达式。在PL/SQL中，唯一不能作为表达式的SQL函数是DECODE函数，因为，在PL/SQL中，已经有了IF-THEN-END语句可以替代它。

表达式之间还可以通过逻辑运算符连接起来。这一点也是比较简单的。

赋值语句

赋值语句上面已经见到了，通过赋值运算符:=实现赋值操作。因此，PL/SQL中的等于使用=实现的，不同于C语言中的==。

条件语句

条件语句无非是if语句及其各种变种。PL/SQL的格式稍微有点不同，最简单的形式如下：

IF(...) THEN
--DO STH
ELSE
--DO STH ELSE
END IF;

这是最简单的一种形式。C语言中还有else if的形式，PL/SQL中也有，如下：

IF () THEN
--A
ELSIF () THEN
--B
ELSE
--C
END IF;

使用上就和别的语言没有什么差别，只是初学的时候需要注意一下写法格式

循环

循环是编程语言中非常重要的一种结构，它用于对一组操作对象执行某一组相同的操作。循环有很多种不同的形式，下面一一介绍：

形式1：

LOOP
--做若干操作
  EXIT WHEN ...;--什么时候退出循环
END LOOP;

第一部分的例子中就用到了这种形式的循环了。

形式2：

LOOP
--做若干操作
  IF ...--什么时候退出循环
  THEN
EXIT;
END IF;
END LOOP;

和形式1其实是等价的

形式3：while循环

WHILE(v_i < 10)
LOOP
----做若干操作
  v_i := v_i + 1;
END LOOP;

形式4：数值FOR循环

FOR v_i IN 1..10
LOOP
--做一些操作
END LOOP;

形式5：游标FOR循环

这一部分，我们在后面讲到游标的时候再讲。

下面，我们先用四种形式打印数字1-10，代码如下：

--形式1
DECLARE
  v_limit number(2) := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_limit);
    v_limit := v_limit + 1;
EXIT WHEN v_limit > 10;
END LOOP;
END;
/

--形式2
DECLARE
  v_limit number(2) := 1;
BEGIN
  LOOP
IF v_limit > 10 
THEN
EXIT;
END IF;
    DBMS_OUTPUT.PUT_LINE(v_limit);
    v_limit := v_limit + 1;    
END LOOP;
END;
/

--形式3
DECLARE
  v_limit number(2) := 1;
BEGIN
WHILE v_limit <= 10
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_limit);
    v_limit := v_limit + 1;    
END LOOP;
END;
/

--形式4
DECLARE
  v_limit number(2) := 1;
BEGIN
FOR v_limit IN 1..10
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_limit); 
END LOOP;
END;
/