-------------------------------------------------------- -- Procedure CALC_SEASONLY_BAL_PROC -------------------------------------------------------- CREATE OR REPLACE PROCEDURE calc_seasonly_bal_proc AS /****************************************************************/ /* Created: DARKMAN */ /* Purpuse: Demonstrate the main goals of Dynamic SQL */ /* through a detailed example */ /* Date: 2014.06.12. */ /****************************************************************/ --ez a "csúnya" típus a BULK COLLECT használatához kell TYPE t_trx_table_type IS TABLE OF v_incoming_trx_per_customer%ROWTYPE; --és kell egy "csúnya" típusú változó is még v_tab t_trx_table_type; c_proc_name CONSTANT VARCHAR2(18) := 'CALC_SEASONLY_BAL_PROC'; v_error_msg error_log.message%TYPE; v_count NUMBER := 0; v_actual_table VARCHAR2(30); v_act_season VARCHAR2(6); --SPRING, SUMMER, AUTUMN, WINTER v_year VARCHAR2(4); v_stmt_num NUMBER :=0; error_excp EXCEPTION; BEGIN SELECT EXTRACT (YEAR FROM SYSDATE) INTO v_year FROM DUAL; -- visszaadja hogy milyen évszak van, jelenleg ORACLE-nek csak olyan fgv-e van ami negyedévet ad vissza v_stmt_num := 10; v_act_season := func_get_season(); -- összekonkatenáljuk a két változót, hogy rövidítsük a kódot v_stmt_num := 20; v_actual_table := v_act_season || '_' || v_year; --itt pedig v_act_season alapján létrehozzuk a temporális táblánkat v_stmt_num := 30; EXECUTE IMMEDIATE 'CREATE TABLE temp_' || v_actual_table || '_transactions ( year NUMBER, season NUMBER, prod_id NUMBER, cust_id NUMBER, tran_date DATE, quantity NUMBER, points FLOAT )'; --a táblára dobunk rá GRANT-ot, majd INDEX-et futási időben, hiszen ilyet statikus SQL-ben nem lehetne v_stmt_num := 40; EXECUTE IMMEDIATE 'GRANT SELECT, INSERT ON system.temp_' || v_actual_table || '_transactions TO SYS'; --most pedig máshogy hajtjuk végre a GRANT-ot az UPDATE-re és a DELETE-re, mert a dinamikus utasításokat kétféleképp is fel tudjuk építeni, -- és hogy láthatóvá tegyem ezt a különbséget v_stmt_num := 50; EXECUTE IMMEDIATE 'GRANT UPDATE, DELETE ON system.temp_' || '(:1)' || '_' || '(:2)' || '_transactions TO SYS;' USING v_act_season, v_year; --nézzük meg a két GRANT utasításnál, hogy a ";"-ket melyiknél hova raktam. Erre figyeljünk, --hogyha csak konkatenálunk akkor a parancs string-jébe ne rakjunk ";"-őt, de ha USING-ot használunk --akkor bizony bele kell rakni, hiszen az maga egy rendes utasítás /*USING előnye: ekkor a paramétereket leellenőrzi futási időben a fordító és ha nincs vagy rossz érték van megadva akkor hibát jelez, mégpedig futási idejű hibát, hogy elszállt az adott eljárás például a '1512'-edik sorban. Ugyanis ha konkrét lekérdezés vagy annak részletei vannak belerejtve az EXECUTE IMMEDIATE paraméterének string-jébe, (pl. vki további infót akar kicsikarni a DB-ből, SQL-injection), akkor USING használata nélkül egy lekérdezés szintű hibát kapna amiben lehetnek bizonyos információk a DB struktúrájáról, sőt konkrét eredményhalmazokat is vissza lehet kapni vagy manipulálni!*/ --tegyünk rá index-et is, ha már létrejött az a tábla és tudjuk, hogy lesz benne mondjuk 20-30 ezer sor --Azért is jó így, mert nem felejtjük el létrehozni az index-(ek)et, mert így a végrehajtási logika itt tartalmazni fogja v_stmt_num := 60; EXECUTE IMMEDIATE 'CREATE INDEX temp_' || '(:1)' || '_' || '(:2)' || '_idx ON system.temp_' || '(:1)' || '_' || '(:2)' || '_transactions (year,season,cust_id) ;' --itt az index neve van összekonkatenálva :) USING v_act_season, v_year; --NOTE: itt akár létrehozhatunk PRIMARY vagy UNIQUE KEY-t, amik automatikusan UNIQUE típusú indexet is tesznek a táblára :) -- BULK COLLECT segítségével végigmegyünk a feldolgozandó halmazon -- ehhez először egy SELECT szükséges, amihez kellett a fenti "csúnya" típus -- amibe belerakjuk a feldolgozandó halmazt SELECT * BULK COLLECT INTO v_tab FROM v_incoming_trx_per_customer; --majd végigmegyünk rajta egy FOR ciklusban és a segítségével beállítjuk a megfelelő értékeket -- a másik táblán, ami a mi esetünkben épp az ügyfelek egyenlegét tárolja FORALL i IN v_tab.first .. v_tab.last EXECUTE IMMEDIATE 'UPDATE seasonal_balance SET balance = :1 WHERE cust_id = :2' USING v_tab(i).points_per_cust, v_tab(i).cust_id; --ha kész akkor DROP TABLE a temp táblára, hogy ne foglalja a helyet és ennyi v_stmt_num := 70; EXECUTE IMMEDIATE 'DROP TABLE temp_' || '(:1)' || '_' || '(:2);' USING v_act_season, v_year; EXCEPTION WHEN OTHERS THEN /*Ez a statement number-es hibajelzés is használatos bár kicsit fapadosabb mint a "Kezdeti ügyfél és tranzakciókezelés egy megvalósítása PL/SQL-ben" példámban használtnál. De ha nem akarunk nagyon bohóckodni a különféle hibakezelésekkel, akkor lehet használni de azzal számolni kell, hogy nagyobb kódoknál nehezen karbantartható és nehezen található meg benne mi az adott hiba okozója, meg kicsit zavaró is hogy ott van*/ proc_error_log(c_proc_name, 'STMT: ' || to_char(v_stmt_num) || '; ' || v_error_msg); END calc_seasonly_bal_proc; --NOTE: mint szinte minden program ez is továbbfejleszthető, mégpedig akár rögtön azzal, -- hogy a futtatni kívánt dinamikus parancsokat(stringeket) kirakjuk táblába, így az eljárásunk kódja leegyszerűsödik, -- a végrehatjandó utasításaink karbantarthatóbbá válnak, hiszen nem az eljárás kódját kell módosítani, hanem -- csak a táblában a megfelelő sorokat. Tulajdonképp egy minimális végrehatjási logikát kell csak implementálni -- egy kurzor segítségével, ami végigmegy az utasításokat tartalmazó táblán és végrehajtja azokat. / -------------------------------------------------------- -- Function FUNC_GET_SEASON -------------------------------------------------------- CREATE OR REPLACE FUNCTION func_get_season RETURN VARCHAR2 AS /****************************************************************/ /* Created: DARKMAN */ /* Purpuse: Return the name of the actual season from the SYSDATE*/ /* Date: 2014.06.12. */ /****************************************************************/ v_return VARCHAR2(6):=''; c_date CONSTANT DATE := SYSDATE; c_func_name CONSTANT VARCHAR2(15) := 'FUNC_GET_SEASON'; BEGIN IF EXTRACT(MONTH FROM c_date) IN (1,2,12) THEN v_return := 'WINTER'; ELSIF EXTRACT(MONTH FROM c_date) IN (3,4,5) THEN v_return := 'SPRING'; ELSIF EXTRACT(MONTH FROM c_date) IN (6,7,8) THEN v_return := 'SUMMER'; ELSIF EXTRACT(MONTH FROM c_date) IN (9,10,11) THEN v_return := 'AUTUMN'; END IF; RETURN v_return; EXCEPTION WHEN OTHERS THEN proc_error_log(c_proc_name); END FUNC_GET_SEASON; / ------------------------------ --** DDL scripts ------------------------------ CREATE TABLE seasonal_balance --szezononkénti egyenleg ( year NUMBER, season NUMBER, cust_id NUMBER, balance NUMBER, created DATE ); CREATE TABLE incoming_transactions -- mindeféle bejövő trx ( id NUMBER, cust_id NUMBER, tran_date DATE, prod_id NUMBER, quantity NUMBER, created DATE ); CREATE TABLE seasons_rules --pontszámítási szabályok ( year NUMBER, season NUMBER, prod_id NUMBER, factor FLOAT, valid_from DATE, valid_to DATE, min_limit NUMBER, created DATE --PRIMARY KEY az első 3 oszlop+a 2 date ); CREATE OR REPLACE VIEW v_incoming_trx_per_customer AS SELECT SUM(points_per_trx) as points_per_cust, cust_id FROM (SELECT (trx.quantity * rules.factor) as points_per_trx , trx.cust_id as cust_id FROM incoming_transactions trx INNER JOIN seasons_rules rules ON trx.prod_id = rules.prod_id WHERE tran_date BETWEEN valid_from AND valid_to AND quantity >= min_limit) GROUP BY cust_id; --process közben létrejövő temp tábla szerkezete /* temp_"season"_ "year"_transactions ( year NUMBER season NUMBER, prod_id NUMBER, cust_id NUMBER, tran_date DATE, quantity NUMBER, points FLOAT ); */