Informix Stored Procedure Structure

1. Introduction         stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in a database in order to accomplish a specific function. The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedures are an important object in a database, and any well-designed database application should use stored procedures.         stored procedures are written by flow control and SQL statements, which are compiled and optimized to be stored in the database server, as long as the application is invoked.  2, Key concepts and FAQs         1 What is SPL in Informix?         SPL (Stored Procedure Language) is a SQL provided in Informix that provides process control (branching and looping). It includes the SPL process (no return value) and the SPL function (with a return value). SPL is parsed and optimized at the time of creation, and it is saved in the System catalog table in an executable manner.         2) Why is using SPL improving performance?         i) reduced application complexity because it runs in the Informix engine, reducing i/o;        II);      &N Bsp III) better use of repetitive operation with higher frequency.         3) What are the flexibility of SPL?         i) it can be called in other SQL syntax;        II) It can also be used in triggers;        III) to Adding Process Control;        IV in SQL is easy to maintain.        4) SPL parameters        i) can pass many parameters;       ii) SPL parameters with a maximum limit of 32K ；       III) can be any kind of SQL data type except for the following two types:             SERIAL/SERIAL8              text/byte (can be delivered using the REFERENCES keyword).        IV) can use a complex or user-defined data type;       v) to specify a default value for the parameter by default.        5) SPL function return value        i) You can use the returning or returns keyword to define the return type of the return value;    & nbsp  II) can be any data type except for the following two types of data:            serial/serial8            Text/byte (can be passed using the REFERENCES keyword).        III) You must have at least one return statement in the procedure;       IV) to take a name for the return value. For example:create function val_comp  (Val1 integer, val2 integer) RETURNING INTEGER  AS comp_res;      6) SPL overload       can define multiple SPL functions with the same name, such as the following SPL function, although the name is the same, But parameter types or different:create function val_comp  (Val1 integer, val2 integer) CREATE  Function val_comp  (Val1 char),  val2 char (+) create function val_comp  ( Val1 decimal, val2 decimal) ...      can use the specific keyword to specify the short, Unique name of the function that is overloaded with SPL. This name is unique in the database. For example:create function val_comp  (Val1 integer, val2 integer)  SPECIFIC int_ compcreate function val_comp  (Val1 char),  val2 char ()  SPECIFIC  string_compcreate function val_comp  (Val1 decimal, val2 decimal)  SPECIFIC  dec_comp     7) What is a statement block?      i) statement block is a set of SPL or SQL statements;     II) Implicit statement blocks contained in the Create Procedure/function and end procedure/ Funciton between;     III) can use begin and end to explicitly well-known block of statements embedded in another block of statements, for example: Create procedure myproc1 ()  --  the beginning of the implicit statement block define x int; let x = 15;insert into table1 values  (x,  ' amigo '); begin --  the beginning of an explicit statement block Define y int; let y = 16;insert into table1 values  ( y,  ' xingxing '); end --  the end of an explicit statement block end procedure; --  the end of an implicit statement block       8) How do I define a variable?       i) is a statement block that uses define to define variables;      II) variables in memory, not in the database save;      III) There are local variables and global variables two;      IV) variables can be any type of SQL data type and extension type except for the following two types:            & nbsp serial/serial8             text/byte (can be declared using the REFERENCES keyword).       Defining variables example:define x, y int; --  the data type built-in below define p person_type;  --  user-defined data type person_typedefine mymusic references byte; --  Using the REFERENCES keyword to define byte types       9) Local variables and global variable comparisons       Local variables:      i) local variable values are valid in SPL;       II) is reset at the end of the SPL function or process;      III) it cannot have a default value;      IV) its scope is in the statement block it defines, or any inline statement block in;    &NBSp V) Local variables can be redefined in another block of statements.       Take a look at an example of a local variable: create procedure local_scope () define x,y,z int; let x = 5; let y = 10; Let z = x + y; -- z equals 15begin  define x, q int;.  -- x is redefined   define z char (5);  -- z is redefined   let x =  100;  let q = x + y; -- q=110  let z =   ' Amigo '; --  sets a new value for z endlet y = x; -- y equals 5let x = z; The value of  -- z is 15, not amigo   end procedure;      global variable:      i) The value of the global variable can be obtained where the same user session is used in the same database;     II) must have a default value of;     III) that must be defined in each SPL function or procedure to use it;      IV) cannot be a collection variable.       For example, two SPL functions, named Func1 and FUNC2,FUNC1, are defined as follows: Create function func1 ()  returning  int;define global gvar int default 2; let gvar = gvar + 1; return gvar; end function;

