How A Cursor Works?

Open:

• Open the cursor, to execute query and identify active set.
• If the query returns no rows, no exception is raised.
• Use cursor attributes to test the outcome after a fetch.

Fetch:

• To retrieve the current row values into variables.
• Include the same number of variables.
• Match each variable to correspond to columns, by position.
• Test to see if cursor contains rows.

Close:

• Close cursor after completing processing-rows. Session scope.
• Reopen cursor, if required.
• Do not attempt to fetch data from cursor once closed.

Character and Date Values with Substitution Variables:

Use single quotation marks for date and character values.

In a WHERE clause, date and character values must be enclosed within single quotation marks. The same rule applies to the substitution variables. 

Enclose the variable in single quotation marks within the SQL statement itself.

Note: You can also use functions such as UPPER and LOWER with the ampersand. Use UPPER(’&job’) so that the user does not have to enter the job title in uppercase.  See examples.

SQL> select ename,deptno,sal*12 from emp where job='&job_title';

Enter value for job_title: analyst

old   1: select ename,deptno,sal*12 from emp where job='&job_title'

new   1: select ename,deptno,sal*12 from emp where job='analyst'

no rows selected

SQL> select ename,deptno,sal*12 from emp where job='&job_title';

Enter value for job_title: ANALYST

old   1: select ename,deptno,sal*12 from emp where job='&job_title'

new   1: select ename,deptno,sal*12 from emp where job='ANALYST'

ENAME         DEPTNO    SAL*12

---------- --------- ---------

SCOTT             20     36000

FORD              20     36000

SQL> select ename,deptno,sal*12 from emp where job=UPPER('&job_title');

Enter value for job_title: analyst

old   1: select ename,deptno,sal*12 from emp where job=UPPER('&job_title')

new   1: select ename,deptno,sal*12 from emp where job=UPPER('analyst')

ENAME         DEPTNO    SAL*12

---------- --------- ---------

SCOTT             20     36000

FORD              20     36000