Oracle pro*c/c++ 动态sql

最新推荐文章于 2021-05-23 02:54:53 发布
最新推荐文章于 2021-05-23 02:54:53 发布
阅读量302 收藏 2
点赞数
分类专栏： orcle 文章标签： oracle
原文链接：https://blog.csdn.net/lzjsqn/article/details/54352861?locationNum=8&fps=1
版权

ANSI

makefile

dm02_ansi4:
@echo ‘dm02_ansi4.pc start…’
proc iname=dm02_ansi4.pc oname=dm02_ansi4.c sqlcheck=full mode=ansi
@echo ‘dm02_ansi4.pc ok…’
gcc dm02_ansi4.c -o dm02_ansi4 -I O R A C L E H O M E / p r e c o m p / p u b l i c − L {ORACLE_HOME}/precomp/public -L ORACLEH​OME/precomp/public−L{ORACLE_HOME}/lib -lclntsh
@echo ‘gcc ok…’

demo

/* sqlcheck=full */  
/* mode=ansi */ 
 
 
#include <stdio.h>  
#include <string.h>   
#include <sqlca.h>  

/* 绑定变量值和 选择列表项值的最大长度 */  
#define MAX_VAR_LEN     30 

/* 定义选择列表项名的最大长度 */  
#define MAX_NAME_LEN    80 
  

/* 定义宿主变量 */  
exec sql begin declare section; 
	char *serverid = "c##scott/root";
    char input_SqlStatements[100];  
    char current_date[20];  
exec sql end declare section;  
  
void doerr();
int connet();
void process_input();  
void process_output();  
  
int main()  
{  

    exec sql whenever sqlerror do doerr();   
    connet();  
    /* 分配输入描述区 输出描述区 */  
    exec sql allocate descriptor 'input_descriptor';  
    exec sql allocate descriptor 'output_descriptor';  
  
    for( ; ; )  
    {  
        printf("\n请输入动态SQL语句(EXIT:退出):\n");  
        gets(input_SqlStatements);  
        if(0 == strncmp(input_SqlStatements , "EXIT" , 4) || 0 == strncmp(input_SqlStatements , "exit" , 4))  
            break;  
  
        /* 准备动态SQL语句 */  
        exec sql prepare mydynamicSql from :input_SqlStatements;  
  
        /* 定义游标 */  
        exec sql declare mycursor cursor for mydynamicSql;  
  
        /* 处理绑定变量 */  
        process_input();  
  
        /* 打开游标 
         * select语句：处理查询结果 
         * 其他SQL语句：执行 
        */  
        exec sql open mycursor using descriptor 'input_descriptor';  
        if(0 == strncmp(input_SqlStatements , "SELECT" , 6) , 0 == strncmp(input_SqlStatements , "select" , 6))  
        {  
			process_output();
        }  
        /* 关闭游标 */  
        exec sql close mycursor;  
    }  
  
    /* 释放输入描述区和输出描述区 */  
    exec sql deallocate descriptor 'input_descriptor';  
    exec sql deallocate descriptor 'output_descriptor';  
  
    /* 提交事务，断开连接 */  
    exec sql commit work release;  
    puts("谢谢使用ANSI动态SQL!\n");  
  
    return 0;  
}  
  
