ORACLE和DB2实现相同功能的做法(1)

1.如何取一表前n笔记录的Oracle和DB2的写法

Oracle 可以这样实现﹕

Select * from user.bsempms where rownum<=n;

DB2 可以这样实现﹕

Select * from db2admin.bsempms fetch first n rows only;

另外也可以用 row_number() over() 去实现的;

2.如何取得当前日期的Oracle和DB2的写法

Oracle 可以这样实现﹕

Select sysdate from dual;

DB2 可以这样实现﹕

Select current timestamp from sysibm.sysdummy1;

3.如何连接字段的Oracle和DB2的写法

Oracle 可以这样实现﹕

Select emp_no||emp_nam from bsempms;

Select concat(emp_no,emp_nam) from bsempms;

DB2 可以这样实现﹕

Select emp_no||emp_nam from db2admin.bsempms;

select emp_no concat emp_nam from db2admin.bsempms;

4.应该是取当前时间的

Oracle 可以这样实现﹕

Select sysdate from dual;

DB2 可以这样实现﹕

Select current timestamp from sysibm.sysdummy1;

5.内连接INNER JOIN的Oracle和DB2的写法

Oracle可以这样实现﹕

Select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no;

DB2 可以这样实现﹕

Select * from db2admin.bsempms inner join db2admin.bsdptms

on db2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;

6.外连接的Oracle和DB2的写法(右外连接,左外连接,完全外连接,组合外连接)

Oracle可以这样实现﹕

Select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no(+);

Select a.* from bsempms a,bsdptms b wherea.dpt_no(+)=b.dpt_no;

DB2 可以这样实现﹕

Select * from db2admin.bsempms right outer join db2admin.bsdptms

on db2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;

Select * from db2admin.bsempms left outer join db2admin.bsdptms

on db2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;

Select * from db2admin.bsempms full outer join db2admin.bsdptms

on db2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;

7.如何执行脚本SQL文件的Oracle和DB2的写法

Oracle 可以这样实现﹕

SQL>@$PATH/filename.sql;

DB2 可以这样实现﹕

db2 -tvf $PATH/filename 文件中每行以 ; ?尾

1.如何查看数据库的版本的Oracle和DB2的写法

Oracle 可以这样实现﹕

SQL> connect system/manager124@test;

已联机.

SQL> select * from v$version;

 

BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

PL/SQL Release 9.2.0.1.0 - Production

CORE 9.2.0.1.0 Production

TNS for 32-bit Windows: Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 - Production

 

DB2 可以这样实现﹕

在命令窗口执行 db2level

D:/SQLLIB/BIN>db2level

DB210851 Instance "DB2" uses DB2 code release "SQL07020" with level identifier

"03010105" and informational tokens "DB2 v7.1.0.40","n010415" and "WR21254".

2.如何快速清空一个大表的Oracle和DB2的写法

Oracle 可以这样实现﹕

SQL>truncate table table_name;

DB2 可以这样实现﹕

alter table table_name active not logged initially with empty table;

3.如何查看表空间的使用状况的Oracle和DB2的写法

Oracle 可以这样实现﹕

SELECT tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m,to_char(100*sum_free_m/sum_m, '99.99') || '%' AS

 

pct_free

FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name),

( SELECT tablespace_name AS fs_ts_name, max(bytes)/1024/1024 AS max_m, count(blocks) AS count_blocks,

 

sum(bytes/1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name )

WHERE tablespace_name = fs_ts_name

DB2 可以这样实现﹕

list tablespace containers for 你的表空间号 show detail;

4.如何从一时间点取出日期的各部分的常用的Oracle和DB2的写法

Oracle 可以这样实现﹕

1>.取时间点的年份的写法:

SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;

2>.取时间点的月份的写法:

SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL;

3>.取时间点的日的写法:

SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL;

4>.取时间点的时的写法:

SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL;

5>.取时间点的分的写法:

SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL;

6>.取时间点的秒的写法:

SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL;

7>.取时间点的毫秒的写法9I以上)

