ITPub博客

首页 > 数据库 > Oracle > db2 数字转换字符 oracle和db2的sql语句区别

db2 数字转换字符 oracle和db2的sql语句区别

Oracle 作者:jinjinyu33 时间:2012-03-23 12:34:34 0 删除 编辑
-------------------------------------------------- ----------------------------------- 表锁模式:

  oracle:

  lock table your_table in share row exclusive mode;

  db2

  lock table your_table in exclusive mode;

  -------------------------------------------------- -----------------------------------

  Oracle中rownum的转换

  Oracle SQL?

  SQL>select rownum,* from BSEMPMS where rownum >=5 and rownum <=100;

  DB2 SQL?

  select * from (select ROW_NUMBER() over() as a, db2admin.bsempms.* from

  db2admin.bsempms) as temp where a>=5 and a<=100 ;

  Oracle SQL?

  SQL>select col1, col2, col3,rownum from your_table where where rownum<3;

  DB2 SQL?

  select col1, col2, col3,

  rownumber () OVER (ORDER BY col1 DESC) AS rownum

  from your_table

  where rownum<3

  -------------------------------------------------- -----------------------------------

  ORACLE 中的to_date()函数

  ORACLE SQL:

  to_date('2008-04-16','yyyy-mm-dd')

  DB2 SQL::

  TO_CHAR(START_DATE,'YYYYMMDD')

  substr(CHAR('2008-04-16'),1,4)||substr(CHAR('2008- 04-16'),6,2)||substr(CHAR('2008-04-16'),9,2)

  ORACLE SQL:

  select m.*

  from dj_mcdj m

  where m.mcqc || ' ' like '%$P{QYMC}%'

  and m.xzqhdm || ' ' like '%$P{XZQH}%'

  and m.hylbdm || ' ' like '%$P{HYLB}%'

  and m.blqsrq >= to_date('$P{QSRQ}', 'yyyy-mm-dd')

  and m.blqsrq < to_date('$P{JZRQ}', 'yyyy-mm-dd')+1

  DB2 SQL:

  select m.*

  from dj_mcdj m

  where m.mcqc || ' ' like '%%'

  and m.xzqhdm || ' ' like '%%%'

  and m.hylbdm || ' ' like '%%%'

  and date(m.blqsrq) >= date('1900-01-01')

  and date(m.blqsrq) < date('2050-01-01')+1 day

  -------------------------------------------------- -----------------------------------

  ORACLE 中的nvl函数

  ORACLE SQL:

  select distinct nvl(

  (select max(

  to_number(

  substr(levelcode,length(levelcode)-4,length(levelc ode))))+1

  from client_clientInfo a,client_corporationinfo b

  where a.id=b.clientId and a.levelid=1),'10001') levelCode

  from client_clientinfo

  DB2 SQL:

  select distinct coalesce(

  (select max(dec(substr(levelcode,length(levelcode)-4,lengt h(levelcode))))+1

  from client_clientInfo as a,client_corporationinfo as b

  where a.id=b.clientId and a.levelid=1)

  ,10001)

  as levelCode

  from client_clientinfo

  比较函数nvl(a1,a2)改为coalesce(a1,a2)

  转化数字函数to_number(string)改为:dec(string)

  -------------------------------------------------- -----------------------------------

  ORACLE 中的DECODE函数

  ORACLE SQL:

  DECODE (A.INVOICE_ITEM_ID, -1, '07其他费用', MIN(A.INVOICE_ITEM_NAME))

  DB2 SQL:

  CASE A.INVOICE_ITEM_ID

  WHEN -1 THEN '07其他费用'

  ELSE MIN(A.INVOICE_ITEM_NAME)

  END

  ORACLE SQL:

  decode((ss.mbalance - ss.muncheckpaymentamount),0,'0.00',CHAR(ss.mbalanc e - ss.muncheckpaymentamount) )

  DB2 SQL:

  CASE(ss.mbalance - ss.muncheckpaymentamount)

  WHEN 0 THEN '0.00'

  ELSE CHAR(ss.mbalance - ss.muncheckpaymentamount)

  END

  -------------------------------------------------- -----------------------------------

  ORACLE 中的lpad函数

  ORACLE SQL:

  select lpad(ecode,2,'0') ecode from your_table where id=2;

  DB2 SQL:

  select repeat('0',2-length(rtrim(char(ecode)))) || rtrim(char(ecode)) ecode from your_table where id=2;

  ORACLE SQL:

  LPAD(slno,20,'0')

  DB2 SQL:

  repeat('0',20-length(rtrim(char(slno)))) || rtrim(char(slno))

  -------------------------------------------------- -----------------------------------

  ORACLE 中的序列

  ORACLE SQL:

  select Seq_a.Nextval from dual

  DB2 SQL:

  select nextval for Seq_a from dual

  -------------------------------------------------- -----------------------------------

  ORACLE 中的to_number

  ORACLE SQL:

  select to_number(S.sname) from your_table

  DB2 SQL:

  select dec(S.sname) from your_table

  -------------------------------------------------- -----------------------------------

  ORACLE 中的外连接 SELECT a.lastname, a_id, b.name

  FROM emp A, customer B

  WHERE A.id(+) = b.sales_rep_id;

  SELECT a.lastname, a_id, b.name

  FROM emp A

  RIGHT OUTER JOIN, customer B

  ON A.id = b.sales_rep_id;

  SELECT a.lastname, a_id, b.name

  FROM emp A, customer B

  WHERE A.id = b.sales_rep_id(+);

  SELECT a.lastname, a_id, b.name

  FROM emp A

  LEFT OUTER JOIN, customer B

  ON A.id = b.sales_rep_id;

  SELECT a.lastname, a_id, b.name

  FROM emp A, customer B

  WHERE A.id(+) = b.sales_rep_id(+);

  SELECT a.lastname, a_id, b.name

  FROM emp A

  FULL OUTER JOIN, customer B

  ON A.id = b.sales_rep_id;

  -------------------------------------------------- ----------------------------------- ORACLE 中的round函数

  ORACLE SQL:

  SELECT round(a.number,2) as rount from your_table

  DB2 SQL:

  和oracle相同,对于只有一个变量的情况,现在发现的功能是返回比a.number小的最大的整数

  ORACLE SQL:SELECT round(a.number) as round from your_table

  DB2 SQL:select FLOOR(a.number) as round from your_table

  -------------------------------------------------- -----------------------------------

  ORACLE 中的to_char(dtExecute,'IW')

  ORACLE SQL:

  select to_char(dtExecute,'IW') from your_table

  DB2 SQL:

  select week(dtExecute,'IW') from your_table

  功能是返回dtExecute这个时间是第几周。

  1、取前N条记录

  Oracle:Select * from TableName where rownum <= N;

  DB2:Select * from TableName fetch first N rows only;

  2、取得系统日期

  Oracle:Select sysdate from dual;

  DB2:Select current timestamp from sysibm.sysdummy1;

  3、空值转换

  Oracle:Select productid,loginname,nvl(cur_rate,'0') from TableName ;

  DB2:Select productid,loginname,value(cur_rate,'0') from TableName;

  Coalesce(cur_rate,'0')

  4、类型转换(8版有了to_char,to_date,9版新增了to_number)

  Oracle:select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

  DB2:select varchar(current timestamp) from sysibm.sysdummy1;

  l Oracle数据类型改变函数:to_char()、to_date()、to_number()等;如果仅仅取年,月,日等,可以用to_char(sysdate, 'YYYY'),to_char('MM') ,to_char('DD')取得。只取年月日TRUNC(SYSDATE),取时分秒TO_CHAR(SYSDATE,'HH24:MI:SS')。

  l DB2数据类型改变函数:char()、varchar()、int()、date()、time()等;取得年,月,日等的写法:YEAR(current timestamp),MONTH(current timestamp),DAY(current timestamp),HOUR(current timestamp),MINUTE(current timestamp),SECOND(current timestamp),MICROSECOND(current timestamp),只取年月日可以用DATE(current timestamp),取时分秒TIME(current timestamp)。Char()是定长字符串(1-255),varchar()为非定长字符串(1-32672)日期,时间形态变为字符形态: char(current date),char(current time)将字符串转换成日期或时间形态:TIMESTAMP('2002-10-2012:00:00'),DATE('2002-10-20' ),DATE('10/20/2002'),TIME('12:00:00')

  l 目前DB2 V8也支持to_char和to_date

  5、快速清空大表

  Oracle:truncate table TableName ;

  DB2:alter table TableName active not logged initially with empty table;

  6、关于ROWID

  Oracle它是由数据库唯一产生的,在程序里可以获得DB2 v8也有此功能。

  7、To_Number

  Oracle:select to_number('123') from dual;

  DB2:select cast('123' as integer) from sysibm.sysdummy1;

  SELECT CAST ( current time as char(8)) FROMsysibm.sysdummy1

  8、创建类似表

  Oracle:create table a as select * from b ;

  DB2:create table a like b ;

  CREATE TABLE tab_newAS select col1,col2…FROMtab_old DEFINITION ONLY (8版有效,9版无效)

  9、decode方法

  Oracle:decode方法(DECODE(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值))或者case语句DB2中只有CASE表达式SELECT id ,name ,CASE WHEN integer(flag)=0 THEN '假' WHEN integer(flag)=1 THEN '真' ELSE '异常'END FROM TEST或者SELECT id ,name , CASE integer(flag) WHEN 0 THEN '假' WHEN 1 THEN '真'ELSE '异常'END FROM TEST

  10、子查询(8版,9版也支持子查询)

  Oracle:直接用子查询

  Db2:with语句WITH a1 AS(select max(id) as aa1 from test ) select id ,aa1 from test ,a1

  11、数据类型

  比较大的差别:

  Oracle:char 2000

  DB2: char 254

  Oracle: date datetime

  Db2: DATE:日期TIME:时间TIMESTAMP:日期时间
<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7519651/viewspace-1117582/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论