DB2 Stored Procedures

I. Benefits of using Stored procedures

1. Reduce the network usage between the client and the server, and the time the database lock is held

Applications typically execute every SQL statement across the network two times, and stored procedures can centralize SQL statements

This makes it possible to only cross the network two times for each set of SQL statements. The more SQL statements that are lumped together in a stored procedure,

The lower the network usage and the less time the database lock is held. By reducing network usage and the length of time the database is locked,

You can improve the overall performance of your network and reduce lock contention issues.

Two. Language for stored procedures

In DB2 's "Dev Center", you can create stored procedures in Java or SQL.

Three. SQL stored procedures

1. Writing stored procedures using the SQL procedure language has the following advantages:

A. You can debug SQL stored procedures by using the integrated debugger in the dev Center

B. With SQL stored procedures, you can call other SQL procedures, which can be nested up to 16 layers of calls

C. SQL stored procedure runs fast because it is run as a compiled routine

2. SQL stored procedures have size and parameter limits, depending on the version of DB2 that is running:

A. For DB2 Windows and UNIX editions, the maximum size of SQL stored procedures is 64KB in version 7 and version 8

Four. Java Stored procedures

1. Using the Java language to write stored procedures has the following advantages:

A. Within the Java security restrictions, you can use Java stored procedures for file input/output. SQL stored procedures do not support

The input/output of the file.

Five. Basic syntax for stored procedures

Create procedureDb2inst.proce1 (inch"Inparam"integer, out "Outparam"varchar(Ten) ) Specific "Proce1" language sqldynamic result sets1 notdeterministicexternal actionmodifies SQL Dataold savepoint LevelbeginL1:begin        /*variable definition*/        DeclareInuminteger default 0; /*assigning values to variables*/        SetInum=  -; /*Branch Statements*/        ifCondition 1 Then... elseif condition 2 Then            ...        Else             ...        End if; /*Multi-branch statement case*/         CaseVariable name whenVariable value 1 Then                 ...             whenVariable Value 2 Then                 ...            Else                 ...        End  Case; /*For Loop*/         forVariable name ascursor name or select expression do ...End  for; /*While Loop*/         whileconditional expression do ...End  while; /*Loop Statement*/insloop:loop leave Insloop;/*Interrupt Loop*/iterate Insloop;/*Next Loop*/        EndLoop; /*How cursors are used one*/            /*Defining Cursors*/            DeclareCursor namecursor  for Selectstatement; /*Open Cursor*/            Opencursor name; /*Take value*/            FetchCursor name intovariable list; /*Close Cursors*/            Closecursor name; /*Goto Statement*/        Gotofail; ... success:return 0fail:return - $    EndL1; /*How cursors are used two*/    /*The definition of a cursor should be "begin...end;" if placed in the middle segment. Segment split flag split open*/L2:begin        DeclareV_notfoundinteger default 0; Declarestmt statement;/*declare the value of the drop cursor*/                DeclareCurcursor  with return  forstmt/*declaring dynamic cursor storage variables*/        Declare ContinueHandler for  notFoundSetV_notfound= 1; Preparestmt fromQrysql; Opencur; FetchCur intovariable list; Closecur; EndL2; End

View Code

Six. Description of stored Procedure properties

ProcedureName: The name of the stored procedure, in the same mode of the same database, can not exist stored procedures with the same name, the same number of parameters, even if the parameters of different types (in|out|inout paramname dataType, ...) : Incoming parameter in: input parameter, out: Output parameter, inout: As input output parameter dataType: parameter type, can receive SQL type and created table, does not support long varchar, long vargraphic,datalink, Reference and user-defined types.    Specific specificname: Unique specific name (alias), which can be replaced with a stored procedure name. Used to add comments to a stored procedure, but not to call a stored procedure. If not specified, the database automatically generates a YYMMDDHHMMSSHHN timestamp name. Language sql: Specifies that the body of the procedure is SQL language dynamic result sets integer: Specifies the maximum number of results returned by the stored procedure, or DB2 returns a warning deterministic or not if it is less than the actual return quantity    Deterministic: Indicates that the stored procedure is dynamic or non-dynamic. The dynamic return value is indeterminate, and the non-dynamic return value is the same external action or no external action: Indicates whether the stored procedure performs an activity that alters the state of the database, not through the database manager. The default is external action. If you specify no external action, the database determines the optimal optimization scheme.    Contains SQL, reads SQL data, modifies SQL data: Specifies the SQL access level in the stored procedure contains SQL: Indicates that the stored procedure can be executed, neither SQL data nor SQL data cannot be modified.    Reads SQL data: Indicates that the stored procedure can be executed, read SQL, but not modify the SQL. Modifies SQL data: Indicates that a stored procedure can execute any SQL statement.    Data in the database can be added, deleted, and modified. Old savepoint level or new savepoint level: establishes a storage point (storing data at some time), the old savepoint level is the defaultCalled on NULL input: Indicates that a stored procedure can be called, regardless of whether any of the input parameters are null, and any out or inout parameter can return a null or non-null value. Verifying that the parameter is null is done in the process.    Inherit Special registers: Represents the inheritance of the private register parameter CCSID: Specifies the encoding of all output string data, the default Unicode encoding database is: Parameter Ccsid Unicode, The other databases default to: Parameter Ccsid 3 ASCII

View Code

Seven. A stored procedure invokes another stored procedure, returning a result set

1. Stored Procedure 1: Returns 2 result sets

Create procedureDb2inst.proc1 () language Sqlresult sets2P1:begin    DeclareC1cursor  with return  toCaller forSELECT statement; DeclareC2cursor  with return  toCaller forSELECT statement; OpenC1; OpenC2; ...EndP1

View Code

2. Stored Procedure 2: Call stored procedure 1, get 2 result set

Create proceduredb2inst.proc2 () language sqlP1:begin    /*create an array of result sets*/    DeclareLOC1,LOC2 Result_set_locatorvarying; /*Call stored procedure 1, return result set*/Call Proc1; /*associating the returned result set with the result set array*/Associate ResultSetLocator (LOC1,LOC2) with procedureProc1; /*assigning an array of result sets*/Allocate Cursor1cursor  forResultSetLoc1; Allocate Cursor2cursor  forResultSetloc2; /*take values directly from the result set*/    FetchCursor1 intovariable list; CloseCursor1; ...EndP1

View Code

DB2 Stored Procedures