--=====================
-- 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; -- 使用表列来定义数据类型
v_sal emp . sal%type ;
2. 复合类型 ( 存放多个值的变量 )
plsql 中包含 pl / sql 记录， pl / sql 表，嵌套表， varray 四种复合类型
-- 定义 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 表
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 = 7788 ;
dbms_output . put_line ( 'Employee Name : ' || ename_table (- 1 ));
end ;
/
PL / SQL 表类似于高级语言中的数组 , 且 PL / SQL 表的下表可以为负值 , 元素的个数没有限制 . 注意必须首先定义 PL / SQL 表类型及表变量
在本例中 , ename_table_type 为表类型 , 表类型中元素的数据类型为 emp . ename % type ，
ename_table 为表变量 , ename_table (- 1 ) 表示下标为 - 1 的元素
-- 嵌套表， varray 例子略
3. 参照类型变量
用于存放数值指针的变量。通常包括游标变量 ( REF CURSOR ) 和对象类型变量 ( REF obj_type )
4.LOB 变量
存储大批量数据的变量，通常分为内部 LOB 以及外部 LOB
5. 使用 SQL * Plus 变量
必须首先使用 variable 命令定义变量，如在 SQL * Plus 中输出变量，则使用 print variable_name
scott@ORCL > var name varchar2 ( 20 );
scott@ORCL > begin
2 select ename into : name from emp where empno = 7788 ;
3 end ;
4 /
PL / SQL procedure successfully completed .
scott@ORCL > print name ;
NAME
--------------------------------
SCOTT
三、 PL / SQL 中常用的变量赋值方式
1. 在定义时赋值
v_empno number := 7788
c_tax_rate constant number ( 3 , 2 ):= 3.35 ;
2. 使用 select ... into 来赋值
SELECT expression INTO var_list FROM table_name WHERE condition
-- 例：将号部门的名称和工作地点显示出来
DECLARE
v_name dept . dname % TYPE ;
v_loc dept . loc % TYPE ;
BEGIN
select dname , loc INTO v_name , v_loc FROM dept WHERE deptno = 10 ;
DBMS_OUTPUT . PUT_LINE ( '10 DEPT NAME: ' || v_name );
DBMS_OUTPUT . PUT_LINE ( '10 DEPT loc: ' || v_loc );
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT . PUT_LINE ( 'NOT FOUND RECORD' );
END ;
/
-- 例：输入员工编号，查询员工姓名，职位，工资
scott@ORCL > DECLARE
2 v_name emp . ename % TYPE ;
3 v_job emp . job % TYPE ;
4 v_sal emp . sal % TYPE ;
5 v_id emp . empno % TYPE ;
6 BEGIN
7 v_id :=& inputid ;
8 SELECT ename , job , sal INTO v_name , v_job , v_sal FROM emp WHERE empno = v_id ;
9 DBMS_OUTPUT . PUT_LINE ( '7788 NAME:' || v_name );
10 DBMS_OUTPUT . PUT_LINE ( '7788 job:' || v_job );
11 DBMS_OUTPUT . PUT_LINE ( '7788 sal:' || v_sal );
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 DBMS_OUTPUT . PUT_LINE ( 'NOT FOUND RECORD!' );
15 END ;
16 /
Enter value for inputid : 7788
old 7 : v_id :=& inputid ;
new 7 : v_id := 7788 ;
7788 NAME : SCOTT
7788 job : ANALYST
7788 sal : 3000
PL / SQL procedure successfully completed .
-- 例：执行 UPDATE
scott@ORCL > DECLARE
2 v_sal emp . sal % TYPE ;
3 old_sal emp . sal % TYPE ;
4 v_id emp . empno % TYPE ;
5 BEGIN
6 v_sal :=& inputsal ;
v_id :=& inputid ;
7 v_id :=& inputid ;
8 SELECT sal INTO old_sal FROM emp WHERE empno = v_id ;
9 UPDATE emp SET sal = v_sal WHERE empno = v_id ;
10 DBMS_OUTPUT . PUT_LINE ( 'old sal:' || old_sal );
11 DBMS_OUTPUT . PUT_LINE ( 'new sal:' || v_sal );
12 END ;
13 /
Enter value for inputsal : 3500
old 6 : v_sal :=& inputsal ;
new 6 : v_sal := 3500 ;
Enter value for inputid : 7788
old 7 : v_id :=& inputid ;
new 7 : v_id := 7788 ;
old sal : 3000
new sal : 3500
PL / SQL procedure successfully completed .
四、更多参考
有关 SQL 请参考
SQL 基础--> ROLLUP 与CUBE 运算符实现数据汇总
SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)
有关 PL/SQL 请参考
