DB2 SQL stored procedure Study Notes

In the last two phases of the DB2 project, data in the temporary table needs to be transferred to the formal table during the system idle period, or some time-consuming operations must be performed on some tables in the background. Stored procedures are required. Basic concepts:
Stored procedures are stored procedure, which is generally referred to as procedure. To learn this, you must first understand another concept: routine, which is generally translated as a "routine"
> 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) Standardize the application logic (not understanding, is dB logic isolated from app logic ?)

Development Features:
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 (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. The stored procedure 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:
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 dynamic composite statements (SQL statement blocks in a single call)
> 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) = );
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; // the label is 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; // optional
Example:
For EMP as select * from employee where bonus> 1000 do
Set total_bonus = total_bonus + EMP. bonus;
End;
> 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_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;