提示:上部为Oracle ,下部为sqlserver
文章目录
前言
例如:最近做日本项目,客户想把oracle转成sqlserver,目前项目快结束了在此做一下总结。
一、SQL変換例
1.引入库
代码如下(示例):
using Oracle.DataAccess.Client;
SQL.MasterData master = new SQL.MasterData();
this.DbOpen();
using (LogOracleCommand oraCmd = new LogOracleCommand(sql.ToString(), this.DbConnection))
LogOracleDataAdapter oradata = new LogOracleDataAdapter(oraCmd);
this.DbClose();
using System.Data.SqlClient;
MsSQL.MasterData master = new MsSQL.MasterData();
this.DbSqlOpen();
using (LogMsSqlCommand oraCmd = new LogMsSqlCommand(sql.ToString(), this.DbSqlConnection))
LogMsSqlDataAdapter oradata = new LogMsSqlDataAdapter(oraCmd);
this.DbSqlClose();
2.SQL參數
代码如下(示例):
oraCmd.Parameters.Add(":K_CD", OracleDbType.Char).Value = UserInfo.KaiCd.Trim();
oraCmd.Parameters.Add("@K_CD", SqlDbType.Char).Value = UserInfo.KaiCd.Trim();
3.DECODE /CASE WHEN
代码如下(示例):
sql.AppendLine(" , DECODE(D.\"xxxx\",'1','2','2','1','') ");
sql.AppendLine(" , CASE D.\"xxxx\" WHEN '1' THEN '2' WHEN '2' THEN '1' ELSE '' END ");
4.FORMAT /CONVERT
代码如下(示例):
FORMAT(CHAR(10), H.\"TDate\", 111)
CONVERT(CHAR(10), H.\"TDate\", 111)
5.(+) /LEFT JOIN ON
代码如下(示例):
FROM TABLE_A, TABLE_B
WHERE A.ID =B.ID(+) (表是外连接)
AND B.CD(+) ='1'
FROM TABLE_A LEFT JOIN TABLE_B ON A.ID =B.ID AND B.CD='1'
6.TO_CHAR /CONVERT
代码如下(示例):
TO_CHAR(SYSDATE,'YYYY/MM/DD')
CONVERT(CHAR(10),GETDATE(),111)
7.SUBSTR/SUBSTRING
代码如下(示例):
SUBSTR(F0.番号, 1,2) 从第一个字节开始,取后面的2个
SUBSTRING(F0.番号, 1,2) 从第一个字节开始,取后面的2个
8.NVL /ISNULL
代码如下(示例):
NVL(TRIM(A.HYOUJI_KBN),'0')
CASE WHEN ISNULL(TRIM(A.HYOUJI_KBN),'') ='' THEN '0' ELSE A.HYOUJI_KBN END
9.rpad/ right(replicate())
rpad(A.NO,8,'0')
left(A.NO+REPLICATE('0', 8),8)
10.REGEXP_INSTR / PATINDEX
select SUBSTRING(TRIM('aaa123123'), 1, PATINDEX('%[0-9][0-9]%',TRIM('aaa123123'))-1) AS SORT_KEY --结果:aaa
select SUBSTR(TRIM('aaa123123'), 0, REGEXP_INSTR(TRIM('aaa123123'),'\d\d')-1) AS SORT_KEY from dual --结果:aaa