Oracle获取表统计信息脚本分享
2026-01-16 08:21 潇湘隐者 阅读(9) 评论(0) 收藏 举报这两个脚本是Kerry Osborne分享的脚本, 经常会用到这两个脚本,是非常实用的SQL脚本.分享于此.希望对你有用.
脚本table_stats.sql
rem $Header$
rem $Name$
rem Copyright (c); 2004 by Hotsos Enterprises, Ltd.
rem
rem Retrieve statistics information for a table
rem
rem Usage: SQL>@hstats <table name>
rem
--
-- Modified by Kerry Osborne
-- commented out Histogram section
-- added prompts
-- added ability to run against table in another schmea
set echo off feed off
set serveroutput on size 1000000
accept ownname prompt 'Owner : '
accept tabname prompt 'Table : '
set lines 80
define v_desc = '&ownname..&tabname.'
desc &v_desc
set lines 200
declare
v_query varchar2(4000) ;
v_owner varchar2(30) := upper('&&ownname');
v_table varchar2(30) := upper('&&tabname');
v_max_colname number ;
v_max_ndv number ;
v_max_nulls number ;
v_max_bkts number ;
v_max_smpl number ;
v_max_endnum number ;
v_max_endval number ;
v_ct number ;
prev_col varchar2(30) ;
cursor col_stats is
select a.column_name, nvl(a.last_analyzed,to_date('01/01/1900','mm/dd/yyyy')) last_analyzed,
decode(a.nullable,'N','NOT NULL',' ') nullable,
a.num_distinct, a.density, a.num_nulls,
a.num_buckets, a.avg_col_len, a.sample_size
from all_tab_columns a
where a.owner = v_owner
and a.table_name = v_table ;
cursor hist_stats is
select b.column_name, b.endpoint_number, b.endpoint_value, b.endpoint_actual_value
from all_tab_histograms b
where b.owner = v_owner
and b.table_name = v_table
and (exists (select 1 from all_tab_columns
where num_buckets > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
or
exists (select 1 from all_tab_histograms
where endpoint_number > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
)
order by b.column_name, b.endpoint_number;
procedure print_table ( p_query in varchar2, p_date_fmt in varchar2 default 'dd-MON-yyyy hh24:mi:ss' ) is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_cs varchar2(255);
l_date_fmt varchar2(255);
-- Small inline procedure to restore the session's state.
-- We may have modified the cursor sharing and nls date format
-- session variables. This just restores them.
procedure restore
is
begin
if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
then
execute immediate
'alter session set cursor_sharing=exact';
end if;
if ( p_date_fmt is not null )
then
execute immediate
'alter session set nls_date_format=''' || l_date_fmt || '''';
end if;
dbms_sql.close_cursor(l_theCursor);
end restore;
begin
-- I like to see the dates print out with times, by default. The
-- format mask I use includes that. In order to be "friendly"
-- we save the current session's date format and then use
-- the one with the date and time. Passing in NULL will cause
-- this routine just to use the current date format.
if ( p_date_fmt is not null )
then
select sys_context( 'userenv', 'nls_date_format' )
into l_date_fmt
from dual;
execute immediate
'alter session set nls_date_format=''' || p_date_fmt || '''';
end if;
-- To be bind variable friendly on ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar. If not, set it to force so when we parse literals
-- are replaced with binds.
if ( dbms_utility.get_parameter_value
( 'cursor_sharing', l_status, l_cs ) = 1 )
then
if ( upper(l_cs) not in ('FORCE','SIMILAR'))
then
execute immediate
'alter session set cursor_sharing=force';
end if;
end if;
-- Parse and describe the query sent to us. We need
-- to know the number of columns and their names.
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );
-- Define all columns to be cast to varchar2s. We
-- are just printing them out.
for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;
-- Execute the query, so we can fetch.
l_status := dbms_sql.execute(l_theCursor);
-- Loop and print out each column on a separate line.
-- Bear in mind that dbms_output prints only 255 characters/line
-- so we'll see only the first 200 characters by my design...
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
substr( l_columnValue, 1, 200 ) );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
-- Now, restore the session state, no matter what.
restore;
exception
when others then
restore;
raise;
end;
begin
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Table Statistics');
dbms_output.put_line('==========================================================================================');
v_query := 'select table_name, last_analyzed, trim(degree) degree, partitioned,
num_rows, chain_cnt, blocks, empty_blocks, avg_space,
avg_row_len, monitoring, sample_size
from all_tables
where owner = ''' || UPPER(v_owner) || ''' and table_name = ''' || UPPER(v_table) || '''';
print_table (v_query);
v_ct := 0 ;
select count(1)
into v_ct
from all_tab_partitions
where table_owner = v_owner
and table_name = v_table;
if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Partition Information');
dbms_output.put_line('==========================================================================================');
v_query := 'select partition_name, last_analyzed, high_value,
num_rows, chain_cnt, blocks, empty_blocks,
avg_space, avg_row_len
from all_tab_partitions
where table_owner = ''' || UPPER(v_owner) || '''
and table_name = ''' || UPPER(v_table) || '''';
print_table (v_query);
end if ;
select max(length(column_name)) + 1, max(length(num_distinct)) + 3,
max(length(num_nulls)) + 1, max(length(num_buckets)) + 1,
max(length(sample_size)) + 1
into v_max_colname, v_max_ndv, v_max_nulls, v_max_bkts, v_max_smpl
from all_tab_columns
where owner = v_owner
and table_name = v_table ;
if v_max_nulls < 8 then
v_max_nulls := 8 ;
end if ;
if v_max_bkts < 10 then
v_max_bkts := 10 ;
end if ;
if v_max_smpl < 7 then
v_max_smpl := 7;
end if;
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Column Statistics');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' ' || rpad('Name',v_max_colname) || ' Analyzed Null? ' ||
rpad(' NDV',v_max_ndv) || ' ' || rpad(' Density',10) ||
rpad('# Nulls',v_max_nulls) || ' ' || rpad('# Buckets',v_max_bkts) || ' ' ||
rpad('Sample',v_max_smpl) || ' Avg Col Len');
dbms_output.put_line('==========================================================================================');
for v_rec in col_stats loop
dbms_output.put_line(rpad(v_rec.column_name,v_max_colname) || ' ' ||
to_char(v_rec.last_analyzed,'mm/dd/yyyy') || ' ' ||
v_rec.nullable || ' ' ||
rpad(v_rec.num_distinct,v_max_ndv) ||
to_char(v_rec.density,'9.999999') || ' ' ||
rpad(v_rec.num_nulls,v_max_nulls) || ' ' ||
rpad(v_rec.num_buckets,v_max_bkts) || ' ' ||
rpad(v_rec.sample_size,v_max_smpl) || ' ' ||
v_rec.avg_col_len );
end loop ;
select max(length(column_name)) + 1, max(length(endpoint_number)) + 1,
max(length(endpoint_value)) + 1
into v_max_colname, v_max_endnum, v_max_endval
from all_tab_histograms
where owner = v_owner
and table_name = v_table ;
if v_max_endnum < 12 then
v_max_endnum := 12 ;
end if ;
if v_max_endval < 16 then
v_max_endval := 16 ;
end if ;
select count(1)
into v_ct
from all_tab_histograms b
where b.owner = v_owner
and b.table_name = v_table
and (exists (select 1 from all_tab_columns
where num_buckets > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
or
exists (select 1 from all_tab_histograms
where endpoint_number > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
);
/* Histogram data commented out
if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Histogram Statistics');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' ' || rpad('Name',v_max_colname) || ' ' ||
rpad('Endpoint #',v_max_endnum) || ' ' ||
rpad('Endpoint Value',v_max_endval) || ' Endpoint Actual Value');
v_ct := 0 ;
for v_rec in hist_stats loop
if v_ct = 0 then
v_ct := 1 ;
prev_col := v_rec.column_name ;
elsif prev_col <> v_rec.column_name then
dbms_output.put_line('------------------------------------------------------------------------------------------');
prev_col := v_rec.column_name ;
end if ;
dbms_output.put_line(rpad(v_rec.column_name, v_max_colname) || ' ' ||
rpad(v_rec.endpoint_number,v_max_endnum) || ' ' ||
rpad(v_rec.endpoint_value,v_max_endval) || ' ' ||
substr(v_rec.endpoint_actual_value,1,20) ) ;
end loop ;
end if ;
*/
v_ct := 0;
select count(1)
into v_ct
from all_indexes a
where a.table_owner = v_owner
and a.table_name = v_table;
if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Index Information');
dbms_output.put_line('==========================================================================================');
v_query := 'select a.index_name, substr(a.index_type, 1, 4) index_type,
a.last_analyzed, a.degree, a.partitioned, a.blevel,
a.leaf_blocks, a.distinct_keys,
a.avg_leaf_blocks_per_key, a.avg_data_blocks_per_key,
a.clustering_factor, b.blocks blocks_in_table, b.num_rows rows_in_table
from all_indexes a, all_tables b
where (a.table_name = b.table_name and a.table_owner = b.owner)
and a.table_owner = ''' || UPPER(v_owner) || '''
and a.table_name = ''' || UPPER(v_table) || '''' ;
print_table (v_query);
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Index Columns Information');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line('Index Name Pos# Order Column Name Expression');
dbms_output.put_line('==========================================================================================');
end if;
end ;
/
set verify off feed off numwidth 15 lines 500 heading off
column column_name format a30 heading 'Column Name'
column index_name heading 'Index Name' format a30
column column_position format 999999999 heading 'Position'
column descend format a5 heading 'Order'
column column_expression format a40 heading 'Expression'
break on index_name skip 1
select b.index_name, b.column_position, b.descend, b.column_name, e.column_expression
from all_ind_columns b, all_ind_expressions e
where b.table_owner = upper('&ownname')
and b.table_name = UPPER('&tabname')
and b.index_name = e.index_name(+)
order by b.index_name, b.column_position, b.column_name
/
undefine ownname
undefine tabname
set feed on numwidth 12 lines 120
clear columns
clear breaks
set lines 155
set head on
脚本table_stats2.sql
set lines 155
rem $Header$
rem $Name$
rem Copyright (c); 2004 by Hotsos Enterprises, Ltd.
rem
rem Retrieve statistics information for a table
rem
rem Usage: SQL>@hstats <table name>
rem
--
-- Modified by Kerry Osborne
-- commented out Histogram section
-- added prompts
-- added ability to run against table in another schmea
-- added max and min values for column stats using display_raw function
-- ordered column stats by column name
-- Modified by Kerry Kong 2016-01-15 此脚本依赖函数DISPLAY_RAW, 执行前请执行脚本display_raw.sql创建此函数,并授权.
set echo off feed off
set serveroutput on size 1000000
accept ownname prompt 'Owner : '
accept tabname prompt 'Table : '
set lines 80
define v_desc = '&ownname..&tabname.'
desc &v_desc
set lines 200
declare
v_query varchar2(4000) ;
v_owner varchar2(30) := upper('&&ownname');
v_table varchar2(30) := upper('&&tabname');
v_max_colname number ;
v_max_ndv number ;
v_max_nulls number ;
v_max_bkts number ;
v_max_smpl number ;
v_max_avg_col_len number ;
v_max_low number ;
v_max_high number ;
v_max_endnum number ;
v_max_endval number ;
v_ct number ;
prev_col varchar2(30) ;
cursor col_stats is
select a.column_name, nvl(a.last_analyzed,to_date('01/01/1900','mm/dd/yyyy')) last_analyzed,
decode(a.nullable,'N','NOT NULL',' ') nullable,
a.num_distinct, a.density, a.num_nulls,
a.num_buckets, a.avg_col_len, nvl(to_char(a.sample_size),' ') sample_size,
display_raw(low_value,data_type) low_value, display_raw(high_value,data_type) high_value
from all_tab_columns a
where a.owner = v_owner
and a.table_name = v_table
order by a.column_name;
cursor hist_stats is
select b.column_name, b.endpoint_number, b.endpoint_value, b.endpoint_actual_value
from all_tab_histograms b
where b.owner = v_owner
and b.table_name = v_table
and (exists (select 1 from all_tab_columns
where num_buckets > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
or
exists (select 1 from all_tab_histograms
where endpoint_number > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
)
order by b.column_name, b.endpoint_number;
procedure print_table ( p_query in varchar2, p_date_fmt in varchar2 default 'dd-MON-yyyy hh24:mi:ss' ) is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_cs varchar2(255);
l_date_fmt varchar2(255);
-- Small inline procedure to restore the session's state.
-- We may have modified the cursor sharing and nls date format
-- session variables. This just restores them.
procedure restore
is
begin
if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
then
execute immediate
'alter session set cursor_sharing=exact';
end if;
if ( p_date_fmt is not null )
then
execute immediate
'alter session set nls_date_format=''' || l_date_fmt || '''';
end if;
dbms_sql.close_cursor(l_theCursor);
end restore;
begin
-- I like to see the dates print out with times, by default. The
-- format mask I use includes that. In order to be "friendly"
-- we save the current session's date format and then use
-- the one with the date and time. Passing in NULL will cause
-- this routine just to use the current date format.
if ( p_date_fmt is not null )
then
select sys_context( 'userenv', 'nls_date_format' )
into l_date_fmt
from dual;
execute immediate
'alter session set nls_date_format=''' || p_date_fmt || '''';
end if;
-- To be bind variable friendly on ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar. If not, set it to force so when we parse literals
-- are replaced with binds.
if ( dbms_utility.get_parameter_value
( 'cursor_sharing', l_status, l_cs ) = 1 )
then
if ( upper(l_cs) not in ('FORCE','SIMILAR'))
then
execute immediate
'alter session set cursor_sharing=force';
end if;
end if;
-- Parse and describe the query sent to us. We need
-- to know the number of columns and their names.
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );
-- Define all columns to be cast to varchar2s. We
-- are just printing them out.
for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;
-- Execute the query, so we can fetch.
l_status := dbms_sql.execute(l_theCursor);
-- Loop and print out each column on a separate line.
-- Bear in mind that dbms_output prints only 255 characters/line
-- so we'll see only the first 200 characters by my design...
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
substr( l_columnValue, 1, 200 ) );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
-- Now, restore the session state, no matter what.
restore;
exception
when others then
restore;
raise;
end;
begin
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Table Statistics');
dbms_output.put_line('==========================================================================================');
v_query := 'select table_name, last_analyzed, trim(degree) degree, partitioned,
num_rows, chain_cnt, blocks, empty_blocks, avg_space,
avg_row_len, monitoring, sample_size
from all_tables
where owner = ''' || UPPER(v_owner) || ''' and table_name = ''' || UPPER(v_table) || '''';
print_table (v_query);
v_ct := 0 ;
select count(1)
into v_ct
from all_tab_partitions
where table_owner = v_owner
and table_name = v_table;
if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Partition Information');
dbms_output.put_line('==========================================================================================');
v_query := 'select partition_name, last_analyzed, high_value,
num_rows, chain_cnt, blocks, empty_blocks,
avg_space, avg_row_len
from all_tab_partitions
where table_owner = ''' || UPPER(v_owner) || '''
and table_name = ''' || UPPER(v_table) || '''';
print_table (v_query);
end if ;
select max(length(column_name)) + 1, max(length(num_distinct)) + 3,
max(length(num_nulls)) + 1, max(length(num_buckets)) + 1,
max(length(sample_size)) + 1,
max(length(avg_col_len)) + 1,
max(length(display_raw(low_value,DATA_TYPE))) + 1,
max(length(display_raw(high_value,DATA_TYPE))) + 1
into v_max_colname, v_max_ndv, v_max_nulls, v_max_bkts, v_max_smpl, v_max_avg_col_len, v_max_low, v_max_high
from all_tab_columns
where owner = v_owner
and table_name = v_table ;
if v_max_nulls < 8 then
v_max_nulls := 8 ;
end if ;
if v_max_bkts < 10 then
v_max_bkts := 10 ;
end if ;
if v_max_smpl < 7 then
v_max_smpl := 7;
end if;
if v_max_avg_col_len < 7 then
v_max_avg_col_len := 7;
end if;
if v_max_low < 4 then
v_max_low := 4;
end if;
if v_max_high < 4 then
v_max_high := 4;
end if;
dbms_output.put_line('===============================================================================================================================');
dbms_output.put_line(' Column Statistics');
dbms_output.put_line('===============================================================================================================================');
dbms_output.put_line('' || rpad('Name',v_max_colname) || ' Analyzed Null? ' ||
lpad(' NDV',v_max_ndv) || ' ' || lpad('Density',8) || ' ' ||
lpad('# Nulls',v_max_nulls) || ' ' || lpad('# Buckets',v_max_bkts) || ' ' ||
lpad('Sample',v_max_smpl) || ' ' || lpad('Avg Len',v_max_avg_col_len) || ' ' ||
lpad('Min',v_max_low) || ' ' || lpad('Max',v_max_high));
dbms_output.put_line('===============================================================================================================================');
for v_rec in col_stats loop
dbms_output.put_line(rpad(v_rec.column_name,v_max_colname) || ' ' ||
to_char(v_rec.last_analyzed,'mm/dd/yyyy') || ' ' ||
v_rec.nullable || ' ' ||
lpad(v_rec.num_distinct,v_max_ndv) || ' ' ||
to_char(v_rec.density,'9.999999') || ' ' ||
lpad(v_rec.num_nulls,v_max_nulls) || ' ' ||
lpad(v_rec.num_buckets,v_max_bkts) || ' ' ||
lpad(v_rec.sample_size,v_max_smpl) || ' ' ||
lpad(v_rec.avg_col_len,v_max_avg_col_len) || ' ' ||
lpad(v_rec.low_value,v_max_low) || ' ' ||
lpad(v_rec.high_value,v_max_high));
end loop ;
select max(length(column_name)) + 1, max(length(endpoint_number)) + 1,
max(length(endpoint_value)) + 1
into v_max_colname, v_max_endnum, v_max_endval
from all_tab_histograms
where owner = v_owner
and table_name = v_table ;
if v_max_endnum < 12 then
v_max_endnum := 12 ;
end if ;
if v_max_endval < 16 then
v_max_endval := 16 ;
end if ;
select count(1)
into v_ct
from all_tab_histograms b
where b.owner = v_owner
and b.table_name = v_table
and (exists (select 1 from all_tab_columns
where num_buckets > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
or
exists (select 1 from all_tab_histograms
where endpoint_number > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
);
/* Histogram data commented out
if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Histogram Statistics');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' ' || rpad('Name',v_max_colname) || ' ' ||
rpad('Endpoint #',v_max_endnum) || ' ' ||
rpad('Endpoint Value',v_max_endval) || ' Endpoint Actual Value');
v_ct := 0 ;
for v_rec in hist_stats loop
if v_ct = 0 then
v_ct := 1 ;
prev_col := v_rec.column_name ;
elsif prev_col <> v_rec.column_name then
dbms_output.put_line('------------------------------------------------------------------------------------------');
prev_col := v_rec.column_name ;
end if ;
dbms_output.put_line(rpad(v_rec.column_name, v_max_colname) || ' ' ||
rpad(v_rec.endpoint_number,v_max_endnum) || ' ' ||
rpad(v_rec.endpoint_value,v_max_endval) || ' ' ||
substr(v_rec.endpoint_actual_value,1,20) ) ;
end loop ;
end if ;
*/
v_ct := 0;
select count(1)
into v_ct
from all_indexes a
where a.table_owner = v_owner
and a.table_name = v_table;
if v_ct > 0 then
dbms_output.put_line('===============================================================================================================================');
dbms_output.put_line(' Index Information');
dbms_output.put_line('===============================================================================================================================');
v_query := 'select a.index_name, substr(a.index_type, 1, 4) index_type,
a.last_analyzed, a.degree, a.partitioned, a.blevel,
a.leaf_blocks, a.distinct_keys,
a.avg_leaf_blocks_per_key, a.avg_data_blocks_per_key,
a.clustering_factor, b.blocks blocks_in_table, b.num_rows rows_in_table
from all_indexes a, all_tables b
where (a.table_name = b.table_name and a.table_owner = b.owner)
and a.table_owner = ''' || UPPER(v_owner) || '''
and a.table_name = ''' || UPPER(v_table) || '''' ;
print_table (v_query);
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Index Columns Information');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line('Index Name Pos# Order Column Name Expression');
dbms_output.put_line('==========================================================================================');
end if;
end ;
/
set verify off feed off numwidth 15 lines 500 heading off
column column_name format a30 heading 'Column Name'
column index_name heading 'Index Name' format a30
column column_position format 999999999 heading 'Position'
column descend format a5 heading 'Order'
column column_expression format a40 heading 'Expression'
break on index_name skip 1
select b.index_name, b.column_position, b.descend, b.column_name, e.column_expression
from all_ind_columns b, all_ind_expressions e
where b.table_owner = upper('&ownname')
and b.table_name = UPPER('&tabname')
and b.index_name = e.index_name(+)
order by b.index_name, b.column_position, b.column_name
/
undefine ownname
undefine tabname
set feed on numwidth 12 lines 120
clear columns
clear breaks
set lines 155
set head on
脚本table_stats2.sql依赖函数display_raw, 如果你想使用table_stats2.sql,必须在数据库创建函数display_raw,并根据实际使用场景进行授权.
create or replace function display_raw (rawval raw, type varchar2)
return varchar2
is
cn number;
cv varchar2(64);
cd date;
cnv nvarchar2(64);
cr rowid;
cc char(64);
cbf binary_float;
cbd binary_double;
begin
if (type = 'VARCHAR2') then
dbms_stats.convert_raw_value(rawval, cv);
return to_char(cv);
elsif (type = 'DATE') then
dbms_stats.convert_raw_value(rawval, cd);
return to_char(cd);
elsif (type = 'NUMBER') then
dbms_stats.convert_raw_value(rawval, cn);
return to_char(cn);
elsif (type = 'BINARY_FLOAT') then
dbms_stats.convert_raw_value(rawval, cbf);
return to_char(cbf);
elsif (type = 'BINARY_DOUBLE') then
dbms_stats.convert_raw_value(rawval, cbd);
return to_char(cbd);
elsif (type = 'NVARCHAR2') then
dbms_stats.convert_raw_value(rawval, cnv);
return to_char(cnv);
elsif (type = 'ROWID') then
dbms_stats.convert_raw_value(rawval, cr);
return to_char(cr);
elsif (type = 'CHAR') then
dbms_stats.convert_raw_value(rawval, cc);
return to_char(cc);
else
return 'UNKNOWN DATATYPE';
end if;
end;
/
show errors
扫描上面二维码关注我
如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！
本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
浙公网安备 33010602011771号