Subprograms are named PL/SQL blocks that can be called with a set of parameters.
PL/SQL has two types of subprograms, procedures and functions.
Generally, you use a procedure to perform an action and a function to compute a value.
Subprograms in ORACLE can be transformed as follows in ALTIBASE HDB.
Example
ORACLE
ALTIASE HDB
Comments
CREATE OR REPLACE PROCEDURE award_bonus
(emp_id NUMBER, bonus NUMBER)
AS
commission REAL;
comm_missing EXCEPTION;
BEGIN -- executable part starts here
SELECT commission_pct / 100 INTO commission FROM employees
WHERE eno = emp_id;
IF commission IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE employees SET salary = salary + bonus*commission
WHERE eno = emp_id;
END IF;
EXCEPTION -- exception-handling part starts here
WHEN comm_missing THEN
DBMS_OUTPUT.PUT_LINE('This employee does not receive a commission.');
commission := 0;
WHEN OTHERS THEN
NULL; -- for other exceptions do nothing
END award_bonus;
/
CALL award_bonus(150, 400);
CREATE OR REPLACE PROCEDURE award_bonus
(emp_id NUMBER, bonus NUMBER)
AS
commission REAL;
comm_missing EXCEPTION;
BEGIN -- executable part starts here
SELECT commission_pct / 100 INTO commission FROM employees
WHERE eno = emp_id;
IF commission IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE employees SET salary = salary + bonus*commission
WHERE eno = emp_id;
END IF;
EXCEPTION -- exception-handling part starts here
WHEN comm_missing THEN
SYSTEM_.PRINTLN('This employee does not receive a commission.');
commission := 0;
WHEN OTHERS THEN
NULL; -- for other exceptions do nothing
END award_bonus;
/
EXEC award_bonus(150, 400);
Creating a Stored Subprogram
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
c1 EmpCurTyp;
c2 EmpCurTyp;
PROCEDURE get_emp_data (emp_cv1 IN OUT EmpCurTyp, emp_cv2 IN OUT EmpCurTyp) IS
emp_rec employees%ROWTYPE;
BEGIN OPEN emp_cv1 FOR SELECT * FROM employees;
emp_cv2 := emp_cv1;
FETCH emp_cv1 INTO emp_rec; -- fetches first row
FETCH emp_cv1 INTO emp_rec; -- fetches second row
FETCH emp_cv2 INTO emp_rec; -- fetches third row
CLOSE emp_cv1; DBMS_OUTPUT.put_line('The following raises an invalid cursor'); -- FETCH emp_cv2 INTO emp_rec; raises invalid cursor when get_emp_data is called
END;
BEGIN
get_emp_data(c1, c2);
END;
/
CREATE OR REPLACE TYPESET EmpCurTypeset AS TYPE EmpCurTyp IS REF CURSOR; END; /
CREATE OR REPLACE PROCEDURE get_emp_data (emp_cv1 IN OUT EmpCurTypeset.EmpCurTyp, emp_cv2 IN OUT EmpCurTypeset.EmpCurTyp)
AS
TYPE EmpCurTyp IS REF CURSOR;
c1 EmpCurTyp;
c2 EmpCurTyp;
emp_rec employees%ROWTYPE;
BEGIN
OPEN emp_cv1 FOR 'SELECT * FROM employees';
emp_cv2 := emp_cv1;
FETCH emp_cv1 INTO emp_rec; -- fetches first row
FETCH emp_cv1 INTO emp_rec; -- fetches second row
FETCH emp_cv2 INTO emp_rec; -- fetches third row
CLOSE emp_cv1;
SYSTEM_.println('The following raises an invalid cursor');
--FETCH emp_cv2 INTO emp_rec; --raises invalid cursor when get_emp_data is called
--END;
--BEGIN
get_emp_data(c1, c2);
END;
/
Subprogram Parameter Aliasing
In the ALTIBASE HDB, A typeset creation and procedure creation using REF CURSOR should be separated.
