In addition to using data manipulation statements directly, as just described, it is also possible to manipulate table data by calling a procedure. Procedures perform the specific data manipulations laid out in the procedure definition.
Any SQL statement in the grouping procedural-sql-statement, see the Mimer SQL Reference Manual, Chapter 12, Procedural SQL Statements, can be used in a procedure, and this includes all the data manipulation statements.
The use of procedures allows data manipulation within the database to be controlled both in terms of strictly defining which data manipulation operations are performed and also in terms of regulating which database objects can be affected.
A procedure is invoked by using the CALL statement. In the case of a result set procedure, used in an ESQL context, the CALL statement is not used directly but is specified in a cursor declaration. An ident requires EXECUTE privilege on a procedure in order to call it.
In the CALL statement, the value-expressions or assignment targets specified for each of the procedure parameters must be of a data type that is assignment-compatible, see the Mimer SQL Reference Manual, Chapter 6, Assignments, with the parameter data type.
See the Mimer SQL Reference Manual, Chapter 12, CALL, for full details of the CALL statement and the Mimer SQL Programmer's Manual, chapter 12, Mimer SQL Stored Procedures, for a general discussion of the stored procedure functionality supported in Mimer SQL.
Examples of Calling Procedures
Invoke the procedure called SEARCH in the MIMER_STORE_MUSIC schema:CALL mimer_store_music.search(:title, :artist, 0);
Declare a cursor that will be used when result-set data is fetched from the result set procedure called BARCODE:DECLARE c_2 CURSOR FOR CALL mimer_store.barcode(itm.ean_code);
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40