SQL · Theory

Procedure

Theory

315/439

Procedure

A stored procedure is a named batch of SQL on the server. CREATE PROCEDURE fail_list() BEGIN SELECT name, marks FROM students WHERE marks < 40; END; CALL fail_list();

Use for a repeated admin task. Don’t put all app logic in procedures — versioning is harder than Git for Java/PHP. DELIMITER $$ is needed in the CLI because ; is inside the body.

Trap: procedure vs function: FUNCTION returns a value and can be in a SELECT. PROCEDURE is CALL’d and can return result sets.

On the example next to this theory: Procedure: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

CREATE PROCEDURE … BEGIN … END; CALL name();

Procedure — sample query

-- Procedure
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Procedure: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefProcedure = stored SQL you CALL.
RuleDELIMITER when defining. FUNCTION returns a value; PROC is CALL.
TrapAll business logic only in procedures.

SQL · Theory

Procedure

← All stacks

Theory

315/439

Procedure

A stored procedure is a named batch of SQL on the server. CREATE PROCEDURE fail_list() BEGIN SELECT name, marks FROM students WHERE marks < 40; END; CALL fail_list();

Use for a repeated admin task. Don’t put all app logic in procedures — versioning is harder than Git for Java/PHP. DELIMITER $$ is needed in the CLI because ; is inside the body.

Trap: procedure vs function: FUNCTION returns a value and can be in a SELECT. PROCEDURE is CALL’d and can return result sets.

On the example next to this theory: Procedure: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

CREATE PROCEDURE … BEGIN … END; CALL name();

Procedure — sample query

-- Procedure
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Procedure: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefProcedure = stored SQL you CALL.
RuleDELIMITER when defining. FUNCTION returns a value; PROC is CALL.
TrapAll business logic only in procedures.

Procedure

Procedure — sample query

Short notes

Questions

Procedure

Procedure — sample query

Short notes

Questions