PL/SQL学习笔记-1

Oracle9i PL/SQL程序设计 的学习笔记

第1章到第3章

[@more@]

第1章 PL/SQL概述
1.1 为什么使用PL/SQL
PL/SQL是Procedural Language/SQL的缩写。
SQL: Structured Query Language. 结构化查询语言 是第四代语言(4GL)，命令少，语法简单。但是相对来说第三代语言(3GL)例如C或者COBOL，在本质上缺少过程化。因此开发出了PL/SQL-将SQL语言的强大性和灵活性与3GL的过程性结构融为一体。
扩展的有：变量和类型、控制结构、过程和函数、对象类型和方法。
使用PL/SQL,可以将SQL打包到一个PL/SQL语句块中提交给数据库，减少网络I/O。

1.2 PL/SQL的功能特性
PL/SQL的基本单位是语句块。所有的PL/SQL程序都是由语句块构成的，它们之间可以相互嵌套。
语句块的结构：
DECLARE
...
BEGIN
...
EXCEPTION
...
END
1.语句块结构 2.错误处理 3.变量和类型
4.条件语句 5.循环结构 6.游标
7.过程和函数 8.包 9.集合


第2章 开发及运行环境
2.1 应用程序模型和PL/SQL
PL/SQL语句块提交给PL/SQL引擎执行。PL/SQL引擎执行其中的过程性语句，但将语句块中的SQL语句发送给用一个SQL语句执行器。
PL/SQL引擎包括编译器和运行系统。PL/SQL引擎可以在服务器上，也可以在客户机上(例如Form,Report)。PL/SQL引擎之间可以通信。

2.2 PL/SQL开发工具
SQL*Plus、Rapid SQL、DBPartner Debugger、SQL Navigator、TOAD、SQL-Programmer、PL/SQL Developer

第二部分 PL/SQL基本特性

第3章 PL/SQL基础
3.1 PL/SQL语句块
有两种类型的语句块：匿名的和命名的。 命令的又可以分为：标号语句块、子程序包含过程和函数、触发器。
基本块结构：声明部分、执行部分和异常处理部分。只有执行部分是必须的，其他都是可选的。
DELCARE
/*Declarative section is here*/
BEGIN
/*Executable session is here*/
EXCEPTION
/*Exception section is here*/
END;
语句块可以嵌套在一个外部语句块的执行部分或者异常处理部分。
下面是一个例子：
-----------------------------------------------------------------------------------------
SQL> DECLARE
2 v_name varchar(50);
3 BEGIN
4 v_name:='this is outter';
5 DECLARE
6 v_name varchar(50);
7 BEGIN
8 v_name:='this is inner';
9 dbms_output.put_line(v_name);
10 END;
11 dbms_output.put_line(v_name);
12 select object_name into v_name from dba_objects where object_name='AS';
13 EXCEPTION
14 WHEN OTHERS THEN
15 DECLARE
16 v_error_code int;
17 v_error_msg varchar(100);
18 BEGIN
19 v_error_code:=SQLCODE;
20 v_error_msg:=SQLERRM;
21 dbms_output.put_line(v_error_code);
22 dbms_output.put_line(v_error_msg);
23 END;
24 END;
25 /
this is inner
this is outter
100
ORA-01403: 未找到数据

PL/SQL procedure successfully completed
-----------------------------------------------------------------------------------------

3.2 词法单元
所有的PL/SQL程序都是由词法单元构成的。词法单元的字符序列包括：
* 大小写字母
* 数字
* 空白：制表符、空格和回车
* 数字符号：+ - * / < > =
* 标点符号：( ) { } [ ] ? ! ; : . ' " @ # % $ ^ & |
PL/SQL是不区分大小写的。
词法单元可以分为 标示符、分界符、文字和注释。
1.标示符
标示符是用来命令PL/SQL对象，例如变量、游标、类型和子程序。标示符由字母开头，后面跟 字母、数字、货币符号、下划线字符和"#"字符。标示符的最大长度是30。
保留字：PL/SQL中有很多是保留字，使用这些单词命令自己的标示符是非法的。
引证标示符：如果要在标示符中使用非法字符或者保留字，那么可以将该标示符用双引号括起来。此时是区分大小写的。
技巧：如果表中列名用的是保留字，可以在表上创建视图，修改列名，然后通过视图来访问表。

