文章目录
PLSQL块
DECLARE
-- 声明部分(可选)
BEGIN
-- 执行部分(必选)
EXCEPTION
-- 异常处理部分(可选)
END;
声明部分:声明程序中用到的所有变量、常量、游标,也可以写注释来对声明内容或程序整体做解释说明。
执行部分:程序正常运行,最终以关键字END结束;若程序运行过程中出现异常,则会以报错或异常处理结束。关键字BEGIN和END成对出现。
异常处理部分:关键字EXCEPTION开始,到该关键字下所有代码执行完毕结束。若程序运行有异常,异常处理部分结束则整个PL/SQL程序块结束;若程序无异常,则异常处理部分不会执行,PL/SQL以END结束。
注意
每条语句必须以英文分号结束,单行或多行书写皆可
块可以嵌套使用
多个PLSQL块之间需要用 / 才能一块执行
1 PLSQL中的执行部分
1)DBMS_OUTPUT程序包
包含一系列相关程序,如下:
-
PUT_LINE() 常用
打印一行内容,并换行
-
PUT()
输出一行内容到缓存但不打印,多次执行会再一行内缓存
-
NEW_LINE
换行 执行过该命令之后可以将缓存中的所有内容打印并换行
-
例子
BEGIN DBMS_OUTPUT.PUT_LINE('HELLO ORACLE!'); DBMS_OUTPUT.PUT_LINE(1+2+3+4+5+6); DBMS_OUTPUT.PUT_LINE(); DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')||'备份EMP完成'); END; / -- 需要有/分开不同的块,才能一起执行 ************************************* BEGIN DBMS_OUTPUT.PUT('今天'); DBMS_OUTPUT.PUT('天气'); DBMS_OUTPUT.PUT('真好'); DBMS_OUTPUT.NEW_LINE; END;
2)DML操作
在PL/SQL程序中,DML操作可以直接运行
DML操作需要在程序中运行COMMIT命令才能自动提交
-
例子
BEGIN -- 1)清理EMP表中10号部门数据 DELETE FROM EMP WHERE DEPTNO = 10; COMMIT; -- 2)从备份表中把10号部门数据恢复 INSERT INTO EMP SELECT * FROM EMP_0418 WHERE DEPTNO = 10; COMMIT; -- 3)调整30号部门的薪资为原来的两倍 UPDATE EMP SET SAL = SAL*2 WHERE DEPTNO = 30; COMMIT; END;
3)DDL
在PL/SQL中,DDL不能直接操作,需要借助EXECUTE命令来实现。
EXECUTE IMMEDIATE ‘SQL语句’ ; 其中SQL语句可以是DML语句也可以是DDL语句
-
例子
BEGIN -- 1)备份EMP表 EXECUTE IMMEDIATE 'CREATE TABLE EMP_041801 AS SELECT * FROM EMP'; DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')||'备份EMP完成'); -- 2)清空EMP表 EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP'; -- 3)从备份表中恢复EMP表数据 EXECUTE IMMEDIATE 'INSERT INTO EMP SELECT * FROM EMP_041801'; COMMIT; END;
-
注意
PL/SQL程序运行时,ORACLE会首先检查程序的合理性,如若有语法等问题,程序不予执行。
写在EXECUTE命令中的SQL,会避开最初的合法性检查,等EXECUTE命令真正执行的时候,会检查EXECUTE命令携带的SQL的合法性
4)注释
单行注释:由两个连接字符 – 开始,后面紧跟着注释内容。换行后脱离注释范围。
多行注释:由/开头,由/结尾,两者之间都属于注释范围。注意:多行注释嵌套使用时,开头的/会变成*,结尾的/会变成*,如果是解除多层嵌套的注释,需要先解除外层注释,再解除里层注释。
2 PLSQL中的声明部分
因为声明部分提供了一项非常中要的功能,就是可以声明变量。所有变量的声明都需要在声明部分进行。
无论是声明变量、常量还是游标等各种声明内容,
声明基本都是由两部分组成:名称和类型,类似于建表语句中声明每一个字段,
每个量要有自己的名字(也称标识符),同时还要有自己的属性(含数据类型与精度)。
1)标识符
标识符组成元素可以为A-Z字母或0-9数字
不建议使用除了_之外的其他特殊字符
标识符用双引号引起后,标识符可随意使用特殊字符,且字母会区分大小写,使用这些字符时也必须加上双引号
标识符必须以字母开头
不能是SQL保留字
不要设置和字段名或表名完全相同的标识符
-
例子
DECLARE "A_0" NUMBER; BEGIN "A_0" := 10; DBMS_OUTPUT.PUT_LINE("A_0"); DBMS_OUTPUT.PUT_LINE("a_0"); -- 报错 必须使用"A_0" END;
2)数据类型(标量变量:存放单个数值的变量)
- 字符型
VARCHAR2(n):可变长度字符串,无默认精度,使用时需指定最大长度。PL/SQL中最长可设为32767个字节。数据库中最大4000
CHAR(n):固定长度字符串,无默认精度,使用时指定最大长度。PL/SQL中最长可设为32767个字节。数据库中最大2000
LONG:可变长度字符串,不需要指定精度。PL/SQL中最长可设为32767个字节。(最长2G)
LONG RAW:可变长度字符串(最长2G)
不适用LONG的原因:
1)字段中根本不必存储这么长的数据,VARCHAR2(n)和char(n)类型即可满足日常需要。
2)一个表中仅能设置一个LONG类型的列
3)LONG类型字段不能作为索引列或约束列
4)LONG类型字段在某些数据库工具中处理受限。
5)LONG类型字段只有在简单查询中可以运作,不能进行WHERE、GROUP BY 、HAVING、ORDER BY 、DISTINCT等
- 数值型
数值型有NUMBER、INT、DECIMAL、BINARY_INTEGER、PLS_INTEGER等等,实际上除了NUMBER类型,其他数值类型都是在NUMBER类型上演化得出的特殊情况,我们称之为“子类型”。
NUMBER(p,s)中的p参数表示数值的整体长度,s参数表示数值的小数位长度,其中整体长度中不含小数点。NUMBER类型的默认长度是38,即p=38。
INT/PLS_INTEGER等表示在数值中只有整数位,没有小数位,即等同于NUMBER(p,0)
其实s参数还有s>=p和s<0的情况,但是没什么实际意义,不必深究。
- 日期型
日期型常用的就只有DATE类型,它的默认长度是7个字节,分别存放世纪、年、月、日、时、分和秒。
除了DATE之外还有个TIMESTAMP类型,它的内容中还包括时区和毫秒。
- 布尔型
布尔类型只有一种——BOOLEAN类型,这种类型不用在表中,而是用在PL/SQL中的过程控制中,其内容可以是TRUE、FALSE、NULL中的一种
3)特殊的数据类型(复合变量:用复合类型来定义变量)
- %TYPE类型 (效仿字段)
使用%TYPE类型可以在声明变量名称之后,再指定一个字段,声明该变量的数据类型与指定字段的数据类型一致。
DECLARE
V_ENAME EMP.ENAME%TYPE; -- V_ENAME 的数据类型和EMP.ENAME一样
BEGIN
V_ENAME := 'SMITH';
DELETE FROM EMP WHERE ENAME = V_ENAME;
DBMS_OUTPUT.PUT_LINE(V_ENAME);
END;
%type优势
1 用户不必特意查看表中字段的字段属性,就能确保变量能够存储该字段中的数据;
2 表中的字段属性发生变化 PL/SQL程序中对应的变量不必做任何改动
- RECORD类型
记录类型,该类型可以存储由多个列组成的一行数据
在声明记录类型的变量之前,需要先定义一个记录类型,再声明变量。
定义记录类型,并且指定NOT NULL约束和初始值
语法
-- 使用语法 方法
DECLARE
TYPE RECORD_TYPE IS RECORD -- 定义一个记录类型 起名字
(
VAR_MEMBER1 DATA_TYPE,
VAR_MEMBER2 DATA_TYPE,
.....
VAR_MEMBERN DATA_TYPE);
VAR_NAME RECORD_TYPE; -- 声明一个记录类型的变量
BEGIN
END;
例子
DECLARE
TYPE A IS RECORD( --定义一个记录类型 名字是 A
V_EMPNO NUMBER(4),
V_ENAME VARCHAR2(100) NOT NULL :='SMITH',
V_JOB EMP.JOB%TYPE);
V_EMP A; -- 声明一个变量 类型是A A是一个记录类型
BEGIN
SELECT EMPNO,ENAME,JOB INTO V_EMP FROM EMP WHERE EMPNO = 7369;
-- DBMS_OUTPUT.PUT_LINE(V_EMP); -- 报错 单列的 V_EMP是多列的 所以下面使用拼接
DBMS_OUTPUT.PUT_LINE(V_EMP.V_EMPNO||V_EMP.V_ENAME||V_EMP.V_JOB);
END;
- %ROWTYPE类型 (效仿表)
%ROWTYPE类型与RECORD类型一样本身没有确切的列数和属性,但是%ROWTYPE类型不需要单独定义,直接使用即可。
语法
DECLARE
V_NAME TB_NAME%ROWTYPE; -- 效仿表结构设置变量的数据类型
BEGIN
END;
例子
DECLARE
V_EMP EMP%ROWTYPE; -- 效仿EMP表设置变量的数据类型
BEGIN
SELECT * INTO V_EMP FROM WHERE EMPNO = 7369;
DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO||V_EMP.ENAME||V_EMP.JOB||V_EMP.SAL);
END;
参照变量:存放数值指针的变量,比如游标变量和对象变量。
4)定义常量和变量
变量与常量都必须在声明部分完成声明,而后在执行部分中,变量与常量都只能被使用,但不能被定义。
1.定义变量
变量是指其值在程序运行过程中可以改变的数据存储结构,必须元素有变量名称和数据类型,可选项有初始值。
2.定义常量
常量是指其值在程序运行过程中不可改变的数据存储结构,必须元素有常量名、关键词constant、数据类型和常量值。
语法
DECLARE
VAR_NAME DATA_TYPE := 'VALUE'; -- 定义一个变量并赋予初始值 可以没有
CON_NAME CONSTANT DATA_TYPE := 'VALUE'; -- 定义一个常量并赋予常量值 必须有值
BEGIN
END;
5)变量赋值
1 通过 := 赋值,该方法可用在PL/SQL块中的任何部分,另外还可以设置&符号手动获取变量值 (如果是字符型的话加上引号,数值型的话可以不用加引号)
-- 方法1
DECLARE
V_DEPTNO NUMBER :=&请指定部门编号; -- 数值型
V_ENAME VARCHAR2(20) := '&请指定员工姓名'; -- 字符型
BEGIN
DELETE FROM EMP WHERE DEPTNO = V_DEPTNO;
DBMS_OUTPUT.PUT_LINE(V_DEPTNO||'号部门已删除');
DELETE FROM EMP WHERE ENAME = UPPER(V_ENAME);
DBMS_OUTPUT.PUT_LINE('员工'||V_ENAME||'已删除');
END;
2 通过SELECT VALUE INTO VAR_NAME的方法赋值,即将数据查出后赋予到某变量
-- 方法2
DECLARE
A VARCHAR2(10);
BEGIN
SELECT ENAME INTO A FROM EMP WHERE EMPNO = 7566;
DBMS_OUTPUT.PUT_LINE(A);
END;
3 EXECUTE IMMEDIATE V_SQL INTO VAR_NAME; 查出之后赋值
-- 方法3
DECLARE
A VARCHAR2(10);
V_SQL VARCHAR2(2000);
BEGIN
--EXECUTE IMMEDIATE 'SELECT ENAME FROM EMP WHERE EMPNO = 7566' INTO A;
V_SQL := 'SELECT ENAME FROM EMP WHERE EMPNO = 7566';
EXECUTE IMMEDIATE V_SQL INTO A;
DBMS_OUTPUT.PUT_LINE(A);
END;
例子
DECLARE
V_ENAME VARCHAR2(10) := 'SMITH';
V_SAL NUMBER := 1000;
V_SQL VARCHAR2(1000);
C_DEPTNO CONSTANT NUMBER := 10;
BEGIN
SELECT SAL INTO V_SAL FROM EMP WHERE ENAME = V_ENAME;
DBMS_OUTPUT.PUT_LINE(V_ENAME||'的工资是'||V_SAL);
V_ENAME := 'SCOTT';
V_SQL := 'SELECT SAL FROM EMP WHERE ENAME = '''||V_ENAME||''''; -- ''表示一个单引号 ' 单引号'
EXECUTE IMMEDIATE V_SQL INTO V_SAL;
DBMS_OUTPUT.PUT_LINE(V_ENAME||'的工资是'||V_SAL);
SELECT SUM(SAL) INTO V_SAL FROM EMP WHERE DEPTNO = C_DEPTNO;
DBMS_OUTPUT.PUT_LINE(C_DEPTNO||'部门的总工资是'||V_SAL);
END;
总结
无论是怎样的赋值方法,变量是单列变量还是多列变量,每次赋值都只能给变量赋予一行数据!
如果一次性将多行数据或0行数据赋予给变量将会导致程序报错!