您的位置: 首页  >  文章  >  PL/SQL_高级编程

PL/SQL_高级编程

分类: 文章 • 2023-10-10 21:16:42

一.PL/SQL基础知识

PL/SQL_高级编程图片

1.什么是PL/SQL

PL/SQL（Procedural Language/SQL，过程语言/SQL）是结合了Oracle过程语言和结构化查询语言（SQL）的一种扩展语言。使用PL/SQL可以编程具有很多高级功能的程序。

优点：

（1）具有编程语言的特点，能把一组SQL语句放到一个模块中，使其更具有模块化程序的特点，如判断循环

（2）可以采用过程性语言控制程序的结构，它能把一组SQL语句放到一个模块中，使其更具有模块化程序结构，如判断循环等程序结构

（3）和其他编程语言一样，PL/SQL可以对程序中的错误进行自动处理，使程序能够在遇到错误时不会中断，即它的异常处理机制。

（4）PL/SQL程序块具有更好的可移植性，可以移植到另一个Oracle数据库中。

（5）PL/SQL程序减少了网络的交互，有助于提高程序性能。

（1）.PL/SQL体系结构

PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言

PL/SQL的工作原理

PL/SQL引擎接受 PL/SQL 块并对其进行编译执行

该引擎执行所有过程语句

将SQL语句发送给Oracle的SQL语句执行器

PL/SQL_高级编程图片

（2）PL/SQL块简介

PL/SQL是一种块结构的语言，它将一组语句放在一个块中。

PL/SQL块将逻辑上相关的声明和语句组合在一起。

匿名块是一个未在数据库中命名的PL/SQL块，在运行时被传递到PL/SQL引擎以便执行。

在PL/SQL块中可以使用select、insert、update、delete等DML语句，事务控制语句及SQL函数等。

PL/SQL不允许直接使用create、drop或alter等DDL语句，但可以通过动态SQL来执行它们。

一个PL/SQL块由三部分组成，即声明、执行部分、异常处理部分。

PL/SQL的结构

[DECLARE]
	--声明部分：在此声明PL/SQL用到的变量、类型及游标，以及局部的存储过程和函数
BEGIN
	--执行部分：过程及SQL语句，即程序的主要部分
[ECEPTION]
	--异常处理部分：错误处理
END;

--在dos/sqlplus中运行时,输入完以上，还需要set serverout on
SQL>set serverout on
SQL>/

注意：其中执行部分不可省略

（3）运算符和表达式

PL/SQL支持的操作符包含关系运算符、一般运算符和逻辑运算符等，与SQL语言类似

1.关系运算符

运算符 意义
= 等于
<>、!=、~=、^= 不等于
< 小于
> 大于
<= 小于或等于
>= 大于或等于

2.一般运算符

运算符 意义
+ 加号
- 减号
* 乘号
/ 除号
:= 赋值符
=> 关系符
.. 范围运算符
|| 字符连接符

3.逻辑运算符

运算符 意义
IS NULL 是空值
BETWEEN AND 介于两者之间
IN 在一列值中间
AND 逻辑与
OR 逻辑或
NOT 取反，如IS NOT NULL,NOT NUll

4.常量和变量的声明

在PL/SQL块的可执行部分引用变量和常量前，必须先对其进行声明。常量和变量在PL/SQL块的部分声明，在PL/SQL块的可执行部分被使用。

语法：

--声明变量
variable_name data_type[(size)][:=init_value]
解释：
1.variable_name:变量名称
2.data_type:变量的SQL或PL/SQL数据类型
3.size：变量范围
4.init_value:变量初始值

--声明常量
variable_name CONSTANT data_type:=value

--引用型变量:即e_no的类型与emp表中的empno相同
--表.字段%type  赋给e_no
declare
	e_no emp.empno%type;
begin
	select empno into e_no from emp where empno=7369;
	--输出
	dba_output.put_line(e_no)；
end;

--记录型变量：记录变量分量的引用
--表%rowtype  赋给emps，相当于对象
declare
	emps emp.empno%rowtype;
begin
	select * into emps from emp where empno=7369;
	--输出
	dba_output.put_line(emps.empno)；
end;

PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同，要求限制如下：

（1）标识符名不能超过30个字符

（2）第一个字符必须为字母

（3）不区分大小写

（4）不能用“-”（减号）

（5）不能是SQL保留字段

标 识 符 命名规则 例 子
程序变量 v_name v_student_name
程序常量 c_name c_company_name
游标变量 cursor_name cursor_emp
异常标志 e_name e_too_many
表类型 name_table_type emp_record_type
表 name_table emp_table
记录类型 name_record emp_record
绑定变量 g_name g_year_sal

​

案例：

declare
	v_name varchar2(20);
	v_rate number(7,2);
	c_rate_incr CONSTANT number(7,2):=1.10
begin
	--方法一：通过select into 给变量赋值
	select ename,sal*c_rate_incr into v_ename.v_rate from employee where 		 empno='7788';
	
	--方法二：通过赋值操作符“:=”给变量赋值
	v_ename:='SCOTT';
end;

select 语句在pl/sql中有改变，为select into。查询结果只能返回一行并赋值到变量中保存。

Oracle 11g新增特性：可以使用pl/sql块中的赋值语句访问序列，提高灵活性。

v_no:=emp_seq.nextval;

5.注释

单行 --

多行 /* */

2.PL/SQL数据类型

PL/SQL变量，常量和参数都必须有一个有效的数据类型，指定存储格式，约束和值的有效范围。本教程介绍标量和LOB在PL/SQL，其他两种数据类型可用的数据类型将涵盖在后面的章节。

分类 描述
标量 单值没有内部组件，如NUMBER, DATE 或 BOOLEAN
大对象(LOB) 指向其他数据项，如文本，图形图像，视频剪辑和声音等存储大对象
复合 具有可单独访问的内部组件的数据项。例如，集合和记录
引用 指向其他数据项

1.PL/SQL标量数据类型和子类型

PL/SQL标量数据类型和子类型受到以下几大类：

数据类型 描述
数字 在其上执行的算术运算的数值
字符 代表单个字符或字符的字母数字字符串值
布尔 在其上执行的逻辑运算的逻辑值
日期时间 日期和时间

PL/SQL提供的数据类型的子类型。例如，数据类型数有一个叫作INTEGER的子类型。可以使用子类型在PL/SQL程序中，使数据类型与其他程序中的数据类型兼容，而嵌入PL/SQL代码在另一个程序，如Java程序。

2.PL/SQL数值数据类型和子类型

以下是PL/ SQL预定义的数字数据类型及其子类型的详细信息：

数据类型 描述
PLS_INTEGER 通过2,147,483,647到-2147483648范围内有符号整数，以32位表示
BINARY_INTEGER 通过2,147,483,647到-2147483648范围内的有符号整数，以32位表示
BINARY_FLOAT 单精度IEEE 754格式的浮点数
BINARY_DOUBLE 双精度IEEE 754格式的浮点数
NUMBER(prec, scale) 定点或浮点数在范围1E-130至（但不包括）绝对值1.0E126。 NUMBER变量也可以表示0
DEC(prec, scale) ANSI具体的定点类型使用38位小数最大精度
DECIMAL(prec, scale) IBM特定的固定点型具有38位小数最大精度
NUMERIC(pre, secale) 浮点类型具有38位小数最大精度。
DOUBLE PRECISION 具有126个二进制数字最大精度ANSI特定浮点型（约38位十进制数）
FLOAT 具有126个二进制数字（约38位十进制数）最大精度ANSI和IBM特定的浮点型
INT 具有38位小数最大精度ANSI具体的整数类型
INTEGER ANSI和IBM的38位小数最大精度具体的整数类型
SMALLINT ANSI和IBM的38位小数最大精度具体的整数类型
REAL 具有63位二进制数字最大精度浮点型（大约18位小数）

下面是一个有效的声明：

DECLARE
   num1 INTEGER;
   num2 REAL;
   num3 DOUBLE PRECISION;
BEGIN
   null;
END;
/

让我们编译和运行上面的程序，这将产生以下结果：

PL/SQL procedure successfully completed

3.PL/SQL字符数据类型和子类型

以下是PL/SQL预定义的字符数据类型及其子类型的详细信息：

