Zadání praktických cvičení předmětu Jazyk SQL

Pro inspiraci, resp. úsporu času se lze podívat na možné řesení kliknutím na nadpis zadání příkladu.

Business Components Diagram

Database Diagram

Vytvoření DB [script]

Téma 1 - knihovní SQL funkce  (relevantní partie dokumentace, snímek prezentace k SQL Funkcím)

tema_01-1

Vytvořte seznam zaměstnanců ve tvaru uvedeném níže. Hodnotu platu (sloupec Salary) v sestavě upravte zvýšením o 15 procent se zaokrouhlením na celá čísla.

Očekávaný tvar výstupu:

Department_Id Last_name Upraveny_plat
20 SMITH 920
30 ALLEN 1840
Nápověda: použijte knihovní funkci ROUND 
Select Department_ID, Last_Name, Round(Salary*1.15)
From Employees;
Select Department_ID,Last_Name, Round(Salary*1.15)Upraveny_plat
From Employees;

tema_01-2

Vytvořte seznam zaměstnanců v tomto tvaru:

Zaměstnanec s Pozicí
SMITH                 CLERK
NOVÁČEK       SALESMAN
Jono                      Ono
Nápověda: použijte knihovní funkce RPAD a LPAD 
Select Rpad(Last_name,10)||Lpad(Job_Id,10) Zamestnanec_a_funkce
       From Employees
/

tema_01-3

Vytvořte seznam zaměstnanců v tomto tvaru:

Zamestnanci
SMITH(Clerk)
ALLEN(Salesman)
Nápověda: použijte funkci INITCAP(string) a operaci zřetězení 
set echo on
Select Last_Name||'('|| InitCap(Job_ID)||')'  Zamestnanci
       From Employees
/

tema_01-4

Bojovnice za práva žen prosadily změnu funkčního označení SALESMAN na SALESPERSON.
Vyrobte výstup v tomto tvaru:

Last_name Department_Id Job
KING 10 President
JONES 20 Manager
Nápověda: použijte funkci REPLACE(kde, co, cim) INITCAP 
Set echo on
Select Last_name, Department_Id, InitCap(Replace(JOB_Id,'SALESMAN','SALESPERSON')) JOB
       From Employees
/

tema_01-5

Zobrazte jméno každého zaměstnance s datem přijetí a datem revize platu za předpokladu, že revize platu probíhá po uplynutí jednoho roku od přijetí.
Uspořádejte vzestupně podle data revize platu.
Vyrobte výstup v tomto tvaru:

Last_name Hire_date Review
SMITH 13-JUN-83 13-JUN-84
ALLEN 15-AUG-83 15-AUG-84
Nápověda: použijte funkci Add_Months(k_cemu, kolik) 
Set echo on
Select Last_name, Hire_date, Add_months(Hire_date,12) Review
       From Employees
       Where Add_months(Hire_date,12) < Sysdate
       Order By Add_months(Hire_date,12)
/

tema_01-6

Zaměstnanci, kteří mají datum přijetí do 15. dne v měsíci mají první výplatní den poslední pátek v měsíci přijetí. Ostatní mají první výplatní den až poslední pátek v měsíci následujícím. Vytiskněte seznam zaměstnanců s jejich prvním výplatním termínem. Sestavu uspořádejte podle data přijetí.
Vyrobte výstup v tomto tvaru:

Last_name Hire_date PAYDAY
SMITH 13-JUN-83 13-JUN-84
ALLEN 15-AUG-83 15-AUG-84
Nápověda: použijte funkce ROUND(datum, přesnost) a NEXT_DAY(od_kdy, ktery_den) 
Set echo on
Select Last_name,
       Hire_Date,
       Next_Day(Last_Day(Round(Hire_date,'Month')) - 7,'FRIDAY') PayDay
       From Employees
       Order By Hire_Date
/

tema_01-7

Pro zaměstnance z oddělení 20 zobrazte jméno a datum přijetí. Poraďte si tak, abz se výsledné řádky nelámaly.
Vyrobte výstup v tomto tvaru:

Last_name Date_Hired
JONES April,Second,1981
FORD December,Third,1981
Nápověda: použijte funkci TO_CHAR s konverzním formátem 'Month,Ddspth,YYYY' 
Column DATE_HIRED Format A30
Set echo on
Select Last_name,
       To_Char(Hire_date,'fmMonth,Ddspth,YYYY') DATE_HIRED
       From Employees
       Where Department_Id = 20
/

tema_01-8

Vytiskněte seznam zaměstnanců s informací o mzdě.
Je-li mzda větší, než 1500, vytiskne se jeho výše.
Je-li právě 1500, zobrazí se text 'Ma prave 1500',
Je-li menší, vytiskne se text 'Ma mene nez 1500'.
Vyrobte výstup v tomto tvaru:

Last_name Plat
KING 5000
JONES 2975
SMITH Ma mene nez 1500
Nápověda:
(CASE
WHEN podm1 THEN vysl1
WHEN podm2 THEN vysl2
...
WHEN podmm THEN vyslm
ELSE vysln
END) 
Set echo on
Spool 5_2.lst
Select Last_name,
       (Case  When SALARY < 2700 Then 'Ma mene nez 2700'
              When SALARY = 2700 Then 'Ma prave 2700'
              Else 'Ma '||to_char(Salary)
        End) Plat
From Employees
/
Spool off

tema_01-9

Mějme tabulku studentů.
STUDENT (Stud_id Integer,
Jmeno Varchar(20),
Prijmeni Varchar(20),
Obor Varchar2(10):{'ZPEV','HRA','KRITIKA'},
Vaha:Number(6.2))

Jako podklad pro rozdělení do oborů byli zúčastnění studenti obodováni:
ROZRAZENI(Stud_id Integer,Body Number)
V tabulce STUDENT jsou záznamy o všech studentech
V tabulce ROZRAZENI jsou vyjmenovaným studentům přiděleny body. Někteří studenti z tabulky STUDENT mohou v tabulce ROZRAZENI chybět.
Aktualizujte tabulku STUDENT na základě podkladů z tabulky ROZRAZENI.
Jestliže má student v tabulce ROZRAZENI počet bodů větší, než je průměr a má dosud nepřiřazený obor, přiřaďte mu obor ?HRA?.
Jestliže student nesplnil předchozí podmínku a v tabulce ROZRAZENI má počet bodů větší, než jsou 2/3 průměru a má dosud nepřiřazený obor, přiřaďte mu obor ?ZPEV?.
Ostatním studentům, kteří jsou zastoupeni v tabulce ROZRAZENI a nemají dosud přiřazený obor přiřaďte obor 'KRITIKA'. 

DROP TABLE STUDENT;
CREATE TABLE STUDENT
 (Stud_id Integer,
  Jmeno Varchar(20),
  Prijmeni Varchar2(20),
  Obor Varchar2(10),
  CHECK (OBOR='ZPEV' OR OBOR='HRA' OR OBOR='KRITIKA'),
  Vaha Number(6,2))
;
DROP TABLE ROZRAZENI;
CREATE TABLE ROZRAZENI(Stud_id Integer,
                       Body Number);
-- tady by to chtelo naplnit tabulky daty
Update student s
Set obor =
 Case
 When (Select body from rozrazeni  r where stud_id = s.stud_id) >
      (Select avg(body) from rozrazeni)
 Then 'HRA'
 When (Select body from rozrazeni  r where stud_id = s.stud_id) <=
      (Select avg(body) from rozrazeni)
  and (Select body from rozrazeni  r where stud_id = s.stud_id) >
      (Select avg(body)*2/3 from rozrazeni)
 Then 'ZPEV'
 Else 'KRITIKA'
 End
Where stud_id  in (select stud_id from rozrazeni)
/

Téma 2 - použití proměnných (snímek relevantní prezentace z loňska 1  a 2 )

tema_02-1a

Zjistěte kolik zaměstnanců má nadrůměrný plat.
a) klasicky formou s využitím poddotazu
Nápověda k b:
Použijte anonymní modul s deklarací proměnných pro uložení mezivýsledku a výsledku.
Použijte příkaz Select ... Into ...
K zobrazení použijte proceduru PUT_LINE z package DBMS_OUTPUT.
Select count(*) Pocet_nadprumernych_platu
From  Employees
Where salary > (Select AVG(salary) from Employees);
/

tema_02-1b

Zjistěte kolik zaměstnanců má nadrůměrný plat.
b) procedurálně s uložením průměrného platu do proměnné. Výsledek zobrazte na konzoli
Nápověda k b:
Použijte anonymní modul s deklarací proměnných pro uložení mezivýsledku a výsledku.
Použijte příkaz Select ... Into ...
K zobrazení použijte proceduru PUT_LINE z package DBMS_OUTPUT.
Declare

v_prumerny_plat Number;
v_pocet_naprumernych Integer;
Begin
Select AVG(salary) Into v_prumerny_plat
From employees;
Select count(*) Into v_pocet_naprumernych
From employees
Where salary > v_prumerny_plat;
DBMS_OUTPUT.Put_Line
('Pocet nadprumernych platu : '||v_pocet_naprumernych);
End;
/

tema_02-2

Upravte řešení předchozího příkladu. Uložte výsledek do vazební proměnné, která je deklarována v hostitelském prostředí SQLPlus. 
VARIABLE g_pocet_nadprumernych number
Declare
v_prumerny_plat Number;
Begin
Select AVG(salary) Into v_prumerny_plat
From employees;
Select count(*) Into :g_pocet_nadprumernych
From employees
Where salary > v_prumerny_plat;
End;
/
PRINT g_pocet_nadprumernych

tema_02-3a

Vyberte seznam zamestnancu, kterí mají nadprumerny plat. a) dotazem s poddotazem Nápověda: Vyuzijte vazební promennou jako globalni promennou. Deklaruje se pomocí SQLPlus prikazu VARIABLE, odkazuje se k ni pomocí jmena s prefixem ":" 
Select Employee_id, Last_name, First_name, Job_id
From Employees
Where salary > (Select AVG(salary)
                From Employees)
/

tema_02-3b

Vyberte seznam zamestnancu, kterí mají nadprumerny plat.
b) proceduralne s ulozením mezivýsledku do promenné.
Nápověda: Vyuzijte vazební promennou jako globalni promennou. Deklaruje se pomocí
SQLPlus prikazu VARIABLE, odkazuje se k ni pomocí jmena s prefixem ":"
VARIABLE ga number
Begin
Select AVG(salary) Into :ga From EMPLOYEES;
End;
/
PRINT ga;

Select Employee_id, Last_name, First_name, Job_id
From EMPLOYEES
Where salary > :ga;

tema_02-4

Napiště PL/SQL blok, který zjistí průměrný, maximální a minimální plat v tabulce EMP. Výsledek zapište do tabulky MESSAGES jako tři samostatné řádky v podobě dvojic ('vysvetleni', pocet). Text vysvětlení pro jednotlivé záznamy vytvořte jednou s využitím deklarované konstanty, jednou pomocí inicializované proměnné a jednou pomocí přiřazovacího příkazu. Nápověda: Tabulka Messages má sloupce Charcol1 (Char Varying) a Numcol1 (Number)

CREATE TABLE messages
( CHARCOL1 VARCHAR2(60)
CHARCOL2 VARCHAR2(60)
NUMCOL1 NUMBER(9,2)
NUMCOL2 NUMBER(9,2)
DATECOL1 DATE
DATECOL2 DATE);

Uložte hodnoty agregací do lokálních proměnných a ty pak použijte v příkazu pro vložení do tabulky.
Upravte řešení předchozího příkladu. Uložte výsledek do vazební proměnné, která je deklarována v hostitelském prostředí SQLPlus.
CREATE TABLE messages
 ( CHARCOL1 VARCHAR2(60),
   CHARCOL2 VARCHAR2(60),
   NUMCOL1  NUMBER(9,2),
   NUMCOL2  NUMBER(9,2),
   DATECOL1 DATE,
   DATECOL2 DATE);