FUNC2 reference statements are as follows:

CREATE FUNCTION Func2 () returning int;define GLOBAL Gvar INT DEFAULT 5; Let Gvar = Gvar + 1; RETURN Gvar; END FUNCTION;

If the order of execution is as follows:

EXECUTE FUNCTION func1 ();     EXECUTE FUNCTION Func2 ();     After the first sentence is executed, Gvar is set to the default value of 2, and 1 is performed, so the value of Gvar is 3 after the first sentence is executed.     Then execute the second sentence, the first time not set the default value of Gvar, so on the basis of 3 and then perform a plus 1 operation, after the completion of the Gvar value is 4. If the order of execution is as follows: Execute FUNCTION func2 ();     EXECUTE FUNCTION func1 ();     After the first sentence is executed, Gvar is set to the default value of 5, and 1 is performed, so the value of Gvar is 6 after the first sentence is executed.     Then execute the second sentence, the first time not set the default value of Gvar, so on the basis of 6 and then perform a plus 1 operation, after the completion of the Gvar value is 7. 10) Assigning a variable i) using an undefined variable will give an error; ii) a method that assigns a value to a defined variable: * Use the Let statement to assign the initial value directly to the variable, the reference statement is as follows: Make < variable > = < valid expression or letter Several >;

* Use the Selet into statement to assign a value to a variable in the query, and the reference statement is as follows:

SELECT ... into < variables > from ...;

* Use call ... The returning statement assigns the returned result to the variable, and the reference statement is as follows:

Call ... Returning < variables >;

* Use the Execute FUNCTION into statement to assign the returned result to the variable, as follows:

EXECUTE FUNCTION ... into < variables >; 3, Syntax 1) creating the SPL process create PROCEDURE name (parameter list) specific specific_name ... {Statement block} END PROCEDURE;

For example, in the following instance, a stored procedure named Set_status is created that can pass in the myID and mystatus two parameters, and in this stored procedure, the Item_inventory table is update operation. The ID field satisfies the myID record to update the Status field to Mystatus. The reference statements are as follows:

CREATE PROCEDURE set_status (myID INTEGER DEFAULT 0, Mystatus CHAR) UPDATE item_inventory Set status = Mystatus WHERE i D = myID; END PROCEDURE;

2) Create SPL function

CREATE FUNCTION Name (parameter list) returning list specific specific_name ... {Statement block} END FUNCTION;

For example, to create an SPL function named Val_comp, you can pass in the Val1 and val2 two parameters, in which the two variables are compared, and if they are equal, return 0, otherwise 1. The reference statements are as follows:

