Scribd
Upload
2 views2 pages

Program 5

The document outlines the creation and management of a database for employee information, including the structure of the Employee table and the insertion of sample data. It also describes the implementation of a stored procedure to fetch and display employee data using a cursor. The procedure includes error handling and looping through the cursor results to output employee details.

Uploaded by

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

Program 5

The document outlines the creation and management of a database for employee information, including the structure of the Employee table and the insertion of sample data. It also describes the implementation of a stored procedure to fetch and display employee data using a cursor. The procedure includes error handling and looping through the cursor results to output employee details.

Uploaded by

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

I 11ndate ooerat

I ions, transactio
ns, and d e .• "- ;
_,
base 1\[Link]
patll ement System
\ (l lc
I r S4~~\
ttbe "a1ues ro III tb e table, Declar
table & [Link] e the va ,
S. Create curs tbe t1a1i1e
or for Et11P1°Y cursor. Clo
ee se the
Open the cu s.tr11ct tbe "81ueS rrolll \1
rsor & e
E J \) )D
,,.,~..55,SJ\LJ\R
=
11
cusToMER
S ( I D ,N ~ ~ G
'
cur&~
,
cREATE DAT
ABASE \ab0 5;

USE lab05;

cREATE TAB
LE Employee
(
E_id INT,
E_name VARC
HAR(255),
Age INT,
Salary DECIM
AL(\ 0, 2)
); I
INSERT INTO
Employee (E_
id, E_name, A
ge, Sa\ar)')
VALUES
(1, •samarth',
30, [Link]),
(2, "Ramesh KU
ll1ar', 25, 4500
0.00),
(3, 'Seema aan
u', 35, 62000.0
0),
(4, •0ennis An
il', 28, 52000.0
0),
(5, 'RebJllan K
han', 32, 5sooo
.oo);
DELIMITER
//

CREATE PRO
CEDURE fetc
h_emp\oyee_d
ataO
BEGIN
-- Declare vari
ables to store cu
rsor values
DECLARE em
p_id INT;
DECLARE em
p_name VARC
HAR(255);
DECLARE em
p_age INT;
DECLARE em
p_salary DEC
IMAL(} o, 2);

-- Declare a cu
rsor for the Em
ployee table
DECLARE em
p_cursor CUR
SOR FOR
Database Management System (BCS403)

SELECT E_id, E_name, Age, Salary


FROM Employee;

- Declare a continue handler for the cursor


DECLARE CONTINUE HANDLER FOR NOT FOUND
SET @finished = I ;

-- Open the cursor


OPEN emp_cursor;

- Initialize a variable to control cursor loop


SET @finished =O;

- Loop through the cursor results


cursor_loop: LOOP
-- Fetch the next row from the cursor into variables
FETCH emp_cursor INTO emp_id, emp_name, emp_age, emp_salary;

- Check if no more rows to fetch


IF @finished = 1 THEN
LEAVE cursor_loop;
ENDIF;

-- Output or process each row (for demonstration, print the values)


SELECT CONCAT('Employee ID:', emp_id, ',Name:', emp_name, ',Age:', emp_age, ',
Salary:', emp_salary) AS Employee_Info;
END LOOP;

- Close the cursor


CLOSE emp_cursor;
END//

DELIMITER;

CALL fetch_employee_dataO;
-- ~ 9

You might also like