{"id":1103,"date":"2017-10-15T16:21:48","date_gmt":"2017-10-15T08:21:48","guid":{"rendered":"http:\/\/www.821121.com\/?p=1103"},"modified":"2020-12-15T16:49:46","modified_gmt":"2020-12-15T08:49:46","slug":"%e6%9f%a5%e8%af%a2%e7%bb%93%e6%9e%9c%e5%af%bc%e5%87%bacsv%e6%96%87%e4%bb%b6","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=1103","title":{"rendered":"\u67e5\u8be2\u7ed3\u679c\u5bfc\u51faCSV\u6587\u4ef6"},"content":{"rendered":"<p>Oracle\u5bfc\u51faCSV\u6587\u4ef6<\/p>\n<p>1\uff09\u5efa\u7acb\u5b58\u50a8\u8fc7\u7a0b<\/p>\n<pre><code class=\"language-sql\">CREATE OR REPLACE PROCEDURE SQL_TO_CSV\n\n(\n\nP_QUERY IN VARCHAR2,--PLSQL\u6587\n\nP_DIR IN VARCHAR2,--\u5bfc\u51fa\u7684\u6587\u4ef6\u653e\u7f6e\u76ee\u5f55\n\nP_FILENAME IN VARCHAR2--CSV\u540d\n\n)\n\nIS\n\nL_OUTPUT UTL_FILE.FILE_TYPE;\n\nL_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;\n\nL_COLUMNVALUE VARCHAR2(4000);\n\nL_STATUS INTEGER;\n\nL_COLCNT NUMBER := 0;\n\nL_SEPARATOR VARCHAR2(1);\n\nL_DESCTBL DBMS_SQL.DESC_TAB;\n\nP_MAX_LINESIZE NUMBER := 32000;\n\nBEGIN\n\n--OPEN FILE\n\nL_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, &#039;W&#039;, P_MAX_LINESIZE);\n\n--DEFINE DATE FORMAT\n\nEXECUTE IMMEDIATE &#039;ALTER SESSION SET NLS_DATE_FORMAT=&#039;&#039;YYYY-MM-DD HH24:MI:SS&#039;&#039;&#039;;\n\n--OPEN CURSOR\n\nDBMS_SQL.PARSE( L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE );\n\nDBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL );\n\n--DUMP TABLE COLUMN NAME\n\nFOR I IN 1 .. L_COLCNT LOOP\n\nUTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || &#039;\u201d&#039; || L_DESCTBL(I).COL_NAME || &#039;\u201d&#039; );\n\nDBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE, 4000 );\n\nL_SEPARATOR := &#039;,&#039;;\n\nEND LOOP;\n\nUTL_FILE.NEW_LINE( L_OUTPUT );\n\n--EXECUTE THE QUERY STATEMENT\n\nL_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);\n\n--DUMP TABLE COLUMN VALUE\n\nWHILE ( DBMS_SQL.FETCH_ROWS(L_THECURSOR) &gt; 0 ) LOOP\n\nL_SEPARATOR := &#039;&#039;;\n\nFOR I IN 1 .. L_COLCNT LOOP\n\nDBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );\n\nUTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || &#039;\u201d&#039; ||\n\nTRIM(BOTH &#039; &#039; FROM REPLACE(L_COLUMNVALUE,&#039;\u201d&#039;,&#039;\u201d\u201d&#039;)) || &#039;\u201d&#039;);\n\nL_SEPARATOR := &#039;,&#039;;\n\nEND LOOP;\n\nUTL_FILE.NEW_LINE( L_OUTPUT );\n\nEND LOOP;\n\n--CLOSE CURSOR\n\nDBMS_SQL.CLOSE_CURSOR(L_THECURSOR);\n\n--CLOSE FILE\n\nUTL_FILE.FCLOSE( L_OUTPUT );\n\nEXCEPTION\n\nWHEN OTHERS THEN\n\nRAISE;\n\nEND;\n\/<\/code><\/pre>\n<p>2\uff09\u521b\u5efa\u5b58\u653e\u6587\u4ef6\u7684\u76ee\u5f55<\/p>\n<pre><code class=\"language-sql\">CREATE OR REPLACE DIRECTORY MYDIR AS &#039;\/u01\/app\/oracle&#039;;\ngrant read,write on DIRECTORY MYDIR to public;<\/code><\/pre>\n<p>3\uff09\u6267\u884c\u5757<\/p>\n<pre><code class=\"language-sql\">begin\nsql_to_csv(&#039;select * from dual&#039;,&#039;MYDIR&#039;,&#039;dbs_segments.CSV&#039;);\nend;\n\/<\/code><\/pre>\n<p>4\uff09\u9a8c\u8bc1<br \/>\n<img decoding=\"async\" src=\"\/wp-content\/uploads\/2020\/12\/image-1608022126478.png\" alt=\"file\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle\u5bfc\u51faCSV\u6587\u4ef6 1\uff09\u5efa\u7acb\u5b58\u50a8\u8fc7\u7a0b CR<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,34],"tags":[],"class_list":["post-1103","post","type-post","status-publish","format-standard","hentry","category-oracle-11g","category-34"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1103","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1103"}],"version-history":[{"count":1,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1103\/revisions"}],"predecessor-version":[{"id":1109,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1103\/revisions\/1109"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1103"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}