here we have a tahbe of customer :
SQL> select *from customer; AC_NO B_NO NAME CITY BALANCE COUNT CHK_DATE ---------- ---------- ---------- ---------- ---------- ---------- --------- 111 101 biku surat 7990 1 02-SEP-19 112 103 sunil vapi 11000 1 02-SEP-19 113 102 balram valsad 19000 3 01-JAN-19 114 104 jenna surat 18000 2 01-JAN-19 115 105 sagar mumbai 9000 1 01-JAN-19 116 101 chirag surat 800 3 01-JAN-19 117 102 aditya vapi 500 5 01-JAN-19 118 103 rahul valsad 14000 2 01-JAN-19 119 103 bhavesh mumbai 5000 1 01-JAN-19 120 104 jay surat 400 4 01-JAN-19 121 105 bhola vapi 11000 4 01-JAN-19 AC_NO B_NO NAME CITY BALANCE COUNT CHK_DATE ---------- ---------- ---------- ---------- ---------- ---------- --------- 122 101 sima valsad 8000 2 01-JAN-19 123 102 montu surat 9000 2 01-JAN-19 13 rows selected.
program of parameterized cursor :
declare cursor select_customer(p_min_balance number ,p_max_balance number) is select * from customer where balance between p_min_balance and p_max_balance; m_customer customer%rowtype; begin for m_customer in select_customer(&p_min_balance,&p_max_balance) loop dbms_output.put_line(m_customer.ac_no ||' '|| m_customer.name ||' '|| m_customer.balance); exit when select_customer%notfound; end loop; end; /
Result :
SQL> @C:\Users\Vibhuti\cursor.sql Enter value for p_min_balance: 1000 Enter value for p_max_balance: 20000 111 biku 7990 112 sunil 11000 113 balram 19000 114 jenna 18000 115 sagar 9000 118 rahul 14000 119 bhavesh 5000 121 bhola 11000 122 sima 8000 123 montu 9000 PL/SQL procedure successfully completed.
If you like code with vibhu and would like to contribute, you can also write an article using This link :
https://docs.google.com/forms/d/e/1FAIpQLScAmvlPvNUz35R-G0nc_zpRVP3o8xlhtFgC3aKPyLetX_RyXg/viewform?pli=1
OR mail your article to codewithvibhu@gmail.com. See your article appearing on the code with vibhu main page and help other students.
0 Comments