Stored procedures and functions

CREATE PROCEDURE and CREATE FUNCTION, the IN, OUT and INOUT parameter modes, RETURN, calling with CALL or inside a SELECT, and what processing placed in the database gains and costs.

28 cards
6 topics
Free

Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

Concepts
Questions
Sort and link
On the diagram
Scenarios
Understand

A few cards from the deck

Stored procedure: what is it?
Show the answer
A named routine, saved in the database and started with CALL; it does not return a value inside an expression and may commit or roll back the transaction (COMMIT, ROLLBACK).
It suits actions: archiving, purging, recalculating. You cannot write it in a SELECT or a WHERE.
What does this block show?

```sql
CREATE PROCEDURE doubler(INOUT x integer)
LANGUAGE plpgsql AS $$
BEGIN
x := x * 2;
END $$;

DO $$
DECLARE
v integer := 4;
BEGIN
CALL doubler(v);
CALL doubler(v);
RAISE NOTICE 'v = %', v;
END $$;
`
- v = 16
- v = 8
- v = 4
- v = <NULL>
Show the answer
v = 16
INOUT hands the changed value back to the caller's variable: 4 becomes 8 on the first call, then 16 on the second. 8 would assume the second call does nothing, 4 that nothing comes back.
A function declares the type of its value with … and returns it with …, as in RETURNS numeric then RETURN prix * 1.2;.
Show the answer
A function declares the type of its value with RETURNS and returns it with RETURN, as in RETURNS numeric then RETURN prix * 1.2;.
RETURNS (with an S) belongs to the header and gives the type; RETURN (no S) is a statement in the body that ends the function with a value.
Stored function: what is it?
Show the answer
A named routine, saved in the database, that returns a value and can therefore be used in an SQL expression: in a SELECT, a WHERE or an ORDER BY.
It suits calculations: SELECT nom, prix_ttc(prix) FROM produit applies it to every row. It cannot commit the current transaction.

Study this deck