"Channel" Description of DB2 pure SQL stored procedure

The following articles mainly describe the practice solution for getting started with DB2 pure SQL stored procedures. The actual operation background is that I am currently using Birt In the DB2 9.1 database, you need to write the pure SQL stored procedure of the DB2 database, query multiple tables through multi-layer nested loops, and return a cursor opened by the final result set ).

Then you can directly call the following in birt to obtain the result set record and display it on the page. To this end, I searched a lot of information on the Internet, but there was no complete DB2 stored procedure that could be executed. After studying for a long time, I finally wrote a template example. Now, I will share my thoughts with you.

Example: create a temporary table, insert data, query the temporary table, and return a cursor.

Create procedure testPrc ()

-- Returns a record.

Dynamic result sets 1

-- DB2 pure SQL stored procedure

P1: begin

-- Define a global temporary table tmp_hy

 
 

  
  
declare global temporary table session.tmp_hy  
  
  
(  
  
  
dm varchar(10),  
  
  
mc varchar(10)   
  
  
) 
 
 

With replace -- if this temporary table exists, replace

Not logged; -- not recorded in logs

Insert three data entries to the temporary table

 
 

  
  
insert into session.tmp_hy values('1','1');  
  
  
insert into session.tmp_hy values('1','1');  
  
  
insert into session.tmp_hy values('1','1');  
  
  
p2: begin  
 
 

Declared cursor

 
 

  
  
declare cursor1 cursor with return for  
  
  
select * from session.tmp_hy;  
 
 

The cursor remains open for the client application.

 
 

  
  
open cursor1;  
  
  
end p2;  
  
  
end p1  
 
 

In addition, some people have asked how to execute this example. I implemented it in the integrated development environment. there are many ways to run the stored procedure, which can be called in advanced programming languages, or through command lines, or through SQL development tools. This cursor cannot be generalized, to see the results, you need to retrieve all records one by one. these are other aspects of knowledge. You can add them elsewhere. how do I run it here and how to handle it? To avoid misleading readers, I will not continue. the following is the result of calling this stored procedure, for example:

Download (1.76 KB)

Summary: This example is very simple, but very practical. It can be used as a template for DB2 pure SQL stored procedures. Temporary tables are fully operated. You can execute the temporary tables in any db2 database and view the results. To keep the example simple and easy to understand, I didn't set the input and output parameters. These parameters are similar to the parameters of functions in Oracle in advanced language, so I wrote this parameter without it.

Question: The stored procedures and functions of DB2 differ greatly from those of Oracle. DB2 stored procedures allow the return value and can also set the number of returned values. The stored procedures of Oracle do not allow the return value; DB2 functions can also return values or tables, while Oracle returns results at will.

It is very painful to use DB2 for development, because there is no good development tool, unlike Oracle's powerful development tools such as PL/SQL, however, DB2 has better performance than Oracle, especially for databases with massive T-level data. At present, I am using Quest Central For DB2 4.3, which has very weak functions and does not even have the column name function For the automatically pop-up table! The built-in development center of DB2 is worse and cannot be used. shit! Seriously not in line with IBM's brand. I don't know what development tools are used by all netizens?