Scribd
Upload
12 views6 pages

122CS0079 Aaa (LEKNATH GHALLEY)

The document contains various SQL commands and PL/SQL procedures related to managing an EMPLOYEE table, including creating the table, inserting data, selecting records, and defining stored procedures and triggers. It also demonstrates error handling with TRY-CATCH blocks and includes a recursive function to calculate the sum of numbers. Overall, it serves as a practical example of database management using PL/SQL syntax.

Uploaded by

sai713194
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
12 views6 pages

122CS0079 Aaa (LEKNATH GHALLEY)

The document contains various SQL commands and PL/SQL procedures related to managing an EMPLOYEE table, including creating the table, inserting data, selecting records, and defining stored procedures and triggers. It also demonstrates error handling with TRY-CATCH blocks and includes a recursive function to calculate the sum of numbers. Overall, it serves as a practical example of database management using PL/SQL syntax.

Uploaded by

sai713194
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
You are on page 1/ 6

DBMS Lab PL/SQL

122CS0079

LEK NATH GHALLEY

Q1.

CREATE TABLE EMPLOYEE(Employee_ID INT, Name VARCHAR(40), Age INT, Salary INT,
Department VARCHAR(80))
INSERT INTO EMPLOYEE VALUES
(1 ,'Adiya', 25 ,30000 ,'Executive' ),
(2 ,'Aditya', 28, 31000 ,'Accountant'),
(3 ,'Priyanka', 30 ,32000, 'Salesman'),
(4 ,'Anmol' ,35, 31000, 'Executive' ),
(5 ,'Rahul', 31 ,29000, 'Accountant' ),
(6 ,'Shubham', 29, 27000, 'Salesman' ),
(7 ,'Sam', 33 ,32000 ,'Salesman' ),
(8 ,'Kabir', 33 ,28000, 'Salesman'),
(9 ,'Priya', 23, 35000 ,'Accountant') ,
(10, 'Kabir' ,29, 26000 ,'Executive' )

SELECT*FROM EMPLOYEE

DECLARE @NAME VARCHAR(40)='Aditya';


DECLARE @AGE INT =28;
DECLARE @SALARY INT;
BEGIN
SELECT @SALARY = SALARY FROM EMPLOYEE
WHERE NAME=@NAME AND AGE=@AGE
print
@SALARY
END;

SELECT NAME,DEPARTMENT FROM EMPLOYEE;


CREATE PROCEDURE SH(
@name varchar(20),
@department varchar(20)
)
as
begin
select * from EMPLOYEE where Name = @name
and Department = @department;
end;

EXECUTE SH @name = 'Adiya' , @department = 'Executive';

SELECT COUNT(*) AS COUNT FROM EMPLOYEE

DECLARE vend_cursor CURSOR


FOR SELECT EMPLOYEE_ID,NAME,DEPARTMENT FROM EMPLOYEE ;

OPEN vend_cursor ;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
DEALLOCATE vend_cursor;
DECLARE vend_cursor CURSOR
FOR SELECT EMPLOYEE_ID,NAME,SALARY FROM EMPLOYEE ;
BEGIN
OPEN vend_cursor ;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
FETCH NEXT FROM vend_cursor;
DEALLOCATE vend_cursor;
END;
BEGIN TRY
DECLARE @num INT, @msg varchar(200),@MNO VARCHAR(200),@ESV varchar(200),@EST
varchar(200),@EP varchar(200) ,@EL varchar(200)
SET @num = 5/0
PRINT 'This will not execute'
END TRY
BEGIN CATCH
PRINT 'Error occured that is'
set @msg=(SELECT ERROR_MESSAGE())
print @msg;
SET @MNO=(SELECT Error_number())
PRINT @MNO;
SET @ESV=(SELECT Error_Severity())
PRINT @ESV;
SET @EST=(SELECT Error_State())
PRINT @EST;
SET @EP=(SELECT Error_Procedure())
PRINT @EP;
SET @EP=(SELECT Error_LINE())
PRINT @EL;
END CATCH
GO
CREATE TRIGGER reminder1
ON dbo.employee
AFTER INSERT, UPDATE
AS RAISERROR ('NEW INSERTS ARE NOT ALLOWED', 5, 3);
GO

IF OBJECT_ID ('reminder1 ', 'TR') IS NOT NULL


DROP TRIGGER reminder1 ;

create function dbo.recursivefun1(@n int)


returns int
as begin
declare @sum int;
if @n=1
set @sum=1;
else
set @sum=@n+dbo.recursivefun1(@n-1);
return @sum;
end;

declare @num int


begin
set @num = dbo.recursivefun1(10);
print 'sum is: ' + cast(@num as varchar(20));
end

================================================================================

You might also like