Scribd Logo
Upload

Welcome to Scribd!

  • 3 views

    Assignment 6

    Uploaded by

    SUMEDH PATIL

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Assignment 6

    Uploaded by

    SUMEDH PATIL
    3 views5 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    3 views5 pages

    Assignment 6

    Uploaded by

    SUMEDH PATIL

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 5

    Name : Sanket Raju Nagade

    Roll No : SI-28
    Subject : DBMS
    Assignment 06
    Write and execute PL/SQL stored procedure and function to perform a suitable task on the
    database. Demonstrate to use.

    1. A procedure to display stu_id and student.

    mysql> use sanket18;


    Database changed
    mysql> create table student
    -> (stu_id int(5) not null,
    -> student_name varchar(10),
    -> DOB date,
    -> primary key(stu_id));
    Query OK, 0 rows affected, 1 warning (0.04 sec)

    mysql> insert into student values(1, 'Sai', 20040912);


    Query OK, 1 row affected (0.01 sec)

    mysql> insert into student values(2, 'Sanket', 20041214);


    Query OK, 1 row affected (0.01 sec)

    mysql> insert into student values(3, 'Savim', 20040818), (4, 'Vinit', 20040321);
    Query OK, 2 rows affected (0.00 sec)
    Records: 2 Duplicates: 0 Warnings: 0

    mysql> select * from student;


    + + + +
    | stu_id | student_name | DOB |
    + + + +
    | 1 | Sai | 2004-09-12 |
    | 2 | Sanket | 2005-03-14 |
    | 3 | Savim | 2004-07-08 |
    | 4 | Vinit | 2004-08-08 |

    + + +
    + 4 rows in set (0.00 sec)

    mysql> delimiter $
    mysql> create procedure my_pro()
    -> select stu_id, student_name from student
    -> $
    Query OK, 0 rows affected (0.08 sec)

    mysql> call mypro()$


    ERROR 1305 (42000): PROCEDURE dbms.mypro does not exist
    mysql> call my_pro()$
    + + +
    | stu_id | student_name |
    + + +
    | 1 | Sai |
    | 2 | Sanket |
    | 3 | Savim |
    | 4 | Vinit |

    + + +
    4 rows in set (0.01 sec)

    Query OK, 0 rows affected (0.01 sec)

    mysql> create procedure stud(IN id INT(5), OUT name varchar(10))


    -> begin
    -> select student_name into name
    -> from student
    -> where stu_id = id;
    -> end$
    Query OK, 0 rows affected, 1 warning (0.01 sec)

    mysql> call stud(2, @x)$


    Query OK, 1 row affected (0.01 sec)

    mysql> call stud(2, @x)


    -> $
    Query OK, 1 row affected (0.00 sec)

    mysql> select @x$


    + +
    | @x |
    + +
    | Sanket |
    + +
    1 row in set (0.00 sec)

    mysql> create procedure cleanup()


    -> delete from student;
    -> $
    Query OK, 0 rows affected (0.01 sec)

    mysql> call cleanup()$


    Query OK, 4 rows affected (0.00 sec)

    mysql> select * from student;$


    Empty set (0.00 sec)

    2. A procedure to find maximum number.


    mysql> delimiter #
    mysql> create procedure findmax(IN a int, IN b int)
    -> begin
    -> declare maxx int;
    -> if a>b then set maxx = a;
    -> else set maxx = b;
    -> end if;
    -> select maxx as 'Maximum Number';
    -> end#
    Query OK, 0 rows affected (0.01 sec)

    mysql> call findmax(14, 9);


    -> #
    + +
    | Maximum Number |
    + +
    | 14 |
    + +
    1 row in set (0.01 sec)

    Query OK, 0 rows affected (0.01 sec)

    3. Function to find odd or even number.


    mysql> delimiter @
    mysql> create function oddeve(a int)
    -> returns varchar(10)
    -> deterministic
    -> begin
    -> declare result varchar(10);
    -> if a%2=0 then
    -> set result = 'Even';
    -> else
    -> set result = 'Odd';
    -> end if;
    -> return result;
    -> end@
    Query OK, 0 rows affected (0.01 sec)

    mysql> select oddeve(14);


    -> @
    + +
    | oddeve(14) |
    + +
    | Even |
    + +
    1 row in set (0.01 sec)

    mysql> select oddeve(7);


    -> @
    + +
    | oddeve(7) |
    + +
    | Odd |
    + +
    1 row in set (0.00 sec)

    You might also like