Declare
   V_max Number;
   V_max_text Char Varying(20) DEFAULT 'maximalni plat';
   V_Min Number;
   C_min_text CONSTANT Char Varying(20) := 'minimalni plat' ;
   V_avg Number;
   V_avg_text Char Varying(20);
Begin
   Delete from Messages;
   Select min(salary), max(salary), avg(salary) into V_min, V_max, V_avg
   From employees;
   Insert Into Messages(NumCol1, Charcol1) Values (V_max, V_max_text);
   Insert Into Messages(NumCol1, Charcol1) Values (V_min, C_min_text);
   V_max_text:='prumerny plat';
   Insert Into Messages(NumCol1, Charcol1) Values (V_avg, V_max_text);
   Commit;
End;
/
Select * from messages;

Téma 3 - výjimky a jejich vlastní zpracování, detekce výsledku DML operací (snímek relevantní prezentace z loňska 3 a 8)


tema_03-1

V příkazovém souboru vytvořte a posléze spusťte blok PL/SQL, který zjistí minimální plat v tabulce EMP a zjistí kolik zaměstnanců pobírá tento minimální plat. Je-li jenom jeden, vybere z tabulky EMP jeho pracovní zařazení (JOB). V dalším kroku vybere z tabulky EMP všechny řádky o zaměstnancích, kteří mají stejné pracovní zařazení, jako onen zaměstnanec s minimálním platem.
Do tabulky MESSAGES uložte zprávu, informující o tom, že byl vybrán žádný řádek, jeden řádek, nebo více řádků. Např.
'Min. plat bere mnoho zamestnancu'
'Min. plat bere jeden zamestnanec a ten nema zadneho kolegu',
'Min. plat bere jeden zamestnanec a ten ma jednoho kolegu',
'Min. plat bere jeden zamestnanec a ten ma mnoho kolegu',
Nápověda: Použijte ošetření výjimek NO_DATA_FOUND a TOO_MANY_ROWS. 
Drop table Messages;
CREATE TABLE messages
 ( CHARCOL1 VARCHAR2(60),
   CHARCOL2 VARCHAR2(60),
   NUMCOL1  NUMBER(9,2),
   NUMCOL2  NUMBER(9,2),
   DATECOL1 DATE,
   DATECOL2 DATE);
Declare
   V_min_sal Number;
   V_Job VarChar2(20);
   V_jmeno  VarChar2(20);
   V_vysledny_text Varchar2(60);
   V_pom VarChar2(20);
Begin
  Select min(salary) Into V_min_sal From employees;
  Select JOB_ID, Last_name into v_job, v_jmeno
  From employees
  Where salary = V_min_sal;
  V_vysledny_text:='Min. plat bere jeden zamestnanec '||v_jmeno;
  Begin
    Select Last_name into v_Pom
    From employees
    Where JOB_Id = V_job;
    V_vysledny_text:= V_vysledny_text||' a ten ma jednoho kolegu';
   Exception
     When no_data_found Then
     V_vysledny_text:= V_vysledny_text||' a ten nema zadneho kolegu';
     When too_many_rows Then
     V_vysledny_text:= V_vysledny_text||' a ten ma mnoho kolegu';
   End;
Insert Into Messages (Charcol1)
Values (V_vysledny_text);
Exception
   When too_many_rows Then
   V_vysledny_text:='Min. plat bere mnoho zamestnancu';
   Insert Into Messages (Charcol1)
   Values (V_vysledny_text);
End;
/


select charcol1 from messages
/
delete from messages;
commit;

tema_03-2a

V příkazovém souboru vytvořte a posléze spusťte blok PL/SQL, který vymaže všechny řádky z tabulky NEWDEPT. Spusťte blok a sledujte, co se bude dít. 
drop table NEWDEPT;
create table NEWDEPT as select * from departments;
/
Begin
   Delete From NEWDEPT;
End;
/

tema_03-2b

Upravte blok tak, aby uměl ošetřit chybu, která se objevila. Do tabulky MESSAGES uložte zprávu, založenou na obsahu SQLERRM a SQLCODE. Spusťte blok a sledujte, co se bude dít. Zkontrolujte tabulku MESSAGES a NEWDEPT.Byly vymazány všechny řádky z tabulky NEWDWEPT? Nápověda: Nalezněte číslo standardní chyby, která se oznámí a použijte PRAGMA EXCEPTION_INIT pro vlastní pojmenování a následné zpracování této nepojmenované výjimky. 
Drop table Messages;
CREATE TABLE messages
 ( CHARCOL1 VARCHAR2(60),
   CHARCOL2 VARCHAR2(60),
   NUMCOL1  NUMBER(9,2),
   NUMCOL2  NUMBER(9,2),
   DATECOL1 DATE,
   DATECOL2 DATE);
/
Declare
   L_V_SqlErrm Constant Number:= 55;
   V_SqlErrm Varchar2 (55);
   V_SQLCode Number;
   Ex2292 Exception;
   Pragma exception_init(Ex2292, -2292);
Begin
   Delete From NEWDEPT;
Exception
   When Ex2292 Then
        V_SqlErrm := Substr (SqlErrm,1,L_V_SqlErrm);
        V_SQLCode := SqlCode;
        Insert Into Messages (Charcol1,NumCol1)
        Values (V_SqlErrm,V_SqlCode);
End;
/
select NumCol1, charcol1 from messages
/
select * from NewDept
/

tema_03-2c

V příkazovém souboru vytvořte a posléze spusťte blok PL/SQL, který vymažeřádky z tabulky NEWEMP řádky o zaměstnancích kteří pracují pro manažera jménem BLACK. Do tabulky MESSAGES uložte zprávu o počtu zrušených záznamů, BLACKOVO zaměstnanecké číslo a komentář co se stalo. Např.:

NUMCOL1 NUMCOL2 NUMCOL3
5 7698 Manažer BLAKE ztratil všechny podřízené 
Delete from NEWEMP
/
Insert into NEWEMP
select * from EMPLOYEES

Declare 
C_MgrName Constant NEWEMP.LAST_NAME%Type := 'BLAKE';
V_MgrNo NEWEMP.EMPLOYEE_ID%Type;
V_RowsDeleted Number;
Begin
Select EMPLOYEE_ID Into V_MgrNo
From NEWEMP
Where LAST_NAME  = C_MgrName;
Delete 
From NEWEMP 
Where MANAGER_ID = V_MgrNo;
V_RowsDeleted := Sql%RowCount;
Insert Into Messages (NumCol1, NumCol2, Charcol1)
Values (V_RowsDeleted, 
V_Mgrno, 
'Manažer '||C_MgrName||' ztratil všechny podřízené ');
end;

Téma 4 - řídicí programové struktury (snímek relevantní prezentace z loňska 4 )

tema_04-1

Begin
For V_pocitadlo In 1..10
Loop If V_pocitadlo Not In (6,8) Then
Insert Into Messages (Numcol1)
Values (V_pocitadlo);
End If;
End Loop;
End;
/
select NumCol1 from messages
/ 
Begin
   For V_pocitadlo In 1..10
   Loop If V_pocitadlo Not In (6,8) Then
           Insert Into Messages (Numcol1)
           Values (V_pocitadlo);
        End If;
   End Loop;
End;
/

select NumCol1 from messages
/

tema_04-2

V příkazovém souboru vytvořte a posléze spusťte blok PL/SQL, který vybere ze sloupců last_name, Hire_date a SAL tabulky EMPLOYEES řádek, u nějž se EMPLOYEE_ID rovná číslu šéfa zaměstnance se jménem SCOTT. Dále uloží do tabulky MESSAGES informace podle těchto kritérií:

Kritérium Zpráva
SAL > 1200 'Plat je větší, než 1200'
ENAME obsahuje 'T' 'Jméno obsahuje znak T '
HIREDATE je červencové 'Datum je červencové'
šéf neexistuje 'Zadaný zaměstnanec nemá šéfa'
Nic z uvedeného neplatí 'Nic zajímavého '

Spusťte postupně pro JONES, SCOTT, ADAMS, KING.
Pozn.:
Deklarujte a použijte proměnnou typu Record se složkami ENAME, HIREDATE a SAL. Výsledek si ověřte vypsání tabulky MESSAGES.

Nápověda: Použijte posloupnost příkazových struktur If .. End If. Zprávy uložte do řady lokálně deklarovaných proměnných. Při tvorbě příkazu Insert zadejte zřetězení hodnot těchto proměnných. Při zjišťování,zda hodnota HIREDATE spadá do zadaného měsíce použijte funkci To_Char s formátem 'MM' nebo 'Mon'. Pozor, KING nemá šéfa. 

Delete From Messages;
--
Declare
  type t_r is Record
      (v_Ename       Employees.Last_name%Type,
       v_HireDate    Employees.Hire_Date%Type,
       v_Sal         Employees.Salary%Type);
  v_r           t_r;
  v_Sal_Mess    VarChar2(25);
  v_Name_Mess   VarChar2(25);
  v_HD_Mess     VarChar2(25);
  c_zadane_jmeno VarChar2(25):= 'Jones';
  v_cislo_sefa Number;
Begin
   Select MANAGER_ID into v_cislo_sefa From Employees
   Where initcap(Last_name)=c_zadane_jmeno;
   Select Last_name, Hire_Date, Salary Into
          v_r.v_Ename, v_r.v_HireDate, v_r.v_Sal
          From Employees
          Where Employee_ID = v_cislo_sefa;
   If v_r.v_Sal > 1200 Then
      v_Sal_Mess := 'Plat je vetsĄ, nez 1200';
   End If;
   If Instr (v_r.v_Ename,'T') > 0 Then
      v_name_mess := 'Jm~Bno obsahuje znak T ';
   End If;
   If To_Char (v_r.v_Hiredate, 'MM') = '07' Then
      v_HD_mess := 'Datum je cervencov~B';
   End If;
   Insert Into Messages (CharCol1)
          Values (v_r.v_Ename||' '||
                  To_Char(v_r.v_HireDate)||' '||
                  To_Char(v_r.v_Sal));
   If v_Sal_Mess is Null and
      v_Name_Mess  is Null and
      v_HD_Mess is Null
      Then Insert Into Messages (CharCol1)
           Values ('Nic zajĄmav~Bho ');
      Else
       Insert Into Messages (CharCol1)
              Values (v_Sal_Mess||'; '||v_Name_Mess||'; '||v_HD_Mess);
   End If;
End;
/


select CharCol1 from messages

/

tema_04-3

Upravte úlohu 4_1. Na závěr vložte do tabulky MESSAGES informaci 'číslo je sudé ' nebo 'číslo je liché ' v závislosti na jeho hodnotě.
Původní zadání:
V příkazovém souboru vytvořte a posléze spusťte blok PL/SQL, který uloží do tabulky MESSAGES hodnoty 1 až 10 s výjimkou hodnot 6 a 8. Účinekpráce potvrďte ještě před ukončením bloku. Výsledek si ověřte vypsání tabulkyMESSAGES.
Nápověda: Použijte libovolnou strukturu cyklu. Pamatujte, že COUNT je rezervované slovo. 
Delete From Messages;


Declare
V_Je_Liche VarChar2(20) := 'číslo je liché ';
V_Je_Sude  VarChar2(20) := 'číslo je sudé ';
Begin
For V_pocitadlo In 1..10
Loop If V_pocitadlo Not In (6,8) Then
Insert Into Messages (Numcol1, Charcol1)
Values (V_pocitadlo, Decode(Mod(V_Pocitadlo,2),
1, V_Je_Liche,
0, V_Je_Sude));
End If;
End Loop;
End;

Téma 5 - záznamy a kolekce (snímek relevantní prezentace z loňska 5 )

tema_05-1a

