Stored Procedures and Functions in PL/SQL

Stored Procedures

A procedure is a named PL/SQL block that performs one or more specific tasks. Unlike functions, procedures do not return a value directly, though they can use OUT or IN OUT parameters to return values.

Key Characteristics

• Procedures are stored in the database for reusability and modularity.

• They can accept zero or more parameters.

• Commonly used to perform an action, such as inserting data, validating input, or logging changes.

---

Structure of a Procedure

A procedure consists of two parts:

1. Header : Contains the procedure name and parameter definitions.

2. Body : Contains declarations, executable statements, and optional exception handling.

Syntax: Creating a Procedure

A procedure is created with the CREATE OR REPLACE PROCEDURE statement.

CREATE [OR REPLACE] PROCEDURE procedure_name 
  [(parameter_name [IN | OUT | IN OUT] datatype [, ...])]
{IS | AS} 
BEGIN
  -- Procedure logic
END procedure_name;

Parameter Modes

• IN: Default. Accepts a value from the caller.

• OUT: Returns a value to the caller.

• IN OUT: Accepts an initial value and returns an updated value.

• The AS keyword is used instead of the IS keyword for creating a standalone procedure.

---

Example: Simple Procedure

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
  DBMS_OUTPUT.PUT_LINE('Hello World!'); 
END;
/

Calling a Procedure

From SQL*Plus or PL/SQL:

EXEC greetings;
-- or
BEGIN
  greetings;
END;
/

CALL procedure_name(parameters);
-- or
EXEC procedure_name(parameters);

---

Example: Procedure with Parameters

CREATE OR REPLACE PROCEDURE welcome_user(name IN VARCHAR2) 
AS 
BEGIN 
  DBMS_OUTPUT.PUT_LINE('Welcome, ' || name); 
END;
/

EXEC welcome_user('John');

CREATE PROCEDURE GetStudentsByMajor(IN majorName VARCHAR(100))
BEGIN
  SELECT StudentID, Name, Email FROM Student WHERE Major = majorName;
END;

CALL GetStudentsByMajor('Computer Science');

---

Deleting a Procedure

DROP PROCEDURE procedure_name;

BEGIN 
  DROP PROCEDURE greetings; 
END; 
/

Functions in PL/SQL

A function is a PL/SQL block similar to a procedure, but it returns a single value using the RETURN clause. Functions are typically used when a computation is needed and the result will be used in expressions or queries.

Key Characteristics

• Must return a single value.

• Can be called from SQL statements (if deterministic and free of side effects).

• Can accept parameters just like procedures.

---

Syntax: Creating a Function

CREATE FUNCTION function_name (
  param_name datatype
) 
RETURNS return_datatype
[DETERMINISTIC]
BEGIN
  -- code
  RETURN value;
END;

CREATE [OR REPLACE] FUNCTION function_name 
  [(parameter_name [IN | OUT | IN OUT] datatype [, ...])] 
  RETURN return_datatype 
{AS | IS}
BEGIN
  -- Function logic
  RETURN value;
END function_name;

• The RETURN clause defines the data type of the value to be returned.

• The function must contain at least one RETURN statement.

• The AS keyword is used instead of the IS keyword for creating a standalone function.

---

Calling a Function

SELECT function_name(value) FROM DUAL;

Example

CREATE FUNCTION GetStudentEmail(stuID INT)
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
  DECLARE email VARCHAR(100);
  SELECT Email INTO email FROM Student WHERE StudentID = stuID;
  RETURN email;
END;

SELECT GetStudentEmail(1001);

Example: Function to Count Records

CREATE OR REPLACE FUNCTION total_customers 
RETURN NUMBER 
AS 
  total NUMBER := 0; 
BEGIN 
  SELECT COUNT(*) INTO total FROM customers; 
  RETURN total; 
END;
/

Using the Function

In PL/SQL:

DECLARE 
  c NUMBER; 
BEGIN 
  c := total_customers(); 
  DBMS_OUTPUT.PUT_LINE('Total customers: ' || c); 
END;
/

In SQL:

SELECT total_customers() FROM DUAL;

---

Example: Function with Logic

DECLARE 
  a NUMBER := 10; 
  b NUMBER := 20; 
  c NUMBER;

  FUNCTION find_max(x IN NUMBER, y IN NUMBER) RETURN NUMBER IS 
  BEGIN 
    IF x > y THEN
      RETURN x; 
    ELSE 
      RETURN y; 
    END IF; 
  END;
BEGIN 
  c := find_max(a, b); 
  DBMS_OUTPUT.PUT_LINE('Max: ' || c); 
END;
/

Differences Between Procedures and Functions

Feature	Procedure	Function
Return Value	None directly (can use OUT)	Must return a value
Called From SQL	No	Yes, if deterministic
Used For	Performing actions	Returning computed values
RETURN Statement	Optional	Mandatory
Usage in Expressions	Not allowed	Allowed

Deterministic vs Non-Deterministic Functions

• A deterministic function returns the same result for the same inputs (e.g., x * x).

• A non-deterministic function may return different results for the same input (e.g., using SYSDATE or RANDOM).

Examples

CREATE FUNCTION square(x NUMBER)
RETURN NUMBER
DETERMINISTIC
AS
BEGIN
  RETURN x * x;
END;
/

CREATE FUNCTION today_plus(x NUMBER)
RETURN DATE
NOT DETERMINISTIC
AS
BEGIN
  RETURN SYSDATE + x;
END;
/

Viewing and Managing Stored Programs

View Stored Procedures or Functions

SELECT object_name, object_type 
FROM user_objects 
WHERE object_type IN ('PROCEDURE', 'FUNCTION');

-- To see the source
SELECT text 
FROM user_source 
WHERE name = 'PROCEDURE_NAME' 
AND type = 'PROCEDURE';

Drop a Function

DROP FUNCTION function_name;

SQL Assertions (Advanced Constraints)

Assertions are used to define complex constraints across multiple tables, beyond what CHECK constraints can do.

Example: Salary Constraint

Ensure that no employee earns more than their manager:

CREATE ASSERTION salary_constraint 
CHECK (
  NOT EXISTS (
    SELECT * 
    FROM employee E, employee M, department D 
    WHERE E.salary > M.salary 
      AND E.dno = D.dnumber 
      AND D.mgr_ssn = M.ssn
  )
);

Example: Only One President Allowed

CREATE ASSERTION at_most_one_president 
CHECK (
  (SELECT COUNT(*) 
   FROM employee 
   WHERE job = 'PRESIDENT') <= 1
);

Note: SQL assertions are part of the SQL standard but may not be supported in all RDBMS like Oracle or MySQL.