{"id":106,"date":"2024-03-26T11:20:20","date_gmt":"2024-03-26T11:20:20","guid":{"rendered":"https:\/\/mein-it-wissen.net\/?p=106"},"modified":"2024-03-26T11:20:20","modified_gmt":"2024-03-26T11:20:20","slug":"split-und-schleifenverarbeitung-ueber-varchar-index-variable","status":"publish","type":"post","link":"https:\/\/mein-it-wissen.net\/?p=106","title":{"rendered":"Split und Schleifenverarbeitung \u00fcber varchar Index Variable"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>declare \r\ntype t_array is table of varchar2(100);\r\nv_array t_array;\r\ntype t_collection is table of number index by varchar2(30);\r\nv_pliste t_collection;\r\nv_key varchar2(30);\r\n\r\n-- Funktion innerhalb des anonymen Blocks\r\nfunction split_string(p_string varchar2, p_delimiter varchar2) return t_array is\r\n  v_array t_array := t_array();\r\n  v_start_pos number := 1;\r\n  v_end_pos number;\r\nbegin\r\n  loop\r\n    v_end_pos := instr(p_string, p_delimiter, v_start_pos);\r\n    exit when v_end_pos = 0;\r\n    \r\n    v_array.extend;\r\n    v_array(v_array.count) := substr(p_string, v_start_pos, v_end_pos - v_start_pos);\r\n    \r\n    v_start_pos := v_end_pos + 1;\r\n  end loop;\r\n  \r\n  v_array.extend;\r\n  v_array(v_array.count) := substr(p_string, v_start_pos);\r\n  \r\n  return v_array;\r\nend;\r\n\r\nbegin\r\n  for c in (\r\n    SELECT table_name FROM all_tables where owner = 'STAGE'\r\n    and table_name like 'STG_%' order by 1\r\n  ) loop\r\n    v_array := split_string(c.table_name,'_');\r\n    --zweites Element herauskopieren und in Collection speichern:\r\n    if not v_pliste.exists(v_array(2)) then \r\n      v_pliste(v_array(2)) := 1;\r\n    end if;\r\n    \r\n  end loop;\r\n  \r\n  --Erstes Element speichern:\r\n  v_key := v_pliste.first;\r\n  \r\n  --Liste ausgeben:\r\n  while v_key is not null loop\r\n    dbms_output.put_line(v_key);\r\n    v_key := v_pliste.NEXT(v_key);\r\n  end loop;\r\n  \r\nend;\r\n\/<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,4],"tags":[17,19,20],"class_list":["post-106","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","tag-pl-sql","tag-pl-sql-split-collection","tag-split"],"_links":{"self":[{"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=\/wp\/v2\/posts\/106","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=106"}],"version-history":[{"count":1,"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=\/wp\/v2\/posts\/106\/revisions"}],"predecessor-version":[{"id":107,"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=\/wp\/v2\/posts\/106\/revisions\/107"}],"wp:attachment":[{"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=106"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=106"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}