Oracle之PL/SQL要点

Oracle复习（PL/SQL）

一、概述

PL/SQL 作为 ORACLE 编程环境中的工具，是 SQL 的扩展，在 SQL 语句的使用上增加了编程语言的特点，PL/SQL 就是把数据操作和查询语句组织在 PL/SQL 代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。

二、基本语法

PL/SQL 是块结构语言；PL/SQL 程序划分成几个部分，并在每个部分中写入逻辑代码块；每个块都由三个部分组成。

声明部分

此部分以关键字 DECLARE 开头，可选部分，定义了程序中需要使用的所有变量，游标，子程序和其他元素。

可执行命令部分

此部分包含在关键字 BEGIN 和 END 之间，强制性部分。由 PL/SQL 语句组成。必须至少有一个可执行代码行，它可以只是 NULL命令，表示不执行任何操作。

异常处理部分

此部分以关键字 EXCEPTION 开头。可选部分，它包含处理程序中错误的异常。

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

Hello World 示例

DECLARE 
   message  varchar2(20):= 'Hello, World!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/
SQL

注：要从 SQL 命令行运行代码，需要在代码的最后一行之后键入 / 字符。

PL/SQL 标识符

PL/SQL标识符是常量，变量，异常，过程，游标和保留字。标识符包括一个字母，可选地后跟多个字母，数字，美元符号，下划线和数字符号，不得超过30个字符。

默认情况下，标识符不区分大小写。例如，可以使用integer或INTEGER来表示一个数值。 不能使用保留关键字作为标识符。

PL/SQL 分隔符

分隔符是具有特殊含义的符号。以下是PL/SQL中的分隔符列表

PL/SQL程序单元

PL/SQL单元是以下任何一个

• PL/SQL 块
• 函数
• 包
• 包体
• 过程
• 触发器
• 类型
• 类型体

三、数据类型

PL/SQL变量，常量和参数必须具有有效的数据类型，数据类型指定了存储格式，约束和有效的值范围。

• 标量(SCALAR)类型 - 它是没有内部组件的单个值，例如：NUMBER，DATE或BOOLEAN等。
• 大对象(LOB)类型 - 指向与其他数据项(例如：文本，图形图像，视频剪辑和声音波形)分开存储的大对象的指针。
• 复合类型 - 具有可单独访问的内部组件的数据项。例如，集合和记录。
• 引用类型 - 指向其他数据项。

四、变量

变量声明

变量声明语法：变量名 [约束] 数据类型 [NOT NULL] [默认值];

--例如：
counter binary_integer := 0; 
greetings varchar2(20) DEFAULT 'Have a Good Day';

如果使用NOT NULL约束，则必须为变量显式分配初始值。

DECLARE 
   a integer := 10; 
   b integer := 20; 
   c integer; 
   f real; 
BEGIN 
   c := a + b; 
   dbms_output.put_line('Value of c: ' || c); 
   f := 70.0/3.0; 
   dbms_output.put_line('Value of f: ' || f); 
END; 
/

变量作用域

PL/SQL允许块的嵌套，即每个程序块可以包含另一个内部块。 如果在内部块中声明了一个变量，则外部块不可访问内部变量。 但是，如果一个变量声明并且可以被外部块访问，那么所有嵌套的内部块都可以访问该变量。变量有两种类型的范围

• 局部变量 - 内部块中声明的变量，外部块不可访问。
• 全局变量 - 在最外部块或包中声明的变量。

将SQL查询结果分配给PL/SQL变量

可以使用SQL的SELECT INTO语句将值分配给PL/SQL变量。 对于SELECT列表中的每个项目，INTO列表中必须有一个对应的类型兼容变量。

DECLARE 
   c_id customers.id%type := 1; 
   c_name  customerS.No.ame%type; 
   c_addr customers.address%type; 
   c_sal  customers.salary%type; 
BEGIN 
   SELECT name, address, salary INTO c_name, c_addr, c_sal 
   FROM customers 
   WHERE id = c_id;  
   dbms_output.put_line 
   ('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal); 
END; 
/

五、条件控制

六、循环

循环类型

循环控制语句

基本循环

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE 
   x number := 10; 
BEGIN 
   LOOP 
      dbms_output.put_line(x); 
      x := x + 10; 
      IF x > 50 THEN 
         exit; 
      END IF; 
   END LOOP; 
   -- after exit, control resumes here  
   dbms_output.put_line('After Exit x is: ' || x); 
END; 
/

while 循环

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE 
   x number := 10; 
BEGIN 
   LOOP 
      dbms_output.put_line(x); 
      x := x + 10; 
      IF x > 50 THEN 
         exit; 
      END IF; 
   END LOOP; 
   -- after exit, control resumes here  
   dbms_output.put_line('After Exit x is: ' || x); 
END; 
/

for 循环

SET SERVEROUTPUT ON SIZE 100000;
DECLARE 
   a number(2); 
BEGIN 
   FOR a in 10 .. 20 LOOP 
      dbms_output.put_line('value of a: ' || a); 
  END LOOP; 
END; 
/

七、存储过程

子程序是执行特定任务的程序单元/模块。 这些子程序组合起来形成更大的程序。这种做法被称为“模块化设计”。 子程序可以被称为调用程序的另一个子程序或程序调用。

可以在以下几个地方中创建一个子程序 -

• 在模式(schema)级别中
• 一个程序包中
• 在PL/SQL块中

在模式(schema)级别中，子程序是一个独立的子程序。它是使用CREATE PROCEDURE或CREATE FUNCTION语句创建的。它存储在数据库中，可以使用DROP PROCEDURE或DROP FUNCTION语句进行删除。

在包中创建的子程序是打包的子程序。它存储在数据库中，只有当使用DROP PACKAGE语句删除程序包时，才能将其删除。我们将在“PL/SQL程序包”一章中讨论程序包的应用。

PL/SQL子程序被命名为可以使用一组参数调用的PL/SQL块。 PL/SQL提供两种子程序 -

• 函数 - 这些子程序返回单个值; 主要用于计算和返回值。
• 存储过程(程序) - 这些子程序不直接返回值; 主要用于执行动作。

创建并执行存储过程

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name;
/

exec greetings;

八、函数

创建并执行存储函数

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
BEGIN 
   < function_body > 
END [function_name];