Oracle的 EXEC SQL CONTEXT学习

转载 2013年12月04日 14:08:50

EXEC SQL CONTEXT... 是Oracle Pro*C的特有的语法,详细信息参见

http://docs.oracle.com/cd/B28359_01/appdev.111/b28427/pc_11thr.htm#i997959

EXEC SQL ENABLE THREADS;
EXEC SQL CONTEXT ALLOCATE :context_var;
EXEC SQL CONTEXT USE { :context_var | DEFAULT};
EXEC SQL CONTEXT FREE :context_var;

所谓context ,被称为runtime context,其实质就是保留连接数据库的信息,保留连接数据库的通道。

官方解释中,使用 exec sql context ,有几种方式:

1 各个进程之间不共享context

事实上,Oracle的官方例子---- Thread_example1.pc,采用的就是这种方式。

各个进程共通处理一堆转账数据,所以它们之间需要考虑对共同数据(文本格式的转账记录)读取时加mutex。

但是,由于每个进程可以自己拥有一个context---数据库通道,故此基本互相不干涉。

我认为这是合理的,也可能是比较高效率的方式。

2 各个进程之间共享一个context

此时,瓶颈出现在此context之上。可以想像一下,通过多线程来处理数据库之外的数据之后,

还是要回到一个共通的数据库通道来排队等候,是否效率并未发挥出来呢。

3 各个进程之间共享多个context

这个是最复杂的,可能也是最没有道理的。1的方式应该更好些。

而且,上述这些,都没有考虑到连接池的作用,也许是这种技术出现的年代是很早的。

为了备忘,记录oracle官方例子如下:

复制代码
/*                                    
 * Name:        Thread_example1.pc                                    
 *                                    
 * Description: This program illustrates how to use threading in                                    
 *      conjunction with precompilers. The program creates as many                                    
 *      sessions as there are threads. Each thread executes zero or                                    
 *      more transactions, that are specified in a transient                                    
 *      structure called 'records'.                                    
 * Requirements:                                    
 *      The program requires a table 'ACCOUNTS' to be in the schema                                    
 *      scott/tiger. The description of ACCOUNTS is:                                    
 *  SQL> desc accounts                                    
 *   Name                            Null?    Type                                    
 *  ------------------------------- -------  ------                                    
 *  ACCOUNT                                  NUMBER(36)                                    
 *  BALANCE                                  NUMBER(36,2)                                    
 *                                    
 *  For proper execution, the table should be filled with the accounts                                    
 *      10001 to 10008.                                    
 *                                    
 *                                    
 */                                    
                                    
#include <stdio.h>                                    
#include <stdlib.h>                                    
#include <string.h>                                    
#include <sqlca.h>                                    
                                    
#define      _EXC_OS_    _EXC__UNIX                                    
#define      _CMA_OS_    _CMA__UNIX                                    
                                    
#ifdef DCE_THREADS                                    
  #include <pthread.h>                                    
#else                                    
  #include <thread.h>                                    
#endif                                    
                                    
/* Function prototypes */                                    
void   err_report();                                    
                                    
#ifdef DCE_THREADS                                    
    void   do_transaction();                                
#else                                    
    void   *do_transaction();                                
#endif                                    
                                    
void   get_transaction();                                    
void   logon();                                    
void   logoff();                                    
                                    
#define CONNINFO "scott/tiger"                                    
                                    
#define THREADS  3                                     
                                    
struct parameters                                     
                                    
{ sql_context * ctx;                                    
  int thread_id;                                    
};                                    
                                    
typedef struct parameters parameters;                                    
                                    
struct record_log                                    
{                                     
    char action;                                
    unsigned int from_account;                                
    unsigned int to_account;                                
    float  amount;                                
};                                    
                                    
typedef struct record_log record_log;                                    
                                    
record_log records[]= { { 'M', 10001, 10002, 12.50 },                                    
                        { 'M', 10001, 10003, 25.00 },                                    
                        { 'M', 10001, 10003, 123.00 },                                    
                        { 'M', 10001, 10003, 125.00 },                                    
                        { 'M', 10002, 10006, 12.23 },                                    
                        { 'M', 10007, 10008, 225.23 },                                    
                        { 'M', 10002, 10008, 0.70 },                                    
                        { 'M', 10001, 10003, 11.30 },                                    
                        { 'M', 10003, 10002, 47.50 },                                    
                        { 'M', 10002, 10006, 125.00 },                                    
                        { 'M', 10007, 10008, 225.00 },                                    
                        { 'M', 10002, 10008, 0.70 },                                    
                        { 'M', 10001, 10003, 11.00 },                                    
                        { 'M', 10003, 10002, 47.50 },                                    
                        { 'M', 10002, 10006, 125.00 },                                    
                        { 'M', 10007, 10008, 225.00 },                                    
                        { 'M', 10002, 10008, 0.70 },                                    
                        { 'M', 10001, 10003, 11.00 },                                    
                        { 'M', 10003, 10002, 47.50 },                                    
                        { 'M', 10008, 10001, 1034.54}};                                    
                                    
