Embedded SQL/Oracle Tutorial 3

Cursors

By now, if you have been following the tutorials closely, you should be quite familiar with inserting, updating, and deleting database records. The next step is to create querying functions (i.e., to handle SELECT operations).  We have intentionally left querying until last because there often are more steps to perform. Unlike the format of queries we typed into SQL*Plus, embedded SQL requires the use of cursors to successfully output the results of the query. Cursors were invented to satisfy both the SQL and host programming languages. SQL queries handle sets of rows at a time, while C++, for example, handles only one row at a time. When we type the following SQL query into SQL*Plus:
 

SQL> select    driver_sin, count(exam_score)
   2 from      exam
   3 where     exam_type = 'L'
   4 group by  driver_sin;

we get the following output:

DRIVER_SIN COUNT(EXAM_SCORE)
---------- -----------------
 111111111                 1
 222222222                 2
 333333333                 3
 444444444                 1

In our embedded SQL code, we cannot simply specify:
 

EXEC SQL SELECT    driver_sin, count(exam_score)
         FROM      exam
         WHERE     exam_type = 'L'
         GROUP BY  driver_sin;

and expect C++ to output the results of the query. We have to fetch the results of this query into a cursor, and then output the results one at a time using C.
To use a cursor in embedded SQL, we must first declare it. We do this by using the DECLARE keyword, with the following syntax:
 

EXEC SQL DECLARE <cursor name> CURSOR FOR
SELECT ... FROM ...;

where the SELECT part of the statement specifies the query. Note that the above statement is only a declaration and the SELECT itself has not been executed yet. The declaration must occur before it is used. The scope of a cursor is the entire Pro*C++ program, but cursor statements (DECLARE, OPEN, FETCH, and CLOSE) must occur within the same precompiled unit. Therefore, for the entire program, each <cursor name> must be unique.
Once a cursor is declared, we have to open it in order to execute the query. To do this, we use the OPEN keyword, as follows:

 
EXEC SQL OPEN <cursor name>;

When we first open a cursor, it points to just before the first row (of the result). To retrieve rows (one at a time) which satisfy the SELECT query, we need to use the FETCH keyword. The syntax of the FETCH statement is:
 

EXEC SQL FETCH <cursor name> INTO :hostvar1, :hostvar2, ...;

Note that we have to first declare and open the cursor with cursor name before being able to use it in a FETCH statement.
After executing the FETCH statement, the cursor is set to point to the beginning of the next row of the answer set. When all rows have been fetched, sqlcode is set to 100 or 1403. Acknowledging this, we can write simple while loops which continuously fetch and print out tuple values for each row by testing sqlcode for the values 100 and 1403. You will see this in the example given below.
After all rows have been fetched, you can close the cursor with the command:
 

EXEC SQL CLOSE <cursor name>

A cursor can always be reused, so if you want to reuse your cursor, all you have to do is reopen it. The FETCH statement only moves forward in tables, so you might want to reopen a cursor to revisit and fetch previous rows in a table.
 
 

Sample Program

You should know enough about cursors by now to complete any homework involving embedded SQL.  Here is the Pro*C++ source code for maintaining the branch relation. In particular, note the subroutine called Show_Branch() which shows information for all branches.

#include <iostream.h>
#include <stdlib.h>                       // needed for atoi()
#include <stdio.h>                        // needed for gets()
#include <string.h>
#include <unistd.h>                       // needed for getpassphrase()
#include <iomanip.h>                      // needed for setw()

#define MAXBUF 50                         // maximum length of buffer

char line[MAXBUF];                        // buffer to hold stdin

EXEC SQL INCLUDE sqlca;                   // declarations for error checking

EXEC SQL WHENEVER SQLERROR    DO  print_error();
EXEC SQL WHENEVER SQLWARNING  DO  print_warning();
EXEC SQL WHENEVER NOTFOUND    DO  print_not_found();