void doerr()  
{  
    char	szerrbuf[120];
	size_t	outputlen, inputlen = 120;
	unsigned int ret = 0;

	//出错时,可以把错误SQL语言给打印出来
	EXEC SQL WHENEVER SQLERROR CONTINUE;

	ret = sqlgls(szerrbuf, &inputlen, &outputlen);
	printf("SQL:%.*s\n", inputlen, szerrbuf);
	printf("提示:%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
	EXEC SQL ROLLBACK WORK RELEASE;
	exit(1);
}  
  
void process_input()  
{  
    int i;  
  
    /* 定义宿主变量 */  
    exec sql begin declare section;  
        int input_count;  
        int input_type ;  
        int input_len;  
        char input_buffer[MAX_VAR_LEN];  
        char name[MAX_NAME_LEN];  
        int occurs;  
    exec sql end declare section;  
  
    /* 绑定变量->输入描述区 */  
    exec sql describe input mydynamicSql using descriptor 'input_descriptor';  
  
    /* 取得绑定变量个数 */  
    exec sql get descriptor 'input_descriptor' :input_count = count;  
  
    /* 循环处理绑定变量名 */  
   	//屏蔽也行 具体没搞懂 input_count一直都是0
    /* for(i = 0 ; i != input_count ; ++i)  
    {  
        occurs = i + 1;  
  
        // 取得绑定变量名   
        exec sql get descriptor 'input_descriptor' value :occurs :name = name;  
        printf("请输入%s的值：" , name);  
        gets(input_buffer);  
  
        // 以NULL结尾   
        input_len = strlen(input_buffer);  
        input_buffer[input_len] = '\0';  
  
        // 设置绑定变量类型、长度和值   
        input_type = 1;  
        exec sql set descriptor 'input_descriptor' value :occurs   
            type = :input_type , length = :input_len , data = :input_buffer;  
    }   */
}  
  
void process_output()  
{  
    int i;  
  
    // 定义宿主变量  
    EXEC SQL BEGIN DECLARE SECTION ;  
   
        int output_count;  
        int output_type;  
        int output_len;  
        char output_buffer[MAX_VAR_LEN];
        short  output_indicator;  
        char name[MAX_NAME_LEN];  
        int occurs;  
    EXEC SQL END DECLARE SECTION ;  
  
 
    // 选择列表项->输出描述区 
    exec sql describe output mydynamicSql using descriptor 'output_descriptor';  
  
    //取得选择列表项个数  
    exec sql get descriptor 'output_descriptor' :output_count = count;  
  
 
    //循环处理选择列表项 （显示表头；设置显示。数据按照字符串格式）
    
    output_type = 12;  //note //设置类型为变长字符串
    //output_type = 1;  //note
    for(i = 0 ; i != output_count ; ++i)  
    {  
        occurs = i + 1;  
        
        output_len = MAX_VAR_LEN;  
      
        // 设置选择列表项的类型和长度(设置每一列，按照varchar类型进行显示) 
        exec sql set descriptor 'output_descriptor' value :occurs   
            type = :output_type , length = :output_len;  
  
        //取得选择列表项的名称并输出 
        exec sql get descriptor 'output_descriptor' value :occurs :name = name;  
        	
        //显示选择列表项名称  
        printf("\t%s" , name);  
    }  
    printf("\n");  
  
    // 提取数据完毕->退出循环  
    exec sql whenever not found do break;  
  
    // 循环处理选择列表项数据  
    for( ; ; )  
    {  
        // 行数据->输出描述区  
        exec sql fetch mycursor into descriptor 'output_descriptor';  
           
        // 循环处理每列数据  
        for(i = 0 ; i < output_count ; ++i)  
        {  
            occurs = i +1;  
           
            // 取得列数据和指示变量值  
            exec sql get descriptor 'output_descriptor' VALUE :occurs  
                :output_buffer = DATA , :output_indicator = INDICATOR;  
  
            //输出列数据   
            if(-1 == output_indicator)  
               printf("\t%s", "   ");         
            else  
               printf("\t%s" , output_buffer);        
        }  
         
        printf("\n"); 
    }  
   
}

void connet()
{
	int ret = 0;
	//连接数据库
	EXEC SQL CONNECT:usrname IDENTIFIED BY:passwd USING:serverid ;
	if (sqlca.sqlcode != 0)
	{
		ret = sqlca.sqlcode;
		printf("sqlca.sqlcode: err:%d \n", sqlca.sqlcode);
		return ;
	}
	else
	{
		printf("connect ok...\n");
	}
}

ORACLE

makefile

  • dm03

all: dm03_oracle4

dm03_oracle4:
@echo ‘dm03_oracle4.pc start…’
proc iname=dm03_oracle4.pc oname=dm03_oracle4.c
@echo ‘dm03_oracle4.pc ok…’
gcc dm03_oracle4.c -o dm03_oracle4 -I O R A C L E H O M E / p r e c o m p / p u b l i c − L {ORACLE_HOME}/precomp/public -L ORACLEH​OME/precomp/public−L{ORACLE_HOME}/lib -lclntsh
@echo ‘gcc ok…’

clean:
@rm dm03_oracle4
@rm dm03_oracle4.c

  • dm04
    all: dm04_myselforacle4

dm04_myselforacle4:
@echo ‘dm04_myselforacle4.pc start…’
proc iname=dm04_myselforacle4.pc oname=dm04_myselforacle4.c
@echo ‘dm04_myselforacle4.pc ok…’
gcc dm04_myselforacle4.c -o dm04_myselforacle4 -I O R A C L E H O M E / p r e c o m p / p u b l i c − L {ORACLE_HOME}/precomp/public -L ORACLEH​OME/precomp/public−L{ORACLE_HOME}/lib -lclntsh
@echo ‘gcc ok…’

clean:
@rm dm04_myselforacle4
@rm dm04_myselforacle4.c

demo

/*******************************************************************
Sample Program 10:  Dynamic SQL Method 4

This program connects you to ORACLE using your username and
password, then prompts you for a SQL statement.  You can enter
any legal SQL statement.  Use regular SQL syntax, not embedded SQL.
Your statement will be processed.  If it is a query, the rows
fetched are displayed.
You can enter multiline statements.  The limit is 1023 characters.
This sample program only processes up to MAX_ITEMS bind variables and
MAX_ITEMS select-list items.  MAX_ITEMS is #defined to be 40.
*******************************************************************/

#include <stdio.h>
#include <string.h>
#include <setjmp.h>
#include <sqlda.h>
#include <stdlib.h>
#include <sqlcpr.h>

#define	SQL_SINGLE_RCTX  ((void *)0) 

/* Maximum number of select-list items or bind variables. */
#define MAX_ITEMS         40

/* Maximum lengths of the _names_ of the
   select-list items or indicator variables. */
#define MAX_VNAME_LEN     30
#define MAX_INAME_LEN     30

#ifndef NULL
#define NULL  0
#endif

/* Prototypes */
#if defined(__STDC__)
  void sql_error(void);
  int oracle_connect(void);
  int alloc_descriptors(int, int, int);
  int get_dyn_statement(void);
  void set_bind_variables(void);
  void process_select_list(void);
  void help(void);
#else
  void sql_error(/*_ void _*/);
  int oracle_connect(/*_ void _*/);
  int alloc_descriptors(/*_ int, int, int _*/);
  int get_dyn_statement(/* void _*/);
  void set_bind_variables(/*_ void -*/);
  void process_select_list(/*_ void _*/);
  void help(/*_ void _*/);
#endif

char *dml_commands[] = {"SELECT", "select", "INSERT", "insert",
                        "UPDATE", "update", "DELETE", "delete"};

EXEC SQL INCLUDE sqlda;
EXEC SQL INCLUDE sqlca;

EXEC SQL BEGIN DECLARE SECTION;
    char    dyn_statement[1024];
    EXEC SQL VAR dyn_statement IS STRING(1024);
EXEC SQL END DECLARE SECTION;
 
SQLDA *bind_dp;
SQLDA *select_dp;

/* Define a buffer to hold longjmp state info. */
jmp_buf jmp_continue;

/* A global flag for the error routine. */
int parse_flag = 0;

void main()
{
    int i;

    /* Connect to the database. */
    if (oracle_connect() != 0)
        exit(1);

    /* Allocate memory for the select and bind descriptors. */
    if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) != 0)
        exit(1);

    /* Process SQL statements. */
    for (;;) 
    {
        (void) setjmp(jmp_continue);

        /* Get the statement.  Break on "exit". */
        if (get_dyn_statement() != 0)
            break;

        /* Prepare the statement and declare a cursor. */
        EXEC SQL WHENEVER SQLERROR DO sql_error();

        parse_flag = 1;     /* Set a flag for sql_error(). */
        EXEC SQL PREPARE S FROM :dyn_statement;
        parse_flag = 0;     /* Unset the flag. */

        EXEC SQL DECLARE C CURSOR FOR S;

        /* Set the bind variables for any placeholders in the
           SQL statement. */
        set_bind_variables();

        /* Open the cursor and execute the statement.
         * If the statement is not a query (SELECT), the
         * statement processing is completed after the
         * OPEN.
         */

        EXEC SQL OPEN C USING DESCRIPTOR bind_dp;

        /* Call the function that processes the select-list.
         * If the statement is not a query, this function
         * just returns, doing nothing.
         */
        process_select_list();

        /* Tell user how many rows processed. */
        for (i = 0; i < 8; i++)
        {
           if (strncmp(dyn_statement, dml_commands[i], 6) == 0)
           {
               printf("\n\n%d row%c processed.\n", sqlca.sqlerrd[2],
                       sqlca.sqlerrd[2] == 1 ? '\0' : 's');
               break;
           }
        }
    }       /* end of for(;;) statement-processing loop */

    /* When done, free the memory allocated for
       pointers in the bind and select descriptors. */
    for (i = 0; i < MAX_ITEMS; i++)
    {    
        if (bind_dp->V[i] != (char *) 0)
            free(bind_dp->V[i]);
        free(bind_dp->I[i]);   /* MAX_ITEMS were allocated. */
        if (select_dp->V[i] != (char *) 0)
            free(select_dp->V[i]);
        free(select_dp->I[i]); /* MAX_ITEMS were allocated. */
    }

    /* Free space used by the descriptors themselves. */
    SQLSQLDAFree(SQL_SINGLE_RCTX, bind_dp);
    SQLSQLDAFree(SQL_SINGLE_RCTX, select_dp);

    EXEC SQL WHENEVER SQLERROR CONTINUE;
    /* Close the cursor. */
    EXEC SQL CLOSE C;

    EXEC SQL COMMIT WORK RELEASE;
    puts("\nHave a good day!\n");

    EXEC SQL WHENEVER SQLERROR DO sql_error();
    return;
}


int oracle_connect()
{
    EXEC SQL BEGIN DECLARE SECTION;
        VARCHAR  username[128];
        VARCHAR  password[32];
    EXEC SQL END DECLARE SECTION;

    printf("\nusername: ");
    fgets((char *) username.arr, sizeof username.arr, stdin);
    username.arr[strlen((char *) username.arr)-1] = '\0';
    username.len = (unsigned short)strlen((char *) username.arr);

    printf("password: ");
    fgets((char *) password.arr, sizeof password.arr, stdin);
    password.arr[strlen((char *) password.arr) - 1] = '\0';
    password.len = (unsigned short)strlen((char *) password.arr);


    EXEC SQL WHENEVER SQLERROR GOTO connect_error;

    EXEC SQL CONNECT :username IDENTIFIED BY :password;

    printf("\nConnected to ORACLE as user %s.\n", username.arr);

    return 0;

connect_error:
    fprintf(stderr, "Cannot connect to ORACLE as user %s\n", username.arr);
    return -1;
}


/*
 *  Allocate the BIND and SELECT descriptors using SQLSQLDAAlloc().
 *  Also allocate the pointers to indicator variables
 *  in each descriptor.  The pointers to the actual bind
 *  variables and the select-list items are realloc'ed in
 *  the set_bind_variables() or process_select_list()
 *  routines.  This routine allocates 1 byte for select_dp->V[i]
 *  and bind_dp->V[i], so the realloc will work correctly.
 */

