Stored Procedure In Mysql

1. How to create stored procedure in mysql ?

CREATE DATABASE javaiq_db;

CREATE TABLE company (
  company_id int PRIMARY KEY NOT NULL AUTO_INCREMENT ,
  name TEXT NOT NULL
);

CREATE TABLE profit (
  profit_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name TEXT,
  company_id int,
  CONSTRAINT fk_company  FOREIGN KEY (company_id)
 REFERENCES company (company_id) MATCH SIMPLE
);

CREATE TABLE employee (
  employee_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  first_name TEXT,
  last_name TEXT,
  company_id int,
  CONSTRAINT fk_companys FOREIGN KEY (company_id)
    REFERENCES company (company_id) MATCH SIMPLE
);

CREATE TABLE employee_profit (
  employee_id int,
  profit_id int,
  CONSTRAINT fk_employee FOREIGN KEY (employee_id)
  REFERENCES employee (employee_id) MATCH SIMPLE ,
  CONSTRAINT fk_profit FOREIGN KEY (profit_id)
  REFERENCES profit (profit_id) MATCH SIMPLE
);

INSERT INTO company (company_id,name) VALUES (1,'JavaIQ');
INSERT INTO employee (employee_id,first_name, last_name, company_id) VALUES (1,'Shubh','Gupta',1);
INSERT INTO profit (profit_id,name,company_id) VALUES (1,'Food',1);
INSERT INTO profit (profit_id,name,company_id) VALUES (2,'Breakfast',1);

Create stored procedure in MYSQL to get all employees list

DELIMITER //

CREATE PROCEDURE GetAllEmployees()
BEGIN
	SELECT *  FROM employee;
END //

DELIMITER ;

Create stored procedure in MYSQL to get employee by employee_id

DELIMITER $$

CREATE PROCEDURE GetEmployee(int_employee_id int)
BEGIN
	SELECT *  FROM employee where employee_id=int_employee_id ;
END $$

DELIMITER ;

Executing a stored procedure in MYSQL

CALL stored_procedure_name(argument_list);

CALL GetAllEmployees();

There are three types of parameters that can be declared:

• IN - This is use as input parameter. The parameter can be referenced by the procedure for input. The value of the parameter can not be overwritten by the procedure..
• OUT - This is use as output parameter. The parameter can not be referenced by the procedure, but the value of the parameter can be overwritten by the procedure.
• IN OUT - This is used for input and output both.The parameter can be referenced by the procedure and the value of the parameter can be overwritten by the procedure or function.

Related Tutorials

• Hibernate Session Interface
• Merge Vs SaveOrUpdate In Hibernate
• Spring Data JPA Interface Methods
• Stored Procedure In Mysql
• Mysql Interview Questions
• Hibernate interview questions part-1
• Hibernate interview questions part-2
• Hibernate interview questions part-3
• Hibernate interview questions part-4
• Hibernate interview questions part-5