Pro*C中COMMIT WORK 和 COMMIT WORK RELEASE的区别
发布网友
发布时间：2022-04-25 00:26
共1个回答
热心网友
时间：2023-10-17 12:23
譬如：
[oracle@huateng c]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Proction 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 Proction
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 procere 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 <stdio.h>
#include <string.h>
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 - Proction 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/proct/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>