select SUBSTR(systimestamp,20,6) from dual;

8>.取时间点的日期的写法:

SELECT TRUNC(SYSDATE) FROM DUAL;

9>.取时间点的时间的写法:

SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') FROM DUAL;

10>.日期,时间形态变为字符形态

SELECT TO_CHAR(SYSDATE) FROM DUAL;

11>.将字符串转换成日期或时间形态:

SELECT TO_DATE('2003/08/01') FROM DUAL;

12>.返回参数的星期几的写法:

SELECT TO_CHAR(SYSDATE,'D') FROM DUAL;

13>.返回参数一年中的第几天的写法:

SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL;

14>.返回午夜和参数中指定的时间值之间的秒数的写法:

SELECT TO_CHAR(SYSDATE,'SSSSS') FROM DUAL;

15>.返回参数中一年的第几周的写法:

SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL;

 

DB2 可以这样实现﹕

1>.取时间点的年份的写法:

SELECT YEAR(current timestamp) FROM SYSIBM.SYSDUMMY1;

2>.取时间点的月份的写法:

SELECT MONTH(current timestamp) FROM SYSIBM.SYSDUMMY1;

3>.取时间点的日的写法:

SELECT DAY(current timestamp) FROM SYSIBM.SYSDUMMY1;

4>.取时间点的时的写法:

SELECT HOUR(current timestamp) FROM SYSIBM.SYSDUMMY1;

5>.取时间点的分的写法:

SELECT MINUTE(current timestamp) FROM SYSIBM.SYSDUMMY1;

6>.取时间点的秒的写法:

SELECT SECOND(current timestamp) FROM SYSIBM.SYSDUMMY1;

7>.取时间点的毫秒的写法:

SELECT MICROSECOND(current timestamp) FROM SYSIBM.SYSDUMMY1;

8>.取时间点的日期的写法:

SELECT DATE(current timestamp) FROM SYSIBM.SYSDUMMY1;

9>.取时间点的时间的写法:

SELECT TIME(current timestamp) FROM SYSIBM.SYSDUMMY1;

10>.日期,时间形态变为字符形态:

SELECT char(current date) FROM SYSIBM.SYSDUMMY1;

SELECT char(current time) FROM SYSIBM.SYSDUMMY1;

SELECT char(current date+12 hours) FROM SYSIBM.SYSDUMMY1;

11>.将字符串转换成日期或时间形态:

SELECT TIMESTAMP('2002-10-20-12.00.00.000000') FROM SYSIBM.SYSDUMMY1;

SELECT TIMESTAMP('2002-10-20 12:00:00') FROM SYSIBM.SYSDUMMY1;

SELECT DATE('2002-10-20') FROM SYSIBM.SYSDUMMY1;

SELECT DATE('10/20/2002') FROM SYSIBM.SYSDUMMY1;

SELECT TIME('12:00:00') FROM SYSIBM.SYSDUMMY1;

SELECT TIME ('12.00.00') FROM SYSIBM.SYSDUMMY1;

12>.返回参数的星期几的写法:

SELECT DAYNAME(current timestamp) FROM SYSIBM.SYSDUMMY1;

SELECT DAYOFWEEK(current timestamp) FROM SYSIBM.SYSDUMMY1;

SELECT DAYOFWEEK_ISO(current timestamp) FROM SYSIBM.SYSDUMMY1;

13>.返回参数一年中的第几天的写法:

SELECT DAYOFYEAR(current timestamp) FROM SYSIBM.SYSDUMMY1;

14>.返回午夜和参数中指定的时间值之间的秒数的写法:

SELECT MIDNIGHT_SECONDS(current timestamp) FORM SYSIBM.SYSDUMMY1;

15>.返回参数中一年的第几周的写法:

SELECT WEEK(current timestamp) FORM SYSIBM.SYSDUMMY1