📋 Kurz Oracle DB PL/SQL

Kurz PL/SQL ťa prevedie od nulových schopností písania PL/SQL po schopnosť písať efektívne programy na spracovanie údajov. V kurze sa naučíš popísať vlastnosti a syntax PL/SQL. Vytvárať a ladiť uložené procedúry a funkcie. PL/SQL je rozšírenie procedurálneho jazyka Oracle SQL. Program PL/SQL môže mať príkazy SQL aj procedurálne príkazy. V programe PL/SQL sa príkazy SQL používajú na prístup k súborom údajov uloženým v databáze, zatiaľ čo procedurálne príkazy sa používajú na spracovanie jednotlivých údajov a riadenie toku programu.

⚓ Odkazy na kurzy

Kurzy Oracle
Kurz Oracle PL/SQL I. Začiatočník

🧱 Testovacia Oracle DB

Prihlasovacie údaje na kurze od lektora

☁️ Oracle Autonomous Database (Oracle Cloud Database)

Username: admin
Wallet: KurzOraclePLSQL.zip
Configuration File: C:\Users\Administrator\Documents\Oracle Wallet\Wallet_KurzOraclePLSQL.zip
Service: kurzoracleplsql_high (automaticky)

DB Name: KurzOraclePLSQL alebo F861DMQMREFDG3ZG
URL Connection: ocid1.autonomousdatabase.oc1.eu-frankfurt-1.antheljtabxkwpiangbzg7rars3ru375p3nm3gsayo3qxmoqv6nce4zidj2a

📋 Samostatne nainštalovaná databáza Oracle Express Database

Username: sysdba alebo sysdba Password: oracle alebo zadané pri inštalácií
Hostname: localhost alebo 127.0.0.1
Port: 1521
SID: xe

📁 Súbory a materiály na kurze od lektora

Registrujte sa na a vytvorte si Oracle konto pre používanie Oracle Live SQL editora Kto si bude chcieť okrem simulátorov vyskúšaj aj vlastné databázy a nad nimi písanie skriptov, nainštalujte si:

Materiály sú dostupné na: Microsoft OneDrive/Sharepoint

🧰 Stránky a nástroje na precvičovanie Oracle DB a PL/SQL

Šablóny Oracle SQL Developer (Code Template)

-- ssf SELECT FROM
-- ii INSERT INTO
-- df DELETE FROM
-- ct CREATE TABLE
-- cp CREATE PROCEDURE
-- cf CREATE FUNCTION

Cesta, kde je uložený konfiguračný súbor (product.conf) SQL Developer: C:\Users\POUZIVATEL\AppData\Roaming\sqldeveloper\VASA_VERZIA\product.conf C:\Users\Administrator\AppData\Roaming\sqldeveloper\22.2.1\product.conf

Cesta, kde sú uložené SQL skripty DBeaver: C:\Users\User\AppData\Roaming\DBeaverData\workspace6\General

📔 Dokumentácia Oracle PL/SQL a Oracle Database a Guidelines

Čo je PL/SQL?

Procedurálne rozšírenie SQL od Oracle Je procedurálna nadstavba jazyka SQL firmy Oracle. Jazyk PL/SQL je pôvodne procedurálny, dnes má však niektoré prvky a možnosti OOP. Na rozdiel od SQL umožňuje používať cykly, podmienky, procedúry, funkcie, ošetrenie výnimiek a iné programátorské postupy.

SQL jazyk – Rozšírenie o procedúry

Každý DB systém ma vlastný druh rozšírenia

V relačných DB častokrát nájdeme aspoň tieto 3:

PROCEDURE - zoskupenie dopytov, výsledkom môže byť tabuľka
FUNCTION - zoskupenie dopytov, výsledkom je skalárna hodnota
TRIGGER - špeciálna procedúra k určitým udalostiam/eventom ako je aktualizáciua, vymazanie, vytvorenie údajov.

Oracle – PL/SQL
Microsoft/Sybase – T–SQL
MySQL – SQL/PSM (Persistent Stored Module)
Sybase – Watcom–SQL
PostgreSQL – PL/PSM

Databázové Objekty

Databáza/Schéma – zoskupenie objektov
Tabuľka – štruktúrované zoskupenie dát
Index – štruktúra dát zvyšujúca výkon a rýchlosť dopytov
Avšak na úkor diskových operácií a diskového priestoru
Procedúra, funkcia – sekvencia dopytov
Pohľad – predpripravený dopyt
Trigger – špeciálna procedúra – vkladanie, aktualizácia a mazanie

