Functions and Packages(1)

Chapter 8: An Introduction to PL/SQL (Functions and Packages)

Overview

• This chapter covers the basics of PL/SQL functions and packages.

  • Named blocks: Definitions and structures.

  • Setting up parameter lists.

Objectives

• Understand how to create functions and packages using PL/SQL by the end of the lecture.

PL/SQL Functions

Definition

• Function: A module that returns a value.

  • Call to a function must be part of an executable statement (e.g., in expressions).

  • Examples: SUM(), AVG().

Creating Functions

• Syntax:

  CREATE [OR REPLACE] FUNCTION function_name [(parameter_list)] RETURN return_type AS|IS 
  [local_declarations] 
  BEGIN 
  program_statements 
  [EXCEPTION exception_handlers] 
  END;

• Example Function:

  CONCAT(FName, ' ', LName) 

  • Says: Peter Parker.

Privileges

• Create Function Privilege: Must be granted by the DBA.

• OR REPLACE option is used if a function is already defined.

• Preserves granted privileges.

Parameter List

• Format for the parameter list: parameter_name data_type.

  • All parameters are input by default.

• No size restrictions on parameters (e.g., VARCHAR2 allowed, not VARCHAR2(25)).

• Default values can be assigned to parameters, e.g., MyVar VARCHAR2 DEFAULT 'TEMP'.

• Return data-type can be Oracle or user-defined type.

Modes of Parameters

1. IN (Default)

2. OUT

3. IN OUT

• No data size should be defined for parameters.

Function Example

• Creating a Greeting Function:

  CREATE OR REPLACE FUNCTION GREET (NAME VARCHAR) RETURN VARCHAR2 AS  
  BEGIN    
  RETURN 'Hello, ' || NAME || ' !';
  END;  

Calling a Function

• To run the function:

  BEGIN 
  DBMS_OUTPUT.PUT_LINE(GREET('Zayed University'));  
  END;

• To call in SQL script:

  SELECT GREET('Sara') FROM DUAL;  

• Example of incorrect function call and correcting it:

  • Wrong: GREET('Peter Parker');

  • Correct: DBMS_OUTPUT.PUT_LINE(GREET('Peter Parker'));

Storing Function Return Values

• Store return values in local variable:

  DECLARE 
  myresult VARCHAR(20);  
  BEGIN 
  SELECT GREET('Sara') INTO myresult FROM DUAL; 
  DBMS_OUTPUT.PUT_LINE(myresult); 
  END;

Concatenation Function Example

• Function to concatenate strings:

  CREATE OR REPLACE FUNCTION MIX (str1 VARCHAR2, str2 VARCHAR2, str3 VARCHAR2 DEFAULT '') RETURN VARCHAR2 AS 
  BEGIN 
  RETURN str1 || ' ' || str2 || ' ' || str3; 
  END;  

  • Sample calls:

    • SELECT MIX('Peter', 'Parker') FROM DUAL;

    • SELECT MIX('Zayed', 'University', 'Abu Dhabi Campus') FROM DUAL;

Compiling and Error Checking

Compilation Success

• Success message: "Function created".

Error Checking

• Use SHOW ERROR to view all errors, reporting line and column numbers of issues.

Use Cases and Examples

Example: Overdue Charges Function

• Create a table for book checkout:

  CREATE TABLE BOOKSHELF_CHECKOUT (
  Name VARCHAR2(25),  
  Title VARCHAR2(100),  
  CheckoutDate DATE,  
  ReturnedDate DATE);

• Insert sample data:

  INSERT INTO bookshelf_checkout VALUES('Gerhardt Kentgen','Wonderful Life','02-JAN-02', '02-FEB-02');
  INSERT INTO bookshelf_checkout VALUES('Pat Lavay','The Shipping News','02-JAN-02', '12-JAN-02');

• Create function to calculate overdue charges:

  CREATE OR REPLACE FUNCTION OVERDUE_CHARGES(aName IN VARCHAR2) RETURN NUMBER IS 
  owed_amount NUMBER(10,2);  
  BEGIN 
  SELECT (((ReturnedDate - CheckoutDate) - 14) * 0.20) INTO owed_amount 
  FROM BOOKSHELF_CHECKOUT WHERE Name = aName; 
  RETURN owed_amount;  
  END;

Calling Functions in SQL

• Call your function from SQL:

  SELECT OVERDUE_CHARGES('Gerhardt Kentgen')FROM DUAL;

Function Rules

• A function used in a SELECT statement cannot modify database tables.

• Functions cannot execute INSERT, UPDATE, or DELETE statements.

• Functions called from a SELECT cannot execute transaction control statements (e.g., COMMIT).

Exercises

Exercise #1

