Working with Stored Procedures
1. Creating Stored Procedures
| DB | Syntax |
|---|---|
| Postgres 11+ | 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.
9. Managing Procedure Security
| Concept | Detail |
|---|---|
| SECURITY DEFINER | Runs with creator's privileges |
| SECURITY INVOKER (default) | Runs with caller's privileges |
| GRANT EXECUTE | Allow role to call |
| search_path (PG) | SET inside DEFINER procs to prevent hijacking |
10. Optimizing Procedure Performance
| Tip | Detail |
|---|---|
| Avoid row-by-row loops | Use set-based SQL |
| Prepared plans | Reuses parsed plan |
| Bulk binding | Oracle FORALL, PG arrays |
| Profile | Use EXPLAIN ANALYZE on internal queries |
| Watch parameter sniffing | SQL Server: use OPTION (RECOMPILE) or local vars |