数据类型 描述
CHAR 具有32,767个字节的最大尺寸固定长度字符串
VARCHAR2 具有32,767个字节的最大尺寸变长字符串
RAW 可变长度的二进制或字节字符串的32,767个字节的最大尺寸，而不是由PL/ SQL解释
NCHAR 具有32,767个字节的最大尺寸的固定长度国家字符串
NVARCHAR2 具有32,767个字节的最大尺寸可变长度国家字符串
LONG 具有32,760字节最大尺寸变长字符串
LONG RAW 可变长度的二进制或字节字符串的32,760字节的最大尺寸，而不是由PL/SQL解释
ROWID 物理行标识符，一行在一个普通的表中的地址
UROWID 物理通用行标识符（物理，逻辑，或外国的行标识符）

4.PL/SQL布尔数据类型

在逻辑操作中使用布尔数据类型存储的逻辑值。逻辑值为：true 和 false 的布尔值以及 NULL 值。

但是，SQL没有数据类型等同于布尔。因此，布尔值不能用于：

  • SQL语句
  • 内置SQL函数（如TO_CHAR）
  • 从SQL语句调用PL/ SQL函数

5.PL/SQL日期时间和间隔类型

DATE数据类型存储固定长度的日期时间，其中包括每天在几秒钟内从午夜开始的时间。有效的日期范围从公元前4712年1月1日至999912月31日。

默认的日期格式由Oracle初始化参数NLS_DATE_FORMAT设置。例如，默认的可以是“DD-MON-YY'，它包括一个两位数字的月份中的日期，月份名称的缩写，以及年的最后两位数字，例如，01- OCT-12。

每个日期，包括世纪，年，月，日，时，分，秒。下表显示了每个字段的有效值：

字段名称 有效日期时间值 有效的间隔值
YEAR -4712 to 9999 (年除外 0) 任何非零整数
MONTH 01 to 12 0 to 11
DAY 01 to 31 (受制于年份和月份的数值，按日历的区域设置的规则) 任何非零整数
HOUR 00 to 23 0 to 23
MINUTE 00 to 59 0 to 59
SECOND 00 to 59.9(n), 其中，9（n）是时间小数秒精度 0 to 59.9(n), 其中，9（n）是区间小数秒精度
TIMEZONE_HOUR -12 to 14 （范围可容纳夏令时更改） 不适用
TIMEZONE_MINUTE 00 to 59 不适用
TIMEZONE_REGION 在动态性能视图找到V$TIMEZONE_NAMES 不适用
TIMEZONE_ABBR 在动态性能视图找到V$TIMEZONE_NAMES 不适用

###6.PL/SQL大对象（LOB）数据类型

大对象（LOB）数据类型是指大到数据项，例如文本，图形图像，视频剪辑和声音波形。 LOB数据类型允许高效的，随机的，分段访问这些数据。以下是预定义的PL/SQL LOB数据类型：

数据类型 描述 大小
BFILE 用于存储大型二进制对象在操作数据库之外的系统文件 依赖于系统，不能超过4千兆字节（GB）
BLOB 用于存储大型二进制对象在数据库中 8到128兆兆字节（TB）的
CLOB 用于存储字符大块数据在数据库中 8 - 128 TB
NCLOB 用于在数据库中存储大块NCHAR数据 8 - 128 TB

7.PL/SQL用户定义的子类型

子类型是另一种数据类型，这是所谓的基本类型的子集。 子类型具有其基本类型相同的操作，但其只是有效值的一个子集。

PL/SQL预定义了一些子类型的封装标准。例如，PL/SQL预定义的子类型CHARACTER和INTEGER如下：

SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);

可以定义和使用自己的子类型。下面的程序说明定义和使用一个用户定义的子类型：

DECLARE
   SUBTYPE name IS char(20);
   SUBTYPE message IS varchar2(100);
   salutation name;
   greetings message;
BEGIN
   salutation := 'Reader ';
   greetings := 'Welcome to the World of PL/SQL';
   dbms_output.put_line('Hello ' || salutation || greetings);
END;
/

当上述代码在SQL提示符执行时，它产生了以下结果：

