Oracle工具包使用规则整理

翼幻幻幻幻

已于 2022-09-21 14:08:27 修改

阅读量795

点赞数 1

文章标签： oracle

于 2022-08-23 17:04:04 首次发布

本文链接：https://blog.csdn.net/m0_51271122/article/details/126481052

版权

dbms_alert

dbms_alert 用于生成并传递数据库预警消息
若某个数据库用户要用dbms_alert包，必须要用sys用户登陆，并授权
1.register
注册预警事件

begin
dbms_alert.register(‘alert’);
end;

2.remove
删除预警事件

begin
dbms_alert.remove(‘alert’);
end;

3.removeall
删除该会话的所有预警事件

begin
dbms_alert.removeall;
end;

4.set_defaults
设置检测预警事件的时间间隔

begin
dbms_alert.set_defaults(20);
end;

5.signal
为预警事件设置预警消息

begin
dbms_alert.signal(‘alert’,’hello’);
end;

6.waitany
该过程等待当前对话的任何预警事件，并且在预警事件发生时，输出相应的消息

dbms_alert.waitany(out name=>,out message => ,out status => ,in timeout );

7.waitone
该过程等待当前对话的某个特定预警事件，并且在预警事件发生时，输出相应的消息；

dbms_alert.waitone( in name =>,out message =>,out status => ,in timeout => );

例子：

首先应先授予用户使用DBMS_ALERT的权限，登录到sys用户下，执行一下语句：

grant execute on dbms_alert to scott;

发送进程（服务器端发出）

declare
v_alertname varchar2(30) := 'alert1';
begin
dbms_alert.signal(v_alertname, 'hello, this is sending process!');
commit; --必须commit，警报才会真正被发出
end;

接受进程（客户端接收）

declare
v_alertname varchar2(30):='alert1';
v_status    number;
v_msg        varchar2(100);
begin
dbms_alert.register(v_alertname);  --注册要接收信息的警报器
dbms_alert.waitone(v_alertname, v_msg, v_status);
if v_status != 0 then
dbms_output.put_line('error');
else
dbms_output.put_line(v_msg);
end if;
end;

方法一：先执行步骤2，再执行步骤3。这时可以获得警报
方法二：先执行步骤3，再执行步骤2。这时不能获得任何警报，执行步骤3的SESSION处于等待状态，当下一时间点再次执行步骤2时，就可以获得警报
警报可以是一对一，也可以是一对多的，即一个警报可以同时被多个接收者接收

dbms_assert

dbms_assert 主要用来执行输入校验
1.enquote_literal
向字符串的开头及结尾添加单引号

begin
dbms_assert.enquote_literal(proposedTableName);
end;

enquote_name

向字符串的开头及结尾添加双引号，如果字符串本身存在双引号，则不添加额外的双引号,第二个参数若为true，则会将字符串转为大写

begin
dbms_output.put_line(DBMS_ASSERT.ENQUOTE_NAME(proposedTableName,true));
end;

3.noop
对字符串不进行任何检查

begin
dbms_output.put_line(DBMS_ASSERT.NOOP(proposedTableName));
end;

4.qualified_sql_name
和simple_sql_name作用相同，但允许带.(小数点，比如用在记录成员、PACKAGE里面的函数、SCHEMA OWNER等)和@ (用在DBLINK)

begin
dbms_output.put_line(DBMS_ASSERT.QUALIFIED_SQL_NAME(proposedTableName));
end;

5.schema_name
检查字符串是否是一个已存在的schema名称

begin
dbms_output.put_line(DBMS_ASSERT.SCHEMA_NAME(proposedTableName));
end;

6.simple_sql_name
检查一个名字是否为SQL中可用的简单名字，名字必须以字母开头，随后可跟随数字、字母或_,$,#字符；名字必须以字母开头，随后可跟随数字、字母或_, $, # 字符；名字必须以字母开头，随后可跟随数字、字母或_, $, # 字符；允许带双引号,双引号之间可以是任意字符；假如双引号之内的名字本身就带有双引号，那么必须重复双引号两次来表示；输入参数如果前后带有空格被忽略。名字的长度没有被检测。

begin
dbms_output.put_line(DBMS_ASSERT.SIMPLE_SQL_NAME(proposedTableName));
end;

7.sql_object_name
验证输入参数字符串是否是现有SQL对象的限定SQL标识符

