Scribd
Upload
0 views4 pages

rdbms lab exercise

The document outlines the creation of a stored procedure to retrieve employee details from an 'employee' table in a MySQL database. It includes steps for creating the database, the table structure, inserting records, and defining the stored procedure. Additionally, it demonstrates how to call the procedure to get details of all employees or a specific employee by ID.

Uploaded by

Chandra
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
0 views4 pages

rdbms lab exercise

The document outlines the creation of a stored procedure to retrieve employee details from an 'employee' table in a MySQL database. It includes steps for creating the database, the table structure, inserting records, and defining the stored procedure. Additionally, it demonstrates how to call the procedure to get details of all employees or a specific employee by ID.

Uploaded by

Chandra
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

Ex.No.

9 STORED PROCEDURE

Statement of problem
Create a stored procedure to get employee details from employee table

Aim
To Create a stored procedure to get employee details from employee table

PROCEDURE:
Create Database
 CREATE DATABASE emp;
show the database
 SHOW DATABASES;
Using the database
 USE emp;

Create the 'employee' table


 CREATE TABLE employee
( employee_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
position VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2));
 Desc employee;

Insert some records into the 'employee’ table


 INSERT INTO employee (first_name, last_name, position, department, salary)
VALUES ('John', 'Doe', 'Software Engineer', 'IT', 60000.00),
('Jane', 'Smith', 'Marketing Manager', 'Marketing', 75000.00),
('Mike', 'Johnson', 'Database Administrator', 'IT', 65000.00),
('Emily', 'Davis', 'HR Specialist', 'HR', 55000.00);
Selecting the table
 select * from employee;

creating stored procedure


DELIMITER //
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT )
BEGIN
IF EmployeeID IS NULL THEN
SELECT employee_id, first_name, last_name, position, department, salary
FROM employee;
ELSE
SELECT employee_id, first_name, last_name, position, department, salary
FROM employee WHERE employee_id = EmployeeID;
END IF;
END //
DELIMITER ;

Using the stored procedure

Get details of all employees:

 CALL GetEmployeeDetails(NULL);

Get details of a specific employee (e.g., employee with ID 1):

 CALL GetEmployeeDetails(1);

Result
The mysql query to create a stored procedure to get employee details from employee
table has been done successfully
Displaying employee Table Structure:

Displaying employee Table Records

Displaying all Employee Details using Stored Procedure:

Displaying Particular Employee Details using Stored Procedure:

You might also like