Practical No :
Aim :WAP a Program in PL/SQL to create Cursor.
Performed by : Silvee C. Choudhari
Class :B.Sc. II Sem IV (COSE)
Date : 02/04/2024
SQL> Describe cust_mstr;
NAME TYPE
ID NUMBER(2)
NAME VARCHAR2(8)
AGE NUMBER(2)
ADDRESS VARCHAR2(9)
SALARY NUMBER(6)
SQL> create table cust_mstr(id number(2),name varchar2(8),age number(2),address
varchar2(9),salary number(6));
Table created.
SQL> insert into
cust_mstr(id,name,age,address,salary)values(1,'Ramesh',32,'Ahmedabad',2000);
1 row created.
SQL> insert into cust_mstr(id,name,age,address,salary)values(2,'Khilan',25,'Delhi',1500);
1 row created.
SQL> insert into cust_mstr(id,name,age,address,salary)values(3,'Kaushik',23,'Kota',2000);
1 row created.
SQL> insert into cust_mstr(id,name,age,address,salary)values(4,'Chaitali',25,'Mumbai',6500);
1 row created.
SQL> insert into cust_mstr(id,name,age,address,salary)values(5,'Hardik',27,'Bhopal',8500);
1 row created.
SQL> insert into cust_mstr(id,name,age,address,salary)values(6,'Komal',22,'Mp',4500);
1 row created.
SQL> select * from cust_mstr;
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000
2 Khilan 25 Delhi 1500
3 Kaushik 23 Kota 2000
4 Chaitali 25 Mumbai 6500
5 Hardik 27 Bhopal 8500
6 Komal 22 Mp 4500
6 rows selected.
SQL> DECLARE
2 total_rows number(2);
3 BEGIN
4 UPDATEcust_mstr
5 SET salary=salary+500;
6 IFsql%notfound THEN
7 dbms_output.put_line('no cust_mstr selected');
8 ELSIFsql%found THEN
9 total_rows:=sql%rowcount;
10 dbms_output.put_line(total_rows||'cust_mstr selected');
11 END IF;
12 END;
13 /
PL/SQL procedure successfully completed.
Output :
SQL> select * from cust_mstr;
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2500
2 Khilan 25 Delhi 2000
3 Kaushik 23 Kota 2500
4 Chaitali 25 Mumbai 7000
5 Hardik 27 Bhopal 9000
6 Komal 22 Mp5000
6 rows selected.
SQL>
Practical No :
Aim :WAP a Program in PL/SQL to create Trigger.
Performed by : Silvee C. Choudhari
Class :B.Sc. II Sem IV (COSE)
Date : 02/04/2024
SQL> Describe Student;
NAME TYPE
ROLL_NO NUMBER(2)
SNAME VARCHAR2(9)
AGE NUMBER(2)
COURSE VARCHAR2(5)
SQL> create table Student(roll_no number(2),sname varchar2(9),age number(2),course
varchar2(5));
Table created.
SQL> insert into Student(roll_no,sname,age,course)values(11,'Anushri',20,'BSC');
1 row created.
SQL> insert into Student(roll_no,sname,age,course)values(12,'Anupam',21,'BCOM');
1 row created.
SQL> insert into Student(roll_no,sname,age,course)values(13,'Arpita',18,'BCA');
1 row created.
SQL> insert into Student(roll_no,sname,age,course)values(14,'Nikhil',20,'BCA');
1 row created.
SQL> insert into Student(roll_no,sname,age,course)values(15,'Rajkumar',19,'BBA');
1 row created.
SQL> select * from Student;
ROLL_NO SNAME AGE COURSE
11 Anushri 20 BSC
12Anupam 21 BCOM
13 Arpita 18 BCA
14 Nikhil 20 BCA
15 Rajkumar 19 BBA
SQL> CREATE OR REPLACE TRIGGER CheckAge
2 BEFORE
3 INSERT OR UPDATE ON Student
4 FOR EACH ROW
5 BEGIN
6 IF:new.Age>30 THEN
7 raise_application_error(-20001,'Age should not be greater than 30');
8 End IF;
9 END;
10 /
Trigger created.
SQL> select * from Student;
ROLL_NO SNAME AGE COURSE
11 Anushri 20 BSC
12 Anupam 21 BCOM
13 Arpita 18 BCA
14 Nikhil 20 BCA
15 Rajkumar 19 BBA
Practical No :
Aim :WAP a Program in PL/SQL code to create Simple Function.
Performed by : Silvee C. Choudhari
Class :B.Sc. II Sem IV (COSE)
Date : 02/04/2024
SQL> CREATE OR REPLACE FUNCTION Adder(n1 in number,n2 in number)
2 return number
3 is
4 n3 number(8);
5 BEGIN
6 n3:=n1+n2;
7 return n3;
8 END;
9 /
Function created.
Practical No :
Aim :WAP in PL/SQL code to create Simple Function.
Performed by : Silvee C. Choudhari
Class :B.Sc. II Sem IV (COSE)
Date : 02/04/2024
SQL> set serveroutput on;
SQL> declare
2 n number:=5;
3 i number;
4 j number;
5 begin
6 for i in 1..n
7 loop
8 for j in 1..i
9 loop
10 dbms_output.put('*');
11 end loop;
12 dbms_output.new_line;
13 end loop;
14 end;
15 /
**
***
****
*****
PL/SQL procedure successfully completed.
SQL>
