CREATE PROCEDURE

Syntax

CREATE [ DBA ] PROCEDURE procedure name
( [ { Parametername { Fieldtype | REFERENCES { BYTE | TEXT } }
[ DEFAULT { value | NULL } ] } [,...] ] )
[ RETURNING { Fieldtype | REFERENCES { BYTE | TEXT } } ; ]
[ Statement ; ] [...]
END PROCEDURE ;






Description

With CREATE PROCEDURE you can create and store in the database an procedure (programm/function), wich you want to you use later in SQL-statements or other programms and procedures.

Example :

CREATE PROCEDURE table_drop ( )
DROP TABLE proc_test;
END PROCEDURE;

Creates a new stored procedure 'table_drop' , which deletes the table 'proc_test' from the current database, when called.

DBA

DBA tells the database to create a dba privileged procedure.

Example :

CREATE DBA PROCEDURE table_create ( )
  CREATE TABLE proc_test (
    fid	 smallint,
    ag	 smallint,
    vsnr integer
  );
END PROCEDURE;

Creates a new dba privileged stored procedure 'table_create' , which creates a new table 'proc_test' with three coplumns 'fid', 'ag' und 'vsnr' in the aktive database when called.

REFERENCES { BYTE | TEXT }

REFERENCES ...

Example :

under construction...
   

Creates a new stored procedure '...' in the database.

DEFAULT { Wert | NULL }

DEFAULT specifyes a default value, that is used when the procedure is called without a parameter.

Example :

CREATE PROCEDURE square ( par INTEGER DEFAULT 0 )
RETURNING INTEGER;
  DEFINE rez INTEGER;
  LET rez = par * par;
  RETURN rez;
END PROCEDURE;

Creates a new in the database stored procedure 'square'. Here is the parameter 'par' optional optional and zero by default. When the procedure is called without paramenter, then it returns 0*0 , otherwise it returns par*par .

RETURNING { Feldtyp | REFERENCES { BYTE | TEXT } }

RETURNING tells the DBMS , how many values and of which type an procedure will return.

Example :

CREATE PROCEDURE table_insert ( )
RETURNING INT;
  DEFINE i INT;
  INSERT INTO aam_proc_test ( fid, ag, vsnr )
  VALUES ( 1, 2, 3 );
  INSERT INTO aam_proc_test ( fid, ag, vsnr )
  VALUES ( 0, 9, 9 );
  LET i = SELECT COUNT(*) FROM proc_test;
  RETURN i;
END PROCEDURE;

Creates a new stored procedure 'table_insert', which inserts two new rows into the table 'proc_test' when called.

Statement

An SPL-statement or command, such as DEFINE, RETURN, IF, FOR, FOREACH, WHILE, and so on.

Example :

CREATE PROCEDURE table_run ( )
DEFINE i INT;
  SET ISOLATION TO dirty read;
  SET LOCK MODE to wait 60;
  SET PDQPRIORITY 5;
  CALL table_drop();
  CALL table_create();
  LET i = table_insert();
END PROCEDURE;

Creates a new stored procedure 'table_run', which calls some other stored procedures and proceeds a return value.

Integrated example

Example :

CREATE PROCEDURE table_show ( )
RETURNING INT, INT, INT;
  DEFINE my_fid, my_ag, my_vsnr INTEGER;
  CALL table_run();
  FOREACH
    SELECT square(fid+1) fid, ag, vsnr
    INTO my_fid, my_ag, my_vsnr
    FROM proc_test
    RETURN my_fid, my_ag, my_vsnr
    WITH RESUME;
  END FOREACH;

END PROCEDURE;

Creates a new stored procedure 'table_show' , which returns all records of the table 'proc_test' created before in the subprocedure 'table_run'.

DROP PROCEDURE

Syntax

DROP PROCEDURE Prozedurname;

Description:

DROP PROCEDURE deletes a stored procedure form the database.

Example :

DROP PROCEDURE table_show;

Deletes the stored procedure 'table_show'.

EXECUTE PROCEDURE

Syntax

EXECUTE PROCEDURE Prozedurname;

Description:

EXECUTE PROCEDURE starts the execution of a stored procedure.

Example:

EXECUTE PROCEDURE table_show;

Starts the stored procedure 'table_show'.