alloc_descriptors(size, max_vname_len, max_iname_len)
int size;
int max_vname_len;
int max_iname_len;
{
    int i;

    /*
     * The first SQLSQLDAAlloc parameter is the runtime context.

     * The second parameter determines the maximum number of
     * array elements in each variable in the descriptor. In
     * other words, it determines the maximum number of bind
     * variables or select-list items in the SQL statement.
     *
     * The third parameter determines the maximum length of
     * strings used to hold the names of select-list items
     * or placeholders.  The maximum length of column 
     * names in ORACLE is 30, but you can allocate more or less
     * as needed.
     *
     * The fourth parameter determines the maximum length of
     * strings used to hold the names of any indicator
     * variables.  To follow ORACLE standards, the maximum
     * length of these should be 30.  But, you can allocate
     * more or less as needed.
     */

    if ((bind_dp =
       SQLSQLDAAlloc(SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) == 
         (SQLDA *) 0)
    {
        fprintf(stderr,
            "Cannot allocate memory for bind descriptor.");
        return -1;  /* Have to exit in this case. */
    }

    if ((select_dp =
        SQLSQLDAAlloc (SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) == 
           (SQLDA *) 0)
    {
        fprintf(stderr,
            "Cannot allocate memory for select descriptor.");
        return -1;
    }
    select_dp->N = MAX_ITEMS;

    /* Allocate the pointers to the indicator variables, and the
       actual data. */
    for (i = 0; i < MAX_ITEMS; i++) {
        bind_dp->I[i] = (short *) malloc(sizeof (short));
        select_dp->I[i] = (short *) malloc(sizeof(short));
        bind_dp->V[i] = (char *) malloc(1);
        select_dp->V[i] = (char *) malloc(1);
    }
       
    return 0;
}


int get_dyn_statement()
{
    char *cp, linebuf[256];
    int iter, plsql;


    for (plsql = 0, iter = 1; ;)
    {
        if (iter == 1)
        {
            printf("\nSQL> ");
            dyn_statement[0] = '\0';
        }
        
        fgets(linebuf, sizeof linebuf, stdin);

        cp = strrchr(linebuf, '\n');
        if (cp && cp != linebuf)
            *cp = ' ';
        else if (cp == linebuf)
            continue;

        if ((strncmp(linebuf, "EXIT", 4) == 0) ||
            (strncmp(linebuf, "exit", 4) == 0))
        {
            return -1;
        }

        else if (linebuf[0] == '?' ||
            (strncmp(linebuf, "HELP", 4) == 0) ||
            (strncmp(linebuf, "help", 4) == 0))
        {
            help();
            iter = 1;
            continue;
        }

        if (strstr(linebuf, "BEGIN") ||
            (strstr(linebuf, "begin")))
        {
            plsql = 1;
        }

        strcat(dyn_statement, linebuf);

        if ((plsql && (cp = strrchr(dyn_statement, '/'))) ||
            (!plsql && (cp = strrchr(dyn_statement, ';'))))
        {
            *cp = '\0';
            break;
        }
        else
        {
            iter++;
            printf("%3d  ", iter);
        }
    }
    return 0;
}



void set_bind_variables()
{
    int i, n;
    char bind_var[64];

    /* Describe any bind variables (input host variables) */
    EXEC SQL WHENEVER SQLERROR DO sql_error();

    bind_dp->N = MAX_ITEMS;  /* Initialize count of array elements. */
    EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;

    /* If F is negative, there were more bind variables
       than originally allocated by SQLSQLDAAlloc(). */
    if (bind_dp->F < 0)
    {
        printf ("\nToo many bind variables (%d), maximum is %d\n.",
                    -bind_dp->F, MAX_ITEMS);
        return;
    }

    /* Set the maximum number of array elements in the
       descriptor to the number found. */
    bind_dp->N = bind_dp->F;
 
    /* Get the value of each bind variable as a
     * character string.
     *   
     * C[i] contains the length of the bind variable
     *      name used in the SQL statement.
     * S[i] contains the actual name of the bind variable
     *      used in the SQL statement.
     *
     * L[i] will contain the length of the data value
     *      entered.
     *
     * V[i] will contain the address of the data value
     *      entered.
     *
     * T[i] is always set to 1 because in this sample program
     *      data values for all bind variables are entered
     *      as character strings.
     *      ORACLE converts to the table value from CHAR.
     *
     * I[i] will point to the indicator value, which is
     *      set to -1 when the bind variable value is "null".
     */
    for (i = 0; i < bind_dp->F; i++)
    {
        printf ("\nEnter value for bind variable %.*s:  ",
               (int)bind_dp->C[i], bind_dp->S[i]);
        fgets(bind_var, sizeof bind_var, stdin);

        /* Get length and remove the new line character. */
        n = strlen(bind_var) - 1;

        /* Set it in the descriptor. */
        bind_dp->L[i] = n;

        /* (re-)allocate the buffer for the value.
           SQLSQLDAAlloc() reserves a pointer location for
           V[i] but does not allocate the full space for
           the pointer. */

         bind_dp->V[i] = (char *) realloc(bind_dp->V[i],
                         (bind_dp->L[i] + 1));            

        /* And copy it in. */
        strncpy(bind_dp->V[i], bind_var, n);

        /* Set the indicator variable's value. */
        if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) ||
                (strncmp(bind_dp->V[i], "null", 4) == 0))
            *bind_dp->I[i] = -1;
        else
            *bind_dp->I[i] = 0;
    
        /* Set the bind datatype to 1 for CHAR. */
        bind_dp->T[i] = 1;
    }
  return;
}



