Stored Procedures & Database Triggers, Tiskové sestavy v Oracle Reports

Stored Procedures & Database Triggers, Tiskové sestavy v Oracle Reports Marek Rychlý Vysoké uˇcení technické v Brneˇ Fakulta informaˇcních technologií Ústav informaˇcních systému˚

Demo-cviˇcení pro IDS 9. dubna 2014

Marek Rychlý

Stored Procedures & Database Triggers, Tiskové sestavy v Oracle Reports — Demo-cviˇcení pro IDS, 9. dubna 1 / 2014 18

Obsah

1

Stored Procedures & Database Triggers Uložené procedury a funkce Databázové triggery

2

Tiskové sestavy v Oracle Reports Tvorba tiskových sestav ˇ tiskových sestav Spuštení

3

ˇ Shrnutí a záver

Marek Rychlý


Stored Procedures & Database Triggers Tiskové sestavy v Oracle Reports ˇ Shrnutí a záver

Uložené procedury a funkce Databázové triggery

Stored Procedures & Database Triggers

Uložené procedury/funkce pro zpracování dat v databázi. (na rozdíl od zpracování až v aplikaci využívající databázi)

Databázové triggery pro složitá integritní omezení a reakce. ˇ dat) (spouští se v reakci na plánované nebo provedené zmeny

Obojí je uloženo v databázi, jsou to objekty schématu databáze. (tj. na db. serveru, sdílené pˇristupujícími aplikacemi)

Procedura a trigger obsahují blok kódu v jazyce PL/SQL. (po hlaviˇcce následuje struktura DECLARE, BEGIN, END )

ˇ atomicky. Bloky PL/SQL jsou provádeny (tj. bud’ se provede vše, nebo nic, napˇr. pˇri vyvolání výjimky)

Marek Rychlý




PL/SQL Engine v Oracle Database

(diagram pˇrevzat z „Oracle8i Concepts, Release 8.1.5)“ Marek Rychlý




Uložené procedury create [or replace] procedure jmenoproc(param1 [in|out|in out] typ, . . . ) is . . .

ˇ než „drop“ a „create“ protože stejný objekt „replace“ vhodnejší ˇ (napˇr. nezruší práva definovaná nad objektem atp., což by „drop“ udelal)

parametry mohou být vstupní, výstupní, nebo vstupneˇ výstupní (pokud neuvedeno „in“, „out“, nebo „in out“, tak jsou implicitneˇ jen vstupní)

ˇ datový typ muže ˚ být odkaz na datový typ sloupce nejaké tabulky ˇ (toto funguje i jinde v PL/SQL, napˇr. pro deklaraci promenných uvnitˇr bloku)

za datovým typem muže ˚ být nastavena hodnota DEFAULT expr create or replace PROCEDURE reset_study( student IN STUDY.STUDENT_ID%TYPE, program IN STUDY.PROGRAM_CODE%TYPE DEFAULT ’IT-BC-3’ ) IS initial_year STUDY.YEAR%TYPE := 1; BEGIN update study set year = initial_year where student_id = student and program_code = program; END; Marek Rychlý




Uložené funkce create [or replace] fucntion jmenofce(. . . ) return typ is . . .

ˇ stejneˇ jako u uložené procedury parametry a telo (vˇc. vstupních a výstupních parametru, ˚ atd.)

typ návratové hodnoty nesmí mít uvedenu délku cˇ i pˇresnost (délka cˇ i pˇresnost dle místa volání funkce, resp. uložení její návratové hodnoty)

muže ˚ vracet také celou tabulku, datový typ pak tabulka objektu˚ (tzv. „pipelined table functions“) create or replace FUNCTION first_year_students_count( program IN STUDY.PROGRAM_CODE%TYPE DEFAULT ’IT-BC-3’ ) RETURN int IS initial_year STUDY.YEAR%TYPE := 1; students_count int; BEGIN select count(*) into students_count from study where year = initial_year and program_code = program; return students_count; END;

Marek Rychlý




ˇ uložených procedur a funkcí Spouštení Procedury i funkce lze volat pˇrímo v PL/SQL blocích. (v anonymních blocích, procedurách, fcích, cˇ i triggerech, ale i v Oracle Forms)

Volání funkce v PL/SQL bloku musí uchovat návratovou hodnotu. ˇ ˇ (napˇr. do promenné prostˇredí, tj. pˇriˇradit volání fce pom. „:=“ do promenné)

Funkce lze navíc volat z SQL dotazu˚ cˇ i pˇríkazu. ˚ (tj. v SELECT/UPDATE/DELETE, napˇr. SELECT fce(123) FROM dual;) DECLARE v_Return int; BEGIN v_Return := FIRST_YEAR_STUDENTS_COUNT( PROGRAM => ’IT-BC-3’ ); :output_variable_Return = v_Return; END; (pˇri volání v SQLDeveloper, je výsledek viditelný v záložce Output Variables)

Marek Rychlý




Databázové triggery Triggery vyvolány modifikací dat tabulky (nebo pohledu). (vázány na akci DELETE/INSERT/UPDATE tabulky nebo pohledu)

ˇ pˇred nebo po modifikaci každého ˇrádku ovlivneného akcí (tj. „BEFORE/AFTER“ akce „DELETE/INSERT/UPDATE“)

pˇred, po, nebo místo akce, která vyvolala trigger (tj. „BEFORE/AFTER/INSTEAD OF“ akce „DELETE/INSERT/UPDATE“)

Marek Rychlý




Poˇradí volání triggeru˚ Pˇri akci DELETE/INSERT/UPDATE nad tabulkou/pohledem 1

jsou zavolány triggery „BEFORE DELETE/INSERT/UPDATE“

2

jsou zavolány triggery „INSTEAD OF DELETE/INSERT/UPDATE“ nebo ˇ akcí jsou zavolány jsou pro každý ˇrádek tabulky ovlivnený 1 2

3

triggery „BEFORE DELETE/INSERT/UPDATE FOR EACH ROW“ triggery „AFTER DELETE/INSERT/UPDATE FOR EACH ROW“

jsou zavolány triggery „AFTER DELETE/INSERT/UPDATE“

ˇ triggeru˚ „FOR EACH ROW“ jsou dostupná puvodní V tele ˚ a nová ˇ (modifikovaná) data pomocí promenných prostˇredí :NEW a :OLD.

Marek Rychlý

Stored Procedures & Database Triggers, Tiskové sestavy v Oracle Reports — Demo-cviˇcení pro IDS, 9. dubna 10 /2014 18



Použítí triggeru˚ Automatické generování obsahu˚ sloupcu. ˚ (napˇr. IF :NEW.sloupec IS NULL THEN :NEW.sloupec := myseq.nextval;)

Ošetˇrení nesprávných transakcí. (napˇr. raise_application_error(-20000, ’error msg’);)

Dosažení komplexní autorizace. Zvýšení referenˇcní integrity. Transparentní záznam událostí. Auditing. Replikace dat. Statistika pˇrístupu k tabulkám. ˇ Triggery lze „povesit“ na více akcí – akce, která vyvolala trigger, lze odlišit v kódu pom. predikátu˚ INSERTING, DELETING a UPDATING. Marek Rychlý




Pˇríklad triggeru create or replace trigger new_student before insert on student for each row declare default_login STUDENT.LOGIN%TYPE; begin if :new.id is null then :new.id := student_id.nextval; end if; if :new.login is null then :new.login := dbms_random.string(’x’,8); elsif length(trim(:new.login)) != 8 then raise_application_error(-20000, ’Login has to have 8 characters!’); end if; end;

Marek Rychlý



Tvorba tiskových sestav ˇ tiskových sestav Spuštení

Oracle Reports ˇ jednoduchou tvorbu tiskových sestav. Oracle Reports umožnují (s výstupem do HTML nebo pˇrímo na tiskárnu, tj. Canvas/PS/PDF)

Data pro sestavu z tzv. „datového modelu“. (definuje „query“ a navazující „group“ bloky, souhrny, atd.)

Dotaz muže ˚ být v rámci datového bloku parametrizovaný. ˇ (parametry jako promenné prostˇredí se pak zadávají pˇri volání sestavy)

Výsledná sestava vypíše data z modelu pak podle šablony. (Web & Paper Layout šablony pro sestavy)

Marek Rychlý




Tvorba sestav pomocí Report Wizard 1 2

V reportu zvolit menu „Tools“ / „Report Wizard“. Vybrat vhodný „Layout“ a „Style“. (papírový/web layout a styl dle zobrazovaných dat, napˇr. výpis se seskupováním)

3

Zvolit jako zdroj dat „SQL Query“ a zadat SQL dotaz. ˇ (v dotazu možno použít parametry jako promenné prostˇredí, tj. uvozené „:“)

4

Vybrat sloupec výsledku, podle kterého se bude seskupovat. (vhodné pro layouts se seskupováním, napˇr. „Grou Left“ layout)

5 6

ˇ Vybrat sloupce výsledku, které se zobrazí v sestave. Vybrat sloupec výsledku, kde se budou aplikovat agregaˇcní fce. (napˇr. pro celkový poˇcet záznamu, ˚ max/min hodnoty, atp.)

7 8

Zvolit nadpisy datových polí a šablonu vizualizace. Uložit report pom. menu „File“ / „Save as“ jako *.rdf. ˇ *.jsp) (puvodní ˚ formát, lépe odlišitelný než novejší Marek Rychlý




ˇ tiskové sestavy Spuštení V Oracle pomocí menu „Program“ / „Run Web/Paper Layout“. (pokud dotaz s parametry, tak vyzve k jejich zadání)

Z Oracle Forms pomocí PL/SQL kódu – více video-tutoriál (pomocí RUN_REPORT_OBJECT & WEB.SHOW_DOCUMENT built-ins) DECLARE v_report_id Report_Object; vc_report_job_id VARCHAR2(100); vc_rep_status VARCHAR2(100); BEGIN v_report_id := find_report_object(’myreportmodule’); SET_REPORT_OBJECT_PROPERTY(v_report_id, REPORT_OTHER, ’myparam=’||:mylocalvar); vc_report_job_id := RUN_REPORT_OBJECT(v_report_id); vc_rep_status := REPORT_OBJECT_STATUS(vc_report_job_id); IF vc_rep_status = ’FINISHED’ THEN web.show_document(’//<servlet name>/getjobid=’|| vc_report_job_id ||’?server=’|| ’’,’_blank’); ELSE message (’Report failed with error message ’ || vc_rep_status); END IF; END; Marek Rychlý



ˇ Shrnutí a záver

Uložené procedury a funkce pro práci s daty na straneˇ databáze. Triggery pro akce pˇred/po/místo modifikace dat. Oracle Reports pro tvorbu tiskových sestav. Dokumentace: Oracle8i Concepts R8.1.5, Procedures and Packages Oracle Database PL/SQL Language Reference 11gR2, PL/SQL Triggers Oracle Reports Documentation ˇ nápoveda v Oracle Forms/Reports Developer ˇ . . . a další na webových stránkách pˇredmetu

Marek Rychlý


Stored Procedures & Database Triggers, Tiskové sestavy v Oracle Reports

Recommend Documents