oracle PL/SQL中变量和常量、内置数据类型以及使用，dbms_lob程序包操作lob类型数据，PL/SQL对序列的改进，控制结构，动态sql,自定义异常和异常处理

关注

原创 2022-04-04 18:35:39 5.4w 阅读 AI 写同款· GEO 优化 ›

数据库笔试题（2）数据库笔试题姓名日期一、选择题 1、当你执行一下查询语句：SELECT empno, ename FROM emp WHERE empno=7782 OR empno=7876; 在WHERE语句中，以下哪个操作符可以取代OR? A A IN B BETWEEN …闭区间 ... 阅读详情

文章目录

一.PL/SQL

1.1 概念

PL/SQL是过程语言和结构化查询语言（sql）结合而成的编程语言；支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构；可用于创建存储过程、触发器、程序包，给SQL语句的执行添加逻辑；与oracle服务器和oracle工具紧密结合，具备可移植性、灵活性、安全性

1.2 体系结构

PL/SQL引擎驻留在oracle服务器中，该引擎接收PL/SQL块，并对其进行编译执行

PL/SQL引擎含有过程语句执行器
SQL引擎含有 sql语句执行器

1.3 PL/SQL块

PL/SQL块是执行PL/SQL程序的基本单元；将逻辑上相关的声明和语句组合在一起，PL/SQL分为三个部分：声明部分、可执行部分、异常处理部分（可选）

[declare 声明部分 ]
begin 
执行语句
exception
[异常处理]
end;

如：
使打印的内容在界面显示，执行：

set serverout on;

输出emp表行数：

declare total_row number default 0;
begin
select count(*) into total_row from scott.emp;
dbms_output.put_line('一共有'||total_row ||'条');
end;
/

在这里插入图片描述

1.3.1 定义变量和常量

PL/SQL块中可以使用变量和常量：使用之前必须在声明部分先声明；声明时必须指定数据类型，每行声明一个标识符；在可执行部分的sql语句和过程语句中使用；

语法：

名称 [constant] 类型 [not null]
[:=|default 表达式]

NOT NULL 的变量必须有初始化赋值

给变量赋值的方法：使用赋值语句 := 或者使用select into语句

declare 
total_row number ;
min_SAL constant   number  default 0;
max_SAL number  not null :=10 ;
begin
select count(*),max(SAL) into total_row,max_SAL from scott.emp;
dbms_output.put_line('一共有'||total_row ||'条'||'最大'||max_SAL||'最小'||min_SAL);
end;
/

在这里插入图片描述

1.3.2 PL/SQL支持的内置数据类型

（1）标量类型

指定数值的存储类型：
BIN_INTEGER
NUMBER
PLS_INTEGER

字符数据类型：
CHAR
VARCHAR2
LONG
RAW
LONG RAW

时间数据类型：
DATE
TIMESTAMP

布尔数据类型：
此类别只有一种类型，即BOOLEAN类型；用于存储逻辑值（true flase null）不能向数据库中插入BOOLEAN数据；不能将列值保存到BOOLEAN变量中；只能对BOOLEAN变量执行逻辑操作；

（2）LOB类型存储非结构化数据

用于存储大文本，图像，视频剪辑，声音剪辑等非结构化数据，LOB数据类型最大可存储4G的数据；

LOB类型包括：
BLOB 将大型二进制对象存储在数据库中
CLOB 将大型字符数据存储在数据库中
NCLOB 存储Unicode字符数据
BFILE 将大型二进制对象存储在操作系统文件中；

LOB类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置；

DBMS_LOB程序包用于操纵LOB数据

如：读取写入大型字符数据

create table testclob(
tid number primary key,
t_info clob);