void process_select_list()
{
    int i, null_ok, precision, scale;

    if ((strncmp(dyn_statement, "SELECT", 6) != 0) &&
        (strncmp(dyn_statement, "select", 6) != 0))
    {
        select_dp->F = 0;
        return;
    }

    /* If the SQL statement is a SELECT, describe the
        select-list items.  The DESCRIBE function returns
        their names, datatypes, lengths (including precision
        and scale), and NULL/NOT NULL statuses. */

    select_dp->N = MAX_ITEMS;
    
    EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;

    /* If F is negative, there were more select-list
       items than originally allocated by SQLSQLDAAlloc(). */
    if (select_dp->F < 0)
    {
        printf ("\nToo many select-list items (%d), maximum is %d\n",
                -(select_dp->F), MAX_ITEMS);
        return;
    }

    /* Set the maximum number of array elements in the
       descriptor to the number found. */
    select_dp->N = select_dp->F;

    /* Allocate storage for each select-list item.
  
       SQLNumberPrecV6() is used to extract precision and scale
       from the length (select_dp->L[i]).

       sqlcolumnNullCheck() is used to reset the high-order bit of
       the datatype and to check whether the column
       is NOT NULL.

       CHAR    datatypes have length, but zero precision and
               scale.  The length is defined at CREATE time.

       NUMBER  datatypes have precision and scale only if
               defined at CREATE time.  If the column
               definition was just NUMBER, the precision
               and scale are zero, and you must allocate
               the required maximum length.

       DATE    datatypes return a length of 7 if the default
               format is used.  This should be increased to
               9 to store the actual date character string.
               If you use the TO_CHAR function, the maximum
               length could be 75, but will probably be less
               (you can see the effects of this in SQL*Plus).

       ROWID   datatype always returns a fixed length of 18 if
               coerced to CHAR.

       LONG and
       LONG RAW datatypes return a length of 0 (zero),
               so you need to set a maximum.  In this example,
               it is 240 characters.

       */
    
    printf ("\n");
    for (i = 0; i < select_dp->F; i++)
    {
        char title[MAX_VNAME_LEN]; 
        /* Turn off high-order bit of datatype (in this example,
           it does not matter if the column is NOT NULL). */
        SQLColumnNullCheck (0, (unsigned short *)&(select_dp->T[i]), 
             (unsigned short *)&(select_dp->T[i]), &null_ok);

        switch (select_dp->T[i])
        {
            case  1 : /* CHAR datatype: no change in length
                         needed, except possibly for TO_CHAR
                         conversions (not handled here). */
                break;
            case  2 : /* NUMBER datatype: use SQLNumberPrecV6() to
                         extract precision and scale. */
                SQLNumberPrecV6( SQL_SINGLE_RCTX, 
                      (unsigned long *)&(select_dp->L[i]), &precision, &scale);
                      /* Allow for maximum size of NUMBER. */
                if (precision == 0) precision = 40;
                      /* Also allow for decimal point and
                         possible sign. */
                /* convert NUMBER datatype to FLOAT if scale > 0,
                   INT otherwise. */
                if (scale > 0)
                    select_dp->L[i] = sizeof(float);
                else
                    select_dp->L[i] = sizeof(int);
                break;

            case  8 : /* LONG datatype */
                select_dp->L[i] = 240;
                break;

            case 11 : /* ROWID datatype */
                select_dp->L[i] = 18;
                break;

            case 12 : /* DATE datatype */
                select_dp->L[i] = 9;
                break;
 
            case 23 : /* RAW datatype */
                break;

            case 24 : /* LONG RAW datatype */
                select_dp->L[i] = 240;
                break;
        }
        /* Allocate space for the select-list data values.
           SQLSQLDAAlloc() reserves a pointer location for
           V[i] but does not allocate the full space for
           the pointer.  */

         if (select_dp->T[i] != 2)
           select_dp->V[i] = (char *) realloc(select_dp->V[i],
                                    select_dp->L[i] + 1);  
         else
           select_dp->V[i] = (char *) realloc(select_dp->V[i],
                                    select_dp->L[i]);  

        /* Print column headings, right-justifying number
            column headings. */
        
        /* Copy to temporary buffer in case name is null-terminated */
        memset(title, ' ', MAX_VNAME_LEN);
        strncpy(title, select_dp->S[i], select_dp->C[i]);
        if (select_dp->T[i] == 2)
           if (scale > 0)
             printf ("%.*s ", select_dp->L[i]+3, title);
           else
             printf ("%.*s ", select_dp->L[i], title);
        else
          printf("%-.*s ", select_dp->L[i], title);

        /* Coerce ALL datatypes except for LONG RAW and NUMBER to
           character. */
        if (select_dp->T[i] != 24 && select_dp->T[i] != 2)
            select_dp->T[i] = 1;

        /* Coerce the datatypes of NUMBERs to float or int depending on
           the scale. */
        if (select_dp->T[i] == 2)
          if (scale > 0)
             select_dp->T[i] = 4;  /* float */
          else
             select_dp->T[i] = 3;  /* int */
    }
    printf ("\n\n");

    /* FETCH each row selected and print the column values. */
    EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;

    for (;;)
    {
        EXEC SQL FETCH C USING DESCRIPTOR select_dp;

        /* Since each variable returned has been coerced to a
           character string, int, or float very little processing 
           is required here.  This routine just prints out the 
           values on the terminal. */
        for (i = 0; i < select_dp->F; i++)
        {
            if (*select_dp->I[i] < 0)
                if (select_dp->T[i] == 4) 
                  printf ("%-*c ",(int)select_dp->L[i]+3, ' ');
                else
                  printf ("%-*c ",(int)select_dp->L[i], ' ');
            else
                if (select_dp->T[i] == 3)     /* int datatype */
                  printf ("%*d ", (int)select_dp->L[i], 
                                 *(int *)select_dp->V[i]);
                else if (select_dp->T[i] == 4)     /* float datatype */
                  printf ("%*.2f ", (int)select_dp->L[i], 
                                 *(float *)select_dp->V[i]);
                else                          /* character string */
                  printf ("%-*.*s ", (int)select_dp->L[i],
                            (int)select_dp->L[i], select_dp->V[i]);
        }
        printf ("\n");
    }
end_select_loop:
    return;
}



void help()
{
    puts("\n\nEnter a SQL statement or a PL/SQL block at the SQL> prompt.");
    puts("Statements can be continued over several lines, except");
    puts("within string literals.");
    puts("Terminate a SQL statement with a semicolon.");
    puts("Terminate a PL/SQL block (which can contain embedded semicolons)");
    puts("with a slash (/).");
    puts("Typing \"exit\" (no semicolon needed) exits the program.");
    puts("You typed \"?\" or \"help\" to get this message.\n\n");
}


void sql_error()
{
    /* ORACLE error handler */
    printf ("\n\n%.70s\n",sqlca.sqlerrm.sqlerrmc);
    if (parse_flag)
        printf
        ("Parse error at character offset %d in SQL statement.\n",
           sqlca.sqlerrd[4]);

    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL ROLLBACK WORK;
    longjmp(jmp_continue, 1);
}
  • dm04
/* 包含C头文件 */  
#include <stdio.h>  
#include <string.h>  
#include <stdlib.h>  
#include <setjmp.h>  
#include <sqlcpr.h>  
  
/* 包含SQLDA和SQLCA结构 */  
#include <sqlda.h>  
#include <sqlca.h>  
  
/* 定义绑定变量和选择列表项的最大个数 */  
#define MAX_ITEMS       40  
  
/* 定义绑定变量和选择列表项名称的最大长度 */  
#define MAX_VNAME_LEN   30  
  
/* 定义指示变量名称的最大长度 */  
#define MAX_INAME_LEN   30  
  
void connect();  
void sql_error();  
void alloc_descriptors(int , int , int);  
void dealloc_descriptors();  
void set_bind_variables();  
void process_select_list();  
  
/* 定义绑定描述区和选择描述区 */  
 
SQLDA* bind_dp;  
SQLDA* select_dp;  
  
/* 定义输入宿主变量：存放动态SQL语句 */ 
exec sql begin declare section; 
  	char sql_stat[100];   
	char current_date[20];
