{"id":6018,"date":"2021-06-11T15:17:52","date_gmt":"2021-06-11T07:17:52","guid":{"rendered":"https:\/\/nj.transwarp.cn:8180\/?p=6018"},"modified":"2021-06-18T10:16:00","modified_gmt":"2021-06-18T02:16:00","slug":"sys_refcursor%e6%b8%b8%e6%a0%87%e4%bd%bf%e7%94%a8%e6%96%b9%e6%b3%95","status":"publish","type":"post","link":"https:\/\/kbwp.transwarp.cn\/?p=6018","title":{"rendered":"sys_refcursor\u6e38\u6807\u4f7f\u7528\u65b9\u6cd5"},"content":{"rendered":"<h3>\u6982\u8981\u63cf\u8ff0<\/h3>\n<hr \/>\n<p>\u5728\u5f00\u53d1\u4e2d\u6211\u9700\u8981\u5728Procedure A\u4e2d\u6253\u5f00\u4e00\u4e2a\u52a8\u6001SQL\u7684\u6e38\u6807\uff0c\u5e76\u8fd4\u56de\u6b64cursor\uff1b\u7136\u540e\u7528Procedure B\u53bbcall A\u5b58\u50a8\u8fc7\u7a0b\uff0c\u518d\u5bf9\u8fd4\u56de\u7684cursor\u8fdb\u884c\u64cd\u4f5c\uff0c\u8fd9\u4e2a\u65f6\u5019\u9700\u8981\u901a\u8fc7sys_refcursor\u6e38\u6807\u5728\u8fc7\u7a0b\u4e2d\u8fd4\u56de\u7ed3\u679c\u96c6\u3002<\/p>\n<p>\u4e0b\u9762\u7684\u4f8b\u5b50\u7b80\u5355\u4ecb\u7ecd\u4e86sys_refcursor\u7684\u4f7f\u7528\u65b9\u6cd5\u3002<\/p>\n<h3>\u8be6\u7ec6\u8bf4\u660e<\/h3>\n<hr \/>\n<p><strong>\u521b\u5efa\u6837\u4f8b\u8868\u5e76\u63d2\u5165\u6570\u636e<\/strong><\/p>\n<pre><code class=\"language-sql\">CREATE  TABLE emp_torc(\n  empno decimal(38,0) DEFAULT NULL, \n  ename string DEFAULT NULL, \n  job string DEFAULT NULL, \n  mgr decimal(38,0) DEFAULT NULL, \n  hiredate timestamp DEFAULT NULL, \n  sal decimal(38,0) DEFAULT NULL, \n  comm decimal(38,0) DEFAULT NULL, \n  deptno decimal(38,0) DEFAULT NULL\n)\nCLUSTERED BY ( \n  empno) \nINTO 3 BUCKETS\nSTORED AS ORC_TRANSACTION;\n\nBATCHINSERT INTO emp_torc BATCHVALUES(\nVALUES (7521,'WARD','SALESMAN',7698,'1981-01-22',1250,500,30),\nVALUES (7566,'JONES','MANAGER',7839,'1981-01-02',2975,NULL,20),\nVALUES (7698,'BLAKE','MANAGER',7839,'1981-01-01',2850,NULL,30),\nVALUES (7782,'CLARK','MANAGER',7839,'1981-01-09',2450,NULL,10),\nVALUES (7788,'SCOTT','ANALYST',7566,'1987-01-13',3000,NULL,20),\nVALUES (7839,'KING','PRESIDENT',NULL,'1981-01-17',5000,NULL,10),\nVALUES (7902,'FORD','ANALYST',7566,'1981-01-03',3000,NULL,20),\nVALUES (7369,'SMITH','CLERK',7902,'1980-01-17',800,NULL,20),\nVALUES (7654,'MARTIN','SALESMAN',7698,'1981-01-28',1250,1400,30),\nVALUES (7876,'ADAMS','CLERK',7788,'1987-01-13',1100,NULL,20),\nVALUES (7900,'JAMES','CLERK',7698,'1981-01-03',950,NULL,30),\nVALUES (7499,'ALLEN','SALESMAN',7698,'1981-01-20',1600,300,30),\nVALUES (7844,'TURNER','SALESMAN',7698,'1981-01-08',1500,0,30),\nVALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10)\n);<\/code><\/pre>\n<p>\u4ee5\u4e0b\u9762\u7684\u8bed\u53e5\u4e3a\u4f8b\uff1a<\/p>\n<pre><code class=\"language-sql\">CREATE OR REPLACE PROCEDURE pcx_dgzq_test_all_user(v_job in string,cur OUT sys_refcursor) is\nbegin\nopen cur for\nSELECT * FROM emp_torc WHERE job=v_job;\nend;<\/code><\/pre>\n<h4>pl\/sql\u8c03\u7528\u65b9\u5f0f<\/h4>\n<pre><code class=\"language-sql\">DECLARE\n    V_SYS_REFCURSOR_ROWS SYS_REFCURSOR;\n    V_ROWS           DEFAULT.emp_torc%ROWTYPE;\nBEGIN\n    pcx_dgzq_test_all_user('SALESMAN', V_SYS_REFCURSOR_ROWS);\n    LOOP\n        FETCH V_SYS_REFCURSOR_ROWS\n            INTO V_ROWS;\n        EXIT WHEN V_SYS_REFCURSOR_ROWS%NOTFOUND;\n        DBMS_OUTPUT.PUT_LINE(V_ROWS.ENAME);\n    END LOOP;\n    CLOSE V_SYS_REFCURSOR_ROWS;\nEND;<\/code><\/pre>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2021\/06\/image-1623310326778.png\" alt=\"file\" \/><\/p>\n<h4>\u5b58\u50a8\u8fc7\u7a0b\u8c03\u7528\u65b9\u5f0f<\/h4>\n<pre><code class=\"language-sql\">CREATE OR REPLACE PROCEDURE pcx_dgzq_test_get_name(v_job IN STRING)\nAS \n    V_SYS_REFCURSOR_ROWS SYS_REFCURSOR;\n    V_ROWS           DEFAULT.emp_torc%ROWTYPE;\nBEGIN\n    pcx_dgzq_test_all_user(v_job, V_SYS_REFCURSOR_ROWS);\n    LOOP\n        FETCH V_SYS_REFCURSOR_ROWS\n            INTO V_ROWS;\n        EXIT WHEN V_SYS_REFCURSOR_ROWS%NOTFOUND;\n        DBMS_OUTPUT.PUT_LINE(V_ROWS.ENAME);\n    END LOOP;\n    CLOSE V_SYS_REFCURSOR_ROWS;\nEND<\/code><\/pre>\n<pre><code class=\"language-sql\">CALL pcx_dgzq_test_get_name('SALESMAN');<\/code><\/pre>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2021\/06\/image-1623395690118.png\" alt=\"file\" \/><\/p>\n<h4>jdbc\u8bbf\u95ee\u8f93\u51fa\u53c2\u6570\u7c7b\u578b\u4e3asys_refcursor\u7684\u7b80\u5355\u793a\u4f8b<\/h4>\n<blockquote>\n<p>\u8be5\u65b9\u6cd5\u540cpl\/sql\u8c03\u7528\u65b9\u5f0f\uff0c\u800c\u975e\u5b58\u50a8\u8fc7\u7a0b\u8c03\u7528\u65b9\u5f0f<\/p>\n<\/blockquote>\n<pre><code class=\"language-java\">import java.sql.*;\npublic class JDBCexample {\n    private static String driverName = \"org.apache.hive.jdbc.HiveDriver\";\n\n    public static void main(String[] args) throws SQLException {\n        try {\n            Class.forName(driverName);\n        } catch (ClassNotFoundException e) {\n            e.printStackTrace();\n            System.exit(1);\n        }\n        \/\/Hive2 JDBC URL with LDAP\n        String jdbcURL = \"jdbc:hive2:\/\/172.22.23.1:10000\/default\";\n        String user = \"hive\";\n        String password = \"123456\";\n        ResultSet rs = null;\n        Connection conn = DriverManager.getConnection(jdbcURL, user, password);\n        CallableStatement cbstmt = conn.prepareCall(\"call pcx_dgzq_test_all_user(?,?)\");\n        cbstmt.setString(1, \"SALESMAN\");\n        cbstmt.registerOutParameter(2, -10);\n        cbstmt.execute();\n        rs = (ResultSet) cbstmt.getObject(2);\n        System.out.println();\n        while (rs.next()) {\n            System.out.println(rs.getString(2));\n        }\n        rs.close();\n        cbstmt.close();\n        conn.close();\n    }\n}<\/code><\/pre>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2021\/06\/image-1623395772608.png\" alt=\"file\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u6982\u8981\u63cf\u8ff0 \u5728\u5f00\u53d1\u4e2d\u6211\u9700\u8981\u5728Procedure A\u4e2d\u6253\u5f00\u4e00\u4e2a\u52a8\u6001SQL\u7684\u6e38\u6807\uff0c\u5e76\u8fd4\u56de\u6b64cursor\uff1b\u7136\u540e\u7528Pro ..<\/p>\n<div class=\"clear-fix\"><\/div>\n<p><a href=\"https:\/\/kbwp.transwarp.cn\/?p=6018\" title=\"read more...\">Read more<\/a><\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[],"class_list":["post-6018","post","type-post","status-publish","format-standard","hentry","category-sql_issue"],"acf":[],"_links":{"self":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/6018","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6018"}],"version-history":[{"count":0,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/6018\/revisions"}],"wp:attachment":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6018"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6018"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6018"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}