Mějme tabulku studentů
. STUDENT (Stud_id Integer,
Jmeno Varchar(20),
Prijmeni Varchar(20),
Obor Varchar2(10):{'ZPEV','HRA','KRITIKA'},
Vaha:Number(6.2))

Jako podklad pro rozdělení do oborů byli zúčastnění studenti obodováni:
ROZRAZENI(Stud_id Integer, Body Number) V tabulce STUDENT jsou záznamy o všech studentech V tabulce ROZRAZENI jsou vyjmenovaným studentům přiděleny body. Někteří studenti z tabulky STUDENT mohou v tabulce ROZRAZENI chybět. Aktualizujte tabulku STUDENT na základě podkladů z tabulky ROZRAZENI. Jestliže má student v tabulce ROZRAZENI počet bodů větší, než je průměr a má dosud nepřiřazený obor, přiřaďte mu obor ?HRA?. Jestliže student nesplnil předchozí podmínku a v tabulce ROZRAZENI má počet bodů větší, než jsou 2/3 průměru a má dosud nepřiřazený obor, přiřaďte mu obor ?ZPEV?. Ostatním studentům, kteří jsou zastoupeni v tabulce ROZRAZENI a nemají dosud přiřazený obor přiřaďte obor 'KRITIKA'. 
Drop table Student cascade constraints;
Drop table Rozrazeni cascade constraints;
create table STUDENT (Stud_id Integer NOT NULL,
                      Jmeno Varchar(20),
                      Prijmeni Varchar(20),
                      Obor Varchar2(10) check ( Obor in ('ZPEV','HRA','KRITIKA')),
                      Vaha Number(6,2),
                      CONSTRAINT Stud_ID_key PRIMARY KEY (Stud_id) );

create table ROZRAZENI(Stud_id Integer,
                       Body  Number,
                       Constraint UNQ_Rozrazeni Unique (Stud_id));
Alter table ROZRAZENI add (
      Constraint FK_Rozrazeni Foreign Key (Stud_id) References Student(Stud_id)
);
begin
 for i in 1..100 loop
  insert into student(Stud_id,Jmeno,Prijmeni,Vaha) values(i,'Student_jmeno'||i,'Student_primeni'||i,i);
  if (i mod 10) != 0 then insert into rozrazeni(Stud_id,Body) values (i,i);
  end if;
 end loop;
end;
/
select * from Student;
select * from Rozrazeni;





Declare
CURSOR c1 is
       (Select stud_id, body
        From ROZRAZENI);
V_prumer Number;
v_obor varchar2(20);
Begin
Select avg(body) into v_prumer from rozrazeni;
For c1_zaznam in c1
Loop
 If c1_zaznam.body > v_prumer then            v_obor:= 'HRA';
     Elsif c1_zaznam.body > v_prumer*2/3 then v_obor := 'ZPEV';
     Else                                     v_obor := 'KRITIKA';
 End If;
 Update student set obor = v_obor where stud_id = c1_zaznam.stud_id;
End Loop;
Commit;
End;

/

tema_05-1b

Mějme tabulku studentů.
STUDENT (Stud_id Integer,
Jmeno Varchar(20),
Prijmeni Varchar(20),
Obor Varchar2(10):{'ZPEV','HRA','KRITIKA'},
Vaha:Number(6.2))
Napište fragment programu, kde se načte obsah tabulky STUDENT do indexované kolekce. Ve druhém kroku tuto kolekci nezměněnu zapíše do tabulky STUDENT2 

Drop table Student cascade constraints;
Drop table Rozrazeni cascade constraints;
create table STUDENT (Stud_id Integer NOT NULL,
                      Jmeno Varchar(20),
                      Prijmeni Varchar(20),
                      Obor Varchar2(10) check ( Obor in ('ZPEV','HRA','KRITIKA')),
                      Vaha Number(6,2),
                      CONSTRAINT Stud_ID_key PRIMARY KEY (Stud_id) );

create table ROZRAZENI(Stud_id Integer,
                       Body  Number,
                       Constraint UNQ_Rozrazeni Unique (Stud_id));
Alter table ROZRAZENI add (
      Constraint FK_Rozrazeni Foreign Key (Stud_id) References Student(Stud_id)
);
begin
 for i in 1..100 loop
  insert into student(Stud_id,Jmeno,Prijmeni,Vaha) values(i,'Student_jmeno'||i,'Student_primeni'||i,i);
  if (i mod 10) != 0 then insert into rozrazeni(Stud_id,Body) values (i,i);
  end if;
 end loop;
end;
/
select * from Student;
select * from Rozrazeni;

Declare
CURSOR c1 is Select stud_id, body From ROZRAZENI;
V_prumer Number;
Type t_studenti is table of Integer Index by Binary_integer;
v_studenti t_studenti; -- bude obsahovat seznam obodovanych studentu
Type t_rozrazeni is table of varchar2(20) Index by Binary_Integer ;
v_rozrazeni t_rozrazeni; -- bude kolekci novych zarazeni studentu z v_studenti
-- studentovi v_studenti(i) bude prirazen obor v_rozrazeni(i)
V_index Binary_Integer;
Begin
Select avg(body) into v_prumer from rozrazeni;
V_index := 0;
For c1_zaznam in c1
Loop
  v_Index := v_index+1;
  v_studenti(V_index) := c1_zaznam.stud_id;
  If c1_zaznam.body > v_prumer then        v_rozrazeni(V_index) := 'HRA';
  ElsIf c1_zaznam.body > v_prumer*2/3 then v_rozrazeni(V_index) := 'ZPEV';
  Else                                    v_rozrazeni(V_index) := 'KRITIKA';
  End If;
End Loop;
-- ted je naplnena kolekce v_studenti ID vsech studentu z tabulky ROZRAZENI
-- ve stejnolehlych pozicich v kolekci v_rozrazeni je naplnen novy obor daneho studenta
ForAll  i in 1 .. v_index
Update student set obor = v_rozrazeni(i)
Where stud_id = v_studenti(i);
Commit;
End;

/

tema_05-2

V tabulce halaska.zaznamy jsou zaznamenany udalosti, majici radu atributu, ale pro nas je zajimavy cas zacatku udalosti (datetime) a cas konce (datetimeend), udavano v sekundach.

Vytvorte histogram, ktery bude informovat kolik udalosti probiha soucasne pro kazdy diskretni cas. Porovnejte casovou narocnost nekolika moznych reseni. Nápověda: Začněte zápisem histogramu do tabulky přímo pomocí SQL, pak počítejte histogramu v paměti a spočtený zapisujte do tabulky. Druhý typ zrychleni je založen na použití "bulk insert". Pro měření času použijte standardní funkci current_timestamp.

Set echo on
Drop table Student cascade constraints;
Drop table Student2 cascade constraints;
Create table Student
(Stud_id Integer,
 Jmeno Varchar(20),
 Prijmeni Varchar(20),
 Obor Varchar2(10),
 Vaha Number(6,2));
Create table Student2 as Select * from Student;
-- naplneni tabulky Student testovacimi daty
Begin
For i in 1..999 Loop
Insert Into Student
Values (i*2,'Jmeno'||to_char(i,'009'),'Prijmeni'||to_char(i,'009'),NULL,50+mod(i,10));
End Loop;
End;
/


Declare
CURSOR c1 is Select * From STUDENT;
Type t_studenti is table of STUDENT%ROWTYPE
     Index by Binary_integer;
v_studenti t_studenti;
V_index Binary_Integer;
i binary_integer;
Begin
V_index := 0;
For c1_zaznam in c1
Loop
  v_studenti(c1_zaznam.stud_id) := c1_zaznam;
End Loop;
-- dale budu kolekci v_studenti nejak pretvaret
--
-- nasleduje zapis pretvorene kolekce v_studenti do tabulky STUDENT2
i:=v_studenti.First;
While i is not NULL Loop
Insert Into student2 (Stud_id,Jmeno,Prijmeni,Obor,Vaha)
       Values(v_studenti(i).Stud_id,
              v_studenti(i).Jmeno,
              v_studenti(i).Prijmeni,
              v_studenti(i).Obor,
              v_studenti(i).Vaha);
i:=v_studenti.Next(i);
End Loop;
Commit;
End;
/
Select * from student2;

Téma 6 - kurzor (snímek relevantní prezentace z loňska 6 a 7)

tema_06-1

S využitím iterativní struktury 'For record in Cursor' vytvořte blok PL/SQLkterý vybere pět zaměstnanců s nejvyšším příjmem (z tabulky EMP). HodnotyEMPNO, ENAME a SAL vložte do tabulky MESSAGES. Výsledek si ověřte vypsáním tabulky MESSAGES.

Nápověda: Vytvořte kurzor s klauzulí Order By. Procházení řádků kurzoru ukončete pomocí atributu %ROWCOUNT.

Delete From Messages;
--
Declare
  Cursor C1 Is
         Select Employee_ID, Last_name, salary
          From  employees
          Order By salary Desc;
Begin
   For Radek In C1
       Loop
           Insert Into MESSAGES (NumCol1,     NumCol2,   CharCol1)
                  Values        (Radek.Employee_ID, Radek.Salary, Radek.Last_name);
       Exit When C1%RowCount = 5;
       End Loop;
       Commit;
End;
/
-- zde uz jsme v SQL*Plus prostredi.
-- vytisknutĄ obsahu tabulky Messages, kam se ukl| dajĄ mezivěsledky 
Column Ename Format A30
select NumCol1 Empno, CharCol1 Ename, NumCol2 Plat from messages
/

tema_06-2

S využitím explicitního kurzoru a jeho atributů vytvořte blok PL/SQL kterýprovede zvýšení platu limitované celkovým balíkem k rozdělení. Celkovýobjem mzdových prostředků byl zvýšen na 35000 peněz. Pro tento účel si uspořádejme zaměstnance podle výše platu vzestupně. Tedy zaměstnanec s nejnižším platem bude první. V tomto pořadí postupně budeme zvyšovat všem zaměstnancům plat o patnáct procent tak dlouho, dokud nevyčerpáme celkový balík k rozdělení (tedy do dosažení celkového součtu platů 35 000 peněz).Na ostatní se už nedostane. Blok PL/SQL nechť pracuje nad tabulkou NEWEMP.Blok by měl vydolovat totální součet platů pouze jednou a provést pouzejeden průchod tabulkou za účelem úpravy platů. (Znovu zkontrolujte totálnísoučet po aktualizaci všech individuálních platů).

Do tabulky MESSAGES zapište seznam všech čísel zaměstnanců, kterým byl upraven plat a vložte tam totální součet platů na konci bloku.

Poznámka:
Blok by měl upravovat všechny řádky pdokud nebude dosaženo totálníhosoučtu 35000 peněz. Je-li tabulka v počátečním stavu, dojde napoprvé kezvýšení platu všem zaměstnancům. Spusťte blok ještě jednou pro další kolo zvýšení platu. To už by se nemělo dostat na všechny. Výsledek si ověřte vypsání tabulky MESSAGES.

Nápověda: V úvodu zjistěte jaký se teď totální součet platů auložte do lokální proměnné. Stačí, když váš explicitní kurzor bude obsahovat pouze sloupec s platy jednotlivých zaměstnanců. Víc nepotřebujete. Zvyšujte jednotlivcům jejich plat a přidávejte hodnotu přírůstku k totálnímu součtuv proměnné. Předčasný konec nastane až se překročí hodnota totálního součtu 35000. V definici kurzoru použijte dotaz 'Select ... For Update'. Následně v příkazu Update použijte klauzuli Where Current Of.

Drop table newemp;
create table newemp as select * from employees;
Declare
  Cursor C1 Is
         Select salary
          From  NEWEMP
          Order By salary ASC
          For Update Of salary;
 v_citac Number := 0;
 v_sal   NEWEMP.salary%TYPE := 0;
 v_Total Number (9,2) := 0;
