Design and Implementation of Stored Procedure Router Based on Dynamic SQL

Abstract

The stored procedure router is designed to dynamically call stored procedure. It is based on dynamic SQL and coded by PL/SQL. Two tables are established according to stored procedure table and stored procedure parameter table before stored procedure router is called. The caller gets input and output parameters of stored procedure by visiting two tables, without direct contact stored procedure itself. Inputting stored procedure number and parameters can obtain result of executing stored procedure. Dynamic SQL statement based on these parameters is designed and executed. Input parameters and output parameters are bind the corresponding variables which begin to the colon in SQL statement.When invoking stored procedure coded by PL/SQL, it is convenient to use stored procedure router. Only modify content of table when stored procedure changes. It brings a great facilitate to secondary development staff and accelerate the system design process, improve the system design performance.