Experiment 9
DELIMITER $$
CREATE PROCEDURE BUS_PROC1()
BEGIN
SELECT * FROM BUS;
END$$
CALL BUS_PROC1()$$
DELIMITER ;
Program 2 :
DELIMITER $$
CREATE PROCEDURE SAMPLE2()
BEGIN
DECLARE X INT(3);
SET X = 10;
SELECT X;
END$$
CALL SAMPLE2()$$
DELIMITER ;
Program 3:
DELIMITER $$
CREATE PROCEDURE SIMPLE_PROC(OUT PARAM1 INT)
BEGIN
SELECT COUNT(*) INTO PARAM1 FROM BUS;
END$$
CALL SIMPLE_PROC(@a)$$
SELECT @a;
DELIMITER ;
Experiment 10-cursors
CREATE DATABASE IF NOT EXISTS experiment_db;
USE experiment_db;
CREATE TABLE students (
sid INT,
sname VARCHAR(20),
age INT,
marks INT
);
INSERT INTO students VALUES
(1,'ravi',15,25),
(2,'ramu',20,30),
(2,'rahul',18,26),
(5,'kiran',19,28),
(6,'varun',21,32),
(8,'ramesh',22,33),
(8,'xyz',10,20);DELIMITER $$
CREATE PROCEDURE p1(IN_customer_id INT)
BEGIN
DECLARE v_id INT;
DECLARE v_name VARCHAR(20);
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT sid, sname
FROM students
WHERE sid = IN_customer_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_finished = 1;
OPEN c1;
std: LOOP
FETCH c1 INTO v_id, v_name;
IF v_finished = 1 THEN
LEAVE std;
END IF;
SELECT CONCAT(v_id, ' ', v_name) AS Student;
END LOOP std;
CLOSE c1;
END$$
CALL p1(1)$$
DELIMITER ;