Quick Start to DB2 Stored Procedure"

The following articles mainly describe the simple solution for getting started with DB2 stored procedures. The basic concept of DB2 stored procedures is stored procedure, which is generally referred to as procedure. If you want to learn this, you must first understand another concept: routine, which is generally translated into "routines ".

> Routine: database objects that exist on the server and are written according to the application logic and can be called through the client or other routine.

> Three types: stored procedures, UDFs (custom function), and methods.

Stored procedures: serves as a client extension but runs on the server; UDFs: Extension and custom SQL; methods: provides structured behavior

> Two forms:

1) SQL routines: fully written in SQL. You can use create statement to register a routine.

2) external routines: it is written in C, C ++, Java, OLE, and stored procedure can also be written in cobol. Any language can contain SQL.

Different forms of routines can be called to each other, regardless of the language.

Let's take a look at stored procedure.

> Stored procedures: it can be called by the client or other routine through call statement; stored procedures and its calling program exchange data through parameters in create procedure statement; stored procedures can return result sets to its callers.

Advantages of stored procedures:

1) Multiple SQL statements can be executed by the caller once, which reduces data transmission between the client and server.

2) isolate the database logic from the application logic

3) Multiple result sets can be returned.

4) if it is called by an application, stored procedure runs like a part of the application.

Disadvantages:

1) cannot be called by SQL statement, except by calling

2) The returned result set cannot be directly used by SQL statement.

3) the status of a call cannot be saved between multiple calls, that is, the call is independent and information cannot be transmitted.

General application:

1) provide an interface to a group of SQL statements. For example, insert operations on multiple tables at the same time

2) is db logic isolated from app logic if the logic of standardized applications is not understood ?)

Development Features of DB2 stored procedures:

After understanding these basic concepts, let's take a look at the features of development. According to the above information, there are many routine development languages. This article only describes SQL procedure, that is, procedure written by SQL/SQL pl ).

> Features of various languages

SQL:

1) The efficiency is higher than that of java routine, basically equivalent to c/c ++ routine

2) It is completely written in SQL and can be executed quickly (making them quick to implement)

3) DB2 regards SQL routine as 'safe 'because it is all SQL, so that SQL routine can run directly on the db engine, good performance and scalability)

 
 

  
  
>>stored procedure feathures:  
  
  
parameter modes:  
 
 

3 types of parameters: 1) IN: Incoming data to stored procedure 2) OUT: stored procedure returns data 3) INOUT: the part of the incoming data, overwritten by returned data during execution

 
 

  
  
result sets: 
 
 

Stored procedure uses cursor to pass the result set to the caller. DB2 stored procedures must retain a cursor for each result set to be returned.

> Use with return to caller/client to specify the objects returned by the result set. If this parameter is set to client, the routine called in the middle cannot obtain the result set. Only the client can obtain the result set.

> Use the dynamic result sets statement to specify the number of returned result sets, which is saved in the result_sets field in the syscat. routines view. If the actual number of returned result sets is greater than the declared number, a warning (sqlcode + 464, sqlstate 0100E) will be issued)

Procedure for returning a result set using SQL stored procedure:

1) declare cursor:

For example, declare clientcur cursor with return to caller for select * from staff;

2) open the cursor: such as open clientcur;

3) Exit stored procedure without closing the cursor

Development of DB2 stored procedures:

Finally, I finally came to real development. I just mentioned that SQL procedure is written by SQL and SQL pl, so there is nothing to say about SQL. Key words: SQL pl (procedural language)

> Function: control the logical flow direction, declare and set variables, and handle warnings and exceptions. It can be used for routines (routine), triggers, and SQL statement blocks in a single call of dynamic composite statements)

> Control statement: declare, set, for, get diagnostics, if, iterate, leave, return, signal, while

> SQL pl statements that cannot be executed: create and drop of table, index, and view

> Begin atomic starts with and ends with end

> Declare: defines variables and definitions for error handling

Declare SQL-var-name data-type default-values

Declare condition-name condition for sqlstate value... // The condition here is generally interpreted as "exception ".

> Set: Declares variables and assigns values to columns in the table defined by the trigger.

Set pay = select salary from employee where empno = 5; // only one value is returned.

Set pay = null; // null

Set pay = default; // default value defined by the variable

// Private register content

Set userid = userid;

Set today = current date;

// Assign values to multiple variables at the same time

 
 

  
  
set pay =10000,bonus = 1500;  
  
  
set (pay,bonus) = (10000,1500);  
  
  
set (pay,bonus) = select (pay,bonus) from employee where empno = 5;  
  
  
>>if/then/else  
 
 

Three forms:

1) if then/end if statement Block

2) if then/else/end if

3) if then/elseif/else/end if

You can use the SQL operator in the if/then/else statement, for example:

 
 

  
  
if (salary between 10000 and 90000) then...  
  
  
if (deptno in ('a00','b01')) then..  
  
  
if (exist (select * from employee)) then...  
  
  
if (select count(*) from employee)>0) then..  
  
  
>>while  
  
  
label:  
  
  
while condition do  
  
  
...sql pl ..  
  
  
end while lable;   
 
 

Label (optional)

> For: Used to loop the rows in the select returned result set

Format:

 
 

  
  
label:  
  
  
for row_label as select satement do  
  
  
..sql pl..  
  
  
end for label;  
 
 

Label (optional)

Example:

 
 

  
  
for emp as select * from employee where bonus >1000 do  
  
  
set total_bonustotal_bonus = total_bonus +emp.bonus;  
  
  
end for;  
 
 

> Iterate: Used to return to the for or while loop and re-execute it.

 
 

  
  
check_bonus:   
  
  
for emp as select * from employee do  
  
  
if(emp.bonus>10000) then  
  
  
set total_bonustotal_bonus = total_bonus +emp.bonus;  
  
  
else  
  
  
iterate check_bonus;  
  
  
end if;  
  
  
end for check_bonus;  
 
 

> Leave: equivalent to break in java. A label is required.

> Signal: Alarm for abnormal applications

Signal sqlstate value set message_text = '...'; // customize a sqlstate, 7, 8, 9, and I ~ Sqlstate starting with Z

Signal condition set message_text = '...'; // custom exception condition

> Get diagnostics: number of records affected by the update, insert, and delete statements returned by SQL pl triggers or statement blocks (not functions.

 
 

  
  
get diagnostics variable = row_count; 
 
 

If you are not familiar with the DB2 stored procedure, you will gain a lot after reading this article.