{"id":3137,"date":"2019-07-05T22:50:12","date_gmt":"2019-07-05T20:50:12","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3137"},"modified":"2019-07-05T22:50:12","modified_gmt":"2019-07-05T20:50:12","slug":"handy-plsql-programme","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3137","title":{"rendered":"Handy PLSQL programme"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">This programme uses PLSQL to select a set of 1000 records per item. Each item is calculated from a cursor that generates the items for which a set of records is calculated.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So the programme can be written in pseudo code as:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>For i:=1 to NumberItem<\/li><li>calculate set <\/li><li>end Loop<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">The programme looks like:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>create or replace PROCEDURE SP_VEXISTABLA2\n IS\n stmt varchar2(1000);\n TELLER NUMBER(3);\n     --Declare the cursor\n CURSOR Product IS\n       select distinct  \"Steering View Deal ListOfValue\" from DCL_FINAL_OUTCOME;\n     Target  DCL_FINAL_OUTCOME.\"Steering View Deal ListOfValue\"%TYPE;\nbegin\nstmt:= 'drop table DCL_DT_CORE_DAX0329_ALL';   \nSELECT COUNT(*) INTO teller FROM user_tables WHERE table_name = UPPER('DCL_DT_CORE_DAX0329_ALL');\nIF (teller>0) THEN \n   execute immediate stmt;\n END IF;\nstmt:= 'create table DCL_DT_CORE_DAX0329_ALL as select * from DCL_FINAL_OUTCOME where 1=0';\n execute immediate stmt;\n \nOPEN Product;\n--Begin the Indefinite LOOP\nLOOP\n    --Retrieve the CURSOR data into the holding object\n    FETCH Product INTO Target;\ndbms_output.put_line('Verwerkt product: '|| Target);        \n    --Test whether a record was found\n    EXIT WHEN Product%NOTFOUND;\n\n  stmt := q'!INSERT INTO  DCL_DT_CORE_DAX0329_!' ||'ALL'|| q'! \n    SELECT DISTINCT * from\n            (select * FROM  DCL_FINAL_OUTCOME\n            where trim(\"Steering View Deal ListOfValue\")=trim('!' ||Target||q'!') and mandant=309\n            ORDER BY dbms_random.value)\n            WHERE\n            rownum &lt;= 100\n\n\n            UNION ALL\n\n            SELECT DISTINCT  * from\n            (select *  FROM  DCL_FINAL_OUTCOME\n            where trim(\"Steering View Deal ListOfValue\")=trim('!' ||Target||q'!') and mandant!=309\n            ORDER BY dbms_random.value)\n            WHERE\n            rownum &lt;= 900!';\ndbms_output.put_line(stmt);\n execute immediate stmt;\n END LOOP;\n\n--Close the CURSOR\nCLOSE Product;\ncommit;\nEXCEPTION\n     WHEN OTHERS THEN\n       dbms_output.put_line('Oops foutje');  \n       dbms_output.put_line('SQL Code '||SQLCODE);  \n       dbms_output.put_line('Message  '||SQLERRM); \nend;\n \/\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"> <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This programme uses PLSQL to select a set of 1000 records per item. Each item is calculated from a cursor that generates the items for which a set of records is calculated. So the programme can be written in pseudo code as: For i:=1 to NumberItem calculate set end Loop The programme looks like:<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3137","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3137","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3137"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3137\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3137"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}