PLSQL基础

深圳软件园人才实训基地版权所有 11
Байду номын сангаас
变量定义例子
• v_name varchar2(10); • v_sal number(6,2); • v_balance binary_float; --oracle 10g新增数据类 型 • c_tax_rate constant number(3,2):=5.5; • v_hiredate date; • v_valid boolean not null default false; • v_string_var varchar2(50):=q’[I’m a string,you’re a string]’;--如果需要在文本中包含单引号，必须使用 分隔符[] {} <>等，而且需要带有前缀q
深圳软件园人才实训基地版权所有 3
块结构
• 分为三部分组成：定义部分、执行部分、异常处理部分 • DECLARE
/* **********定义部分（可选）-----定义常量、变量、复杂 数据类型、游标等 */
BEGIN
/* **********执行部分（必须）-----PL SQL语句和SQL语句 */
深圳软件园人才实训基地版权所有 10
为变量和常量赋值
• 用户可以使用很多方法为变量(在程序块的声明部分和可执 行部分)和常量(在声明部分)赋值。最常用的赋值方法是使用 PL/SQL的赋值运算符(:=)。赋值运算符的语法格式如下： –variable datatype := expression; -- 在程序块的声明部 分 –variable := expression; -- 在程序块的可执行部分 • 在程序块的声明部分初始化变量时，可以使用default关键 字。使用default关键字为变量赋值，表示在可执行部分既 可以直接引用该变量的值，也可以重新为该变量赋值。 • 在声明变量时，可以为变量指定not null属性。not null属性 表示该变量不允许空，必须为其赋予明确的值。
深圳软件园人才实训基地版权所有 20
逻辑数据类型
• 在PL/SQL程序块中可以使用boolean数据类型，即逻辑数 据类型。该数据类型不能用于SQL中数据库的列。 • boolean变量的值可以是true、false和null，它能够作为条 件语句的单一参数。需要注意的是，在使用逻辑数据类型赋 值时，不能使用引号。在如图9-12所示的示例中有两个示例。 在第一个示例中，由于逻辑值true有引号，所以出现了错误。 第二个示例将逻辑值true直接赋予逻辑变量，所以赋值成功。
• 2、代码缩进 • 3、大小写编写规则（建议）
– SQL关键字大写 – 自定义类型或者变量小写
深圳软件园人才实训基地版权所有 13
可视性和作用域
• 当声明变量和常量时，有一些控制用户声明的作用 域和可视性的规则。作用域是用户能够引用变量名 称标识符的程序块。只有用户处于可以使用非限定 名称引用标识符的程序域时，标识符才是可视的。 也就是说，如果在该作用域中，没有必要使用定义 标识符的程序块名称作为标识符的前缀，那么该标 识符是可视的。 • 在单独的程序块中，整个程序块都是在声明部分定 义的标识符的作用域，标识符都是可视的。 • 在嵌套程序块中，父块本身和任何嵌套块都是在父 块中定义的标识符的作用域，这些标识符都是可视 的。在子块声明中定义的标识符只有在子块本身中 才处于其作用域，才是可视的。
– 当用户声明单独的变量而不是记录时可以使用%type。 – 当用户声明表示表、视图或游标的完整行的记录变量时可以使用%rowtype。 – V_A – V_B – V_C NUMBER(5):=10; V_A%TYPE:=15; V_A%TYPE;
• DELCARE
• BEGIN
• END;
– DBMS_OUTPUT.PUT_LINE ('V_A='||V_A||'V_B='||V_B||'V_C='||V_C);
用户
引擎将 SQL 语句发送给 SQL 语句执行器
SQL 语句 执行器 执行 SQL 语句
深圳软件园人才实训基地版权所有 7
示例程序块
深圳软件园人才实训基地版权所有 8
9.2 声明
• 前面已经介绍过，可以声明将要在PL/SQL代 码中使用的变量和常量。声明变量时可以选 择是否为变量赋值。但是，常量必须初始化。 之后，就可以在指定的PL/SQL代码块中使用 这些变量和常量。
深圳软件园人才实训基地版权所有 9
变量和常量
• 用户使用的所有变量和常量都必须在程序块中的声明部分定 义。对于每一个变量，用户都必须规定名称和数据类型，以 便在可执行部分为其赋值。 • 在何处为变量赋值是可以选择。既可以选择在可执行部分中 为变量赋值，也可以选择在声明变量时同时为其赋值。如图 9-4所示是这两种赋值方式。其中，第一个示例在可执行部 分中为变量赋值，而在第二个示例中，声明变量时同时赋值。 但是，这两种赋值的结果完全一样。第二个示例使用了关键 字null。null关键字表示什么都不做。 • 常量声明与变量声明类似，但是也有一些区别。首先需要明 白，常量的值是不能改变的。因此，用户必须在声明常量时 为其赋值，而且还必须在数据类型的左边使用constant关 键字。
EXCEPTION
/* **********异常处理部分（可选）-----处理运行错误 */ END; --块结束表级（必须）
深圳软件园人才实训基地版权所有 4
PL/SQL 的优点 2-1
支持 SQL，在 PL/SQL 中可以使用： 数据操纵命令 事务控制命令 游标控制 SQL 函数和 SQL 运算符 支持面向对象编程 (OOP) 可移植性，可运行在任何操作系统和平台上的Oralce 数据库 更佳的性能，PL/SQL 经过编译执行
Procedure Begin ProcedureCall SQL Command … End
Oracle
用户将整个语句块发送给 Oracle
深圳软件园人才实训基地版权所有 5
PL/SQL 的优点 2-2
与 SQL 紧密集成，简化数据处理。 支持所有 SQL 数据类型 支持 NULL 值 支持 %TYPE 和 %ROWTYPE 属性类型
深圳软件园人才实训基地版权所有 21
日期和时间类型
• PL/SQL的日期和时间类型包括date类型、timestamp类型 和interval类型。这3种日期和时间类型与SQL相应的类型具 有相同的名称和含义。 • PL/SQL的date类型可以存储世纪、年、月、日、时、分和 秒。其中，秒不能带小数。可以使用内置的to_date()和 to_char()函数在字符串和date类型之间进行相互转换。使 用这些内置函数，通过调整日期格式，可以使其包含或不包 含日期或时间的值，也可以使其使用12时制或24时制。 • PL/SQL的timestamp类型用于描述时戳，有3种不同的类型， 即timestamp、timestamp with time zone和timestamp with local time zone类型。 • PL/SQL有两种类型的interval，即interval year to month 和interval day to second。这两种类型的差别主要在于计 算的精度。
SQL PL/SQL
安全性，可以通过存储过程限制用户对数据的访问
深圳软件园人才实训基地版权所有 6
PL/SQL 的体系结构
PL/SQL 引擎驻留在 Oracle 服务器中 该引擎接受 PL/SQL 块并对其进行编译执行
Oracle 服务器 将PL/SQL 块发送给 Oracle 服务器 将结果发送给用户 PL/SQL引擎 过程语句执行器 执行过程语句
深圳软件园人才实训基地版权所有 18
字符数据类型
• PL/SQL字符数据类型可以包含单个字符，也可以包 含长度在32K以内的字符串。这些类型可以存储字 母、数字和二进制数据，也可以存储数据库字符集 所支持的所有字符。在使用这些类型定义变量时， 一般都使用整数来定义其长度，单位是字节。 • 如表9-1所示列出了PL/SQL字符数据类型的名称和 特点。需要注意的是，虽然varchar2的两种子类型， 即varchar和string，能够和varchar2替换使用， 但是Oracle还是建议使用varchar2。因为在为未 来的某个时候，varchar可能会变成与varchar2不 同的数据类型。
深圳软件园人才实训基地版权所有 17
9.3 PL/SQL数据类型
• PL/SQL数据类型与SQL数据类型既有相同之 处，也有不同之处。PL/SQL数据类型可以分 为标量类型、引用类型、复合类型、LOB类 型。标量类型是只能存储单值的数据类型， 它又可以进一步划分为字符类型、数值类型、 逻辑类型、日期和时间类型。 • 本节重点介绍PL/SQL标量数据类型的特点。
深圳软件园人才实训基地版权所有 14
问题
• • 阅读如下代码： declare v1 number(6); v2 number(6); begin v1:=100; v2:=200; declare v1 number(6); v3 number(6); begin v1:=110; v2:=210; v3:=300; end; v3:=400; end; 确定 – 1、子块中v1,v2,v3的值 – 2、主块中v1,v2,v3的值
深圳软件园人才实训基地版权所有 12
命名规则
• 1、标识符命名规则
– – – – – – – – 当定义变量时，建议使用v_作为前缀 当定义常量时，建议使用c_作为前缀 当定义游标时，建议使用_cursor作为后缀 当定义异常时，建议使用e_作为前缀 当定义PL/SQL表类型时，建议使用_table_type作为后缀 当定义表变量时，建议使用_table作为后缀 当定义记录类型时，建议使用_record_type作为后缀 当定义记录变量时，建议使用_record作为后缀
深圳软件园人才实训基地版权所有 19
数值数据类型
• 在Oracle Database 10g系统中，常用的数值类 型包括binary_double、binary_float、 binary_integer、number、pls_integer。
–binary_double是Oracle Database 10g系统新引入的 一种数值类型，它是IEEE-754标准的双精度浮点模型。 –binary_float也是Oracle Database 10g系统新引入的 一种数值类型，它是一种单精度类型。 –binary_integer数据类型存储数值的范围是 −2147483647～+2147483647。 –PL/SQL中的number类型与数据库中的number类型含 义相同。这种类型可以存储浮点值和整数值。 –pls_integer是-2^31～2^31之间有符号的整数，提供了 从-2147483648～2147483648的范围。
深圳软件园人才实训基地版权所有 16
使用%type和%rowtype
• 可以使用%type和%rowtype来声明变量，而不必指定特定的数据类型。 这两个属性提供了这种方式：在声明变量时，不指定变量的数据类型， 而由表/视图列或PL/SQL程序包变量来定义变量的数据类型。 • %type和%rowtype可以用于不同的情况。
深圳软件园人才实训基地版权所有 15
•
定义不确定内容
• 前面的许多示例都引用了null关键字。null关键字表示缺少、不可知或不 适用等含义。从本质上来讲，null关键字表示没有内容。 • 在PL/SQL程序块中，如果引用某个已经声明的变量，且没有为该变量赋 值，那么该变量的值就是null。也就是说，null通常会赋予在声明时没有 赋值的变量。 • 如图9-8所示的示例声明了4个变量。但是，这4个变量都会产生相同的 结果：具有null值的varchar2变量。
PL/SQL基础
目录
9.1 9.2 9.3 9.4 9.5 9.6 9.7 概述 声明 PL/SQL数据类型 PL/SQL集合 游标 控制语句 错误处理
深圳软件园人才实训基地版权所有 2
9.1 概述
• PL/SQL 是过程语言(Procedural Language)与结构化查询语 言(SQL)结合而成的编程语言 • PL/SQL 是对 SQL 的扩展 • 支持多种数据类型，如大对象和集合类型，可使用条件和循环 等控制结构 • PL/SQL是基于Ada编程语言的结构化编程语言，是由Oracle 公司从版本6开始提供的专用于Oracle产品的数据库编程语言。 用户可以使用PL/SQL语言编写过程、函数、程序报、触发器等 PL/SQL代码，并且把这些代码存储起来，以便由具有适当权限 的数据库用户重新使用。 • PL/SQL对大小写不敏感，因此用户应该选择符合自己的编码标 准来描述性地规范自己的PL/SQL代码形式。 • PL/SQL代码使用了程序块，利用模块化方式进行构建。