parameterized cursor in pl/sql.

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. 

❮ Previous                                                      Next ❯