c - Oracle Pro*C — 在嵌套循环中使用游标

Question

我正在自学 Pro*C，并且有一个程序(据说)用游标遍历数据库中的记录，并且它可以编译并运行。问题是，当变量打印时(那些使用光标读取的变量)，我得到了内存中的任何垃圾。

我尝试用几种不同的方式拆分 sql exec 语句，但这没有帮助。还尝试在不同的地方打开和关闭sql，也没有帮助。我真的已经结束了漫长的调试过程，并且我很确定我正在犯一个极其新手的错误。如果这里的任何 Oracle 程序员不介意花一点时间，我真的很想得到一些关于如何回到正轨的反馈。

它应该打印:

Enter a Guest_ID(type 0 to terminate)>>

1

Charge Summary for: Firstname Lastname Guest-ID: 1

Sales_Item: 1 – Room (Taxable)

Hotel-Id Hotel-Name Trans-Date Quantity Unit-Price Extended-Price

Hotel-Id Hotel-Name Trans-Date Quantity Unit-Price Extended-Price

Hotel-Id Hotel-Name Trans-Date Quantity Unit-Price Extended-Price

Sales Item Total Quantity Extended-Price

它实际上打印:

Enter a Guest_ID(type 0 to terminate)>>

3

Charge Summary for: l▒   Guest_ID: 3

我觉得我完全搞乱了光标，但我无法准确指出问题出在哪里，因为我仍在习惯 Pro*C 中如何声明和使用变量。另外，C 程序通常需要调试，但这是在远程服务器上运行的，调试非常有限，甚至没有 dbx 命令。

代码:

#include<stdio.h>
#include<string.h>
#include<stdlib.h>
exec sql include sqlca;

// OK - Here we GO
void main()
{
    // First, create all the variables that we will need to communicate between 
    // the "C" program and the database
    exec sql begin declare section;
        //VARCHAR sLastName[51], sFirstName[51], sHotelName[51], sCheckInDate[12], sRoom[11];
        VARCHAR sLastName[51], sFirstName[51], sHotelName[51], sTransDate[11];
        //int nDays, nGuest_ID, nCount;
        int nGuest_ID, nQuantity, nUnitPrice, nCount, nHotelID, nItemID;
        //VARCHAR sInCity[11];
        VARCHAR sItemName[31], sTaxable[11];
        VARCHAR sUserID[21], sPassword[21];
    exec sql end declare section;

        // Now define the cursor we will use to get all of the charges that the guest incurred at all hotels
    exec sql declare dbGuest cursor for
        Select G.Guest_ID, G.Last_Name, G.First_Name, C.Item_ID, C.Item_Name, C.Quantity, C.Unit_Price, C.Trans_Date, H.Hotel_Name, H.Hotel_ID, SI.Taxable
        From Hotel H, Charge C, Stay S, Guest G, Sales_Item SI Where
        C.Stay_ID=S.Stay_ID And H.Hotel_ID=S.Hotel_ID And G.Guest_ID=S.Guest_ID
            And SI.Item_ID=C.Item_ID
        Group By S.Guest_ID;

    // Set up the user-id and password to access my database
    // Because we are using the local database on this server
    // we don't need to use any database location or SID
    strcpy(sUserID.arr,"myuserid"); 
    strcpy(sPassword.arr,"mypassword");  
    sUserID.len=strlen(sUserID.arr);
    sPassword.len=strlen(sPassword.arr);
    exec sql connect :sUserID identified by :sPassword;

    // sqlca.sqlcode is a variable that is set based on the last command sent in to the database
    // a value anything other than zero for what we just did (connect to the database) indicates
    // a error.
    if(sqlca.sqlcode !=0)
       {
        //printf("Sorry, cannot connect to server, pgm aborted %s\n",sqlca.sqlcode); //correction 2/5/14
        printf("Sorry, cannot connect to server, pgm aborted %d\n",sqlca.sqlcode); //change to %d
        exit(1);
       }
    //we made it here, so we were able to open the database correctly
    exec sql SELECT COUNT(*) INTO :nCount FROM Guest;
    printf ("There are %d Guests.\n",nCount);
    for(;;){
        // Read in through stdio the Guest we want to query, then set it up do we can use it
        printf("Enter a Guest_ID(type 0 to terminate)>>\n");
        scanf("%d",&nGuest_ID);
        //Guest_ID.len= strlen(Guest_ID.arr);
        if(nGuest_ID==0)
        {
            printf("BYE\n");
            exit(0);
        }
        printf("%s %s %s %s %d\n","Charge Summary for:", sFirstName.arr, sLastName.arr, " Guest_ID:", nGuest_ID);
        //printf("I do not work yet (type exit to terminate)>>\n");
                // Open our cursor and begin reading records
        exec sql open dbGuest;
        for(;;)
        {
            //exec sql fetch dbGuest into :nGuest_ID, :sLastName, :sFirstName, :sHotelName, :sCheckInDate, :nDays, :sRoom;
            exec sql fetch dbGuest into :sLastName, :sFirstName, :nItemID, :sItemName, :nQuantity, :nUnitPrice, :sTransDate, :sHotelName, :nHotelID;
            if(sqlca.sqlcode !=0)  // If anything went wrong or we read past eof, stop the loop
            {
                break;
            }
            printf("%s %s %s %s %d\n","Charge Summary for:", sFirstName.arr, sLastName.arr, " Guest_ID:", nGuest_ID);
            // Do the crazy stuff to end the C-Strings
            sLastName.arr[sLastName.len] = 0;
            sFirstName.arr[sFirstName.len] = 0;
            sItemName.arr[sItemName.len] = 0;
            sTransDate.arr[sTransDate.len] = 0;
            sHotelName.arr[sHotelName.len] = 0;

            // Print out the information for this guest
            printf("%s %d %s %s \n", "Sales_Item: ", nItemID, " - ", sItemName.arr);

            printf("%d %s %s %d %d \n", nHotelID, " ", sHotelName.arr, " ",sTransDate.arr, " ", nQuantity, " ", nUnitPrice);
        }
        // close the cursor and end the program
        exec sql close dbGuest ;
    }
    exit(0);
}

