pl初学者总结（下）
五、动态sql和异常
1.动态 SQL
动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句
编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行
DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行
执行动态 SQL 的语法：
EXECUTE IMMEDIATE 字符串类型的命令
[INTO define_variable_list]
[USING bind_argument_list];
execute immediate 字符串参数 [into] 变量 using 参数
immediate后面跟的参数：它是一个字符串类型的sql语句，或者字符串类型的plsql代码
into 变量:是把sql语句的执行结果保存到变量中
using 参数：传给sql语句的参数
如：
--按照员工编号查询员工的信息
declare--声名一个变量保存员工信息v_emp emp%rowtype;--声名一个字符串类型变量v_sql varchar2(255);beginexecute immediate 'select * from emp where empno=7369' into v_emp;dbms_output.put_line(v_emp.ename||','||v_emp.job);v_sql:='select * from emp where empno=7369';execute immediate v_sql into v_emp;dbms_output.put_line(v_emp.ename||','||v_emp.job);v_sql:='select * from emp where empno=:a and deptno=:b';execute immediate v_sql into v_emp using 7369,20;dbms_output.put_line(v_emp.ename||','||v_emp.job);end;declare--声名一个变量保存员工信息v_emp emp%rowtype;--声名一个字符串类型变量v_sql varchar2(255);--n number(10):=1000;begin-- execute immediate 'update emp set sal=:1 where empno=:2' using n,7369 ;--execute immediate 'delete from emp where empno=:1' using 7369;execute immediate 'create table t(id number(11),name varchar2(20))';end;select * from emp where empno=7369;select * from t;2.异常
1.异常概述
在运行程序时出现的错误叫做异常
发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分
declare声明部分;beginplsql代码块;exception异常处理部分;end;ORACLE异常分为两种类型：系统异常、自定义异常。其中系统异常又分为：预定义异常和非预定义异常。
2.预定义异常
ORACLE定义了它们的错误编号和异常名字，常见的预定义异常处理如下:
ACCESS_INTO_NULL:对应ORA-06530为了引用对象属性，必须首先初始化对象。直接引用未初始化的对象属性时，会发生异常
CASE_NOT_FOUND:对应ORA-06592，当CASE语句的WHEN子句没有包含必须条件分支或者ELSE子句时，会触发
COLLECTION_IS_NULL:对应ORA-06531,在给嵌套表变量或者varrary变量赋值之前，必须首先初始化集合
CURSOR_ALREADY_OPEN:ORA-06511，当已打开游标上执行OPEN操作时会触发
INVALID_CURSOR:ORA-01001,当视图从未打开游标，提取数据，或者关闭未打开游标时会触发
INVALID_NUMBER:ORA-01722,当内嵌SQL语句不能将字符转变成数字时会触发
LOGIN_DENIED:ORA-01017,连接Oracle数据库时，如果提供了不正解的用户名和口令时会触发
NO_DATA_FOUND:ORA-01403执行SELECT INTO 未返回行或者引用了未初始化的PL/SQL表元素时会触发
NOT_LOGGED_ON:ORA-01012没有连接数据库执行SQL时会触发
PROGRAM_ERROR:ORA-06501存在PL/SQL内部问题，在这种情况下需要重新安装数据字典视图和PL/SQL包
ROWTYPE_MISMATCH:ORA-016504当执行赋值操作时，如果宿主变量和游标变量不兼容的返回类型时，会触发
SELF_IS_NULL: ORA-30625,当使用对象类型时，如果在null实例上调用成员方法时，会触发
STORAGE_ERROR:ORA-06500当执行PL/SQL块时，如果超出内存空间或者内存被破坏，会触发
SUBSCRIPT_BEYOND_COUNT:ORA-06533当使用嵌套或者varray元素的范围进会触发
SUBSCRIPT_OUTSIDE_LIMIT:ORA-06532，使用嵌套表或者varray元素时，如果元素下标为负值时，会触发
SYS-INVALID_ROWID:ORA-01410当字符串转变为ROWID时如果使用了无效字符串，会触发
TIMEOUT_ON_RESOURCE:ORA-00051当等待资源时如果出现超时会触发
TOO_MANY_ROWS:ORA-01422当执行SELECT INTO时，如果返回超过一行、会触发
VALUE_ERROR:ORA-06502,执行赋值时，如果变量长度不足，会触发
ZERO_DIVIDE：ORA-01476如果用数字值除0，会触发
如：
declarev emp.ename%type;beginselect ename into v from emp where 1=0;end;语法：
beginexceptionwhen 异常名称 then异常处理代码;when 异常名称 then异常处理代码;when 异常名称 then异常处理代码;when others then异常处理代码; end;exception:出现在end前面，在exception后不能有除 异常处理代码 外的其它代码
others:others其他所有的异常
注：
SQLCODE与SQLERRM为异常处理函数。
函数SQLCODE用于取得Oracle错误号，
函数SQLERRM用于取得与错误号对应的相关错误消息
3.非预定义异常
ORACLE为它定义了错误编号，但没有定义异常名字。我们使用的时候，先声名一个异常名，通过伪过程PRAGMA EXCEPTION_INIT，将异常名与错误号关联起来。
如：
pragma exception_init(自定义的异常,编号);
insert into emp(empno,deptno) values(7369,10);
declare--声名异常变量myexc exception;--绑定异常pragma exception_init(myexc,-00001);beginbegininsert into emp(empno,deptno) values(7369,10);-- commit;exceptionwhen myexc then-- rollback;dbms_output.put_line('违反唯一约束');end;dbms_output.put_line('-----'); end;4.自定义异常
程序员从业务角度出发，制定的一些规则和限制。
异常名称 exception;
抛出异常：
raise 异常名称;
exception 捕获异常 处理异常
----------------------------------------
declare--定义一个自己的异常myexcp exception;--声名两个变量接收两个数m number(10):=&m;n number(10):=&n;beginif n=0 thendbms_output.put_line('除数不能为零');raise myexcp;--抛出异常 需要处理异常end if;dbms_output.put_line(m/n);end;5.引发应用程序错误
RAISE_APPLICATION_ERROR 过程
用于创建用户定义的错误信息
可以在可执行部分和异常处理部分使用
错误编号必须介于 –20000 和 –20999 之间
引发应用程序错误的语法：
RAISE_APPLICATION_ERROR(error_number, error_message);
如：
declaremyexc exception;pragma exception_init(myexc,-20001);begindbms_standard.raise_application_error(-20001,'我的异常');exceptionwhen myexc thendbms_output.put_line('1111111111');end;六、存储过程和函数
1.概述
ORACLE 提供 可以把PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。存储过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。
2.存储过程
存储过程，就是一段存储在数据库中执行某块业务功能的程序模块。它是由一段或者多段的PL/SQL代码块或者SQL语句组成的一系列代码块。
创建存储过程的语法
create [or replace] procedure 存储过程名[(参数 in|out|in out 参数数据类型,...)]
is|as
声名部分;
begin
plsql代码块;
exception
异常处理;
end;
如：
--无参数存储过程
create or replace procedure p1isbeginfor v in (select * from emp) loopdbms_output.put_line(v.ename||','||v.job||','||v.sal||','||v.deptno);end loop;end;select * from user_objects where object_type='PROCEDURE';存储过程的调用
过程名字（）;
1.在plsql块中调用
2.call命令调用
call 名字();
- execute命令调用（sqlplus中输出）
Sqlplus :
set serverout on;
Begin
过程名();
end;
删除存储过程
drop procedure p1;
参数的三种模式
IN 用于接受调用程序的值。默认的参数模式
OUT 用于向调用程序返回值
IN OUT 用于接受调用程序的值，并向调用程序返回更新的值
--写一个存储过程，根据传入部门编号，查询该部门下的所有员工，并打印员工信息
create or replace procedure p1(v_deptno in number)isbeginfor v in (select * from emp where deptno=v_deptno) loopdbms_output.put_line(v.ename||','||v.job||','||v.sal||','||v.deptno);end loop;end;declarev_dno number(10):=20;beginp1(10); --传值dbms_output.put_line('______________________________________________________');p1(v_dno); --传变量dbms_output.put_line('______________________________________________________');p1(v_deptno=>30); --按位传值end;--根据传入的员工编号，查询出员工信息并以输出参数的方式返回
create or replace procedure p2(v_empno in number,v_emp out emp%rowtype)isbeginselect * into v_emp from emp where empno=v_empno;end;declarev emp%rowtype;beginp2(7369,v);dbms_output.put_line(v.empno||','||v.ename||','||v.job||','||v.deptno);end;create or replace procedure p3(v_emp in out emp%rowtype)isbeginselect * into v_emp from emp where empno=v_emp.empno;end;declarev_e emp%rowtype;begin--要给v_e里的empno赋值v_e.empno:=7369;p3(v_e);dbms_output.put_line(v_e.empno||','||v_e.ename||','||v_e.job||','||v_e.deptno);end;注：将过程的执行权限授予其他用户
--存储过程所在的用户执行赋权限
grant EXECUTE ON 存储过程名 to 用户名;
3.函数
函数是通过关键字function按照自己的需求把复杂的业务逻辑封装进PL/SQL函数中，函数提供一个返回值，返回给使用者。
创建函数的语法
create [or replace] function 函数名称[(形式参数 参数类型,...)]
return 返回值类型
is
声名变量;
begin
plsql代码块;
return 返回值;--return后面的内容不执行
end;
如:
create or replace function f1return varchar2isbeginreturn 'hello';end;访问函数的方式
使用 SQL 语句
Select 函数名字() from dual;
使用 PL/SQL 块
declarev varchar2(30);beginv:=f1;dbms_output.put_line(v);end;--求二个数的和
create or replace function f1(n number,n2 number)return numberisbegin return n + n2;end;--求一个数的阶乘
create or replace function f1(n number)return numberis--声名一个变量保存返回值res number(10):=1;beginfor i in 1..n loopres:=res*i;end loop;return res;end;七、触发器
1.触发器概述
触发器是一种在事件发生时隐式地自动执行的PL/SQL块，不能接受参数，不能被显式调用
2.触发器类型
1.DML触发器：在对表或视图执行DML语句时 执行,其中包含 语句级触发器(表级触发器),行级触发器,INSTEAD OF触发器(替换触发器)
2.模式(DDL)触发器:在模式中执行 DDL 语句时执行
3.数据库级触发器:在发生打开、关闭、登录和退出数据库等系统事件时执行
3.创建触发器的语法
语法：
create [or replace] trigger 触发器名字
before|after 触发器事件 on table_name
[for each row]--行级触发器
[when trigger_condition]
trigger_body
语法解释：
trigger_name：触发器名称
before | after | instead of : 指定触发器是在触发事件发生之前触发还暗示发生之后触发。
trigger_event：触发事件，在DML触发器中主要为insert、update [OF column_list]、delete
table_name：表名，表示发生触发器作用的对象
for each row：指定创建的是行级触发器，若没有该子句则创建的是语句级触发器
when trigger_condition：添加的触发条件
trigger_body：触发体，是标准的PL/SQL语句块
Declare begin end;
4.表级触发器（语句级触发器）
无论受影响的行数是多少，都只执行一次
没有for each row语句，在begin代码段中不可以使用:new和:old。
语法：
create or replace trigger 触发器名称
before|after
update or |delete or |insert on 表名
declare
声名变量;
begin
触发器代码;
end;
before:表示在sql语句执行前，执行触发器的代码
after:表示在sql语句执行之后，执行触发器的代码
以下三个都是boolean类型的
updating:如果触发这个触发器的是一条update语句，它值就是true
deleting:如果触发它是一条delete语句，它值就是true
inserting:如果触发它是一条insert语句，它值就是true
--给dept表创建一个触发器，当dept表中的数据被修改、删除、插入时打印该操作
create trigger t1 before insert or update or delete on deptbeginif inserting thendbms_output.put_line('插入');end if;if updating thendbms_output.put_line('修改');end if;if deleting thendbms_output.put_line('删除');end if;end;insert into dept(deptno,dname,loc) values(41,'财务部','上海');update dept set dname='ss';delete from dept;5.行级触发器
对DML语句修改的每个行执行一次,有for each row语句，在begin代码段中可以使用:new和:old。
:new --是 一个引用最新的列值
:old --是 一个引用以前的列值
语法：
create or replace trigger 触发器名称
before|after update or delete or insert on 表名 for each row
begin
触发器代码;
end;
create or replace trigger t1beforeupdate or delete or insert on dept for each rowbeginif updating thendbms_output.put_line('修改');end if;if inserting thendbms_output.put_line('插入');end if;if deleting thendbms_output.put_line('删除');end if;end;create table seqtab(id number(11) primary key,name varchar2(30));create sequence seq start with 1 increment by 1;select seq.nextval from dual;--建一个触发器从序列中取值，作为表的主键create or replace trigger t2before insert on seqtab for each rowbegin:new.id:=seq.nextval;end;insert into seqtab(name) values('张三');select * from seqtab;insert into seqtab(name) values('李四');delete from seqtab where id=3;insert into seqtab(id) values(10000);create or replace trigger t2before update on seqtab for each rowbegindbms_output.put_line(:old.id||','||:old.name);end;update seqtab set name='王五' where id=5;6.替换触发器
替换触发器使用在视图上，而且是行级的触发器
create view de as select d.deptno dno, d.dname,d.loc,e.* from dept d,emp e where d.deptno=e.deptno;
select * from de;
语法：
create or replace trigger 触发器名
instead of insert or update or delete on 视图名 for each row
begin
end;
如：
create or replace trigger t3instead of update on de for each rowdeclarev number;beginselect deptno into v from emp where empno = :old.empno;update dept set dname=:new.dname where deptno = v;end;update de set dname = '财务部' where empno=7369;7.模式触发器
在模式中执行 DDL 语句时执行
CREATE TABLE dropped_obj (obj_name VARCHAR2(30), obj_type VARCHAR2(20),drop_date DATE);CREATE OR REPLACE TRIGGER 触发器名字before ALTER or DROP or CREATE ON SCHEMABEGININSERT INTO dropped_obj VALUES(ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE, SYSDATE);END;常用系统变量：
Ora_client_ip_address 返回客户端的ip地址
Ora_database_name 返回当前数据库名
Ora_login_user 返回登录用户名
Ora_dict_obj_name 返回ddl操作所对应的数据库对象名
Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型
8.数据库级触发器
在发生打开、关闭、登录和退出数据库等系统事件时执行
startup打开 关闭shutdown 登录logon 退出logoff
--数据库启动触发器
create table event_table ( event varchar2(30), time date);create or replace trigger tr_startupafter startup on databasebegininsert into event_table values( ora_sysevent, sysdate);end;--用户登陆触发器
create table log_table(username varchar2(20), logon_time date);create or replace trigger tr_logonafter logon on databasebegininsert into log_table(username,logon_time) values(ora_login_user, sysdate);end;注：启用、禁用和删除触发器
启用和禁用触发器
ALTER TRIGGER 触发器名字 DISABLE;
ALTER TRIGGER 触发器名字 ENABLE;
删除触发器
DROP TRIGGER aiu_itemfile;
八、程序包
1.程序包概述
程序包是对相关过程、函数、变量、游标和异常等对象的封装
程序包由规范(包头)和主体(包体)两部分组成
规范中可以声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等
主体中可以声明程序包私有对象和实现在包规范中声明的子程序和游标
2.创建程序包的语法
包头和包体名字一致
程序包规范语法
CREATE [OR REPLACE] PACKAGE 名字 IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];
package_name:包的名称
Public item declarations：公共声明部分
Subprogram specification：声明PL/SQL子程序
程序包主体
CREATE [OR REPLACE] PACKAGE BODY 名字 IS|AS
[Private item declarations]
[Subprogram bodies]
[BEGIN
Initialization]
END [package_name];
package_name是包的名称
public type and item declarations::私有声明部分
subprogram specificatons:子程序体
如：
--定义包头
create or replace package pack1isaa number := 9;procedure insert_dept(v_dept in dept%rowtype);function fun(v1 number,v2 number) return number;end;--包体实现
create or replace package body pack1isbb number := 10;procedure insert_dept(v_dept in dept%rowtype)isbegininsert into dept values(v_dept.deptno,v_dept.dname,v_dept.loc);end;function fun(v1 number,v2 number) return numberisbeginreturn v1 + v2;end;end;调用:包名.元素名;
3.程序包中的游标
游标的定义分为游标规范和游标主体两部分
在包规范中声明游标规范时必须使用return子句指定游标的返回类型 return 数据类型;
RETURN子句指定的数据类型可以是：
用 %ROWTYPE 属性引用表定义的记录类型
程序员定义的记录类型，例如 TYPE EMPRECTYP IS RECORD(emp_id INTEGER,salary REAL) 来定义的。
不可以是number, varchar2， %TYPE等类型。
如：
--显示游标
create or replace package pack2 iscursor mycursor return emp%rowtype;procedure mycursor_use;end;create or replace package body pack2 iscursor mycursor return emp%rowtype is select * from emp;procedure mycursor_useisv_emp emp%rowtype;beginopen mycursor;fetch mycursor into v_emp;while mycursor%found loopdbms_output.put_line(v_emp.ename);fetch mycursor into v_emp;end loop;close mycursor;end;end;--ref游标
create or replace package pack3 istype refcur is ref cursor;procedure mycursor_use;end;create or replace package body pack3 isprocedure mycursor_useismycursor refcur;v_emp emp%rowtype;beginopen mycursor for select * from emp;fetch mycursor into v_emp;while mycursor%found loopdbms_output.put_line(v_emp.ename);fetch mycursor into v_emp;end loop;close mycursor;end;end;4.内置程序包
可以扩展数据库的功能为 PL/SQL 提供对 SQL 功能的访问用户 SYS 拥有所有程序包是公有同义词
可以由任何用户访问
一些内置程序包：
standard和dbms_standard：定义和扩展pl/sql语言环境
sbms_lob：提供对lob数据类型进行操作的功能
dbms_output：处理pl/sql块和子程序输出调试信息
dbms_random：提供随机数生成器
dbms_sql：允许用户使用动态sql
dbms_xmldom：用dom模型读写xml类型的数据
dbms_xmlparser：xml解析，处理xml文档内容和结构
dbms_xmlquery：提供将数据转换为xml类型的功能
dbms_xslprocessor：
提供xslt（xslt是一种用于将xml文档转换为xhtml文档或其他xml文档的语言）功能，转换xml文档
utl_file：用pl/sql程序来读写操作系统文本文件
DBMS_RANDOM包的的使用random
用来产生随机的数字、字符、日期
--产生随机整数
SELECT DBMS_RANDOM.RANDOM FROM DUAL;BeginDbms_output.put_line(dbms_random.random)End;--产生一个100以内的随机整数
SELECT ABS(MOD(DBMS_RANDOM.RANDOM,100))
FROM DUAL;
--VALUE函数会返回一个大于等于0但是小于1的数
例如：
SELECT DBMS_RANDOM.VALUE FROM DUAL;
对于指定范围内的数，要加入参数low_value和
high_value
--0到100之间的小数
SELECT DBMS_RANDOM.VALUE(0, 100)
FROM DUAL;
--产生0到100之间的整数
SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100))
FROM DUAL;
STRING函数生成随机文本字符串，可以指定字符串的类型和所希望的长度。
SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL;
下面是一些类型的代码：
‘U’用来生成大写字符，upper
‘L’用来生成小写字符，lower
‘A’用来生成大小写混合的字符，
’P’ 表示 字符串由任意可打印字符构成， +-#$%^^
’X’表示字符串由大写字符和数字构成。
返回某年内的随机日期，分两步：
--日期对应的内部整数，用格式'J' -4712
1, SELECT TO_CHAR(TO_DATE('01/01/21','mm/dd/yy'),'J')
FROM DUAL;
2, SELECT
TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2459216,2459216+365) ),'J') FROM DUAL;
DBMS_Job包的用法
--创建测试表
create table mytable1(a date);
--创建一个自定义过程
create or replace procedure test1isbegininsert into mytable1 values(sysdate);end;declare
v number;
begin
----创建job
Dbms_job.submit()
/* submit()参数：
1 job OUT BINARY_INTEGER, 输出的no 编号
2 what IN VARCHAR2,'存储过程名;'
3 next_date IN DATE DEFAULT sysdate,执行任务的时间
4 interval IN VARCHAR2 DEFAULT 'null', 间隔时间 单位是1天*/
dbms_job.submit(v,'test1;',sysdate,'sysdate+1/1440');
--运行JOB
dbms_job.run(v);
--删除JOB
dbms_job.remove(v);
end;
--停止任务
dbms_job.broken(27,true);
UTL_FILE包的用法
directory:目录
--1创建一个oracle的目录：
create or replace directory 目录名称 as '目录的路径';
create or replace directory FILEPATH as 'd:/SQL';
declare--声名文件类型的变量f utl_file.file_type;begin--1.打开文件utl_file.fopen(创建的directory,文件名,打开方式字符串)打开方式分三种 w:写，r：读，a:追加f:=utl_file.fopen('FILEPATH','test.txt','w');--2.读或写文件utl_file.put_line(文件类型变量，要写的文件内容)utl_file.put_line(f,'a');utl_file.put_line(f,'b');utl_file.put_line(f,'c');utl_file.put_line(f,'d');--3.关闭文件utl_file.fclose(文件类型变量)utl_file.fclose(f); end;declare--声名文件类型的变量f utl_file.file_type;--声名一个变量保存文件中的一行记录str varchar2(200);begin--1.打开文件utl_file.fopen(创建的directory,文件名,打开方式字符串)打开方式分三种 w:写，r：读，a:追加f:=utl_file.fopen('FILEPATH','test.txt','r');--2.读或写文件utl_file.get_line(文件类型变量，读文件内容的变量)utl_file.get_line(f,str);dbms_output.put_line(str);utl_file.get_line(f,str);dbms_output.put_line(str);utl_file.get_line(f,str);dbms_output.put_line(str);utl_file.get_line(f,str);dbms_output.put_line(str);--3.关闭文件utl_file.fclose(文件类型变量)utl_file.fclose(f); end;declare--声名文件类型的变量f utl_file.file_type;begin--1.打开文件utl_file.fopen(创建的directory,文件名,打开方式字符串)打开方式分三种 w:写，r：读，a:追加f:=utl_file.fopen('FILEPATH','test.txt','r');--2.读或写文件utl_file.get_line(文件类型变量，读文件内容的变量)loopdeclare--声名一个变量保存文件中的一行记录str varchar2(200);beginutl_file.get_line(f,str);dbms_output.put_line(str);exceptionwhen no_data_found thenexit; end;end loop;--3.关闭文件utl_file.fclose(文件类型变量)utl_file.fclose(f); end;本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！