PL/SQL基础（1）：语法

本篇是 Oracle基础小结 系列之一。

本篇目录

1、什么是PL/SQL？

2、PL/SQL基本结构

3、PL/SQL符号定义

4、PL/SQL数据类型

5、PL/SQL条件句法

6、PL/SQL循环

什么是PL/SQL？

PL/SQL有两重含义，一是指PL/SQLDeveloper，是一个集成开发环境（IDE），专门开发面向Oracle数据库的应用（程序）；另外也是一种程序语言，叫做过程化SQL语言（ProceduralLanguage/SQL）。下面讲的都是后者PL/SQL，它是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元（也叫PL/SQL体）中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL只有 Oracle数据库有。MySQL等目前不支持 PL/SQL的。

初步了解PL/SQL移步https://en.wikipedia.org/wiki/PL/SQL，想比较系统的学习移步http://www.tutorialspoint.com/plsql。另外，学习SQL移步http://beginner-sql-tutorial.com，国内初步学习SQL的网站移步http://www.w3cschool.cn/sql，http://www.phpstudy.net/e/sql/sql_intro.html。有点扯远了，下面回归PL/SQL。

引用：

http://baike.baidu.com/link?url=_NBUf3gNxzNLNpAUkarTWsYxFHQ6t6hUS1V9TwJ3uqrD-5A9CQWhSe26IzY49oaoWlZm5aeIWIxYPniE14s8Pa

 

PL/SQL基本结构

PL/SQL是块结构的语言，每个块包括（但不全部包括）三部分：声明、可执行命令、异常处理。可执行命令是必须的。可执行命令包含在一个BEGIN END;体里。他们基本结构是：

DECLARE

  <declarations section>      --变量、常量、游标、用户定义异常的声明

BEGIN

  <executable command(s)>         --SQL语句和PL/SQL语句构成的执行程序

EXCEPTION

  <exception handling>         --程序出现异常时，捕捉异常并处理异常

END;

下面是一个'Hello World' 例子，注意最后有 / ：

DECLARE

  message  varchar2(20):= 'Hello,World!';

BEGIN

  dbms_output.put_line(message);

END;

/

引用：

http://www.tutorialspoint.com/plsql/plsql_basic_syntax.htm

 

PL/SQL符号定义

         对于不怎么写SQL或者习惯MySQL的人，有一些符号需要注意：

单引号‘’           字符串

双引号“”          关键/预留词转义

PS：关键/预留词移步http://docs.oracle.com/cd/B10501_01/appdev.920/a42525/apb.htm

--                单行注释

=                等于比较，不是赋值

:=               赋值

注意，在PL/SQL Developer脚本里多个单元（单元是指建表、存储过程等，后续有介绍）之间要用 / 分隔，才能一次执行完，不然会报错。另外，虽然/*This is comment*/是多行注释，但是不能任性的写成/* //This is comment //*/这种出现//的情况，会导致多个PL/SQL单元在PL/SQLDeveloper中执行中断。

附各种符号（Delimiters）的描述：

+, -, *, /    Addition,subtraction/negation, multiplication, division

%      Attributeindicator

'        Characterstring delimiter

.        Componentselector

(,)     Expressionor list delimiter

:        Hostvariable indicator

,        Itemseparator

"       Quotedidentifier delimiter

=       Relationaloperator

@     Remoteaccess indicator

;        Statementterminator

:=      Assignmentoperator

=>     Associationoperator

||     Concatenationoperator

**     Exponentiationoperator

<<, >>       Label delimiter (begin and end)

/*, */        Multi-linecomment delimiter (begin and end)

--       Single-linecomment indicator

..       Rangeoperator

<, >, <=, >=       Relational operators

<>, '=, ~=, ^=    Different versions of NOT EQUAL

引用：

http://www.tutorialspoint.com/plsql/plsql_basic_syntax.htm

 

PL/SQL数据类型

PL/SQL的数据类型有4类：标量（Scalar）、大对象（LargeObject (LOB)）、混合（Composite）和引用（Reference）。常用的是标量（数值、日期、布尔、字符或字符串等）、大对象（文本、图像、音频、视频等）。

Category

Description

Scalar

Single values with no internal components, such as a NUMBER, DATE, Character, or BOOLEAN.

Large Object (LOB)

Pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms.

Composite

Data items that have internal components that can be accessed individually. For example, collections and records.

Reference

Pointers to other data items.

         标量（Scalar）包含4种数据类型（数值、日期、布尔、字符或字符串），每种数据类型又包含子数据类型。例如INTEGER是NUMBER类型的一种子类型。子类型使得PL/SQL可以同其他编程语言在数据类型上相兼容。

         PL/SQL数值型数据类型和子类型包括：

Data Type

Description

PLS_INTEGER

Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits

BINARY_INTEGER

Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits

BINARY_FLOAT

Single-precision IEEE 754-format floating-point number

BINARY_DOUBLE

Double-precision IEEE 754-format floating-point number

NUMBER(prec, scale)

Fixed-point or floating-point number with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable can also represent 0.

DEC(prec, scale)

ANSI specific fixed-point type with maximum precision of 38 decimal digits.

DECIMAL(prec, scale)

IBM specific fixed-point type with maximum precision of 38 decimal digits.

NUMERIC(pre, secale)

Floating type with maximum precision of 38 decimal digits.

DOUBLE PRECISION

ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)

FLOAT

ANSI and IBM specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)

INT

ANSI specific integer type with maximum precision of 38 decimal digits

INTEGER

