What is the difference between oracle and db2 sql

SQL differences between oracle and db2: 1. Oracle uses "create table a as select * from b" to create similar tables, and db2 uses "create table a like b" to create similar tables; 2. Oracle uses "to_char "Function conversion, db2 uses varchar type conversion.

The operating system for this tutorial: Windows 10 system, Oracle version 19c, Dell G3 computer.

What is the difference between db2 and oracle sql statements

1. Create similar table syntax

Oracle :
    create table a as select * from b;
DB2    :
    create table a like b;
          
    (8版本有效，9版本无效)
    create table new_a as select col1,col2... from a definition only

2. Quickly clear large table syntax

Oracle :
    truncate table a;
DB2    :
    alter table a active not logged initially with empty table;

3. Get the first N data syntax

Oracle :
    select * from a where rownum <= N;
DB2    ：
    select * from a fetch first N rows only;

4. Get the system time syntax

Oracle ：
    select sysdate from dual;
DB2    :
    select current timestamp from sysibm.sysdummy1;

5. Different null value conversion methods

Oracle :
    select col1,col2,nvl(col3,'0') from tablename; (判断col3字段是否为空，不为空就输出原来的数值，为空就输出0)
DB2   ：
    select col1,col2,value(col3,'0') from tablename;
     
    （mysql和Db2可以使用Coalesce(col3,'0')函数来实现上述功能）

Coalesce() function

这个函数主要用来进行空值处理，其参数格式如下： COALESCE ( expression,value1,value2……,valuen)
COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。
COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。
    如果expression不为空值则返回expression；
    否则判断value1是否是空值，如果value1不为空值则返回value1；
    否则判断value2是否是空值，如果value2不为空值则返回value2；
    ……以此类推，
    如果所有的表达式都为空值，则返回NULL。

6. Different type conversion methods

oracle :
    select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
DB2    :
    select varchar(current timestamp) from sysibm.sysdummy1;

Analysis:

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')。
 
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')

Currently DB2 V8 also supports to_char and to_date, and V9 version has added to_number

7. Different methods of converting strings to dates (To_Number/cast)

Oracle:
    select to_number('123') from dual;
    select cast('123' as integer) from dual;
DB2   ：
    select cast('123' as integer) from sysibm.sysdummy1;
    select cast(current time as char(8)) from sysibm.sysdummy1;

8. Subquery

Oracle:
    直接用子查询
 
Db2:WITH语句
    WITH  a1 AS(SELECT max(id) AS aa1 FROM test )SELECT id ,aa1 FROM test ,a1

9. Recursive query (hierarchical query)

Oracle递归查询：CONNECT BY PRIOR ... START WITH ...
DB2   递归查询：DB2较难理解，要WITH一个虚拟表

Oracle:

//从child是son的数据向上查询出所有的长辈select distinct test_parent from (
       select t.test_parent from t_test t
              connect by prior t.test_parent = t.test_child
              start with t.test_child = 'son' )

Learn more: Oracle hierarchical query (recursive query): start with...connect by prior and level keyword

DB2:

// Db2递归查询写法with par_test(test_child,test_parent) as(
     select test_child,test_parent from t_test where child='grandchild'  -- 设置递归起点
     union all
     select t.test_child,t.test_parent from par_test pt,t_test t where pt.test_parent = t.test_child ----递归的方向为从子向父)select distinct test_parent from par_test

As shown in the figure above, start from grandchild to query all its parent nodes. First set the starting point of the virtual table, which is the fourth row on the left; push up in order, its father is son = up One row of child, that is, VT.parent = T.child...

The final query result is:

test_parent

son

father

grandpa

10. There are differences in data types

比较大的差别：
1. char大小对比
   Oracle: char 2000
   DB2   : char 254
2. 日期类型
   Oracle: date datetime
   DB2   ：date(日期) time（时间）timestamp（日期时间）

11. About rowId

Oracle中它是由数据库唯一产生的，在程序中可以获得
DB2  在V8版本才有此功能

12.decode method

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

The above is the detailed content of What is the difference between oracle and db2 sql. For more information, please follow other related articles on the PHP Chinese website!