CREATE PROCEDURE name(...) LANGUAGE plpgsql AS $$ ... $$;
MySQL
CREATE PROCEDURE name(...) BEGIN ... END;
SQL Server
CREATE PROCEDURE name AS BEGIN ... END;
Oracle
CREATE PROCEDURE name IS BEGIN ... END;
Difference vs Function
Procedures can issue COMMIT/ROLLBACK; functions usually cannot
2. Defining Procedure Parameters
Mode
Direction
IN
Input (default)
OUT
Output only
INOUT
Both in and out
DEFAULT value
Optional argument
VARIADIC
Variable-length args (PG)
3. Using Variables in Procedures
Example: PL/pgSQL Variables
CREATE PROCEDURE transfer(p_from BIGINT, p_to BIGINT, p_amt NUMERIC)LANGUAGE plpgsql AS $DECLARE v_balance NUMERIC;BEGIN SELECT balance INTO v_balance FROM accounts WHERE id = p_from FOR UPDATE; IF v_balance < p_amt THEN RAISE EXCEPTION 'insufficient funds'; END IF; UPDATE accounts SET balance = balance - p_amt WHERE id = p_from; UPDATE accounts SET balance = balance + p_amt WHERE id = p_to;END;$;
Declaration
Example
Scalar
v_count INTEGER := 0;
Row type
v_row orders%ROWTYPE;
Cursor
DECLARE cur CURSOR FOR SELECT ...;
4. Implementing Control Flow
Construct
Syntax
IF
IF cond THEN ... ELSIF ... ELSE ... END IF;
CASE
CASE x WHEN ... THEN ... END CASE;
LOOP / EXIT
Infinite loop with explicit exit
WHILE / FOR
Conditional and counted loops
CONTINUE
Skip to next iteration
5. Handling Errors in Procedures
Mechanism
Detail
RAISE EXCEPTION
Throw error with message + SQLSTATE
EXCEPTION block (PG)
BEGIN ... EXCEPTION WHEN ... THEN ... END;
DECLARE HANDLER (MySQL)
DECLARE EXIT HANDLER FOR SQLSTATE '...' ...
SQLCODE / SQLERRM
Inspect current error
6. Executing Stored Procedures
DB
Call
Postgres
CALL transfer(1, 2, 100);
MySQL
CALL transfer(1, 2, 100);
SQL Server
EXEC transfer @from=1, @to=2, @amt=100;
Oracle
BEGIN transfer(1,2,100); END; /
7. Returning Result Sets
DB
Mechanism
Postgres
REFCURSOR OUT param, or use functions returning SETOF
MySQL
SELECT inside procedure returns to client
SQL Server
SELECT inside proc; OUTPUT params for scalars
Oracle
SYS_REFCURSOR OUT param
8. Using Dynamic SQL in Procedures
DB
Syntax
PL/pgSQL
EXECUTE 'SELECT ...' INTO var USING param;
MySQL
PREPARE stmt FROM '...'; EXECUTE stmt USING @p; DEALLOCATE PREPARE stmt;
SQL Server
sp_executesql N'...', N'@p INT', @p = 1;
Oracle
EXECUTE IMMEDIATE 'SELECT ...' INTO var USING param;
Warning: Always use parameter binding in dynamic SQL — string concatenation enables SQL injection.