-- 日期：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 ), @SOUR
--日期：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_TYPE WHEN THEN CASE NVL(TO_CHAR(B.DATA_SCALE),) WHEN ELSE END WHEN WHEN WHEN WHEN ELSE B.DATA_TYPE END AS SQLTYPE, CASE DATA_TYPE WHEN ) WHEN ELSE B.DATA_TYPE END AS ALLTYPE, CASE B.NULLABLE WHEN END AS ISNULLABLE, COLUMN_ID AS COLUMN_ID from 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.name 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 WHEN ELSE ) AS SQLTYPE, case b.ISNULLABLE when 0 then ELSE END AS ISNULLABLE,B.COLORDER AS COLUMN_ID FROM .DBO.sysobjects a JOIN .DBO.syscolumns b ON a.id=b.id JOIN .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_TYPE WHEN WHEN WHEN ELSE UPPER(B.COLUMN_TYPE) END AS SQLTYPE, B.COLUMN_TYPE AS ALLTYPE, CASE B.IS_NULLABLE WHEN WHEN END AS ISNULLABLE,B.ORDINAL_POSITION AS COLUMN_ID from information_schema.`TABLES` A JOIN information_schema.`COLUMNS` B ON (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 ##TEMPSOURCE tempdb.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_ID FROM ##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_ID DROP TABLE ##TEMPSOURCE DROP 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=DBUSER TBNAMEtable_name ; R_COL(TABLENAME dba_tab_columns WHERE owner=DBUSER AND table_name=tablename ORDER 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); LOOP FETCH 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=DBUSER AND table_name=tablename AND column_name IN ( SELECT col.column_name FROM user_constraints con, user_cons_columns col col.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); LOOP FETCH 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); ELSE sqltext: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=DBUSER TBNAMEtable_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 col col.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 col col.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); LOOP FETCH 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); LOOP FETCH 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=DBUSER OBJECT_TYPE ,,,,,,,) ,,,,,,,) ,,,,,,,,8); DBA_JOBS TAG=9); OBJ_LIST DBA_OBJECTS%ROWTYPE; JOB_LIST DBA_JOBS%ROWTYPE; SQLTEXT VARCHAR2(2000); LOOP FETCH CUR_OBJECTS INTO OBJ_LIST; EXIT WHEN CUR_OBJECTS%NOTFOUND; SQLTEXT:OBJ_LIST.OBJECT_TYPEOBJ_LIST.; ) THEN SQLTEXT:; END IF; EXECUTE IMMEDIATE SQLTEXT; END LOOP; CLOSE CUR_OBJECTS; CUR_JOBS; --循环删除作业 LOOP FETCH 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 ) AS VCOUNT NUMBER; OBJ_TYPE DBA_OBJECTS.OBJECT_TYPE%TYPE; SQLTEXT VARCHAR2(2000); (,,,,,,,) pOBJECTNAME; VCOUNTOBJECT_TYPE pOBJECTNAME; SQLTEXT:OBJ_TYPEpOBJECTNAME; ) THEN SQLTEXT:; 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=DBUSER TBNAMEOBJECT_TYPE ,,,,,,,,) ,,,,,,,,) ,,,,,,,,,9); R_OBJ_LIST DBA_OBJECTS%ROWTYPE; DDL_TEXT CLOB; BEGIN OPEN R_OBJ; LOOP FETCH 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','添加所有结构脚本','执行添加用户添加所有结构脚本','郭君') AS l_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 LOOP col_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 LOOP dbms_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; LOOP FETCH 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; /
乐彼多用户商城系统，采用ASP.NET分层技术和AJAX技术，运营于高速稳定的微软.NET+MSSQL 2005平台；完全具备搭建超大型网络购物多用户网上商城的整体技术框架和应用层次LBMall 秉承乐彼软件优秀品质，后台人性化设计，管理窗口识别客户端分辨率自动调整，独立配置的菜单操作锁，使管理操作简单便捷。待办事项1、新订单、支付、付款、短信提醒2、每5分钟自动读取3、新事项声音提醒 店铺管理1
