Pro*C中COMMIT WORK 和 COMMIT WORK RELEASE的区别

Pro*C中COMMIT WORK 和 COMMIT WORK RELEASE的区别

首先说明的是COMMIT WORK RELEASE是Pro*C的语法，ORACLE的SQLPLUS和PL/SQL块中不支持这样的写法。
譬如：

[oracle@huateng c]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 9 20:43:10 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> COMMIT WORK;

Commit complete.

SQL> COMMIT WORK RELEASE;
COMMIT WORK RELEASE
            *
ERROR at line 1:
ORA-02185: a token other than WORK follows COMMIT

SQL> BEGIN
  2    COMMIT WORK;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    COMMIT WORK RELEASE;
  3  END;
  4  /
  COMMIT WORK RELEASE;
              *
ERROR at line 2:
ORA-06550: line 2, column 15:
PL/SQL: ORA-02185: a token other than WORK follows COMMIT
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored

SQL>

在Pro*C中 COMMIT WORK 会提交事务并释放所有的锁定及其资源，而COMMIT WORK RELEASE会提交事务并释放所有的锁定及其资源
然后断开与数据库的连接，以后所有的与数据库操作的命令都会报ORA-01012错误。

看如下一个简单的Pro*C程序：

[oracle@huateng c]$ cat commit.pc
#include
#include

exec sql include sqlca;

void main()
{
  exec sql begin declare section;
     char *username="scott";
     char *password="tiger";
     char ename[30];
  exec sql end   declare section;

  exec sql connect :username identified by :password;

  exec sql commit work;
  exec sql select ename into :ename from emp where empno=7788;
  printf("sqlcode=[%d],ename=[%s]\n",sqlca.sqlcode,ename);

  exec sql commit work release;
  exec sql select ename into :ename from emp where empno=7839;
  printf("sqlcode=[%d],ename=[%s]\n",sqlca.sqlcode,ename);

  exec sql connect :username identified by :password;
  exec sql select ename into :ename from emp where empno=7844;
  printf("sqlcode=[%d],ename=[%s]\n",sqlca.sqlcode,ename);
  return;
}

[oracle@huateng c]$ proc parse=none commit.pc

Pro*C/C++: Release 11.2.0.1.0 - Production on Mon Jan 9 21:05:47 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

System default option values taken from: /appsdata/oracle/product/db11gr2/precomp/admin/pcscfg.cfg

[oracle@huateng c]$ gcc  commit.c -o commit -lclntsh  -L $ORACLE_HOME/lib
[oracle@huateng c]$ ./commit
sqlcode=[0],ename=[SCOTT                        ]
sqlcode=[-1012],ename=[SCOTT                        ]
sqlcode=[0],ename=[TURNER                       ]
[oracle@huateng c]$ oerr ora 1012
01012, 00000, "not logged on"
// *Cause:
// *Action:

可以看到第一个SELECT 语句能正常运行，第二个SQL语句就报ORA-01012错误，重新登录后，第三个SELECT又可以正常运行。

[oracle@huateng c]$

SQL> select empno,ename from emp order by 1;

     EMPNO ENAME
---------- ------------------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

SQL>