void print_error()
{
  // display the error message returned by Oracle
  cout << "\n!! Unsuccessful operation.  Error code: " << sqlca.sqlcode;
  cout << "\n   Oracle Message: " << sqlca.sqlerrm.sqlerrmc << "\n";
}

void print_warning()
{
  // display the warning message returned by Oracle
  cout << "\n!! A warning occurred.   Error code: " << sqlca.sqlcode;
  cout << "\n   Oracle Message: " << sqlca.sqlerrm.sqlerrmc << "\n";
}

void print_not_found()
{
  // display the "row not found" message returned by Oracle
  cout << "\n!! Warning.  Row not found.  Error code: " << sqlca.sqlcode;
  cout << "\n   Oracle Message: " << sqlca.sqlerrm.sqlerrmc << "\n";
}

void Connect()
{
    // connect to database
    EXEC SQL BEGIN DECLARE SECTION;
        char userid[64];
        char password[64];
        char *DBname = "@ug";
    EXEC SQL END DECLARE SECTION;

    cout << "\nUsername: ";
    gets(userid);
    strcat(userid, DBname);
    strcpy(password, getpassphrase("Password: "));

    EXEC SQL CONNECT :userid IDENTIFIED BY :password;
}

void Insert_Branch()
{
  // Insert a tuple into the branch relation
  EXEC SQL BEGIN DECLARE SECTION;
    int        bid;
    VARCHAR    bname[20];
    VARCHAR    baddr[50];
    VARCHAR    bcity[20];
    int        bphone;
    short int  baddr_ind;
    short int  bphone_ind;
  EXEC SQL END DECLARE SECTION;

  cout << "\nBranch ID: ";
  gets(line);
  bid = atoi(line);

  cout << "\nBranch Name: ";
  gets(line);
  bname.len = strlen(line);
  strncpy((char *) bname.arr, line, bname.len);

  cout << "\nBranch Address: ";
  gets(line);
  baddr.len = strlen(line);
  strncpy((char *) baddr.arr, line, baddr.len);

  cout << "\nBranch City: ";
  gets(line);
  bcity.len = strlen(line);
  strncpy((char *) bcity.arr, line, bcity.len);

  cout << "\nBranch Phone: ";
  gets(line);
  if (strlen(line) != 0)
     bphone = atoi(line);         // phone number is not null
  else
     bphone_ind = -1;             // phone number is null;  set indicator
   baddr_ind = 0;

  EXEC SQL INSERT
           INTO    branch (branch_id, branch_name, branch_addr, branch_city,
                           branch_phone)
           VALUES (:bid, :bname, :baddr:baddr_ind, :bcity, :bphone:bphone_ind);

  //  The WHENEVER statement will handle the error processing, but
  //  to show the sequence of error messages, let's add the following.

  if (sqlca.sqlcode < 0)
     cout << "An error was detected.  The details are described above.\n";

  EXEC SQL COMMIT WORK;
}

void Delete_Branch()
{
  // Delete a tuple from the branch relation, given the branch id
  EXEC SQL BEGIN DECLARE SECTION;
    int  bid;
  EXEC SQL END DECLARE SECTION;

  cout << "Branch ID: ";
  gets(line);
  bid = atoi(line);

  EXEC SQL DELETE
           FROM   branch
           WHERE  branch_id = :bid;

  EXEC SQL COMMIT WORK;
}

void Update_Branch()
{
  // Update the branch name, given the branch id
  EXEC SQL BEGIN DECLARE SECTION;
    int      bid;
    VARCHAR  bname[20];
  EXEC SQL END DECLARE SECTION;

  cout << "Branch ID: ";
  gets(line);
  bid = atoi(line);

  cout << "New Branch Name: ";
  gets(line);
  bname.len = strlen(line);
  strncpy((char *) bname.arr, line, bname.len);

  EXEC SQL UPDATE branch
           SET    branch_name = :bname
           WHERE  branch_id = :bid;

  EXEC SQL COMMIT WORK;
}