begin
dbms_output.put_line(DBMS_ASSERT.SQL_OBJECT_NAME(proposedTableName));
end;

dbms_output

dbms_output:主要用于输入和输出信息，使用过程put和put_lines可以将信息发送到缓冲区，使用过程get_line和get_lines可以显示缓冲区信息，该包用来输出plsql变量的值，属于系统用户sys

1.enable
用于激活本工具包，如果没有被激活，将无法调用本包的其他其余过程和函数，当调用该过程，缓冲区最大尺寸为1000000字节，最小为2000字节，默认为20000字节

begin
dbms_output.enable(20000);
end;

2.disable
用于禁止本包，并清除缓冲区的内容，当本包被禁止，将无法调用本包的其他其余过程和函数

begin
dbms_output.disable;
end;

3.put
将内容保存到缓冲区中，不包含换行符，等执行put_line时一起输出

begin
dbms_output.put('zg');
dbms_output.put(',伟大的祖国');
dbms_output.new_line;
end;

4.put_line
直接输出指定内容，包括换行符

begin
dbms_output.put_line('zg,伟大的祖国');
end;

5.new_line
在行尾添加换行符，在使用put时必须依靠new_line来添加换行符

begin
dbms_output.new_line;
end;

6.get_line
get_line用于取得缓冲区的单行信息，存在两个参数，第一个参数用于取得缓冲区的单行信息，第二个参数用于表示执行是否成功，0成功1表示没有取回数据

begin
dbms_output.get_line(varchar,flag);
end;

7.get_lines
取得缓冲区的多行信息,存在两个参数，第一个参数chararr表示被get_line取回的行，是一个chararr类型，此类型是一个varchar2（255）的嵌套表，会返回缓冲区的多行信息，第二个参数lineNumber如果作为输入参数表明要返回的行数，作为返回参数表示实际取回的行数

declare
chararr dbms_output.chararr;
lineNumber number := 3;
begin
dbms_output.put('zg');
dbms_output.put(',伟大的祖国');
dbms_output.get_lines(chararr,lineNumber);
end;

注意：当使用get_line取得缓冲区数据后再进行put操作时，会将缓冲区清空

declare
chararr dbms_output.chararr;
lineNumber number := 3;
begin
dbms_output.put('zg');
dbms_output.put(',伟大的祖国');
dbms_output.new_line;
dbms_output.put_line(',伟大的祖国');
dbms_output.new_line;
dbms_output.get_line(proposedTableNamek,k);
dbms_output.put_line(proposedTableNamek);
dbms_output.get_lines(v_lines,v);
dbms_output.put_line(v_lines(1));
end;

dbms_pipe
dbms_pipe:主要用于在同一实例的不同会话之间进行通信，如果用户要执行dbms_pipe中的过程或函数，则必须为用户授权
1.create_pipe
用于建立公用管道或私有管道，如果将参数private设置为true，则建立私有管道；如果设置为false，则建立公用管道

declare
flag int;
begin
flag := dbms_pipe.create_pipe('public_pipe',8192,false);
if flag=0 then
dbms_output.put_line('建立公用管道成功');
end if;
end;

2.pack_message
用于将消息写入到本地消息缓冲区
3.send_message
用于将本地消息缓冲区的内容发送到管道

create or replace procedure send_message（pipename varchar2,message varchar2）
is
flag number;
begin
flag := dbms_pipe.create_pipe(pipename);
if flag = 0 then
dbms_pipe.pack_message(message);
flag := dbms_pipe.send_message(pipename);
end if;
end;

4.receive_message
用于接收管道消息
5.unpack_message
用于将消息缓冲区的内容写入到变量中

create or replace procedure receive_message(pipename varchar2,message out varchar2)
is
flag number;
begin
flag := dbms_pipe.receive_message(pipename);
if flag = 0 then
dbms_pipe.unpack_message(message);
flag:=dbms_pipe.remove_pipe(pipename);
end if;
end;

6.next_item_type
用于确定本地消息缓冲区下一项的数据类型，如果该函数返回0,则表示管道没有任何消息，如果返回6,则表示下一项的数据类型为number，如果返回9,则表示下一项的数据类型为varchar2，如果返回11,则表示下一项的数据类型为rowid，如果返回12,则表示下一项的数据类型为date，如果返回23,则表示下一项的数据类型为raw
7.remove_pipe
用于清除管道中的内容
8.reset_buffer
用于复位管道缓冲区
9.unique_session_name
用于为特定会话返回唯一的名称，并且名称最长为30字节