static unsigned int trx_nr=0;                                    
                                    
#ifdef DCE_THREADS                                    
    pthread_mutex_t mutex;                                
#else                                    
    mutex_t mutex;                                
#endif                                    
                                    
                                    
                                    
/*********************************************************************                                    
 *  Main                                    
 ********************************************************************/                                    
main()                                    
{                                    
    sql_context ctx[THREADS];                                
                                    
    #ifdef DCE_THREADS                                
        pthread_t thread_id[THREADS];                            
        pthread_addr_t status;                            
    #else                                
        thread_t thread_id[THREADS];                            
        int status;                            
    #endif                                
                                    
    parameters params[THREADS];                                
                                    
    int i;                                
                                      
    EXEC SQL ENABLE THREADS;                                
                                    
    EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);                                
                                    
    /* Create THREADS sessions by connecting THREADS times */                                
    for(i=0;i<THREADS;i++)                                
    {                                
        printf("Start Session %d....",i);                            
        EXEC SQL CONTEXT ALLOCATE :ctx[i];                            
        logon(ctx[i],CONNINFO);                            
    }                                
                                    
                                    
    /*Create mutex for transaction retrieval */                                
    #ifdef DCE_THREADS                                
        if (pthread_mutex_init(&mutex,pthread_mutexattr_default))                            
    #else                                
        if (mutex_init(&mutex, USYNC_THREAD, NULL))                            
    #endif                                
    {                                
        printf("Can't initialize mutex\n");                            
        exit(1);                            
    }                                
                                    
    /*Spawn threads*/                                
    for(i=0;i<THREADS;i++)                                
    {                                
            params[i].ctx=ctx[i];                            
            params[i].thread_id=i;                            
                                    
            printf("Thread %d... ",i);                            
                                    
                                    
    #ifdef DCE_THREADS                                
        if (pthread_create(&thread_id[i],pthread_attr_default,                            
                (pthread_startroutine_t)do_transaction,                            
                (pthread_addr_t) &params[i]))                            
    #else                                
        if (status = thr_create                            
            (NULL, 0, do_transaction, &params[i], 0, &thread_id[i]))                            
    #endif                                
                  printf("Cant create thread %d\n",i);                        
        else                            
                  printf("Created\n");                        
    }                                
                                    
                                    
    /* Logoff sessions....*/                                
    for(i=0;i<THREADS;i++)                                
    {                                
        /*wait for thread to end */                            
        printf("Thread %d ....",i);                            
                                    
        #ifdef DCE_THREADS                            
            if (pthread_join(thread_id[i],&status))                        
                       printf("Error when waiting for thread % to terminate\n", i);                    
            else                        
                      printf("stopped\n");                    
                                    
            printf("Detach thread...");                        
                                    
            if (pthread_detach(&thread_id[i]))                        
                   printf("Error detaching thread! \n");                        
            else                        
                   printf("Detached!\n");                        
                                    
        #else                            
            if (thr_join(thread_id[i], NULL, NULL))                        
                   printf("Error waiting for thread to terminate\n");                        
        #endif                            
                                    
        printf("Stop Session %d....",i);                            
        logoff(ctx[i]);                            
        EXEC SQL CONTEXT FREE :ctx[i];                            
    }                                
                                    
                                    
    /*Destroys mutex*/                                
    #ifdef DCE_THREADS                                
        if (pthread_mutex_destroy(&mutex))                            
    #else                                
        if (mutex_destroy(&mutex))                            
    #endif                                
    {                                
        printf("Can't destroy mutex\n");                            
        exit(1);                            
    }                                
}                                    
                                    
/*********************************************************************                                    
 * Function: do_transaction                                    
 *                                    
 * Description:  This functions executes one transaction out of the                                     
 *               records array. The records array is 'managed' by                                    
 *               the get_transaction function.                                    
 *                                    
 *                                    
 ********************************************************************/                                    
