--日期：2013-02-13--调用:--exec compare_dbtodb '','openvone',2,'mysql1','openvone',3--exec compare_dbtodb 'oracle2','openvone',3,'','openvone',2--.((((()(4000),@mssqltext varchar(4000),@mysqltext varchar(4000),@sourcetext varchar(4000),@targettext varchar(4000),@sqltext varchar(4000)n'select a.table_name as tablename,b.column_name as columnname,case data_typewhen thencase nvl(to_char(b.data_scale),)when else endwhen when when when else b.data_typeend as sqltype,case data_typewhen )when else b.data_typeend as alltype,case b.nullable when end as isnullable,column_id as column_idfrom dba_tables a join dba_tab_columns b on (a.table_name=b.table_name and a.owner=b.owner)where a.owner=n'select tablename,columnname,sqltype,sqltype as alltype,isnullable,column_id from(select a.name as tablename,b.name as columnname,upper(c.name)+replace(case c.namewhen when when when when when when when when when when when when when when when when when when when when when when when when when when else ) as sqltype,case b.isnullablewhen 0 then else end as isnullable,b.colorder as column_idfrom .dbo.sysobjects ajoin .dbo.syscolumns b on a.id=b.idjoin .dbo.systypes c on (b.xtype=c.xtype and b.xusertype=c.xusertype)where a.xtype=n'select a.table_name as tablename,b.column_name as columnname,case b.column_typewhen when when else upper(b.column_type) end as sqltype,b.column_type as alltype,case b.is_nullablewhen when end as isnullable,b.ordinal_position as column_idfrom information_schema.`tables` ajoin information_schema.`columns` bon (a.table_name=b.table_name and a.table_schema=b.table_schema and a.table_catalog=b.table_catalog)where a.table_schema=(,,@sourcedb)(,,@targetdb)(((((tempdb.dbo.sysobjects name)drop table ##tempsourcetempdb.dbo.sysobjects name)drop table ##temptarget))select a.tablename,a.columnname,a.sqltype as sqltype1,b.sqltype as sqltype2,a.alltype as alltype1,b.alltype as alltype2,a.isnullable as isnullable1,b.isnullable as isnullable2,a.column_idfrom ##tempsource a join ##temptarget b on (a.tablename=b.tablename and a.columnname=b.columnname and (convert(varchar,a.sqltype)convert(varchar,b.sqltype) or a.isnullableb.isnullable))order by a.tablename,a.column_iddrop table ##tempsourcedrop table ##temptarget二：以下为oracle数据库断自动生成存储过程和获取数据库ddl语句的过程，最终通过调用clob_read保存入文件中，目前暂未修正超过32767字节的写文本问题。超过32767字节文本会被自动截断。 add_all_tables--功能:执行添加表记录的存储过程,返回大字段，需要保存到文件进行读取--参数:dbuser-数据库用户，tbname-模糊匹配的表名，如果为空或''则生成全库的过程，sqltext-返回的存储过程大文本字段--调用:--作者:郭君--日期:2013-02-10( dbuser in varchar2, tbname in varchar2, sqltext out clob)dba_tables where owner=dbusertbnametable_name ;r_col(tablename dba_tab_columns where owner=dbuser and table_name=tablenameorder by column_id; r_tb_list dba_tables%rowtype;r_col_list dba_tab_columns%rowtype;addcol1 varchar2(4000):='';addcol2 varchar2(4000):=''; begin open r_tb; loopr_tb into r_tb_list;exit when r_tb%notfound;sqltext:chr(10);sqltext:substr(r_tb_list.table_name,r_tb_list.table_namechr(10);sqltext:chr(10);sqltext:substr(r_tb_list.table_name,chr(10);addcol1:='';addcol2:='';open r_col(r_tb_list.table_name);loopfetch r_col into r_col_list;exit when r_col%notfound;sqltext:r_col_list.column_namer_col_list.data_typechr(10); --参数名称默认以小写'p'开头，可以修改该处的规则为想要的命名方式addcol1:;addcol2:r_col_list.column_name; loop;close r_col;addcol1:= substr(addcol1, 0, length(addcol1)-1);addcol2:= substr(addcol2, 0, length(addcol2)-1);sqltext:chr(chr(chr(r_tb_list.table_nameaddcol1chr(addcol2chr(chr(substr(r_tb_list.table_name,chr(chr(10);end loop; close r_tb; end; del_all_tables --功能:执行删除的存储过程(根据主键列删除,注意标识符不能超过30个字符),返回大字段，需要保存到文件进行读取--参数:dbuser-数据库用户，tbname-模糊匹配的表名，如果为空或''则生成全库的过程，sqltext-返回的存储过程大文本字段--调用:--作者:郭君--日期:2013-02-10( dbuser in varchar2, tbname in varchar2, sqltext out clob) dba_tables where owner=dbuser tbnametable_name ;r_pk(tablename dba_tab_columns where owner=dbuserand table_name=tablename and column_name in(select col.column_name from user_constraints con, user_cons_columns colcol.table_name=tablename)order by column_id; r_tb_list dba_tables%rowtype;r_pk_list dba_tab_columns%rowtype;wherecol varchar2(4000):=''; begin open r_tb; loopr_tb into r_tb_list;exit when r_tb%notfound;sqltext:chr(10);sqltext:substr(r_tb_list.table_name,r_tb_list.table_namechr(10);sqltext:chr(10);sqltext:substr(r_tb_list.table_name,chr(10);wherecol:='';open r_pk(r_tb_list.table_name);loopfetch r_pk into r_pk_list;exit when r_pk%notfound;wherecol:r_pk_list.column_name;--参数名称默认以小写'p'开头，可以修改该处的规则为想要的命名方式sqltext:r_pk_list.column_namer_pk_list.data_typechr( loop;close r_pk;sqltext:chr(chr(chr(r_tb_list.table_namesubstr(wherecol, chr(chr(substr(r_tb_list.table_name,chr(chr(10);elsesqltext:chr(chr(r_tb_list.table_namechr(chr(substr(r_tb_list.table_name,chr(chr(10);end if;end loop; close r_tb; end; update_all_tables--功能:执行修改的存储过程(根据主键列修改,注意标识符不能超过30个字符),返回大字段，需要保存到文件进行读取--参数:dbuser-数据库用户，tbname-模糊匹配的表名，如果为空或''则生成全库的过程，sqltext-返回的存储过程大文本字段--调用:--作者:郭君--日期:2013-02-10( dbuser in varchar2, tbname in varchar2, sqltext out clob) dba_tables where owner=dbusertbnametable_name ;r_col(tablename dba_tab_columns where owner=dbuser and table_name=tablename and column_name not in(select col.column_name from user_constraints con, user_cons_columns colcol.table_name=tablename)order by column_id; dba_tab_columns table_name=tablename and column_name in(select col.column_name from user_constraints con, user_cons_columns colcol.table_name=tablename)order by column_id; r_tb_list dba_tables%rowtype;r_col_list dba_tab_columns%rowtype;r_pk_list dba_tab_columns%rowtype;setcol varchar2(4000):='';wherecol varchar2(4000):=''; begin open r_tb; loopr_tb into r_tb_list;exit when r_tb%notfound;sqltext:chr(10);sqltext:substr(r_tb_list.table_name,r_tb_list.table_namechr(10);sqltext:chr(10);sqltext:substr(r_tb_list.table_name,chr(10);setcol:='';wherecol:='';open r_pk(r_tb_list.table_name);loopfetch r_pk into r_pk_list;exit when r_pk%notfound;sqltext:r_pk_list.column_namer_pk_list.data_typechr(10);wherecol:r_pk_list.column_name;end loop;close r_pk;open r_col(r_tb_list.table_name);loopfetch r_col into r_col_list;exit when r_col%notfound;sqltext:r_col_list.column_namer_col_list.data_typechr(10);setcol:r_col_list.column_name;end loop;close r_col;setcol:= substr(setcol, 0, length(setcol)-1);substr(wherecol, 0, length(wherecol)-5)) into wherecol from dual;sqltext:chr(chr(chr(r_tb_list.table_namesetcolchr(chr(substr(r_tb_list.table_name,chr(chr(10);end loop; close r_tb; end; drop_all_objects --功能:删除用户下所有的结构关系--参数:dbuser-数据库用户，tag-结构类型，见下方说明--调用:execute drop_all_objects('openvone',0)--作者:郭君--日期:2013-02-10( dbuser in varchar2, tag in number)--操作均基于指定用户下进行，其他用户不做处理--0.删除全部--1.删除视图--2.删除函数--3.删除过程--4.删除类型--5.删除包--6.删除触发器--7.删除表（解除主键关系再删除）--8.删除序列--9.清空作业--cur_objects dba_objects where owner=dbuserobject_type ,,,,,,,),,,,,,,),,,,,,,,8);dba_jobs tag=9);obj_list dba_objects%rowtype;job_list dba_jobs%rowtype;sqltext varchar2(2000); loopfetch cur_objects into obj_list;exit when cur_objects%notfound;sqltext:obj_list.object_typeobj_list.;) thensqltext:;end if;execute immediate sqltext;end loop;close cur_objects;cur_jobs;--循环删除作业 loopfetch cur_jobs into job_list;exit when cur_jobs%notfound;dbms_job.remove(job_list.job); loop;close cur_jobs;(tagtagimmediate ;;end drop_all_objects; drop_single_object--功能:基于指定用户下删除单个结构(表、视图、函数、类型、包、触发器、序列),但不能删除该过程本身--参数:dbuser-数据库用户，pobjectname-结构名--调用:execute drop_single_object('openvone','tb_users')--作者:郭君--日期:2013-02-10( dbuser in varchar2, pobjectname in varchar2)asvcount number;obj_type dba_objects.object_type%type; sqltext varchar2(2000);(,,,,,,,) pobjectname; vcountobject_type pobjectname;sqltext:obj_typepobjectname;) thensqltext:;end if;execute immediate sqltext; end if; commit;end; get_all_objects--功能:查询用户下所有的结构并返回大字段文段，美国服务器，需要保存到文件进行读取--参数:dbuser-数据库用户，tag-结构类型，见下方说明，tbname-模糊匹配的结构名，如果为空或''则生成全库的ddl语句，sqltext-返回生成结构的ddl语句大文本字段--调用:--作者:郭君--日期:2013-02-10( dbuser in varchar2, tag in number, tbname in varchar2, sqltext out clob)--操作均基于指定用户下进行，其他用户不做处理--0.全部--1.序列--2.表（未解决键/索引对应关系以及创建先后顺序问题，需注意）--3.索引--4.触发器--5.视图--6.类型--7.函数--8.过程--r_obj dba_objects where owner=dbusertbnameobject_type ,,,,,,,,),,,,,,,,),,,,,,,,,9);r_obj_list dba_objects%rowtype;ddl_text clob;begin open r_obj; loopfetch r_obj into r_obj_list;exit when r_obj%notfound;select dbms_metadata.get_ddl(r_obj_list.object_type,r_obj_list.object_name,r_obj_list.owner) into ddl_text from dual;sqltext:=sqltext||ddl_text;end loop; close r_obj; end; clob_read--功能:读取指定用户下某一存储过程返回的大对象文本，并保存到指定目下下的文本文件中--参数:dbuser-数据库用户名，香港服务器，procname-该用户下的存储过程名，paras-该用户下的存储过程参数列表(以','分隔)，dir-指定目录，filename-文件名，title-文件标题说明，author-文件作者说明--调用:execute clob_read('openvone','get_all_objects','''openvone'',0,''''','d:\backdb','添加所有结构脚本','执行添加用户添加所有结构脚本','郭君')--作者:郭君--日期:2013-02-10( dbuser in varchar2, procname in varchar2, paras in varchar2, dir in varchar2, filename in varchar2, title in varchar2, author in varchar2)--步骤：分三步走--一：设置文件目录--alter system set utl_file_dir='d:\backdb'scope=spfile;--二：赋予用户操作文件的权限--grant execute on utl_file to openvone;--三：调用示例：execute clob_read('openvone','get_all_objects','''openvone'',0,''''','d:\backdb','添加所有结构脚本','执行添加用户添加所有结构脚本','郭君')asl_file utl_file.file_type;l_buffer varchar2(4000);l_amount binary_integer:=2000;l_pos integer:=1;l_clob clob;l_clob_len integer;t_sql varchar2(2000);col_list varchar2(300);out_count number; --输出参数个数，有且只能为一个in_count (procname ; (procname ;t_sql:procname;for i in 1..in_count loopcol_list:,; loop;t_sql:;immediate t_sql using out l_clob;--此处为输出参数l_clob_len:= dbms_lob.getlength(l_clob);l_file:,,32767);--文件目录/名称utl_file.put_line(l_file,);utl_file.put_line(l_file,title);utl_file.put_line(l_file,author);utl_file.put_line(l_file,to_char(sysdate,));utl_file.put_line(l_file,);while l_posl_clob_len loopdbms_lob.read(l_clob,l_amount, l_pos, l_buffer);utl_file.put(l_file,l_buffer);l_pos:=l_pos+l_amount;end loop;utl_file.fclose(l_file);end if;end clob_read; recompile_all_projects --功能:编译失效的结构，一般用的很少，服务器空间，查看失效过程可通过select * from dba_objects where status='invalid'实现--参数:dbuser-数据库用户名--调用:--作者:郭君--日期:2013-02-10( dbuser in varchar2)cur_objects dba_objects object_type ,,,,,)decode(object_type,,,,,,,6);obj_list dba_objects%rowtype;sqltext varchar2(2000);begin open cur_objects; loopfetch cur_objects into obj_list;exit when cur_objects%notfound;sqltext:obj_list.object_typeobj_list.;execute immediate sqltext; end loop; close cur_objects;end recompile_all_projects;/