\u51e0\u5e74\u4ee5\u524d\u6709\u670b\u53cb\u95ee\u8fc7\u7c7b\u4f3c\u7684\u95ee\u9898\uff0c\u5f53\u65f6\u8003\u8651\u4e86\u4e00\u4e0b\uff0c\u6ca1\u6709\u60f3\u5230\u597d\u7684\u89e3\u51b3\u65b9\u6cd5\uff0c\u524d\u4e9b\u5929\u6709\u5ba2\u6237\u8be2\u95ee\u540c\u6837\u7684\u95ee\u9898\uff0c\u6ca1\u6709\u529e\u6cd5\u5468\u672b\u4ed4\u7ec6\u7422\u78e8\u4e86\u4e00\u4e0b\uff0c\u603b\u7b97\u662f\u627e\u5230\u4e00\u4e2a\u89e3\u51b3\u7684\u65b9\u6cd5\u3002
\n\u5176\u5b9e\u73b0\u5728Oracle\u6709\u4e13\u95e8\u7684\u5de5\u5177\u53ef\u4ee5\u89e3\u51b3\u8fd9\u4e2a\u95ee\u9898\uff0c\u5c31\u662fOracle\u7684FireWall\uff0c\u901a\u8fc7\u76f4\u8fde\u7684\u914d\u7f6e\u65b9\u5f0f\u53ef\u4ee5\u963b\u585e\u9884\u914d\u7f6e\u597d\u7684FOR UPDATE\u64cd\u4f5c\uff0c\u4e0d\u8fc7\u90a3\u9700\u8981\u5355\u72ec\u7684\u8f6f\u4ef6\u3002\u8fd9\u91cc\u4e3b\u8981\u65b9\u6848\u662f\u8981\u901a\u8fc7\u6570\u636e\u5e93\u73b0\u6709\u7684\u529f\u80fd\u5b9e\u73b0\u8fd9\u4e2a\u76ee\u6807\u3002
\n\u9650\u5236FOR UPDATE\u662f\u6709\u5b9e\u9645\u610f\u4e49\u7684\uff0c\u6709\u65f6\u5019\u53ea\u5e0c\u671b\u7ed9\u7528\u6237\u5206\u914d\u67e5\u8be2\u6743\u9650\uff0c\u4f46\u662f\u4e00\u65e6\u5206\u914d\u4e86SELECT\u6743\u9650\u540e\uff0c\u7528\u6237\u5c31\u81ea\u52a8\u62e5\u6709\u4e86FOR UPDATE\u80fd\u529b\uff0c\u867d\u7136\u7528\u6237\u5e76\u6ca1\u6709\u771f\u6b63UPDATE\u7684\u6743\u9650\uff0c\u4f46\u662f\u4ecd\u7136\u53ef\u4ee5\u5c06\u8868\u7684\u8bb0\u5f55\u9501\u5b9a\uff0c\u800c\u8fd9\u6709\u65f6\u5019\u5e76\u4e0d\u662f\u6240\u671f\u671b\u7684\u3002<\/p>\n
SQL> conn test\/test\r\nConnected.\r\nSQL> create table t_update (id number, name varchar2(30));\r\nTable created.\r\nSQL> insert into t_update values (1, 'a');\r\n1 row created.\r\nSQL> commit;\r\nCommit complete.\r\nSQL> create user u1 identified by u1; \r\nUser created.\r\nSQL> grant create session to u1;\r\nGrant succeeded.\r\nSQL> grant select on t_update to u1;\r\nGrant succeeded.<\/pre>\n\u5207\u6362\u5230U1\u7528\u6237\uff0c\u73b0\u5728\u53ef\u4ee5\u5bf9T_UPDATE\u8fdb\u884cSELECT FOR UPDATE\u64cd\u4f5c\uff1a<\/p>\n
SQL> conn u1\/u1\r\nConnected.\r\nSQL> set sqlp 'SQL2> '\r\nSQL2> select * from test.t_update where id = 1 for update;\r\n ID NAME\r\n---------- ------------------------------\r\n 1 a<\/pre>\n\u4e3a\u4e86\u907f\u514dFOR UPDATE\u64cd\u4f5c\uff0c\u53ef\u4ee5\u5c01\u88c5\u4e00\u5c42\u89c6\u56fe\u3002<\/p>\n
SQL> create view v_update as select * from t_update;\r\nView created.\r\nSQL> grant select on v_update to u1;\r\nGrant succeeded.<\/pre>\n\u4f46\u662f\u5982\u679c\u4ec5\u662f\u89c6\u56fe\uff0c\u90a3\u4e48\u6ca1\u6709\u4efb\u4f55\u4f5c\u7528\uff0cFOR UPDATE\u64cd\u4f5c\u540c\u6837\u53ef\u4ee5\u5bf9\u5355\u8868\u67e5\u8be2\u7684\u89c6\u56fe\u6267\u884c\uff1a<\/p>\n
SQL2> select * from test.v_update where id = 1 for update;\r\n ID NAME\r\n---------- ------------------------------\r\n 1 a<\/pre>\n\u5982\u679c\u6dfb\u52a0ROWNUM\u7b49\u4f2a\u5217\uff0c\u53ef\u4ee5\u907f\u514d\u76f4\u63a5FOR UPDATE\uff1a<\/p>\n
SQL> create or replace view v_update as select rownum rn, a.* from t_update a;\r\nView created.<\/pre>\n\u4f46\u662f\u5982\u679cFOR UPDATE\u6307\u5b9aROWNUM\u4f2a\u5217\u5916\u7684\u771f\u5b9e\u5217\uff0c\u8fd8\u662f\u53ef\u4ee5\u7ed5\u5f00\uff1a<\/p>\n
SQL2> select * from test.v_update where id = 1 for update; \r\nselect * from test.v_update where id = 1 for update\r\n *\r\nERROR at line 1:\r\nORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.\r\nSQL2> select id, name from test.v_update where id = 1 for update;\r\n ID NAME\r\n---------- ------------------------------\r\n 1 a<\/pre>\n\u901a\u8fc7\u62a5\u9519\u4fe1\u606f\u53ef\u4ee5\u770b\u51fa\uff0c\u5982\u679c\u5305\u542b\u4e86GROUP BY\u6216DISTINCT\u5c31\u53ef\u4ee5\u963b\u6b62FOR UPDATE\u64cd\u4f5c\uff1a<\/p>\n
SQL> create or replace view v_update as select distinct * from t_update;\r\nView created.<\/pre>\n\u4f46\u662f\u8fd9\u79cd\u65b9\u5f0f\u65e0\u7591\u4f1a\u5e26\u6765\u6027\u80fd\u95ee\u9898\uff0c\u66f4\u91cd\u8981\u7684\u662f\uff0c\u5982\u679c\u8868\u4e2d\u5b58\u5728\u91cd\u590d\u8bb0\u5f55\uff0c\u90a3\u4e48DISTINCT\u64cd\u4f5c\u4f1a\u4f7f\u5f97\u91cd\u590d\u8bb0\u5f55\u4e22\u5931\u3002
\n\u800c\u6700\u597d\u7684\u89e3\u51b3\u65b9\u6cd5\u662f\u91c7\u7528UNION ALL\u65b9\u5f0f\u521b\u5efa\u89c6\u56fe\uff1a<\/p>\n
SQL> create or replace view v_update as \r\n2 select * from t_update\r\n3 union all\r\n4 select * from t_update where 1 = 2;\r\nView created.<\/pre>\n\u73b0\u5728\u5c31\u8fbe\u5230\u4e86\u963b\u6b62FOR UPDATE\u7684\u64cd\u4f5c\uff0c\u4e14\u5bf9\u4e8e\u67e5\u8be2\u57fa\u8868\u7684\u6027\u80fd\u5f71\u54cd\u6700\u5c0f\uff1a<\/p>\n
SQL2> select * from test.v_update where id = 1 for update;\r\nselect * from test.v_update where id = 1 for update\r\n*\r\nERROR at line 1:\r\nORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.<\/pre>\n\u5bf9\u4e8e\u4e0d\u5e0c\u671b\u7528\u6237\u6267\u884cFOR UPDATE\u64cd\u4f5c\u7684\u8868\uff0c\u53ef\u4ee5\u521b\u5efa\u6210UNION ALL\u89c6\u56fe\uff0c\u5e76\u5c06\u89c6\u56fe\u7684\u67e5\u8be2\u6743\u9650\u6388\u6743\u7ed9\u7528\u6237\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u51e0\u5e74\u4ee5\u524d\u6709\u670b\u53cb\u95ee\u8fc7\u7c7b\u4f3c\u7684\u95ee\u9898\uff0c\u5f53\u65f6\u8003\u8651\u4e86\u4e00\u4e0b\uff0c\u6ca1\u6709\u60f3\u5230\u597d\u7684\u89e3\u51b3\u65b9\u6cd5\uff0c\u524d\u4e9b\u5929\u6709\u5ba2\u6237\u8be2\u95ee\u540c\u6837\u7684\u95ee\u9898\uff0c\u6ca1\u6709\u529e\u6cd5\u5468\u672b\u4ed4\u7ec6\u7422\u78e8\u4e86\u4e00\u4e0b\uff0c\u603b\u7b97\u662f\u627e\u5230\u4e00\u4e2a\u89e3\u51b3\u7684\u65b9\u6cd5\u3002 \u5176\u5b9e\u73b0\u5728Oracle\u6709\u4e13\u95e8\u7684\u5de5\u5177\u53ef\u4ee5\u89e3\u51b3\u8fd9\u4e2a\u95ee\u9898\uff0c\u5c31\u662fOracle\u7684FireWall\uff0c\u901a\u8fc7\u76f4\u8fde\u7684\u914d\u7f6e\u65b9\u5f0f\u53ef\u4ee5\u963b\u585e\u9884\u914d\u7f6e\u597d\u7684FOR UPDATE\u64cd\u4f5c\uff0c\u4e0d\u8fc7\u90a3\u9700\u8981\u5355\u72ec\u7684\u8f6f\u4ef6\u3002\u8fd9\u91cc\u4e3b\u8981\u65b9\u6848\u662f\u8981\u901a\u8fc7\u6570\u636e\u5e93\u73b0\u6709\u7684\u529f\u80fd\u5b9e\u73b0\u8fd9\u4e2a\u76ee\u6807\u3002 \u9650\u5236FOR UPDATE\u662f\u6709\u5b9e\u9645\u610f\u4e49\u7684\uff0c\u6709\u65f6\u5019\u53ea\u5e0c\u671b\u7ed9\u7528\u6237\u5206\u914d\u67e5\u8be2\u6743\u9650\uff0c\u4f46\u662f\u4e00\u65e6\u5206\u914d\u4e86SELECT\u6743\u9650\u540e\uff0c\u7528\u6237\u5c31\u81ea\u52a8\u62e5\u6709\u4e86FOR UPDATE\u80fd\u529b\uff0c\u867d\u7136\u7528\u6237\u5e76\u6ca1\u6709\u771f\u6b63UPDATE\u7684\u6743\u9650\uff0c\u4f46\u662f\u4ecd\u7136\u53ef\u4ee5\u5c06\u8868\u7684\u8bb0\u5f55\u9501\u5b9a\uff0c\u800c\u8fd9\u6709\u65f6\u5019\u5e76\u4e0d\u662f\u6240\u671f\u671b\u7684\u3002 SQL> conn test\/test Connected. SQL> create table t_update (id number, name varchar2(30)); Table created. SQL> insert into t_update values (1, ‘a’); 1 row created. SQL> commit; Commit complete. SQL> create user u1 identified by … Continue reading