Generating sql insert into for Oracle
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Generating sql insert into for Oracle,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含13492字,纯文字阅读大概需要20分钟。
内容图文
![Generating sql insert into for Oracle](/upload/InfoBanner/zyjiaocheng/1320/cd392e563e6b47758ced6fc2df4d1c94.jpg)
create or replace FUNCTION fn_gen_inserts ( p_sql CLOB, p_new_owner_name VARCHAR2 , p_new_table_name VARCHAR2 ) RETURN CLOB IS l_cur NUMBER ; l_sql CLOB : = p_sql; l_ret NUMBER ; l_col_cnt NUMBER ; l_rec_tab dbms_sql.desc_tab; l_separator CHAR(1) :=‘!‘; l_clob CLOB; l_clob_line CLOB; l_clob_ins CLOB; l_clob_all CLOB; l_line CLOB :=‘-----------------------------------‘; cons_date_frm VARCHAR2(32) :=‘DD.MM.YYYY HH24:MI:SS‘; cons_timestamp_frm VARCHAR2(32) :=‘DD.MM.YYYY HH24:MI:SSXFF‘; cons_timestamp_wtz_frm VARCHAR2(32) :=‘DD.MM.YYYY HH24:MI:SSXFF TZR‘; cons_varchar2_code NUMBER :=1; cons_nvarchar2_code NUMBER :=1; cons_number_code NUMBER :=2; cons_float_code NUMBER :=2; cons_long_code NUMBER :=8; cons_date_code NUMBER :=12; cons_binary_float_code NUMBER :=100; cons_binary_double_code NUMBER :=101; cons_timestamp_code NUMBER :=180; cons_timestamp_wtz_code NUMBER :=181; cons_timestamp_lwtz_code NUMBER :=231; cons_interval_ytm_code NUMBER :=182; cons_interval_dts_code NUMBER :=183; cons_raw_code NUMBER :=23; cons_long_raw_code NUMBER :=24; cons_rowid_code NUMBER :=11; cons_urowid_code NUMBER :=208; cons_char_code NUMBER :=96; cons_nchar_code NUMBER :=96; cons_clob_code NUMBER :=112; cons_nclob_code NUMBER :=112; cons_blob_code NUMBER :=113; cons_bfile_code NUMBER :=114; cons_xmltype_code NUMBER :=109; --------------------------------------- Supported types------------------------------------- l_varchar2_col VARCHAR2(32767); --1 l_number_col NUMBER; --2--l_long_col long; --8 - not supported l_date_col DATE; --12--l_raw_col raw(2000); --23 - not supported l_rowid_col ROWID; --69 l_char_col CHAR(2000); --96 l_binary_float_col BINARY_FLOAT; --100 l_binary_double_col BINARY_DOUBLE; --101 l_clob_col CLOB; --112 l_timestamp_col TIMESTAMP(9); --180 l_timestamp_wtz_col TIMESTAMP(9) WITH TIME ZONE; --181 l_interval_ytm_col INTERVAL YEAR(9) TOMONTH; --182 l_interval_dts_col INTERVAL DAY(9) TO SECOND(2); --183 l_urowid_col UROWID; --208 l_timestamp_wltz_col TIMESTAMPWITH LOCAL TIME ZONE; --231 l_xmltype_col XMLTYPE; --109--l_nchar_col nchar(2000); --96 the same as char--l_nclob_col nclob; --112 the same as clob--l_blob_col - not supported--l_bfile_col - not supported--l_long_raw_col - not supportedPROCEDURE print_rec(rec IN dbms_sql.desc_rec) ISBEGIN l_clob_all := l_clob_all||chr(10)||‘col_type = ‘|| rec.col_type||chr(10)||‘col_maxlen = ‘|| rec.col_max_len||chr(10)||‘col_name = ‘|| rec.col_name||chr(10)||‘col_name_len = ‘|| rec.col_name_len||chr(10)||‘col_schema_name = ‘|| rec.col_schema_name||chr(10)||‘col_schema_name_len = ‘|| rec.col_schema_name_len||chr(10)||‘col_precision = ‘|| rec.col_precision||chr(10)||‘col_scale = ‘|| rec.col_scale||chr(10)||‘col_null_ok = ‘; IF (rec.col_null_ok) THEN l_clob_all := l_clob_all||‘true‘||chr(10); ELSE l_clob_all := l_clob_all||‘false‘||chr(10); ENDIF; END; BEGIN----------------------------------------- INSERT - header generation--------------------------------------- l_clob_all :=‘declare‘||chr(10)||‘ type t_clob is table of clob index by binary_integer;‘||chr(10)||‘ l_clob t_clob;‘||chr(10)||‘ type t_varchar2 is table of varchar2(64) index by binary_integer;‘||chr(10)||‘ l_varchar2 t_varchar2;‘||chr(10)||‘begin‘||chr(10)||‘/*‘||chr(10); ----------------------------------------- Introduction--------------------------------------- l_clob_all := l_clob_all||l_line||chr(10)||‘Parsing query:‘||chr(10)||l_sql||chr(10); ----------------------------------------- Open parse cursor--------------------------------------- l_cur := dbms_sql.open_cursor; dbms_sql.parse(l_cur, l_sql, dbms_sql.NATIVE); ----------------------------------------- Describe columns--------------------------------------- l_clob_all := l_clob_all||l_line||chr(10)||‘Describe columns:‘||chr(10); dbms_sql.describe_columns(l_cur, l_col_cnt, l_rec_tab); FOR i IN1..l_rec_tab.count loop print_rec(l_rec_tab(i)); END loop; l_clob_all := l_clob_all||chr(10)||‘*/‘||chr(10)||‘‘||chr(10)||l_line||chr(10)||‘ -- start generation of records‘||chr(10)||‘‘||l_line||chr(10); ----------------------------------------- Define columns---------------------------------------FOR i IN1..l_rec_tab.count loop IF l_rec_tab(i).col_type = cons_varchar2_code THEN--varchar2 dbms_sql.define_column(l_cur, i, l_varchar2_col, l_rec_tab(i).col_max_len); elsif l_rec_tab(i).col_type = cons_number_code THEN--number dbms_sql.define_column(l_cur, i, l_number_col); --elsif l_rec_tab(i).col_type = cons_long_code then --long-- dbms_sql.define_column_long(l_cur, i); elsif l_rec_tab(i).col_type = cons_date_code THEN--date dbms_sql.define_column(l_cur, i, l_date_col); elsif l_rec_tab(i).col_type = cons_binary_float_code THEN--binary_float dbms_sql.define_column(l_cur, i, l_binary_float_col); elsif l_rec_tab(i).col_type = cons_binary_double_code THEN--binary_double dbms_sql.define_column(l_cur, i, l_binary_double_col); -- elsif l_rec_tab(i).col_type = cons_raw_code then --raw -- dbms_sql.define_column_raw(l_cur, i, l_raw_col, l_rec_tab(i).col_max_len); elsif l_rec_tab(i).col_type = cons_rowid_code THEN--rowid dbms_sql.define_column_rowid(l_cur, i, l_rowid_col); elsif l_rec_tab(i).col_type = cons_char_code THEN--char dbms_sql.define_column_char(l_cur, i, l_char_col, l_rec_tab(i).col_max_len); elsif l_rec_tab(i).col_type = cons_clob_code THEN--clob dbms_sql.define_column(l_cur, i, l_clob_col); elsif l_rec_tab(i).col_type = cons_timestamp_code THEN--timestamp dbms_sql.define_column(l_cur, i, l_timestamp_col); elsif l_rec_tab(i).col_type = cons_timestamp_wtz_code THEN--timestamp with time zone dbms_sql.define_column(l_cur, i, l_timestamp_wtz_col); elsif l_rec_tab(i).col_type = cons_rowid_code THEN--urowid dbms_sql.define_column(l_cur, i, l_urowid_col); elsif l_rec_tab(i).col_type = cons_timestamp_lwtz_code THEN--timestamp with local time zone dbms_sql.define_column(l_cur, i, l_timestamp_wltz_col); elsif l_rec_tab(i).col_type = cons_interval_ytm_code THEN--interval year to month dbms_sql.define_column(l_cur, i, l_interval_ytm_col); elsif l_rec_tab(i).col_type = cons_interval_dts_code THEN--interval day to second dbms_sql.define_column(l_cur, i, l_interval_dts_col); elsif l_rec_tab(i).col_type = cons_urowid_code THEN--urowid dbms_sql.define_column(l_cur, i, l_urowid_col); elsif l_rec_tab(i).col_type = cons_xmltype_code THEN--xmltype dbms_sql.define_column(l_cur, i, l_xmltype_col); ELSE raise_application_error(-20001, ‘Column: ‘||l_rec_tab(i).col_name||chr(10)||‘Type not supported: ‘||l_rec_tab(i).col_type); --not supportedENDIF; END loop; ----------------------------------------- Execute cursor--------------------------------------- l_ret := dbms_sql.EXECUTE(l_cur); ----------------------------------------- Fetch rows--------------------------------------- loop l_ret := dbms_sql.fetch_rows(l_cur); exitWHEN l_ret =0; ----------------------------------------- Building INSERT - build column declarations--------------------------------------- l_clob_line :=‘‘; FOR i IN1..l_rec_tab.count loop IF l_rec_tab(i).col_type = cons_varchar2_code THEN--varchar2 dbms_sql.COLUMN_VALUE(l_cur, i, l_varchar2_col); l_clob := l_varchar2_col; elsif l_rec_tab(i).col_type = cons_number_code THEN--number dbms_sql.COLUMN_VALUE(l_cur, i, l_number_col); l_clob := to_char(l_number_col); -- elsif l_rec_tab(i).col_type = cons_long_code then --long -- dbms_sql.column_value(l_cur, i, l_long_col); -- l_clob := l_long_col; elsif l_rec_tab(i).col_type = cons_date_code THEN--date dbms_sql.COLUMN_VALUE(l_cur, i, l_date_col); l_clob := to_char(l_date_col, cons_date_frm); elsif l_rec_tab(i).col_type = cons_binary_float_code THEN--binary_float dbms_sql.COLUMN_VALUE(l_cur, i, l_binary_float_col); l_clob := to_char(l_binary_float_col); elsif l_rec_tab(i).col_type = cons_binary_double_code THEN--binary_double dbms_sql.COLUMN_VALUE(l_cur, i, l_binary_double_col); l_clob := to_char(l_binary_double_col); -- elsif l_rec_tab(i).col_type = cons_raw_code then --raw -- dbms_sql.column_value(l_cur, i, l_raw_col); -- l_clob := to_char(l_raw_col); elsif l_rec_tab(i).col_type = cons_rowid_code THEN--rowid dbms_sql.COLUMN_VALUE(l_cur, i, l_rowid_col); l_clob := to_char(l_rowid_col); elsif l_rec_tab(i).col_type = cons_char_code THEN--char dbms_sql.column_value_char(l_cur, i, l_char_col); -- l_clob := substr(l_char_col, 1, l_rec_tab(i).col_max_len - 1); l_clob := substr(l_char_col, 1, l_rec_tab(i).col_max_len); elsif l_rec_tab(i).col_type = cons_clob_code THEN--clob dbms_sql.COLUMN_VALUE(l_cur, i, l_clob_col); l_clob := l_clob_col; elsif l_rec_tab(i).col_type = cons_timestamp_code THEN--timestamp dbms_sql.COLUMN_VALUE(l_cur, i, l_timestamp_col); l_clob := to_char(l_timestamp_col, cons_timestamp_frm); elsif l_rec_tab(i).col_type = cons_timestamp_wtz_code THEN--timestamp with time zone dbms_sql.COLUMN_VALUE(l_cur, i, l_timestamp_wtz_col); l_clob := to_char(l_timestamp_wtz_col, cons_timestamp_wtz_frm); elsif l_rec_tab(i).col_type = cons_interval_ytm_code THEN--interval year to month dbms_sql.COLUMN_VALUE(l_cur, i, l_interval_ytm_col); l_clob := to_char(l_interval_ytm_col); elsif l_rec_tab(i).col_type = cons_interval_dts_code THEN--interval day to second dbms_sql.COLUMN_VALUE(l_cur, i, l_interval_dts_col); l_clob := to_char(l_interval_dts_col); elsif l_rec_tab(i).col_type = cons_urowid_code THEN--urowid dbms_sql.COLUMN_VALUE(l_cur, i, l_urowid_col); l_clob := to_char(l_urowid_col); elsif l_rec_tab(i).col_type = cons_timestamp_lwtz_code THEN--timestamp with local time zone dbms_sql.COLUMN_VALUE(l_cur, i, l_timestamp_wltz_col); l_clob := to_char(l_timestamp_wltz_col, cons_timestamp_wtz_frm); elsif l_rec_tab(i).col_type = cons_xmltype_code THEN--xmltype dbms_sql.COLUMN_VALUE(l_cur, i, l_xmltype_col); l_clob := l_xmltype_col.getclobval(); ENDIF; IF l_rec_tab(i).col_type IN (cons_clob_code, cons_char_code, cons_varchar2_code) THEN l_clob_line := l_clob_line||‘ l_clob(‘||to_char(i)||‘) :=q‘‘‘||l_separator||l_clob||l_separator||‘‘‘;‘||chr(10); ELSIF l_rec_tab(i).col_type IN (cons_xmltype_code) THEN l_clob_line := l_clob_line||‘ l_clob(‘||to_char(i)||‘) :=q‘‘‘||l_separator||l_clob||l_separator||‘‘‘;‘||chr(10); ELSE l_clob_line := l_clob_line||‘ l_varchar2(‘||to_char(i)||‘) :=q‘‘‘||l_separator||l_clob||l_separator||‘‘‘;‘||chr(10); ENDIF; END loop; l_clob_all := l_clob_all||chr(10)||l_clob_line; ----------------------------------------- Building INSERT - build column list--------------------------------------- l_clob_all := l_clob_all||chr(10)||‘ insert into ‘||p_new_owner_name||‘.‘||p_new_table_name||chr(10)||‘ (‘||chr(10); FOR i IN1..l_rec_tab.count loop IF i =1THEN l_clob_all := l_clob_all||‘‘||l_rec_tab(i).col_name||chr(10); ELSE l_clob_all := l_clob_all||‘ ,‘||l_rec_tab(i).col_name||chr(10); ENDIF; END loop; l_clob_all := l_clob_all||‘ )‘||chr(10)||‘ values‘||chr(10)||‘ (‘||chr(10); ----------------------------------------- Building INSERT - build values---------------------------------------FOR i IN1..l_rec_tab.count loop IF i!=1THEN l_clob_all := l_clob_all||‘ ,‘; ELSE l_clob_all := l_clob_all||‘‘; ENDIF; IF l_rec_tab(i).col_type = cons_number_code THEN--number l_clob_all := l_clob_all||‘to_number(l_varchar2(‘||to_char(i)||‘))‘||chr(10); -- elsif l_rec_tab(i).col_type = cons_long_code then --long -- l_clob := l_long_col; elsif l_rec_tab(i).col_type = cons_clob_code THEN--clob, xmltype l_clob_all := l_clob_all||‘l_clob(‘||to_char(i)||‘)‘||chr(10); ELSIF L_REC_TAB(I).COL_TYPE = CONS_XMLTYPE_CODE then--clob, xmltype l_clob_all := l_clob_all||‘xmltype(l_clob(‘||to_char(i)||‘))‘||chr(10); elsif l_rec_tab(i).col_type = cons_char_code THEN--timestamp with local time zone l_clob_all := l_clob_all||‘to_char(l_clob(‘||to_char(i)||‘))‘||chr(10); elsif l_rec_tab(i).col_type = cons_varchar2_code THEN--timestamp with local time zone l_clob_all := l_clob_all||‘to_char(l_clob(‘||to_char(i)||‘))‘||chr(10); elsif l_rec_tab(i).col_type = cons_date_code THEN--date l_clob_all := l_clob_all||‘to_date(l_varchar2(‘||to_char(i)||‘),‘‘‘||cons_date_frm||‘‘‘)‘||chr(10); elsif l_rec_tab(i).col_type = cons_timestamp_code THEN--timestamp l_clob_all := l_clob_all||‘to_timestamp(l_varchar2(‘||to_char(i)||‘),‘‘‘||cons_timestamp_frm||‘‘‘)‘||chr(10); elsif l_rec_tab(i).col_type = cons_timestamp_wtz_code THEN--timestamp with time zone l_clob_all := l_clob_all||‘to_timestamp_tz(l_varchar2(‘||to_char(i)||‘),‘‘‘||cons_timestamp_wtz_frm||‘‘‘)‘||chr(10); elsif l_rec_tab(i).col_type = cons_interval_ytm_code THEN--interval year to month l_clob_all := l_clob_all||‘to_yminterval(l_varchar2(‘||to_char(i)||‘))‘||chr(10); elsif l_rec_tab(i).col_type = cons_interval_dts_code THEN--interval day to second l_clob_all := l_clob_all||‘to_dsinterval(l_varchar2(‘||to_char(i)||‘))‘||chr(10); elsif l_rec_tab(i).col_type = cons_timestamp_lwtz_code THEN--timestamp with local time zone l_clob_all := l_clob_all||‘to_timestamp_tz(l_varchar2(‘||to_char(i)||‘),‘‘‘||cons_timestamp_wtz_frm||‘‘‘)‘||chr(10); ELSE l_clob_all := l_clob_all||‘l_varchar2(‘||to_char(i)||‘)‘||chr(10); ENDIF; END loop; l_clob_all := l_clob_all||‘ );‘||chr(10); END loop; ----------------------------------------- Building INSERT - end of code--------------------------------------- l_clob_all := l_clob_all||chr(10)||‘end;‘; l_clob_all := l_clob_all||chr(10)||‘/‘; ----------------------------------------- Close cursor--------------------------------------- dbms_sql.close_cursor(l_cur); RETURN l_clob_all; END; /
select fn_gen_inserts(‘select * from USG‘, ‘UATDB‘, ‘USG‘) from dual;
原文:https://www.cnblogs.com/kakaisgood/p/12228818.html
内容总结
以上是互联网集市为您收集整理的Generating sql insert into for Oracle全部内容,希望文章能够帮你解决Generating sql insert into for Oracle所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。