Answer» - STORED procedure is a group of SQL queries that are executed as a single logical unit to perform a specific task. Name of the procedure should be unique since each procedure is represented by its name.
- For example, operations on an employee database like obtaining information about an employee COULD be coded as stored procedures that will be executed by an application. Code for creating a stored procedure named GET_EMP_DETAILS is given below:
DELIMITER $$DROP PROCEDURE IF EXISTS `EMP`.`GET_EMP_DETAILS` $$CREATE PROCEDURE `EMP`.`GET_EMP_DETAILS` (IN EMP_ID INT, OUT EMP_DETAILS VARCHAR(255))BEGIN SELECT first INTO EMP_DETAILS FROM Employees WHERE ID = EMP_ID;END $$DELIMITER ;Stored procedures are called using CALLABLESTATEMENT class AVAILABLE in JDBC API. Below given code demonstrates this: CallableStatement cs = con.prepareCall("{call GET_EMP_DETAILS(?,?)}");ResultSet rs = cs.executeQuery();- Three types of parameters are provided in the stored procedures. They are:
- IN: It is used for passing the INPUT values to the procedure. With the help of setXXX() methods, you can bind values to IN parameters.
- OUT: It is used for getting the value from the procedure. With the help of getXXX() methods, you can obtain values from OUT parameters.
- IN/OUT: It is used for passing the input values and obtaining the value to/from the procedure. You bind variable values with the setXXX() methods and obtain values with the getXXX() methods.
|
