Detailed analysis of the DB2 SQL stored procedure (1)

The following articles mainly describe the basis of the DB2 SQL stored procedure. The stored procedure is simply procedure. To learn this, we 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:

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: 
 
 

Three types of parameters:

1) IN: Incoming data to stored procedure

2) OUT: stored procedure returns data

3) INOUT: the part of data passed in is overwritten by the returned data during execution.

Result sets:

Stored procedure uses cursor to pass the result set to the caller. DB2 SQL 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)