PL/SQL 基本语法

PL/SQL介绍

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

PL/SQL 作用

  1. 能够使一组 SQL 语句的功能更具模块化程序特点
  2. 采用了过程性语言控制程序的结构
  3. 可以对程序中的错误进行自动处理,使程序能够在遇到错误的时候不会被中断
  4. 具有较好的可移植性,可以移植到另一个 Oracle 数据库中
  5. 集成在数据库中,调用更快
  6. 减少了网络的交互,有助于提高程序性能

PL/SQL 程序的基本结构

PL/SQL 块由四个基本部分组成:块头、声明单元、执行单元、异常处理单元

  1. 块头:只有命名块才会有这个单元。块头确定命名块获程序的调用方式,这个单元是可选的
  2. 声明单元:定义变量、游标、以及可以被后面执行单元或异常单元引用的子块单元。这个单元也是可选的
  3. 执行单元: 这一部分包含要执行的业务逻辑,这个单元是必须的
  4. 异常处理单元:处理抛出的异常,这个单元式可选的

示例:

1
2
3
4
5
6
7
8
9
10
PROCEDURE get_happy (ename_in IN VARCHAR2)    -- 块头
IS
l_hiredate DATE; -- 声明单元
BEGIN
l_hiredate := SYSDATE - 2;
INSERT INTO employee (emp_name, hiredate) VALUES (ename_in, l_hiredate); -- 执行单元
EXCEPTION
WHEN DUP_VAL_IN_INDEX THEN -- 异常处理单元
DBMS_OUTPUT.PUT_LINE('Cannot insert.');
END;

匿名块

匿名块没有一个可以引用的句柄,因此不能被其他块调用。相反匿名块可以调用其他块。匿名块直接以 DECLARE 或 BEGIN 开始
语法:

1
2
3
4
5
6
7
[DECLARE
. . . 声明语句 . . . ]
BEGIN
. . . 一个或多个执行语句. . .
[EXCEPTION
. . . 异常处理语句]
END;

示例:

1
2
3
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;

命名块

命名块和匿名块最大区别在于,命名块有头部信息
示例:

1
2
3
4
5
6
7
PROCEDURE hello
IS
v_str VARCHAR2(100);
BEGIN
v_str := 'Hello World';
DBMS_OUTPUT.PUT_LINE(v_str );
END;

嵌套块

PL/SQL 允许块中再嵌套块。被嵌套的块还有其他的叫法比如闭包块、子块或者 sub-block
示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
PROCEDURE hello
IS
v_str VARCHAR2(100);
BEGIN
v_str := 'Hello World';

/* 嵌套模块开始 */
DECLARE
v_str2
BEGIN
v_str2 := v_str || ' inner block';
DBMS_OUTPUT.PUT_LINE(v_str2);
END;
/* 嵌套模块结束 */
DBMS_OUTPUT.PUT_LINE(v_str );
END;

PL/SQL 的基本规则

  1. 标识符不区分大小写,所有的名称在存储时自动改成大写
  2. 标识符只允许字母、数字、下划线,并且以字母开头
  3. 不能使用保留字,与保留字同名必须使用双引号括起来
  4. END 后需要使用分号结束
  5. 字符类型和日期类型需要使用单引号括起来

建议的写作规范:

  1. 命名应以“_”的连接方式,而不是用大小写混合的方式,如:p_id(表示名字为 id,”p” 表示它是一个参数)
  2. 变量前最好加上前缀,以表示该变量的数据类型、作用范围等
  3. 每个变量都应加上注释
  4. 建议用3个半角空格替代TAB健进行缩进
  5. 逗号后面以及操作符前后都应加空格

PL/SQL 注释

1
2
3
4
-- 单行注释
/*
* 多行注释
*/

PL/SQL 变量声明

1
variable_name datatype [:= expression ];

###直接声明变量,不赋值

1
s_id number;

声明变量并赋值

1
s_id number := 1;

PL/SQL IF 条件控制语句

1
2
3
4
5
6
7
if condition1 then
  statements1;
elsif condition2 then
  statements2;
else
statements3;
end if;

PL/SQL CASE 控制语句

1
2
3
4
5
6
case s_id
when value1 then
statements1;
when value2 then
statements2;
end;

或者

1
2
3
4
5
6
case
when exp1 then
statements1;
when exp2 then
statements2;
end;

PL/SQL 循环语句

简单循环 LOOP

简单循环以LOOP开头、END LOOP结尾,以EXIT WHEN指定退出条件

1
2
3
4
5
6
7
8
9
DECLARE
i NUMBER := 1;
BEGIN
LOOP
i := i + 1;
dbms_output.put_line(i);
EXIT WHEN i = 100;
END LOOP;
END;

FOR 循环

FOR 循环有两种形式,数值型游标型

数值型 FOR 循环

数值型 FOR 循环,用于替代数值用的变量(x)无需在块声明中进行声明,必须指定数值起点值和终点值,起点值和终点值之间用空格+..+空格隔开

1
2
3
4
5
6
7
8
DECLARE
i NUMBER := 1;
BEGIN
FOR x IN i .. 100
LOOP
dbms_output.put_line(x);
END LOOP;
END;

游标型 FOR 循环

游标型 FOR 循环用于对一个查询的结果进行处理,用于替代查询结果中每一行记录的变量(rec)无需在块声明中进行声明,可通过”变量名.字段名”引用查询结果中的字段

1
2
3
4
5
6
BEGIN
FOR rec IN (SELECT * FROM hr.parents)
LOOP
dbms_output.put_line(rec.person_id);
END LOOP;
END;

WHILE循环

1
2
3
4
5
6
7
8
9
DECLARE
i NUMBER := 1;
BEGIN
WHILE i <= 100
LOOP
dbms_output.put_line(i);
i := i + 1;
END LOOP;
END;

CONTINUE与EXIT

CONTINUE

CONTINUE用于跳出循环的本次循环的判断,继续下一次循环的判断

1
2
3
4
5
6
7
8
9
10
BEGIN
FOR i IN 1 .. 100
LOOP
IF i = 10 THEN
CONTINUE;
ELSE
dbms_output.put_line(i);
END IF;
END LOOP;
END;

输出结果为1-9及11-100,当循环进行到i=10时,会跳过本次循环,继续i=11的循环

EXIT

EXIT用于退出整个循环

1
2
3
4
5
6
7
8
9
10
BEGIN
FOR i IN 1 .. 100
LOOP
IF i = 10 THEN
EXIT;
ELSE
dbms_output.put_line(i);
END IF;
END LOOP;
END;

输出结果为1-9,这是因为当循环进行到i=10时,exit退出了整个循环,块的执行就结束了

查看评论