Procedure Example using cursor

 

Problem Statement : 

1. Create a procedure using cursor that will show the names of all banks and providers that are using these bank and count how many providers are for each bank.

For example,

Transilvania BANK

_________

ProfessionalCioco

Fidesco

2 providers

Diagram : 

Procedure Script : 

---create the procedure  

CREATE OR replace PROCEDURE pr_get_provdr_name (i_bank_name in bank.name%TYPE)  IS

--declaration 

    v_swift   bank.swift%TYPE;

    v_cntr    NUMBER(10) := 0;

    

    CURSOR cur_provider_name(c_swift bank.swift%TYPE)

    is 

    SELECT  name

    FROM    provider p, provider_account   pa

    WHERE         p.cui = pa.cui

    AND pa.swift = c_swift;

---excution 

BEGIN  

---checking for bank name  

    SELECT swift

    INTO v_swift

    FROM  bank b

    WHERE b.name = i_bank_name;

    

    dbms_output.put_line('Bank Name:-' || i_bank_name);

    dbms_output.put_line('-------Provider Name----------------');

    

  ---For loop for provider name   

FOR rec_provider_name IN cur_provider_name(v_swift) loop

    dbms_output.put_line (rec_provider_name.name); 

---to count the provider number 

    v_cntr := v_cntr + 1;

end loop;

    dbms_output.put_line(v_cntr || ' providers');

    

 --exception    

EXCEPTION

    WHEN no_data_found THEN

        dbms_output.put_line('No Data Found against the given bank name'); 

    WHEN OTHERS THEN

        dbms_output.put_line(sqlerrm);

END; 

Calling the procedure  : 

For explanation watch the video

I am interested in hearing your feedback about this article , So that I can improve my lab series on sql for you.connect with us on facebook, YouTube

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: