SQL commands supporting data extraction

Linklist

1. CREATE VIEW - create special views on database tables
2. DROP VIEW - delete a created view
3. CREATE PROCEDURE - create a stored procedure
4. DROP PROCEDURE - delete a stored procedure
5. EXECUTE PROCEDURE - execute a stored procedure

CREATE VIEW - create special views on database tables

Syntax

CREATE VIEW Viewname
[ ( fieldname [,...] ) ]
SELECT-Command ;

Description:

CREATE VIEW creates an speciel view on one or more sql-tables in the database in form of a new virtual (only in the memory created) table. In CREATE VIEW you can use accordingly to the database software only a part of the SELECT-Commands clauses.

Example:
CREATE VIEW myorders (
my_onum,
my_anz )
SELECT
onum,
anz
FROM torder
WHERE preis > 0;

Creates a view on the table 'torders', which contains inly the fields 'anum' und 'anz' from records with 'preis' > 0.

DROP VIEW - delete a view from the database

Syntax

DROP VIEW Viewname;

Description

DROP VIEW deletes an in the database stored view.

Example:
DROP VIEW myorders;

Here is the view 'myorders' deleted from the database.

CREATE PROCEDURE - create a stored 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 - delete a stored procedure

Syntax

DROP PROCEDURE Prozedurname;

Description:

DROP PROCEDURE deletes a stored procedure form the database.

Example :
DROP PROCEDURE table_show;

Hier wird die gespeicherte Prozedur 'table_show' gelöscht.

EXECUTE PROCEDURE - start a stored procedure

Syntax

EXECUTE PROCEDURE Prozedurname;

Description:

EXECUTE PROCEDURE starts the execution of an stored procedure.

Example:
EXECUTE PROCEDURE table_show;

Hier wird die gespeicherte Prozedur 'table_show' gestartet.