Previously we learnt that in PL/SQL we are allowed to create a cursor with parameter known as Cursor Parameter (a.k.a. Parameterized Cursor). Every cursor parameter has some restrictions which we have to comply with for example we have to specify the argument for each parameter when we open the cursor otherwise we get a PLS 00306 error.
To overcome this restriction, we have the option called default value. Default value is the value which you assign to the parameter of your cursor during the declaration of the cursor.
As you can see in the above code I assigned a value (which is 190) using assignment operator to the parameter var_e_id in the cursor declaration and nothing while opening the cursor. Upon the execution this PL/SQL block will show you the first name and employee id of all the employees who have employee id greater than 190.
For line by line explanation of the above code please watch the Video Tutorial on my YouTube.
If you compare the result of both the codes demonstrated in the above examples you will see the difference because the result returned by Example 1 is according to the default value (which is 190) which you specified during the declaration while the result returned by Example 2 will be according to the argument (which is 200) you have specified in the OPEN cursor statement.
Hope you enjoyed reading this blog. Please do share this on your Facebook or Twitter and help your friends in learning new concepts. From now on you can win a goody bag on sharing the blogs, just make sure to tag me.
Have a great day!
You can DOWNLOAD SQL script and presentation used in the Video and in this article.