Architektúra PL/SQL

PL/SQL compilation and run-time system (ďalej len CRTS) je technológia, nie nezávislý produkt. Predstavte si túto technológiu nasledovne. CRTS je engina, ktorá kompiluje a vykonáva PL/SQL bloky a podprogramy. Engina môže byť nainštalovaná buď v Oracle serveri alebo v application development tool, ako napr. v Oracle Forms alebo Oracle Report. Takže PL/SQL sídli v 2 prostrediach.

Databázový server Oracle
Oracle tools

Tieto dve prostredia sú nezávislé.mV oboch prostrediach PL/SQL engina prijme ako vstup ľubovoľný platný PL/SQL blok alebo podprogram a vykoná ho tak, že vykoná procedurálne príkazy/statementy v bloku alebo podprograme, ale SQL statementy pošle SQL Statement Executorovi v databázovom serveri Oracle.

Kompilačný a runtime systém PL/SQL je engine, ktorý kompiluje a spúšťa jednotky PL/SQL Engine je možné nainštalovať do databázy alebo do nástroja na vývoj aplikácií, ako je napríklad Oracle Forms. V oboch prostrediach engine PL/SQL akceptuje ako vstup akúkoľvek platnú jednotku PL/SQL. Engine spúšťa procedurálne príkazy, ale odosiela príkazy SQL do nástroja SQL v databáze. Databáza zvyčajne spracováva jednotky PL/SQL. Keď nástroj na vývoj aplikácií spracuje jednotky PL/SQL, odovzdá ich svojmu lokálnemu jadru PL/SQL. Ak jednotka PL/SQL neobsahuje žiadne príkazy SQL, lokálny mechanizmus spracuje celú jednotku PL/SQL. Je to užitočné, ak nástroj na vývoj aplikácií môže ťažiť z podmieneného a iteračného riadenia. Napríklad aplikácie Oracle Forms často používajú príkazy SQL na testovanie hodnôt položiek polí a vykonávanie jednoduchých výpočtov. Použitím PL/SQL namiesto SQL sa tieto aplikácie môžu vyhnúť volaniam do databázy.

Výhody PL/SQL

Základná štruktúra anonymných blokov

DECLARE
Deklaračná sekcia
v_priezvisko varchar2(10):='Sangala';
BEGIN
Výkonná/exekučná sekcia
DBMS_OUTPUT.PUT_LINE(v_priezvisko);
EXCEPTION
Ošetrenie výnimiek/chýb
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('SELECT vratil viacero riadkov');
END;
Ukončenie bloku

Príklady PL-SQL Ahoj Svet (Hello World)

BEGIN
 -- Najkratsi Ahoj Svet/Hello World skript
	-- Nazov balicka/package.procedura/funkcia 
	-- package dbms_output umožňuje odosielať správy z uložených procedúr, balíkov a spúšťačov/triggerov. Balík je užitočný najmä na zobrazenie informácií o ladení/debuggovani PL/SQL.
    dbms_output.put_line('Ahoj Svet');
END;
/

REM Kurz_PL_SQL_I-D1_01_Ahoj_Svet

DECLARE 
 -- Deklaracia premennych 
 sprava varchar(10):='Ahoj svet'; 
BEGIN 
 /* 
    PL/SQL spustitelne prikazy 
    put, put_line (odriadkuje) 
 */ 
 dbms_output.put_line('Hello World'); 
 dbms_output.put_line(sprava); 
-- EXCEPTION 
-- Spracovanie vynimiek/chyb 
END; 
/

Formatovanie Výstupov

/*
    Microsoft Windows - Concatenate CR a LF: chr(13)||chr(10)
    Linux, Mac: chr(10)
    dbms_output.put_line('Hello World' || chr(13) || chr(10)); 
*/

BEGIN
	DBMS_OUTPUT.PUT('1. riadok (put)... ');
	DBMS_OUTPUT.PUT('stale 1. riadok (put)...');
	DBMS_OUTPUT.NEW_LINE;
	DBMS_OUTPUT.PUT_LINE('2. riadok (new_line + put_line)...');
	DBMS_OUTPUT.PUT('3. riadok TEST');
	DBMS_OUTPUT.NEW_LINE;
	DBMS_OUTPUT.PUT_LINE('4. riadok' || CHR(10) || '5. riadok priklad LF (chr10)');
	DBMS_OUTPUT.PUT_LINE(CHR(10));
	DBMS_OUTPUT.PUT_LINE('9. riadok');
END;
/

📦 Dátové Typy

🎰 Znakové Dátové Typy

VARCHAR2 uchováva reťazce s premenlivou dĺžkou. Pri deklarácii premennej treba zadať jej maximálnu dĺžku (1 – 32 767 bytov).
Dĺžku je možné zadať aj počtom znakov, vtedy sa potrebnýpočet bytov preráta podľa najvačšej dĺžky ktorú znaková sada databázy využíva pre nejaký znak.
Napr. pre UTF8 je to 3, t.j. 100 CHAR = 300 BYTE

nazov_premennej VARCHAR2(max_dlzka [BYTE|CHAR])

CHAR pre reťazce pevnej dĺžky, táto sa zadáva pri deklarácii obdobne ako pri VARCHAR2
V rozmedzí 1‐32 767 bytov alebo korešpondujúci počet znakov.
Ak sa nezadá dĺžka, premenná bude dlhá 1 znak, ak sa nezadá CHAR alebo BYTE, určí sa to z hodnoty inicializačnej premennej NLS_LENGTH_SEMANTICS.
Ak sú hodnoty v premennej CHAR menšie ako jej dĺžka, doplnia sa do tejto dĺžky automaticky medzerami sprava.

NVARCHAR2 unicode obdoba VARCHAR2, uchováva premenné v znakovej sade definovanej parametrom NLS_NCHAR_CHARACTERSET t.j. UTF-8 alebo UTF-16

NCHAR unicode obdoba CHAR

🎱 Číselné Dátové Typy (Reálne)

NUMBER je najpoužívanejší číselný dátový typ, ukladá decimálne hodnoty od 10^-130 do 10^126-1

nazov_premennej NUMBER [(precision, scale)]

Ak zadáme precision a scale, premenná bude číslo s pevnou desatinnou čiarkou.
Precision je celkový počet signifikantných číslic v rozmedzí 1 až 38. Scale je počet číslic za desatinnou čiarkou (ak je kladné) alebo pred ňou (záporné scale).
Rozmedzie scale je -84 až 127.
Hodnoty, ktoré presnosťou presahujú scale premennej, do ktorej majú byť vložené, budú implicitne zaokrúhlené. Ak nezadáme precision ani scale, premenná bude číslo s pohyblivou desatinnou čiarkou s dĺžkou maximálne 40 číslic.

BINARY_FLOAT a BINARY_DOUBLE sa používajú pre čísla s pohyblivou desatinnou čiarkou a jednoduchou alebo dvojitou presnosťou podľa normy IEEE-754.
BINARY_FLOAT zaberá 4 byty a ukladá čísla od 1,17549435E-38F do 3,40282347E+38F.
BINARY_DOUBLE zaberie 8 bytov a spracuje čísla od 2,2250748585072014E-308 do 1,7976931348623157E+308.

SIMPLE_FLOAT a SIMPLE_DOUBLE (od ver. 11g) je ich rýchlejšia alternatíva, ktorá ale nepodporuje NULL hodnoty, predikáty IS NAN a IS INFINITY a nekontroluje podmienkypretečenia

🎲 Číselné Dátové Typy (Celé)

PLS_INTEGER ukladá celočíselné hodnoty (signed integer) od -2 147 483 648 do 2 147 483 647.
Hodnoty sú reprezentované natívnym integer formátom príslušnej hardvérovej platformy, celočíselné výpočty sa tak vykonávajú rýchlejšie ako pri dátovom type NUMBER. PLS_INTEGER je náhradou a ekvivalentom staršieho typu BINARY_INTEGER.

SIMPLE_INTEGER (od ver. 11g) je rýchlejšia alternatíva PLS_INTEGER, nepodporuje však NULL hodnoty a nekontroluje podmienky pretečenia.

📅 Dátumové Dátové Typy

DATE ukladá datum a čas s presnosťou na sekundy.

nazov_premennej DATE

TIMESTAMP ukladá datum a čas s presnosťou až na miliardtinu sekundy (9 miest, defaultne 6).

nazov_premennej TIMESTAMP [(precision)]

TIMESTAMP WITH TIMEZONE ukladá datum a čas ako TIMESTAMP, navyše však ukladá informáciu o časovej zóne

nazov_premennej TIMESTAMP [(precision)] WITH TIME ZONE

