The difference between DB2 and Oracle 2____oracle

The difference between DB2 and Oracle

1, DB2 access to the first 10 lines of data and Oracle Difference

DB2:
SELECT * FROM Test fetch a
Oracle:
SELECT * FROM Test where rownum<=10
Query statement
DB2: Do not provide stealth conversion
SELECT * from employee where empno=1010;
Oracle: Provides stealth conversion
SELECT * FROM employee where empno= ' 1010 ';

2, DB2 Insert into and Oracle difference
DB2 allows multiple rows to be inserted like this:
INSERT into staff values (1212, ' cemy ', ', ' Sales ', 3,90000,30000);
(1212, ' cemy ',, ' sales ', 3,90000,30000);
Oracle:www.2cto.com
Sql> INSERT into staff values (1212, ' cemy ',, ' sales ', 3,90000,30000), (1212, ' cemy ')
, ' Sales ', 3,90000,30000)

3, DB2 Update and Oracle update
Db2
DB2 Update staff Set (SALARY,COMM) = (80000,50000);
DB2 update staff set salary=80000,comm=50000;
Oracle:
Sql> update staff set salary=80000,comm=50000;
1 rows have been updated. I
4. Get the system date
Oracle:
Select sysdate from dual;
DB2:
Select current timestamp from sysibm.sysdummy1;

5. Convert date time to character type:
Oracle
To_char (Date_expression_r_r, ' yyyy-mm-dd ')
To_char (Date_expression_r_r, ' HH24:MI:SS ')
DB2
CHAR (Date_expression_r_r,iso)
CHAR (Time_expression_r_r,iso)

6, conversion date time string to date Time type:
Oracle
To_char (Date_expression_r_r, ' yyyy-mm-dd ')
To_char (Date_expression_r_r, ' HH24:MI:SS ')
DB2
DATE (' 2005-05-20 ')
Time (' 18:59:59 ')
Tiemstamp (' 2007-2-1 ', ' 21:12:12 ')
Tiemstamp (' 2007-2-1 21:12:12 ')
Www.2cto.com
DB2 also has To_char and to_date functions, but can only provide a fixed conversion format, as follows
To_char (Timestamp_expression_r_r, ' yyy-mm-dd HH24:MI:SS ')
To_date (String_expression_r_r, ' yyy-mm-dd HH24:MI:SS ')

7, quickly empty large table
Oracle:
TRUNCATE TABLE tablename;
DB2:
ALTER TABLE TableName Active not logged initially with empty table;

8, create similar tables
Oracle:
CREATE TABLE A as SELECT * from B;
DB2:
Create Table a like B;

9, modify the field length or type:
ORACLE:
ALTER TABLE NODES MODIFY node_name varchar (32);
DB2:
ALTER TABLE NODES alter NODE_NAME SET DATA TYPE varchar (32);

10, null value processing to get ABC
Oracle:
SELECT ' abc ' | | C1 from T1 (C1 is NULL)
DB2:
SELECT ' abc ' | | COALESCE (C1, "") from T1

11. Create indexes
Oralce:
Create TABLE T1 ....... In Data_ta
Create Index ..... On T1 ... In Indx_ts
Www.2cto.com
DB2:
Create TABLE T1 ... In Data_ts INDEX in Indx_ts
Create INDEX ..... On T1

12, change the name of the column
Oracle:
ALTER TABLE Test Rename column mail to Mail2;
Db2
Do not provide change column name functionality (workaround with Delete, or resolve by creating a new view)
13. Change Column type
Oracle:alter Table Test Modify column (Mail2 integer);
Db2:alter table test ALTER mail varchar (256) can only be widened and the type cannot be changed

14 The difference between creating a procedure parameter
1 The location of the parameter type and parameter name is different
DB2:
CREATE PROCEDURE PRO1 (in OrgID int)
Oracle
CREATE PROCEDURE PRO1 (orgid in int)
2) as input and output parameters are different
DB2:
CREATE PROCEDURE PRO1 (INOUT orgid int) INOUT attached write
Oracle:www.2cto.com
The CREATE PROCEDURE PRO1 (orgid in out int) is separated from the middle space, and must be in before out
3 when no input or output parameters
DB2:
CREATE PROCEDURE PRO1 ()
Oracle
CREATE PROCEDURE PRO1 cannot have empty parentheses

Variable definition and begin end body position are different
DB2 variables are defined in the begin end body, and each variable is declared with declare, end of the stored procedure is followed by P1, and no semicolon is required
In Oracle, the variable definition must be outside the begin end body, and the variable does not need to be declared with the declare (there is a saying that the first variable needs to be declare, the other variable is not required); At the end of the stored procedure, the name of the stored procedure is followed by a semicolon
DB2:
CREATE PROCEDURE PRO1 ()
LANGUAGE SQL
P1:begin
--Variable definition
DECLARE insert_date TIMESTAMP;
DECLARE alldept_no VARCHAR (20);
--Concrete Operation www.2cto.com
SELECT a from tab_1;
.........
End P1
Oracle
CREATE PROCEDURE PRO1
Is
--Variable definition
Insert_date TIMESTAMP;
Alldept_no VARCHAR (20);
BEGIN
--Concrete operation
SELECT a from tab_1;
.........
End PRO1;

15, control statements of the different
DB2:
IF ... THEN ... ELSEIF THEN ..... End IF;
Oracle
IF ... THEN ... Elsif THEN ..... End IF;

16. How to execute script SQL file
Oracle:
@ $PATH/filename.sql;
Db2:www.2cto.com
DB2-TVF $PATH/filename.sql

17, view the current user's table and view
DB2:
LIST TABLES
ORACLE:
SELECT * from TBAB;

Db2:drop table deletes tables and definitions. List tables found that the table does not exist.
Oracle:drop table deletes the definition of a table's content reservation table. can see the table.