What are Stored procedures in JDBC?

Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. All the applications that can access Relational databases (Java, Python, PHP etc.), can access stored procedures.

Stored procedures contain IN and OUT parameters or both. They may return result sets in case you use SELECT statements. Stored procedures can return multiple result sets.

Creating a Stored procedure

Suppose, we have created a table named Employee in MySQL database as shown below:

String createTable = "CREATE TABLE Employee("
   + "Name VARCHAR(255), "
   + "Salary INT NOT NULL, "
   + "Location VARCHAR(255))";

Following is an example of a MySQL stored procedure. Here we are inserting a column in to the Employee table taking values from user. (through input parameters)

mysql> DELIMITER // ;
mysql> Create procedure myProcedure (IN name VARCHAR(30), IN sal INT, IN locVARCHAR(45))
   -> BEGIN
   -> INSERT INTO Employee(Name, Salary, Location) VALUES (name, sal, loc);
   -> END //
Query OK, 0 rows affected (0.13 sec)

Calling a stored procedure:

Following statement calls the above created stored procedure.

CALL myProcedure ('Raman', 35000, 'Bangalore')//

If you retrieve the contents of table, you can observe the newly inserted row as

mysql> select * from employee;
+---------+--------+----------------+
| Name    | Salary | Location       |
+---------+--------+----------------+
|| Raman  | 35000  | Bangalore      |
+---------+--------+----------------+
1 rows in set (0.00 sec)

Nancy Den

Published on 20-Mar-2019 15:16:24