0440 Procedures in MonetDB
Procedures
A SQL Stored Procedure is a named set of SQL statements that are frequently executed together. When we want to execute those statements, we only need to specify the procedure name and the argument values.
Procedures are very similar to functions, but they differ in purpose and usage.
1) The purpose of functions is to perform calculations and return values. The purpose of procedures is to change database objects. This means that DML statements should be used within procedures, not within functions.
2) Functions are used in statements. They can be used in SELECT, WHERE, GROUP BY, and HAVING. Procedures are self-contained and independent from other statements. They are executed with the "CALL" statement.
DDL Statements in Procedures
CREATE OR REPLACE PROCEDURE procDDL() | We can not use DDL statements within a procedure.![]() |
CREATE OR REPLACE PROCEDURE procDDL() BEGIN | It is possible to use "CREATE TABLE" statement inside of procedure. But when we try to read from our new table (after we called a procedure), we get nothing: SELECT * FROM Tab1; | ![]() |
I will now try to create a table inside of procedure, but this time I will specify schema. We will get an interesting message.
CREATE OR REPLACE PROCEDURE procDDL() BEGIN | ![]() | It seems that CREATE TABLE is the same as DECLARE TABLE. We were not using DDL statement, we were just definining a table variable. |
Now we know that DDL statements can not be used in Functions and Procedures at all.
Sample Table
CREATE TABLE procTab ( Number INT ); | ![]() |
DML Statements in Procedures
CREATE OR REPLACE PROCEDURE procDML() --we call the procedure | ![]() | SELECT * FROM procTab; DML statements will work normally inside of procedures. |
CREATE OR REPLACE PROCEDURE procDML() | SELECT statement is not allowed in procedures. Procedures don't return values.![]() |
Variables and Arguments in Procedures
CREATE OR REPLACE PROCEDURE procVar() END; | We can use variables in procedures. CALL procVar(); SELECT * FROM procTab; | ![]() |
CREATE PROCEDURE procArg(Arg1 INT) | Procedures can accept arguments.CALL procArg(3); SELECT * FROM procTab; | ![]() |
CREATE PROCEDURE procArg(Arg1 INT, Arg2 INT) | We can overload procedures, just like functions.CALL procArg( 2, 2 ); | ![]() |
When deleting overloaded procedure we have to use the full signature. | DROP PROCEDURE procArg( INT ); DROP PROCEDURE procArg( INT, INT ); |
DROP ALL PROCEDURE procARG; | If we use ALL keyword, then we can delete all of the overloaded functions that share the same name, at once. |
Transactions in Procedures
CREATE OR REPLACE PROCEDURE procTrans() | Because we can not use DDL statemens inside of procedures, then it is not possible to use transaction statements.![]() |
CREATE OR REPLACE PROCEDURE procTrans() | Just like with functions, we have to use ATOMIC keyword after BEGIN to make procedure one big transaction. |
Flow Controls in Procedures
Inside procedures we can use flow control statements like CASE, WHILE and IF. | CREATE OR REPLACE PROCEDURE procCASE(Arg1 CHAR) | CALL procCASE( 'A' ); ![]() |
System Tables
We can read about procedures from sys.functions system table. Procedures are of the type "2". User created procedures have "false" in the system column.SELECT * FROM sys.functions WHERE name = 'proctrans'; |
![]() |
Data about procedures argument can be found in the system table sys.args.SELECT * FROM sys.args WHERE func_id = 15148; | ![]() |
Nesting Functions and Procedures
We will try to call this function from one other function: CREATE OR REPLACE FUNCTION funcReturnOne() | This is a function calling a function.CREATE OR REPLACE FUNCTION funcReturnSum() |
This will work, we can chain functions.SELECT funcReturnSum(); | ![]() |
CREATE OR REPLACE FUNCTION funcProc() | This time, we will call procedure from a function. This function will work as expected.SELECT funcProc(); ![]() |
![]() | Procedure "procCASE" will also do its job. It will change the value in "procTab" table.SELECT * FROM procTab; |
It is also possible to nest procedures. This will be inner procedure: CREATE PROCEDURE procInner() | This outer procedure will just call inner procedure: CREATE PROCEDURE procOuter() |
Outer procedure will call inner procedure, and inner procedure will update the table.SELECT * FROM procTab; | ![]() |
Deleting a Procedure
DROP PROCEDURE IF EXISTS procDDL(); | If we use "IF EXISTS", then deleting of a procedure will never return an error. |
If we try to delete not existing procedure, there will be no error, if we use "IF EXISTS".DROP PROCEDURE IF EXISTS procZZZ(); | ![]() |
Function funcProc() is depentand on the procedure "procCASE". That means that we can not delete "procCASE" procedure. | ![]() |
DROP PROCEDURE procCASE CASCADE; | We can use CASCADE keyword to delete procedure and all of the depending objects. Now our procedure is deleted. ![]() |