Begin
   Select Sum(salary) Into v_Total From NEWEMP;
   Open C1;
   While v_Total <= 35000
       Loop
           DBMS_output.put(v_Total); DBMS_output.put(':');
           Fetch C1 Into v_sal;
           DBMS_output.put('+');DBMS_output.put (V_Sal*0.15);
           Exit When C1%NotFound;
           Update NEWEMP
                  Set salary = salary * 1.15
                  Where Current Of C1;
           v_Total := v_Total + v_sal*0.15;
           v_Citac := v_Citac + 1;
           DBMS_output.new_line;
       End Loop;
   Insert Into MESSAGES (NumCol1, CharCol1)
               Values   (v_Total,
                         'Provedeno '||To_Char(v_citac)||' Łprav platu');
   Commit;
End;
/

Column Ename Format A30
select NumCol1 "Nově Total", CharCol1 from messages
/

Téma 7 - procedury a funkce (snímek relevantní prezentace z loňska 9 ,  11  a 12 , také 10)

tema_07-1

Vytvořte proceduru se jménem ADD_PROD, která vloží nový řádek do tabulky PRODUCT. Ověřte proceduru opakovaným zavoláním. Vložte dva nebo tři řádky o nových výrobcích. Zavolejte proceduru ještě jednou a zadejte jakočíslo nového výrobku PRODID = 100860. Co se stane a proč?

Nápověda: Používejte příkaz SHOW ERRORS pro zobrazení kompilační chchyb. Procedura by měla přijímat dva parametry. Jeden číselný a druhý textový. Uložte je do tabulky PRODUCT. Formát příkazu pro volání zprostředí SQL*Plus:

SQL>EXECUTE jméno_procedury(par1,par2) 

Drop table product;
Create table product(prodid integer unique,
                     Description varchar(40)
);
Create or Replace Procedure
  ADD_PROD(v_ProdId PRODUCT.PRODID%Type, v_Descrip PRODUCT.Description%Type)
As
begin
   Insert Into PRODUCT (Prodid, Description)
               Values(v_ProdId, v_Descrip);
End ADD_PROD;
/
Show errors

tema_07-2

Vytvořte proceduru se jménem DEL_PROD, která vymaže jeden řádek z tabulky PRODUCT dostane-li jeho číslo PRODID. Před testováním se ujistěte, že vprostředí SQL*Plus je nastaveno Set Autocommit Off. Ověřte proceduru opakovaným zavoláním. Vypište obsah tabulky. Zavolejte proceduru ještě jednou a zadejte neexistující číslo výrobku. Co se stane a proč?

Nápověda: Používejte příkaz SHOW ERRORS pro zobrazení kompilačních chyb. Procedura by měla přijímat jeden číselný parametr. Následně provede Delete takto určeného řádku z tabulky PRODUCT. Formát příkazu pro volání z prostředí SQL*Plus:

SQL>EXECUTE jméno_procedury(par)

Create or Replace Procedure
  DEL_PROD(v_ProdId PRODUCT.PRODID%Type)
As
Begin
   Delete PRODUCT Where Prodid = v_Prodid;
End DEL_PROD;
/

tema_07-3

Vytvořte proceduru se jménem UPD_PROD, která upraví popis výrobku v tabulce PRODUCT dostane-li jeho číslo PRODID. Ověřte proceduru opakovaným zavoláním.Vypište obsah tabulky. Zavolejte proceduru ještě jednou a zadejte neexistujícíčíslo výrobku. Co se stane a proč?

Nápověda: Používejte příkaz SHOW ERRORS pro zobrazení kompilačních chyb. Procedura by měla přijímat dva parametry, první číselný parametr,druhý textový). Formát příkazu pro volání z prostředí SQL*Plus:

SQL>EXECUTE jméno_procedury(par1,par2) 

Create or Replace Procedure
  UPD_PROD(v_Prodid PRODUCT.PRODID%Type,v_Descrip PRODUCT.DESCRIPTION%Type)
As
Begin
   Update PRODUCT
          Set DESCRIPTION = v_Descrip
          Where Prodid = v_Prodid;
End UPD_PROD;
/

tema_07-4

Vytvořte proceduru se jménem Q_PROD, která vrátí popis zadaného výrobku. Ověřte proceduru opakovaným zavoláním. Zavolejte proceduru ještě jednoua zadejte jako číslo neexistujícího výrobku PRODID = 100860. Co se stane a proč?

Nápověda: Používejte příkaz SHOW ERRORS pro zobrazení kompilačních chyb. Funkce by měla přijímat jeden číselný parametr. Otestujte přiřazení vazební proměnné SQL*Plus.Formát příkazu pro volání z prostředí SQL*Plus:

SQL>Variable jmeno1 typ
SQL>EXECUTE :jmeno1 := jméno_procedury(par)
SQL>Print jmeno1

Create or Replace Function
  Q_PROD(v_ProdId PRODUCT.PRODID%Type)
  Return  PRODUCT.Description%Type
As
v_Descrip PRODUCT.Description%Type;
Begin
   Select Description Into v_Descrip
          From PRODUCT
          Where ProdId = v_ProdId;
  Return (v_Descrip);
End Q_PROD;
/

Variable Pom Varchar2(30)
Execute :Pom := Q_Prod(100860)
Print Pom
Execute :Pom := Q_Prod(70)
Print Pom

tema_07-5

Upravte funkci Q_PROD z úlohy 7_4 tak, aby se uživateli nezobrazovala chybová zpráva ORA-01403: no data found. v případě zadání neexistujícího PRODID.

Namísto ní zobrazte zprávu ORA-20202: Výrobek s číslem xx v tabulcePRODUCT není, kde xx je zadaný parametr funkce.

Create or Replace Function
  Q_PROD(v_ProdId PRODUCT.PRODID%Type)
  Return  PRODUCT.Description%Type
As
v_Descrip PRODUCT.Description%Type;
Begin
   Select Description Into v_Descrip
          From PRODUCT
          Where ProdId = v_ProdId;
  Return (v_Descrip);
Exception
  When No_Data_Found Then
       Raise_Application_Error
       (-20202, 'Věrobek s cĄslem '||v_ProdId||' v tabulce PRODUCT nenĄ');
End Q_PROD;
/

Variable Pom Varchar2(30)
Execute :Pom := Q_Prod(100860)
Print Pom
Execute :Pom := Q_Prod(70)
Print Pom

tema_07-6

Upravte proceduru DEL_PROD (viz. úloha 7_2). Jak jste si ověřili, proceduranijak nereaguje na zadání neexistujícího čísla výrobku. Nová verze procedury bude na tuto situaci reagovat sdělením ORA-20203: Výrobek zadaného číslaneexistuje.

Nápověda: Dopad operace Delete ověřte pomocí atributu kurzoru%NotFound. Použijte k tomu větvení IF. V případě, že atribut bude mít hodnotuTRUE, vyvolejte Raise_Application_Error. Formát příkazu pro volání z prostředíSQL*Plus:

SQL>EXECUTE jméno_procedury(par1) 

Create or Replace Procedure
  DEL_PROD(v_ProdId PRODUCT.PRODID%Type)
As
Begin
   Delete PRODUCT Where Prodid = v_Prodid;
   If Sql%NotFound Then
      Raise_Application_Error
      (-20203, 'Věrobek zadan~Bho cisla '||v_Prodid||' neexistuje');
   End If;
End DEL_PROD;
/
Execute Del_Prod(70)

tema_07-7

Upravte proceduru UPD_PROD (viz úloha 7_3) Jak jste si ověřili, proceduranijak nereaguje na zadání neexistujícího čísla výrobku. Nová verze procedury bude na tuto situaci reagovat sdělením ORA-20203: Výrobek zadaného čísla neexistuje.

Nápověda: Dopad operace Update ověřte pomocí atributu kurzoruSql%NotFound. Použijte k tomu větvení IF. V případě, že atribut bude mít hodnotu TRUE, vyvolejte Raise_Application_Error. Formát příkazu pro volání z prostředí SQL*Plus:

SQL>EXECUTE jméno_procedury(par1, par2)

Formát příkazu pro volání z prostředí SQL*Plus:

SQL>EXECUTE jméno_procedury(par1,par2) 

Create or Replace Procedure
  UPD_PROD(v_Prodid PRODUCT.PRODID%Type,v_Descrip PRODUCT.DESCRIPTION%Type)
As
Begin
   Update PRODUCT
          Set DESCRIPTION = v_Descrip
          Where Prodid = v_Prodid;
   If Sql%NotFound Then
      Raise_Application_Error
      (-20203, 'Věrobek zadan~Bho cĄsla '||v_Prodid||' neexistuje');
   End If;
End UPD_PROD;
/
Execute Upd_Prod(70,'Oprava popisu')

tema_07-8

Upravte proceduru ADD_PROD (viz úloha 7_1). Opakované zadání již použitého čísla zatím vyvolá nepojmenovanou výjimku ORA-00001 (narušení integritního omezení UNIQUE) Nahraďte její standardní ohlášení vlastním sdělením, např:.ORA-20203: Zadané číslo má již jiný výrobek.

Nápověda: Deklarujte jméno pro výjimku, např.: Cons_vio. Použijte Pragma Excepion_Init pro přiřazení tohoto jména dosud nepojmenované výjimceč. -1. Doplňte vlastní ošetření výjimky v sekci Exception.

Formát příkazu pro volání z prostředí SQL*Plus:

SQL>EXECUTE jméno_procedury(par1,par2) 

Create or Replace Procedure
  ADD_PROD(v_ProdId PRODUCT.PRODID%Type, v_Descrip PRODUCT.Description%Type)
As
   Cons_vio Exception ;
   Pragma Exception_Init (Cons_vio, -1);
begin
   Insert Into PRODUCT (Prodid, Description)
               Values(v_ProdId, v_Descrip);
Exception
   When Cons_vio Then
        Raise_Application_Error
        (-20203,'Zadan~B cĄslo m|  jiz jině věrobek');
End ADD_PROD;
/

EXECUTE ADD_PROD(70,'aa');
EXECUTE ADD_PROD(70,'bb');
select * from product;

tema_07-9

řte rekurzivní funkci pro výpočet faktoriálu
Oveřte, pro které n dojde k přetečení.
poznámka: maximální hodnota typu number je 10**127 

create or replace function fak(n in number) return number
is
begin
if n = 0 then return 1;
else return n*fak(n-1);
end if;
end fak;
/
show errors
variable g number
execute :g := fak(83)
print g

tema_07-_10

V tabulce Employees je cizí klíč Manager_id, který odkazuje k primárnímu klíči téže tabulky. Je tím zaznamenán vztah daný zaměstnanec má za šéfa daného zaměstnance. Je tak vytvořena stromová hierarchie mezi řádky této tabulky. Vytvořte rekurzivní funkci pro vytvoření seznamu podřízených zadaného pracovníka.

create or replace procedure ktere_ma_sefy(p_employee_id in employees.employee_id%type)
is
v_emp_rec employees%rowtype;
v_manager_id employees.manager_id%type;
begin
Select manager_id into v_manager_id
From employees
where employee_id=p_employee_id;
If v_manager_id is NULL then return; end if;
Select * into v_emp_rec From employees
where employee_id=v_manager_id;
dbms_output.put_line(v_emp_rec.last_name);
ktere_ma_sefy(v_emp_rec.employee_id);
return;
end;
/

tema_07-_11

V tabulce Employees je cizí klíč Manager_id, který odkazuje k primárnímu klíči téže tabulky. Je tím zaznamenán vztah daný zaměstnanec má za šéfa daného zaměstnance. Je tak vytvořena stromová hierarchie mezi řádky této tabulky. Vytvořte rekurzivní funkci pro vytvoření seznamu nadřízených zadaného pracovníka. 

create or replace procedure ktere_ma_podrizene(p_employee_id in employees.employee_id%type, p_hloubka in integer default 0)

