In a relational database management system such as Oracle, a stored procedure is a precompiled set of SQL statements and queries that can be shared by a number of programs. It is stored under a name as an executable unit. A stored function is similar to a function (like in Java and C/C++); it accepts zero, one, or more parameters and returns a single result. Stored procedures and functions are helpful in the following ways:
Oracle, Microsoft SQL Server 2000, and Sybase Adaptive Server support stored procedures, but MySQL does not (stored procedures and views will be supported in MySQL 5.0.1, however). In general, you can use stored procedures to maximize security and increase data access efficiency. Because stored procedures execute in the database server, they minimize the network traffic between applications and the database, increasing application and system performance. Most of the time, stored procedures run faster than SQL. They also allow you to isolate your SQL code from your application.
Using Oracle9i database, consider the following table:SQL> describe zemps;
Next, try a basic query of the zemps table:SQL> select id, firstName, lastName from zemps;
The following stored procedure, getEmpCount, returns the number of records in the zemps table:SQL> CREATE OR REPLACE function getEmpCount return int is
In order to make sure that getEmpCount is created correctly, you can execute it as follows,without passing any parameters:SQL> var empCount number;
The output proves that the getEmpCount performed correctly because it returned 14, which is the total number of records in the zemps table.
Overloading Stored Procedures
Oracle’s PL/SQL allows two or more packaged subprograms to have the same name. A package is a set of logically related functions and procedures, also known as a stored procedure. When stored procedures have the same name but different parameters, this is called overloading. This option is useful when you want a subprogram or function to accept parameters that have different data types. Be very cautious when you call overloaded subprogram or functions. You must make sure that you are passing the expected number of arguments and data types. For example, in Oracle 9i, the following package defines two functions named empPackage.
Oracle’s package specification is as follows:CREATE or REPLACE PACKAGE empPackage AS
Oracle’s package implementation is as follows:CREATE or REPLACE PACKAGE BODY empPackage AS
Note You may be wondering what this discussion has to do with getting the names of the stored procedures. This is because stored procedure names can be overloaded, and so you must be very careful in selecting the stored procedure names and their associated input parameter types.
How Can You Find the Package Code in Oracle?
The following SQL statement provides a way to see the Oracle package code:select LINE, TEXT
What Is the user_source Table?
The user_source table, which is a property of Oracle’s SYS user, is as follows. The output has been modified to include a description column.SQL> describe user_source;
What Are the Names of a Database’s Stored Procedures?
In the JDBC API, you can use the DatabaseMetaData.getProcedures() method to get the names of a database’s stored procedures and functions. However, this is not sufficient for very large databases. For example, in an Oracle database, atabaseMetadata.getProcedures() can return hundreds of stored procedures; most are system stored procedures, which most likely you do not need to retrieve. When you call this method, be as specific as possible when you provide names and patterns.
JDBC Solution: getProcedures()
Using JDBC, you can use DatabaseMetaData.getProcedures() to retrieve stored procedure names: To have a better performance, try to pass as much as information you can and avoid passing empty and null values to the DatabaseMetaData.getProcedures() method. Passing empty and null values might have a poor performance, and this is due to the fact that it might search all database catalogs and schemas. Therefore, it is best to pass as much as information (actual parameter values) to the DatabaseMetaData.getProcedures() method./**
A Client Program
Before invoking a client program, let’s add another stored function: the getEmployeeCount stored function returns the number of employees for a specific department.SQL> create FUNCTION getEmployeeCount(dept INTEGER) RETURN INTEGER IS
A Client ProgramString spNames = DatabaseMetaDataTool.getStoredProcedureNames
Output of the Client Program<storedProcedures>
JDBC Related Interview Questions
|Core Java Interview Questions||JSP Interview Questions|
|Java Servlets Interview Questions||EJB(Enterprise JavaBeans) Interview Questions|
|JSTL(JSP Standard Tag Library) Interview Questions||JNDI (Java Naming and Directory Interface) Interview Questions|
|Java Bean Utils Interview Questions||AWT (Abstract Window Toolkit) Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.