oracle存储过程替换字符,oracle的存储过程实现字段类型的替换

抱紧上仙

于 2021-04-03 17:51:17 发布

阅读量520

点赞数

文章标签： oracle存储过程替换字符

create or replace procedure convert_date_to_timestamp

/**************************

function: 将数据库中所有的表中含DATE类型的字段改为TIMESTAMP类型；

需要建立下列类型表：

create table type_table_info (

table_name varchar2(30),

column_name varchar2(30),

data_type varchar2(100)

)

***************************/

v_query_base_sql varchar2(100) := 'SELECT table_name, column_name, data_type FROM all_tab_cols WHERE table_name = ''';

v_query_table_sql varchar2(150);

v_alter_sql varchar2(100);

type table_type is table of type_table_info%rowtype;

table_array table_type;

begin

for c_tabs in (

select table_name from user_tables where table_name <> 'type_table_info'

)

loop

v_query_table_sql := v_query_base_sql || c_tabs.table_name || '''';

execute immediate v_query_table_sql bulk collect into table_array;

for i in table_array.first .. table_array.last

loop

--DBMS_OUTPUT.put_line(table_array(i).column_name || ':' || table_array(i).data_type);

if table_array(i).data_type = 'DATE' then

v_alter_sql := 'alter table ' || table_array(i).table_name || ' modify ' || table_array(i).column_name || ' timestamp';

DBMS_OUTPUT.put_line(table_array(i).column_name || ': ' || v_alter_sql);

execute immediate v_alter_sql;

end if;

end loop;

end convert_date_to_timestamp;

create or replace procedure convert_number_to_decimal

/**************************

function: 将数据库中所有的表中含DATE类型的字段改为TIMESTAMP类型；

需要建立下列类型表：

create table type_table_info (

table_name varchar2(30),

column_name varchar2(30),

data_type varchar2(100)

)

***************************/

v_query_base_sql varchar2(100) := 'SELECT table_name, column_name, data_type FROM all_tab_cols WHERE table_name = ''';

v_query_table_sql varchar2(150);

v_alter_sql varchar2(100);

type table_type is table of type_table_info%rowtype;

table_array table_type;

begin

for c_tabs in (

select table_name from user_tables where table_name <> 'type_table_info'

)

loop

v_query_table_sql := v_query_base_sql || c_tabs.table_name || '''';

execute immediate v_query_table_sql bulk collect into table_array;

for i in table_array.first .. table_array.last

loop

--DBMS_OUTPUT.put_line(table_array(i).column_name || ':' || table_array(i).data_type);

if table_array(i).data_type = 'NUMBER' then

v_alter_sql := 'alter table ' || table_array(i).table_name || ' modify ' || table_array(i).column_name || ' decimal(65)';

DBMS_OUTPUT.put_line(table_array(i).column_name || ': ' || v_alter_sql);

execute immediate v_alter_sql;

end if;

end loop;

end convert_numbr_to_decimal;

create or replace procedure convert_varchar2_to_varchar /************************** function: 将数据库中所有的表中含DATE类型的字段改为TIMESTAMP类型；需要建立下列类型表： create table type_table_info ( table_name varchar2(30), column_name varchar2(30), data_type varchar2(100) ) ***************************/ is v_query_base_sql varchar2(100) := 'SELECT table_name, column_name, data_type FROM all_tab_cols WHERE table_name = '''; v_query_table_sql varchar2(150); v_alter_sql varchar2(100); type table_type is table of type_table_info%rowtype; table_array table_type; begin for c_tabs in ( select table_name from user_tables where table_name <> 'type_table_info' ) loop v_query_table_sql := v_query_base_sql || c_tabs.table_name || ''''; execute immediate v_query_table_sql bulk collect into table_array; for i in table_array.first .. table_array.last loop --DBMS_OUTPUT.put_line(table_array(i).column_name || ':' || table_array(i).data_type); if table_array(i).data_type = 'VARCHAR2(200)' then v_alter_sql := 'alter table ' || table_array(i).table_name || ' modify ' || table_array(i).column_name || ' VARCHAR2(500)'; DBMS_OUTPUT.put_line(table_array(i).column_name || ': ' || v_alter_sql); execute immediate v_alter_sql; end if; end loop; end loop; end convert_varchar2_to_varchar;