{"id":12,"date":"2011-12-26T17:03:38","date_gmt":"2011-12-26T09:03:38","guid":{"rendered":"http:\/\/www.royalwzy.com\/?p=12"},"modified":"2013-12-30T11:07:29","modified_gmt":"2013-12-30T03:07:29","slug":"%e8%84%9a%e6%9c%ac%e5%b7%a5%e5%85%b701-%e6%8a%8a%e6%9f%90%e4%b8%80%e4%b8%aa%e5%ad%97%e7%ac%a6%e4%b8%b2%e6%8c%89%e7%85%a7%e6%8c%87%e5%ae%9a%e7%9a%84%e5%88%86%e9%9a%94%e7%ac%a6%e5%88%86%e9%9a%94","status":"publish","type":"post","link":"http:\/\/www.royalwzy.com\/?p=12","title":{"rendered":"\u811a\u672c\u5de5\u517701–\u628a\u67d0\u4e00\u4e2a\u5b57\u7b26\u4e32\u6309\u7167\u6307\u5b9a\u7684\u5206\u9694\u7b26\u5206\u9694,\u5e76\u4ee5Table\u5f62\u5f0f\u8fd4\u56de"},"content":{"rendered":"
\n
CREATE<\/span>\u00a0OR<\/span>\u00a0REPLACE<\/span>\u00a0TYPE<\/span>\u00a0t_Table<\/span>\u00a0IS<\/span>\u00a0TABLE<\/span>\u00a0OF<\/span>\u00a0VARCHAR2<\/span>(<\/span>4000<\/span>);<\/span><\/div>\n
\u00a0<\/span><\/div>\n
CREATE<\/span>\u00a0OR<\/span>\u00a0REPLACE<\/span>\u00a0FUNCTION<\/span>\u00a0FN_SplitStringByDelimiter<\/span>(<\/span>p_string<\/span>\u00a0IN<\/span>\u00a0VARCHAR2<\/span>,<\/span>\u00a0p_delimiter<\/span>\u00a0IN<\/span>\u00a0VARCHAR2<\/span>)<\/span><\/div>\n
RETURN<\/span>\u00a0t_Table<\/span>\u00a0PIPELINED<\/span>\u00a0AS<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0v_delimiter_index<\/span>\u00a0INTEGER<\/span>;<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0v_start_index<\/span>\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0INTEGER<\/span>\u00a0:=<\/span>\u00a01<\/span>;<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0v_table<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0VARCHAR2<\/span>(<\/span>4000<\/span>);<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0v_delimiter_length<\/span>\u00a0INTEGER<\/span>\u00a0:=<\/span>\u00a0LENGTH<\/span>(<\/span>p_delimiter<\/span>);<\/span><\/div>\n
\/*<\/span><\/div>\n
\u00a0<\/span>\u00a0\u529f\u80fd<\/span>:<\/span>\u628a\u67d0\u4e00\u4e2a\u5b57\u7b26\u4e32\u6309\u7167\u6307\u5b9a\u7684\u5206\u9694\u7b26\u5206\u9694<\/span>,<\/span>\u5e76\u4ee5<\/span>Table<\/span>\u5f62\u5f0f\u8fd4\u56de<\/span><\/div>\n
*\/<\/span><\/div>\n
BEGIN<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0LOOP<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0—<\/span>\u00a0\u67e5\u627e\u5f53\u524d\u5206\u9694\u7b26\u7684\u4f4d\u7f6e<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0v_delimiter_index<\/span>\u00a0:=<\/span>\u00a0INSTR<\/span>(<\/span>p_string<\/span>\u00a0||<\/span>\u00a0p_delimiter<\/span>,<\/span>\u00a0p_delimiter<\/span>,<\/span>\u00a0v_start_index<\/span>);<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0—<\/span>\u00a0\u5982\u679c\u5728\u5b57\u7b26\u4e32\u627e\u4e0d\u5230\u5b57\u4e32\u5219\u8fd4\u56de<\/span>,<\/span>\u9000\u51fa\u5faa\u73af<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0EXIT<\/span>\u00a0WHEN<\/span>\u00a0v_delimiter_index<\/span>\u00a0=<\/span>\u00a00<\/span>;<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0—<\/span>\u00a0\u5f97\u5230\u5206\u9694\u7b26\u524d\u7684\u503c<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0v_table<\/span>\u00a0:=<\/span>\u00a0SUBSTR<\/span>(<\/span>p_string<\/span>,<\/span>\u00a0v_start_index<\/span>,<\/span>\u00a0v_delimiter_index<\/span>\u00a0–<\/span>\u00a0v_start_index<\/span>);<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0IF<\/span>\u00a0v_table<\/span>\u00a0IS<\/span>\u00a0NOT<\/span>\u00a0NULL<\/span>\u00a0THEN<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0—<\/span>\u00a0\u8fd4\u56de\u8be5\u96c6\u5408\u7684\u5355\u4e2a\u5143\u7d20<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0PIPE<\/span>\u00a0ROW<\/span>(<\/span>v_table<\/span>);<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0END<\/span>\u00a0IF<\/span>;<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0—<\/span>\u00a0\u68c0\u7d22\u4f4d\u7f6e\u8bbe\u7f6e\u4e3a\u5f53\u524d\u5206\u9694\u7b26\u7684\u4e0b\u4e00\u4e2a<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0v_start_index<\/span>\u00a0:=<\/span>\u00a0v_delimiter_index<\/span>\u00a0+<\/span>\u00a0v_delimiter_length<\/span>;<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0END<\/span>\u00a0LOOP<\/span>;<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0—<\/span>\u00a0\u5fc5\u987b\u4ee5\u4e00\u4e2a\u7a7a\u7684<\/span>RETURN<\/span>\u00a0\u8bed\u53e5\u7ed3\u675f<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/span>\u00a0RETURN<\/span>;<\/span><\/div>\n
END<\/span>\u00a0FN_SplitStringByDelimiter<\/span>;<\/span><\/div>\n
\u00a0<\/span><\/div>\n
\u00a0<\/span><\/div>\n
\u00a0<\/span><\/div>\n
\u8c03\u7528\u65b9\u6cd5<\/span>:SELECT<\/span>\u00a0column_value\u00a0FROM<\/span>\u00a0TABLE<\/span>(<\/span>FN_SplitStringByDelimiter<\/span>\u00a0(<\/span>‘a.b.c.d.e’<\/span>,<\/span>\u00a0‘.’<\/span>));<\/span><\/div>\n
SELECT<\/span>\u00a0column_value\u00a0FROM<\/span>\u00a0TABLE<\/span>(<\/span>FN_SplitStringByDelimiter<\/span>\u00a0(<\/span>‘123-;456-;789-;000’<\/span>,<\/span>\u00a0‘-;’<\/span>));<\/span><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

CREATE\u00a0OR\u00a0REPLACE\u00a0TYPE\u00a0t_Table\u00a0IS\u00a0TABLE\u00a0OF\u00a0VARCHAR2(400 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[34,2],"class_list":["post-12","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle","tag-oracle_basics"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/12","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=12"}],"version-history":[{"count":0,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/12\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=12"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=12"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=12"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}