Hello Reader Welcome to the World of PL/SQL

PL/SQL procedure successfully completed.

8.PL/SQL NULL

PL/SQL NULL值表示缺少或未知的数据，蛤它不是一个整数，字符，或任何其他特定的数据类型。需要注意的是NULL不是一样的空数据串或空字符值'\0'。NULL可以被分配，但它不能与任何东西等同，包括其本身。

二.PL/SQL控制语句

条件控制，循环控制，顺序控制

（1）条件控制

语法:

--if语句
if <布尔表达式> then
	pl/sql和sql语句；
end if；
-------------------
if <布尔表达式> then
	pl/sql和sql语句；
elsif
	其他语句；
end if；

--case语句
--格式一
case 条件表达式
	when 条件表达式结果1 then
		语句段1
	when 条件表达式结果2 then
		语句段2
	......
	when 条件表达式结果n then
		语句段n
	[else 语句段]
end case;
--格式二
case
	when 条件表达式结果1 then
		语句段1
	when 条件表达式结果2 then
		语句段2
	......
	when 条件表达式结果n then
		语句段n
	[else 语句段]
end case;

案例：

--sqldeveloper 相当于dos
set serverout on
--键盘输入
--num:地址符
accept num prompt'请输入一个数字';
declare 
  --定义保存变量
  pnum number:=&num;
begin
  if pnum = 0 then dbms_output.put_line('输入为0');
  elsif pnum = 1 then dbms_output.put_line('输入为1');
  else dbms_output.put_line('输入为其他');
  end if;
end;
/

（2）循环控制

循环控制包括LOOP和EXIT语句，使用exit语句可以立即退出循环，使用exit when语句可以根据条件结束循环

循环共三种：loop、while、for

1.loop循环

语法：

loop
	要执行的语句
	exit when <条件语句> --条件满足，退出循环语句
end loop;

案例：

declare
num number:=1;
begin
  loop
    num:=num+1;
    exit when num=10;
    dbms_output.put_line(num);
  end loop;
end;

2.while循环

语法：

while <布尔表达式> loop
	要执行的语句
end loop;

案例：

declare
num number:=1;
begin
  while num<10 loop
        num:=num+1;
        dbms_output.put_line(num);
  end loop;
end;

3.for循环

语法：

for 循环计数器 in [reverse] 下限...下限 loop
	要执行的语句
end loop;

案例:

declare
  i number;
begin
	--循环遍历1-10
  for i in 1..10 loop
     dbms_output.put_line(i);
  end loop;
  	--反向循环遍历1-10
  for i in reverse 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

（3）顺序控制

顺序控制用于顺序执行语句。顺序控制包括null语句和goto语句。goto不推荐

null语句：null语句是一个可执行语句，相当于一个占位符或不可执行任何操作的空语句，提高可读性。

三.异常

#四.游标（光标Cursor）

游标是用来处理使用select语句从数据库检索到的多行记录的工具

游标分类：

​ 1.显示游标：返回多条记录时，使用显示游标逐行读取

​ 2.隐式游标：PL/SQL自动为DML语句创建隐式游标，包含一条返回记录

1.显式游标

（1）游标的使用

（1）声明光标：

语法：

cursor cursor_name [(parameter[,patameter]...)]
[return return_type] is select_statement;
解释：
1.cursor_name:游标的名称
2.parameter：用于为游标指定输入参数，不能使用长度约束
3.return_type：用于定义游标提取的行的类型
4.select_statement:指定游标定义的查询语句

（2）打开光标：

语法：

open curosr_name [(parameters)];

（3）提取游标

语法：

fetch cursor_name into variables;
解释：
1.cursor_name：游标名称
2.variables：变量名

（4）关闭光标

语法：

close cursor_name;

案例：

declare 
  name emp.ename%type;
  sal emp.sal%type;
  cursor emp_cursor is select ename,sal from emp;
begin
  open emp_cursor;
  loop
  fetch emp_cursor into name,sal;
  exit when emp_cursor%notfound;
  dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员:'||name||'的月薪是'||sal);
  end loop;
  close emp_cursor;
