PL/SQL Table Function Examples

 Example 1 -

CREATE OR REPLACE FUNCTION squares (p_limit IN NUMBER) RETURN SYS.ODCINUMBERLIST IS l_squares SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(); BEGIN FOR i IN 1..p_limit LOOP l_squares.EXTEND; l_squares(l_squares.COUNT) := i * i; END LOOP; RETURN l_squares; END;

SELECT column_value FROM TABLE(squares(10));

----------------------------------------------

1 4 9 16 25 36 49 64 81 100

Example 2 -

CREATE OR REPLACE FUNCTION get_employees_salaries RETURN SYS.ODCIVARCHAR2LIST AS emp_salaries SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(); BEGIN SELECT ename || ': ' || sal INTO emp_salaries FROM emp; RETURN emp_salaries; END; /

SELECT * FROM TABLE(get_employees_salaries);

REGEXP_LIKE to validate email id.

DECLARE

email VARCHAR2(50) := 'user@example.com';

BEGIN IF NOT REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') THEN raise_application_error(-20000, 'Invalid email'); END IF; END;

Exceptional handling in DB2 stored procedure

 In DB2, you can use the HANDLER statement to handle exceptions that occur within a stored procedure.

Here is an example of how to use the HANDLER statement in a stored procedure:

CREATE OR REPLACE PROCEDURE myProcedure (IN id INTEGER) BEGIN DECLARE myException CONDITION FOR SQLSTATE '45000'; DECLARE CONTINUE HANDLER FOR myException BEGIN -- code to handle the exception goes here SELECT 'An error occurred: ' || SQLERRMC; END; -- code for your stored procedure goes here DELETE FROM myTable WHERE id = id; END;

 Example -1 for insert operation

CREATE PROCEDURE InsertData() BEGIN DECLARE SQLCODE INT; DECLARE SQLSTATE CHAR(5); DECLARE EXIT HANDLER FOR SQLSTATE '23505' -- Handle unique constraint violation exception BEGIN SET SQLCODE = 100; SET SQLSTATE = '00000'; END; BEGIN INSERT INTO ExampleTable (column1, column2) VALUES (1, 'value1'); END; END;

Example -2 update operation

CREATE PROCEDURE UpdateData() BEGIN DECLARE SQLCODE INT; DECLARE SQLSTATE CHAR(5); DECLARE EXIT HANDLER FOR SQLSTATE '02000' -- Handle division by zero exception BEGIN SET SQLCODE = 100; SET SQLSTATE = '00000'; END; BEGIN UPDATE ExampleTable SET column2 = column2 / 0; -- Raises division by zero exception END; END;

PL/SQL Procesure to delete the data older than 60 days

Here is a sample PL/SQL procedure that will delete records from a table that are older than 60 days:

CREATE OR REPLACE PROCEDURE delete_old_records (table_name IN VARCHAR2)

AS

BEGIN

  EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || ' WHERE creation_date < SYSDATE - 60';

END;

This procedure takes the name of the table as an input  and deletes all records from that table whose creation_date is more than 60 days old. The EXECUTE IMMEDIATE statement is used to dynamically generate and execute a DELETE statement.

To call this procedure, you can use the following syntax:

BEGIN

  delete_old_records('my_table');

END;