TIMESTAMP WITH LOCAL TIMEZONE ako TIMESTAMP, je však citlivý na rozdiely medzi SESSION_TIMEZONE a DB_TIMEZONE. Informácie sa pri ukladaní na server prepočítavajú a uložia v časovej zone databázy, naopak pri výbere sa prerátajú na časovú zónu session.

nazov_premennej TIMESTAMP [(precision)] WITH LOCAL TIME ZONE

⏳ Intervalové Dátové Typy

INTERVAL YEAR TO MONTH umožňuje ukladať časové úseky ako počet rokov a mesiacov
INTERVAL DAY TO SECOND použijeme pre časové úseky zadané ako počet dní, hodín, minút a sekúnd (vrátane zlomkov sekúnd)

✅ Ďalšie Dátové Typy

BOOLEAN môže nadobúdať 3 hodnoty – TRUE, FALSE a NULL
ROWID slúži pre ukladanie fyzickej adresy riadka v tabuľke
UROWID pre logickú adresu riadka v index organized tabuľke
REF CURSOR a SYS_REFCURSOR pre kurzorové premenné používané so statickým, alebo dynamickým SQL

⬛ LOB (Large Objects) Dátové Typy

CLOB (Character Large Object) pre deklaráciu premennej, ktorá predstavuje LOB locator – odkaz na blok veľkého textu v databáze
NCLOB odkaz na blok veľkého unicode textu v databáze
BLOB odkaz na veľký binárny objekt uložený v databáze
BFILE odkaz na veľký binárny objekt uložený mimo databázy

Rozšírenia (prípony) súborv Oracle PL/SQL

Functions (*.pls)
Procedures (*.pls)
Package Specification (*.pks)
Package Body (*.pkb)
Trigger (*.trg)
SQL skripty oddelené / (tvorba/odstránenie tabuliek, reláci a pod.) (*.sql)

Odporúčanie:

Špecifikácia balíčka do (*.pks)
Telo/implementácia balíčka do (*.pkb)
Všetko ostatné vrátane funkcií, procedúry, triggerov, SQL príkazov do (*.sql)

Packages (Balíčky)

PL/SQL umožňuje zoskupiť logický príbuzné typy, premenné, kurzory a podprogramy do balíčkov. Balíčky majú obvykle 2 časti: špecifikáciu a telo. Špecifikácia je rozhranie/interface balíčka pre aplikáciu - deklaruje typy, konštanty, premenné, výnimky, kurzory a podprogramy, ktoré môžeme z balíčka využívať. Telo obsahuje definície kurzorov a podprogramov, takže implementuje špecifikáciu. Iba deklarácie v špecifikácii balíčkov sú viditeľné a prístupné z aplikácie. Implementačné detaily (v tele balíčka) sú skryté a neprístupné z aplikácie. Balíčky môžu byť skompilované a uložené v Oracle databáze, kde môžu byť zdieľané mnohými aplikáciami, keď voláte prvýkrát podprogram z balíčka, tak sa celý balíček načíta do pamäte. Pri ďalších volaniach už je balíček v pamäti a nevyžaduje teda ďalšie I/O operácie, čo priaznivo ovplyvňuje výkon.

Prečo používať Balíčky (Packages)?

-- Tabulka pre logovanie
create table log_table
( message varchar2(200)
)
//

-- Vytvor špecifikáciu balíčka
create or replace package pkg_test
is
    -- Definícia 1 verejnej/public procedúry
    procedure do;
end;
//

-- Vytvor telo balíčka
create or replace package body pkg_test
is
    -- Privátna/súkromná logovacia procedúra
    procedure log(p_message in varchar)
    is
    begin
        insert into log_table(message) values (p_message);
    end;

    -- Privátna/súkromná, ktorá vráti na výstupe dvojnásobné číslo
    function double(p_number in number)
    return number
    is
    begin
        return 2 * p_number;
    end;

    -- Použitie verejnej/public procedúry
    procedure do
    is
    begin
        log('2 * 12 = ' || double(12));
    end;

end;
//

Spustenie príslušnej verejnej procedúry a balíka

-- Vykonanie verejnej/public procedúry balíka
begin
    pkg_test.do;
end;
/

-- dbms_output nefunguje, takže sa lognene do tabuľky
-- (podobne ako v skutočnom živote) a vyberiete z neho všetky záznamy
select *
from   log_table
/