is v_hloubka integer:=p_hloubka+1;
v_emp_rec employees%rowtype; v_manager_id employees.manager_id%type;
cursor cur_podrizeni is select * from employees where manager_id=p_employee_id; 
begin 
For p_emp_cur in cur_podrizeni loop dbms_output.put_line(p_emp_cur.employee_id|| lpad('_',v_hloubka*4,'_')|| p_emp_cur.last_name||'(uroven podrizeni=' ||to_char(v_hloubka)||')');
ktere_ma_podrizene(p_emp_cur.employee_id, v_hloubka); 
end loop;
&nbsp;end;
&nbsp;/ 
show errors set serveroutput on declare v_a integer; 
begin

Select employee_id into v_a From employees Where last_name='King';

ktere_ma_podrizene(v_a); 
end;
&nbsp;

Téma 8 - datový slovník

tema_08-1

použitím datového slovníku vypište seznam všech vašich procedur a funkcí. Do seznamu začleňte jméno objektu, jeho typ a datum, kdy byl vytvořen.
Nápověda: Použijte pohled do datového slovníku USER_OBJECTS s výběrovou podmínkou vztaženou na sloupec OBJECT_TYPE. 
Column object_name format A30
Select object_name, object_type, created
      From user_objects
      Where object_type in ('PROCEDURE','FUNCTION')
/

tema_08-2

Vytvořte dávkový soubor s příkazem, kterým vyvoláte popis všech vašich procedur a funkcí. Přesměrujte výstup do spool souboru.

Nápověda: Jde o použití SQL pro vytváření SQL příkazů. Vytvořte dávku s touto strukturou:

SQL>Spool jméno_souboru
SQL>Select
SQL>Spool Off

Set Heading Off
Set Echo Off
Set Verify Off
Set Feedback Off

Column object_name format A30
Select 'Describe '||object_name
        From user_objects
        Where object_type in ('PROCEDURE','FUNCTION')
/

tema_08-3

Vytvořte dávkový soubor s příkazem, kterým vyvoláte zdrojový text zadané procedury/funkce.
Nápověda: Použijte pohled User_Source.
Set Heading Off
Set Echo Off
Set Verify Off
Set Feedback Off
Set Pause Off
Select Text
        From user_source
        Where NAME = Upper('&jmeno_objektu')
/
Set Verify On
Set FeedBack On
/*
Pro test pou~Vijte napý. parametr ADD_PROD */

tema_08-4

Předpokládejme, že se vám ztratil původní dávkový soubor pro vytvoření procedury ADD_PROD. Upravte dávkový soubor z úlohy 26_3 tak, abyste získali nový soubor se zdrojovým textem procedury. Pokuste se upravit příkaz Select tak, aby vystupoval přímo příkaz Create Or Replace Procedure ....

Nápověda: Pro vytvoření více řádků cílového příkazu použijte množinové sjednocení výsledku jednotlivých dotazů 

Set Heading Off
Set Verify Off
Set Feedback Off

Column pom Format A18
Column Text Format A79 Truncate
Select 'Create Or Replace ' cor From Dual
Union all
Select  Text
        From user_source
        Where NAME = Upper('&jmeno_objektu')
Union All
Select '/' from dual
/

Set Verify On
Set FeedBack On

Téma 9 - package (snímek relevantní prezentace z loňska 13 17)

tema_09-1

Zkontrolujte status procedur a funkcí ADD_PROD, DEL_PROD, UPD_PROD, a Q_PROD.Měly by být ve stavu 'Valid'. Spojte je všechny do jednoho programového modulu (package) jménem PROD_PACK. Ve specifikaci je všechny zveřejněte. Zrušte samostatné procedury ADD_PROD, DEL_PROD, UPD_PROD a Q_PROD.
Otestujte modul voláním všech jeho procedur a funkcí.

Nápověda: Jako zdroj použijte původní soubory s příkazy pro vytvoření samostaných procedur a funkcí. Dejte pozor, aby si parametry hlaviček procedurve specifikaci modulu a v těle modulu přesně odpovídaly. Formát pro testovací volání:

SQL>Execute jmeno_modulu.jmeno_procedury(parametry) 

Create or Replace Package Prod_Pack
-- specifikace modulu
As
Procedure ADD_PROD( v_Prodid  PRODUCT.PRODID%Type,
    v_Descrip PRODUCT.Description%Type);
Procedure UPD_PROD(v_Prodid PRODUCT.PRODID%Type,
                   v_Descrip PRODUCT.DESCRIPtion%Type);
Procedure DEL_PROD(v_ProdId PRODUCT.PRODID%Type);
Function Q_PROD (v_ProdId PRODUCT.PRODID%Type)
 Return  PRODUCT.Description%Type;
End Prod_Pack;
/
Create or Replace Package Body Prod_Pack
As
Procedure ADD_PROD( v_Prodid  PRODUCT.PRODID%Type,
    v_Descrip PRODUCT.Description%Type)
Is
   Cons_vio Exception;
   Pragma   Exception_Init (Cons_vio, -1);
begin
   Insert Into PRODUCT (Prodid, Description)
               Values(v_ProdId, v_Descrip);
Exception
 When Cons_vio Then
      Raise_Application_Error
      (-20203,'Zadan~B cĄslo m|  jiz jině věrobek');
End ADD_PROD;
Procedure UPD_PROD(v_Prodid PRODUCT.PRODID%Type,
                   v_Descrip PRODUCT.DESCRIPTION%Type)
Is
Begin
   Update PRODUCT
          Set DESCRIPTION = v_Descrip
          Where Prodid = v_Prodid;
   If Sql%NotFound Then
      Raise_Application_Error
      (-20203, 'Věrobek zadan~Bho cĄsla '||v_Prodid||' neexistuje');
   End If;
End UPD_PROD;
Procedure DEL_PROD(v_ProdId PRODUCT.PRODID%Type)
Is
Begin
   Delete PRODUCT Where Prodid = v_Prodid;
   If Sql%NotFound Then
      Raise_Application_Error
      (-20203, 'Věrobek zadan~Bho cĄsla '||v_Prodid||' neexistuje');
   End If;
End DEL_PROD;
Function Q_PROD(v_ProdId PRODUCT.PRODID%Type)
  Return  PRODUCT.Description%Type
Is
v_Descrip PRODUCT.Description%Type;
Begin
   Select Description Into v_Descrip
          From PRODUCT
          Where ProdId = v_ProdId;
  Return (v_Descrip);
Exception
  When No_Data_Found Then
       Raise_Application_Error
       (-20202, 'Věrobek s cĄslem '||v_ProdId||' v tabulce PRODUCT nenĄ');
End Q_PROD;
End Prod_Pack;
/
show errors
-- tady uz jsme v prostredĄ SQL*Plus.
Variable Pom Varchar2(30)
Execute :Pom := Prod_pack.Q_Prod(100860)
Print Pom
Execute :Pom := Prod_pack.Q_Prod(70)
Print Pom

tema_09-2

Napište proceduru zvanou ADD_ITEM, která vytvoří novou položku do zadané faktury. Procedura přidá řádek do tabulky ITEM a měla by postupovat v těchto krocích:

a) Přijme zadání ORDID, ITEMID, PRODID, ACTUALPRICE, QTY jako vstupní parametry. Implicitní hodnotou QTY bude 1.
b) automaticky spočte novou hodnotu ITEM Total (součet všech položekjedné objednávky.
c) Uloží nový řádek do tabulky ITEM a aktualizuje položku TOTAL v odpovídajícímřádku tabulky ORD zahrnutím hodnoty nové položky.

Nápověda:
Existují dvě tabulky ORD, ITEM a PRODUCT. Jejich definice následuje.

Name null? Type
Orid not null number 4
itemid not null date
proid
number(6)

Formát pro testovací volání:
SQL>Execute jmeno_procedury(parametry) 

drop table ord;
drop table item ;
create table ord (
ORDID NUMBER(4) NOT NULL UNIQUE,
ORDERDATE DATE,
COMMPLAN VARCHAR2(1),
CUSTID  NUMBER(6) NOT NULL,
SHIPDATE DATE,
TOTAL NUMBER(8,2)
);
create table item (
ORDID  NUMBER(4) NOT NULL,
ITEMID  NUMBER(4) NOT NULL UNIQUE,
PRODID NUMBER(6),
ACTUALPRICE NUMBER(8,2),
QTY NUMBER(8),
ITEMTOT NUMBER(8,2)
);


Create or Replace
 Procedure Add_Item
           ( a_ORDID      ITEM.ORDID%Type,
            a_ITEMID      ITEM.ITEMID%Type,
            a_PRODID      ITEM.PRODID%Type,
            a_ACTUALPRICE ITEM.ACTUALPRICE%Type,
            a_QTY         ITEM.QTY%Type Default 1)
As
v_ITEMTOT   ITEM.ITEMTOT%Type;
Begin
  v_ITEMTOT := a_QTY * NVL(a_ACTUALPRICE,0);
  Insert Into ITEM
         (ORDID,   ITEMID,   PRODID,   ACTUALPRICE,   QTY,   ITEMTOT)
  Values (a_ORDID, a_ITEMID, a_PRODID, a_ACTUALPRICE, a_QTY, v_ITEMTOT);
  Update ORD
         Set TOTAL = TOTAL + v_ITEMTOT
         Where ORDID = a_ORDID;
End Add_Item;
/

Set autocommit off
Column Ordid format 99999
column Itemid format 999999
column Prodid format 999999
column Actualprice format 9999.99
column Qty format 999
column Itemtot format 9999999
column descrip format A15
column vyrobek format A15

Execute ADD_ITEM(610,4,100860,30,200)
Select Item.*, ORD.ORDERDATE, PRODUCT.DESCRIPtion
       From Item,Ord, Product
       Where Item.Ordid = Ord.Ordid And Item.Prodid = Product.Prodid And
             ORD.Ordid = 610;

Execute ADD_ITEM(610,4,100860,30,200)

Execute ADD_ITEM(300,4,100860,30,1)

Execute ADD_ITEM(610,5,100865,30,1)
Select Item.*, ORD.ORDERDATE, NVL(PRODUCT.DESCRIP,'neexistuje') vyrobek
     From Item,Ord, Product
     Where Item.Ordid = Ord.Ordid And Item.Prodid = Product.Prodid(+)
           And ORD.Ordid = 610;
Rollback;

tema_09-3a

Upravte proceduru ADD_ITEM. S využitím EXCEPTION_INIT zajistěte, aby bylyošetřeny všechny chyby, které mohou nastat. Chybné číslo
objednávky, duplicitní číslo položky na objednávce, chybné číslo zboží.Chyby zachyťte a sdělte společnou zprávu tohoto znění: 'Chybné číslo objednávkynebo/a chybné číslo zboží nebo/a duplicitní číslo objednací položky '

Nápověda: Čísla chyb, které mohou nastat zjistěte spuštěním proceduryADD_ITEM v provedení v jakém je po vyřešení úlohy 27_2.

SQL> -- ještě jednou přidáme tutéž položku
SQL> Execute ADD_ITEM(610,4,100860,30,200)
ERROR at line 1:
ORA-00001: unique constraint (OPS$HALASKA.ITEM_PRIMARY_KEY) violated
SQL> -- přidáme položku k neextující objednávce 300
SQL> Execute ADD_ITEM(300,4,100860,30,1)
ERROR at line 1:
ORA-02291: integrity constraint (OPS$HALASKA.ITEM_F_K_Ordid) violated- parent key not found
SQL> -- přidáme položku k objednávce 610
SQL> -- na neexistující zboží 100865
ERROR at line 1:
ORA-02291: integrity constraint (OPS$HALASKA.ITEM_FK_Prodid) violated- parent key not found

Formát pro testovací volání:
SQL>Execute jmeno_procedury(parametry)

