declare sql语句_PL/SQL 学习

导读：介绍 PL/SQL 概念、程序结构、变量、流程控制、游标、存储过程。

基本上每一种数据库，都会对 SQL 语句进行扩展。PL/SQL 则是 Oracle 数据库对于 SQL 语言的扩展，PL/SQL 全称是 Procedural Language/SQL，即过程化 SQL 语言。

PL/SQL 在普通 SQL 语言上添加了编程语言的特点，可以把数据操作和查询语句组织在 PL/SQL 代码的过程性单元中，通过逻辑判断、循环等操作，实现复杂的功能或者计算。

PL/SQL 有专门的开发工具 PL/SQL Developer，我们可以通过PL/SQL Developer工具的 Test Window 创建程序模板或者通过语句在 SQL Window 编写。

PL/SQL 是不区分大小写的，所以我们编写 PL/SQL 时不用区分大小写。

PL/SQL 的程序结构，可以分为三个部分，声明部分、可执行部分、异常处理部分。

DECLARE 
    -- 声明变量、游标
BEGIN
    -- 执行语句
    -- [异常处理]
END;
/

DECLARE 部分用来声明变量或者游标（结果集类型变量），如果程序中无变量声明，可以省略，异常处理部分也是可以省略。

BEGIN 和 END「;」「/」 不能省略。在 PL/SQL中，「;」冒号表示每条语句结束，「/」表示整个 PL/SQL 程序结束。

了解完 PL/SQL 的程序结构后，我们就开始写 PL/SQL 的第一个 hello world 了。

begin
    DBMS_OUTPUT.PUT_LINE('hello world');
end;
/

DBMS_OUTPUT 是 Oracle 中的一个输出对象，PUT_LINE 是上述对象的一个方法，用于输出一个字符串自动换行。SQLPLUS 或者「命令窗口」中如果没有输出，可以使用 set serveroutput on/off 命令更改设置。

PL/SQL 既然有了编程语言的特点，自然也支持变量，

PL/SQL 的变量可以分为两种，「普通变量」和「特殊变量」，特殊变量又分为引用型和记录型。

普通变量就是 Oracle 的数据类型，包括 char、varchar2、date、number、boolean、long。

变量声明方式： 变量名 变量类型（变量大小）;
​
例如：O_NAME VARCHAR2(255);

PL/SQL 变量有两种赋值方式：

直接赋值 「:=」冒号加等号
​
语句赋值，使用 select ... into ... 赋值，语法 select 值 into 变量

PL/SQL 中的连接使用「||」,和 Java 中「+」号一样，下面是实例，我们输出一个变量：

DECLARE
    O_NAME VARCHAR2(5) := 'TOM';
BEGIN
    dbms_output.put_line('NAME:'||O_NAME);
END;
/
​
输出结果  NAME:TOM

我们来看一下引用型变量，顾名思义，引用型变量它的类型和大小是引用数据库表中字段的类型和大小。

变量声明方式：变量名 表名.列名%TYPE;
​
例如：O_NAME EXP.NAME%TYPE;

记录型变量是用于保存一行记录，相当于 Java 中的一个对象。

变量声明方式：变量名 表名%ROWTYPE
​
例如：o_exp exp%rowtype

学完变量，我来学习一下流程控制，流程控制有条件分支和循环，PL/SQL 有三种循环，这里我们只学习 LOOP 循环。

条件分支就是我们常见的 if...else... 下面看一下它的格式：

BEGIN
    IF 条件1 THEN 执行1;
    ELSIF 条件2 THEN 执行2;
    ELSE 执行3;
    END IF;
END;

这里需要注意的是，这里是「ELSIF」，并没有写错，不是我们编程中常见的「else if」。有 IF 就要有 END IF，这个好比 Java 中条件判断开始和结束的大括号。

PL/SQL 中 LOOP、FOR、WHILE 三种循环，这里我们主要学习 LOOP 循环。

BEGIN
    LOOP
      EXIT WHEN 退出循环条件；
    END LOOP;
END;
/

这里和分支控制一样，LOOP 完成后要有 END LOOP，也是相当于 Java 中循环开始和结束的大括号。

接着我们学习一个 SQL 编程中很重要的知识点「游标」，游标在其它数据库中也有支持。

游标：用于临时存储一个查询返回的多行数据，类似于 Java 中的容器（不知道 Java 中的容器，可以参考我之前的文章Java容器框架学习整理），Java 中的容器是用于装 Java 对象的，这里的游标是用于装数据库中表的记录。

游标的使用方式为：声明 -> 打开 -> 读取 ->关闭 。

游标还有属于自己的属性：

我们来看一个游标的使用实例：

declare
    --定义游标
    cursor o_cur is select name from exp;
    --定义变量
    o_name exp.name%type;
begin
    --打开游标，这时游标位于第一条记录之前
    open o_cur;
    --循环
    loop
       --读取游标，向下移动游标一次
       fetch o_cur into o_name; 
       --退出循环,当游标下移一次后，找不到记录时，则退出循环
       exit when o_cur%notfound;
       --输出结果
       dbms_output.put_line('name:'||o_name);
    end loop;
    --关闭游标
    close o_cur;
end;
/

最后我们一起学习一下「存储过程」。

存储过程是 PL/SQL 将一个个 PL/SQL 的业务处理过程，存储起来进行复用，类似于 Java 中的方法。

存储过程的语法：
​
CREATE OR REPLACE PROCEDURE 存储过程名字（参数列表） IS
BEGIN
​
END [存储过程名字];

存储过程的参数可以分为不带参数、带输入参数、带输入输出输出参数。

下面我们来看一个带有输入输出参数的存储过程示例:

CREATE OR REPLACE PROCEDURE findName(O_NO IN NUMBER, O_NAME OUT VARCHAR2)
AS
  BEGIN
    SELECT NAME INTO O_NAME FROM EXP WHERE NO = O_NO;
  END;

大家可能也发现了，这里没有 DECLARE，因为 DECLARE出现在匿名程序中，所以这里声明变量不需要 DECLARE。

这里的 AS 可以换成 IS ，它们表示相同的意思，可以互换。

创建完存储过程，成功编译一次，我们就可以按照存储过程名称进行调用，调用存储过程时需要注意传入参数的顺序需要和定义时一样，存储过程可以有三种调用方式：

我们在 PL/SQL 中调用我们上面创建的存储过程：

DECLARE
  O_NAME EXP.NAME%TYPE;
BEGIN
  findName(11111,O_NAME );
  DBMS_OUTPUT.PUT_LINE('NAME = '||O_NAME);
END;/

除了存储过程，PL/SQL 还支持函数，存储过程和函数很相似。但是函数必须返回参数，不如存储过程灵活，所以这里就不介绍了，大家感兴趣可以自行去了解。