《Oracle》开发实战经典
包
-
包是一种模块化设计的主要实现手段,通过包可以将一个模块中所要使用的各个程序结构(过程,函数,游标,类型,变量)放到一起管理。同时包中所定义的程序结构也可以方便地进行相互调用。
-
在一个大型项目中,可能有很多模块,而每个模块又有自己的过程、函数等。而这些过程、函数默认是放在一起的(如在PL/SQL中,过程默认都是放在一起的,即Procedures中),这些非常不方便查询和维护,甚至会发生误删除的事件。所以通过使用包就可以分类管理过程和函数。
-
包分两部分,包规范和包体。
-
定义包规范
-
create or [replace] package 包名称 [authid current_user | definer] is | as 结构体名称定义(类型,过程,函数,游标,异常等) end[包名称];
-
定义包体
create or [replace] package body 包名称
is|as
结构实现(类型,过程,函数,游标,异常等)
begin
包初始化程序代码;
end[报名称];
-
--定义包规范 create or replace package p_stu as --定义结构体 type re_stu is record( rname student.name%type, rage student.age%type ); --定义游标 type c_stu is ref cursor; --定义函数 function numAdd(num1 number,num2 number)return number; --定义过程 procedure GetStuList(cid in varchar2,c_st out c_stu); end;
-
实现包规范,即包体,名称必须一致,同样的游标定义不能出现,但结构体可以,方法、过程必须实现。
-
只有当包头编辑成功后才能编辑包体.其中的函数名与过程名须和包头中的函数过程一样.
- 1 包说明和包体必须有相同的名字
- 2 包的开始没有begin语句,与存储过程和函数不同。
- 3 在包的说明部分定义函数和过程的名称和参数,具体实现在包体中定义。
- 4 在包内声明常量、变量、类型定义、异常、及游标时不使用declare。
- 5 包内的过程和函数的定义不要create or replace语句。
- 6 包声明和包体两者分离。
-
使用authid设置包权限
- 如果一个用户定义地包需要将执行权限授予其他用户,则考虑权限设置地问题。在包规范定义语法中存在权限地设置项authid[current_user|definer],如果包规范中地某个操作可以由使用者权限决定,则将其设置为current_user.在包规范中所定义所有子程序权限必须与包规范声明时定义的权限相同。
-
删除包
-
drop package 包名称; --删除包规范 drop package body 包名称; --删除包体
-
-
重新编译包
-
alter package 包名称 compile [debug] package|specification|body [reusesettings]; --package:重新编译包规范和包体 --specification:重新编译包规范 --body:重新编译包体
-
-
包的初始化
- 在程序第一次调用子程序,相关变量或类型引用,表对包进行默认的实例化操作,此时会将包的内容从硬盘读到内存中,而此时包会一直持续到整个会话结束。
-
包的纯度级别
-
pragma restrict_references(函数名称,纯度等级);
-
纯度等级 说明 WNDS 函数不能修改数据库表数据(无法使用DML更新) RNDS 函数不能都数据库表(既无法使用select查询) WNPS 函数不允许修改报的变量内容 RNPS 函数不允许读取包的变量内容
-
-
系统工具包
-
DBMS_outpt包
-
dbms_output包主要用于调试pl/sql程序,或者在sql*plus命令中显示信息(displaying message)和报表,譬如我们可以写一个简单的匿名pl/sql程序块,而该块出于某种目的使用dbms_output包来显示一些信息。
-
子程序名称 描述 enable 打开缓冲区,在serveroutput on的情况下,用来使dbms_output生效(默认即打开),其中缓冲区最大尺寸为1000000个字节,最小为20000个字节 disable 关闭缓冲区,在serveroutput on的情况下,用来使dbms_output失效 put 将内容写到内存缓冲区中,等到put_line时一起输出 put_line 不用多说了,输出字符 new_line 作为一行的结束,可以理解为写入buffer时的换行符 get_line(value, index) 获取缓冲区的单行信息;procedure get_line(line out varchar2,status out integer) .其中line被get_line取回的行,status是否取回一行,设置为1表示取回一行,设置为0表示没有取回数据。 get_lines(array, index) 以数组形式来获取缓冲区的多行信息;procedure get_lines(lines out chararr,numlines in out integer);其中line被get_line取回的行,是一个chararr类型,此类型是一个varchar2(255)的嵌套表,会返回缓存区多行信息:status是否取回一行,设置为1表示取回一行,设置为0表示没有取回数据;numlines:作为输入参数表示要返回的行数
-
-
DBMS_JOB包及数据库作业
-
为实现方便数据库自动执行某些操作后台操作,可以建立多个调度任务,而调度任务又被称为作业。
-
-
创建job:
-
declare jobno number; begin dbms_job.submit(jobno, —-job号 'your_procedure;', —-执行的存储过程, ';'不能省略 next_date, —-下次执行时间 'interval' —-每次间隔时间,interval以天为单位 ); end;
-
-
删除job:
-
dbms_job.remove(jobno);
-
-
修改要执行的操作:
-
job:dbms_job.what(jobno, what);
-
-
修改下次执行时间:
-
dbms_job.next_date(jobno, next_date);
-
-
修改间隔时间:
-
dbms_job.interval(jobno, interval);
-
-
启动job:
-
dbms_job.run(jobno);
-
-
停止job:
-
dbms.broken(jobno, broken, nextdate); –-broken为boolean值
-
-
user_jobs表结构
-
字段 类型 描述 job number 任务的唯一标示号 log_user varchar2(30) 提交任务的用户 priv_user varchar2(30) 赋予任务权限的用户 schema_user varchar2(30) 对任务作语法分析的用户模式 last_date date 最后一次成功运行任务的时间 last_sec varchar2(8) 如hh24:mm:ss格式的last_date日期的小时,分钟和秒 this_date date 正在运行任务的开始时间,如果没有运行任务则为null this_sec varchar2(8) 如hh24:mm:ss格式的this_date日期的小时,分钟和秒 next_date date 下一次定时运行任务的时间
-
-
-
数据库字典 user_jobs
-
Broken()过程更新一个已提交的工作的状态,典型地是用来把一个已破工作标记为未破工作。
-
这个过程有三个参数:job 、broken与next_date。 PROCEDURE Broken (job IN binary_integer, Broken IN boolean, next_date IN date :=SYSDATE) job参数是工作号,它在问题中唯一标识工作。 broken参数指示此工作是否将标记为破——TRUE说明此工作将标记为破,而FLASE说明此工作将标记为未破。 next_date参数指示在什么时候此工作将再次运行。此参数缺省值为当前日期和时间。
-
-
Change()过程用来改变指定工作的设置。
-
这个过程有四个参数:job、what 、next_date与interval。 PROCEDURE Change (job IN binary_integer, What IN varchar2, next_date IN date, interval IN varchar2) 此job参数是一个整数值,它唯一标识此工作。 What参数是由此工作运行的一块PL/SQL代码块。 next_date参数指示何时此工作将被执行。 interval参数指示一个工作重执行的频度。
-
-
Interval()过程用来显式地设置重执行一个工作之间的时间间隔数。
-
这个过程有两个参数:job与interval。 PROCEDURE Interval (job IN binary_integer, Interval IN varchar2) job参数标识一个特定的工作。interval参数指示一个工作重执行的频度。
-
-
ISubmit()过程用来用特定的工作号提交一个工作。
-
这个过程有五个参数:job、what、next_date、interval与no_parse。 PROCEDURE ISubmit (job IN binary_ineger, What IN varchar2, next_date IN date, interval IN varchar2, no_parse IN booean:=FALSE) 这个过程与Submit()过程的唯一区别在于此job参数作为IN型参数传递且包括一个 由开发者提供的工作号。如果提供的工作号已被使用,将产生一个错误。
-
-
Next_Date()过程用来显式地设定一个工作的执行时间。
-
这个过程接收两个参数:job与next_date。 PROCEDURE Next_Date(job IN binary_ineger, next_date IN date) job标识一个已存在的工作。next_date参数指示了此工作应被执行的日期与时间。
-
-
Remove()过程来删除一个已计划运行的工作。
-
这个过程接收一个参数: PROCEDURE Remove(job IN binary_ineger); job参数唯一地标识一个工作。这个参数的值是由为此工作调用Submit()过程返回的job参数的值。 已正在运行的工作不能由调用过程序删除。
-
-
Run()过程用来立即执行一个指定的工作。
-
这个过程只接收一个参数: PROCEDURE Run(job IN binary_ineger) job参数标识将被立即执行的工作。
-
-
Submit()过程,工作被正常地计划好。
-
这个过程有五个参数:job、what、next_date、interval与no_parse。 PROCEDURE Submit ( job OUT binary_ineger, What IN varchar2, next_date IN date, interval IN varchar2, no_parse IN booean:=FALSE) job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。 what参数是将被执行的PL/SQL代码块。 next_date参数指识何时将运行这个工作。 interval参数何时这个工作将被重执行。 no_parse参数指示此工作在提交时或执行时是否应进行语法分析——TRUE 指示此PL/SQL代码在它第一次执行时应进行语法分析, 而FALSE指示本PL/SQL代码应立即进行语法分析。
-
-
User_Export()过程返回一个命令,此命令用来安排一个存在的工作以便此工作能重新提交。
-
此程序有两个参数:job与my_call。 PROCEDURE User_Export(job IN binary_ineger, my_call IN OUT varchar2) job参数标识一个安排了的工作。my_call参数包含在它的当前状态重新提交此工作所需要 的正文。
-
-
What()过程应许在工作执行时重新设置此正在运行的命令。
-
这个过程接收两个参数:job与what。 PROCEDURE What (job IN binary_ineger, What IN OUT varchar2) job参数标识一个存在的工作。what参数指示将被执行的新的PL/SQL代码。
-
-
-
DBMS_ASSERT包
-
在编写sql语句时经常会出现一些敏感字符无法使用,在Oracle中提供了DBMS_ASSERT包可以通过字符串的转换。
-
子程序 描述 ENQUOTE_LITERAL 接受一个字符串并将字符串前后都加上单引号 ENQUOTE_NAME 接收一个字符串输入,会自动将字符串变成大写,最后在前后都加上双引号 SQL_OBJECT_NAME 验证字符串是否为有效的模式对象名 SCHEMA_NAME 判断给定字符串是否为有效的模式名,输入的字符串必须大写 QUALIFIFD_SQL_NAME 验证一个字符串是否为一个有效模式对象名 SIMPLE_SQL_NAME 验证一个字符串是否为一个有效模式对象名 NOOP 接收一个字符串输入并返回同样的内容,该函数不做任何验证,主要用于管理varchar2和clob数据 -
DBMS_ASSERT可以解决SQL注入攻击
- 在日后的动态SQL处理部分经常使用,利用语句中不对称的引号所引起的SQL注入漏洞
-
-
DBMS_LOG包
-
支持大对象的操作支持,用户可以直接利用该包实现对CLOB(大文本)或是BLOG(二进制数据)类型的列进行操作。
-
子程序 描述 getlength 取源文件长度 fileopen 打开源文件 loadfromfile 读取文件内容 fileclose 关闭文件 -
BFILE提供一个指向磁盘物理文件的定位器,所以它只是只读数据,不参与事务处理
-
如果想操作BFILE ,还需要另外一个数据库对象(DIRECTORY),此对象提供的是BFILE所在服务器中的文件系统目录指定别名(映射路径)。通过给使用者相应的权限,可以直接访问此映射路径进行文件的安全访问,而且此目录的对象所有者是sys,即需要通过管理员进行创建(管理员需要具备create any directory 权限)
-
create or replace directory 映射目录名称 as ‘磁盘目录路径’
-
-
触发器
- 触发器类似于过程和函数,,都具有程序主体部分(声明段,可执行段,异常处理段),但是与手工调用过程或函数不同的是,所有触法器都是依靠事件执行的。过程与函数是显示调用,是可以接收参数,但是触发器由于采用的隐式调用,所以不能接收参数的。
- 在Oracle触发器中主要分为DML触发器,instead-of(替代)触发器,DDL触发器,系统或数据库事件触发器。
- 数据操作(DML)触发器:此触发器是定义在Oracle表上的,当对表执行insert、update、delete操作时可以触发该触发器。如果按照对表中行级数据进行触发或语句级触发,又可以分为行级(row)触发器,语句级触发器,按照修改数据的前后触发触发器,又可以分为 after 触发器和before触发器之分。
- 数据定义操作(DDL)触发器:当对数据库对象进行create、alter、drop操作时,触发触发器进行一些操作记录保存、或者限定操作。
- **用户和系统事件触发器:**该类型的触发器是作用在Oracle数据库系统上,当进行数据库事件时,触发触发器,一般用来记录登录的相关信息。
- INSTEAD OF 触发器:此类型的触发器是作用在视图上,当用户对视图进行操作时,触发该触发器把相关的操作转换为对表进行操作。
- 复合触发器:指的是对数据操作(DML)触发器当中的多种类型触发器进行复合,比如;一个触发器当中包含着after(或before)的行级触发器和after(或before)的语句级触发器,来完成一些更为复杂的操作。
-
创建触发器
-
CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER } --触发时间 [instead of] {INSERT | DELETE | UPDATE [OF column [, column …]]} --触发事件 [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] ON [schema.]table_name | [schema.]view_name --触发对象 [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}] [FOR EACH ROW ] --触发频率 [follows 触发器名称] [disable] [WHEN condition] --触发条件 [declare] --触发操作 [程序声明部分;] [pragma autonomous_transaction;] begin codes; end[触发器名字]; / PL/SQL_BLOCK | CALL procedure_name;
-
CREATE [OR REPLACE] TRIGGER trigger_name:创建一个触发器,设置名称,如果选择or replace选项,则表示替换已有的触发器
-
{BEFORE | AFTER }:指的是该触发器的触发事件,是在操作之前(before)还是操作之后(after)
-
[instead of]:替代触发器,对于视图操作所定义的触发器类型。
-
{INSERT | DELETE | UPDATE [OF column [, column …]]}:触发事件,可以是数据表的增加(insert),修改(update),删除(delete)或是部分字段更新。
-
ON [schema.]table_name | [schema.]view_name: 触发对象,可以是数据表,视图,数据库,模式(用户)。
-
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]:对于“:old”,“:new”,“:parent”这3个标识符别名。、
-
[FOR EACH ROW ]:定义行级触发,如果不定义此语句则表示定义标记触发器
-
[follows 触发器名称]:配置多个触发器执行的先后顺序
-
[disable]:一个触发器建立之后默认是开启,可选用此选项禁用状态
-
[WHEN condition]:当满足指定条件才执行触发器操作
-
[declare]:触发器主体程序声明部分,定义变量或游标
-
[pragma autonomous_transaction;]:自治事务声明,编写此语句后会在触发器启动一个子事务处理,并可以使用commit提交事务
-
begin:程序主体部分
-
end:触发器结束标志
-
/:完结标志
- 触发器不接受任何的参数,并只能在产生某一触发事件才会自动调用
- 在一张数据表中最多只能有12个触发器,同一类型的触发器只能定义一次。
- (before insert,before insert for each row,after insert,after insert for each row,before update,before update for each row,after update,after update for each row,before delete,before delete for each row,after delete,after delete for each row,)
- 一个触发器最大为32KB,如果需要编写的代码比较多,可以通过过程或函数调用来完成
- 默认情况下,触发器不饿能使用事务的操作,或是采用自治事务进行处理
- 在一张表中,如果定义过多的触发器,则会造成DML性能的下降。
-
DML触发器
-
主要对DML语句进行触发,当用户进行增删改就会触发操作
-
create [or replace] trigger 触发器名称 [before|after] [insert|update|update of 列名 [,列名] | delete] on 表名 [for each row] [disable] [when 触发条件] [declare] [程序声明部分;] begin 程序代码部分; end[触发器]; /
-
触发器执行顺序
- before表级触发器
- before行级触发器
- 执行更新操作
- after行级触发器
- after表级触发器
-
表级DML触发器
- 表级触发器指的是正对全表数据的检查,每次更新之前或之后触发一次,不用配置for each row
- create any trigger:为任意用户创建触发器的权限
- alter any trigger:修改任意触发器的权限
- drop any trigger:删除任意触发器的权限
- 表级触发器指的是正对全表数据的检查,每次更新之前或之后触发一次,不用配置for each row
-
行级DML触发器
-
行级触发器是在表中每行记录出现更新时都会引起触发器操作。需要配置for each row选项
-
“:old”和“:new”标识符
-
触发语句 :old.字段 :new.字段 insert 未定义,字段内容均为null 插入结束后,为增加数据值 update 更新前的原始值 更新数据后的新值 delete 删除前的原始值 未定义,字段内容均为null -
可以使用referencing子句设置别名为以上两个标识符
-
触发器谓词
-
谓词 描述 inserting 如触发语句时insert,返回true,不是则为false updating 如触发语句时update,返回true,不是则为false deleting 如触发语句时delete,返回true,不是则为false
-
-
follow子句:为一个表创建了多个触发器,那么其在进行出触发时,是不会按照用户希望的触发顺序执行触发器的,,通过follow子句可以调节执行顺序。
-
变异表:当一张表执行了更新操作(增删改)就会变成一张变异表,如果在这样的表设置行级触发器便会出现ORA-04091异常。
-
复合触发器:既是行级触发器,也是表级触发器。在之前针对于不同级别的触发器,如果要在一张数据表上完成表级触发(BEFORE和AFTER)与行级触发(BEFORE和AFTER)则需要编写四个触发器才可以完成,而有了复合触发器之后,只需要一个触发器就可以定义完全部的四个功能
- 触发执行语句之前(BEFORE STATEMENT)
- 触发语句中的每一行发生变化之前(BEFORE EACH ROW)
- 触发语句中的每一行发生变化之后(AFTER EACH ROW)
- 触发执行语句之后(AFTER STATEMENT)
-
--复合触发器创建语法 CREATE [OR REPLACE] TRIGGER 触发器名称 FOR [INSERT | UPDATE | UPDATE OF 列名称 [,列名称,...] | DELETE] ON 表名称 COMPOUND TRIGGER [ BEFORE STATEMENT IS -- 语句执行前触发(表级) [ 声明部分 ; ] BEGIN 程序主体部分 ; END BEFORE STATEMENT ; ] [ BEFORE EACH ROW IS -- 语句执行前触发(行级) [ 声明部分 ; ] BEGIN 程序主体部分 ; END BEFORE EACH ROW ; ] [ AFTER STATEMENT IS -- 语句执行后触发(表级) [ 声明部分 ; ] BEGIN 程序主体部分 ; END AFTER STATEMENT ; ] [ AFTER EACH ROW IS -- 语句执行后触发(行级) [ 声明部分 ; ] BEGIN 程序主体部分 ; END AFTER EACH ROW ; ] END ; /
-
使用符合触发器可以很好的解决多个触发器操作中数据溢出的问题。
-
复合触发器就像一个多线程的进程操作,可以同时处理多个不同的事件,在复合触发器中最小实现要求定义至少一个事件处理块,并且只有DML能触发复合触发器工作。
-
-
instead-of触发器
-
如果一张视图有多张数据表组成,那么视图是无法直接进行更新,如果需要进行更新,那么要借助替代触发器来解决。
-
create [or replace] trigger 触发器名称 instead of [insert|update|update of 列名 [,列名] | delete] on 视图名称 [for each row] [when 触发条件] [declare] [程序声明部分;] begin 程序代码部分; end [触发器名称];
-
不可更新的视图
- 统计函数
- case函数或decode语句
- group by,having语句
- distinct消除重复列
- 集合运算连接
-
-
DDL触发器
-
DDL 触发器将激发,以响应各种数据定义语言 (DDL) 事件。 这些事件主要与以关键字 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 或 UPDATE STATISTICS 开头的 Transact-SQL 语句对应。 执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。
-
create [or replace] trigger 触发器名称 [before|after|instead of][DDL事件] on [database|schema] [when 触发条件] [declare] [程序声明部分;] begin 程序代码部分; end [触发器名称]; /
-
database:对数据库级别的触发,需要相应的管理员权限才可以创建
-
schema:对一个具体的模式(用户)的触发,每个用户都可以直接创建
-
-
自治事务
-
事务的“要么全部完成,要么什么都没完成”的本性会使将错误信息记入数据库表中变得很困难,因为当事务失败重新运行时,用来编写日志条目的INSERT语句还未完成。针对这种困境,Oracle提供了一种便捷的方法,即自治事务。自治事务从当前事务开始,在其自身的语境中执行。它们能独立地被提交或重新运行,而不影响正在运行的事务。正因为这样,它们成了编写错误日志表格的理想形式。在事务中检测到错误时,您可以在错误日志表格中插入一行并提交它,然后在不丢失这次插入的情况下回滚主事务。
-
因为自治事务是与主事务相分离的,所以它不能检测到被修改过的行的当前状态。这就好像在主事务提交之前,它们一直处于单独的会话里,对自治事务来说,它们是不可用的。然而,反过来情况就不同了:主事务能够检测到已经执行过的自治事务的结果。
-
在Oracle中每一个session都拥有独立的事务,而每一个事务处理过程中都会执行一系列的SQL更新操作。这些受到一个整体的事务控制,其他用户如果需要操作,必须在执行commit或callback之后才可以。
-
要创建一个自治事务,您必须在匿名块的最高层或者存储过程、函数、数据包或触发的定义部分中,使用PL/SQL中的PRAGMAAUTONOMOUS_TRANSACTION语句。在这样的模块或过程中执行的SQL Server语句都是自治的。
-
自治事务是主事务单独开启的独立事务,在自治事务处理期间,主事务会暂时挂起,一直等到自治事务执行commit和rollback之后才恢复主事务执行。