insert into  testclob values (1,'BLOB 将大型二进制对象存储在数据库中CLOB 将大型字符数据存储在数据库中
NCLOB 存储Unicode字符数据BFILE  将大型二进制对象存储在操作系统文件中');
commit;

普通的select查询，无法完全显示clob字段属性：
在这里插入图片描述

declare 
 clob_var clob;
 amount integer;
 offset integer;
 output_var varchar2(1000);
begin
 select t_info into  clob_var from testclob where tid=1;
 amount :=1000;
 offset :=1;
 dbms_lob.read(clob_var ,amount,offset,output_var );
 dbms_output.put_line(output_var );
end;
/

在这里插入图片描述

如：存入图片文件

create table person(pid varchar2(20) primary key ,photo blob);

在这里插入图片描述
使用dba用户登录创建目录

create directory PHOTO as 'D:\c';

授权用户读写目录的权限

 grant read,write on directory PHOTO to scott;

在这里插入图片描述

create or replace procedure insertBlob(id varchar2,imgFile varchar2)
 is 
	img_file bfile;
	img_blob blob;
	lob_length number;
begin 
	insert into person values(id,empty_blob());
	select photo  into img_blob from person where pid =id;
	img_file := bfilename('PHOTO',imgFile);
	dbms_lob.open(img_file);
	lob_length :=dbms_lob.getlength(img_file);
	dbms_lob.loadfromfile(img_blob,img_file,lob_length);
	dbms_lob.close(img_file);
	commit;
	end;
	/

将图片插入

 exec insertBlob('1','a.jpg');

在这里插入图片描述

（3）属性类型

用于引用数据库列的数据类型或表中一行记录的类型；
使用属性变量，不需要知道被引用的表列的具体类型，如被引用对象的数据类型发生改变，PL/SQL变量的数据类型也随之发生改变

%TYPE 引用变量和数据库列的数据类型

%ROWTYPE 提供表示表中一行的记录类型

1.3.3 PL/SQL对序列的改进

在PL/SQL中取出序列的 nextval、currval时，可以不使用select语句
创建序列：

create sequence seq1 start with 1 increment by 1 maxvalue 10000 nocycle cache 10;

取出序列：

declare
	id int;
begin
	id:=seq1.nextval;
	dbms_output.put_line('id为'||id);
	end;
	/

在这里插入图片描述

二.控制结构

在这里插入图片描述

2.1 条件控制

IF语句

有三种形式 IF-THEN 、IF-THEN-ELSE、IF-THEN-ELSIF

IF-THEN

empHireDate 引用HIREDATE列的类型

declare 
 empHireDate emp.HIREDATE%type;
begin 
	select e.HIREDATE into empHireDate from emp e where e.EMPNO=7934;
	if empHireDate<to_date('19900101','YYYYMMDD')
		then
		dbms_output.put_line('大于32');
		end if;
	if empHireDate>=to_date('19900101','YYYYMMDD')
		then
		dbms_output.put_line('小于32');
		end if;
	end;
 /

在这里插入图片描述

IF-THEN-ELSE

declare 
 empHireDate emp.HIREDATE%type;
begin 
	select e.HIREDATE into empHireDate from emp e where e.EMPNO=7934;
	if empHireDate<to_date('19900101','YYYYMMDD')
		then
		dbms_output.put_line('大于32');
	else
		dbms_output.put_line('小于32');
		end if;
	end;
 /

在这里插入图片描述

IF-THEN-ELSIF

declare 
 empHireDate emp.HIREDATE%type;
begin 
	select e.HIREDATE into empHireDate from emp e where e.EMPNO=7934;
	if empHireDate<to_date('19800101','YYYYMMDD')
		then
		dbms_output.put_line('大于42');
		elsif empHireDate<to_date('19900101','YYYYMMDD')
		then
		dbms_output.put_line('大于32');
		else
		dbms_output.put_line('小于32');
		end if;
	end;
 /

在这里插入图片描述

CASE语句

&job 表示在键盘上输入内容

declare
	job varchar2(20);
begin
	 job := 
	 case &job
	 	when 'A' then 'a'
	 	when 'B' then 'b'
	 	when 'C' then 'c'
	 	when 'D' then 'd'
	 	else 'e'
	 	end;
	 dbms_output.put_line(job);
	end;
	/

在这里插入图片描述

2.2 循环控制

exit when 表示退出循环；

LOOP 无条件循环

declare 
	j number;
begin
	j:=1;
	loop
		dbms_output.put_line(j||'---');
		exit when j>7;
		j:=j+1;
		end loop;
		dbms_output.put_line('结束');
	end;
/

在这里插入图片描述

WHILE 根据条件循环

declare 
	j number;
begin
	j:=1;
	while  j<9
	loop
		dbms_output.put_line(j||'---');
			j:=j+1;
		end loop;
		dbms_output.put_line('结束');
	end;
/

在这里插入图片描述

FOR 循环固定的次数

语法FOR counter IN [reverse] initial_value .. final_value LOOP sequence_of_statements; END LOOP;

declare 
	j number;
begin
	for j in 1..8
	loop
		dbms_output.put_line(j||'---');
		end loop;
		dbms_output.put_line('结束');
	end;
/

在这里插入图片描述

declare 
	j number;
begin
	for j in 1..8
	loop
		dbms_output.put_line(j||'---');
		exit when j=4;
		end loop;
		dbms_output.put_line('结束');
	end;
/

在这里插入图片描述

oracle 11g新增continue语句

将逻辑移到循环结尾，进行下一次循环

declare 
	j number;
begin
	for j in 1..18
	loop	
		exit when j>8;
		continue when j=4;
		dbms_output.put_line(j||'---');
		end loop;
		dbms_output.put_line('结束');
	end;
/

在这里插入图片描述

2.3 顺序控制

GOTO语句无条件跳转到标签指定位置

declare 
	j number;
begin
	j:=1;
	<<aa>>
	dbms_output.put_line(j||'---');
	j:=j+1;
    if j<7 
    then goto aa;
    else goto bb;
    end if;
<<bb>>
null;    
		dbms_output.put_line('结束');
	end;
/

在这里插入图片描述

NULL语句什么都不做语句

三.动态sql

在PL/SQL程序执行时生成的sql语句；DDL语句命令和会话控制语句不能在PL/SQL直接使用，但可以通过动态SQL执行；
语法 execute immediate 动态sql [into 预设变量] [using 绑定变量]

在这里插入图片描述

begin
   execute immediate 'create table t(t1 int)';
   end;
   /

四.错误处理

当运行程序发生异常，语句停止执行，控制权转移到PL/SQL块的异常处理部分；

异常有2种类型：

4.1 预定义异常：当PL/SQL程序违反oracle规则或超越系统限制时隐式引发

declare
  t1 t.t1%type;
  begin
   select t1 into t1 from t ;
   dbms_output.put_line(t1);
  end;
  /

在这里插入图片描述
ORA-01403 为预定义异常,对应no_data_found 异常

declare
  t1 t.t1%type;
  begin
   select t1 into t1 from t ;
   dbms_output.put_line(t1);
   exception
   when no_data_found then  dbms_output.put_line('没有爱了');
  end;
  /

在这里插入图片描述

4.2 用户定义异常：用户可在PL/SQL块的声明部分定义异常，自定义异常通过RAISE语句显式引发

声明异常exp1，抛出异常,处理异常

declare 
 empHireDate emp.HIREDATE%type;
 e1 exception;
 begin 
	select e.HIREDATE into empHireDate from emp e where e.EMPNO=7934;
	if empHireDate<to_date('19800101','YYYYMMDD')
		then
		dbms_output.put_line('大于42');
		elsif empHireDate<to_date('19900101','YYYYMMDD')
		then
		raise e1;
		else
		dbms_output.put_line('小于32');
		end if;
	exception
		when e1 then dbms_output.put_line('没用了');
	end;
 /

在这里插入图片描述

注：
RAISE_APPLICATION_ERROR 用于创建用户定义的错误信息，可在可执行部分和异常处理部分使用，错误编号必须介于-20000和-20999之间，错误消息长度可长达2048个字节
语法RAISE_APPLICATION_ERROR (错误编号,错误信息)

declare 
 empHireDate emp.HIREDATE%type;
 e1 exception;
 begin 
	select e.HIREDATE into empHireDate from emp e where e.EMPNO=7934;
	if empHireDate<to_date('19800101','YYYYMMDD')
		then
		dbms_output.put_line('大于42');
		elsif empHireDate<to_date('19900101','YYYYMMDD')
		then
		raise e1;
		else
		dbms_output.put_line('小于32');
		end if;
	exception
		when e1 then dbms_output.put_line('没用了');
		raise_application_error(-20001,'没用');
	end;
 /