void Show_Branch()
{
  // Display information about branches
  EXEC SQL BEGIN DECLARE SECTION;
    int        bid;
    VARCHAR    bname[20];
    VARCHAR    baddr[50];
    VARCHAR    bcity[20];
    int        bphone;
    short int  baddr_ind;
    short int  bphone_ind;
  EXEC SQL END DECLARE SECTION;

  EXEC SQL DECLARE branch_info CURSOR FOR
           SELECT * FROM BRANCH;

  EXEC SQL OPEN branch_info;

  EXEC SQL FETCH branch_info
           INTO  :bid, :bname, :baddr:baddr_ind, :bcity, :bphone:bphone_ind;

  cout << setiosflags(ios::left);       // left justify the names to come
  cout << setw(10) << "ID"   << setw(15) << "NAME"  << setw(15) << "ADDRESS"
       << setw(15) << "CITY" << setw(15) << "PHONE" << "\n";
  cout << "--------------------------------------------------------------\n";

  while (sqlca.sqlcode >= 0  &&  sqlca.sqlcode != 100  &&
         sqlca.sqlcode != 1403)
   {
     bname.arr[bname.len] = '\0';       // null terminates the VARCHARs
     baddr.arr[baddr.len] = '\0';
     bcity.arr[bcity.len] = '\0';

     // display results;  keep the columns aligned reasonably well
     cout << setw(10) << bid       << setw(15) << bname.arr
          << setw(15) << baddr.arr << setw(15) << bcity.arr << setw(15);
     if (bphone_ind != -1)              // display phone number, if not null
        cout << bphone;
     else
        cout << " ";
     cout << "\n";

     EXEC SQL FETCH branch_info
              INTO  :bid, :bname, :baddr:baddr_ind, :bcity, :bphone:bphone_ind;
   }

  cout << "The last warning just signifies that the cursor fetched the "
       << "final record\n";

  EXEC SQL CLOSE branch_info;

  EXEC SQL COMMIT WORK;
}

int main()
{
  // simple text interface for above functions

  int  choice, quit;

  Connect();                        // connect to Oracle

  quit = 0;
  while (!quit)
    {
      cout << "\nPlease choose one of the following: \n";
      cout << "1. Insert branch\n";
      cout << "2. Delete branch\n";
      cout << "3. Update branch\n";
      cout << "4. Show   branch\n";
      cout << "5. Quit\n>> ";

      gets(line);
      choice = atoi(line);

      printf("\n\n");

      switch (choice)
        {
          case 1:  Insert_Branch();
                   break;
          case 2:  Delete_Branch();
                   break;
          case 3:  Update_Branch();
                   break;
          case 4:  Show_Branch();
                   break;
          case 5:  quit = 1;
          default: exit(0);
        }
     }

  EXEC SQL COMMIT WORK RELEASE;     // Commit and free any locks held.

  // Any additional non-SQL/non-Oracle work can go here.

}

Compile and run the code. You can now test modifications (insert, update, and delete) to the branch relation without having to start up an SQL*Plus session. Although our example tested sqlca.sqlcode for the values 100 and 1403 in the Show_Branch() function, we could have used error trapping instead and done something like this:
 

EXEC SQL WHENEVER NOTFOUND DO BREAK;
while(1)  {
. . .
EXEC SQL FETCH branch_info
         INTO  :bid, :bname, :baddr:baddr_ind, :bcity, :bphone:bphone_ind;
. . .
}
/* restore WHENEVER NOTFOUND to what we had before */
EXEC SQL WHENEVER NOTFOUND DO print_not_found();

http://www.google.co.id/url?sa=t&rct=j&q=&esrc=s&source=web&cd=6&ved=0CEoQFjAF&url=http%3A%2F%2Fwww.angelfire.com%2Ffolk%2Fanoop%2FSQL.pdf&ei=-uxmULHZIs7IrQfL2IDwDg&usg=AFQjCNFWXSaUVNhnKVCSNJgd4F19Gp1Tbg&sig2=l_VtZuZQ115aNJwNCgtDLQ&cad=rja

Artikel terkait :