Create or Replace
 Procedure Add_Item
           ( a_ORDID      ITEM.ORDID%Type,
            a_ITEMID      ITEM.ITEMID%Type,
            a_PRODID      ITEM.PRODID%Type,
            a_ACTUALPRICE ITEM.ACTUALPRICE%Type,
            a_QTY         ITEM.QTY%Type Default 1)
As
v_ITEMTOT   ITEM.ITEMTOT%Type := 0;
e_F_K   Exception;
e_P_K   Exception;
Pragma Exception_init (e_F_K,-2291);
Pragma Exception_init (e_P_K,-1);
Begin
  v_ITEMTOT := a_QTY * NVL(a_ACTUALPRICE,0);
  Insert Into ITEM
         (ORDID,   ITEMID,   PRODID,   ACTUALPRICE,   QTY,   ITEMTOT)
  Values (a_ORDID, a_ITEMID, a_PRODID, a_ACTUALPRICE, a_QTY, v_ITEMTOT);
  Update ORD
         Set TOTAL = TOTAL + v_ITEMTOT
         Where ORDID = a_ORDID;
Exception
  When e_F_K Then Raise_Application_Error
                  (-20399, 'Chybn~B cĄslo objedn| vky nebo zbozĄ');
  When e_P_K Then Raise_Application_Error
                  (-20399, 'DuplicitnĄ cĄslo obj. polozky');
End Add_Item;
/


Set autocommit off
Column Ordid format 99999
column Itemid format 999999
column Prodid format 999999
column Actualprice format 9999.99
column Qty format 999
column Itemtot format 9999999
column vyrobek format A15


Execute ADD_ITEM(610,4,100860,30,200)
--
Select Item.*, ORD.ORDERDATE, PRODUCT.DESCRIP
       From Item,Ord, Product
       Where Item.Ordid = Ord.Ordid And Item.Prodid = Product.Prodid And
             ORD.Ordid = 610;

Select Item.*, ORD.ORDERDATE, PRODUCT.DESCRIP
       From Item,Ord, Product
       Where Item.Ordid = Ord.Ordid And Item.Prodid = Product.Prodid And
             ORD.Ordid = 610;
-- je1ti jednou poid| me tut~B3 polo3ku
Execute ADD_ITEM(610,4,100860,30,200)
-- poid| me polo3ku k neextujĄcĄ objedn| vce 300
Execute ADD_ITEM(300,4,100860,30,1)
-- poid| me polo3ku k objedn| vce 610 na neexistujĄcĄ zbo3Ą 100865
Execute ADD_ITEM(610,5,100865,30,1)
-- 
Select Item.*, ORD.ORDERDATE, NVL(PRODUCT.DESCRIP,'neexistuje') vyrobek
     From Item,Ord, Product
     Where Item.Ordid = Ord.Ordid And Item.Prodid = Product.Prodid(+)
           And ORD.Ordid = 610;
Rollback;

tema_09-3b

Ve 9_3a jsme upravili proceduru ADD_ITEM. Predpokladejme, ze na urovniDDL nebyla navázána referenční integrita ITEM[Prodid] ref PRODUCT[PRODID]. Potom vytvorena procedura ad 27_3a nebude reagovat na vlozeni neexistujicihocisla zbozi. Upravte proceduru ADD_ITEM tak, aby programově odhalila pokuso porušení teto vazby.

Nápověda: Před vlastním příkazem Insert do ITEM vyberte z tabulkyPRODUCT řádek se zadaným PRODID. V případě chyby nastane výjimka No_Data_Found.

Formát pro testovací volání:
SQL>Execute jmeno_procedury(parametry)

Create or Replace
 Procedure Add_Item
           ( a_ORDID      ITEM.ORDID%Type,
            a_ITEMID      ITEM.ITEMID%Type,
            a_PRODID      ITEM.PRODID%Type,
            a_ACTUALPRICE ITEM.ACTUALPRICE%Type,
            a_QTY         ITEM.QTY%Type Default 1)
As
v_ITEMTOT   ITEM.ITEMTOT%Type := 0;
e_F_K   Exception;
e_P_K   Exception;
v_Rowid Rowid;
Pragma Exception_init (e_F_K,-2291);
Pragma Exception_init (e_P_K,-1);
Begin
  Select ROWID Into v_Rowid From PRODUCT
         Where PRODID = a_PRODID;
  v_ITEMTOT := a_QTY * NVL(a_ACTUALPRICE,0);
  Insert Into ITEM
         (ORDID,   ITEMID,   PRODID,   ACTUALPRICE,   QTY,   ITEMTOT)
  Values (a_ORDID, a_ITEMID, a_PRODID, a_ACTUALPRICE, a_QTY, v_ITEMTOT);
  Update ORD
         Set TOTAL = TOTAL + v_ITEMTOT
         Where ORDID = a_ORDID;
Exception
  When No_Data_Found Then Raise_Application_Error
                  (-20399, 'Chybn~B cislo zbozĄ');
  When e_F_K Then Raise_Application_Error
                  (-20399, 'Chybn~B cĄslo objedn| vky nebo zbozĄ');
  When e_P_K Then Raise_Application_Error
                  (-20399, 'DuplicitnĄ cĄslo obj. polozky');
End Add_Item;
/


Set autocommit off
Column Ordid format 99999
column Itemid format 999999
column Prodid format 999999
column Actualprice format 9999.99
column Qty format 999
column Itemtot format 9999999
column descrip format A15
column vyrobek format A15


Execute ADD_ITEM(610,4,100860,30,200)
--
Select Item.*, ORD.ORDERDATE, PRODUCT.DESCRIP
       From Item,Ord, Product
       Where Item.Ordid = Ord.Ordid And Item.Prodid = Product.Prodid(+)
             And ORD.Ordid = 610;
-- je1ti jednou poid| me tut~B3 polo3ku
Execute ADD_ITEM(610,4,100860,30,200)
-- poid| me polo3ku k neextujĄcĄ objedn| vce 300
Execute ADD_ITEM(300,4,100860,30,1)
-- poid| me polo3ku k objedn| vce 610 na neexistujĄcĄ zbo3Ą 100865
Execute ADD_ITEM(610,5,100865,30,1)
-- 
Select Item.*, ORD.ORDERDATE, NVL(PRODUCT.DESCRIP,'neexistuje') vyrobek
     From Item,Ord, Product
     Where Item.Ordid = Ord.Ordid And Item.Prodid = Product.Prodid(+)
     And ORD.Ordid = 610;
Rollback;

tema_09-4

Napište další proceduru se jménem UPD_ITEM, která zvýší nebo sníží hodnotupoložky (QTY) u dané objednací položky (ITEM) o zadanou hodnotu.
a) Procedura převezme přes vstupní parametry hodnoty ORDID, ITEMIDa ADDQTY.
b) Zadání záporného 'přírůstku', které povede k záporné hodnotě QTYmusí vyvolat chybu, kterou zachyťte jako výjimku.
c) Aktualizujte také položku ITEM.ITEMTOT a ORD.TOTAL objednávky zadanéhočísla.

Ošetřete opět zadání chybných čísel objednávky, položky a zboží.

Formát pro testovací volání:
SQL>Execute jmeno_procedury(parametry)

Create or Replace
Procedure Upd_Item
           (a_ORDID      ITEM.ORDID%Type,
            a_ITEMID      ITEM.ITEMID%Type,
            a_ADDQTY      Number Default 1)
 As
 v_Nove_QTY       ITEM.QTY%Type;
 v_Nove_ITEMTOT   ITEM.ITEMTOT%Type := 0;
 v_prirustek_ceny Number;
 v_OrdTotal ORD.TOTAL%Type;
 v_Rowid Rowid;
Begin
  Select  QTY + a_ADDQTY,
          a_ADDQTY * NVL(ACTUALPRICE,0),
          ITEMTOT + a_ADDQTY * NVL(ACTUALPRICE,0)
     Into v_Nove_QTY, v_prirustek_ceny, v_Nove_ITEMTOT
     From ITEM
     Where ORDID = a_ORDID And
           ITEMID = a_ITEMID;
  If v_Nove_QTY <= 0 Then
     Raise_Application_Error
           (-20250,'Nelze nastavit QTY na hodnotu '||v_Nove_QTY);
  End If;
  Update ORD
         Set TOTAL = TOTAL + v_prirustek_ceny
         Where ORDID = a_ORDID;
  If SQL%NotFound Then Raise_Application_Error
                  (-20399, 'Chybn~B cĄslo objedn| vky');
  End If;
-- kdyz zadan|  polozka neexistuje, tak to sem nedojde.
  Update ITEM
         Set QTY     = v_Nove_QTY,
             ITEMTOT = v_Nove_ITEMTOT
         Where ORDID  = a_ORDID And ITEMID = a_ITEMID;
Exception
   When No_Data_Found Then Raise_Application_Error
                  (-20399, 'Chybn~B cĄslo objednacĄ polozky');
End Upd_Item;
/

Set autocommit off
Column Ordid format 99999
column Itemid format 999999
column Prodid format 999999
column Actualprice format 9999.99
column Qty format 999
column Itemtot format 9999999
column descrip format A15
column vyrobek format A15
--
Execute Add_ITEM(610,4,100860,30,200)
Select Item.*, ORD.ORDERDATE, PRODUCT.DESCRIPtion
       From Item,Ord, Product
       Where Item.Ordid = Ord.Ordid And Item.Prodid = Product.Prodid(+)
             And ORD.Ordid = 610;
-- ZvěsenĄ mnozstvĄ o pades| t
Execute Upd_ITEM(610,4,50)
-- pokus o snĄzenĄ mnozstvĄ do z| porněch hodnot.
Execute Upd_ITEM(610,4,-260)
--
Select Item.*
       From Item
       Where Ordid = 610;
Rollback;

tema_09-5

Vložte procedury ADD_ITEM a UPD_ITEM do modulu se jménem ITEM_PACK a cvičněje zavolejte.
Formát pro testovací volání:
SQL>Execute Jmeno_modulu.jmeno_procedury(parametry)

Create Or Replace Package Item_Pack
As
Procedure Add_Item
           ( a_ORDID      ITEM.ORDID%Type,
            a_ITEMID      ITEM.ITEMID%Type,
            a_PRODID      ITEM.PRODID%Type,
            a_ACTUALPRICE ITEM.ACTUALPRICE%Type,
            a_QTY         ITEM.QTY%Type Default 1);
Procedure Upd_Item
           (a_ORDID      ITEM.ORDID%Type,
            a_ITEMID      ITEM.ITEMID%Type,
            a_ADDQTY      Number Default 1);
End Item_Pack;
/
Create Or Replace Package Body Item_Pack
As
Procedure Add_Item
           ( a_ORDID      ITEM.ORDID%Type,
            a_ITEMID      ITEM.ITEMID%Type,
            a_PRODID      ITEM.PRODID%Type,
            a_ACTUALPRICE ITEM.ACTUALPRICE%Type,
            a_QTY         ITEM.QTY%Type Default 1)
Is
v_ITEMTOT   ITEM.ITEMTOT%Type := 0;
e_F_K   Exception;
e_P_K   Exception;
v_Rowid Rowid;
Pragma Exception_init (e_F_K,-2291);
Pragma Exception_init (e_P_K,-1);
Begin
  Select ROWID Into v_Rowid From PRODUCT
         Where PRODID = a_PRODID;
  v_ITEMTOT := a_QTY * NVL(a_ACTUALPRICE,0);
  Insert Into ITEM
         (ORDID,   ITEMID,   PRODID,   ACTUALPRICE,   QTY,   ITEMTOT)
  Values (a_ORDID, a_ITEMID, a_PRODID, a_ACTUALPRICE, a_QTY, v_ITEMTOT);
  Update ORD
         Set TOTAL = TOTAL + v_ITEMTOT
         Where ORDID = a_ORDID;
