RDBMS slip 3 contains a perfect format of coding

create table newspaper (name char(20) primary key,language char(20) , publisher
varchar(20) , cost number(5) );
create table cities (pincode number(20) primary key , city char(20), state
char(20));
create table nc (name char(20) references newspaper(name), pincode number(20)
references cities(pincode), dailyr number(20) );

SQL> insert into newspaper values('sakal','marathi','xyz',10);

1 row created.
SQL> insert into cities values(411038,'kothrud','maharashtra');

1 row created.

SQL> insert into nc values('sakal',411038,500);

1 row created.


SQL> create or replace trigger t8
2 before insert on cities
3 for each row
4 Begin
5 if(length(:new.pincode) !=6) then
6 raise_application_error(-20001,'pincode length should be 6');
7 End if;
8 End;
9 /

Output

SQL> insert into cities values(4110367,'pune','Maharashtra');
insert into cities values(4110367,'pune','Maharashtra')
*
ERROR at line 1:
ORA-20001: pincode length should be 6
ORA-06512: at "SYSTEM.T8", line 3
ORA-04088: error during execution of trigger 'SYSTEM.T8'


1)
SQL> CREATE OR REPLACE Procedure tc(ecity IN char)
2 IS
3 tot number;
4 BEGIN
5 select sum(cost) into tot from newspaper,cities,nc where
newspaper.name=nc.name and cities.pincode=nc.pincode and city=ecity;
6 dbms_output.put_line('citiwise total cost of newspapaper='||tot);
7
8 END;
9 /

Procedure created.

SQL> begin
2 tc('pune');
3 end;
4 /
citiwise total cost of newspapaper=230