#ifdef DCE_THREADS                                    
    void do_transaction(params)                                
#else                                    
    void *do_transaction(params)                                
#endif                                    
parameters *params;                                    
{                                    
    struct sqlca sqlca;                                
    record_log *trx;                                
    sql_context ctx=params->ctx;                                
                                    
    /* Done all transactions ? */                                
    while (trx_nr < (sizeof(records)/sizeof(record_log)))                                
    {                                
        get_transaction(&trx);                            
                                    
        EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);                            
        EXEC SQL CONTEXT USE :ctx;                            
                                    
        printf("Thread %d executing transaction\n",params->thread_id);                            
                                    
        switch(trx->action)                            
        {                            
              case 'M':  EXEC SQL UPDATE ACCOUNTS                            
                                  SET    BALANCE=BALANCE+:trx->amount                            
                                  WHERE  ACCOUNT=:trx->to_account;                            
                                    
                         EXEC SQL UPDATE ACCOUNTS                            
                                  SET    BALANCE=BALANCE-:trx->amount                            
                                  WHERE  ACCOUNT=:trx->from_account;                            
                         break;                            
               default:  break;                            
            }                            
                                    
        EXEC SQL COMMIT;                            
      }                                
}                                    
                                    
                                    
/*****************************************************************                                    
 * Function: err_report                                    
 *                                    
 * Description: This routine prints out the most recent error                                    
 *                                    
 ****************************************************************/                                    
void      err_report(sqlca)                                    
struct sqlca sqlca;                                    
{                                    
  if (sqlca.sqlcode < 0)                                    
   printf("\n%.*s\n\n",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc);                                    
  exit(1);                                    
}                                    
                                    
/*****************************************************************                                    
 * Function: logon                                    
 *                                    
 * Description: Logs on to the database as USERNAME/PASSWORD                                    
 *                                    
 *****************************************************************/                                    
void      logon(ctx,connect_info)                                    
sql_context ctx;                                    
char * connect_info;                                    
{                                    
    EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);                                
    EXEC SQL CONTEXT USE :ctx;                                
    EXEC SQL CONNECT :connect_info;                                
    printf("Connected!\n");                                
                                    
}                                    
                                    
/******************************************************************                                    
 * Function: logoff                                    
 *                                    
 * Description: This routine logs off the database                                    
 *                                    
 ******************************************************************/                                    
void      logoff(ctx)                                    
sql_context ctx;                                    
{                                    
    EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);                                    
    EXEC SQL CONTEXT USE :ctx;                                    
    EXEC SQL COMMIT WORK RELEASE;                                    
    printf("Logged off!\n");                                    
}                                    
                                    
                                    
/******************************************************************                                    
 * Function: get_transaction                                    
 *                                    
 * Description: This routine returns the next transaction to process                                    
 *                                    
 ******************************************************************/                                    
void get_transaction(trx)                                    
record_log ** trx;                                    
{                                    
    #ifdef DCE_THREADS                                
        if (pthread_mutex_lock(&mutex))                            
    #else                                
        if (mutex_lock(&mutex))                            
    #endif                                
        printf("Can't lock mutex\n");                            
                                    
                                    
    *trx = &records[trx_nr];                                
                                    
    trx_nr++;                                
                                    
    #ifdef DCE_THREADS                                
        if (pthread_mutex_unlock(&mutex))                            
    #else                                
        if (mutex_unlock(&mutex))                            
    #endif                                
        printf("Can't unlock mutex\n");                            

相关文章推荐

[export excel]Oracle Form導入sql查詢再導出CSV查詢結果( Using EXEC_SQL Package)

功能描述:一個簡單的Form,將調試好的SQL文本導入到Form文字框,再將SQL結果導出成CSV文件。相關代碼:[Import SQL Text] Button : WHEN-BUTTON-PRES...

SQL Server查询计划sys.dm_exec_text_query_plan

SQL 管理视图的使用

SQL server 2008里面通过sys.dm_exec_procedure_stats得到存储过程的执行信息

很多DBA都希望能够得到某个存储过程的执行信息,比如: 1)       执行了多少次 2)       执行的执行计划如何 3)   &#...

SQL server 2008里面通过sys.dm_exec_procedure_stats得到存储过程的执行信息

----http://www.cnblogs.com/zhangxiaohuid/archive/2011/12/29/2306173.html 很多DBA都希望能够得到某个存储过程的执行信息,...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)