exec sql end declare section;  
     
  
int main()  
{  
    /* 安装错误处理句柄 */  
    exec sql whenever sqlerror do sql_error();  
  
    /* 连接到数据库 */  
    connect2();  
  
    /* 分配绑定描述区和选择描述区 */  
    alloc_descriptors(MAX_ITEMS , MAX_VNAME_LEN , MAX_INAME_LEN);  
  
    for( ; ; )  
    {  
        printf("请输入动态SQL语句(exit:退出):");  
        gets(sql_stat);  
  
        /* EXIT(exit):退出 */  
        if(0 == strncmp(sql_stat , "exit" , 4) || 0 == strncmp(sql_stat , "EXIT" , 4))  
            break;  
  
        /* 准备动态SQL语句 */  
        exec sql prepare s from :sql_stat;  
  
        /* 定义游标 */  
        exec sql declare c cursor for s;  
  
        /* 出错，继续下一循环 */  
        if(0 != sqlca.sqlcode)  
            continue;  
  
        /* 设置绑定变量 */  
        set_bind_variables();  
  
        /* 
         * 打开游标 
         * select语句：生成结果集 
         * 其他SQL语句：执行语句 
         */  
        exec sql open c using descriptor bind_dp;  
  
        /* 
         * select语句 
         */  
        if(0 == strncmp(sql_stat , "select" , 6) || 0 == strncmp(sql_stat , "SELECT" , 6)) 
		{  
            process_select_list();   
        }  
        /* 关闭游标 */  
        exec sql close c;  
    }  
  
    /* 释放选择描述区和选择描述区 */  
    dealloc_descriptors();  
  
    /* 提交事务，断开连接 */  
    exec sql commit work release;  
    puts("谢谢使用Oracle动态SQL方法四!\n");  
  
    return 0;  
}  

 
void connect2()  
{  
    /* 定义宿主变量 */  
    exec sql begin declare section; 
   		char username[20] , password[20] , server[20];
	exec sql end declare section;
     
  
    /* 输入用户名、口令和网络服务名 */  
    printf("username:");  
    gets(username);  
  
    printf("password: ");  
    gets(password);  
  
    printf("server:");  
    gets(server);  
  
    /* 连接到数据库 */  
    EXEC SQL CONNECT :username identified by :password using :server;  
}  


void sql_error()  
{  
    /* 显示SQL错误信息 */  
    printf("%.*s\n" , sqlca.sqlerrm.sqlerrml , sqlca.sqlerrm.sqlerrmc);  
}  

//struct SQLDA {
  // ub4    // int        N; // Descriptor size in number of entries        //
  // text** // char     **V; // Ptr to Arr of addresses of main variables   //
  // ub4*   // int       *L; // Ptr to Arr of lengths of buffers            //
  // sb2*   // short     *T; // Ptr to Arr of types of buffers              //
  // sb2**  // short    **I; // Ptr to Arr of addresses of indicator vars   //
  // sb4    // int        F; // Number of variables found by DESCRIBE       //
  // text** // char     **S; // Ptr to Arr of variable name pointers        //
  // ub2*   // short     *M; // Ptr to Arr of max lengths of var. names     //
  // ub2*   // short     *C; // Ptr to Arr of current lengths of var. names //
  // text** // char     **X; // Ptr to Arr of ind. var. name pointers       //
  // ub2*   // short     *Y; // Ptr to Arr of max lengths of ind. var. names//
  // ub2*   // short     *Z; // Ptr to Arr of cur lengths of ind. var. names//
 // };
  
void alloc_descriptors(int size , int max_vname_len , int max_iname_len)  
{  
      
    int i;  
  
    /* 分配绑定描述区和选择描述区 */  
    bind_dp = SQLSQLDAAlloc(0 , size , MAX_VNAME_LEN , MAX_INAME_LEN);  
    select_dp = SQLSQLDAAlloc(0 , size , MAX_VNAME_LEN , MAX_INAME_LEN);  
  
  
    /* 为指示变量、绑定变量和选择列表项分配内存 */  
    for(i = 0 ; i != MAX_ITEMS ; ++i)  
    {  
        bind_dp->I[i] = (short*)malloc(sizeof(short));  
        select_dp->I[i] = (short*)malloc(sizeof(short));  
  
        bind_dp->V[i] = (char*)malloc(1);  
        select_dp->V[i] = (char*)malloc(1);  
    }  
}  
  
void dealloc_descriptors()  
{  
    int i;  
  
    /* 释放指示变量、绑定变量和选择列表项占用的内存 */  
    for(i = 0 ; i != MAX_ITEMS ; ++i)  
    {  
        if(bind_dp->V[i] != (char*)0)  
            free(bind_dp->V[i]);  
        free(bind_dp->I[i]);  
  
        if(select_dp->V[i] != (char*)0)  
            free(select_dp->V[i]);  
        free(select_dp->I[i]);  
    }  
  
    /* 释放绑定描述区和选择描述区 */  
    SQLSQLDAFree(0 , bind_dp);  
    SQLSQLDAFree(0 , select_dp);  
}  
  
void set_bind_variables()  
{  
    int i;  
    char bind_var[64];  
  
    /* 设置绑定变量最大个数 */  
    bind_dp->N = MAX_ITEMS;  
  
    /* 绑定变量名称: 绑定描述区 */  
    exec sql describe bind variables for s into bind_dp;  
  
    /* 设置绑定变量实际个数 */  
    bind_dp->N = bind_dp->F;  
  
    /* 循环处理绑定变量 */  
    for(i = 0 ; i != bind_dp->F ; ++i)  
    {  
        /* 显示绑定变量名 */  
        printf("请输入绑定变量%.*s的值：" , (int)bind_dp->C[i] , bind_dp->S[i]);  
  
        /* 输入绑定变量的值 */  
        gets(bind_var);  
  
        /* 设置绑定变量的长度成员 */  
        bind_dp->L[i] = strlen(bind_var);  
  
        /* 为绑定变量数据缓冲区重新分配内存(多一位，留给'\0') */  
        bind_dp->V[i] = (char*)realloc(bind_dp->V[i] , bind_dp->L[i] + 1);  
  
        /* 绑定变量数据: 数据缓冲区 */  
        strcpy(bind_dp->V[i] , bind_var);  
  
        /* 设置指示变量，处理NULL */  
        if(0 == strncmp(bind_var , "NULL" , 4) || 0 == strncmp(bind_var , "null" , 4))  
            *bind_dp->I[i] = -1;  
        else  
            *bind_dp->I[i] = 0;  
  
        /* 设置数据缓冲区数据类型代码->char */  
        bind_dp->T[i] = 1;  
    }  
}  
  