2.分界符
分界符是对PL/SQL有特殊意义的符号。用来将标示符相互分割开来。
例如：+ - * / ( ) ... (具体见P62 表3-1)

3.文字
1.字符型文字。 'four sec' '100%' '''' ...
2.数字型文字。 -17.1 1.345E7 -7.2e-2
3.布尔型文字。 TRUE FALSE NULL

4.注释
1.单行注释。用 -- 标示。
2.多行注释。用 /* 和 */ 标示。

3.3 变量声明
我们通过PL/SQL语句块中的变量与数据库进行通信。
变量声明语法： variable_name type [CONSTANT] [NOT NULL] [:=value];
如果一个变量没有被初始化，默认被赋值为NULL，如果被指名为 NOT NULL ，则必须初始化，否则会抱错。
CONSTANT说明该变量是常量变量，不能被改变值。
在声明中可以用 DEFALUT 替换 ":="。
例如： v_number NUMBER := 45 ; -> v_number NUMBER DEFAULT 45;


3.4 PL/SQL类型
1.标量类型
1.1 数字系列
BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL,
NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, POSITIVEN, REAL, SIGNTYPE, SMALLINT.
1.2 字符系列
CHAR, CHARACTER, LONG, NCHAR, NVARCHAR2, STRING, VARCHAR, VARCHAR2
1.3 日期/区间系列
DATE, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
1.4 行标识系列
ROWID, UROWID
1.5 布尔系列
BOOLEAN
1.6 受托系列
MLSLABEL
1.7 原始系列
RAW, LONG RAW
2.复合类型
RECORD, NESTD TABLE, INDEX-BY TABLE, VARRAY
3.LOB类型
BFILE, CLOB, LOB, NLOB
4.引用类型
REF CURSOR, REF object type
5.对象类型
object type, SYS.ANYTYPE, SYS.ANYDATA, SYS.ANYDATASET

1.标量类型
1.1 数字系列
数字类型有3种基本类型-NUMBER、PLS_INTEGER和BINARY_INTEGER。
NUMBER类型的变量可以存储整数或者实数值。
BINARY_INTEGER或PLS_INTEGER类型的变量仅可以存储整数。
NUMBER(P,S) P是精度，最大值是38。S是刻度，范围是 -84 到 127。
BINARY_INTEGER 数据类型。范围从 -2147483647 到 +2147483647 。以二进制形式存储。
PLS_INTEGER 和 BINARY_INTEGER 的范围相同。
BINARY_INTEGER的字类型：
NATURAL 0..2147483647
NATURALN 0..2147483647 NOT NULL
POSITIVE 1..2147483647
POSITIVEN 1..2147483647 NOT NULL
SSIGNTYPE -1,0,1

NUMBER是以十进制存储，因此在计算的时候会转换成2进制，然后进行计算。因此如果数值不是存储在数据库中但仅仅是用来计算，那只需要用到 BINARY_INTEGER 数据类型了。BINARY_INTEGER 用来存储带符号的整数值。
PLS_INTEGER 和 BINARY_INTEGER 的最大区别在于：在计算过程中，如果结果溢出了，BINARY_INTEGER 会用一个更大的NUMBER变量来计算，不会发生错误。而 PLS_INTEGER 会抱错。(注意这里是计算过程中，如果最终结果溢出，则都回抱错)。

-----------------------------------------------------------------------------------------
SQL> DECLARE
2 v_binaryinteger BINARY_INTEGER;
3 BEGIN
4 v_binaryinteger := 2147483647;
5 v_binaryinteger := v_binaryinteger+1-1;
6 dbms_output.put_line(v_binaryinteger);
7 END;
8 /
2147483647

PL/SQL procedure successfully completed

SQL>
SQL> DECLARE
2 v_pslinteger PLS_INTEGER;
3 BEGIN
4 v_pslinteger := 2147483647;
5 v_pslinteger := v_pslinteger+1-1;
6 dbms_output.put_line(v_pslinteger);
7 END;
8 /

DECLARE
v_pslinteger PLS_INTEGER;
BEGIN
v_pslinteger := 2147483647;
v_pslinteger := v_pslinteger+1-1;
dbms_output.put_line(v_pslinteger);
END;

ORA-01426: 数字溢出
ORA-06512: 在line 5

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

1.2 字符类型
VARCHAR2：PL/SQL中VARCHAR2的最大长度是32767字节。但是注意，数据库中VARCHAR2仅能存储4000字节。因此如果超过只能放到数据库中的LONG或CLOB类型。
在Oracle9i中可以这么声明： VARCHAR2(L[CHAR|BYTE])
CHAR： PL/SQL中CHAR的最大长度是32767字节。但是注意，数据库中 CHAR 仅能存储2000字节。因此如果超过只能放到数据库中的LONG或CLOB类型。
在Oracle9i中可以这么声明： CHAR(L[CHAR|BYTE])
LONG: 与数据库中的LONG类型不同，PL/SQL LONG 类型是一个变长的字符串，其最大长度是 32760字节。
还有NCHAR和NVARCHAR2，具体见Server SQL Reference。

1.3 原始系列
原始类型中用来存储二进制数据。在不同的字符集之间是不会发生转换。
RAW： PL/SQL中 RAW 的最大长度是32767字节。但是注意，数据库中 RAW 仅能存储2000字节。因此如果超过只能放到数据库中的 LONG RAW 类型。
在Oracle9i中可以这么声明： RAW(L)
LONG RAW：PL/SQL中CHAR的最大长度是32760字节。

1.4 日期/区间系列
DATE： 长度是7个字节。
TIMESTAMP： 包含毫米的时间类型。 TIMESTAMP[(P)]。 P是秒字段的小数部分，缺省为6。
TIMESTAMP[(P)] WITH TIME ZONE： 包含时区信息的TIMESTAMP。
TIMESTAMP[(P)] WITH LOCAL ZONE： 存储的是数据库的时区。
INTERVAL类型用于存储两个时间戳之间的时间间隔。
INTERVAL[(P)] TO MONTH、INTERVAL[(DP)] TO SECOND[(SP)]

------------------------------------------------------------------------------------------------
SQL> create table bb
2 (
3 yminterval1 interval year to month,
4 yminterval2 interval year to month,
5 dsinterval1 interval day to second,
6 dsinterval2 interval day to second,
7 dsinterval3 interval day to second,
8 dsinterval4 interval day to second
9 );

Table created

SQL> insert into bb values(NUMTOYMINTERVAL(90,'year'),NUMTOYMINTERVAL(99,'month'),
NUMTODSINTERVAL(99,'DAY'),NUMTODSINTERVAL(99,'HOUR'),
NUMTODSINTERVAL(99,'MINUTE'),NUMTODSINTERVAL(99,'SECOND'));

1 row inserted

SQL> select * from bb;

YMINTERVAL1 YMINTERVAL2 DSINTERVAL1 DSINTERVAL2 DSINTERVAL3 DSINTERVAL4
------------ ------------ ------------------- ------------------- ------------------- --------------------
+90-00 +08-03 +99 00:00:00.000000 +04 03:00:00.000000 +00 01:39:00.000000 +00 00:01:39.000000

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

1.5 行标识系列
ROWID： ROWID PL/SQL类型与数据库ROWID伪列类型是相同的。可以用ROWIDTOCHAR将其转换成为字符串。这是一个18位的字符串。
UROWID：UROWID即可以存储ROWID，又可以存储逻辑行标识符。

1.6 布尔系列
BOOLEAN

1.7 受托类
MLSLABEL： 这种数据类型用在Trusted Oracle中以保存可变长的二进制标记标签。 (从来没有用过，不懂)

2 复合类型
有 记录、表(嵌套表和索引表)、数组。具体的在后面讨论。
3 引用类型
4 LOB类型
用户存储大型的对象。具体的参见后面。
5 对象类型
对象类型是一种复合类型，包括属性和方法。具体的见后面。

推荐使用 %TYPE 来申明变量类型。原表列的属性为NOT NULL的话，并不意味着变量必须也是NOT NULL的
用户自定义子类型： SUBTYPE new_type IS original_type 。
例如：
SUBTYPE t_loopcount IS NUMBER;
v_count t_loopcount;
SUBTYPE定义不能在定义中直接进行限制。以下语法是错误的(除了Oracle8i)。
SUBTYPE t_loopcounter is NUMBER(4);
可以使用下面的方法实现：
v_dummyvar NUMBER(4);
SUBTYPE t_loopcounter is v_dummyvar%TYPE;
v_counter t_loopcounter;

数据类型之间可以有 显式或隐式 的转换。

3.5 表达式和运算符
赋值： variable:=expression;

运算符优先级
**、NOT 求幕、逻辑非
+、- 正、负
*、/ 乘、除
+、-、|| 加、减、字符串连接
=、!=、、<=、>=、IS NULL 逻辑比
LIKE、BETWEEN、IN
AND 逻辑与
OR 逻辑或

3.6 PL/SQL控制结构
IF-THEN-ELSE的语法：
IF ... THEN
...
ELSIF ... THEN
...
ELSE
...
END IF;

CASE语法
[<>]
CASE [test_var]
WHEN value1 THEN sequence_of_statements1;
WHEN value2 THEN sequence_of_statements2;
...
WHEN valuen THEN sequence_of_statementsn;
[ELSE else_sequence;]
END CASE [MyCase];
1.没有ELSE的CASE语句。如果没有ELSE，且表达式与任何一个检测值都不匹配的时候，会抱错ORA-6592，CASE_NOT_FOUND
2.设定标号的CASE语句。CASE语句可以设定标号。
3.搜索式CASE语句。可以没有检测变量。
注意只碰到一个TRUE就会执行之后的语句，然后跳出CASE语句。

简单循环：
LOOP
..
EXIT [WHEN condition];
END LOOP;

WHILE循环：
WHILE condition LOOP
...
END LOOP;
在循环中随时可以用 EXIT 或者 EXIT WHEN 语句退出。

数字式FOR循环
FOR loop_counter IN [REVERSE] low_bound..high_bound LOOP
sequence_of_statement;
END LOOP;
FOR循环的循环索引被隐式声明为 BINARY_INTEGER 。

GOTO： 从外层跳转到内层语句块、循环或者IF语句是非法的。从一个IF子句跳到另一个子句也是非法的。从一个异常处理语句块内部跳转到当前语句块也是非法的。

编译指示(Pragmas)是一些编译器指令，类似于C语言中的#pragma或者#define指令。它是提供给PL/SQL编译器的指令。

3.7 PL/SQL记录
定义记录类型的一般语法是：
TYPE record_type IS RECORD(
field1 type1 [NOT NULL][:=expr1],
...
)
可以使用 select into 的方法，给记录负值。
%ROWTYPE 将返回一个基于表定义的类型。雷上定义的所有 NOT NULL 约束都不会被包含近来。但是VARCHAR2和CHAR的长度和NUMBER的精度会包含近来。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/133835/viewspace-918680/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/133835/viewspace-918680/