KEMBAR78
Sample Worksheet 4 | PDF | Databases | Information Retrieval
Scribd
Upload
50 views5 pages

Sample Worksheet 4

The document describes an experiment involving creating procedures to perform CRUD (create, read, update, delete) operations on a database table. It involves creating a table to hold employee data, then creating procedures to insert, select, delete, and update records in the table. The procedures are called to demonstrate performing each operation. The student learns how to show records, update records, select specific records, and update records using procedures in a database.

Uploaded by

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

Sample Worksheet 4

The document describes an experiment involving creating procedures to perform CRUD (create, read, update, delete) operations on a database table. It involves creating a table to hold employee data, then creating procedures to insert, select, delete, and update records in the table. The procedures are called to demonstrate performing each operation. The student learns how to show records, update records, select specific records, and update records using procedures in a database.

Uploaded by

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

Experiment: Programs related to Procedures

Student Name: Vijay kumar Maurya                                   UID: 21MCI1113

Branch: MCA   (AI&ML)                                                                Section/Group: B

Semester: 1ST                                                                  Date of Performance: 01/10/2021

Subject Name: PL/SQL LAB Subject Code: 21CAP-627

1) Task to be done:
CREATE THE TABLE AND CREATE THE PROCEDURE FOR PERFORMING
FOLLOWING OPERATIONS
i. Insert the records into the database
ii. Fetch the records from the database
iii. Delete the specific row of your choice records from the database
iv. Modified the specific records of the database.

2) Steps for experiment/practical:

Create a table:
create table emp(
    NAME VARCHAR2(4000),
    EMAIL VARCHAR2(4000),
    PHONE VARCHAR(10),
    SAL VARCHAR2(4000)
);

Insert the record into the database:


CREATE OR REPLACE procedure insertemployee
( 
    CR IN NUMBER, 
    NAMES IN VARCHAR, 
    AGE IN NUMBER
)
is
Begin
insert into EMPLOYEE values(CR, NAMES, AGE);
end;

insert record through calling the procedure


Begin
insertemployee ('2', 'Vijay', '11');
end;

Fetch the records from the database


CREATE OR REPLACE PROCEDURE selectEMPLOYEE(
    P_CR IN EMPLOYEE.CR%TYPE,
    P_NAMES OUT EMPLOYEE.NAMES%TYPE
)
IS
BEGIN
select NAMES INTO P_NAMES from EMPLOYEE WHERE CR = P_CR;
END;

Call the record:


DECLARE
   p_cr employee.CR%TYPE;
   p_names employee.NAMES%TYPE;
BEGIN

   selectemployee(1,p_nameS);
   DBMS_OUTPUT.PUT_LINE('serial Number :  ' || p_cr);
   DBMS_OUTPUT.PUT_LINE('Name :  ' || p_names);
END;

Delete the specific row of your choice records from the database:
CREATE OR REPLACE PROCEDURE deleteEMPLOYEE(
    P_CR IN EMPLOYEE.CR%TYPE
)
IS
BEGIN
delete from EMPLOYEE WHERE P_CR = CR;
END;
Call delete procedure
BEGIN
deleteemployee('2');
END;

Modified the specific records of the database.


CREATE OR REPLACE PROCEDURE UPDATEEMPLOYEE(
    P_CR IN EMPLOYEE.CR%TYPE,
    P_NAMES IN EMPLOYEE.NAMES%TYPE,
    P_AGE IN EMPLOYEE.AGE%TYPE
)
IS
BEGIN
UPDATE EMPLOYEE SET NAMES = P_NAMES, AGE = P_AGE WHERE P_CR = CR;
END;

Calling the procedure


BEGIN
UPDATEEMPLOYEE('2','Aakas','23');
end;

3) Output

Create table:

Insert values
Fetch the records from the database

Delete the specific row of your choice records from the database:
Before calling deleting procedure

After the calling the procedure:

Modified the specific records of the database.


Before calling the update procedure

After calling the procedure

4) Learning outcomes (What I have learnt):

1. Show the record through procedure.


2. Update the table record through procedure.

3. Show the specific record through table.

4. Update the record through procedure.

Evaluation Grid:
Sr. No. Parameters Marks Obtained Maximum Marks
1. Demonstration and Performance 5
(Pre Lab Quiz)
2. Worksheet 10
3. Post Lab Quiz 5

You might also like