Differences between Oracle and DB2 stored procedures

This article mainly describes the differences between the Oracle database and the DB2 stored procedure. If you are curious about the differences between the Oracle database and the DB2 stored procedure, you can click to view the following articles. The following is a detailed description of the articles. I hope you can learn from them.

Differences between Oracle and DB2 stored procedures

1) The stored procedure is actually implemented by SQL statements at, but logical processes can be added.

Oracle's stored procedure only understands its syntax, and has recently learned the DB2 stored procedure. I always feel that it has no effect to look at its syntax or something. Let's take a look at the Oracle Stored Procedure file in the project and try to change it to db2. Several differences are found:

 
 

  
  
Create or replace procedure proname(var_in in number,var_out out number)  
  
  
As  
  
  
Var1 varchar2(18);  
  
  
Var2 user.name%TYPE;  
  
  
Begin  
  
  
Var_out := 0;  
  
  
Execute immediate ‘truncate table user’;  
  
  
…  
  
  
End  
  
  
create procedure proname(in var_in int,out var_out int)  
  
  
Declare var1 varchar(18);  
  
  
Begin  
  
  
Set var_out=0;  
  
  
…  
  
  
End  
 
 

1) DB2 does not support the "or replace" syntax. The parameter names and IN/OUT locations must also be exchanged. The declare keyword must be used for variable declaration, you must use the set keyword when assigning values to variables.

2) For the % TYPE Function in Oracle, DB2 does not seem to have a good implementation method yet.

3) Similarly, the command truncate for clearing tables in Oracle and DB2 cannot be referenced as is, but it also provides two implementation methods: first, import the content of an empty file into the table to import null. dat; second, set the table feature to not record the log alter table tablename. however, the second method may cause the original table to be unavailable in some cases. The specific method has not been clarified yet.

4) The performance of SQL statements is different from that of Oracle and db2. For example, Oracle rownum, db2 can use fetch first n only, or row_number () over.

Of course, the method for calling a stored procedure is the same, call proname (2 ,?), Used for output parameters? .

For the above 2nd points to a good solution, this function is used in the DB2 stored procedure is quite a lot.

The above content is an introduction to the differences between the stored procedures in Oracle and DB2. I hope you will find some gains.