Oracle常用包函数存储过程.docx
- 文档编号:11167753
- 上传时间:2023-05-29
- 格式:DOCX
- 页数:35
- 大小:31.21KB
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_
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 常用 函数 存储 过程