end;
----------
第1个雇员:SMITH的月薪是800
第2个雇员:ALLEN的月薪是1600
第3个雇员:WARD的月薪是1250
第4个雇员:JONES的月薪是2975
第5个雇员:MARTIN的月薪是1250
第6个雇员:BLAKE的月薪是2850
第7个雇员:CLARK的月薪是2450
第8个雇员:KING的月薪是5000
第9个雇员:TURNER的月薪是1500
第10个雇员:JAMES的月薪是950
第11个雇员:FORD的月薪是3000
第12个雇员:MILLER的月薪是1300

（2）显式子游标的属性

属性名称 说 明
%found 用于检验游标是否成功，通常在FETCH语句前使用，当游标按照条件查询出一条记录时，返回true
%isopen 判断游标是否处于打开状态，视图打开一个已经打开或者已经关闭的游标，将会出现错误
%notfound 与%found的作用相反，当按照条件无法查询到记录时，返回true
%rowcount 循环执行游标读取数据时，返回检索出的记录数据的行数

（3）使用显式游标删除或更新

使用游标时，如果处理过程中需要删除或更新行，在定义游标时必须使用select ...for update语句，而在执行delete和update时使用where current of子句指定游标的当前行。

声明更新游标语法：

update table_name is select_sataement for update [of columns];
解释：
1.for update [of columns]为更新查询，锁定选择的行。
（1）当前单表更新查询时，可以省略of子句
（2）当选择多个表更新查询时，被锁定的行来源于of子句后声明的列所在的表中的行。

在使用for update子句声明游标之后，可以使用以下更新行：

update table_name 
	set columns_name = columns_value 
where current of cursor_name;
解释：
多表查询更新时，更新表为锁定行所在的表。

案例：给所有员工增加200工资

declare 
  cursor emp_cursor is select ename,sal from emp e inner join dept d on e.deptno=d.deptno for update of sal;
  --v_num记录行数
  v_num emp_cursor%rowtype;
begin
  open emp_cursor;
  if not emp_cursor%isopen then
    open emp_cursor;
  end if;
  loop
  fetch emp_cursor into v_num;  
  exit when emp_cursor%notfound;
  update emp set sal=sal+200 where current of emp_cursor;
  end loop;
  close emp_cursor;
end;

###（4）使用循环游标简化游标的读取

循环游标隐式打开游标自动从活动集获取行，然后再处理完所有行时关闭游标。循环游标自动创建%rowtype类型的记录型变量用作记录索引。

语法：

for record_index in cursor_name
loop
	exectable_statements
end loop;
解释：
1.record_index:是PL/SQL声明的记录变量，此变量的属性为%rowtype类型，作用域在for循环之内，即在for循环之外不能访问此变量。

循环游标特性：

（1）在从游标中提取了所有记录之后自动终止

（2）提取和处理游标中的每一条记录

（3）如果在提取记录之后%notfound属性返回true，则终止循环。

（4）如果未返回行，则不进入循环。

案例：

--显示员工表所有员工的姓名和薪水
declare 
  cursor emp_cursor is select ename,sal from emp;
begin
  for emp_record in emp_cursor loop
      dbms_output.put_line('第'||emp_cursor%rowcount||'个员工：'||emp_record.ename||'的工资是'||emp_record.sal);
  end loop;
end;
--------------
第1个员工：SMITH的工资是1000
第2个员工：ALLEN的工资是1800
第3个员工：WARD的工资是1450
第4个员工：JONES的工资是3175
第5个员工：MARTIN的工资是1450
第6个员工：BLAKE的工资是3050
第7个员工：CLARK的工资是2650
第8个员工：KING的工资是5200
第9个员工：TURNER的工资是1700
第10个员工：JAMES的工资是1150
第11个员工：FORD的工资是3200
第12个员工：MILLER的工资是1500

（5）no_data_found和%notfound区别

1.select...into语句返回0条和多条记录时触发not_data_found

2.当update或delete语句的where子句未找到时，触发%notfound

3.在提取循环中用%notfound或%found来确定循环的退出条件，而不用not_data_found

其他

1.&

--手动给a赋值
select empno,&a from emp;  
select * from &a;
insert into emp(empno,sal) values(&a,&b)；

相关推荐