{"id":114,"date":"2021-07-08T22:24:04","date_gmt":"2021-07-08T14:24:04","guid":{"rendered":"http:\/\/139.196.75.244\/?p=114"},"modified":"2021-07-08T22:24:04","modified_gmt":"2021-07-08T14:24:04","slug":"%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e5%88%9b%e5%bb%ba","status":"publish","type":"post","link":"https:\/\/www.tianjm.cn\/?p=114","title":{"rendered":"\u5b58\u50a8\u8fc7\u7a0b\u521b\u5efa"},"content":{"rendered":"\n<ol><li>CREATE OR REPLACE PROCEDURE \u201c\u5b58\u50a8\u8fc7\u7a0b\u540d\u79f0\u201d(\u53c2\u65701 IN VARCHAR2,\u53c2\u65702 IN VARCHAR2,\u53c2\u65703 OUT INTEGER) is<\/li><li>PROCEDURE \u201c\u5b58\u50a8\u8fc7\u7a0b\u540d\u79f0\u201d(\u53c2\u65701 IN VARCHAR2,\u53c2\u65702 IN VARCHAR2,\u53c2\u65703 OUT INTEGER) is<\/li><\/ol>\n\n\n\n<pre class=\"wp-block-verse\">CREATE OR REPLACE PROCEDURE \u201c\u5b58\u50a8\u8fc7\u7a0b\u540d\u79f0\u201d(\u53c2\u65701 IN VARCHAR2,\u53c2\u65702 IN VARCHAR2,\u53c2\u65703 OUT INTEGER) IS\n--\u5b9a\u4e49\u53d8\u91cf\nV_1 <strong>VARCHAR<\/strong>(100);\n--\u5b9a\u4e49\u53d8\u91cf\u5e76\u8d4b\u503c\nV_2 <strong>VARCHAR<\/strong>(32767) : = '\u5185\u5bb9';\n--\u5b9a\u4e49\u53d8\u91cf\nV_3 <strong>NUMBER<\/strong>(10);\n--\u5b9a\u4e49\u53d8\u91cf\u5e76\u8d4b\u503c\nV_4 <strong>NUMBER<\/strong>(10)\uff1a=0;\n--\u5b9a\u4e49\u6e38\u6807\n<strong>TYPE <\/strong>CUR   <strong>IS  REF CURSOR<\/strong>;\nV_CHK_CUR   CUR;\n--\u5b9a\u4e49\u96c6\u5408\n<strong>TYPE<\/strong> CHK_REC IS <strong>RECORD<\/strong>(\nROW_ID <strong>ROWID<\/strong>\n,V5 <strong>NUMBER<\/strong>(1)\n,V6 <strong>NUMBER<\/strong>(1)\n,RESULT VARCHAR(100)\n);\n<strong>TYPE <\/strong>CHK_LIST_T <strong>IS TABLE OF<\/strong> CHK_REC;\nCHK_LIST CHK_LIST_T;\n--\u5b9a\u4e49\u6e38\u6807\nCURSOR CUR_V7 IS SELECT * FROM A;\nROW_INDEX CUR_V7%ROWTYPE\nBEGIN\n--\u67e5\u8be2\u6570\u636e\u5e76\u7528\u6e38\u6807\u4fbf\u5229 \u4ee3\u7801\u57571\u5f00\u59cb\nV_2 \uff1a=\u201cSELECT ROW_ID ,V5,V6,RESULT    FROM VIEW\u201d; --\u67e5\u8be2\u6709\u95ee\u9898\u7684\u6570\u636e\nOPEN V_CHK_CUR   FOR V_2 ;--\u6253\u5f00\u6e38\u6807\uff0c\u4ee3\u7801\u57572\u5f00\u59cb\nLOOP\nFETCH V_CHK_CUR   BULK COLLECT INTO CHK_LIST LIMT 1000;--\u5faa\u73af\u904d\u5386\uff0c\u4e00\u6b21\u53d6\u51fa1000\u6761\u6570\u636e\nEXIT WHEN CHK_LIST.COUNT =0;--\u5982\u679c\u6ca1\u6570\u636e\u4e86\uff0c\u5c31\u9000\u51fa\u5faa\u73af\u3002\n\nFOR idx IN 1 .. CHK_LIST.COUNT LOOP --\u5faa\u73af1000\u6761\u6570\u636e \uff0c\u4ee3\u7801\u57573\u5f00\u59cb\n\nCHK_LIST(idx).RESULT\uff1a=CHK_LIST(idx).RESULT || CASE CHK_LIST(idx).V5 WHEN 1 THEN '\u6761\u4ef6\u63cf\u8ff01' ELSE \u2018\u6761\u4ef6\u63cf\u8ff02\u2019 END\uff1b\n\nEND LOOP;--\u5faa\u73af1000\u6761\u6570\u636e \uff0c\u4ee3\u7801\u57573\u7ed3\u675f\n\nBEGIN--\u904d\u5386\u66f4\u65b0\u5230\u76ee\u6807\u8868\nFORALL IDX IN 1.. CHK_LIST.COUNT\nUPDATE VIEW_A SET B = CHK_LIST(idx).RESULT\nWHERE ROWID =  CHK_LIST(idx).ROW_ID;\nEND;--\u904d\u5386\u66f4\u65b0\u5230\u76ee\u6807\u8868\n\n\n\n\nEND LOOP;--\u4ee3\u7801\u57572\u7ed3\u675f\n--\u4ee3\u7801\u57571\u7ed3\u675f\nEND;\n<\/pre>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>CREATE OR REPLACE PROCEDURE \u201c\u5b58\u50a8\u8fc7\u7a0b\u540d&#46;&#46;&#46;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[8],"tags":[],"_links":{"self":[{"href":"https:\/\/www.tianjm.cn\/index.php?rest_route=\/wp\/v2\/posts\/114"}],"collection":[{"href":"https:\/\/www.tianjm.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tianjm.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tianjm.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tianjm.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=114"}],"version-history":[{"count":1,"href":"https:\/\/www.tianjm.cn\/index.php?rest_route=\/wp\/v2\/posts\/114\/revisions"}],"predecessor-version":[{"id":115,"href":"https:\/\/www.tianjm.cn\/index.php?rest_route=\/wp\/v2\/posts\/114\/revisions\/115"}],"wp:attachment":[{"href":"https:\/\/www.tianjm.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=114"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tianjm.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=114"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tianjm.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=114"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}