Záznamy (Records)

Record je skupina dátových položiek uložená v poliach, každé má svoje meno a datatype. Atribút %ROWTYPE dovoľuje deklarovať recordy, ktoré zodpovedajú riadku databázovej tabuľky. Avšak tak nemôžete určiť datatypy políčok recordu, alebo ich sami definovať. Takýto record je presný obraz datatypu databázovej tabuľky. Preto existuje datatype RECORD, ktorý toto obmedzenie ruší.

PL/SQL dokáže spracovať nasledujúce 3 typy záznamov:

Table-based (Na základe tabuľky)
Cursor-based records (Záznamy založené na kurzore)
User-defined records (Používateľsky definované záznamy)

Table-Based Records

Atribút %ROWTYPE umožňuje programátorovi vytvárať záznamy založené na tabuľkách a kurzoroch.

DECLARE 
   customer_rec customers%rowtype; 
BEGIN 
   SELECT * into customer_rec 
   FROM customers 
   WHERE id = 5;  
   dbms_output.put_line('Zakaznik ID: ' || customer_rec.id); 
   dbms_output.put_line('Zakaznik meno: ' || customer_rec.name); 
   dbms_output.put_line('Zakaznik adresa: ' || customer_rec.address); 
   dbms_output.put_line('Zakaznik mzda: ' || customer_rec.salary); 
END; 
/

Cursor-based records (Záznamy založené na kurzore)

DECLARE CURSOR customer_cur is SELECT id, name, address
FROM customers; customer_rec customer_cur%rowtype; BEGIN OPEN customer_cur; LOOP FETCH customer_cur into customer_rec; EXIT WHEN customer_cur%notfound; DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name); END LOOP; END; /

Používateľom definované záznamy

PL/SQL poskytuje používateľom definovaný typ záznamu, ktorý vám umožňuje definovať rôzne štruktúry záznamov. Tieto záznamy pozostávajú z rôznych polí.

TYPE 
type_name IS RECORD 
  ( field_name1  datatype1  [NOT NULL]  [:= DEFAULT EXPRESSION], 
   field_name2   datatype2   [NOT NULL]  [:= DEFAULT EXPRESSION], 
   ... 
   field_nameN  datatypeN  [NOT NULL]  [:= DEFAULT EXPRESSION); 
record-name  type_name;

DECLARE 
TYPE books IS RECORD 
(title  varchar(50), 
   author  varchar(50), 
   subject varchar(100), 
   book_id   number); 
book1 books; 
book2 books;

Pristupovanie k dátam (Používateľom definované záznamy)

DECLARE 
   type books is record 
      (title varchar(50), 
      author varchar(50), 
      subject varchar(100), 
      book_id number); 
   book1 books; 
   book2 books; 
BEGIN 
   -- Book 1 specifikacia
   book1.title  := 'Harry Potter a Kameň mudrcov'; 
   book1.author := 'Joanne Jo Rowlingová';  
   book1.subject := 'Fantasy Román'; 
   book1.book_id := 1005123;  
   -- Book 2 specifikacia 
   book2.title := 'Harry Potter a Tajomná komnata'; 
   book2.author := 'Joanne Jo Rowlingová'; 
   book2.subject := 'Fantasy Román'; 
   book2.book_id := 1005124;  
  
  -- Vypis book 1 zaznamu 
   dbms_output.put_line('Book 1 title : '|| book1.title); 
   dbms_output.put_line('Book 1 author : '|| book1.author); 
   dbms_output.put_line('Book 1 subject : '|| book1.subject); 
   dbms_output.put_line('Book 1 book_id : ' || book1.book_id); 
   
   -- Vypis book 2 zaznamu 
   dbms_output.put_line('Book 2 title : '|| book2.title); 
   dbms_output.put_line('Book 2 author : '|| book2.author); 
   dbms_output.put_line('Book 2 subject : '|| book2.subject); 
   dbms_output.put_line('Book 2 book_id : '|| book2.book_id); 
END; 
/

Name		Name	Last commit message	Last commit date
Latest commit History 57 Commits
Cheatsheety		Cheatsheety
Data		Data
Pripojenie		Pripojenie
Zdrojove_Kody		Zdrojove_Kody
README.md		README.md
Reiter - PLSQL I.pdf		Reiter - PLSQL I.pdf

miroslav-reiter/Kurz_Oracle_DB_PLSQL

Folders and files

Latest commit

History

Repository files navigation