Consider the following entities and their relationship.
University (u_no, u_name, u_city)
College (c_no, c_name, c_city, year_of_establishment )
Relationship between University and College is one-to-many
Constraints:primary key, foreign key,u_name and c_name should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a package, which consists of one procedure and one function. Pass university number as a parameter to procedure and display details of that university. Pass city as a parameter to a function and return total number of colleges in given city.
2)Write a cursor which will display university wise their college details. (Use parameterized cursor)
SQL> set serveroutput on
SQL> declare
2 cursor c2(unm university.uno%type) is select uname,cname,ccity,yearofest from university,college where university.uno=college.uno and university.uno=102;
3 c c2%rowtype;
4 begin
5 open c2('&unm');
6 loop
7 fetch c2 into c;
8 exit when c2%notfound;
9 dbms_output.put_line(c.uname||''||c.cname||''||c.ccity||''||c.yearofest);
10 end loop;
11 close c2;
12 end;
13 /
Enter value for unm: 102
old 5: open c2('&unm');
new 5: open c2('102');
delhi university delhi university delhi1997
PL/SQL procedure successfully completed.
University (u_no, u_name, u_city)
College (c_no, c_name, c_city, year_of_establishment )
Relationship between University and College is one-to-many
Constraints:primary key, foreign key,u_name and c_name should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a package, which consists of one procedure and one function. Pass university number as a parameter to procedure and display details of that university. Pass city as a parameter to a function and return total number of colleges in given city.
2)Write a cursor which will display university wise their college details. (Use parameterized cursor)
SQL> set serveroutput on
SQL> declare
2 cursor c2(unm university.uno%type) is select uname,cname,ccity,yearofest from university,college where university.uno=college.uno and university.uno=102;
3 c c2%rowtype;
4 begin
5 open c2('&unm');
6 loop
7 fetch c2 into c;
8 exit when c2%notfound;
9 dbms_output.put_line(c.uname||''||c.cname||''||c.ccity||''||c.yearofest);
10 end loop;
11 close c2;
12 end;
13 /
Enter value for unm: 102
old 5: open c2('&unm');
new 5: open c2('102');
delhi university delhi university delhi1997
PL/SQL procedure successfully completed.