ANSI and IBM specific integer type with maximum precision of 38 decimal digits

SMALLINT

ANSI and IBM specific integer type with maximum precision of 38 decimal digits

REAL

Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)

         PL/SQL字符型数据类型和子类型包括：

Data Type

Description

CHAR

Fixed-length character string with maximum size of 32,767 bytes

VARCHAR2

Variable-length character string with maximum size of 32,767 bytes

RAW

Variable-length binary or byte string with maximum size of 32,767 bytes, not interpreted by PL/SQL

NCHAR

Fixed-length national character string with maximum size of 32,767 bytes

NVARCHAR2

Variable-length national character string with maximum size of 32,767 bytes

LONG

Variable-length character string with maximum size of 32,760 bytes

LONG RAW

Variable-length binary or byte string with maximum size of 32,760 bytes, not interpreted by PL/SQL

ROWID

Physical row identifier, the address of a row in an ordinary table

UROWID

Universal row identifier (physical, logical, or foreign row identifier)

         PL/SQL日期型数据类型和子类型包括：

Field Name

Valid Datetime Values

Valid Interval Values

YEAR

-4712 to 9999 (excluding year 0)

Any nonzero integer

MONTH

01 to 12

0 to 11

DAY

01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale)

Any nonzero integer

HOUR

00 to 23

0 to 23

MINUTE

00 to 59

0 to 59

SECOND

00 to 59.9(n), where 9(n) is the precision of time fractional seconds

0 to 59.9(n), where 9(n) is the precision of interval fractional seconds

TIMEZONE_HOUR

-12 to 14 (range accommodates daylight savings time changes)

Not applicable

TIMEZONE_MINUTE

00 to 59

Not applicable

TIMEZONE_REGION

Found in the dynamic performance view V$TIMEZONE_NAMES

Not applicable

TIMEZONE_ABBR

Found in the dynamic performance view V$TIMEZONE_NAMES

Not applicable

PL/SQL大对象（LOB）数据类型用于存储诸如文本、图像、音频和视频等大数据条目，BLOB存储二进制文件（图像等），CLOB存储文本文件。具体包括：

Data Type

Description

Size

BFILE

Used to store large binary objects in operating system files outside the database.

System-dependent. Cannot exceed 4 gigabytes (GB).

BLOB

Used to store large binary objects in the database.

8 to 128 terabytes (TB)

CLOB

Used to store large blocks of character data in the database.

8 to 128 TB

NCLOB

Used to store large blocks of NCHAR data in the database.

8 to 128 TB

引用：

http://www.tutorialspoint.com/plsql/plsql_data_types.htm

 

PL/SQL条件

PL/SQL条件句法有IF-THEN-ELSIF和CASE两种结构，两者可以互换。下面是IF-THEN-ELSIF结构的示例，其中ELSIF和 ELSE部分是可选的，因此可以简化成IF-THEN or, IF-THEN-ELSE结构。

IF x = 1 THEN

  sequence_of_statements_1;

ELSIF x = 2 THEN

  sequence_of_statements_2;

ELSIF x = 3 THEN

  sequence_of_statements_3;

ELSE

  sequence_of_statements_N;

END IF;

以下的CASE结构等价于上面的IF-THEN-ELSIF结构：

CASE

   WHENx = 1 THEN sequence_of_statements_1;

   WHENx = 2 THEN sequence_of_statements_2;

   WHENx = 3 THEN sequence_of_statements_3;

   ELSEsequence_of_statements_N;

END CASE;

         CASE结构在选择条件已知的情况下可以简化为如下结构，类似于其它语言的switch。

CASE x

   WHEN1 THEN sequence_of_statements_1;

   WHEN2 THEN sequence_of_statements_2;

   WHEN3 THEN sequence_of_statements_3;

   ELSEsequence_of_statements_N;

END CASE;

引用：

https://en.wikipedia.org/wiki/PL/SQL#Conditional_statements

 

PL/SQL循环

PL/SQL循环有4种：基础（Basic）、FOR、WHILE和嵌套（Nested）循环。

Loop Type

Description

PL/SQL Basic LOOP

In this loop structure, sequence of statements is enclosed between the LOOP and END LOOP statements. At each iteration, the sequence of statements is executed and then control resumes at the top of the loop.

PL/SQL WHILE LOOP

Repeats a statement or group of statements while a given condition is true. It tests the condition before executing the loop body.

PL/SQL FOR LOOP

Execute a sequence of statements multiple times and abbreviates the code that manages the loop variable.

Nested loops in PL/SQL

You can use one or more loop inside any another basic loop, while or for loop.

         基础循环语句中需要额外的EXIT或EXIT WHEN来中断循环，相当于其他语言的Break。

LOOP

 Sequence of statements;

END LOOP;

         WHILE循环在条件满足时一直执行。

WHILE condition LOOP

  sequence_of_statements;

END LOOP;

         FOR循环有两种用法，一是从初始值到结束值循环一段范围，格式：

FOR counter IN initial_value .. final_valueLOOP

  sequence_of_statements;

END LOOP;

例如：

for i in 0..9 loop  

dbms_output.put_line('i:' || i);  

end loop;  

另一种用法是遍历隐式游标，例如：

for currow in ( 

select t.col1,t.col2 

from tableNamet 

where ... 

) loop 

if currow.col1 =0 then 

return;    -- 中止，返回 

end if; 

end loop;

引用：

http://www.tutorialspoint.com/plsql/plsql_loops.htm

http://wen866595.iteye.com/blog/1733887