• Create a function countEmp(department_number) to count employees:

  CREATE OR REPLACE FUNCTION countEmp(dep_no NUMBER) RETURN NUMBER AS 
  total_emp NUMBER; 
  BEGIN 
  SELECT COUNT(*) INTO total_emp FROM scott.emp WHERE dep_no = dep_no; 
  RETURN total_emp;  
  END;

  • Call: DBMS_OUTPUT.PUT_LINE(countEmp(10));

Exercise #2

• Implement findVal(x IN NUMBER, y IN NUMBER) to find the maximum:

  CREATE OR REPLACE FUNCTION findVal(x IN NUMBER, y IN NUMBER) RETURN NUMBER IS 
  z NUMBER; 
  BEGIN 
  IF x > y THEN z := x; ELSE z := y; END IF; 
  RETURN z;  
  END;  

  • Test the function:

  DECLARE  
  a NUMBER := 23;  
  b NUMBER := 45;  
  BEGIN  
  DBMS_OUTPUT.PUT_LINE(findVal(a, b));  
  END;

Exercise #3

• Function displayEmp(emp_numb) to display employee info:

  • Sample call: displayEmp(7782)

Exercise #4

• Function getEmp(manager) to display employees under a manager:

  • Sample call: getEmp(7566)

Managing Functions

Dropping Functions

• Commands to drop a function:

  • DROP FUNCTION <function_name>;

  • DROP FUNCTION BODY <function_name>;

Cursor and FOR Loop

Cursors

• Cursors retrieve data row by row, facilitating updates.

• Example of using a cursor:

  DECLARE  
  CURSOR c_product IS SELECT product_name, list_price FROM products ORDER BY list_price DESC;  
  BEGIN  
  FOR r_product IN c_product LOOP  
  DBMS_OUTPUT.PUT_LINE(r_product.product_name || ': $' || r_product.list_price);  
  END LOOP;  
  END;

Named Block (Function)

• Example: Calculate total salary of an employee using cursor:

  CREATE OR REPLACE FUNCTION TotalIncome(name_in IN VARCHAR2) RETURN NUMBER IS 
  total_val NUMBER(6);  
  cursor c1 IS SELECT monthly_income FROM employees WHERE name = name_in;  
  BEGIN  
  total_val := 0;  
  FOR employee_rec IN c1 LOOP  
  total_val := total_val + employee_rec.monthly_income;  
  END LOOP;  
  RETURN total_val;  
  END;

Packages in PL/SQL

Overview of Packages

• Grouping of procedures, functions, and variables.

• Execution commands for the package that aren't specific to functions.

• Must possess the CREATE ANY PROCEDURE system privilege.

Creating Packages

1. Package Specification:

   • Lists available functions, procedures, variables, etc.:

   CREATE [OR REPLACE] PACKAGE package_name AS 
   package_specification;  
   END;  

2. Package Body:

   • Contains implementation:

   CREATE [OR REPLACE] PACKAGE BODY package_name AS 
   package_body;  
   END;  

Example of Package Specification

• Creating package for book management functions:

  CREATE OR REPLACE PACKAGE BOOK_MANAGEMENT AS  
  FUNCTION OVERDUE_CHARGES(aName IN VARCHAR2) RETURN NUMBER;  
  PROCEDURE RECEIVE_BOOK_ORDER(aTitle IN VARCHAR2, aPublisher IN VARCHAR2, aCategoryNames IN VARCHAR2);  
  END BOOK_MANAGEMENT;

Package Body Example

• Implementation of package body:

  CREATE OR REPLACE PACKAGE BODY BOOK_MANAGEMENT AS  
  FUNCTION OVERDUE_CHARGES(aName IN VARCHAR2) RETURN NUMBER IS  
  owed_amount NUMBER(10,2);  
  BEGIN  
  RETURN owed_amount;  
  END OVERDUE_CHARGES;  
  
  PROCEDURE RECEIVE_BOOK_ORDER(aTitle IN VARCHAR2, aPublisher IN VARCHAR2, aCategoryNames IN VARCHAR2) AS  
  BEGIN  
  END RECEIVE_BOOK_ORDER;  
  END BOOK_MANAGEMENT;

Using Package Components

• To execute a procedure, prefix with package name:

  EXECUTE BOOK_MANAGEMENT.OVERDUE_CHARGES('PAT LAVAY');

Dropping Package

• Command to drop a package:

  DROP PACKAGE package_name;

Compiling Procedures, Functions, and Packages

Compilation Relationship

• Compiled when created, can become invalid if referenced database objects change.

• Objects will recompile when executed again.

Explicit Compilation

• Avoid runtime compilation by:

  ALTER FUNCTION OVERDUE_CHARGES COMPILE;