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