Exception
  When No_Data_Found Then Raise_Application_Error
                  (-20399, 'Chybn~B cĄslo zbozĄ');
  When e_F_K Then Raise_Application_Error
                  (-20399, 'Chybn~B cĄslo objedn| vky nebo zbozĄ');
  When e_P_K Then Raise_Application_Error
                  (-20399, 'DuplicitnĄ cĄslo obj. polozky');
End Add_Item;
Procedure Upd_Item
           (a_ORDID      ITEM.ORDID%Type,
            a_ITEMID      ITEM.ITEMID%Type,
            a_ADDQTY      Number Default 1)
Is
 v_Nove_QTY       ITEM.QTY%Type;
 v_Nove_ITEMTOT   ITEM.ITEMTOT%Type := 0;
 v_prirustek_ceny Number;
 v_OrdTotal ORD.TOTAL%Type;
 v_Rowid Rowid;
Begin
  Select  QTY + a_ADDQTY,
          a_ADDQTY * NVL(ACTUALPRICE,0),
          ITEMTOT + a_ADDQTY * NVL(ACTUALPRICE,0)
     Into v_Nove_QTY, v_prirustek_ceny, v_Nove_ITEMTOT
     From ITEM
     Where ORDID = a_ORDID And
           ITEMID = a_ITEMID;
  If v_Nove_QTY <= 0 Then
     Raise_Application_Error
           (-20250,'Nelze nastavit QTY na hodnotu '||v_Nove_QTY);
  End If;
  Update ORD
         Set TOTAL = TOTAL + v_prirustek_ceny
         Where ORDID = a_ORDID;
  If SQL%NotFound Then Raise_Application_Error
                  (-20399, 'Chybn~B cĄslo objedn| vky');
  End If;
-- kdyz zadan|  polozka neexistuje, tak to sem nedojde.
  Update ITEM
         Set QTY     = v_Nove_QTY,
             ITEMTOT = v_Nove_ITEMTOT
         Where ORDID  = a_ORDID And ITEMID = a_ITEMID;
Exception
   When No_Data_Found Then Raise_Application_Error
                  (-20399, 'Chybn~B cĄslo objednacĄ polozky');
End Upd_Item;
End Item_Pack;
/

Set autocommit off
Column Ordid format 99999
column Itemid format 999999
column Prodid format 999999
column Actualprice format 9999.99
column Qty format 999
column Itemtot format 9999999
column descrip format A15
column vyrobek format A15


Execute Item_Pack.Add_ITEM(610,4,100860,30,200)
Select Item.*, ORD.ORDERDATE, NVL(PRODUCT.DESCRIPTION,'neexistuje') Vyrobek
       From Item,Ord, Product
       Where Item.Ordid = Ord.Ordid And Item.Prodid = Product.Prodid(+)
             And ORD.Ordid = 610;

Execute  Item_Pack.Upd_ITEM(610,4,50)

Execute  Item_Pack.Upd_ITEM(610,4,-260)

Select Item.*
       From Item
       Where Ordid = 610;


Execute  Item_Pack.ADD_ITEM(610,4,100860,30,200)

Execute  Item_Pack.ADD_ITEM(300,4,100860,30,1)

Execute  Item_Pack.ADD_ITEM(610,5,100865,30,1)

Select Item.*, ORD.ORDERDATE, NVL(PRODUCT.DESCRIPTION,'neexistuje') vyrobek
     From Item,Ord, Product
     Where Item.Ordid = Ord.Ordid And Item.Prodid = Product.Prodid(+)
           And ORD.Ordid = 610;
Rollback;

tema_09-6

Pomocí dynamického SQL vytvořte proceduru, která zruší všechny tabulky, pohledy a sekvence ve schématu přihlášeného uživatele. poznámka: pozor, až se vám to povede, spusťte proceduru jen tehdy, když vam nebude vadit, že zmizí všechny objekty vašeho schématu. 

set serveroutput on
column object_name format a40
CREATE OR REPLACE PROCEDURE DROP_ALL_OBJECTS
IS
  v_statement VARCHAR2(60);
  v_cursorid INTEGER;
BEGIN
dbms_output.enable(10000); -- aby nepretekl dbms_output buffer
For v_rec in (Select object_name,object_type
              From user_objects
              Where object_type in ('TABLE','VIEW','SEQUENCE'))
Loop
v_cursorid:=DBMS_SQL.OPEN_CURSOR;
v_statement:='DROP '||v_rec.object_type||' '||v_rec.object_name;
if v_rec.object_type='TABLE' then v_statement := v_statement||' cascade constraints';
dbms_output.put_line(v_statement);
-- DBMS_SQL.PARSE(v_cursorid,v_statement,dbms_sql.native);
-- predchozi radek odkomentuj pouze tehdy, jsi-li si jisty, ze chces vsechno zrusit
-- akce bude nevratna
DBMS_SQL.CLOSE_CURSOR(v_cursorid);
end loop;
end;
/
show errors
--
-- zavolani procedury
--
EXECUTE drop_all_objects
--
SELECT object_name, object_type  from user_objects
/

Téma 10 - trigger (snímek relevantní prezentace z loňska 14  15)

tema_10-1

Byla vydána podniková směrnice, že DML operace nad tabulkami budou povolenypouze od pondělí do pátku v době od 23:45 do 23:55 hod.
a) Vytvořte proceduru jménem SECURE_DML, která vyvolá chybu, je-lispuštěna v době mimo definované období a sdělí tuto zprávu: 'změna datje povolena pouze v úředních hodinách'.
b) Otestujte správnou funkci této procedury s využitím dočasné změny systémového času, nebo změnou podmínky tak, aby momentální čas podmínce nevyhovoval.

Create or Replace Procedure Secure_DML
As
Begin
     If To_Char(sysdate,'hh24:mi') not Between '23:45' and '23:55' Or
        To_Char(sysdate,'d') not Between '2' and '6'
        Then Raise_Application_Error
        (-20101,'ZmŘna dat je povolena pouze v ŁýednĄch hodin| ch');
     End If;
End;

tema_10-2

Vytvořte trigger zakotvený nad tabulkou PRODUCT, který bude volat proceduruSECURE_DML (viz. úloha 28_1). Pokuste se potom vložit s využitím procedury PROD_PACK.ADD_PROD nový řádek do tabulky PRODUCT.

Create or Replace Trigger Secure_Prod
Before Insert Or Update Or Delete On PRODUCT
Begin
Secure_DML;
End Secure_Prod;
/
-- uz jsme v prostredĄ SQL*Plus.
Execute Prod_Pack.Add_Prod(300,'Nově věrobek');

tema_10-3

Upravte podmínku v proceduře SECURE_DML tak, aby jí vyhovoval momentální nastavený systémový čas, např. nechť jsou úřední hodiny od 08:45 do 17:30.Znovu použijte proceduru Prod_Pack.Add_Prod. 

Create or Replace Procedure Secure_DML
As
Begin
     If To_Char(sysdate,'hh24:mi') not Between '08:45' and '17:30' Or
        To_Char(sysdate,'d') not Between '2' and '6'
        Then Raise_Application_Error
        (-20101,'Zmena dat je povolena pouze v ŁrednĄch hodin| ch');
     End If;
End;
/
Set Autocommit off
-- uz jsme v prostredĄ SQL*Plus.
Execute Prod_Pack.Add_Prod(300,'Nově věrobek');
Select * from Product;
Rollback;

tema_10-4

Celková hodnota provize prodejce (zaměstnanců s funkcí SALESMAN) je uloženave sloupci COMM tabulky EMP. Ta se může změnit po přijetí kterékoliv novéobjednávky. Je možné ji změnit i na základě dodatečné úpravy objemu staršíobjednávky. Každému zákazníkovi je přiřazen prodejce, který se o něj 'stará', v tabulce CUSTOMER (sloupec REPID).

a) Vytvořte proceduru, která bude volána triggerem. Procedura upravína základě přijetí nové objednávky nebo úpravy objemu existující objednávky odpovídajícímu prodejci osobní provizi.

Použijte vstupní parametry pomocí nichž volající trigger předá - číslo zákazníka, - starý totální součet objednávky, - nový totální součet objednávky. Pro tento příklad předpokládejme konstantní provizi 5%.

b) Vytvořte trigger nad tabulkou ORD, který bude volat proceduru ada).
Nápověda: Procedura najde v tabulce CUSTOMER pro určeného zákazníka odpovídající číslo zaměstnance (REPID) a tomu upraví jeho záznam v tabulce EMP změnou hodnoty ve sloupci COMM. Pro výběr REPID z tabulky CUSTOMER použijte kurzor. Takto zadaný příkaz bude předem přeložený, takže režieprovádění volajícího triggeru bude nižší. 

Create or Replace Procedure Update_Comm
   (p_custid In CUSTOMER.CUSTID%Type, -- zakaznik, ktery zadal objedn| vku
    p_stary_ORD_total  EMPLOYEES.COMMISSION_PCT%Type, -- u nove objedn| vky bude NULL
    p_novy_ORD_total  EMPLOYEES.COMMISSION_PCT%Type) -- celk. cena zbozi na objedn| vce
As
v_RepId CUSTOMER.REPID%Type;
v_prirustek  EMPLOYEES.COMMISSION_PCT%Type;
Cursor Get_RepId
       Is Select REPID
                 From CUSTOMER
                 Where CUSTID = p_custid;
Begin
  Open Get_RepId;
  Fetch Get_Repid Into V_Repid;
  Close Get_Repid;
  v_prirustek := (NVL(p_novy_ORD_total,0)-NVL(p_stary_ORD_total,0))*0.05;
  Update EMPLOYEES
         Set COMMISSION_PCT = COMMISSION_PCT + v_prirustek
         Where EMPLOYEE_ID = v_RepId;
End Update_Comm;
/

show errors;



Create Or Replace Trigger Update_Emp_Comm
       After Insert Or Update  Or Delete on ORD
       For Each Row
Begin
Update_Comm(:New.CUSTID,
            :Old.TOTAL,
            :New.TOTAL);
End Update_Emp_Comm;
/

tema_10-5

Objednávka č. 610 má v současnosti tři položky:
ordid itemid proid actualprize qty itemtot
610 1 100860 35 1 35
610 2 100870 2.8 3 8.4
Zákazník teď chce redukovat položku č. 2, nechce 3 kusy, nýbrž pouze dva. Dále chce přidat třetí objednací položku na jeden kus tohoto zboží:
Execute Item_Pack.ADD_ITEM(610,4,102130,3.4,1);
Select * from EMPLOYEES Where LAST_NAME ='WARD';
Execute Item_Pack.Upd_ITEM(610,2,-1);

Select Item.*, ORD.ORDERDATE, NVL(PRODUCT.DESCRIPTION,'neexistuje') vyrobek
     From Item,Ord, Product
     Where Item.Ordid = Ord.Ordid And Item.Prodid = Product.Prodid(+)
           And ORD.Ordid = 610;


Select * from EMPLOYEES Where LAST_NAME ='WARD';
Rollback;
Select * from EMPLOYEES Where LAST_NAME ='WARD';

tema_10-6

Původní schéma má tento vztah:
zaměstnanec může být zařazen do jednoho ddělení
oddělení může mít jednoho až n zaměstnanců

Změňte tento vztah takto:
oddělení musí mít alespoň jednoho zaměstnance (DEPT bude mít povinnou účast ve vztahu).
Deklarativně toto IO nelze ošetřit. Zabezpečte kontrolu tohoto IO procedurálně.

--pomocny package
create or replace package emp_dept_pkg
as
flag boolean:=FALSE;
cursor g_cur is
select DEPARTMENT_ID, count(*) pocet_clenu
from employees
group by DEPARTMENT_ID;
type g_t is table of integer
index by binary_integer;
g_pocet_clenu g_t;
g_inicializace g_t;
end emp_dept_pkg;
/

show errors;




