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 10:46:24

• Related Questions & Answers
• What are stored procedures? How to call stored procedures using JDBC program?
• What are the advantages of stored procedures?
• What are the differences between Stored procedures and functions?
• What are recursive stored procedures and why MySQL limits the recursion?
• What are the advantages and disadvantages of using MySQL stored procedures?
• What are TRIGGERS in DB2? What is the difference between TRIGGERS and STORED PROCEDURES?
• What is stored procedure and how can we create MySQL stored procedures?
• Call Stored Procedures within a Stored Procedure with IF Logic?
• Create a stored Procedures using MySQL Workbench?
• What are the special security requirements for using stored procedures and functions together with replication?
• What are the procedures to make Fondant Cakes?
• How can we access tables through MySQL stored procedures?
• What are save points in JDBC? Explain?
• What are batch updates in JDBC? Explain?
• How can we see the list of stored procedures and stored functions in a particular MySQL database?