Differences between DB2 and oracle

Differences between DB2 and oracle 1. Differences between the first 10 rows of data accessed by DB2 and Oracle DB2: select * from test fetch first 10 rows onlyOracle: select * from test where rownum <= 10 query statement Db2: do not provide implicit conversion select * from employee where empno = 1010; oracle: provide implicit conversion select * from employee where empno = '000000'; 2. Difference Between DB2 Insert into and Oracle DB2 allows insert of multiple rows like this: Insert into staff values (1010, 'cmy', 20, 'sales', 30000, 1212); (30000, 'cmy', 20, 'sales',); oracle: www.2 Cto.com SQL> insert into staff values (1212, 'cmy', 20, 'sales', 30000, 1212), (, 'cmy', 20, 'sales, 30000) 3. DB2 Update and Oracle UpdateDb2DB2 update staff set (salary, comm) = (80000); DB2 update staff set salary = 50000, comm =; Oracle: SQL> update staff set salary = 80000, comm = 50000; 1 row updated. I4. Obtain 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. Convert Date and Time string to date and time type: Oracle TO_CHAR (date_expression_r_r, 'yyyy-MM-DD ') TO_CHAR (date_expression_r_r, 'hh24: MI: SS ') DB2 DATE ('2017-05-20') TIME ('18: 59: 59') TIEMSTAMP ('2017-2-1 ', '21: 12: 12 ') TIEMSTAMP ('2017-2-1 21:12:12 ') www.2cto.com DB2 also has TO_CHAR and TO_DATE functions, but can only provide fixed conversion formats, as shown below 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 clear a large table Oracle: truncate table TableName; DB2: alter table TableName active not logged initially with empty table; 8. create a table like 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. Obtain abcOracle: SELECT 'abc' after NULL value processing | c1 FROM t1 (c1 is null) DB2: SELECT 'abc' | COALESCE (c1 ,'') FROM t1 11. Create indexes Oralce: Create TABLE T1 ............. IN DATA_TACreate Index ........ ON T1 ...... IN INDX_TS www.2cto.com DB2: Create TABLE T1 ........ IN DATA_TS index in INDX_TSCreate INDEX ..... ON T1 12. Change the column name oracle: alter table test rename column mail to mail2; db2 does not provide the function of changing the column name (the solution is to delete the column name, or create a new view) 13. Change the column type oracle: alter table test modify column (mail2 integer); db2: alter table test alter mail varchar (256) can only be widened, you cannot change the differences between the PROCEDURE creation parameters IN Type 14. 1) the parameter type and parameter name location are different. db2: create procedure PRO1 (IN OrgID int) oracle: create procedure PRO1 (OrgID IN int) 2) The statement used as the input and output parameters is different. db2: create procedure PRO1 (INOUT OrgID int) INOUT is connected to oracle: www.2cto.com create procedure PRO1 (OrgID in out int) in out are separated by spaces, and must be IN before OUT. 3) If no input or output parameter exists, db2: create procedure PRO1 () oracle: create procedure PRO1 variable definitions that cannot be empty and the position of the begin end body are different. In db2, variables are defined in the begin end body, and each variable must be declared with DECLARE. At the END of the stored PROCEDURE, the END is followed by P1, and the variable definition in oracle does not need to be in the external body of the begin end, and the variables do not need to be declared with DECLARE (there is a saying that the first variable requires DECLARE, other variables are not required); END is followed by the name of the stored PROCEDURE at the END of the stored PROCEDURE, and the semicolon db2: create procedure PRO1 () language SQL P1: BEGIN -- variable definition DECLARE INSERT_DATE TIMESTAMP; DECLARE ALLDEPT_NO VARCHAR (20); -- the specific operation is www.2cto.com SELECT a FROM TAB_1 ;......... END P1oracle: create procedure PRO1 IS -- variable definition INSERT_DATE TIMESTAMP; ALLDEPT_NO VARCHAR (20); BEGIN -- specific operation SELECT a FROM TAB_1 ;......... END PRO1;
15. Different db2 control statements: IF... THEN ...... elseif then ...... end if; oracle: IF... THEN ...... elsif then .... end if; 16. How to execute the script SQL file Oracle: @ $ PATH/filename. SQL; DB2: www.2cto.com db2-tvf $ PATH/filename. SQL 17. view the current user's TABLES and views DB2: LIST TABLES ORACLE: SELECT * FROM TBAB; DB2: DROP TABLE Delete TABLES and definitions. List tables finds that the table does not exist. ORACLE: drop table delete TABLE content retain TABLE definition. You can see the table. ----------------------------------------------------------------- In addition to the content shared today: [quick copy table] 1. copy the TABLE structure and data to the new table create table new table select * FROM old TABLE (DB2 is not applicable) 2. only copy the TABLE structure to the new TABLE (1) oracle www.2cto.com create table new table select * FROM old table where 1 = 2 that is: Make the WHERE condition invalid. (2) DB2CREATE TABLE new table like old TABLE 3. copy the data FROM the old table to the new table (assuming the two tables have the same structure) insert into the new table SELECT * FROM the old table 4. copy the data from the old table to the new table (assuming the two tables have different structures) insert into the new table (Field 1, Field 2 ,.......) SELECT Field 1, Field 2 ,...... FROM old table author tbwshc