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
