近来,在项目中用到数据移植,将oracle中的数据移到sysbase中, 对它进行了整理。
1.数据类型介绍比较
ORACALE SYBASE
VARCHAR2(n) VARCHAR(n)
NVARCHAR2(n) VARCHAR(n)
CHAR CHAR
CHAR(1) BIT
CHAR(n) CHAR(n)
NCHAR(n) NCHAR(n)
NUMBER(3) TINYINT(0-255)
NUMBER(4) SAMLLINT(-32768~32768)
NUMBER(9) INT(-231~231-1)
NUMBER(19,4) MONEY/SMALLMONEY
NUMBER REAL,FLOAT,DOUBLE
FLOAT FLOAT,REAL
FLOAT(p) FLOAT(p),DOUBLE PRECISION
NUMBER(p,s) NUMERIC(p,s)
NUMBER(p,s) DECIMAL(p,s)
BINARY_FLOAT N/A
BINARY_DOUBLE N/A
DATE DATETIME(1/300 second)
SMALLDATETIME(minute)
DATE, TIME
CLOB TEXT
BLOB IMAGE
RAW(n) BINARY(n), VARBINARY(n), IMAGE
BFILE N/A
2.JDBC应用
ODBC应用一般不需要改变
(1)jdbc 驱动的设置
import com.sybase.jdbcx.SybDriver;
SybDriver sybDriver = (SybDriver)
Class.forName ("com.sybase.jdbc3.jdbc.SybDriver").newInstance();
sybDriver.setVersion(com.sybase.jdbcx.SybDriver.VERSION_6);
DriverManager.registerDriver(sybDriver);
//connect to database
Properties props = new Properties();
props.put("user", "userid");
props.put("password", "user_password");
/* Make sure you set connection properties before attempting to make a connection. You can also* set the properties in the URL.*/
Connection con = DriverManager.getConnection
("jdbc:sybase:Tds:host:port", props);
(2)sql 语句的使用
Statement stmt = conn.createStatement
(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
// Use the Statement to return an updatable ResultSet
ResultSet rs = stmt.executeQuery("SELECT * FROM T1 WHERE...");
while (rs.next())
{
rs.updateString(2, "xyz");
rs.updateInt(3,100);
rs.updateRow();
}
(3)sql语句的注意,在左右连接中的问题
col1 = col2(+) ===? col1*=col2(left outer join)
col1(+) = col2 ===? col1=*col2 (right outer join)
Sybase ASE DOES NOT allow another join relationship on a table that already
has an outer join.
select count(*)
from Circuit a,Device c,Device e,
(SELECT a.DeviceID as resid
from Device a, singleuserresauth b,node c
where a.DeviceID = b.resid and b.netuserid='TEST2'
and a.nodecode = c.nodecode and c.nodefullcode like '%NOD999%') r
where (r.resid=c.deviceid or r.resid=e.deviceid) and a.ADeviceID=c.DeviceID
and isnull(c.ChangeType,0)=0 and isnull(e.ChangeType,0)=0
and a.BDeviceID*=e.DeviceID AND a.ChangeType = 1 AND a.IPVersion = 'IPv4'
AND a.CirPropCode in (select CirPropCode from cirprop where CirPropFullCode like %PRP999%')
3.注意事项
1、采用小事务
2、查询语句要尽可能用上索引
3、避免where语句中数据类型转换
4、> 变为 >=,> 变为 <=
5、not exists 变为exists ,not in变为in
6、 判断数据存在性时用exists而不用count(*)
7、表连接操作中的or语句,若能变为union操作,ASE可优化
8、min()和max()函数
若这两个函数所使用的列为索引的第一列,ASE可优化,只需索引页。不要在两函数中用表达式,如:
max(numeric_col*2),而把它变为max(numeric_col)*2
9、尽可能使用存贮过程,并适时对存贮过程进行从新编译(sp_recompile)
10、oracle中的序列来进行主键定义的话,可以采用sysbase中的自动增长型。
11.注意sysbase中的大小写问题。