pl/sql之动态sql与异常

动态sql

静态sql可以理解为在程序编译前该语句就已经确定好所有内容，不存在未知因素，动态sql在编译时无法确定，只有在程序运行时才能真正确定

• 在SQL中如果某些参数没有确定，如"select * from emp where empno=?"，这种语句是静态SQL

动态sql适用范围（目前用户仅学习到此处）

• DDL语句和带参数的DML语句

动态sql语法格式

execute immediate 'select语句/ddl语句'	[into 变量名][using 补位数据];

• into 变量名表示将select语句赋给相应变量
• using 补位数据表示将该数据传递给前方的select语句后查询结果

动态sql简单示例

（1）select 语句

--输出雇员号为7369的员工姓名与工作
declare
	--声明变量
	b emp%rowtype;
begin
	--动态sql语句，列名=:q用户可以理解为占位符，最后会由using后数据补位
	execute immediate 'select * from emp where empno=:q' into b using 7369;
	--输出语句
	dbms_output.put_line(b.ename||', '||b.job);
end;

此处需要注意一点，如果用户将select赋值给一个变量，则相关格式会产生小小变化，关键部分代码如下：

b:='select语句';
execute immediate b into 变量名 using 补位数据;

（2）ddl语句

--创建表
begin
execute immediate 'create table b( bid number, bname varchar2(210))';
end;

异常

程序运行发生错误称为异常,发生错误后转到exception部分

程序框架如下：

declare
	声明部分;
begin
	pl/sql代码块;
exception 
	异常处理部分;
end;

exception后可接多个when then语句但当出现when others 时when others then语句必须放在最后

系统异常

（1）预定义异常（有名有错误代码）

• 常见预定义异常
  （1）ACCESS_INTO_NULL:对应ORA-06530为了引用对象属性，必须首先初始化对象。直接引用未初始化的对象属性时，会发生异常
  （2）CASE_NOT_FOUND:对应ORA-06592，当CASE语句的WHEN子句没有包含必须条件分支或者ELSE子句时，会触发
  （3）COLLECTION_IS_NULL:对应ORA-06531,在给嵌套表变量或者varrary变量赋值之前，必须首先初始化集合
  （4）CURSOR_ALREADY_OPEN:ORA-06511，当已打开游标上执行OPEN操作时会触发
  （5）INVALID_CURSOR:ORA-01001,当视图从未打开游标，提取数据，或者关闭未打开游标时会触发
  （6）INVALID_NUMBER:ORA-01722,当内嵌SQL语句不能将字符转变成数字时会触发
  （7）LOGIN_DENIED:ORA-01017,连接Oracle数据库时，如果提供了不正确的用户名和口令时会触发
  （8）NO_DATA_FOUND:ORA-01403执行SELECT INTO 未返回行或者引用了未初始化的PL/SQL表元素时会触发
  （9）NOT_LOGGED_ON:ORA-01012没有连接数据库执行SQL时会触发
  （10）PROGRAM_ERROR:ORA-06501存在PL/SQL内部问题，在这种情况下需要重新安装数据字典视图和PL/SQL包
  （11）ROWTYPE_MISMATCH:ORA-016504当执行赋值操作时，如果宿主变量和游标变量不兼容的返回类型时，会触发
  （12）SELF_IS_NULL: ORA-30625,当使用对象类型时，如果在null实例上调用成员方法时，会触发
  （13）STORAGE_ERROR:ORA-06500当执行PL/SQL块时，如果超出内存空间或者内存被破坏，会触发
  （14）SUBSCRIPT_BEYOND_COUNT:ORA-06533当使用嵌套或者varray元素的范围进会触发
  （15）SUBSCRIPT_OUTSIDE_LIMIT:ORA-06532，使用嵌套表或者varray元素时，如果元素下标为负值时，会触发
  （16）SYS-INVALID_ROWID:ORA-01410当字符串转变为ROWID时如果使用了无效字符串，会触发
  （17）TIMEOUT_ON_RESOURCE:ORA-00051当等待资源时如果出现超时会触发
  （18）TOO_MANY_ROWS:ORA-01422当执行SELECT INTO时，如果返回超过一行、会触发
  （19）VALUE_ERROR:ORA-06502,执行赋值时，如果变量长度不足，会触发
  （20）ZERO_DIVIDE：ORA-01476如果用数字值除0，会触发

• 简单示例如下

/*exception部分关键代码格式如下： when 异常名 then 输出语句; */
begin
	dbms_output.put_line(2/0);
exception
	when ZERO_DIVIDE then
		dbms_output.put_line('0不能做除数');
end;

（2）非预定义异常（无名有错误代码,需用户手动绑定一个异常名）

简单示例如下：

declare
	--声明一个异常名
	a exception;
	--将错误代码与声明异常名绑定
	pragma exception_init(a,-00001);
begin
	insert into dept values(10,'保洁部','伦敦');
exception
	when a then
		dbms_output.put_line('违反唯一约束');
end;

自定义异常

程序员从业务角度出发，指定的一些规则与限制

• 异常名由程序员自行指定
• 关键代码如下所示：

--进入异常
raise 异常名;

• 简单示例如下：

declare
	--声明一个异常
	e exception;
	--声明一个变量
	n number:=&请输入一个数字;
begin
	--如果n为0进入异常处理，反之输出
	if n=0 then
		raise e;
	else
		dbms_output.put_line('n的值为：'||n);
	end if;
exception
	--当异常名为e时，输出下列语句 
	when e then
		dbms_output.put_line('n不能为0');
end;

引发应用程序错误

• RAISE_APPLICATION_ERROR 过程用于创建用户定义的错误信息
• 可以在可执行部分和异常处理部分使用
• 错误编号必须介于 –20000 和 –20999 之间

关键部分语法格式如下：

--绑定名称与错误代码注意错误代码范围在-20000和-20999
pragma exception_init(异常名,-20001);
--用户自定义返回错误信息
dbms_standard.raise_application_error(-20001,'我的异常');

完整示例如下：

--1
declare
	--声明一个异常名
	e exception;
	--将异常名与用户定义错误代码绑定
	pragma exception_init(e,-20001);
	--定义一个变量
	a number :=&请输入一个数字;
begin
	--定义异常提醒
	dbms_standard.raise_application_error(-20001,'值不能为3');
	if a=3 then
		raise e;
	end if;
end;
--2
declare
	--声明一个异常名
	e exception;
	--将异常名与用户定义错误代码绑定
	pragma exception_init(e,-20001);
	--定义一个变量
	a number :=&请输入一个数字;
begin
	
	if a<3 then
		--抛出异常且提示信息为用户自行设置
		raise_application_error(-20001,'值不能小于3');
	end if;
	
exception
	when e then
		dbms_output.put_line(SQLCODE||','||SQLERRM);
end;

SQLCODE与SQLERRM

• sqlcode表示取异常的错误代码
• sqlerrm表示取异常的错误信息

简单示例如下：

DECLARE
a emp%ROWTYPE;
BEGIN
a.EMPNO:=&请输入雇员编号;
SELECT ENAME INTO a.ENAME FROM EMP WHERE EMPNO=a.EMPNO;
EXCEPTION
WHEN no_data_found THEN
	dbms_output.put_line('SQLCODE:'||SQLCODE||', SQLERRM:'||SQLERRM);
END;

注

• 其中自定义异常、非预定义异常下第一张图片、异常处理流程图均来源于如下网址 https://www.cnblogs.com/thescentedpath/p/errordeal.html