PL/SQL Encapsulation Packages
 |
|
PL/SQL Encapsulation Packages | |
Apollo Pro can generate a PL/SQL package containing procedures and functions that can be used to provide a programatic interface to the object. This is often more desirable than writing DML directly into code, as it centralizes access to the object and makes it easier to update the database object later. This feature is available by using the popup menu from the Database tab in the SQL Window
In order to generate an encapsulation package, the object must have a primary key defined. Note also that the generated merge procedure and function use the Oracle 9i MERGE statement, so if you are compiling on 9.0 or earlier, you will have to remove or modify this code.
This is an example package specification for the SCOTT.EMP table showing the generated procedures and functions:
CREATE OR REPLACE PACKAGE access_emp AS
-- This package was generated by Apollo Pro, www.apollo-pro.com
-- Generated on 2/5/2005 at 3:12:31 PM
TYPE ref_cursor_type IS REF CURSOR;
/*----------------------------------------------------------------------------*/
FUNCTION fn_sel_cur_emp(
p_empno IN NUMBER
) RETURN access_emp.ref_cursor_type;
PRAGMA RESTRICT_REFERENCES(fn_sel_cur_emp, WNDS, WNPS);
-- This function accepts the primary key of the object SCOTT.EMP
-- It returns a REF CURSOR to the row that matches the primary key passed in.
/*----------------------------------------------------------------------------*/
PROCEDURE sel_cur_emp(
p_empno IN NUMBER,
cur_out OUT access_emp.ref_cursor_type
);
-- This procedure accepts the primary key of the object SCOTT.EMP
-- It passes back a REF CURSOR to the row that matches the primary key passed in.
/*----------------------------------------------------------------------------*/
PROCEDURE sel_emp(
p_empno IN NUMBER,
p_ename OUT VARCHAR2,
p_job OUT VARCHAR2,
p_mgr OUT NUMBER,
p_hiredate OUT DATE,
p_sal OUT NUMBER,
p_comm OUT NUMBER,
p_deptno OUT NUMBER
);
-- This procedure accepts the primary key of the object SCOTT.EMP
-- It passes back OUT parameters for each non primary key column of the row that matches the primary key passed in.
/*----------------------------------------------------------------------------*/
FUNCTION fn_mrg_emp(
p_empno IN NUMBER,
p_ename IN VARCHAR2 DEFAULT NULL,
p_job IN VARCHAR2 DEFAULT NULL,
p_mgr IN NUMBER DEFAULT NULL,
p_hiredate IN DATE DEFAULT NULL,
p_sal IN NUMBER DEFAULT NULL,
p_comm IN NUMBER DEFAULT NULL,
p_deptno IN NUMBER DEFAULT NULL
) RETURN NUMBER;
-- This function accepts a value for each column of the object SCOTT.EMP
-- It will merge the values using the MERGE statement.
-- It returns a count of the number of rows merged.
/*----------------------------------------------------------------------------*/
PROCEDURE mrg_emp(
p_empno IN NUMBER,
p_ename IN VARCHAR2 DEFAULT NULL,
p_job IN VARCHAR2 DEFAULT NULL,
p_mgr IN NUMBER DEFAULT NULL,
p_hiredate IN DATE DEFAULT NULL,
p_sal IN NUMBER DEFAULT NULL,
p_comm IN NUMBER DEFAULT NULL,
p_deptno IN NUMBER DEFAULT NULL
);
-- This procedure accepts a value for each column of the object SCOTT.EMP
-- It will merge the values using the MERGE statement.
/*----------------------------------------------------------------------------*/
FUNCTION fn_ins_emp(
p_empno IN NUMBER,
p_ename IN VARCHAR2 DEFAULT NULL,
p_job IN VARCHAR2 DEFAULT NULL,
p_mgr IN NUMBER DEFAULT NULL,
p_hiredate IN DATE DEFAULT NULL,
p_sal IN NUMBER DEFAULT NULL,
p_comm IN NUMBER DEFAULT NULL,
p_deptno IN NUMBER DEFAULT NULL
) RETURN NUMBER;
-- This function accepts a value for each column of the object SCOTT.EMP
-- It will insert the values using the INSERT statement.
-- It returns a count of the number of rows inserted.
/*----------------------------------------------------------------------------*/
PROCEDURE ins_emp(
p_empno IN NUMBER,
p_ename IN VARCHAR2 DEFAULT NULL,
p_job IN VARCHAR2 DEFAULT NULL,
p_mgr IN NUMBER DEFAULT NULL,
p_hiredate IN DATE DEFAULT NULL,
p_sal IN NUMBER DEFAULT NULL,
p_comm IN NUMBER DEFAULT NULL,
p_deptno IN NUMBER DEFAULT NULL
);
-- This procedure accepts a value for each column of the object SCOTT.EMP
-- It will insert the values using the INSERT statement.
/*----------------------------------------------------------------------------*/
FUNCTION fn_upd_emp(
p_empno IN NUMBER,
p_ename IN VARCHAR2 DEFAULT NULL,
p_job IN VARCHAR2 DEFAULT NULL,
p_mgr IN NUMBER DEFAULT NULL,
p_hiredate IN DATE DEFAULT NULL,
p_sal IN NUMBER DEFAULT NULL,
p_comm IN NUMBER DEFAULT NULL,
p_deptno IN NUMBER DEFAULT NULL
) RETURN NUMBER;
-- This function accepts a value for each column of the object SCOTT.EMP
-- It will update the matching row using the UPDATE statement based on the primary key.
-- It returns a count of the number of rows updated (either zero or one).
/*----------------------------------------------------------------------------*/
PROCEDURE upd_emp(
p_empno IN NUMBER,
p_ename IN VARCHAR2 DEFAULT NULL,
p_job IN VARCHAR2 DEFAULT NULL,
p_mgr IN NUMBER DEFAULT NULL,
p_hiredate IN DATE DEFAULT NULL,
p_sal IN NUMBER DEFAULT NULL,
p_comm IN NUMBER DEFAULT NULL,
p_deptno IN NUMBER DEFAULT NULL
);
-- This procedure accepts a value for each column of the object SCOTT.EMP
-- It will update the matching row using the UPDATE statement based on the primary key.
/*----------------------------------------------------------------------------*/
FUNCTION fn_del_emp(
p_empno IN NUMBER
) RETURN NUMBER;
-- This function accepts the primary key of the object SCOTT.EMP
-- It will delete the matching row using the DELETE statement based on the primary key.
-- It returns a count of the number of rows deleted (either zero or one).
/*----------------------------------------------------------------------------*/
PROCEDURE del_emp(
p_empno IN NUMBER
);
-- This procedure accepts the primary key of the object SCOTT.EMP
-- It will delete rows using the DELETE statement based on the primary key.
/*----------------------------------------------------------------------------*/
END access_emp;
/
In order to generate an encapsulation package, the object must have a primary key defined. Note also that the generated merge procedure and function use the Oracle 9i MERGE statement, so if you are compiling on 9.0 or earlier, you will have to remove or modify this code.
This is an example package specification for the SCOTT.EMP table showing the generated procedures and functions:
CREATE OR REPLACE PACKAGE access_emp AS
-- This package was generated by Apollo Pro, www.apollo-pro.com
-- Generated on 2/5/2005 at 3:12:31 PM
TYPE ref_cursor_type IS REF CURSOR;
/*----------------------------------------------------------------------------*/
FUNCTION fn_sel_cur_emp(
p_empno IN NUMBER
) RETURN access_emp.ref_cursor_type;
PRAGMA RESTRICT_REFERENCES(fn_sel_cur_emp, WNDS, WNPS);
-- This function accepts the primary key of the object SCOTT.EMP
-- It returns a REF CURSOR to the row that matches the primary key passed in.
/*----------------------------------------------------------------------------*/
PROCEDURE sel_cur_emp(
p_empno IN NUMBER,
cur_out OUT access_emp.ref_cursor_type
);
-- This procedure accepts the primary key of the object SCOTT.EMP
-- It passes back a REF CURSOR to the row that matches the primary key passed in.
/*----------------------------------------------------------------------------*/
PROCEDURE sel_emp(
p_empno IN NUMBER,
p_ename OUT VARCHAR2,
p_job OUT VARCHAR2,
p_mgr OUT NUMBER,
p_hiredate OUT DATE,
p_sal OUT NUMBER,
p_comm OUT NUMBER,
p_deptno OUT NUMBER
);
-- This procedure accepts the primary key of the object SCOTT.EMP
-- It passes back OUT parameters for each non primary key column of the row that matches the primary key passed in.
/*----------------------------------------------------------------------------*/
FUNCTION fn_mrg_emp(
p_empno IN NUMBER,
p_ename IN VARCHAR2 DEFAULT NULL,
p_job IN VARCHAR2 DEFAULT NULL,
p_mgr IN NUMBER DEFAULT NULL,
p_hiredate IN DATE DEFAULT NULL,
p_sal IN NUMBER DEFAULT NULL,
p_comm IN NUMBER DEFAULT NULL,
p_deptno IN NUMBER DEFAULT NULL
) RETURN NUMBER;
-- This function accepts a value for each column of the object SCOTT.EMP
-- It will merge the values using the MERGE statement.
-- It returns a count of the number of rows merged.
/*----------------------------------------------------------------------------*/
PROCEDURE mrg_emp(
p_empno IN NUMBER,
p_ename IN VARCHAR2 DEFAULT NULL,
p_job IN VARCHAR2 DEFAULT NULL,
p_mgr IN NUMBER DEFAULT NULL,
p_hiredate IN DATE DEFAULT NULL,
p_sal IN NUMBER DEFAULT NULL,
p_comm IN NUMBER DEFAULT NULL,
p_deptno IN NUMBER DEFAULT NULL
);
-- This procedure accepts a value for each column of the object SCOTT.EMP
-- It will merge the values using the MERGE statement.
/*----------------------------------------------------------------------------*/
FUNCTION fn_ins_emp(
p_empno IN NUMBER,
p_ename IN VARCHAR2 DEFAULT NULL,
p_job IN VARCHAR2 DEFAULT NULL,
p_mgr IN NUMBER DEFAULT NULL,
p_hiredate IN DATE DEFAULT NULL,
p_sal IN NUMBER DEFAULT NULL,
p_comm IN NUMBER DEFAULT NULL,
p_deptno IN NUMBER DEFAULT NULL
) RETURN NUMBER;
-- This function accepts a value for each column of the object SCOTT.EMP
-- It will insert the values using the INSERT statement.
-- It returns a count of the number of rows inserted.
/*----------------------------------------------------------------------------*/
PROCEDURE ins_emp(
p_empno IN NUMBER,
p_ename IN VARCHAR2 DEFAULT NULL,
p_job IN VARCHAR2 DEFAULT NULL,
p_mgr IN NUMBER DEFAULT NULL,
p_hiredate IN DATE DEFAULT NULL,
p_sal IN NUMBER DEFAULT NULL,
p_comm IN NUMBER DEFAULT NULL,
p_deptno IN NUMBER DEFAULT NULL
);
-- This procedure accepts a value for each column of the object SCOTT.EMP
-- It will insert the values using the INSERT statement.
/*----------------------------------------------------------------------------*/
FUNCTION fn_upd_emp(
p_empno IN NUMBER,
p_ename IN VARCHAR2 DEFAULT NULL,
p_job IN VARCHAR2 DEFAULT NULL,
p_mgr IN NUMBER DEFAULT NULL,
p_hiredate IN DATE DEFAULT NULL,
p_sal IN NUMBER DEFAULT NULL,
p_comm IN NUMBER DEFAULT NULL,
p_deptno IN NUMBER DEFAULT NULL
) RETURN NUMBER;
-- This function accepts a value for each column of the object SCOTT.EMP
-- It will update the matching row using the UPDATE statement based on the primary key.
-- It returns a count of the number of rows updated (either zero or one).
/*----------------------------------------------------------------------------*/
PROCEDURE upd_emp(
p_empno IN NUMBER,
p_ename IN VARCHAR2 DEFAULT NULL,
p_job IN VARCHAR2 DEFAULT NULL,
p_mgr IN NUMBER DEFAULT NULL,
p_hiredate IN DATE DEFAULT NULL,
p_sal IN NUMBER DEFAULT NULL,
p_comm IN NUMBER DEFAULT NULL,
p_deptno IN NUMBER DEFAULT NULL
);
-- This procedure accepts a value for each column of the object SCOTT.EMP
-- It will update the matching row using the UPDATE statement based on the primary key.
/*----------------------------------------------------------------------------*/
FUNCTION fn_del_emp(
p_empno IN NUMBER
) RETURN NUMBER;
-- This function accepts the primary key of the object SCOTT.EMP
-- It will delete the matching row using the DELETE statement based on the primary key.
-- It returns a count of the number of rows deleted (either zero or one).
/*----------------------------------------------------------------------------*/
PROCEDURE del_emp(
p_empno IN NUMBER
);
-- This procedure accepts the primary key of the object SCOTT.EMP
-- It will delete rows using the DELETE statement based on the primary key.
/*----------------------------------------------------------------------------*/
END access_emp;
/