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

dh4bc1er5f

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