Stored Procedure

A stored procedure executes application logic directly inside the RDBMS server.

Reasons for stored procedures:

• Reduces volume of data transfer between client and server
• Centralizes application code at the server
• Conceptual schema enhancement

Atomic-Valued Function

Function returns a single scalar value.

CREATE FUNCTION sumSalaries(dept CHAR(3))
	RETURNS DECIMAL(9,2)
LANGUAGE SQL
RETURN
	SELECT sum(salary)
	FROM employee
	WHERE workdept = dept

Then, you can run this as a query

SELECT deptno, sumSalaries(deptno) AS sal \
FROM department

Table-Valued Function

Function returns a table.

CREATE FUNCTION deptSalariesF(dept CHAR(3))
	RETURNS TABLE(salary DECIMAL(9,2))
LANGUAGE SQL
RETURN
	SELECT salary
	FROM employee
	WHERE workdept = dept

SELECT * FROM TABLE \
(deptSalariesF(CAST(’A00’ AS CHAR(3)))) AS s

Stored Procedure with Branching

CREATE PROCEDURE UPDATE_SALARY_IF
	(IN employee_number CHAR(6), INOUT rating SMALLINT)
LANGUAGE SQL
BEGIN
	DECLARE not_found CONDITION FOR SQLSTATE ’02000’;
	DECLARE EXIT HANDLER FOR not_found
		SET rating = -1;
	IF rating = 1 THEN
		UPDATE employee
		SET salary = salary * 1.10, bonus = 1000
		WHERE empno = employee_number;
	ELSEIF rating = 2 THEN
		UPDATE employee
		SET salary = salary * 1.05, bonus = 500
		WHERE empno = employee_number;
	ELSE
		UPDATE employee
		SET salary = salary * 1.03, bonus = 0
		WHERE empno = employee_number;
	END IF;
END