PL/SQL语言基础(原创)-白红宇

PL/SQL概述

PL/SQL是过程化的SQL语言，是ORACLE对SQL语言的扩展，在普通SQL语句的基础上增加了编程语言的特点。使得该语言不仅具有过程编程语言的特征，如循环、条件分支等。同时也具有对象编程语言的特征，如重载、继承等。

PL/SQL程序语言的组成

主要由块组成

一个块由三个基本部分组成：声明、执行体、异常处理

PL/SQL块有匿名块和命名块

命名块会将代码保存到服务器

典型的块结构如下

[ DECLARE --声明部分

declaration_statements

]

BEGIN

executable_statements --执行体部分

[EXCEPTION

exception_handling_statements --异常处理部分

]

END;

PL/SQL的语言优点有:支持SQL；高生成率，性能好；可移植性；与Oracle集成。

PL/SQL的语法要素

PL/SQL语法要素包括字符集、标识符、文本、分隔符、注释等。

字符集：

包括英文大小写

0－数字

空白符、制表符、空格

一些特殊符号，如～、！、@、＃等

PL/SQL字符集不区分大小写

标识符：

变量、常量、子程序的名称

以字母开头、最大长度个字符

包含空格等特殊符号时，要用英文双引号括起来

文字：

数值型

可用常规表示，也可以用科学计数法和幂的形式

字符型

字符型用单引号括起

日期型

布尔型

TRUE、FALSE、NULL

PL/SQL的数据类型

NUMBER 数字型能存放整数值和实数值，并且可以定义精度和取值范围

DEC 数字型NUMBER的子类型，小数

DOUBLE PRECISION 数字型NUMBER的子类型，高精度实数

INTEGER 数字型NUMBER的子类型，整数

INT 数字型NUMBER的子类型，整数

NUMERIC 数字型NUMBER的子类型，与NUMBER等价

REAL 数字型NUMBER的子类型，与NUMBER等价

SMALLINT 数字型NUMBER的子类型，取值范围比INTEGER小

BINARY_INTEGER 数字型可存储带符号整数，为整数计算优化性能

VARCHAR2 字符型存放可变长字符串，有最大长度

CHAR 字符型定长字符串

LONG 字符型变长字符串，最大长度可达,767

DATE 日期型以相同的格式存放日期值

BOOLEAN 布尔型TRUE OR FALSE

ROWID ROWID 存放数据库的行号

LOB类型，又分为BFILE,BLOB,CLOB,NCLOB

操作符

算术运算符

＋、－、*、／、**

比较运算符

＝、<>、~=、!= 、^=、<、<=、>、>=

其它分隔符

