Oracle PL/SQL语言(Procedural Language/SQL)是结合了结构化查询与Oracle自身过程控制为一体的强大语言,PL/SQL不但支持更多的数据类型,拥有自身的变量声明、赋值语句,而且还有条件、循环等流程控制语句。过程控制结构与SQL数据处理能力无缝的结合形成了强大的编程语言,可以创建过程和函数以及程序包。
PL/SQL的优点:
1.支持SQL:SQL是访问数据库的标准语言,通过SQL命令,用户可以操纵数据库中的数据。PL/SQL支持 所有的SQL数据操纵命令、游标控制命令、事务控制命令、SQL函数、运算符和伪列。同时PL/SQL和SQL语言紧密集成,PL/SQL支持 所有的SQL数据类型和NULL值。
2.支持面向对象编程:PL/SQL支持面向对象的编程,在PL/SQL中可以创建类型,可以对类型进行继承,可以在子程序中重载方法等。 (暂时不是很理解)
3.更好的性能:SQL是非过程语言,只能一条一条执行,而PL/SQL把一个PL/SQL块统一进行编译后执行,同时还可以把编译好的PL/SQL块存储起来,以备重用,减少了应用程序和服务器之间的通信时间,PL/SQL是快速而高效的。
4.可移植性:使用PL/SQL编写的应用程序,可以移植到任何操作系统平台上的Oracle服务器,同时还可以编写可移植程序库,在不同环境中重用。
5.安全性:可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分隔,这样可以限制对Oracle数据库的访问,数据库还可以授权和撤销其他用户访问的能力。
PL/SQL块:是一种块结构的语言,一个PL/SQL程序包含了一个或者多个逻辑块,逻辑块中可以声明变量,变量在使用之前必须先声明。除了正常的执行程序外,PL/SQL还提供了专门的异常处理部分进行异常处理。 每个逻辑块分为三个部分,语法是:
语法结构:PL/SQL逻辑块的语法
[DECLARE
--declaration statements] ①
BEGIN
--executable statements ②
[EXCEPTION
--exception statements] ③
END;
说明:
① 声明部分:声明部分包含了 变量和常量的定义。这个部分由关键字DECLARE开始, 如果不声明变量或者常量,可以省略这部分。
② 执行部分:执行部分是 PL/SQL块的指令部分,由关键字BEGIN开始,关键字END结尾。所有的可执行PL/SQL语句都放在这一部分,该部分执行命令并操作变量。其他的PL/SQL块可以作为子块嵌套在该部分。PL/SQL块的执行部分是必选的。注意END关键字后面用分号结尾。
③ 异常处理部分:该部分是 可选的,该部分用EXCEPTION关键字把可执行部分分成两个小部分,之前的程序是正常运行的程序,一旦出现异常就跳转到异常部分执行。
PL/SQL对大小写不敏感,为了良好的程序风格,开发团队都会选择一个合适的编码标准。比如有的团队规定:关键字全部大些,其余的部分小写。
PL/SQL块中的每一条语句都必须以分号结束,SQL语句可以是多行的,但分号表示该语句结束。一行中可以有多条SQL语句,他们之间以分号分隔,但是不推荐一行中写多条语句。
PL/SQL中的特殊符号说明:
类型 符号 说明
赋值运算符 := Java和C#中都是等号,PL/SQL的赋值是:=
特殊字符 || 字符串连接操作符。
-- PL/SQL中的单行注释。
/*,*/ PL/SQL中的多行注释,多行注释不能嵌套。
<<,>> 标签分隔符。只为了标识程序特殊位置。
.. 范围操作符,比如:1..5 标识从1到5
算术运算符 +,-,*,/ 基本算术运算符。
** 求幂操作,比如:3**2=9
关系运算符 >,<,>=,<=,= 基本关系运算符,=表示相等关系,不是赋值。
<>,!= 不等关系。
逻辑运算符 AND,OR,NOT 逻辑运算符。
变量声明:
PL/SQL支持SQL中的数据类型,PL/SQL中正常支持NUMBER,VARCHAR2,DATE等Oracle SQL数据类型。声明变量必须指明变量的数据类型,也可以声明变量时对变量初始化,变量声明必须在声明部分。
语法格式:声明变量
变量名 数据类型[ :=初始值]
数据类型如果需要长度,可以用括号指明长度,比如:varchar2(20)。
eg:声明变量
SQL> DECLARE
2 sname VARCHAR2(20) :='jerry'; ①
3 BEGIN
4 sname:=sname||' and tom'; ②
5 dbms_output.put_line(sname); ③
② 对变量sname重新赋值,赋值运算符是“:=”。
③ dbms_output.put_line是输出语句,可以把一个变量的值输出,在SQL*Plus中输出数据时,可能没有结果显示,可以使用命令:set serveroutput on设置输出到SQL*Plus控制台上。
对变量赋值还可以 使用SELECT…INTO 语句从数据库中查询数据 对变量进行赋值。但是查询的结果 只能是一行记录,不能是零行或者多行记录。
eg:变量赋值
DECLARE
sname VARCHAR2(20) DEFAULT 'jerry'; ①
BEGIN
SELECT ename INTO sname FROM emp WHERE empno=7934; ②
dbms_output.put_line(sname);
END;
① 变量初始化时,可以使用DEFAULT关键字对变量进行初始化。
② 使用select…into语句对变量sname赋值,要求查询的结果必须是一行,不能是多行或者没有记录。
声明常量:
常量在声明时赋予初值,并且在运行时不允许重新赋值。使用 CONSTANT关键字声明常量。
eg:声明常量
SQL> DECLARE
2 pi CONSTANT number :=3.14; --圆周率长值 ①
3 r number DEFAULT 3; --圆的半径默认值3
4 area number; --面积。
5 BEGIN
6 area:=pi*r*r; --计算面积
7 dbms_output.put_line(area); --输出圆的面积
8 END;
① 声明常量时使用关键字CONSTANT,常量初值可以使用赋值运算符(:=)赋值,也可以使用DEFAULT关键字赋值。
代码演示:宿主常量
SQL> var emp_name varchar(30); ①
SQL> BEGIN
2 SELECT ename INTO :emp_name FROM emp WHERE empno=7499; ②
3 END;
4 /
PL/SQL procedure successfully completed
emp_name
---------
ALLEN
SQL> print emp_name; ③
emp_name
---------
ALLEN
① 可以使用var声明宿主变量。
② PL/SQL中访问宿主变量时要在变量前加“:”。
③ 在SQL*Plus中,使用print可以输出变量中的结果。
PL/SQL数据类型:PL/SQL不但支持oracle的数据类型,还具备自身的数据类型。PL/SQL的数据类型包括标量数据类型,引用数据类型和存储文本、图像、视频、声音等非结构化的大数据类型(LOB数据类型)等
标量数据类型:变量只有一个值,且内部没有分量。标量数据类型包括数字型,字符型,日期型和布尔型。这些类型有的是Oracle SQL中定义的数据类型,有的是PL/SQL自身附加的数据类型。字符型和数字型又有子类型,子类型只与限定的范围有关,比如NUMBER类型可以表示整数,也可以表示小数,而其子类型POSITIVE只表示正整数。
类型 说明
VARCHAR2(长度) 可变长度字符串,Oracle SQL定义的数据类型,在PL/SQL中使用时最长32767字节。在PL/SQL中使用没有默认长度,因此必须指定。
NUMBER(精度,小数) Oracle SQL定义的数据类型,见第二章。
DATE Oracle SQL定义的日期类型,见第二章。
TIMESTAMP Oracle SQL定义的日期类型,见第二章。
CHAR(长度) Oracle SQL定义的日期类型,固定长度字符,最长32767字节,默认长度是1,如果内容不够用空格代替。
LONG Oracle SQL定义的数据类型,变长字符串基本类型,最长32760字节。在Oracle SQL中最长2147483647字节。
BOOLEAN PL/SQL附加的数据类型,逻辑值为TRUE、FALSE、NULL
BINARY_INTEGER PL/SQL附加的数据类型,介于-231和231之间的整数。
PLS_INTEGER PL/SQL附加的数据类型,介于-231和231之间的整数。类似于BINARY_INTEGER,只是PLS_INTEGER值上的运行速度更快。
NATURAL PL/SQL附加的数据类型,BINARY_INTEGER子类型,表示从0开始的自然数。
NATURALN 与NATURAL一样,只是要求NATURALN类型变量值不能为NULL。
POSITIVE PL/SQL附加的数据类型,BINARY_INTEGER子类型,正整数。
POSITIVEN 与POSITIVE一样,只是要求POSITIVE的变量值不能为NULL。
REAL Oracle SQL定义的数据类型,18位精度的浮点数
INT,INTEGER,SMALLINTOracle SQL定义的数据类型,NUMBERDE的子类型,38位精度整数。
SIGNTYPE PL/SQL附加的数据类型,BINARY_INTEGER子类型。值有:1、-1、0。
STRING 与VARCHAR2相同。
属性数据类型:当声明一个 变量 的值是数据库中的 一行或者是数据库中 某列时,可以直接使用属性类型来声明。Oracle中存在两种属性类型:%TYPE和%ROWTYPE。
Ø % ROWTYPE: 引用数据库表中的一行作为数据类型,即RECORD类型(记录类型),是PL/SQL附加的数据类型。表示一条记录,就相当于C#中的一个对象。可以使用“ .”来访问记录中的属性。
代码:
SQL> DECLARE
2 myemp EMP%ROWTYPE; ①
3 BEGIN
4 SELECT * INTO myemp FROM emp WHERE empno=7934; ②
5 dbms_output.put_line(myemp.ename); ③
6 END;
7 /
MILLER
PL/SQL procedure successfully completed
① 声明一个myemp对象,该对象表示EMP表中的一行。
② 从EMP表中查询一条记录放入myemp对象中。
③ 访问该对象的属性可以使用“.”。
SQL> DECLARE
2 sal emp.sal%TYPE; ①
3 mysal number(4):=3000;
4 totalsal mysal%TYPE; ②
5 BEGIN
6 SELECT SAL INTO sal FROM emp WHERE empno=7934;
7 totalsal:=sal+mysal;
8 dbms_output.put_line(totalsal);
9 END;
10 /
4300
PL/SQL procedure successfully completed
① 定义变量sal为emp表中sal列的类型。
② 定义totalsal是变量mysal的类型。
PL/SQL条件控制和循环控制:
有三种控制结构:顺序结构,条件结构,循环结构
条件控制:PL/SQL中关于条件控制的关键字有IF-THEN、IF-THEN-ELSE、IF-THEN-ELSIF和多分枝条件CASE。
Ø IF-THEN:
该结构先判断一个条件是否为TRUE,条件成立则执行对应的语句块
语法(PL/SQL中条件语法):
IF 条件 THEN
--条件结构体
END IF;
说明:
① 用IF关键字开始,END IF关键字结束,注意END IF后面有一个分号。
② 条件部分可以不使用括号,但是必须以关键字THEN来标识条件结束,如果条件成立,则执行THEN后到对应END IF之间的语句块内容。如果条件不成立,则不执行条件语句块的内容。
③ PL/SQL中关键字THEN到END IF之间的内容是条件结构体内容。
④ 条件可以使用关系运算符和逻辑运算符。
eg:查询JAMES的工资,如果大于900元,则发奖金800元。
代码演示:IF-THEN应用
DECLARE
newSal emp.sal%TYPE;
BEGIN
SELECT sal INTO newSal FROM emp
WHERE ename='JAMES';
IF newSal>900 THEN ①
UPDATE emp SET comm=800 WHERE ename='JAMES';
END IF;
COMMIT ; ②
END;
① 先判断条件,如果条件为TRUE,则执行条件结构体内部的内容。
② 在PL/SQL块中可以使用事务控制语句,该COMMIT同时也能把PL/SQL块外没有提交的数据一并提交,使用时需要注意。
Ø IF-THEN-ELSE:
语法(PL/SQL中条件语法):
IF 条件 THEN
--条件成立结构体
ELSE
--条件不成立结构体
END IF;
eg:查询JAMES的工资,如果大于900元,则发奖金800元,否则发奖金400元。
代码演示:IF-THEN-ELSE应用
DECLARE
newSal emp.sal%TYPE;
BEGIN
SELECT sal INTO newSal FROM emp
WHERE ename='JAMES';
IF newSal>900 THEN
UPDATE emp
SET comm=800
WHERE ename='JAMES';
ELSE
UPDATE emp
SET comm=400
WHERE ename='JAMES';
END IF;
END;
Ø IF-THEN-ELSIF:
语法(PL/SQL中多分枝条件语法):
IF 条件1 THEN
--条件1成立结构体
ELSIF 条件2 THEN
--条件2成立结构体
ELSE
--以上条件都不成立结构体
END IF;
eg:查询JAMES的工资,如果大于1500元,则发放奖金1000元,如果工资大于900元,则发奖金800元,否则发奖金400元。
代码:
DECLARE
newSal emp.sal%TYPE;
BEGIN
SELECT sal INTO newSal FROM emp
WHERE ename='JAMES';
IF newSal>1500 THEN
UPDATE emp
SET comm=1000
WHERE ename='JAMES';
ELSIF newSal>900 THEN
UPDATE emp
SET comm=800
WHERE ename='JAMES';
ELSE
UPDATE emp
SET comm=400
WHERE ename='JAMES';
END IF;
END;
Ø CASE
CASE是一种 选择结构的控制语句,可以根据条件从多个执行分支中选择相应的执行动作。也可以作为表达式使用,返回一个值。
语法:
CASE [selector]
WHEN 表达式1 THEN 语句序列1;
WHEN 表达式2 THEN 语句序列2;
WHEN 表达式3 THEN 语句序列3;
……
[ELSE 语句序列N];
END CASE;
语法解析:
如果 存在选择器selector,选择器selector与WHEN后面的表达式匹配,匹配成功就执行THEN后面的语句。如果所有表达式都与selector不匹配,则执行ELSE后面的语句。
eg:输入一个字母A、B、C分别输出对应的级别信息。
代码演示:CASE中存在selector,不返回值
DECLARE
v_grade CHAR(1):=UPPER('&p_grade'); ①
BEGIN
CASE v_grade ②
WHEN 'A' THEN
dbms_output.put_line('Excellent');
WHEN 'B' THEN
dbms_output.put_line('Very Good');
WHEN 'C' THEN
dbms_output.put_line('Good');
ELSE
dbms_output.put_line('No such grade');
END CASE;
END;
代码解析:
① & grade表示在运行时由键盘输入字符串到grade变量中。
② v_grade分别于WHEN后面的值匹配,如果成功就执行WHEN后的程序序列。
CASE语句还可以作为表达式使用,返回一个值。
代码演示:CASE中存在selector,作为表达式使用
DECLARE
v_grade CHAR(1):=UPPER('&grade');
p_grade VARCHAR(20) ;
BEGIN
p_grade := ①
CASE v_grade
WHEN 'A' THEN
'Excellent'
WHEN 'B' THEN
'Very Good'
WHEN 'C' THEN
'Good'
ELSE
'No such grade'
END;
dbms_output.put_line('Grade:' ||v_grade||',the result is '||p_grade);
END;
代码解析:
① CASE语句可以返回一个结果给变量p_grade
PL/SQL还提供了 搜索CASE语句。也就是说, 不使用CASE中的选择器,直接在WHEN后面判断条件,第一个条件为真时,执行对应THEN后面的语句序列。
代码演示:搜索CASE
DECLARE
v_grade CHAR(1):=UPPER('&grade');
p_grade VARCHAR(20) ;
BEGIN
p_grade :=
CASE
WHEN v_grade='A' THEN
'Excellent'
WHEN v_grade='B' THEN
'Very Good'
WHEN v_grade='C' THEN
'Good'
ELSE
'No such grade'
END;
dbms_output.put_line('Grade:' ||v_grade||',the result is '||p_grade);
END;
循环结构:
PL/SQL提供了丰富的循环结构来重复执行一些列语句。Oracle提供的循环类型有:无条件循环LOOP-END LOOP语句,WHILE循环语句,FOR循环语句,这三类循环中用 EXIT强制结束循环,相当于C#循环中的break。
Ø LOOP循环:最简单的循环,也称为无限循环,LOOP和END LOOP是关键字。
语法:
LOOP
--循环体
END LOOP;
说明:
1.循环体在LOOP和END LOOP之间,在每个LOOP循环体中,首先执行循环体中的语句序列,执行完后再重新开始执行。
2.在LOOP循环中可以使用EXIT或者[EXIT WHEN 条件]的形式终止循环。否则该循环就是死循环。
eg:执行1+2+3+…+100的值
代码演示:
DECLARE
counter number(3):=0;
sumResult number:=0;
BEGIN
LOOP
counter := counter+1;
sumResult := sumResult+counter;
IF counter>=100 THEN ①
EXIT;
END IF;
--EXIT WHEN counter>=100; ②
END LOOP;
dbms_output.put_line('result is :'||to_char(sumResult));
END;
代码解析:
① LOOP循环中可以使用IF结构嵌套EXIT关键字退出循环
② 注释行,该行可以代替①中的循环结构,WHEN后面的条件成立时跳出循环。
Ø WHILE循环:先判断条件,条件成立再执行循环体。
语法:
WHILE 条件 LOOP
--循环体
END LOOP;
代码演示:
DECLARE
counter number(3):=0;
sumResult number:=0;
BEGIN
WHILE counter<100 LOOP
counter := counter+1;
sumResult := sumResult+counter;
END LOOP;
dbms_output.put_line('result is :'||sumResult);
END;
Ø FOR循环:需要预先确定的循环次数,可 通过给循环变量指定下限和上限来确定循环运行的次数,然后 循环变量在每次循环中递增(或者递减)。
语法:
FOR 循环变量 IN [REVERSE] 循环下限..循环上限 LOOP
--循环体
END LOOP;
语法解析:
循环变量:该变量的值每次循环根据上下限的REVERSE关键字进行加1或者减1。
REVERSE:指明循环从上限向下限依次循环。
代码演示:
DECLARE
counter number(3):=0;
sumResult number:=0;
BEGIN
FOR counter IN 1..100 LOOP
sumResult := sumResult+counter;
END LOOP;
dbms_output.put_line('result is :'||sumResult);
END;
顺序结构:在程序顺序结构中有两个特殊的语句。GOTO和NULL
Ø GOTO语句:GOTO语句将 无条件的跳转到标签指定的语句去执行。 标签是用双尖括号括起来的标示符,在PL/SQL块中必须 具有唯一的名称,标签后必须紧跟 可执行语句或者 PL/SQL块。GOTO 不能跳转到 IF语句、CASE语句、LOOP语句、或者子块中。
Ø NULL语句:NULL语句 什么都不做, 只是将控制权转到下一行语句。NULL语句是 可执行语句(这句的意思是,goto可以通过标签跳转到NULL)。NULL语句在 IF或者其他语句语法要求 至少需要一条可执行语句,但又不需要具体操作的地方。比如GOTO的目标地方不需要执行任何语句时。
eg:GOGO 和 NULL
代码演示:
DECLARE
sumsal emp.sal%TYPE;
BEGIN
SELECT SUM(sal) INTO sumsal FROM EMP;
IF sumsal>20000 THEN
GOTO first_label; ①
ELSE
GOTO second_label; ②
END IF;
<<first_label>> ③
dbms_output.put_line('ABOVE 20000:' || sumsal);
<<second_label>> ④
NULL;
END;
代码解析:
① 跳转到程序first_label位置,就是②的位置,first_label是一个标签,用两个尖括号包含。
② 无条件跳转到sedond_label位置,就是④的位置。④处不执行任何内容,因此是一个NULL语句。
在PL/SQL中,各种循环之间可以相互嵌套。
PL/SQL中动态执行SQL语句:
在PL/SQL程序开发中,可以使用 DML语句和 事务控制语句,但是还有很多语句(比如 DDL语句) 不能直接在PL/SQL中执行。这些语句可以使用 动态SQL来实现。
PL/SQL块先编译然后再执行,动态SQL语句在编译时不能确定,只有在程序执行时把SQL语句作为字符串的形式由动态SQL命令来执行。在 编译阶段SQL语句作为字符串存在,程序 不会对字符串中的内容进行 编译,在 运行阶段再对字符串中的SQL语句进行 编译和执行。
语法:
EXECUTE IMMEDIATE 动态语句字符串
[INTO 变量列表]
[USING 参数列表]
语法解析:
如果动态语句是SELECT语句,可以把查询的结果保存到INTO后面的变量中。如果动态语句中存在参数, USING为语句中的参数传值。
动态SQL中的参数格式是:[:参数名],参数在运行时需要 使用USING传值。
eg:动态SQL
代码演示:
DECLARE
sql_stmt VARCHAR2(200); --动态SQL语句
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 90;
dept_name VARCHAR2(14) := 'PERSONNEL';
location VARCHAR2(13) := 'DALLAS';
emp_rec emp%ROWTYPE;
BEGIN
--无子句的execute immediate
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; ④
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
USING dept_id; ⑤
END;
代码解析:
① 动态执行一个完整的SQL语句。
② SQL语句中存在3个参数分别标识为:[:1、:2、:3],因此需要用USING关键字对三个参数分别赋值。
③ 对动态查询语句可以使用INTO子句把查询的结果保存到一个变量中,要求该结果只能是单行。
④ 在Oracle的insert,update,delete语句都可以使用RETURNING子句把操作影响的行中的数据返回, 对SQL语句中存在RETURNING子句时,在动态执行时可以使用RETURNING INTO来接收。
⑤ 动态执行参数中可以是: [:数字]也可以是[:字符串]。
PL/SQL的异常处理:
在程序运行时出现的错误,称为异常。 发生异常后,语句将停止执行,PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。异常处理机制简化了代码中的错误检测。PL/SQL中任何异常出现时,每一个异常都对应一个异常码和异常信息。
预定义异常:
为了Oracle开发和维护的方便,在Oracle异常中,为常见的异常码定义了对应的异常名称,称为预定义异常,常见的预定义异常有:
异常名称 异常码 描述
DUP_VAL_ON_INDEX ORA-00001 试图向唯一索引列插入重复值
INVALID_CURSOR ORA-01001 试图进行非法游标操作。
INVALID_NUMBER ORA-01722 试图将字符串转换为数字
NO_DATA_FOUND ORA-01403 SELECT INTO语句中没有返回任何记录。
TOO_MANY_ROWS ORA-01422 SELECT INTO语句中返回多于1条记录。
ZERO_DIVIDE ORA-01476 试图用0作为除数。
CURSOR_ALREADY_OPEN ORA-06511 试图打开一个已经打开的游标
PL/SQL中用EXCEPTION关键字开始异常处理 (异常处理在end之前):
语法:
BEGIN
--可执行部分
EXCEPTION -- 异常处理开始
WHEN 异常名1 THEN
--对应异常处理
WHEN 异常名2 THEN
--对应异常处理
……
WHEN OTHERS THEN
--其他异常处理
END;
语法解析:
异常发生时,进入异常处理部分,具体的异常与若干个WHEN子句中指明的异常名匹配,匹配成功就进入对应的异常处理部分,如果对应不成功,则进入OTHERS进行处理。
eg:异常处理
代码:
SQL> DECLARE
2 newSal emp.sal % TYPE;
3 BEGIN
4 SELECT sal INTO newSal FROM emp;
5 EXCEPTION
6 WHEN TOO_MANY_ROWS THEN
7 dbms_output.put_line('返回的记录太多了');
8 WHEN OTHERS THEN
9 dbms_output.put_line('未知异常');
10 END;
11 /
返回的记录太多了
PL/SQL procedure successfully completed
自定义异常:
除了预定义异常外,用户还可以在开发中自定义异常,自定义异常可以让用户采用与PL/SQL引擎处理错误相同的方式进行处理,用户自定义异常的两个关键点:
Ø 异常定义:在PL/SQL块的声明部分采用EXCEPTION关键字声明异常,定义方法与定义变量相同。比如声明一个myexception异常方法是:
myexception EXCEPTION;
Ø 异常引发:在程序可执行区域,使用 RAISE关键字进行引发。比如引发myexception方法是:
RAISE myexception;
eg:自定义异常
代码:SQL> DECLARE
2 sal emp.sal%TYPE;
3 myexp EXCEPTION; ①
4 BEGIN
5 SELECT sal INTO sal FROM emp WHERE ename='JAMES';
6 IF sal<5000 THEN
7 RAISE myexp; ②
8 END IF;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 dbms_output.put_line('NO RECORDSET FIND!');
12 WHEN MYEXP THEN ③
13 dbms_output.put_line('SAL IS TO LESS!');
14 END;
15 /
SAL IS TO LESS!
PL/SQL procedure successfully completed
说明:
① 用EXCEPTION定义一个异常变量myexp
② 在一定条件下用RAISE引发异常myexp
③ 在异常处理部分,捕获异常,如果不处理异常,该异常就抛给程序执行者。
引发应用程序异常 (不是很理解):
在Oracle开发中,遇到的系统异常都有对应的异常码,在应用系统开发中,用户自定义的异常也可以指定一个异常码和异常信息,Oracle系统为用户预留了自定义异常码,其范围介于-20000到-20999之间的负整数。
语法:
RAISE_APPLICATION_ERROR(异常码,异常信息)
eg:引发系统异常
代码:
SQL> DECLARE
2 sal emp.sal%TYPE;
3 myexp EXCEPTION;
4 BEGIN
5 SELECT sal INTO sal FROM emp WHERE ename='JAMES';
6 IF sal<5000 THEN
7 RAISE myexp;
8 END IF;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 dbms_output.put_line('NO RECORDSET FIND!');
12 WHEN MYEXP THEN
13 RAISE_APPLICATION_ERROR(-20001,'SAL IS TO LESS!'); ①
14 END;
15 /
ORA-20001: SAL IS TO LESS! ②
ORA-06512: 在 line 14
说明:
① 引发应用系统异常,指明异常码和异常信息。
② 在控制台上显示异常码和异常信息。
如果要处理未命名的内部异常,必须使用OTHERS异常处理器。也可以利用PRAGMA EXCEPTION_INIT把一个异常码与异常名绑定。
PRAGMA由编译器控制,PRAGMA在编译时处理,而不是在运行时处理。EXCEPTION_INIT告诉编译器将异常名与ORACLE错误码绑定起来,这样可以通过异常名引用任意的内部异常,并且可以通过异常名为异常编写适当的异常处理器。
语法:
PRAGMA EXCEPTION_INIT(异常名,异常码)
这里的异常码可以是用户自定义的异常码,也可以是Oracle系统的异常码。
eg:PRAGMA EXCEPTION_INIT异常
代码:
<<outterseg>>
DECLARE
null_salary EXCEPTION;
PRAGMA EXCEPTION_INIT(null_salary, -20101); ①
BEGIN
<<innerStart>> ②
DECLARE
curr_comm NUMBER;
BEGIN
SELECT comm INTO curr_comm FROM emp WHERE empno = &empno;
IF curr_comm IS NULL THEN
RAISE_APPLICATION_ERROR(-20101, 'Salary is missing'); ③
ELSE
dbms_output.put_line('有津贴');
END IF;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('没有发现行');
WHEN null_salary THEN
dbms_output.put_line('津贴未知'); ④
WHEN OTHERS THEN
dbms_output.put_line('未知异常');
END;
说明:
① 把异常名称null_salary与异常码-20101关联,该语句由于是预编译语句,必须放在声明部分。也就是说-20101的异常名称就是null_salary。
② 嵌套PL/SQL语句块
③ 在内部PL/SQL语句块中引发应用系统异常-20101。
④ 在外部的PL/SQL语句块中就可以用异常名null_salary进行捕获。
总结:
Ø PL/SQL是一种块结构的语言,它将一组语句放在一个块中,一次性发送给服务器,PL/SQL引擎把接收到PL/SQL语句块中的内容进行分析,把其中的过程控制语句由PL/SQL引擎自身去执行,把PL/SQL语句块中的SQL语句交给服务器的SQL语句执行器执行。
Ø PL/SQL的数据类型包括标量数据类型,引用数据类型和存储文本、图像、视频、声音等非结构化的大数据类型(LOB数据类型)等。
Ø Oracle中存在两种属性类型:%TYPE和%ROWTYPE。
Ø PL/SQL程序可通过控制结构来控制命令执行的流程。PL/SQL中提供三种程序结构:顺序结构、条件结构和循环结构。
Ø 在PL/SQL程序开发中,可以使用DML语句和事务控制语句,还可以动态执行SQL语句,动态执行SQL语句的命令是:EXECUTE IMMEDIATE。
PL/SQL的优点:
1.支持SQL:SQL是访问数据库的标准语言,通过SQL命令,用户可以操纵数据库中的数据。PL/SQL支持 所有的SQL数据操纵命令、游标控制命令、事务控制命令、SQL函数、运算符和伪列。同时PL/SQL和SQL语言紧密集成,PL/SQL支持 所有的SQL数据类型和NULL值。
2.支持面向对象编程:PL/SQL支持面向对象的编程,在PL/SQL中可以创建类型,可以对类型进行继承,可以在子程序中重载方法等。 (暂时不是很理解)
3.更好的性能:SQL是非过程语言,只能一条一条执行,而PL/SQL把一个PL/SQL块统一进行编译后执行,同时还可以把编译好的PL/SQL块存储起来,以备重用,减少了应用程序和服务器之间的通信时间,PL/SQL是快速而高效的。
4.可移植性:使用PL/SQL编写的应用程序,可以移植到任何操作系统平台上的Oracle服务器,同时还可以编写可移植程序库,在不同环境中重用。
5.安全性:可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分隔,这样可以限制对Oracle数据库的访问,数据库还可以授权和撤销其他用户访问的能力。
PL/SQL块:是一种块结构的语言,一个PL/SQL程序包含了一个或者多个逻辑块,逻辑块中可以声明变量,变量在使用之前必须先声明。除了正常的执行程序外,PL/SQL还提供了专门的异常处理部分进行异常处理。 每个逻辑块分为三个部分,语法是:
语法结构:PL/SQL逻辑块的语法
[DECLARE
--declaration statements] ①
BEGIN
--executable statements ②
[EXCEPTION
--exception statements] ③
END;
说明:
① 声明部分:声明部分包含了 变量和常量的定义。这个部分由关键字DECLARE开始, 如果不声明变量或者常量,可以省略这部分。
② 执行部分:执行部分是 PL/SQL块的指令部分,由关键字BEGIN开始,关键字END结尾。所有的可执行PL/SQL语句都放在这一部分,该部分执行命令并操作变量。其他的PL/SQL块可以作为子块嵌套在该部分。PL/SQL块的执行部分是必选的。注意END关键字后面用分号结尾。
③ 异常处理部分:该部分是 可选的,该部分用EXCEPTION关键字把可执行部分分成两个小部分,之前的程序是正常运行的程序,一旦出现异常就跳转到异常部分执行。
PL/SQL对大小写不敏感,为了良好的程序风格,开发团队都会选择一个合适的编码标准。比如有的团队规定:关键字全部大些,其余的部分小写。
PL/SQL块中的每一条语句都必须以分号结束,SQL语句可以是多行的,但分号表示该语句结束。一行中可以有多条SQL语句,他们之间以分号分隔,但是不推荐一行中写多条语句。
PL/SQL中的特殊符号说明:
类型 符号 说明
赋值运算符 := Java和C#中都是等号,PL/SQL的赋值是:=
特殊字符 || 字符串连接操作符。
-- PL/SQL中的单行注释。
/*,*/ PL/SQL中的多行注释,多行注释不能嵌套。
<<,>> 标签分隔符。只为了标识程序特殊位置。
.. 范围操作符,比如:1..5 标识从1到5
算术运算符 +,-,*,/ 基本算术运算符。
** 求幂操作,比如:3**2=9
关系运算符 >,<,>=,<=,= 基本关系运算符,=表示相等关系,不是赋值。
<>,!= 不等关系。
逻辑运算符 AND,OR,NOT 逻辑运算符。
变量声明:
PL/SQL支持SQL中的数据类型,PL/SQL中正常支持NUMBER,VARCHAR2,DATE等Oracle SQL数据类型。声明变量必须指明变量的数据类型,也可以声明变量时对变量初始化,变量声明必须在声明部分。
语法格式:声明变量
变量名 数据类型[ :=初始值]
数据类型如果需要长度,可以用括号指明长度,比如:varchar2(20)。
eg:声明变量
SQL> DECLARE
2 sname VARCHAR2(20) :='jerry'; ①
3 BEGIN
4 sname:=sname||' and tom'; ②
5 dbms_output.put_line(sname); ③
6 END;
输出结果:jerry and tom
① 声明一个变量sname,初始化值是“jerry”。字符串用单引号,如果字符串中出现单引号可以使用两个单引号(’’)来表示,即 单引号同时也具有转义的作用。② 对变量sname重新赋值,赋值运算符是“:=”。
③ dbms_output.put_line是输出语句,可以把一个变量的值输出,在SQL*Plus中输出数据时,可能没有结果显示,可以使用命令:set serveroutput on设置输出到SQL*Plus控制台上。
对变量赋值还可以 使用SELECT…INTO 语句从数据库中查询数据 对变量进行赋值。但是查询的结果 只能是一行记录,不能是零行或者多行记录。
eg:变量赋值
DECLARE
sname VARCHAR2(20) DEFAULT 'jerry'; ①
BEGIN
SELECT ename INTO sname FROM emp WHERE empno=7934; ②
dbms_output.put_line(sname);
END;
① 变量初始化时,可以使用DEFAULT关键字对变量进行初始化。
② 使用select…into语句对变量sname赋值,要求查询的结果必须是一行,不能是多行或者没有记录。
声明常量:
常量在声明时赋予初值,并且在运行时不允许重新赋值。使用 CONSTANT关键字声明常量。
eg:声明常量
SQL> DECLARE
2 pi CONSTANT number :=3.14; --圆周率长值 ①
3 r number DEFAULT 3; --圆的半径默认值3
4 area number; --面积。
5 BEGIN
6 area:=pi*r*r; --计算面积
7 dbms_output.put_line(area); --输出圆的面积
8 END;
① 声明常量时使用关键字CONSTANT,常量初值可以使用赋值运算符(:=)赋值,也可以使用DEFAULT关键字赋值。
以下内容不是很理解:
在SQL*Plus中还可以声明Session(会话,也就是一个客户端从连接到退出的过程称为当前用户的会话。)全局级变量,该变量在整个会话过程中均起作用,类似的这种变量称为宿主变量。宿主变量在PL/SQL引用时要用“:变量名”引用。代码演示:宿主常量
SQL> var emp_name varchar(30); ①
SQL> BEGIN
2 SELECT ename INTO :emp_name FROM emp WHERE empno=7499; ②
3 END;
4 /
PL/SQL procedure successfully completed
emp_name
---------
ALLEN
SQL> print emp_name; ③
emp_name
---------
ALLEN
① 可以使用var声明宿主变量。
② PL/SQL中访问宿主变量时要在变量前加“:”。
③ 在SQL*Plus中,使用print可以输出变量中的结果。
PL/SQL数据类型:PL/SQL不但支持oracle的数据类型,还具备自身的数据类型。PL/SQL的数据类型包括标量数据类型,引用数据类型和存储文本、图像、视频、声音等非结构化的大数据类型(LOB数据类型)等
标量数据类型:变量只有一个值,且内部没有分量。标量数据类型包括数字型,字符型,日期型和布尔型。这些类型有的是Oracle SQL中定义的数据类型,有的是PL/SQL自身附加的数据类型。字符型和数字型又有子类型,子类型只与限定的范围有关,比如NUMBER类型可以表示整数,也可以表示小数,而其子类型POSITIVE只表示正整数。
类型 说明
VARCHAR2(长度) 可变长度字符串,Oracle SQL定义的数据类型,在PL/SQL中使用时最长32767字节。在PL/SQL中使用没有默认长度,因此必须指定。
NUMBER(精度,小数) Oracle SQL定义的数据类型,见第二章。
DATE Oracle SQL定义的日期类型,见第二章。
TIMESTAMP Oracle SQL定义的日期类型,见第二章。
CHAR(长度) Oracle SQL定义的日期类型,固定长度字符,最长32767字节,默认长度是1,如果内容不够用空格代替。
LONG Oracle SQL定义的数据类型,变长字符串基本类型,最长32760字节。在Oracle SQL中最长2147483647字节。
BOOLEAN PL/SQL附加的数据类型,逻辑值为TRUE、FALSE、NULL
BINARY_INTEGER PL/SQL附加的数据类型,介于-231和231之间的整数。
PLS_INTEGER PL/SQL附加的数据类型,介于-231和231之间的整数。类似于BINARY_INTEGER,只是PLS_INTEGER值上的运行速度更快。
NATURAL PL/SQL附加的数据类型,BINARY_INTEGER子类型,表示从0开始的自然数。
NATURALN 与NATURAL一样,只是要求NATURALN类型变量值不能为NULL。
POSITIVE PL/SQL附加的数据类型,BINARY_INTEGER子类型,正整数。
POSITIVEN 与POSITIVE一样,只是要求POSITIVE的变量值不能为NULL。
REAL Oracle SQL定义的数据类型,18位精度的浮点数
INT,INTEGER,SMALLINTOracle SQL定义的数据类型,NUMBERDE的子类型,38位精度整数。
SIGNTYPE PL/SQL附加的数据类型,BINARY_INTEGER子类型。值有:1、-1、0。
STRING 与VARCHAR2相同。
属性数据类型:当声明一个 变量 的值是数据库中的 一行或者是数据库中 某列时,可以直接使用属性类型来声明。Oracle中存在两种属性类型:%TYPE和%ROWTYPE。
Ø % ROWTYPE: 引用数据库表中的一行作为数据类型,即RECORD类型(记录类型),是PL/SQL附加的数据类型。表示一条记录,就相当于C#中的一个对象。可以使用“ .”来访问记录中的属性。
代码:
SQL> DECLARE
2 myemp EMP%ROWTYPE; ①
3 BEGIN
4 SELECT * INTO myemp FROM emp WHERE empno=7934; ②
5 dbms_output.put_line(myemp.ename); ③
6 END;
7 /
MILLER
PL/SQL procedure successfully completed
① 声明一个myemp对象,该对象表示EMP表中的一行。
② 从EMP表中查询一条记录放入myemp对象中。
③ 访问该对象的属性可以使用“.”。
Ø %TYPE:引用某个变量或者数据库的列的类型作为某变量的数据类型。
代码:SQL> DECLARE
2 sal emp.sal%TYPE; ①
3 mysal number(4):=3000;
4 totalsal mysal%TYPE; ②
5 BEGIN
6 SELECT SAL INTO sal FROM emp WHERE empno=7934;
7 totalsal:=sal+mysal;
8 dbms_output.put_line(totalsal);
9 END;
10 /
4300
PL/SQL procedure successfully completed
① 定义变量sal为emp表中sal列的类型。
② 定义totalsal是变量mysal的类型。
PL/SQL条件控制和循环控制:
有三种控制结构:顺序结构,条件结构,循环结构
条件控制:PL/SQL中关于条件控制的关键字有IF-THEN、IF-THEN-ELSE、IF-THEN-ELSIF和多分枝条件CASE。
Ø IF-THEN:
该结构先判断一个条件是否为TRUE,条件成立则执行对应的语句块
语法(PL/SQL中条件语法):
IF 条件 THEN
--条件结构体
END IF;
说明:
① 用IF关键字开始,END IF关键字结束,注意END IF后面有一个分号。
② 条件部分可以不使用括号,但是必须以关键字THEN来标识条件结束,如果条件成立,则执行THEN后到对应END IF之间的语句块内容。如果条件不成立,则不执行条件语句块的内容。
③ PL/SQL中关键字THEN到END IF之间的内容是条件结构体内容。
④ 条件可以使用关系运算符和逻辑运算符。
eg:查询JAMES的工资,如果大于900元,则发奖金800元。
代码演示:IF-THEN应用
DECLARE
newSal emp.sal%TYPE;
BEGIN
SELECT sal INTO newSal FROM emp
WHERE ename='JAMES';
IF newSal>900 THEN ①
UPDATE emp SET comm=800 WHERE ename='JAMES';
END IF;
COMMIT ; ②
END;
① 先判断条件,如果条件为TRUE,则执行条件结构体内部的内容。
② 在PL/SQL块中可以使用事务控制语句,该COMMIT同时也能把PL/SQL块外没有提交的数据一并提交,使用时需要注意。
Ø IF-THEN-ELSE:
语法(PL/SQL中条件语法):
IF 条件 THEN
--条件成立结构体
ELSE
--条件不成立结构体
END IF;
eg:查询JAMES的工资,如果大于900元,则发奖金800元,否则发奖金400元。
代码演示:IF-THEN-ELSE应用
DECLARE
newSal emp.sal%TYPE;
BEGIN
SELECT sal INTO newSal FROM emp
WHERE ename='JAMES';
IF newSal>900 THEN
UPDATE emp
SET comm=800
WHERE ename='JAMES';
ELSE
UPDATE emp
SET comm=400
WHERE ename='JAMES';
END IF;
END;
Ø IF-THEN-ELSIF:
语法(PL/SQL中多分枝条件语法):
IF 条件1 THEN
--条件1成立结构体
ELSIF 条件2 THEN
--条件2成立结构体
ELSE
--以上条件都不成立结构体
END IF;
eg:查询JAMES的工资,如果大于1500元,则发放奖金1000元,如果工资大于900元,则发奖金800元,否则发奖金400元。
代码:
DECLARE
newSal emp.sal%TYPE;
BEGIN
SELECT sal INTO newSal FROM emp
WHERE ename='JAMES';
IF newSal>1500 THEN
UPDATE emp
SET comm=1000
WHERE ename='JAMES';
ELSIF newSal>900 THEN
UPDATE emp
SET comm=800
WHERE ename='JAMES';
ELSE
UPDATE emp
SET comm=400
WHERE ename='JAMES';
END IF;
END;
Ø CASE
CASE是一种 选择结构的控制语句,可以根据条件从多个执行分支中选择相应的执行动作。也可以作为表达式使用,返回一个值。
语法:
CASE [selector]
WHEN 表达式1 THEN 语句序列1;
WHEN 表达式2 THEN 语句序列2;
WHEN 表达式3 THEN 语句序列3;
……
[ELSE 语句序列N];
END CASE;
语法解析:
如果 存在选择器selector,选择器selector与WHEN后面的表达式匹配,匹配成功就执行THEN后面的语句。如果所有表达式都与selector不匹配,则执行ELSE后面的语句。
eg:输入一个字母A、B、C分别输出对应的级别信息。
代码演示:CASE中存在selector,不返回值
DECLARE
v_grade CHAR(1):=UPPER('&p_grade'); ①
BEGIN
CASE v_grade ②
WHEN 'A' THEN
dbms_output.put_line('Excellent');
WHEN 'B' THEN
dbms_output.put_line('Very Good');
WHEN 'C' THEN
dbms_output.put_line('Good');
ELSE
dbms_output.put_line('No such grade');
END CASE;
END;
代码解析:
① & grade表示在运行时由键盘输入字符串到grade变量中。
② v_grade分别于WHEN后面的值匹配,如果成功就执行WHEN后的程序序列。
CASE语句还可以作为表达式使用,返回一个值。
代码演示:CASE中存在selector,作为表达式使用
DECLARE
v_grade CHAR(1):=UPPER('&grade');
p_grade VARCHAR(20) ;
BEGIN
p_grade := ①
CASE v_grade
WHEN 'A' THEN
'Excellent'
WHEN 'B' THEN
'Very Good'
WHEN 'C' THEN
'Good'
ELSE
'No such grade'
END;
dbms_output.put_line('Grade:' ||v_grade||',the result is '||p_grade);
END;
代码解析:
① CASE语句可以返回一个结果给变量p_grade
PL/SQL还提供了 搜索CASE语句。也就是说, 不使用CASE中的选择器,直接在WHEN后面判断条件,第一个条件为真时,执行对应THEN后面的语句序列。
代码演示:搜索CASE
DECLARE
v_grade CHAR(1):=UPPER('&grade');
p_grade VARCHAR(20) ;
BEGIN
p_grade :=
CASE
WHEN v_grade='A' THEN
'Excellent'
WHEN v_grade='B' THEN
'Very Good'
WHEN v_grade='C' THEN
'Good'
ELSE
'No such grade'
END;
dbms_output.put_line('Grade:' ||v_grade||',the result is '||p_grade);
END;
循环结构:
PL/SQL提供了丰富的循环结构来重复执行一些列语句。Oracle提供的循环类型有:无条件循环LOOP-END LOOP语句,WHILE循环语句,FOR循环语句,这三类循环中用 EXIT强制结束循环,相当于C#循环中的break。
Ø LOOP循环:最简单的循环,也称为无限循环,LOOP和END LOOP是关键字。
语法:
LOOP
--循环体
END LOOP;
说明:
1.循环体在LOOP和END LOOP之间,在每个LOOP循环体中,首先执行循环体中的语句序列,执行完后再重新开始执行。
2.在LOOP循环中可以使用EXIT或者[EXIT WHEN 条件]的形式终止循环。否则该循环就是死循环。
eg:执行1+2+3+…+100的值
代码演示:
DECLARE
counter number(3):=0;
sumResult number:=0;
BEGIN
LOOP
counter := counter+1;
sumResult := sumResult+counter;
IF counter>=100 THEN ①
EXIT;
END IF;
--EXIT WHEN counter>=100; ②
END LOOP;
dbms_output.put_line('result is :'||to_char(sumResult));
END;
代码解析:
① LOOP循环中可以使用IF结构嵌套EXIT关键字退出循环
② 注释行,该行可以代替①中的循环结构,WHEN后面的条件成立时跳出循环。
Ø WHILE循环:先判断条件,条件成立再执行循环体。
语法:
WHILE 条件 LOOP
--循环体
END LOOP;
代码演示:
DECLARE
counter number(3):=0;
sumResult number:=0;
BEGIN
WHILE counter<100 LOOP
counter := counter+1;
sumResult := sumResult+counter;
END LOOP;
dbms_output.put_line('result is :'||sumResult);
END;
Ø FOR循环:需要预先确定的循环次数,可 通过给循环变量指定下限和上限来确定循环运行的次数,然后 循环变量在每次循环中递增(或者递减)。
语法:
FOR 循环变量 IN [REVERSE] 循环下限..循环上限 LOOP
--循环体
END LOOP;
语法解析:
循环变量:该变量的值每次循环根据上下限的REVERSE关键字进行加1或者减1。
REVERSE:指明循环从上限向下限依次循环。
代码演示:
DECLARE
counter number(3):=0;
sumResult number:=0;
BEGIN
FOR counter IN 1..100 LOOP
sumResult := sumResult+counter;
END LOOP;
dbms_output.put_line('result is :'||sumResult);
END;
顺序结构:在程序顺序结构中有两个特殊的语句。GOTO和NULL
Ø GOTO语句:GOTO语句将 无条件的跳转到标签指定的语句去执行。 标签是用双尖括号括起来的标示符,在PL/SQL块中必须 具有唯一的名称,标签后必须紧跟 可执行语句或者 PL/SQL块。GOTO 不能跳转到 IF语句、CASE语句、LOOP语句、或者子块中。
Ø NULL语句:NULL语句 什么都不做, 只是将控制权转到下一行语句。NULL语句是 可执行语句(这句的意思是,goto可以通过标签跳转到NULL)。NULL语句在 IF或者其他语句语法要求 至少需要一条可执行语句,但又不需要具体操作的地方。比如GOTO的目标地方不需要执行任何语句时。
eg:GOGO 和 NULL
代码演示:
DECLARE
sumsal emp.sal%TYPE;
BEGIN
SELECT SUM(sal) INTO sumsal FROM EMP;
IF sumsal>20000 THEN
GOTO first_label; ①
ELSE
GOTO second_label; ②
END IF;
<<first_label>> ③
dbms_output.put_line('ABOVE 20000:' || sumsal);
<<second_label>> ④
NULL;
END;
代码解析:
① 跳转到程序first_label位置,就是②的位置,first_label是一个标签,用两个尖括号包含。
② 无条件跳转到sedond_label位置,就是④的位置。④处不执行任何内容,因此是一个NULL语句。
在PL/SQL中,各种循环之间可以相互嵌套。
PL/SQL中动态执行SQL语句:
在PL/SQL程序开发中,可以使用 DML语句和 事务控制语句,但是还有很多语句(比如 DDL语句) 不能直接在PL/SQL中执行。这些语句可以使用 动态SQL来实现。
PL/SQL块先编译然后再执行,动态SQL语句在编译时不能确定,只有在程序执行时把SQL语句作为字符串的形式由动态SQL命令来执行。在 编译阶段SQL语句作为字符串存在,程序 不会对字符串中的内容进行 编译,在 运行阶段再对字符串中的SQL语句进行 编译和执行。
语法:
EXECUTE IMMEDIATE 动态语句字符串
[INTO 变量列表]
[USING 参数列表]
语法解析:
如果动态语句是SELECT语句,可以把查询的结果保存到INTO后面的变量中。如果动态语句中存在参数, USING为语句中的参数传值。
动态SQL中的参数格式是:[:参数名],参数在运行时需要 使用USING传值。
eg:动态SQL
代码演示:
DECLARE
sql_stmt VARCHAR2(200); --动态SQL语句
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 90;
dept_name VARCHAR2(14) := 'PERSONNEL';
location VARCHAR2(13) := 'DALLAS';
emp_rec emp%ROWTYPE;
BEGIN
--无子句的execute immediate
EXECUTE IMMEDIATE 'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)'; ①
----using子句的execute immediatesql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; ②
----into子句的execute immediatesql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; ③
----returning into子句的execute immediatesql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; ④
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
USING dept_id; ⑤
END;
代码解析:
① 动态执行一个完整的SQL语句。
② SQL语句中存在3个参数分别标识为:[:1、:2、:3],因此需要用USING关键字对三个参数分别赋值。
③ 对动态查询语句可以使用INTO子句把查询的结果保存到一个变量中,要求该结果只能是单行。
④ 在Oracle的insert,update,delete语句都可以使用RETURNING子句把操作影响的行中的数据返回, 对SQL语句中存在RETURNING子句时,在动态执行时可以使用RETURNING INTO来接收。
⑤ 动态执行参数中可以是: [:数字]也可以是[:字符串]。
PL/SQL的异常处理:
在程序运行时出现的错误,称为异常。 发生异常后,语句将停止执行,PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。异常处理机制简化了代码中的错误检测。PL/SQL中任何异常出现时,每一个异常都对应一个异常码和异常信息。
预定义异常:
为了Oracle开发和维护的方便,在Oracle异常中,为常见的异常码定义了对应的异常名称,称为预定义异常,常见的预定义异常有:
异常名称 异常码 描述
DUP_VAL_ON_INDEX ORA-00001 试图向唯一索引列插入重复值
INVALID_CURSOR ORA-01001 试图进行非法游标操作。
INVALID_NUMBER ORA-01722 试图将字符串转换为数字
NO_DATA_FOUND ORA-01403 SELECT INTO语句中没有返回任何记录。
TOO_MANY_ROWS ORA-01422 SELECT INTO语句中返回多于1条记录。
ZERO_DIVIDE ORA-01476 试图用0作为除数。
CURSOR_ALREADY_OPEN ORA-06511 试图打开一个已经打开的游标
PL/SQL中用EXCEPTION关键字开始异常处理 (异常处理在end之前):
语法:
BEGIN
--可执行部分
EXCEPTION -- 异常处理开始
WHEN 异常名1 THEN
--对应异常处理
WHEN 异常名2 THEN
--对应异常处理
……
WHEN OTHERS THEN
--其他异常处理
END;
语法解析:
异常发生时,进入异常处理部分,具体的异常与若干个WHEN子句中指明的异常名匹配,匹配成功就进入对应的异常处理部分,如果对应不成功,则进入OTHERS进行处理。
eg:异常处理
代码:
SQL> DECLARE
2 newSal emp.sal % TYPE;
3 BEGIN
4 SELECT sal INTO newSal FROM emp;
5 EXCEPTION
6 WHEN TOO_MANY_ROWS THEN
7 dbms_output.put_line('返回的记录太多了');
8 WHEN OTHERS THEN
9 dbms_output.put_line('未知异常');
10 END;
11 /
返回的记录太多了
PL/SQL procedure successfully completed
自定义异常:
除了预定义异常外,用户还可以在开发中自定义异常,自定义异常可以让用户采用与PL/SQL引擎处理错误相同的方式进行处理,用户自定义异常的两个关键点:
Ø 异常定义:在PL/SQL块的声明部分采用EXCEPTION关键字声明异常,定义方法与定义变量相同。比如声明一个myexception异常方法是:
myexception EXCEPTION;
Ø 异常引发:在程序可执行区域,使用 RAISE关键字进行引发。比如引发myexception方法是:
RAISE myexception;
eg:自定义异常
代码:SQL> DECLARE
2 sal emp.sal%TYPE;
3 myexp EXCEPTION; ①
4 BEGIN
5 SELECT sal INTO sal FROM emp WHERE ename='JAMES';
6 IF sal<5000 THEN
7 RAISE myexp; ②
8 END IF;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 dbms_output.put_line('NO RECORDSET FIND!');
12 WHEN MYEXP THEN ③
13 dbms_output.put_line('SAL IS TO LESS!');
14 END;
15 /
SAL IS TO LESS!
PL/SQL procedure successfully completed
说明:
① 用EXCEPTION定义一个异常变量myexp
② 在一定条件下用RAISE引发异常myexp
③ 在异常处理部分,捕获异常,如果不处理异常,该异常就抛给程序执行者。
引发应用程序异常 (不是很理解):
在Oracle开发中,遇到的系统异常都有对应的异常码,在应用系统开发中,用户自定义的异常也可以指定一个异常码和异常信息,Oracle系统为用户预留了自定义异常码,其范围介于-20000到-20999之间的负整数。
语法:
RAISE_APPLICATION_ERROR(异常码,异常信息)
eg:引发系统异常
代码:
SQL> DECLARE
2 sal emp.sal%TYPE;
3 myexp EXCEPTION;
4 BEGIN
5 SELECT sal INTO sal FROM emp WHERE ename='JAMES';
6 IF sal<5000 THEN
7 RAISE myexp;
8 END IF;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 dbms_output.put_line('NO RECORDSET FIND!');
12 WHEN MYEXP THEN
13 RAISE_APPLICATION_ERROR(-20001,'SAL IS TO LESS!'); ①
14 END;
15 /
ORA-20001: SAL IS TO LESS! ②
ORA-06512: 在 line 14
说明:
① 引发应用系统异常,指明异常码和异常信息。
② 在控制台上显示异常码和异常信息。
如果要处理未命名的内部异常,必须使用OTHERS异常处理器。也可以利用PRAGMA EXCEPTION_INIT把一个异常码与异常名绑定。
PRAGMA由编译器控制,PRAGMA在编译时处理,而不是在运行时处理。EXCEPTION_INIT告诉编译器将异常名与ORACLE错误码绑定起来,这样可以通过异常名引用任意的内部异常,并且可以通过异常名为异常编写适当的异常处理器。
语法:
PRAGMA EXCEPTION_INIT(异常名,异常码)
这里的异常码可以是用户自定义的异常码,也可以是Oracle系统的异常码。
eg:PRAGMA EXCEPTION_INIT异常
代码:
<<outterseg>>
DECLARE
null_salary EXCEPTION;
PRAGMA EXCEPTION_INIT(null_salary, -20101); ①
BEGIN
<<innerStart>> ②
DECLARE
curr_comm NUMBER;
BEGIN
SELECT comm INTO curr_comm FROM emp WHERE empno = &empno;
IF curr_comm IS NULL THEN
RAISE_APPLICATION_ERROR(-20101, 'Salary is missing'); ③
ELSE
dbms_output.put_line('有津贴');
END IF;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('没有发现行');
WHEN null_salary THEN
dbms_output.put_line('津贴未知'); ④
WHEN OTHERS THEN
dbms_output.put_line('未知异常');
END;
说明:
① 把异常名称null_salary与异常码-20101关联,该语句由于是预编译语句,必须放在声明部分。也就是说-20101的异常名称就是null_salary。
② 嵌套PL/SQL语句块
③ 在内部PL/SQL语句块中引发应用系统异常-20101。
④ 在外部的PL/SQL语句块中就可以用异常名null_salary进行捕获。
总结:
Ø PL/SQL是一种块结构的语言,它将一组语句放在一个块中,一次性发送给服务器,PL/SQL引擎把接收到PL/SQL语句块中的内容进行分析,把其中的过程控制语句由PL/SQL引擎自身去执行,把PL/SQL语句块中的SQL语句交给服务器的SQL语句执行器执行。
Ø PL/SQL的数据类型包括标量数据类型,引用数据类型和存储文本、图像、视频、声音等非结构化的大数据类型(LOB数据类型)等。
Ø Oracle中存在两种属性类型:%TYPE和%ROWTYPE。
Ø PL/SQL程序可通过控制结构来控制命令执行的流程。PL/SQL中提供三种程序结构:顺序结构、条件结构和循环结构。
Ø 在PL/SQL程序开发中,可以使用DML语句和事务控制语句,还可以动态执行SQL语句,动态执行SQL语句的命令是:EXECUTE IMMEDIATE。
Ø 在程序运行时出现的错误,称为异常。发生异常后,语句将停止执行,PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。PL/SQL中任何异常出现时,每一个异常都对应一个异常码和异常信息。
本文出自《oracle经典教程》,原链接:http://blog.csdn.net/self_realian/article/details/72724307#t9