PL\SQL基础
PL\SQL(Procedural Language/SQL)结合了结构化查询和Oracle自身的过程控制,支持更多数据类型,拥有自身的变量声明、赋值语句,而且还有条件、循环等流程
控制语句。
PL/SQL块发送给服务器后,先被编译然后执行,对于有名称的PL/SQL块(如子程序)可以单独编译,永久的存储在数据库中,随时准备执行。PL/SQL的优点还
有:
1、支持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块的语法
[DECLARE --declaration statements] ①
BEGIN --executable statements ②
[EXCEPTION --exception statements] ③
END;
PL/SQL中的特殊符号说明:
类型 符号 说明
赋值运算符
:= Java和C#中都是等号,PL/SQL的赋值是:=
特殊字符
|| 字符串连接操作符。
-- PL/SQL中的单行注释。
/*,*/ PL/SQL中的多行注释,多行注释不能嵌套。
<<,>> 标签分隔符。只为了标识程序特殊位置。
.. 范围操作符,比如:1..5 标识从1到5
算术运算符
+,-,*,/ 基本算术运算符。
** 求幂操作,比如:3**2=9
关系运算符 >,<,>=,<=,=
基本关系运算符,
=表示相等关系,不是赋值。
<>,!= 不等关系。
逻辑运算符
AND,OR,NOT 逻辑运算符。
声明变量
SQL> DECLARE
2 sname VARCHAR2(20) :='jerry'; ①
3 BEGIN
4 sname:=sname||' and tom'; ②
5 dbms_output.put_line(sname); ③
6 END;
7 /jerry
dbms_output.put_line是输出语句,可以把一个变量的值输出,在SQL*Plus中输出 数据时,可能没有结果显示,可以使用命令:set serveroutput on设置输出到SQL*Plus控
制台上。
变量赋值
SQL> DECLARE
2 sname VARCHAR2(20) DEFAULT 'jerry'; ①
3 BEGIN
4 SELECT ename INTO sname FROM emp WHERE empno=7934; ②
5 dbms_output.put_line(sname);
6 END;
7 /
MILLER
PL/SQL procedure successfully completed
代码解析:
① 变量初始化时,可以使用DEFAULT关键字对变量进行初始化。
② 使用select…into语句对变量sname赋值,要求查询的结果必须是一行,不能是多 行或者没有记录。
使用CONSTANT关键字声明常量。
在SQL*Plus中还可以声明Session(会话,也就是一个客户端从连接到退出的过程称为当前用户的会话。)全局级变量,该变量在整个会话过程中均起作用,类
似的这种变量称为宿主变量。宿主变量在PL/SQL引用时要用“:变量名”引用。 使用var声明宿主变量。
代码演示:
宿主常量
SQL> var emp_name varchar(30); ①
SQL> BEGIN
2 SELECT ename INTO :emp_name FROM emp WHERE empno=7499; ②
3 END;
4 /
PL/SQL数据类型
标量数据类型: 标量数据类型的变量只有一个值,且内部没有分量。标量数据类型包括数字型,字符型,日期型和布尔型。这些类型有的是Oracle SQL中定义
的数据类型,有的是PL/SQL自身附加的数据类型。
属性数据类型: 当声明一个变量的值是数据库中的一行或者是数据库中某列时,可以直接使用属性类型来声明。
% 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
%TYPE 引用某个变量或者数据库的列的类型作为某变量的数据类型。
代码演示:%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流程控制
与C#一样也有三种控制结构 - 顺序结构, 条件控制和循环控制。
PL/SQL中关于条件控制的关键字有IF-THEN、IF-THEN-ELSE、IF-THEN-ELSIF和多分枝条件CASE。
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;
Oracle提供的循环类型有:
1. 无条件循环LOOP-END LOOP语句
2. WHILE循环语句
3. FOR循环语句
在上面的三类循环中EXIT用来强制结束循环,相当于C#循环中的break。
顺序结构
在程序顺序结构中有两个特殊的语句。GOTO和NULL
NULL语句什么都不做,只是将控制权转到下一行语句。NULL语句是可执行语句。NULL语句在IF或者其他语句语法要求至少需要一条可执行语句,但又不需
要具体操作的地方。比如GOTO的目标地方不需要执行任何语句时。
动态SQL
EXECUTE IMMEDIATE 动态语句字符串
[INTO 变量列表]
[USING 参数列表]
语法解析: 如果动态语句是SELECT语句,可以把查询的结果保存到INTO后面的变量中。如果动态语句中存在参数,USING为语句中的参数传值。
动态SQL中的参数格式是:[:参数名],参数在运行时需要使用USING传值。
PL/SQL的异常处理
发生异常后,语句将停止执行,PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。异常处理机制简化了代码中的错误检测。PL/SQL中任何异常出现时
,每一个异常都对应一个异常码和异常信息。
语法格式:异常处理
BEGIN
--可执行部分
EXCEPTION
-- 异常处理开始
WHEN 异常名1 THEN
--对应异常处理
WHEN 异常名2 THEN
--对应异常处理
……
WHEN OTHERS THEN
--其他异常处理
END;
自定义异常
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
引发应用程序异常
在Oracle开发中,遇到的系统异常都有对应的异常码,在应用系统开发中,用户自定义的异常也可以指定一个异常码和异常信息,Oracle系统为用户预留了
自定义异常码,其范围介于-20000到-20999之间的负整数。引发应用程序异常的语法是:
RAISE_APPLICATION_ERROR(异常码,异常信息)
代码演示:引发应用系统异常
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的语法是:
PRAGMA EXCEPTION_INIT(异常名,异常码)
这里的异常码可以是用户自定义的异常码,也可以是Oracle系统的异常码。
PL\SQL(Procedural Language/SQL)结合了结构化查询和Oracle自身的过程控制,支持更多数据类型,拥有自身的变量声明、赋值语句,而且还有条件、循环等流程
控制语句。
PL/SQL块发送给服务器后,先被编译然后执行,对于有名称的PL/SQL块(如子程序)可以单独编译,永久的存储在数据库中,随时准备执行。PL/SQL的优点还
有:
1、支持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块的语法
[DECLARE --declaration statements] ①
BEGIN --executable statements ②
[EXCEPTION --exception statements] ③
END;
PL/SQL中的特殊符号说明:
类型 符号 说明
赋值运算符
:= Java和C#中都是等号,PL/SQL的赋值是:=
特殊字符
|| 字符串连接操作符。
-- PL/SQL中的单行注释。
/*,*/ PL/SQL中的多行注释,多行注释不能嵌套。
<<,>> 标签分隔符。只为了标识程序特殊位置。
.. 范围操作符,比如:1..5 标识从1到5
算术运算符
+,-,*,/ 基本算术运算符。
** 求幂操作,比如:3**2=9
关系运算符 >,<,>=,<=,=
基本关系运算符,
=表示相等关系,不是赋值。
<>,!= 不等关系。
逻辑运算符
AND,OR,NOT 逻辑运算符。
声明变量
SQL> DECLARE
2 sname VARCHAR2(20) :='jerry'; ①
3 BEGIN
4 sname:=sname||' and tom'; ②
5 dbms_output.put_line(sname); ③
6 END;
7 /jerry
dbms_output.put_line是输出语句,可以把一个变量的值输出,在SQL*Plus中输出 数据时,可能没有结果显示,可以使用命令:set serveroutput on设置输出到SQL*Plus控
制台上。
变量赋值
SQL> DECLARE
2 sname VARCHAR2(20) DEFAULT 'jerry'; ①
3 BEGIN
4 SELECT ename INTO sname FROM emp WHERE empno=7934; ②
5 dbms_output.put_line(sname);
6 END;
7 /
MILLER
PL/SQL procedure successfully completed
代码解析:
① 变量初始化时,可以使用DEFAULT关键字对变量进行初始化。
② 使用select…into语句对变量sname赋值,要求查询的结果必须是一行,不能是多 行或者没有记录。
使用CONSTANT关键字声明常量。
在SQL*Plus中还可以声明Session(会话,也就是一个客户端从连接到退出的过程称为当前用户的会话。)全局级变量,该变量在整个会话过程中均起作用,类
似的这种变量称为宿主变量。宿主变量在PL/SQL引用时要用“:变量名”引用。 使用var声明宿主变量。
代码演示:
宿主常量
SQL> var emp_name varchar(30); ①
SQL> BEGIN
2 SELECT ename INTO :emp_name FROM emp WHERE empno=7499; ②
3 END;
4 /
PL/SQL数据类型
标量数据类型: 标量数据类型的变量只有一个值,且内部没有分量。标量数据类型包括数字型,字符型,日期型和布尔型。这些类型有的是Oracle SQL中定义
的数据类型,有的是PL/SQL自身附加的数据类型。
属性数据类型: 当声明一个变量的值是数据库中的一行或者是数据库中某列时,可以直接使用属性类型来声明。
% 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
%TYPE 引用某个变量或者数据库的列的类型作为某变量的数据类型。
代码演示:%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流程控制
与C#一样也有三种控制结构 - 顺序结构, 条件控制和循环控制。
PL/SQL中关于条件控制的关键字有IF-THEN、IF-THEN-ELSE、IF-THEN-ELSIF和多分枝条件CASE。
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;
Oracle提供的循环类型有:
1. 无条件循环LOOP-END LOOP语句
2. WHILE循环语句
3. FOR循环语句
在上面的三类循环中EXIT用来强制结束循环,相当于C#循环中的break。
顺序结构
在程序顺序结构中有两个特殊的语句。GOTO和NULL
NULL语句什么都不做,只是将控制权转到下一行语句。NULL语句是可执行语句。NULL语句在IF或者其他语句语法要求至少需要一条可执行语句,但又不需
要具体操作的地方。比如GOTO的目标地方不需要执行任何语句时。
动态SQL
EXECUTE IMMEDIATE 动态语句字符串
[INTO 变量列表]
[USING 参数列表]
语法解析: 如果动态语句是SELECT语句,可以把查询的结果保存到INTO后面的变量中。如果动态语句中存在参数,USING为语句中的参数传值。
动态SQL中的参数格式是:[:参数名],参数在运行时需要使用USING传值。
PL/SQL的异常处理
发生异常后,语句将停止执行,PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。异常处理机制简化了代码中的错误检测。PL/SQL中任何异常出现时
,每一个异常都对应一个异常码和异常信息。
语法格式:异常处理
BEGIN
--可执行部分
EXCEPTION
-- 异常处理开始
WHEN 异常名1 THEN
--对应异常处理
WHEN 异常名2 THEN
--对应异常处理
……
WHEN OTHERS THEN
--其他异常处理
END;
自定义异常
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
引发应用程序异常
在Oracle开发中,遇到的系统异常都有对应的异常码,在应用系统开发中,用户自定义的异常也可以指定一个异常码和异常信息,Oracle系统为用户预留了
自定义异常码,其范围介于-20000到-20999之间的负整数。引发应用程序异常的语法是:
RAISE_APPLICATION_ERROR(异常码,异常信息)
代码演示:引发应用系统异常
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的语法是:
PRAGMA EXCEPTION_INIT(异常名,异常码)
这里的异常码可以是用户自定义的异常码,也可以是Oracle系统的异常码。