()、:= (赋值)、，(表表项的分隔）、| |（字符串连接）

<< （标号开始） >> (标号的结束)

-- 单行注释

/* */ 多行注释

% 属性指示器，一般与TYPE、ROWTYPE等一起用

定义变量

变量的类型可以分为

标量类型

复合类型

参照类型

LOB 类型

1、标量类型(常用)

varchar2(n),char(n),number(p,s),date,timestamp,long(long raw),boolean,binary_integer,binary_float,binary_double

定义方法(定义时必须使用标量类型)

identifier [constant] datatype [not null] [:= | default expr]

v_ename varchar2(20);

v_sal number(6,2);

c_tax_rate constant number(3,2):=3.35;

v_hiredate date;

v_valid boolean not null default false;

v_ename emp.ename%type; --使用表列来定义数据类型

2、复合类型变量，指用于存放多个值的变量。当定义复合变量时，必须要使用PL/SQL的复合数据类型。PL/SQL包括PL/SQL记录、PL/SQL表、嵌套表及VARRAY等四种复合数据类型。

PL/SQL记录

PL/SQL记录类似于高级语言中的结构，每个PL/SQL记录一般都包含多个成员。当使用PL/SQL记录时，首先需要在定义部分定义记录类型和记录变量，然后在执行部分引用该记录变量。需要注意，当引用记录成员时，必须要加记录变量作为前缀（记录变量.记录成员）。

使用select into进行变量赋值时，如果选择列表包含多个列和表达式，并且使用标量接收数据，就需要定义多个标量，如果使用plsql记录接收数据就只需要定义一个记录变量即可，从而简化了数据的处理。在select..into语句中使用plsql记录时，既可以使用记录变量，也可以使用记录成员。

==============定义PL/SQL记录==============

declare

type emp_record_type is record(

name emp.ename%type,

salary emp.sal%type,

title emp.job%type);

emp_record emp_record_type;

begin

select ename,sal,job into emp_record

from scott.emp where empno=7788;

dbms_output.put_line('Employee Name: ' || emp_record.name);

end;

=========================================

PL/SQL表

与记录一样，表也是PL/SQL中的一种复合。PL/SQL表是一个一维结构，它是存储相同类型值的元素的一个集合。PL/SQL表类似于高级语言中的数组。需要注意，PL/SQL表与高级语言的数组有所区别，高级语言数组的下标不能为负，但PL/SQL表的下标可以为负值；高级语言数组的元素个数有限制，而PL/SQL表的元素个数没有限制，并且其下标没有上下限。当使用PL/SQL表时，必须首先在定义部分定义PL /SQL表类型和PL/SQL表变量，然后在执行部分中引用该PL/SQL表变量。

PL/SQL表的声明

通用语法格式为：

TYPE tabletypename IS TABLE OF

datatype | variablename%TYPE | tablename.columnname%TYPE

[ NOT NULL ] INDEX BY BINARY_INTERGER;

例如：

TYPE deptname_table_type IS TABLE OF dept.DeptName%TYPE

INDEX BY BINARY_INTERGER;

TYPE major_table_type IS TABLE OF VARCHAR2(50)

INDEX BY BINARY_INTEGER;

我们可用标量数据类型或声明属性%TYPE来声明一个表类型。但声明时必须添加INDEX BY BINARY_INTEGER子句。这是目前对表索引唯一可以使用的子句。主键随同数据列一起存储在表的内部。表包括两列，即索引/主键列和数据列。

声明实际的表，通过“表名+PL/SQL表名”来定义实际的表。基于上面的声明的表类型来定义该实际的表，如下：

deptname_table deptname_table_type;

引用表元素/行时，我们不能只通过表名来引用表，必须将主键值置于一对圆括号中，作为其下标所索引：

tablename(primarykeyvalue)

例如

deptname_table(5):=’Human Resources’

注：在PL/SQL表中，主键值不是预分配的，只有当一行赋值时此行才会创建

内置函数

内置函数是PL/SQL提供的对PL\SQL表进行操作的方法，通用语法格式为：

tablename.methodname[(index1[,index2])

其中methodname是对表进行操作的方法。内置方法如下表所示：

==============定义PL/SQL表==============

declare

type ename_table_type is table of emp.ename%type

index by binary_integer;

ename_table ename_table_type;

begin

select ename into ename_table(-1) from scott.emp

where empno=7369;

select ename into ename_table(100) from scott.emp

where empno=7788;

dbms_output.put_line('First Employee Name : '|| ename_table(-1) || ' Number is ' || ename_table.first);

dbms_output.put_line('Last Employee Name : '|| ename_table(100) || ' Number is ' || ename_table.last);

dbms_output.put_line('Employee Number : '|| ename_table.count);

end;

First Employee Name : SMITH Number is -1

Last Employee Name : SCOTT Number is 100

Employee Number : 2

=========================================

可以看到，PL\SQL下标的计算只针对以使用的PL\SQL表成员有效。

--嵌套表，varray例子略

3、参照类型变量

用于存放数值指针的变量。通常包括游标变量(REF CURSOR)和对象类型变量(REF obj_type)

4、LOB变量

存储大批量数据的变量，通常分为内部LOB以及外部LOB

变量的赋值

在SQL*PLUS中必须首先使用variable命令定义变量，如在SQL*Plus中输出变量，则使用print variable_name

==============SQL*PLUS变量赋值==============

SQL> var name varchar2(20)

SQL> begin

2 select ename into :name from emp where empno=7788;

3 end;

4 /

PL/SQL procedure successfully completed.

SQL> print name

NAME

--------------------------------------------------------------------------------

SCOTT

当然可以直接为变量赋值，方法如下

SQL> var empno number

SQL> exec :empno :=7788

PL/SQL procedure successfully completed.

SQL> print empno

EMPNO

----------

7788

===========================================

在PL\SQL中变量的赋值方法有：在定义时赋值，使用select ... into赋值例，手工赋值三种。

==============PL\SQL变量赋值==============

declare

v_deptno number :=30; --在定义时赋值

v_name emp.ename%type;

v_job emp.job%type;

v_empno emp.empno%type;

begin

v_empno := &empno; --手工赋值

select ename,job into v_name,v_job from emp where deptno = v_deptno and empno = v_empno; --通过select into赋值

dbms_output.put_line('Name: ' || v_name);

dbms_output.put_line('Job: ' || v_job);

exception

when no_data_found then

dbms_output.put_line('NO FOUND RECORD');

end;

===========================================

参考至:http://blog.csdn.net/robinson_0612/article/details/6063399

http://www.cnblogs.com/FeiyueHang/archive/2011/06/30/2094862.html

http://blog.163.com/xuejelly1985@126/blog/static/36210340200882394133500/

http://fzguon.blog.sohu.com/160135515.html

http://www.2cto.com/database/201110/107424.html

本文原创，转载请注明出处、作者

如有错误，欢迎指正

游戏:czmcj@163.com

作者：czmmiao 文章出处：http://czmmiao.iteye.com/blog/1810096