To make use of this lesson, you must create the pasta table and populate it.

Using BIND VARIABLES.... what the heck is that all about?

If you read the performance tuning book, it says "USE BIND VARIABLES"
at least 20 times. Does the book ever tell you what they are? No. The following 
is a demonstration of what they are:

Imagine you have the following query:

Select cheese, tomato, salt, oregano, olive_oil from pasta where cheese = 238;

You find that you will be asking this exact query 10,000 times per day, 
and the only thing that will change is the number for cheese in the where clause. 
This is an ideal situation for bind variables, because you can quickly 
write this query into a stored procedure that asks the exact same thing
but replaces the number at the end with a variable, like so:

Select cheese, tomato, salt, oregano, olive_oil from pasta where cheese = :aa;

The parsing of this statement will now be the same no matter what value
we substitute for :aa. 

Now lets take a look at this in action:

= = = =
create or replace procedure select_pasta(cheeseVAR number)
is
c             number; -- My cursor!
n             number;
sqlStatement  varchar2(400);
buildSTRING   varchar2(200);

colCHEESE     NUMBER(4);
colTOMATO     VARCHAR2(20);
colSALT       VARCHAR2(1);
colOREGANO    VARCHAR2(20);
colOLIVE_OIL  VARCHAR2(40);

BEGIN
sqlStatement := 'Select cheese, tomato, salt, oregano, olive_oil 
                 from pasta where cheese = :aa';

-- opening the cursor sets a spot in memory to work with.
   c := dbms_sql.open_cursor;

-- parse the statement in the cursor location
   dbms_sql.parse(c, sqlStatement, dbms_sql.native);

-- Notice how we bind a value to the variable AFTER the parse.
-- That means the parsed structure never changes and can be reused 
-- with ANY value given for the bind variable in another query!

   DBMS_sql.bind_variable(c, 'aa', cheeseVAR);

   DBMS_sql.define_column(c, 1, colCHEESE); --(cursor, column#, variable)
   DBMS_sql.define_column(c, 2, colTOMATO, 20); --(cursor, column#, variable, length)
   DBMS_sql.define_column(c, 3, colSALT, 1);
   DBMS_sql.define_column(c, 4, colOREGANO, 20);
   DBMS_sql.define_column(c, 5, colOLIVE_OIL, 40);
   n := dbms_sql.execute(c);

    loop
      exit when dbms_sql.fetch_rows(c) = 0;
      dbms_sql.column_value(c,1, colCHEESE);
      dbms_sql.column_value(c,2, colTOMATO);
      dbms_sql.column_value(c,3, colSALT);
      dbms_sql.column_value(c,4, colOREGANO);
      dbms_sql.column_value(c,5, colOLIVE_OIL);

buildstring := rpad(colCHEESE,5,' ')   ||
               rpad(colTOMATO,21,' ')  ||
               rpad(colSALT,2,' ')     ||
               rpad(colOREGANO,21,' ') ||
                    colOLIVE_OIL;

dbms_output.put_line(buildstring);

    end loop;

   dbms_sql.close_cursor(c);
END select_pasta;
/


----------------------------------------------------------------
Now, you can run this procedure from the command line instead of typing the query.
Here is an example of how to run this procedure from the command line:

SQL> exec select_pasta(876);

There is another way to build this kind of procedure WITHOUT using the DBMS_SQL.BIND_VARIABLE
procedure. Here is an example. It involves building the query in a string. I do not recommend
this method because it will not utilize the library cache efficiently. Nevertheless, you 
should be familiar with it in case you see it in the workplace:

Create or replace procedure select_pasta_str(cheeseVAR number)
is
c             number; -- My cursor!
n             number;
sqlStatement  varchar2(400);
buildSTRING   varchar2(200);

colCHEESE     NUMBER(4);
colTOMATO     VARCHAR2(20);
colSALT       VARCHAR2(1);
colOREGANO    VARCHAR2(20);
colOLIVE_OIL  VARCHAR2(40);

BEGIN 
sqlStatement := 'Select cheese, tomato, salt, oregano, olive_oil 
                 from pasta where cheese = ' || cheeseVAR;

-- opening the cursor sets a spot in memory to work with.
   c := dbms_sql.open_cursor;  

-- parse the statement in the cursor location
   dbms_sql.parse(c, sqlStatement, dbms_sql.native); 


   DBMS_sql.define_column(c, 1, colCHEESE); --(cursor, column#, variable)
   DBMS_sql.define_column(c, 2, colTOMATO, 20); --(cursor, column#, variable, length)
   DBMS_sql.define_column(c, 3, colSALT, 1);
   DBMS_sql.define_column(c, 4, colOREGANO, 20);
   DBMS_sql.define_column(c, 5, colOLIVE_OIL, 40);
   n := dbms_sql.execute(c);

    loop
      exit when dbms_sql.fetch_rows(c) = 0;
      dbms_sql.column_value(c,1, colCHEESE);
      dbms_sql.column_value(c,2, colTOMATO);
      dbms_sql.column_value(c,3, colSALT);
      dbms_sql.column_value(c,4, colOREGANO);
      dbms_sql.column_value(c,5, colOLIVE_OIL);

buildstring := rpad(colCHEESE,5,' ')   || 
               rpad(colTOMATO,21,' ')  ||
               rpad(colSALT,2,' ')     ||
               rpad(colOREGANO,21,' ') ||
                    colOLIVE_OIL;

dbms_output.put_line(buildstring);

    end loop;

   dbms_sql.close_cursor(c); 
END select_pasta_str; 
/

Notice how the two procedures select_pasta() and select_pasta_str() do exactly the same thing.
In fact, the only difference is in the way the query string is constructed. If you are going to 
make procedures that query the database, it is a good idea to use the first method because
the SAME parsed query will reside in memory for ALL queries regardless of the value for 
CHEESE in the where clause!

= = = =
Can I use BIND_VARIABLEs for inserts, updates, and deletes? Yes, here is an example of an insert:

create or replace PROCEDURE insert_pasta(cheeseVAR number, tomatoVAR varchar2, 
                                         saltVAR varchar2, oreganoVAR varchar2, 
                                         olive_oilVAR varchar2)
   IS
   c number;
   n number;
   cantgo exception;
BEGIN
if cheeseVAR is NULL then
 raise cantgo;
end if;

if cheeseVAR > 9999 then
 raise cantgo;
end if;
   c:= dbms_sql.open_cursor;
   dbms_sql.parse(c, 'insert into PASTA (cheese, tomato, salt, oregano, olive_oil) 
                      values ( :aa, :bb, :cc, :dd, :ee )', dbms_sql.native);
   dbms_sql.bind_variable(c, 'aa', cheeseVAR);
   dbms_sql.bind_variable(c, 'bb', tomatoVAR);
   dbms_sql.bind_variable(c, 'cc', saltVAR);
   dbms_sql.bind_variable(c, 'dd', oreganoVAR);
   dbms_sql.bind_variable(c, 'ee', olive_oilVAR);
   n := dbms_sql.execute(c);
   dbms_sql.close_cursor(c);
   commit;

   dbms_output.put_line('Your insert into the pasta table has been committed');   

exception
    when cantgo then
      dbms_output.put_line('CHEESE MUST BE AN INTEGER FROM 0 TO 9999');

END insert_pasta;
/