Oracle常用包函数存储过程.docx
《Oracle常用包函数存储过程.docx》由会员分享，可在线阅读，更多相关《Oracle常用包函数存储过程.docx（35页珍藏版）》请在冰点文库上搜索。
Oracle常用包函数存储过程
Oracle常用包、存储过程、函数
常用包、存储过程
1dbms_output
作用：
输入和输出信息，使用过程PUT和PUT_LINES可以将信息发送到缓冲区，使用过程GET_LINE和GET_LINES可以显示缓冲区信息。
1.1enable
该过程用于激活对过程PUT，PUT_LINE，GET_LINE，GET_LINES的调用。
语法如下：
dbms_output.enable（buffer_sizeinintegerdefault20000）;
1.2disable
该过程用于禁止对过程PUT，PUT_LINE，GET_LINE，GET_LINES的调用。
语法如下：
dbms_output.disable;
1.3put和put_line
过程put_line用于将一个完整行的信息写入到缓冲区中，过程put则用地分块建立行信息，当使用过程put_line时，会自动在行的尾部追加行结束符；当使用过程put时,需要使用过程new_line追加行结束符。
示例如下:
setserverouton
begin
dbms_output.put_line（'伟大的中华民族'）;
dbms_output.put（'中国'）;
dbms_output.put（',伟大的祖国'）;
dbms_output.new_line;
end;
/
伟大的中华民族
中国,伟大的祖国
1.4new_line
该过程用于在行的尾部追加行结束符。
当使用过程PUT时，必须调用NEW_LINE过程来结束行。
1.5get_line和get_lines
过程get_line用于取得缓冲区的单行信息，过程get_lines用于取得缓冲区的多行信息。
2dbms_job
作用：
安排和管理作业队列，通过使用作业，可以使ORACLE数据库定期执行特定的任务。
2.1submit
用于建立一个新作业。
当建立作业时，需要给作业要执行的操作，作业的下次运行日期及运行时间间隔。
语法如下：
dbms_out.submit（
joboutbinary_integer,
whatinvarchar2,
next_dateindatedefaultsysdate,
intervalinvarchar2default'null',
no_parseinbooleandefaultfalse,
instanceinbinary_integerdefaultany_instance,
forceinbooleandefaultfalse）;
注：
job用于指定作业编号；what用于指定作业要执行的操作；next_date用于指定作业的下次运行日期；interval用于指定运行作业的时间间隔；no_parse用于指定是否解析与作业相关的过程；instance用于指定哪个例程可以运行作业；force用于指定是否强制运行与作业相关的例程。
示例如下：
varjobnonumber
begin
dbms_job.submit（:
jobno,
'dbms_ddl.analyze_object（''table'',
''scott'',''emp'',''compute''）;',
sysdate,'sysdate+1'）;
commit;
end;
/
2.2remove
删除作业队列中的特定作业。
示例如下：
SQL>execdbms_job.remove
（1）;
2.3change
用于改变与作业相关的所有信息,包括作业操作,作业运行日期以及运行时间间隔等。
示例如下：
SQL>execdbms_job.change（2,null,null,'sysdate+2'）;
2.4what
用于改变作业要执行的操作。
示例如下：
SQL>execdbms_job.what（2,'dbms_stats.gather_table_stats->（''scott'',''emp''）;'）;
2.5next_date
用于改变作业的下次运行日期。
示例如下：
SQL>execdbms_job.next_date（'2','sysdate+1'）;
2.6instance。
用于改变作业的例程。
示例如下：
SQL>execdbms_job.instance（2,1）;
2.7interval
用于改变作业的运行时间间隔。
示例如下：
SQL>execdbms_job.interval（2,'sysdate+1/24/60'）;
2.8broken
用于设置作业的中断标识。
示例如下：
SQL>execdbms_job.broken（2,true,'sysdate+1'）;
2.9run
用于运行已存在的作业。
示例如下:
sql>execdbms_job.run
（1）;
3dbms_pipe
作用：
在同一例程程的不同会话之间进行管道通信。
注意：
如果用户要执行包dbms_pipe中的过程和函数，则必须要为用户授权。
sql>connsys/oracleassysdba;
sql>grantexecuteondbms_pipetoscott;
3.1create_pipe
该函数用于建立公用管道或私有管道。
如果将参数private设置为TRUE，则建立私有管道；如果设置为FALSE，则建立公用管道。
示例如下：
declare
falgint;
begin
flag:
=dbms_pipe.create_pipe（'public_pipe',8192,false）;
ifflag=0then
dbms_output.put_line（'建立公用管道成功'）;
endif;
end;
/
3.2 pack_message
该过程用于将消息写入到本地消息缓冲区。
3.3send_message
该函数用于将本地消息缓冲区中的内容发送到管道。
3.4receive_message
该函数用于接收管道消息
3.5.next_item_type
该函数用于确定本地消息缓冲区下一项的数据类型，如果该函数返回0，则表示管道没有任何消息；如果返回6，则表示下一项的数据类型为number；如果返回9，则表示下一项的数据类型为varchar2；如果返回11，则表示下一项的数据类型为rowid；如果返回12，则表示下一项的数据类型为date；如果返回23，则表示下一项的数据类型为raw；
3.6unpack_message
该过程用于将消息缓冲区的内容写入到变量中
3.7remove_pipe
该函数用于删除已经建立的管道
3.8purge
该过程用于清除管道中的内容
3.9reset_buffer
该过程用于复位管道缓冲区
3.10.unique_session_name
该函数用于为特定会话返回惟一的名称,并且名称的最长度为30字节
4dbms_alert
作用：
用于生成并传递数据库预警信息。
使用包DBMS_ALERT，则必须以SYS登陆，为该用户授予执行权限.
sql>connsys/oracleassysdba
sql>grantexecuteondbms_alerttoscott;
4.1register
注册预警事件。
示例如下：
sql>execdbms_alter.register（'alter1'）;
4.2remove
删除会话不需要的预警事件。
4.3removeall
删除当前会话所有已注册的预警事件。
语法如下：
dbms_alter.removeall;
4.4set_defaults
设置检测预警事件的时间间隔,默认时间间隔为5秒。
4.5signal
用于指定预警事件所对应的预警消息。
4.6waitany
用于等待当前会话的任何预警事件，并且在预警事件发生时输出相应信息，在执行该过程之前，会隐含地发出COMMIT。
语法如下：
dbms_alter.waitany（
nameoutvarchar2,messageoutvarchar2,
statusoutinteger,timeoutinnumberdefaultmaxwait
）;
注：
status用于返回状态值。
返回0表示发生了预警事件；返回1表示超时；;timeout用于设置预警事件的超时时间。
4.7.waitone
用于等待当前会话的特定预警事件，并且在发生预警事件时输出预警消息。
语法同上
5.dbms_transaction
作用：
用于在过程、函数和包中执行SQL事务处理语句。
5.1read_only
用于开始只读事务，其作用与SQL语句SETTRANSACTIONREADONLY完全相同。
5.2read_write
用于开始读写事务,------------------------------------WRITE-------
5.3advise_rollback
用于建议回退远程数据库的分布式事务
5.4advise_nothing
用于建议远程数据库的分布式事务不进行任何处理
5.5advise_commit
用于建议提交远程数据库的分布式事务
5.6user_rollback_segment
用于指定事务所要使用的回滚段
5.7commit_comment
用于在提交事务时指定注释.
5.8commit_force
用于强制提交分布式事务.
5.9commit
用于提交当前事务
5.10savepoint
用于设置保存点
5.11rollback
用于回退当前事务
5.12rollback_savepoint
用于回退到保存点
5.13rollback_force
用于强制回退分布式事务
5.14begin_discrete_transaction
用于开始独立事务模式
5.15.purge_mixed
用于清除分布式事务的混合事务结果
5.16purge_lost_db_entry
用于清除本地数据库所记载的远程事务入口,该事务入口操作因为远程数据库问题未能在远程数据库完成.
5.17local_transaction_id
用于返回当前事务的事务标识号
5.18.step_id
用于返回排序DML事务的惟一正整数
6.dbms_session
作用：
提供了使用PL/SQL实现ALTERSESSION命令，SETROLE命令和其他会话信息的方法.。
6.1set_identifier
用于设置会话的客户ID号
6.2set_context
用于设置应用上下文属性
6.3clear_context
用于清除应用上下文的属性设置
6.4clear_identifier
用于删除会话的set_client_id.
6.5set_role
用于激活或禁止会话角色
6.6set_sql_trace
用于激活或禁止当前会话的SQL跟踪
语法如下:
dbms_session.set_sql_trace（sql_straceboolean）;
6.7set_nls
用于设置NLS特征
语法如下:
dbms_session.set_nls（paramvarchar2,valuevarchar2）;
6.8close_database_link
用于关闭已经打开的数据库链
6.9reset_package
用于复位当前会话的所有包,并且会释放包状态
6.10modify_package_state
用于修改当前会话的PL/SQL程序单元的状态
语法如下:
dbms_session.modify_package_state（action_flagsinpls_integer）;
6.11unique_session_id
用于返回当前会话的惟一ID标识符
6.12is_role_enabled
用于确定当前会话是否激活了特定角色.
语法如下:
dbms_session.is_role_enabled（rolenamevarchar2）
returnboolean;
6.13is_session_alive
用于确定特定会话是否处于活动状态.
6.14set_close_cached_open_cursors
用于打开或关闭close_cached_open_cursors
6.15free_unused_user_meory
用于在执行了大内在操作（超过100K）之后回收未用内存
6.16set_context
设置应用上下文属性的值
6.17list_context
用于返回当前会话原命名空间和上下文列表
6.18swith_current_consumer_group
用于改变当前会话的资源使用组
7dbms_rowid
作用：
用于在PL/SQL程序和SQL语句中取得行标识符的信息并建立ROWID，通过该包可以取得行所在的文件号，行所在文件的数据块号，行所在数据块的行号，以及数据库对象号等消息.
7.1rowid_create
建立ROWID
语法如下:
dbms_rowid.rowid_create（
rowid_typeinnumber,object_numberinnumber,
relative_fnoinnumber,block_numberinnumber,
row_numberinnumber）
returnrowid;
注：
rowid_type用于指定ROWID类型（0：
受限ROWID；1：
扩展ROWID）；object_number用于指定数据对象号；relative_fno用于指定相对文件号；block_number用于指定在文件中的数据块号；row_number用于指定在数据块中的行号。
7.2rowid_info
用于取得特定ROWID的详细信息.
7.3rowid_type
用于返回特定ROWID的类型
7.4rowid_object
用于取得特定ROWID所对应的数据对象号
7.5rowid_relative_fno
用于取得特定ROWID所对应的相对文件号
7.6rowid_block_number
用于返回特定ROWID在数据文件中所对应的数据块号.
7.7rowid_row_number
用于返回特定ROWID在数据块中所对应的行号.
7.8rowid_to_obsolute_fno
用于返回特定ROWID所对应的绝对文件号
7.9rowid_to_extended
用于将受限rowid转变为扩展rowid
7.10rowid_to_restricted
用于将扩展rowid转换为受限rowid
7.11rowid_verify
检查是否可以将受限rowid转变为扩展rowid
8dbms_rls
作用：
只适用于OracleEnterpriseEdition，它用于实现精细访问控制,并且精细访问控制是通过在SQL语句中动态增加谓词（WHERE子句）来实现的。
通过使用ORACLE的精细访问控制特征，可以使不同数据库用户在执行相同SQL语句时操作同一张表上的不同数据。
9dbms_ddl
作用：
提供了在PL/SQL块中执行DDL语句的方法，并且也提供了一些DDL的特殊管理方法。
10dbms_shared_pool
作用：
提供了对共享池的一些过程和函数访问，它使用户可以显示共享池中的对象尺寸，定对象到共享池，清除绑定到共享池的对象，为了使用该包，必须运行dbmspool.sql脚本来建立该包。
11dbms_random
作用：
提供了内置的随机数生成器，可以用于快速生成随机数。
12dbms_logmnr
作用：
通过使用包DBMS_LOGMNR和DBMS_LOGMNR_D，可以分析重做日志和归档日志所记载的事务变化，最终确定误操作（例如DROPTABLE）的时间，跟踪用户事务操作，跟踪并还原表的DML操作。
13dbms_flashback
作用：
用于激活或禁止会话的flashback特征，为了使得普通用户可以使用该包，必须要将执行该包的权限授予这些用户。
14dbms_obfuscation_toolkit
作用：
用于加密和解密数据，另外还可以生成密码检验和，通过加密输入数据，可以防止黑客或其他用户窃取私有数据；而通过结合使用加密和密码检验和，可以防止黑客破坏初加密的数据。
当使用该包加密数据时，要求被加密数据的长度必须为8字节的整数倍；当使用DES算法加密数据时，密钥长度不能低于8字节；当使用DES3算法加密数据时，密钥长度不能低于16字节。
15dbms_space
作用：
用于分析段增长和空间的需求。
16dbms_space_admin
作用：
提供了局部管理表空间的功能。
17dbms_tts
作用：
用于检查表空间集合是否是自包含的，并在执行了检查之后，将违反自包含规则的信息写入到临时表TRANSPORT_SET_VIOLATIONS中。
18dbms_repair
作用：
用于检测，修复在表和索引上的损坏数据块。
19dbms_resource_manager
作用：
用于维护资源计划，资源使用组和资源计划指令。
包dbms_resource_manager_privs用于维护与资源管理相关的权限。
20dbms_stats
作用：
用于搜集，查看，修改数据库对象的优化统计信息。
21utl_file
作用：
用于读写OS文件。
使用该包访问OS文件时，必须要为OS目录建立相应的DIRECTORY对象。
当用户要访问特定目录下的文件时，必须要具有读写DIRECTORY对象的权限。
在使用UTL_FILE包之前，应首先建立DIRECTORY对象。
示例1：
在PL/SQL3.3以上的版本中，UTL_FILE包允许用户通过PL/SQL读写操作系统文件。
DECALRE
FILE_HANDLEUTL_FILE.FILE_TYPE;
BEGIN
FILE_HANDLE:
=UTL_FILE.FOPEN（'C:
\','TEST.TXT','A'）;
UTL_FILE.PUT_LINE（FILE_HANDLE,'HELLO,ITiSATESTTXTFILE'）;
UTL_FILE.FCLOSE（FILE_HANDLE）;
END;
示例2：
在Oracle中写操作系统文件，如写日志转自。
可以利用utl_file包，但是，在此之前，要注意设置好Utl_file_dir初始化参数
parameter:
textContextinvarchar2日志内容
desc:
·写日志,把内容记到服务器指定目录下
·必须配置Utl_file_dir初始化参数，并保证日志路径与Utl_file_dir路径一致或者是其中一个
CREATEORREPLACEPROCEDUREsp_Write_log（text_contextVARCHAR2）IS
file_handleutl_file.file_type;
Write_contentVARCHAR2（1024）;
Write_file_nameVARCHAR2（50）;
BEGIN
--openfile
write_file_name:
='db_alert.log';
file_handle:
=utl_file.fopen（'/u01/logs',write_file_name,'a'）;
write_content:
=to_char（SYSDATE,'yyyy-mm-ddhh24:
mi:
ss'）||'||'||text_context;
--writefile
IFutl_file.is_open（file_handle）THEN
utl_file.put_line（file_handle,write_content）;
ENDIF;
--closefile
utl_file.fclose（file_handle）;
EXCEPTION
WHENOTHERSTHEN
BEGIN
IFutl_file.is_open（file_handle）THEN
utl_file.fclose（file_handle）;
ENDIF;
EXCEPTION
WHENOTHERSTHEN
NULL;
END;
ENDsp_Write_log;
22utl_inaddr
作用：
用于取得局域网或Internet环境中的主机名和IP地址。
23dbms_mail
连接oracle*mail
24dbms_lock
进行复杂的锁机制管理。
25dbms_lob
提供对OracleLOB数据类型进行操作的功能。
26dbms_sql
允许用户使用动态SQL,构造和执行任意DML和DDL语句。
26.1OPEN_CURSOR
返回新游标的ID值
26.2PARSE
解析要执行的语句
26.3BIND_VARIABLE
将给定的数量与特定的变量相连接
26.4DEFINE_COLOUMN
定义字段变量，其值对应于指定游标中某个位置元素的值（仅用于SELECT语句）
26.5EXECUTE
执行指定的游标
26.6EXECUTE_AND_FETCH
执行指定的游标并取记录
26.7FETCH_ROWS
从指定的游标中取出记录
26.8COLUMN_VALUE
返回游标中指定位置的元素
26.9IS_OPEN
当指定的游标状态为OPEN时返回真值
26.10CLOSE_CURSOR
关闭指定的游标并释放内存
26.11LAST_ERROR_POSITION
返回出错SQL语句的字节偏移量
26.12LAST_ROW_ID
返回最后一条记录的ROWID
26.13LAST_SQL_FUNCTION_CODE
返回语句的SQL FUNCTION CODE
示例：
CREATE OR REPLACE procedure dml_sql （the_rq varchar2） as
The_c1Integer;
The_resultInteger;--dml_sql_result
M_jlsnumber;
The_xhvarchar2
（2）;
Begin
The_xh:
=lpad（ltrim（the_
升级会员 