oracle数据库转换到Sqlserver的几点经验

应广大朋友的要求，本人将《PB-ORACLE通用开发框架2.1》版本的程序

成功转换到SQLServer下，下面是我总结的几条经验，与大家分享，有不

正确的地方，请批评指正。谢谢！[e-mail]

1.数据类型的转换  建立两个数据源basedb, basdb_sql
  为每个表，建立一个数据管道，修改部分字段类型   

   Oracle                              SQLServer

   varchar2                            varchar
 
   blob                                image

2.函数的转换
 
   ORACLE                               SQLServer

   to_char(readingdate,’yyyymm’)      convert(char(6),readingdate,111)

   to_char(readingdate,’yyyy/mm’)     convert(char(7),readingdate,112)

   decode( ,  ,  ,  ,)                  case when then end 或者 isnull(x,0)

   左右连接 (+)                         left outer join

   nvl(field1,'')                       isnull(field1,'')

   举例子如下：

oracle:
  SELECT EFMFID,  
         EFMMID,  
         EFMNAME,  
         EFMMETHOD,  
         EFMUPPICT,  
         EFMDNPICT,  
         EFMCHECK,  
         EFMVISIBLE,  
         EFMHINT,  
         EFMMICOHELP,  
         EFMTBITEMINDEX,  
         EFMTBITEMORDER,  
         EFMTBITEMSPACE,
  DECODE(ORFMRID,NULL,'N','Y')  FLAG
    FROM ERPFUNCMETHOD,
         (SELECT ORFMRID,ORFMFID,ORFMETHOD
            FROM OPERRFMETHOD WHERE ORFMRID=:v_role)
        WHERE EFMFID=ORFMFID(+) AND EFMMID=ORFMETHOD(+) AND EFMFID=:v_func
    ORDER BY EFMTBITEMORDER

-----------------------------------
sqlserver:
SELECT   A.EFMFID,  
         A.EFMMID,  
         A.EFMNAME,  
         A.EFMMETHOD,  
         A.EFMUPPICT,  
         A.EFMDNPICT,  
         A.EFMCHECK,  
         A.EFMVISIBLE,  
         A.EFMHINT,  
         A.EFMMICOHELP,  
         A.EFMTBITEMINDEX,  
         A.EFMTBITEMORDER,  
         A.EFMTBITEMSPACE,
         case IsNUll(B.ORFMRID,'*')
           when '*' then 'N'
           else 'Y'
         end FLAG
    FROM ERPFUNCMETHOD AS A
         LEFT OUTER JOIN
              (SELECT ORFMRID,ORFMFID,ORFMETHOD
                FROM OPERRFMETHOD WHERE ORFMRID = :v_role ) AS B
 ON A.EFMFID=B.ORFMFID AND A.EFMMID=B.ORFMETHOD
    WHERE A.EFMFID = :v_func
    ORDER BY A.EFMTBITEMORDER;

3. 数据窗口中的语法
   在测试的过程会发现有一些数据窗口在 SqlServer不能用：修改方法是将字段的引号去掉

4. 取服务器时间
ORACEL                   
-------------------------------------------
datetime ld_today

declare cur cursor for select getdate();
open cur;
fetch cur into :ld_today;
close cur;

IF SQLCA.SQLCODE <> 0 THEN
 MESSAGEBOX('错误信息', SQLCA.SQLERRTEXT)
END IF
return ld_today

SQLSERVER:
---------------------------------------------
date ld_today

SELECT SYSDATE INTO :Ld_TODAY FROM DUAL;
IF SQLCA.SQLCODE <> 0 THEN
 MESSAGEBOX('错误信息', SQLCA.SQLERRTEXT)
END IF
return ld_today

5.函数的转换
  尽量不要使用select count(*),而由EXISTS 来代替

ORACEL                   
-------------------------------------------
CREATE OR REPLACE FUNCTION fChkRoleFunc
   (Vrole IN VARCHAR2,vcode IN VARCHAR2)
   Return Integer
AS
   lpcode Integer;
BEGIN
   SELECT COUNT(*) INTO lpcode FROM operrolefunc
 WHERE orfrid=vrole AND orffid = vcode;
   Return lpcode;
EXCEPTION WHEN OTHERS THEN
   lpcode := 0;
   Return lpcode;
END;

SQLSERVER:
---------------------------------------------
CREATE  FUNCTION fChkRoleFunc
   (@Vrole  VARCHAR(2), @vcode  VARCHAR(10))
Returns Int
AS
BEGIN
   DECLARE   @lpcode Int
IF EXISTS(  SELECT *  FROM operrolefunc WHERE orfrid=@vrole  AND orffid = @vcode)
  set @lpcode = 1
else
  set  @lpcode = 0

   RETURN (@lpcode)
END

6. 取本机IP
在ORACLE中是利用系统对象获得

//登录数据库客户端名称
SELECT sys_context('USERENV','HOST')
INTO :gs_workstation
FROM dual;
gs_workstation=mid(gs_workstation,pos(gs_workstation,'/')+1,len(gs_workstation) - pos(gs_workstation,'/'))

//登录数据库客户端IP
SELECT sys_context('USERENV','IP_ADDRESS')
INTO :gs_ws_ip
FROM dual;
在SQLServer版本是用winsock.pbl和pslib5.dll 完成的
增加了一个函数f_ip_sock