Procedures, Functions and Packages

Stored Procedures

CREATE OR REPLACE PROCEDURE GetCustomer(id_in NUMBER) AS
  v_name VARCHAR2(100);
BEGIN
  SELECT name INTO v_name FROM customers WHERE id = id_in;
  DBMS_OUTPUT.PUT_LINE(v_name);
END GetCustomer;
/

EXEC GetCustomer(1);

Stored Functions

CREATE OR REPLACE FUNCTION discounted(total_in NUMBER)
RETURN NUMBER AS
BEGIN
  RETURN total_in * 0.9;
END discounted;
/

SELECT discounted(100) FROM dual;

Packages Group Related Objects

CREATE OR REPLACE PACKAGE customer_api AS
  PROCEDURE AddCustomer(p_name IN VARCHAR2, p_email IN VARCHAR2);
  FUNCTION  TotalSpend(p_id NUMBER) RETURN NUMBER;
END customer_api;
/

CREATE OR REPLACE PACKAGE BODY customer_api AS
  PROCEDURE AddCustomer(p_name IN VARCHAR2, p_email IN VARCHAR2) IS
  BEGIN
    INSERT INTO customers (name, email) VALUES (p_name, p_email);
    COMMIT;
  END AddCustomer;

  FUNCTION TotalSpend(p_id NUMBER) RETURN NUMBER IS
    v_sum NUMBER := 0;
  BEGIN
    SELECT NVL(SUM(total), 0) INTO v_sum FROM orders WHERE customer_id = p_id;
    RETURN v_sum;
  END TotalSpend;
END customer_api;
/

Key Points

• PROCEDURES do work; FUNCTIONS return a value.
• CREATE OR REPLACE rewrites without losing grants.
• Packages wrap related procedures and functions.
• EXEC calls a procedure; functions appear in SELECT.