create function val_comp  (Val1 integer, val2 integer) RETURNING INTEGER;D efine res integer;if  (VAL1&NBSP;=&NBSP;VAL2)  THENLET res = 0; elselet res = 1; end if; return res; End function; 4, SPL, Process Control     &NBSP;1) Branch statements if...then...else...end if      Reference instances: if   ( condition )  THEN   statementsELIF  ( condition )  then &NBSP;&NBSP;&NBSP;STATEMENTS&NBSP;&NBSP;&NBSP, ..... else   statementsend if;    &NBSP;2) Branch statements case      Reference Instance:case  (  condition )    WHEN <value1> THEN       Statements   when <value2> then      statements       ...    ELSE       statementsend case;    &NBSP;3) Loop statement for      Reference syntax as follows:for  variable  IN  (  range or value  )       or use the following syntax:for  variable = range       Examples are as follows:for count = 2 to 30for count  = 2 to 30 step 2for count in  ( 2, 5, 7, 9) for  count IN  ( 2 to 8 step 2, 12 to 19, 22) for  name in  ("AMY",  "MAX", (select name from customer where customer_num  = 100)  )      4) Loop statement while      Reference syntax as shown below:while  (conditional expression)    An example of a conditional expression in a   executed statement end while;      while is as follows:while  (COUNT&NBSP;&LT;&NBSP;20) while  ( status matches  "A *") while  (exists  (select name from customer where  cus_num=100)) while  (status in  ("A",  "I",  "D"))       5) Loop statement foreach     &NBSP; i) is used to fetch multiple rows of data;      II) It opens a cursor;      III) cursor used to get the current row for the row update or delete operation.       Reference examples are as follows:foreach select salary into ind_salfrom customerwhere  location =  "UK" sum_sal += ind_sal; end foreach; retrun sum_sal;      &NBSP;6) Continue or exit statements         can all be used in the for, In the foreach and while loop statements.         Reference examples are as follows: for i = 1 to 5   let j =  i;   WHILE  (j > 0)       LET id  = foo (j);      if  (id = 5)  THEN          LET j = j – 1;          CONTINUE WHILE; --  does not perform subsequent operations and proceeds back to the while loop and then executes        end if;      let sum = sum + 5;      let j = j – 1;       IF  (sum > 500)  THEN          EXIT FOR; --  Exit For Loop       END IF;    END WHILE; end for; Return sum;5, exception handling       1) on exception statements       ON Exception statement statements provide a mechanism for catching and handling exceptions. Specify the error to catch in in and specify the action to take when the exception occurs. Multiple on exception statements are allowed in a block of statements. The       on exception statement must be after the Define statement and before any executable block of statements. And it is also valid in an inline statement block. It uses the SET statement to receive SQL, ISAM error codes, and error messages.       Reference examples are as follows: Create procedure ex_test ()    DEFINE sql_err  Integer;   define isam_err integer;   define err_txt char ( );   on exception in  ( -206)  set sql_err, isam_err, err_ txt      create table tab1  ( col1 int, col2 int);       INSERT INTO tab1 VALUES  (1, 2);       insert  INTO tab1 VALUES  (2, 3);    end exception   insert  INTO tab1 VALUES  (1, 2)  --if TAB1 does not exist, skip to exception handling    insert into  tab1 VALUES  (2, 3); end procedure;      &NBSP;2) with Resume statement         This statement restores the statement when a stored procedure error occurs.         The following example shows the use of the with resume, and continues the subsequent statement processing when the exception has been processed. The reference statements are as follows: Create procedure ex_test () on exception in  ( -206)        CREATE TABLE tab1  (Col1 int, col2 int);       INSERT INTO tab1 VALUES  (1,&NBSP;2); End exception with resume;insert into tab1&nbSp values  (1, 2); --  if TAB1 does not exist, jump to exception handling insert into tab1 values  (2,&NBSP;3);  --  after the completion of the exception processing, continue to deal with the sentence insert into tab1 values  (3, 4); end procedure;      &NBSP;3) RAISE exception statements       &NBSP;I)   This statement is used to create the error;  & nbsp   &NBSP;II) It can be used to specify SQL errors, ISAM errors, and error messages;      &NBSP;III) errors created with this statement that can be captured by on exception;      &NBSP;IV) can use the specified error code-746来 to represent a custom error message.         The following example uses the on exception statement to indicate that an error code, error message is inserted into a custom error table my_error_table When an error occurs, and that a custom 746 exception is thrown when the passed parameter is less than 1 o'clock. The rest of the data is successfully inserted into the TAB1 table, as the reference statement:create procedure ex_test5  (A int)    DEFINE  Sql_err integer;   define isam_err integer;   define err_txt  char (;   on exception in ) ( -746)  set sql_err, isam_err,  err_txt      insert into my_error_table values  (sql_err, isam_err, err_txt);   end exception;    IF  (a < 1)  then      raise exception  -746, 0,  "Insert value must be greater than 0";    end if;   insert into tab1  VALUES  (1, a); End procedure;6, perform SPL functions, or stored procedures         Use the Execute PROCEDURE statement to execute SPL stored procedures using execute function to perform SPL functions;        Reference Instance 1:execute procedure foo ();        Reference instance 2:CREATE&NBSP;FUNCTION&NBSP;FUNC1 ()  returning int    define myvalue  int;    call foo ()  returning myvalue;    insert  INTO table1 VALUES  (myvalue);    return myvalue; end function;        Reference instance 3:select * from table2 where id =  get_ID ("amigo");        Reference Instance 4:update table2 set col2=foo ()  where id=1;7, Delete SPL functions or stored procedures         1) Use drop procedure to delete SPL stored procedures;        2) using drop function to remove the SPL function;        3) Use drop routine to delete SPL stored procedures or SPL functions.         Several reference examples are as follows:drop procedure foo;drop procedure foo  (INTEGER);D ROP  specific procedure foo_int;drop function foo_ret;drop function foo_ret   (INTEGER);D Rop specific function foo_ret_int;drop routine foo;drop routine  foo_ret;

Informix Stored Procedure structure