Customer and Account Table Having one To Many Relationship. Consider the Following Entities and Their Relationship. Customer(cno,cname,city) Account(ano,acc_type,balance) Constraints: Primary Key, acc_type not be Null

SQL> create table customer

  2  (cno number primary key,

  3  cname varchar(20),

  4  city varchar(15));


Table created.


SQL> create table account

  2  (ano number primary key,

  3  acc_type varchar(20) NOT NULL,

  4  balance number,

  5  cno number references customer);


Table created.


SQL> select * from customer;


       CNO CNAME                CITY

---------- -------------------- ---------------

         1 Raj                  Pune

         2 Ramesh               Pune

         3 Ganesh               Mumbai

         4 Harsh                Nasik

         5 Prince               Delhi


SQL> select * from account;


       ANO ACC_TYPE                BALANCE        CNO

---------- -------------------- ---------- ----------

       101 Current                    1000          1

       102 Saving                      500          1

       103 Recuring                    700          2

       104 Saving                      800          3

       105 Current                     900          3

       106 Current                     850          4

       107 Recuring                    600          5


7 rows selected.


a)Create or Replace a PL/SQL Function to Return Account Balance of a Given Customer.


SQL> set serveroutput on;

SQL> create or replace function f4

  2  (v_cname in varchar2)

  3  return number

  4  as

  5  v_count number;

  6  cursor c4 is

  7  select balance,acc_type from account,customer

  8  where account.cno=customer.cno

  9  and cname=v_cname;

 10  y c4%rowtype;

 11  begin

 12  dbms_output.put_line('Balance  ACC_Type');

 13  dbms_output.put_line('-----------------');

 14  for y in c4

 15  loop

 16  dbms_output.put_line(y.balance || '  ' || y.acc_type);

 17  v_count:=c4%rowcount;

 18  end loop;

 19  if(v_count>=1) then

 20  return v_count;

 21  else return -1;

 22  end if;

 23  end f4;

 24  /


Function created.


**CALL PROGRAM**

SQL> declare

  2  n varchar(20);

  3  r number;

  4  begin

  5  n:=&n;

  6  r:=f4(n);

  7  if(r>0) then

  8  dbms_output.put_line(r);

  9  else

 10  dbms_output.put_line('No Record Found');

 11  end if;

 12  end;

 13  /


Enter value for n: 'Raj'

old   5: n:=&n;

new   5: n:='Raj';

Balance  ACC_Type

-----------------

1000  Current

500  Saving


Enter value for n: 'Ganesh'

old   5: n:=&n;

new   5: n:='Ganesh';

Balance  ACC_Type

-----------------

800  Saving

900  Current


b)Create or Replace a Trigger That Restricts Insertion or Updation of Account Having Balance Less Than 100.


SQL> create or replace trigger t4

  2  before

  3  insert or update

  4  on account

  5  for each row

  6  begin

  7  if :new.balance<100 then

  8  raise_application_error(-20007,'Balance Should Be Greater Than 100');

  9  end if;

 10  end;

 11  /


Trigger created.


SQL> insert into account values(108,'Current',90,5);

insert into account values(108,'Current',90,5)

            *

ERROR at line 1:

ORA-20007: Balance Should Be Greater Than 100

ORA-06512: at "SYSTEM.T4", line 3

ORA-04088: error during execution of trigger 'SYSTEM.T4'