dbms_random

可以生成随机数值或字符串的程序包
1.dbms_random.value
生成一个具有38位精度的数值，有两种用法，第一种用法没有参数，会返回一个范围从0.0到1.0，但不包括1.0的随机数

begin
for I in 1 .. 10 loop
dbms_output.put_line(round(dbms_random.value * 100));
end loop;
end;

第二种用法有两个参数，第一个参数指代下限，第二个参数指代上限，将会生成下限到上限的数字，但不包含上限

begin
for i in 1 .. 10 loop
dbms_output.put_line(trunc(dbms_random.value(1,101)));
end loop;
end;

2.dbms_random.string
生成随机的字符，有两个参数，第一个参数表示返回的字符格式，第二个参数表示字符串长度
参数：
U：返回大写字母
L：返回小写字母
A：返回大小写混合字母
X：返回由大写字母和数字组成的字符串
P：返回任何随机的字符，其中的字母为大写

select dbms_random.string('p',8) from dual;

3.dbms_random.random
返回一个任意大小的随机数，返回值为BINARY_INTEGER类型

select dbms_random.random from dual;

备注：dbms_random.value 和 dbms_random.random 两者之间有什么区别？
1.Order By dbms_random.value ，为结果集的每一行计算一个随机数，dbms_random.value 是结果集的一个列（虽然这个列并不在select list 中），然后根据该列排序，得到的顺序自然就是随机的啦。
2.value返回的是number类型，并且返回的值介于1和0之间，而random返回的是BINARY_INTEGER类型（以二进制形式存储的数字，运算的效率高于number）

4.dbms_random.normal
返回服从正态分布的一组数。此正态分布标准偏差为1，期望值为0。这个函数返回的数值中有68%是介于-1与+1之间，95%介于-2与+2之间，99%介于-3与+3之间。

begin
for i in 1 .. 10 loop
dbms_output.put_line(round(dbms_random.normal));
end loop;
end;

5.dbms_random.seed
用于生成一个随机数种子,设置种子的目的是可以重复生成随机数，用于调试。否则每次不同，难以调度

begin
dbms_random.seed('cux');
for i in 1 .. 10 loop
dbms_output.put_line(round(dbms_random.value * 100));
end loop;
end;

dbms_utility

用于数据类型处理和计算的包
https://docs.oracle.com/cd/F49540_01/DOC/server.815/a68001/dbms_uti.htm
1.dbms_utility.analyze_database
分析当前数据库中所有schema中的所有表

