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 facebookYouTube

Share

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

No comments:

Post a Comment