void process_select_list()  
{  
    int i , null_ok , precision , scale;  
    char title[MAX_VNAME_LEN];  
  
    /* 设置选择列表项的最大个数 */  
    select_dp->N = MAX_ITEMS;  
  
    /* 选择列表项: 选择描述区 */  
    exec sql describe select list for s into select_dp;  
  
    /* 设置选择列表项的实际个数 */  
    select_dp->N = select_dp->F;  
  
    /* 循环处理选择列表项 */  
    for(i = 0 ; i != select_dp->F ; ++i)  
    {  
        /* 清除select_dp->T[i]的高位->null */  
        SQLColumnNullCheck(0 , (unsigned short*)&select_dp->T[i]  
            , (unsigned short*)&select_dp->T[i] , &null_ok);  
  
        /* 根据内部数据类型确定外部类型数据长度(显示长度) */  
        switch(select_dp->T[i])  
        {  
        case 2:  
            /* number类型，取得精度与标度 */  
            //SQLNumberPrecV6(0 , (unsigned short*)&select_dp->T[i] , &precision , &scale); 
            SQLNumberPrecV6(0 , (unsigned long *)&select_dp->L[i] , &precision , &scale);  //wangbaoming modify 201409
			
            if(scale > 0)  
                /* 实数: 显示长度:float  */  
                select_dp->L[i] = sizeof(float);  
            else  
                /* 整数: 显示长度 int */  
                select_dp->L[i] = sizeof(int);  
            break;  
        case 12:  
            /* DATA数据类型(DD-MON-YY) */  
            select_dp->L[i] = 9;  
            break;  
        }  
  
        /* 根据变量长度，重新为选择列表项数据缓冲区分配内存 */  
        if(2 != select_dp->T[i])  
            /* 其他类型 */  
            select_dp->V[i] = (char*)realloc(select_dp->V[i] , select_dp->L[i] + 1);  
        else  
            /* number类型 */  
            select_dp->V[i] = (char*)realloc(select_dp->V[i] , select_dp->L[i]);  
  
        /* 初始化title */  
        memset(title , ' ' , MAX_VNAME_LEN);  
  
        /* 选择列表项名称: title */  
        strncpy(title , select_dp->S[i] , select_dp->C[i]);  
  
        /* 显示列名 */  
        if(2 == select_dp->T[i])  
            if(scale > 0)  
                printf("\t%.*s" , select_dp->L[i] + 3, title);  
            else  
                printf("\t%.*s" , select_dp->L[i] , title);  
        else  
            printf("\t%-.*s" , select_dp->L[i] , title);  
  
        /* 根据Oracle内部类型确定外部数据类型(显示类型) */  
        if( 2 == select_dp->T[i])  
        {  
            /* number 类型*/  
            if(scale > 0)  
                /* float */  
                select_dp->T[i] = 4;  
            else  
                /* int */  
                select_dp->T[i] = 3;  
        }  
        else  
            /* char */  
            select_dp->T[i] = 1;  
    }  
  
    printf("\n");  
  
    /* 提取数据完毕->结束循环 */  
    exec sql whenever not found do break;  
  
    /* 循环处理选择列表数据 */  
    for( ; ; )  
    {  
        /* 数据->选择描述区 */  
        exec sql fetch c using descriptor select_dp;  
  
        /* 显示数据 */  
        for( i = 0 ; i != select_dp->F ; ++i)  
        {  
            if(*select_dp->I[i] < 0){  
                /* 处理NULL */  
                printf("\tNULL");  
            }else{  
                if(3 == select_dp->T[i]) {  
                    /* int */  
                    printf("\t%d" , *(int*)select_dp->V[i]);  
                }else if(4 == select_dp->T[i]){  
                    /* float */  
                    printf("\t%8.2f" , *(float*)select_dp->V[i]);  
                }else{  
                    /* char */  
                    printf("\t%.*s" , select_dp->L[i] , select_dp->V[i]);  
                }  
                }  
        }  
        printf("\n");  
    }  
}
想想**
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle 11g Pro*C/C++ 编程艺术中文版
10-23
本书为应用开发人员提供了使用Pro*C/C++开发数据库应用的方法。针对各种不同类型的Pro*C/C++应用，本书都提供了非常详细、具体的开发方法，并且为读者提供了大量的示例程序。本书不仅介绍了各种类型Pro*C/C++应用的开发方法，而且还介绍了Oracle 11g在Pro*C/C++方面所提供的各种新特征，包括使用大纲固定执行计划、DB2数组插入和数组提取、隐含缓冲区插入、动态SQL语句缓存等。通过学习本书，读者可以快速掌握使用Pro*C/C++开发数据库应用的方法。
Pro*C 错误的详细信息以及解决方法(收下备用)
系统运维
07-01 2204
Oracle Precompiler: Pro*C/C++ Release 3.0 MessagesPCC-02010: found end-of-file while scanning string literalCause: A string in a SQL statement, which should be delimited by single quotation marks, was...
linux下oracle11g proc*c开发环境搭建
tj807126663的专栏
04-12 5306
#include #include #include EXEC SQL INCLUDE sqlca; EXEC SQL BEGIN DECLARE SECTION; varchar username[20]; varchar password[20]; EXEC SQL END DECLARE SECTION; int main(void) { strcpy(username.
Oracle Pro*C 动态SQL技术
代威涛的主页
11-06 3010
Pro*C的常用动态SQL技术一共有3种: 用于处理不包含宿主变量的动态SQL, 不能用于SELECT语句. 用于处理输入宿主变量个数和类型已经确定的动态SQL, 不能用于SELECT语句. 用于处理选择列表项和输入宿主变量个数已经确定的动态SQL, 此种方法可以处理所有前两种方法能处理的情况, 此外, 还能处理SELECT语句. 由于第3种方法已经包
Oracle-mode SQL 和ANSI-mode SQL 区别
sinovoice的专栏
10-23 2212
写了这么多年proc，一直都是基于公司的解决方案来的，很多细节没有关注过，换公司后，又一套makefile定义，发现sql语句返回的错误值和以前不一样，例如1403,100是不同的返回值，但是意义是一样的，只是一个是ORAcle SQL，一个是ANSI SQL，但是怎么就是其中一种sql那？百度了下没找到，后来看makefile定义看看到，原来就是proc的编译选项内有个参数是可以指定其mode的
Oracle pro*c/c++ 动态sql(方法二示例)
u012516419的博客
10-21 303
方法二 方法二 也只能是非查询语句， 允许非查询动态 SQL 语句内包含输入宿主变量占位符, 而且只能带固定数量的宿主变量。使用内嵌PREPARE命令准备SQL语言： 1 PREPARE语法 EXEC SQL PREPARE statement_name FROM {:host_string | string_literal}; PREPARE是一个预编译器标识符,而不是宿主变量 2 USING子句 EXEC SQL EXECUTE statement_name [USING :host_variabl.
oracle头文件 makefile,Pro*c/c++ makefile编译相关
weixin_29898529的博客
04-11 349
Pro*c/c++ 欲编译选项1：iname作用：该选项使用与制定输入的文件名，并且是必须的。当指定的是pro*c的原程序的时候，文件扩展名.pc可以忽略。Exp：iname=path_add_filename2：oname作用：该选项用于指定输出的文件名，如果该选项没有的话，输出的文件名默认为输入的编译文件名，文件后缀则由选项CPP_SUFFIX确定。编译输出的文件是欲编译之后的c/c++代码...
[Oracle,Windows PC]Windows 下PC的调用方法和配置
WillSea的专栏
07-09 1078
本人的Blog更新：http://www.iwllsea.com/article.asp?id=38，涉及更多Oracle文章，希望对大家有所帮助。Windows 下PC的调用方法和配置：PC 设置：  1: 安装Oracle Server,创建Database.   2: 安装Oracle Client的预编译Proc/C++   3: 安装VC6.0  PC 预编译
v6c语言程序错误,Pro*C 错误的详细信息以及解决方法(收下备用)
weixin_39962770的博客
05-23 1370
Oracle Precompiler: Pro*C/C++ Release 3.0 MessagesPCC-02010: found end-of-file while scanning string literalCause: A string in a SQL statement, which should be delimited by single quotation marks, was...
VC动态创建SQL数据库
09-03
动态创建数据库的方法。以及最基本的数据库操作讲解。
使用Pro*C/C++ 开发嵌入式SQL程序
04-12
#有书签# 如何使用嵌入式SQL 编写访问ORACLE的C/C++程序。 内容翻译自 ORACLE9i Pro*C Programming Guide.
Pro*C/C++Precompiler Programmer’s Guide
06-06
The Pro*C/C++ Programmer's Guide is intended for programmers, systems analysts, project managers, and other Oracle users who perform, or are interested in learning about, the following tasks: ...
oracle pro c/c++资料
12-30
在Pro*C程序中可以嵌入SQL语言，利用这些SQL语言可以完成动态地建立、修改和删除数据库中的表，也可以查询、插入、修改和删除数据库表中的行，还可以在Pro*C程序中还可以嵌入PL/SQL块，以改进应用程序的性能，特别是...
oracle pro*c入门
07-16
介绍proc的组成,使用等. 如何在c/c++中连接数据库,数据类型,增删改查,使用游标,以及预编译,事务,带参动态sql等内容. 含例子
重现ORA-021500(17099)错误，并提供解决办法
04-19 2219
    两天前写的一篇文章提到公司的应用程序常常出现ORA-21500错误（http://blog.csdn.net/ah__fu/archive/2008/04/16/2296641.aspx），在网上找了很久，终于有以下资料提供了些头绪：http://www.dbatools.net/doc/bug10204.html     Bugs fixed in the 10.2.0.4 Patch
SQL Server的3种恢复模式（Simple,Full,Bulk-logged)
09-29 1738
1.SimpleSimple模式的旧称叫”Checkpoint with truncate log“，其实这个名字更形象，在Simple模式下，SQL Server会在每次checkpoint或backup之后自动截断...
SQL学习笔记（一）
mingo_敏
07-12 5586
本文地址：http://blog.csdn.net/shanglianlm/article/details/51890450SQL 中最重要的 DDL （ 数据定义语言）语句: CREATE DATABASE - 创建新数据库 ALTER DATABASE - 修改数据库 CREATE TABLE - 创建新表 ALTER TABLE - 变更（改变）数据库表 DROP TABLE - 删
valgrind 内存泄露检测
hknaruto的专栏
03-17 1541
valgrind --leak-check=full ./progname
精通oracle 10gpro*c/c++编程 pdf
最新发布
08-03
Oracle 10g Pro*C/C是一种针对Oracle数据库的编程语言，它允许开发人员使用C/C++语言来访问和操作Oracle数据库。精通Oracle 10g Pro*C/C编程意味着具备深入的理解和熟练运用这种编程语言的能力。 首先，精通Oracle ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值