DB2 stored Procedure Syntax rules

How to declare a stored procedure
CREATE PROCEDURE Stored procedure name (in input variable name input variable type, out output variable name output variable type)
followed by a list of stored procedure properties
Commonly used are: LANGUAGE sql, modifies SQL DATA, result sets 1 (number of result sets returned)
l The stored procedure body begins with begin
L End of stored procedure body with end
Stored procedure constraint rules
Calling stored procedures in stored procedures
Call stored procedure name (parameter 1, parameter 2, parameter n)
Cases:
Call Spco_init_custom (BANKCODE,ERRNO,ERRMSG);
GET Diagnostics Retval=return_status;
if (retval<>0) then
Set Errno=errno;
Set errmsg=errmsg;
return errno;
End If;

Definition of a variable
A variable must be defined before it can be used by
DECLARE variable name variable type (default value)
Cases:
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE inum INTEGER DEFAULT 0;
DECLARE Curtime char (8);
DECLARE Bcode char (6);
DECLARE SQLState char (5);
An If expression
If condition 1 Then
logical body;
ElseIf Condition 2 Then
logical body;
Else
logical body;
End If;
Cases:
IF rating = 1 Then
UPDATE Employee
SET Salary = salary * 1.10, bonus = 1000
WHERE empno = employee_number;
ELSEIF rating = 2 Then
UPDATE Employee
SET salary = salary * 1.05, bonus = 500
WHERE empno = employee_number;
ELSE
UPDATE Employee
SET Salary = salary * 1.03, bonus = 0
WHERE empno = employee_number;
END IF;
Case expression
Case variable name when
Variable value 1 Then
. . .
When
Variable Value 2 Then
- - -
Else
. . .
End case;
Or
Case
Variable name = variable value 1 Then
. . .
When
Variable name = variable Value 2 Then
- - -
Else
. . .
End case;
Example one:
Case V_workdept
When ' A00 '
Then UPDATE Department
SET deptname = ' DATA ACCESS 1 ';
When ' B01 '
Then UPDATE Department
SET deptname = ' DATA ACCESS 2 ';
ELSE UPDATE Department
SET deptname = ' DATA ACCESS 3 ';
END case;
Example two:
Case
When v_workdept = ' A00 '
Then UPDATE Department
SET deptname = ' DATA ACCESS 1 ';
When v_workdept = ' B01 '
Then UPDATE Department
SET deptname = ' DATA ACCESS 2 ';
ELSE UPDATE Department
SET deptname = ' DATA ACCESS 3 ';
END case;
for-expression
For Loop name as
Cursor name or select expression
Do
an SQL expression;
End for;
Cases:
1)
DECLARE fullname CHAR (40);
For VL AS
SELECT Firstnme, Midinit, LastName from employee
Do
SET fullname = LastName | | ', ' | | Firstnme | | ' ' | | Midinit;
INSERT into Tnames VALUE (fullname);
END for
2)
For LOOPCS1 as Cousor1 cursor as
Select Market_code as Market_code
From Tb_market_code
For update
Do
End for;
Goto expression
Goto flag Name;
Label name:
logical body;
Cases:
GOTO FAIL;
...
Success:return 0
fail:return-200
While expression
While conditional expression do
logical body;
End while;
Loop expression
LOOP ... END LOOP;
Cases:
OPEN C1;
Ins_loop:
LOOP
FETCH C1 into V_dept, V_deptname, v_admdept;
IF at_end = 1 Then
Leaveins_loop; --Interrupt Loop
ELSEIF v_dept = ' D11 ' Then
Iterateins_loop; --Next loop
END IF;
INSERT into department (DEPTNO, Deptname, admrdept)
VALUES (' NEW ', V_deptname, v_admdept);
END LOOP;
CLOSE C1;
About Cursors
To define a cursor:
DECLARE cursor named cursor for
Select statement;
To open a cursor:
OPEN cursor name;
Value:
FETCH cursor name into variable list
Cases:
DECLARE C1 CURSOR for
SELECT CAST (Salary as DOUBLE)
From staff
WHERE DEPT = Deptnumber
ORDER by salary;
DECLARE EXIT HANDLER for not FOUND
SET mediansalary = 6666;
SET mediansalary = 0;
SELECT COUNT (*) into v_numrecords
From staff
WHERE DEPT = Deptnumber;
OPEN C1;
While V_counter < (V_NUMRECORDS/2 + 1) do
FETCH C1 into mediansalary;
SET V_counter = v_counter + 1;
END while;
CLOSE C1;
Note: If you place the cursor in the middle segment, use "Begin ... End; ". Segment split Mark split open;
Dynamic SQL
1) DECLARE stmt varchar (1024);
Set stmt= ' CREATE TABLE zhouhaiming (F1 smallint, F2 varchar (9), F3 char (5)) ';
Prepare S1 from stmt;
Execute S1;
Set stmt= ' INSERT into zhouhaiming values (1, ' www ', ' aaa ') ';
Prepare S1 from stmt;
Execute S1;
2) DECLARE CURSOR C1 for STMT1;
PREPARE STMT1 from
' ALLOCATE C2 CURSOR for RESULT SET? ';
Establishment of temporary tables
DECLARE GLOBAL Temporary TABLE table_name
As (Fullselect) DEFINITION only
Excluding IDENTITY COLUMN ATTRIBUTES
On COMMIT DELETE ROWS
Logged in temporary tablespace name with replace;
The first line rules the name of the time table.
The second line defines the definition of the column of the time table.
The third row is not an identical column that is restored from the source results table definition.
Line four if you do not open the with gold light, all rows of the table will be erased.
The five-line rule does not record the changes in the table.
The WITH REPLACE option implicitly deletes the temporary table automatically.
For example:
DECLARE GLOBAL Temporary TABLE Dec_bsempms
As (SELECT * from Bsempms) DEFINITION only
Excluding IDENTITY COLUMN ATTRIBUTES
On COMMIT DELETE ROWS
Not logged;
Several global variables in the DB2
n row_count-affects the number of rows
UPDATE Corpdata. PROJECT
SET Prstaff = Prstaff + 1.5
WHERE DEPTNO = DEPTNBR;
GET diagnosticsrcount = Row_count;
n return_status--Return status
Call tryit;--calling a stored procedure
GET diagnosticsretval = return_status;
IF RETVAL <> 0 Then
...
LEAVE A1;
ELSE
...
END IF;
N Sqlstate-sql return error code
Note: must be defined before use
DECLARE SQLState char (5);
Declare state char (5);
INSERT into tbname values (...)
Set state=sqlstate;
if (state<> ' 00000 ') then
return-1;
End If;
About ATOMIC and not ATOMIC
P1:begin atomic–p1 section of the transaction will be automatically rolled back
P1:begin not ATOMIC–P1 transactions are not automatically rolled back
Conditional handles in DB2
Handle Type:
N CONTINUE
N EXIT
N UNDO
Condition Type:
N SQLSTATE String
N SQLEXCEPTION
N sqlwarning
N Not FOUND
Cases:
1) DECLARE EXIT HANDLER for not FOUND
SET mediansalary = 6666;
2) DECLARE Not_found CONDITION for SQLSTATE ' 02000 ';
DECLARE EXIT HANDLER for Not_found
SET rating =-1;
3) DECLARE Not_found CONDITION for SQLSTATE ' 02000 ';
DECLARE C1 CURSOR for
SELECT Deptno, Deptname, admrdept
From department
ORDER by Deptno;
DECLARE CONTINUE HANDLER for Not_found
SET at_end = 1;
How to extract/submit a stored procedure
DB2 "get routine into filename from procedure stored procedure name"
extracting stored procedures;
Submit a Stored procedure
DB2 "put routine from file name"
Install the compiled stored procedure.
How to commit a stored procedure under a command prompt
Add the @ symbol at the end of the stored procedure and then enter it at the command: DB2 [email protected]-VF Procfile.sql can generate the process.
SQL file for non-stored procedure, break in command: DB2–TVF sqlfile.sql
To return the use of a result set (cursor) from a stored procedure
1. Build one SP return result set
CREATE PROCEDURE Db2inst1. Proc1 (
LANGUAGE SQL
Result sets 2-(returns two result sets)
P1:begin
Declare c1 cursor with return to caller for
Select Market_code
From Tb_market_code;
--Specifies that the result set is used to return to the caller
DECLARE C2 cursor with return to caller for
Select Market_code
From Tb_market_code;
Open C1;
Open C2;
END P1

2. Build an SP to tune the SP and use its result set

CREATE PROCEDURE Db2inst1. PROC2 (
Out Out_market_code char (1))
LANGUAGE SQL
P1:begin
DECLARE loc1,loc2 result_set_locator varying;
--Create an array of result sets
Call Proc1;
--Call the SP to return the result set.
Associate result set Locator (LOC1,LOC2) with procedure Proc1;
--associating the returned result set and the result set array
Allocate CURSOR1 cursor for result set loc1;
Allocate CURSOR2 cursor for result set loc2;
--Assigning a result set array to the cursor
Fetch cursor1 into Out_market_code;
--Assign values directly from the result set
Close Cursor1;
END P1

This article from Csdn Blog, reproduced please indicate the source:http://blog.csdn.net/aawanghuan/archive/2008/02/20/2108951. aspx

DB2 stored Procedure Syntax rules