Stored Procedures and Java calls

Document directory

• Process and trigger Overview

Process and trigger Overview

Stored Procedure SQL statements in the database for all applications. These include control statements that allow repeated execution of SQL statements (loop statements) and conditional execution (if statements and case statements.

The process is called through the call statement, and the parameter is used to accept the value and return the value to the call environment. The SELECT statement can also operate the process result set by including the process name in the from clause.

The process can return the result set to the caller, call other processes, or trigger. For example, a user-defined function is a stored procedure that returns a single value to the calling environment. User-defined functions do not modify the parameters passed to them, but expand the range of functions that can be used for queries and other SQL statements.

Triggers are associated with specific database tables. The trigger is automatically triggered when someone inserts, updates, or deletes a row in the associated table. Triggers can call the process and trigger other triggers, but they do not have any parameters and cannot be called by the call statement.

Example

The following is a simple example of the new_dept creation process, which executes the insert operation to the Department table of the sample database to create a new department.

CREATE PROCEDURE new_dept (   IN id INT,   IN name CHAR(35),   IN head_id INT )BEGIN   INSERT   INTO DBA.department ( dept_id,       dept_name, dept_head_id )   VALUES ( id, name, head_id );END

The main body of the process is compound statements. The compound statement starts with a begin statement and ends with an end statement. In the new_dept example, a composite statement is a single insert Between the in and end statements.
Process parameters can be marked as one of In, out, or inout. By default, the parameter is the inout parameter. All parameters of the new_dept process are in parameters because they are not modified in this process.
Call Process
 
Call statement call process. A process can be called by an application or by other processes and triggers.
For more information, see call statements.
The following statement calls the new_dept process to insert the eastern sales department:
CALL new_dept( 210, 'Eastern Sales', 902 );
After this call, you 'd better check the Department table to see if the new department has been added.
All users that have been granted the execute permission for this process can call the new_dept process even if they do not have any permissions for the department table.
For more information about the execute permission, see execute statements.
[Esql].
Another way to call the process of returning a result set is to call it in the query. You can query the result set of the process and apply the WHERE clause and other select functions to limit the result set.
SELECT t.id, t.quantity_ordered AS qFROM sp_customer_products( 149 ) t
For more information, see the from clause.
Example
The following statement deletes new_dept from the database:
DROP PROCEDURE new_dept
 
 
 

  
  
 
   
 
    
try{ 
int age = 39; 
String poetName = "dylan thomas"; 
CallableStatement proc = connection.prepareCall("{ call set_death_age(?, ?) }"); 
proc.setString(1, poetName); 
proc.setInt(2, age); 
cs.execute(); 
}catch (SQLException e){ // ....}