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() BEGIN CREATE TABLE Tab1 ( Number INT ); ALTER TABLE Tab1 ADD COLUMN Letter CHAR; END;
We can not use DDL statements within a procedure.
CREATE OR REPLACE PROCEDURE procDDL() BEGIN CREATE TABLE Tab1 ( Number INT ); END;
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 CREATE TABLEvoc.Tab1 ( Number INT ); END;
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 ); SELECT * FROM procTab;
DML Statements in Procedures
CREATE OR REPLACE PROCEDURE procDML() BEGIN INSERT INTO procTab ( Number ) VALUES ( 1 ); END; CALL procDML();--we call the procedure
SELECT * FROM procTab;
DML statements will work normally inside of procedures.
CREATE OR REPLACE PROCEDURE procDML() BEGIN SELECT * FROM Tab1; END;
SELECT statement is not allowed in procedures. Procedures don't return values.
Variables and Arguments in Procedures
CREATE OR REPLACE PROCEDURE procVar() BEGIN DECLARE Var1 INTEGER; SET Var1 = 2; UPDATE procTab SET Number = Var1 WHERE Number = 1; END;
We can use variables in procedures. CALL procVar(); SELECT * FROM procTab;
CREATE PROCEDURE procArg(Arg1 INT) BEGIN UPDATE procTab SET Number = Arg1 WHERE Number = 2; END;
Procedures can accept arguments.
CALL procArg(3); SELECT * FROM procTab;
CREATE PROCEDURE procArg(Arg1 INT, Arg2 INT) BEGIN UPDATE procTab SET Number = Arg1 + Arg2 WHERE Number = 3; END;
We can overload procedures, just like functions. CALL procArg( 2, 2 ); SELECT * FROM procTab;
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() BEGIN START TRANSACTION; UPDATE procTab SET Number = 5 WHERE Number = 4; COMMIT; END;
Because we can not use DDL statemens inside of procedures, then it is not possible to use transaction statements.
CREATE OR REPLACE PROCEDURE procTrans() BEGIN ATOMIC UPDATE procTab SET Number = 5 WHERE Number = 4; END;
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) BEGIN ATOMIC DECLARE Res INT; CASE WHEN Arg1 = 'A' THEN SET Res = 6; ELSE SET Res = 7; END CASE; UPDATE procTab SET Number = Res; END;
CALL procCASE( 'A' ); SELECT * FROM procTab;
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() RETURNS INTEGER BEGIN RETURN 1; END;
This is a function calling a function. CREATE OR REPLACE FUNCTION funcReturnSum() RETURNS INTEGER BEGIN RETURN funcReturnOne() + 2; END;
This will work, we can chain functions.
SELECT funcReturnSum();
CREATE OR REPLACE FUNCTION funcProc() RETURNS INTEGER BEGIN CALL procCASE( 'B' ); RETURN 1; END;
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() BEGIN UPDATE procTab SET Number = 8 WHERE Number = 7; END;
This outer procedure will just call inner procedure: CREATE PROCEDURE procOuter() BEGIN CALL procInner(); END; CALL 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;
DROP PROCEDURE procCASE CASCADE;
We can use CASCADE keyword to delete procedure and all of the depending objects. Now our procedure is deleted.