Create or Replace Trigger ZAKAZ_INSERTU_DO_DEPT
Before Insert On departments
Begin
If emp_dept_pkg.flag
Then
-- sem to projde, kdyz byl isert vyvolan neprimo z triggeru insert_dept
Null;
Else raise_application_error
(-20001, 'nelze insertovat radky do DEPT, pouzijte insert into v_dept(deptno,dname,loc,prvni_empno)');
End If;
end ZAKAZ_INSERTU_DO_DEPT;
/




alter table employees drop constraint EMP_FOREIGN_KEY;
alter table employees add constraint EMP_FK_deptno
foreign key(DEPARTMENT_ID) references departments
deferrable initially deferred;



create or replace view v_dept as
Select DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID, 1 as prvni_empno
From departments;


create or replace trigger insert_v_dept
instead of insert on v_dept
for each row
declare
v_emp_existuje integer;
v_puv_deptno_1_clena departments.DEPARTMENT_ID%type;
v_poc_cl_puv_odd_1_clena integer;
begin
select count(*) into v_emp_existuje  -- test, zda zadany prvni zamestnanec oddeleni existuje
from employees
where EMPLOYEE_ID=:new.prvni_empno;
if v_emp_existuje = 0
then raise_application_error(-20001,'chybne nebo nezadane cislo prvniho zamestnance oddeleni');
end if;
select DEPARTMENT_ID into v_puv_deptno_1_clena -- puvodni oddeleni prvniho clena noveho oddeleni
from employees
where EMPLOYEE_ID=:new.PRVNI_EMPNO;
if :new.DEPARTMENT_ID != v_puv_deptno_1_clena
then
 select  count(*) into v_poc_cl_puv_odd_1_clena -- pocet clenu puv. oddeleni prvniho clena noveho oddeleni
 from employees
 where DEPARTMENT_ID=v_puv_deptno_1_clena;
 if v_poc_cl_puv_odd_1_clena = 1
 then raise_application_error
      (-20002,
       'nelze presunout do noveho oddeleni posledniho clena puvodniho oddeleni '||v_puv_deptno_1_clena);
 end if;
 -- zalozeni noveho oddeleni
 emp_dept_pkg.flag:=TRUE;
 insert into departments
 values(:new.DEPARTMENT_ID, :new.DEPARTMENT_NAME,'', :new.LOCATION_ID);
 -- presun zadaneho zamestnance do noveho oddeleni
 emp_dept_pkg.flag:=FALSE;
 update employees
 set DEPARTMENT_ID=:new.DEPARTMENT_ID
 where EMPLOYEE_ID=:new.prvni_empno;
else
 -- prvni zamestnanec noveho oddeleni byl v ramci bezici transakce a hned zarazen do noveho oddeleni 
 insert into departments
 values(:new.DEPARTMENT_ID, :new.DEPARTMENT_NAME,'', :new.LOCATION_ID);
end if;
end insert_v_dept;
/
show errors


create or replace trigger zabrana_vyprazdneni_odd_stmt
before delete or update of DEPARTMENT_ID on employees
declare i integer := 1;
begin
for rec in emp_dept_pkg.g_cur loop
emp_dept_pkg.g_pocet_clenu(rec.DEPARTMENT_ID):=rec.pocet_clenu;
end loop;
end zabrana_vyprazdneni_odd_stmt;
/
show errors;



create or replace trigger zabrana_vyprazdneni_odd_row
before delete or update of DEPARTMENT_ID on employees
for each row
declare
begin
if emp_dept_pkg.g_pocet_clenu(:old.DEPARTMENT_ID)=1
then raise_application_error(-20002,'nelze zrusit posledniho zamestnance v oddeleni '||:old.DEPARTMENT_ID);
end if;
emp_dept_pkg.g_pocet_clenu(:old.DEPARTMENT_ID):=emp_dept_pkg.g_pocet_clenu(:old.DEPARTMENT_ID)-1;
end zabrana_vyprazdneni_odd_row;
/




create or replace trigger zabrana_vyp_odd_after_stmt
after delete or update of DEPARTMENT_ID on employees
begin
emp_dept_pkg.g_pocet_clenu:=emp_dept_pkg.g_inicializace;
end zabrana_vyp_odd_after_stmt;
/





Jak se vyhnout tomu, že nelze z triggeru nad tabulkou A zadavat DML příkaz vůči A?

set echo on
set serveroutput on
-- jak se vyhnout problému s tím, že v řádkovém triggeru nad tabulkou A nelze zadat DML příkaz týkající se A.
-- Za každého nového přímo podřízeného má pracovník přidáno o 100 peněz, jeho nadřízení postupně o 50,25,12.5, ...
-- Za každého nově odebraného přímo podřízeného má pracovník ubráno o 100 peněz, jeho nadřízení postupně o 50, 25, 12.5, ...

create or replace view v_employees
as select * from employees;
View created.

desc v_employees
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME   VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER   VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY   NUMBER(8,2)
COMMISSION_PCT   NUMBER(2,2)
MANAGER_ID   NUMBER(6)
DEPARTMENT_ID   NUMBER(4)

create or replace package semafor as
 procedure rozsvit_cervenou;
 procedure rozsvit_zelenou;
 function je_cervena return boolean;
 function je_zelena return boolean;
end semafor;
/
show errors
create or replace package body semafor as
smfr boolean :=false;
procedure rozsvit_cervenou is
begin
  smfr:=false;
end rozsvit_cervenou;
procedure rozsvit_zelenou is
begin
  smfr:=true;
end rozsvit_zelenou;
function je_zelena return boolean
is
begin
 return smfr;
end je_zelena;
function je_cervena return boolean
is
begin
  return not smfr;
end je_cervena;
end semafor;
/
create or replace trigger zakaz_dml_emp
before insert or update or delete on employees
begin
if semafor.je_cervena then raise_application_error(-20001, 'v tabulce EMPLOYEES nelze menit data primo, pouzijte pohled v_employees');
end if;
end zakaz_dml_emp;
/
show errors trigger zakaz_dml_emp
-- nasledujici procedura je rekurzivni
-- prvni parametr rika id nejblizsiho sefa
-- druhy parametr rika o kolik penez se ma zvysit plat prvnimu sefovi
-- vyssim sefum se pridava dvakrat, ctyrikrat, adt. mene
create or replace procedure uprav_plat_sefum(p_nejblizsi integer, p_o_kolik number)
as
v_pom integer;
begin
semafor.rozsvit_zelenou;
update employees set salary = salary+p_o_kolik
   where employee_id=p_nejblizsi
returning manager_id into v_pom;
semafor.rozsvit_cervenou;
dbms_output.put_line('prac. '||p_nejblizsi||' upraven plat o '||p_o_kolik);
if v_pom is not NULL then uprav_plat_sefum(v_pom,p_o_kolik/2); end if;
end;
/
Procedure created.
show errors
No errors.
create or replace trigger pokus
  instead of insert or update on v_employees for each row
declare
  pom integer;
begin
  if inserting then
   uprav_plat_sefum(:new.manager_id, 100);
   semafor.rozsvit_zelenou;
   insert into employees
   values(:new.employee_id,:new.FIRST_NAME,
             :new.LAST_NAME,:new.EMAIL,
             :new.PHONE_NUMBER,:new.HIRE_DATE,
             :new.JOB_ID,:new.SALARY,
             :new.COMMISSION_PCT,
             :new.MANAGER_ID,:new.DEPARTMENT_ID);
   semafor.rozsvit_cervenou;
  else if deleting then uprav_plat_sefum(:new.manager_id, -100);
                             semafor.rozsvit_zelenou;
                             delete employees where employee_id=:old.employee_id;
                             semafor.rozsvit_cervenou;
        else if  (:old.manager_id) != (:new.manager_id) then 
                 uprav_plat_sefum(:new.manager_id, 100);
                 uprav_plat_sefum(:old.manager_id, -100);
                 semafor.rozsvit_zelenou;
                 update employees set FIRST_NAME=:new.FIRST_NAME,
                                                 LAST_NAME=:new.LAST_NAME,
                                                 EMAIL=:new.EMAIL,
                                                 PHONE_NUMBER=:new.PHONE_NUMBER,
                                                 HIRE_DATE=:new.HIRE_DATE,
                                                 JOB_ID=:new.JOB_ID,
                                                 SALARY =:new.SALARY,
                                                 COMMISSION_PCT=:new.COMMISSION_PCT,
                                                 MANAGER_ID=:new.MANAGER_ID,
                                                 DEPARTMENT_ID=:new.DEPARTMENT_ID
                 where employee_id=:old.employee_id;
                 semafor.rozsvit_cervenou;
              end if;
    end if;
end if;
end POKUS;
/
show errors trigger pokus
No errors.
select employee_id,salary
from employees
start with employee_id=205
connect by employee_id=prior manager_id;
EMPLOYEE_ID SALARY
205 12000
101 17000
100 24000

update v_employees
set manager_id = 205
where employee_id=105;
prac. 205 upraven plat o 100
prac. 101 upraven plat o 50
prac. 100 upraven plat o 25
prac. 103 upraven plat o -100
prac. 102 upraven plat o -50
prac. 100 upraven plat o -25
1 row updated.

select employee_id,salary
from employees
start with employee_id=205
connect by employee_id=prior manager_id;

EMPLOYEE_ID SALARY
205 12100
101 17050
100 24000

insert into employees
select * from employees
where rownum=1; 
insert into employees
         *
ERROR at line 1:
ORA-20001: v tabulce EMPLOYEES nelze menit data primo, pouzijte pohled v_employees
ORA-06512: at "HALASKA.ZAKAZ_DML_EMP", line 2
ORA-04088: error during execution of trigger 'HALASKA.ZAKAZ_DML_EMP'

rollback;
Rollback complete.

Ověření, zda umíme hierarchický update

V řešení testu se objevily pokusy updatovat CTE tabulku:
with
cte as (select * from employees)
Update cte set salary=11
where employee_id =100
/
Bylo by to hezké, ale příkaz With umožňuje pouze Select. Reakce našeho SQL stroje je tato:
 
Update cte set salary = 11
*
ERROR at line 3:
ORA-00928: missing SELECT keyword
Ani pokus o obejití učiněním CTE tabulky in-line pohledem, nevede k cíli:
--
Update
(With cte as (select * from employees)
  Select * from cte)
set salary=11
where employee_id =100
/ 
(With cte as (select * from employees)
*
ERROR at line 2:
ORA-01732: data manipulation operation not legal on this view

Cesta vede tudy:

Ilustrativní, proto jednoduchý, příklad:

-- přidej 5 korun všem v kaskádě šéfů pracovnika 105

Update employees set salary=salary+5
where employee_id in
( with
  cte as (select employee_id from employees
            start with employee_id=105
            connect by prior manager_id= employee_id)
  Select * from cte
  where employee_id<>105)
/

-- v kurzoru je možné použít select s CTE tabulkou:
declare
cursor cte is
with
cte1 as (select * from employees
                 start with employee_id=105
                 connect by prior manager_id= employee_id)
Select * from cte1;
begin
for x in cte loop
if x.employee_id <>105
then Update employees set salary=salary+5
          where employee_id =x.employee_id;
end if;
end loop;
end;
/
rollback;
PL/SQL procedure successfully completed.

-- nebo efektivněji:
declare
cursor cte is
with
cte1 as (select employee_id from employees
start with employee_id=105
connect by prior manager_id= employee_id)
Select employee_id from cte1;
type t_sefove is table of employees.employee_id%type
index by pls_integer;
v_sefove t_sefove;
begin
open cte;
fetch cte bulk collect into v_sefove;
close cte;
v_sefove.delete(v_sefove.first);  -- jiným způsobem se zbavím pracovníka 105, vím, že je první
Forall i in v_sefove.first..v_sefove.last
Update employees set salary=salary+5
where employee_id =v_sefove(i);
end;
/
PL/SQL procedure successfully completed.
rollback;
Rollback complete.