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

16komentar:

  1. Hello, U write some extraordinarily scr888 slot game download malaysia attractive blogs. I always check back here frequently to see if you have updated

    BalasHapus
  2. It was another joy to see your mega888 apk download 2019 post. It is such an important topic and ignored by so many, even professionals. I thank you to help making people more aware of possible issues. Great stuff as usual...

    BalasHapus
  3. Can you please provide more information scr888 apk download android 2019 on this subject? BTW your blog is great. Cheers.

    BalasHapus
  4. You have a great blog - I would Rapid Slim Keto think your readership is very high?

    BalasHapus
  5. Good information scr888 apk download android 2019 and great post.  I like the scr888 apps website, and am sure to keep returning.
    your scr888 apk free download site layout is very good

    BalasHapus
  6. I was very 918kiss apk download918kiss download android 2019 to thank you for this great read!! I definitely enjoying every little 918kiss apk download 2019 bit of it and I have you bookmarked to check out new stuff you post.

    BalasHapus
  7. Spot on with this article, I really joker123 download apk
    think this website needs more attention. I'll probably be back to read more, thanks for the info.

    BalasHapus
  8. The blog article very surprised to me! Your writing is good. In this I learned a lot! Thank you!
    https://www.sendspace.com/file/co41c9

    BalasHapus
  9. Ultra Fast Keto Boost frequently, weight reduction additionally ends up enhancing the fees of triglycerides and HDL.
    To avoid setting your health at chance, goal the most loss of four hundred-600 grams in keeping with week.
    - exercises
    https://purefitketodietplan.com/ultra-fast-keto-boost/

    BalasHapus
  10. Milk is a good source of protein, in addition to Go ketogenic providing us with calcium, an essential mineral for the growth and maintenance of bone tissue and also responsible for reducing cardiovascular diseases.
    https://goketoganic.com/

    BalasHapus
  11. Marketing strategy is actually a comprehensive, long-term, proactive approach and a game plan of any business or company with the ultimate objective of reaching a competitive edge by knowing the desires and needs of consumers. Marketing Strategy is the art and science of knowing your audience, understanding their needs and maximizing the use of available resources to reach them. It is about the development of an understanding between you and the customer that lead to a win-win situation for both of you. click for more...

    BalasHapus
  12. A debt of gratitude is in order for a pleasant post. I incredibly love it. It's so acceptable thus marvelous Satta Matka or essentially Matka is Indian Form of Lottery. Matkaoffice.net is world's quickest matka official site. Welcome to top matka world sattamatka, kalyan.

    BalasHapus
  13. There are unmistakable sure notes for IQ Derma things. A couple of reviewers offered the things are startling and utilized keto diet healthy nothing inside and out that in fact matters, uncertain from IQ Derma. A couple of ladies say this link has the best things for wrinkles, dark circles, and puffiness. different clients say their skin feels milder and sun hurt was diminished. The more you utilize the things the more excellent outcomes you should see.

    BalasHapus
  14. Finding a designer or interior decorator can be a daunting task if you're not sure which designer you need for your project or project. Are you building, renovating interior designing courses online free or relocating and need professional advice? Planning to sell your property and don't know how to prepare for your first inspection?

    BalasHapus
  15. From lashes to lips, SELF has put together some of the best beauty tips, trends, advice & product reviews for women. https://deluxwire.com/category/beauty/

    BalasHapus