通常 C 程序会在调试器中运行，但这是 ProC，我对整个 Oracle ProC 调试有点迷失(因为它在远程数据库上运行)。 p>

经历过这些但没有帮助:

Strange behaviours with oracle nested cursors

Oracle ProC INSERT INTO VALUES ( (select ...) )

Oracle Pro*C updating table with cursor failed

有人告诉我 VARCHAR 变量应该以不同的方式声明，但其他方式似乎会引发错误。

Answer 1

甚至在将值提取到 sFirstName 之前，您就可以打印它们。首先，由于您没有初始化它，它会打印垃圾值。另外，如果您感觉游标获取被中断，请在“中断”循环之前使用 sqlca 的 sqlerrm 打印错误消息。就像sqlca.sqlerrm.sqlerrmc

然后，您的 OPEN CURSOR 调用将会失败，因为查询存在语法错误。因此，您需要如下修改光标，或者正确修改查询。

在继续之前，我们必须检查OPEN游标的状态，否则FETCH将再次失败，并且结果可能是不可预测的。因此，请在每次 EXEC SQL 调用后检查 sqlca.sqlcode。

此外，我们需要处理NULL，我们可以在查询中使用NVL()，if Indicator variables没有使用过

   exec sql declare dbGuest cursor for
        Select G.Guest_ID,
               G.Last_Name,
               G.First_Name,
               C.Item_ID,
               C.Item_Name,
               C.Quantity,
               C.Unit_Price,
               C.Trans_Date,
               H.Hotel_Name,
               H.Hotel_ID,
               SI.Taxable
        From Hotel H, Charge C, Stay S, Guest G, Sales_Item SI
        Where C.Stay_ID=S.Stay_ID 
          And H.Hotel_ID=S.Hotel_ID 
          And G.Guest_ID=S.Guest_ID
          And SI.Item_ID=C.Item_ID;

非聚合列只能与聚合函数一起使用。因此，要么删除分组，要么将 MAX() 添加到其他列。

将以下内容添加到您的声明中

int temp_sales_id = -999;
int first_iter = 1;
int total_nQuantity = 0;
float total_nUnitPrice = 0.0;

那么，

exec sql open dbGuest;

    /* Lets check the status of the OPEN statement before proceeding , else exceptions would be suppressed */
    if(sqlca.sqlcode !=0)  // If anything went wrong or we read past eof, stop the loop
     {
        printf("Error while opening Cursor <%d><%s>\n",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);
        break;
     }

for(;;)
    {
        //exec sql fetch dbGuest into :nGuest_ID, :sLastName, :sFirstName, :sHotelName, :sCheckInDate, :nDays, :sRoom;
        exec sql fetch dbGuest into :sLastName, :sFirstName, :nItemID, :sItemName, :nQuantity, :nUnitPrice, :sTransDate, :sHotelName, :nHotelID;

        /* Check for No DATA FOUND */
        if(sqlca.sqlcode == 100 || sqlca.sqlcode == 1403)  // If anything went wrong or we read past eof, stop the loop
        {
            printf("CURSOR is empty after all fetch");
            break;
        }
        /* Check for other errors */
        else if(sqlca.sqlcode != 0)
        {
             printf("Error while fetching from Cursor <%d><%s>\n",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);
             break;
        }

        if(first_iter) {
           printf("%s %s %s %s %d\n","Charge Summary for:", sFirstName.arr, sLastName.arr, " Guest_ID:", nGuest_ID);
           first_iter = 0;
        }
        // Do the crazy stuff to end the C-Strings
        sLastName.arr[sLastName.len] = 0;
        sFirstName.arr[sFirstName.len] = 0;
        sItemName.arr[sItemName.len] = 0;
        sTransDate.arr[sTransDate.len] = 0;
        sHotelName.arr[sHotelName.len] = 0;

        if(temp_sales_id == -999 || temp_sales_id != nItemID)
        {
          /* First Item or Sales Item has Changed (next sales id)*/
          temp_sales_id = nItemID;
          // Print out the information for this guest
          printf("%s %d %s %s \n", "Sales_Item: ", nItemID, " - ", sItemName.arr);

          printf("%d %s %s %d %d \n", nHotelID, " ", sHotelName.arr, " ",sTransDate.arr, " ", nQuantity, " ", nUnitPrice);

          total_nQuantity += nQuantity;
          total_nUnitPrice += nUnitPrice;
        }
        if (temp_sales_id != nItemID) {
            /* Printing total for Current Sale id */
            /* If you want to Sum all the sale id together take this finally */
            printf("Total Quantity <%d> Total Extended Price <%g>\n",total_nQuantity,total_nUnitPrice);
            total_nUnitPrice = 0;
            total_nQuantity = 0;
        }

        if(temp_sales_id == -999 || temp_sales_id == nItemID) {
          printf("%d %s %s %d %d \n", nHotelID, " ", sHotelName.arr, " ",sTransDate.arr, " ", nQuantity, " ", nUnitPrice);
        }
    }
    // close the cursor and end the program
    exec sql close dbGuest ;