exec dbms_utility.analyze_schema('hr','compute’);
DBMS_UTILITY.ANALYZE_DATABASE (    method           VARCHAR2,     estimate_rows    NUMBER   DEFAULT NULL,     estimate_percent NUMBER   DEFAULT NULL,     method_opt       VARCHAR2 DEFAULT NULL);

2.dbms_utility.analyze_schema
分析给定schema（位于当前数据库中）中的所有表

DBMS_UTILITY.ANALYZE_SCHEMA (    schema           VARCHAR2,     method           VARCHAR2,     estimate_rows    NUMBER   DEFAULT NULL,     estimate_percent NUMBER   DEFAULT NULL,     method_opt       VARCHAR2 DEFAULT NULL);

3.dbms_utility.analyze_part_object
分析单个表

DBMS_UTILITY.ANALYZE_PART_OBJECT (    schema        IN VARCHAR2 DEFAULT NULL,    object_name   IN VARCHAR2 DEFAULT NULL,    object_type   IN CHAR     DEFAULT 'T',    command_type  IN CHAR     DEFAULT 'E',    command_opt   IN VARCHAR2 DEFAULT NULL,    sample_clause IN VARCHAR2 DEFAULT 'SAMPLE 5 PERCENT');

4.dbms_utility.compile_schema
因为升级等状况可能导致某schema下的对象变为invalid，这时就要对这些对象重新编译，用于编译指定模式中的所有过程、函数、包和触发器

DBMS_UTILITY.COMPILE_SCHEMA (    schema VARCHAR2);

5.dbms_utility.format_error_backtrace
返回一个格式化的字符串堆栈，堆栈中的程序及其行号可以回溯到错误被最先抛出的那一行
6.dbms_utility.format_call_stack
返回一个格式化的字符串，显示了执行调用堆栈，直至此函数的调用点处的所有过程或者函数的调用顺序

DBMS_UTILITY.FORMAT_CALL_STACK    RETURN VARCHAR2;

7.dbms_utility.format_error_stack
和SQLERRM一样，返回的是和当前错误（SQLCODE返回的值）所关联的错误信息

DBMS_UTILITY.FORMAT_ERROR_STACK    RETURN VARCHAR2;

http://blog.itpub.net/8520577/viewspace-1088970/
https://www.cnblogs.com/pompeii2008/p/7382640.html
8.dbms_utility.is_parallel_server
判断数据库是否以并行模式运行

DBMS_UTILITY.IS_PARALLEL_SERVER    RETURN BOOLEAN;

9.dbms_utility.get_time
返回当前时间，精确到毫秒

DBMS_UTILITY.GET_TIME    RETURN NUMBER;

10.get_parameter_value
检索数据库初始化参数设置（init.ora文件）

DBMS_UTILITY.GET_PARAMETER_VALUE (    parnam IN     VARCHAR2,    intval IN OUT BINARY_INTEGER,    strval IN OUT VARCHAR2)   RETURN BINARY_INTEGER;

11.dbms_utility.name_resolve
获取数据库对象的模式以及user的权限

DBMS_UTILITY.NAME_RESOLVE (    name          IN  VARCHAR2,     context       IN  NUMBER,    schema        OUT VARCHAR2,     part1         OUT VARCHAR2,     part2         OUT VARCHAR2,    dblink        OUT VARCHAR2,     part1_type    OUT NUMBER,     object_number OUT NUMBER);

12.dbms_utility.name_tokenize
将传入变量名称分解为"a[.b[.c]][@dblink]"格式，如果有引号就去掉引号，没有引号就转成大写

`DBMS_UTILITY.NAME_TOKENIZE (     name    IN  VARCHAR2,    a       OUT VARCHAR2,    b       OUT VARCHAR2,    c       OUT VARCHAR2,    dblink  OUT VARCHAR2,     nextpos OUT BINARY_INTEGER);`

https://www.ibm.com/docs/zh/db2/10.5?topic=dum-name-tokenize-procedure-parse-given-name-into-its-component-parts
13.dbms_utility.table_to_comma
将数组转化为用逗号分隔的字符串

DBMS_UTILITY.TABLE_TO_COMMA (     tab    IN  UNCL_ARRAY,     tablen OUT BINARY_INTEGER,    list   OUT VARCHAR2);
https://blog.csdn.net/daimin1983/article/details/3428804

14.dbms_utility.port_string
返回操作系统和数据库的TWO TASK PROTOCOL版本。例如,“VAX / VMX-7.1.0.0” 最大长度与端口相关

DBMS_UTILITY.PORT_STRING     RETURN VARCHAR2;

15.dbms_utility.db_version
返回数据库的版本信息

DBMS_UTILITY.DB_VERSION (
version OUT VARCHAR2,
compatibility OUT VARCHAR2);

16.dbms_utility.make_data_block_address
创建一个给定文件号和块号的数据块地址。数据块地址是用于在数据库中标识一个块的内部结构

DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS (    file  NUMBER,     block NUMBER)    RETURN NUMBER;

https://blog.csdn.net/ghostliming/article/details/51746343
17.dbms_utility.data_block_address_file
通过表里的数据定位文件号块号，通过dba地址定位所在的文件号块号

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (    dba NUMBER)    RETURN NUMBER;

18.dbms_utility.get_hash_value
计算字符串的哈希值

DBMS_UTILITY.GET_HASH_VALUE (    name      VARCHAR2,     base      NUMBER,     hash_size NUMBER)   RETURN NUMBER;

19.dbms_utility.analyze_part_object
等价于sql：

ANALYZE TABLE|INDEX [<schema>.]<object_name> PARTITION <pname> [<command_type>]   [<command_opt>] [<sample_clause>]
DBMS_UTILITY.ANALYZE_PART_OBJECT (    schema        IN VARCHAR2 DEFAULT NULL,    object_name   IN VARCHAR2 DEFAULT NULL,    object_type   IN CHAR     DEFAULT 'T',    command_type  IN CHAR     DEFAULT 'E',    command_opt   IN VARCHAR2 DEFAULT NULL,    sample_clause IN VARCHAR2 DEFAULT 'SAMPLE 5 PERCENT');

20.dbms_utility.exec_ddl_statement
执行parse_string中的DDL语句

DBMS_UTILITY.EXEC_DDL_STATEMENT (    parse_string IN VARCHAR2);

21.dbms_utility.current_instance
返回当前的数据库实例数目

DBMS_UTILITY.CURRENT_INSTANCE    RETURN NUMBER;

dbms_sql

用于执行动态sql，支持各种数据操作语言（DML）或数据定义语言（DDL）语句
详情参考
https://docs.oracle.com/cd/F49540_01/DOC/server.815/a68001/dbms_sql.htm
https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#i1027597
https://www.iteye.com/blog/zhangzhongjie-1948093

utl_file

用来实现对磁盘文件的I/O操作
https://blog.csdn.net/qq_37613533/article/details/81706402
1.utl_file.fopen
打开指定文件并返回一个文件句柄用于操作文件

utl_file.fopen(
file_location IN VARCHAR2,  --路径
file_name     IN VARCHAR2,   --文件名称
open_mode     IN VARCHAR2,   --打开模式 R 读 W 写 A 追加
max_linesize  IN BINARY_INTEGER DEFAULT NULL)
RETURN file_type;

2.utl_file.is_open
如果文件句柄处于打开状态，则返回True，否则返回False

utl_file.is_open(file IN FILE_TYPE) RETURN BOOLEAN;

utl_file.getline
读取文件中的一行数据

utl_file.getline(
file IN FILE_TYPE,
buffer OUT VARCHAR2,
len IN BINARY_INTEGER DEFAULT NULL);

4.utl_file.put
将字符写入文件

utl_file.put(file IN FILE_TYPE, buffer IN VARCHAR2);

5.utl_file.new_line
在当前位置输出新行或行终止符，必须使用NEW_LINE来结束当前行，或者使用PUT_LINE输出带有行终止符的完整行数据

utl_file.new_line(file IN FILE_TYPE, lines IN NATURAL := 1);

6.utl_file.put_line
输出一个字符串以及一个与系统有关的行终止符

utl_file.put_line(
file IN FILE_TYPE,
buffer IN VARCHAR2,
autoflush IN BOOLEAN DEFAULT FALSE);

7.utl_file.putf
以一个模版样式输出，使用至多5个参数，类似C中的printf

utl_file.putf(
file IN file_type,
format IN VARCHAR2,
arg1 IN VARCHAR2 DEFAULT NULL,
arg2 IN VARCHAR2 DEFAULT NULL, arg3 IN VARCHAR2 DEFAULT NULL,
arg4 IN VARCHAR2 DEFAULT NULL,
arg5 IN VARCHAR2 DEFAULT NULL);

8.utl_file.put_raw
接受一个RAW数据值作为输入，并将该值写入输出缓冲区

utl_file.put_raw(
file IN file_type,
buffer IN RAW,
autoflush IN BOOLEAN DEFAULT FALSE);

9.utl_file.fclose
关闭命名的文件

utl_file.fclose(file IN OUT file_type);

10.utl_file.fclose_all
关闭所有文件

utl_file.fclose_all

11.utl_file.frename
重命名一个文件，而且可以将文件从src_location移动到dest_location

utl_file.frename (
src_location IN VARCHAR2,
src_filename IN VARCHAR2,
dest_location IN VARCHAR2,
dest_filename IN VARCHAR2,
overwrite IN BOOLEAN DEFAULT FALSE);

12.utl_file.fremove
删除文件

utl_file.fremove(location IN VARCHAR2, filename IN VARCHAR2);

13.utl_file.fflush
强制将缓冲的数据写入文件。因为通常待写入文件的数据都是都在缓冲存储位置。当有必要去read一个仍然处于打开状态的文件时，FFLUSH就起作用了，例如在调试程序中，可以将调试的消息及时冲到文件中，已便于我们马上就能read这些内容

utl_file.fflush(file IN file_type);

14.utl_file.fcopy
把一个文件中文本拷贝到另外一个文件中

utl_file.fcopy(
src_location IN VARCHAR2,
src_filename IN VARCHAR2,
dest_location IN VARCHAR2,
dest_filename IN VARCHAR2,
start_line IN BINARY_INTEGER DEFAULT 1,
end_line IN BINARY_INTEGER DEFAULT NULL);