a że nie ma kategorii ZSBD |  |
| patman | 19.01.2012 22:16:45 |
 Grupa: Użytkownik Posty: 20 #845124 Od: 2012-1-3 | to wrzucam to tutaj. Zapytania SQL. Nie wszystkie działają, niektóre to moje próbki, ale powinno przydać się: select nazwisko, imie, rokurodz from osoby where nazwisko in (select nazwisko from ttt); select nazwisko, imie, rokurodz from osoby where nazwisko in (select nazwisko from ttt) and imie in (select imie from ttt); select nazwisko, imie, rokurodz from osoby where nazwisko||imie in (select nazwisko||imie from ttt); --zabezpiecza przed wybranie różnych rekordów imienia i nazwiska select nazwisko, imie, rokurodz from osoby where nazwisko||imie||rokurodz in ( select nazwisko||imie||rokurodz from ttt); select nazwisko, imie, rokurodz from osoby where nazwisko||imie||to_char(rokurodz) in ( select nazwisko||imie||to_char(rokurodz) from ttt); -- to_char konwertuje wartości liczbowe na string(stare wersje oracle'a); select nazwisko, imie, rokurodz from osoby where (nazwisko, imie, rokurodz) in ( select nazwisko, imie, rokurodz from ttt); --tablica pól w nawiasach select osoby.nazwisko, osoby.imie, osoby.rokurodz, ttt.nazwisko, ttt.imie, ttt.rokurodz from osoby join ttt on osoby.nazwisko = ttt.nazwisko and osoby.imie = ttt.imie and osoby.rokurodz = ttt.rokurodz; -- na złączeniach select nazwisko, osoby.imie, osoby.rokurodz from osoby join ttt using (nazwisko); --klauzula using nie pozwala na użycie nazwy kwalifikowanej, która użyta jest w nawiasie select nazwisko, imie, rokurodz from osoby join ttt using (nazwisko, imie, rokurodz); --żadne nazwy kwalifikaowane select nazwisko, imie, rokurodz from osoby natural join ttt -- złącza wszystkimi polami select osoby.nazwisko, osoby.imie, osoby.rokurodz, ttt.nazwisko, ttt.imie, ttt.rokurodz from osoby left join ttt on osoby.nazwisko = ttt.nazwisko and osoby.imie = ttt.imie and osoby.rokurodz = ttt.rokurodz where ttt.rokurodz is null select nazwisko, imie, rokurodz from osoby left join ttt using (nazwisko, imie, rokurodz) where wzrost is null select nazwisko, imie, rokurodz from osoby right join ttt using (nazwisko, imie, ) where wzrost is null select nazwisko, imie, wzrost, from osoby where wzrost > (select avg(wzrost) from osoby) --zapytania skalarne mogą być użyte w zapytaniach filtrujących select nazwisko, imie, wzrost, (select avg(wzrost) from osoby) as srrokurodz from osoby where wzrost > (select avg(wzrost) from osoby)--j.w. wraz ze średnią select nazwisko, imie, wzrost, (select max(wzrost) from osoby) from osoby where wzrost = (select max(wzrost) from osoby) group by nazwisko, imie, wzrost--zapytania skalarne mogą być użyte w zapytaniach filtrujących select nazwisko, imie, avg(brutto) as sredniaplaca from osoby join zarobki using(idosoby) group by nazwisko,imie select nazwisko, imie, avg(brutto) as sredniaplaca from osoby join zarobki using(idosoby) group by nazwisko,imie having avg(brutto) > (select avg(brutto) from zarobki) select nazwa, count(idosoby) as ile from dzialy join osoby using (iddzialu) group by nazwa; select nazwa, count(idosoby) as ile from dzialy join osoby using (iddzialu) group by nazwa having count (idosoby) = (select max(ile) from (select iddzialu, count(idosoby) as ile from osoby group by iddzialu)) select nazwisko, sum(brutto) as rp from osoby join zarobki using (idosoby) group by nazwisko, idosoby having sum(brutto)=(select max(razem)from (select idosoby, sum(brutto) as razem from zarobki group by idosoby)xxx) --najwyższa suma wypłat pracownika select nazwa, sum(brutto) as rp from dzialy join osoby using (iddzialu) join zarobki using (idosoby) group by nazwa having sum(brutto)=(select max(razem) from (select iddzialu, sum(brutto) as razem from osoby join zarobki using(idosoby) group by iddzialu)xxx) --największa suma zarobków w dziale select nazwa, avg(razem) as srsuma from (select nazwa, nazwisko, sum(brutto) as razem from dzialy join osoby using (iddzialu) join zarobki using (idosoby) group by nazwa, nazwisko, idosoby)xxx group by nazwa having select iddzialu, sum(brutto) as razem from osoby join zarobki using (idosoby) --dzial, w którym średnia suma wypat jest największa select nazwa, avg(razem) as srsuma from (select nazwa, sum(brutto) as razem from dzialy join osoby using (iddzialu) join zarobki using (idosoby) group by nazwa, nazwisko, idosoby)xxx group by nazwa having avg(razem) = (select max(sr) from (select iddzialu, avg(razem) as sr from (select iddzialu, sum(brutto) as razem from osoby join zarobki using (idosoby) group by iddzialu, idosoby)xxx group by iddzialu)yyy) select nazwa, nazwisko, wzrost, sd from dzialy join osoby using (iddzialu) join (select iddzialu, avg(wzrost) as sd from osoby group by iddzialu) using (iddzialu) where wzrost > sd select nazwa, nazwisko, avg(brutto) as sp from dzialy join osoby using (iddzialu) join zarobki using (idosoby) join (select iddzialu, avg(brutto) as sd from osoby join zarobki using (idosoby) group by iddzialu)xxx using(iddzialu) group by nazwa, nazwisko select nazwa, nazwisko, avg(brutto) as sp, sd from dzialy join osoby using (iddzialu) join zarobki using (idosoby) join (select iddzialu, avg(brutto) as sd from osoby join zarobki using (idosoby) group by iddzialu)xxx using(iddzialu) group by nazwa, nazwisko, sd having avg(brutto) > sd select nazwa, nazwisko, sum(brutto) as sp, sd from dzialy join osoby using (iddzialu) join zarobki using (idosoby) join (select iddzialu, avg(ss) as sd from (select iddzialu, sum(brutto) as ss from osoby join zarobki using (idosoby) group by iddzialu, idosoby)xxx group by iddzialu)yyy using(iddzialu) group by nazwa, nazwisko, sd having sum(brutto) > sd select nazwa, nazwisko, sum(brutto) as sp, sd from dzialy join osoby using (iddzialu) join zarobki using (idosoby) join (select iddzialu, sum(brutto) as sd from osoby join zarobki using (idosoby) group by iddzialu)xxx using(iddzialu) group by nazwa, nazwisko, sd select nazwa, nazwisko, sum(brutto) as sp from dzialy join osoby using (iddzialu) join zarobki using (idosoby) group by rollup (nazwa, nazwisko) --podsumowanie select nazwa, nazwisko, sum(brutto) as sp from dzialy join osoby using (iddzialu) join zarobki using (idosoby) group by cube (nazwa, nazwisko) select nazwa, nazwisko, sum(brutto) as sp from dzialy join osoby using (iddzialu) join zarobki using (idosoby) group by grouping sets (nazwa, nazwisko) select nazwa, nazwisko, brutto, sum(brutto) as sp, avg(sum(brutto)) over() as total, avg(sum(brutto)) over(partition by nazwa) as sd, avg(sum(brutto)) over(partition by nazwisko) as sp, avg(sum(brutto)) over(partition by nazwisko) / avg(sum(brutto)) over(partition by nazwa) as wsp, ratio_to_report(sum(brutto)) over (partition by nazwisko) as wsp1 from dzialy join osoby using(iddzialu) join zarobki using(idosoby) group by nazwa, nazwisko --nie dziala select nazwa, nazwisko, brutto, sum(brutto) over (order by nazwisko rows between unbounded preceding and current row) as xxx, --suma bieżąca sum(brutto) over (order by nazwisko rows between current row and unbounded following) as yyy from dzialy join osoby using(iddzialu) join zarobki using(idosoby) select nazwa, nazwisko, brutto, sum(brutto) over (partition by nazwa order by nazwisko rows between unbounded preceding and current row) as xxx, --suma bieżąca sum(brutto) over (partition by nazwa order by nazwisko rows between current row and unbounded following) as yyy from dzialy join osoby using(iddzialu) join zarobki using(idosoby) select nazwa, nazwisko, brutto, rank() over(order by brutto desc nulls first) as xx --nulls first last ustala pozycję wyświetlania wartości null from dzialy join osoby using(iddzialu) full join zarobki using(idosoby) select nazwa, nazwisko, brutto, rank() over(order by brutto desc) as xx, rank() over(partition by nazwisko order by brutto desc) as yy, rank() over(partition by nazwa order by brutto desc) as zz from dzialy join osoby using(iddzialu) join zarobki using(idosoby) select nazwa, nazwisko, brutto, dense_rank() over(order by brutto desc) as xx, dense_rank() over(partition by nazwisko order by brutto desc) as yy, dense_rank() over(partition by nazwa order by brutto desc) as zz from dzialy join osoby using(iddzialu) join zarobki using(idosoby) select nazwa, nazwisko, brutto, row_number() over(order by brutto desc) as xx, row_number() over(partition by nazwisko order by brutto desc) as yy, row_number() over(partition by nazwa order by brutto desc) as zz from dzialy join osoby using(iddzialu) join zarobki using(idosoby) select nazwa, nazwisko, brutto, row_number() over(order by brutto desc) as xx, row_number() over(partition by nazwisko order by brutto desc) as yy, row_number() over(partition by nazwa order by brutto desc) as zz from dzialy join osoby using(iddzialu) join zarobki using(idosoby) select nazwa, nazwisko, brutto, percent_rank() over(order by brutto desc) as xx, percent_rank() over(partition by nazwisko order by brutto desc) as yy, percent_rank() over(partition by nazwa order by brutto desc) as zz from dzialy join osoby using(iddzialu) join zarobki using(idosoby) select nazwa, nazwisko, brutto, ntile(4) over(order by brutto desc) as xx, ntile(4) over(partition by nazwisko order by brutto desc) as yy, ntile(4) over(partition by nazwa order by brutto desc) as zz from dzialy join osoby using(iddzialu) join zarobki using(idosoby) --ntile - funkcja rankingowa z glebokoscia kodowania select nazwa, nazwisko, brutto, rokurodz, --stats_mode (brutto) over (order by brutto) as sm--, --median(brutto) over (partition by nazwisko order by brutto desc) as me--, corr(rokurodz, brutto) over (order by brutto desc) as co from dzialy join osoby using(iddzialu) join zarobki using(idosoby) select nazwa, nazwisko, stats_mode (brutto), median(brutto) from dzialy join osoby using(iddzialu) join zarobki using(idosoby) group by nazwa, nazwisko -- z innej beczki select idosoby, nazwisko, idszefa from osoby start with idszefa is null connect by prior idosoby=idszefa select idosoby, nazwisko, idszefa from osoby start with idszefa is null connect by prior idszefa=idosoby select level, idosoby, --rpad('|', 2 * level, '_') || '|' || substr('__', 1, 2 * level) || nazwisko, idszefa from osoby start with idszefa is null connect by prior idosoby=idszefa order by level select level, sum(brutto) as razem from osoby join zarobki using (idosoby) start with idszefa is null connect by prior idosoby=idszefa group by level select poziom, osoby.idosoby, pracownik, osoby.idszefa, osoby.nazwisko as szef from osoby right join (select level as poziom, nazwisko as pracownik, idszefa from osoby start with idszefa is null connect by prior idosoby = idszefa)xxx on osoby.idosoby=xxx.idszefa select level, idosoby, nazwisko, idszefa, prior nazwisko as szef from osoby start with idszefa is null connect by prior idosoby=idszefa select rpad('|', 2*level,'_')||nazwisko as szef, (select count(o2.idosoby) from osoby o2 start with o2.idszefa = o1.idosoby connect by prior o2.idosoby=o2.idszefa) as ile from osoby o1 start with o1.idszefa is null connect by prior o1.idosoby=o1.idszefa select rpad('|', 2*level,'_')||nazwisko as szef, (select count(o2.idosoby) from osoby o2 start with o2.idosoby = o1.idosoby --ol.idszefa -> idosoby - wlicza tez szefa connect by prior o2.idosoby=o2.idszefa) as ile from osoby o1 start with o1.idszefa is null connect by prior o1.idosoby=o1.idszefa select rpad('|', 2*level,'_')||nazwisko as szef, (select sum(brutto) as razem from osoby o2 join zarobki on o2.idosoby = zarobki.idosoby start with o2.idszefa = o1.idosoby connect by prior o2.idosoby=o2.idszefa) as ile from osoby o1 start with o1.idszefa is null connect by prior o1.idosoby=o1.idszefa drop table test create table test (nr1 int primary key, nr2 int) create table test (nr1 int, nr2 int, constraint pk primary key(nr1,nr2) ) create table test (nr1 int, nr2 int, constraint pk unique(nr1,nr2) ) create table test (nr1 int not null, nr2 int not null, constraint pk unique(nr1,nr2)) create table test (nr1 int primary key, nr2 int not null unique) --funkcjonalnie dwa klucze glowne create table test (nr1 int, nr2 int not null, constraint pk primary key(nr1), constraint un unique(nr2)) --dowolna liczba constraint create table test (nr1 int not null, nr2 int not null, constraint pk unique(nr1), constraint un unique(nr2)) --dowolna liczba constraint drop table test insert into test values(1,1); insert into test values(1,2); insert into test values(2,1); insert into test values(2,2); insert into test values(1,2); insert into test(nr2) values(3); insert into test(nr2) values(4); insert into test(nr2) values(5); select * from test create table zlec( idzlec int primary key, idosoba int, opis varchar2(22) default 'Brak' not null, v_m int, v_mm int) create table zlec( idzlec int primary key, idosoba int, opis varchar2(22) default 'Brak' not null, v_m int check(v_m > 10), v_mm int check(v_mm < 100), constraint spr check(v_m < v_mm) --ograniczenie pierwsze mniejsze od drugiego ) create table zlec( idzlec int primary key, idosoba int, constraint fk1 foreign key(idosoby) references osoby(idosoby), --jakis blad opis varchar2(22) default 'Brak' not null, v_m int check(v_m > 10), v_mm int check(v_mm < 100), constraint spr check(v_m < v_mm)) drop table zlec insert into zlec values (1,1,'Zlec 1', 20, 80); insert into zlec values (2,2,'Zlec 2', 30, 70); insert into zlec values (3,99,'Zlec 3', 40, 60); insert into zlec (idzlec, v_m, v_mm) values (4, 20, 80); insert into zlec (idzlec, v_m, v_mm) values (5, 5, 80); insert into zlec (idzlec, v_m, v_mm) values (6, 20, 200); insert into zlec (idzlec, v_m, v_mm) values (20, 80, 20); select * from zlec; drop table zlec drop sequence licznik create sequence licznik start with 5 increment by 3 maxvalue 100 minvalue -1 cycle cache 2 noorder --order nie ma sensu create table zlec (idzlec int primary key, idosoba int, opis varchar2(22) default 'Brak' not null, v_m int, v_mm int) insert into zlec values (licznik.nextval,1,'Zlec 1', 20, 80); insert into zlec values (licznik.nextval,2,'Zlec 2', 30, 70); insert into zlec values (licznik.nextval,99,'Zlec 3', 40, 60); insert into zlec (idzlec, v_m, v_mm) values (licznik.nextval, 20, 80); insert into zlec (idzlec, v_m, v_mm) values (licznik.nextval, 5, 80); insert into zlec (idzlec, v_m, v_mm) values (licznik.nextval, 20, 200); insert into zlec (idzlec, v_m, v_mm) values (licznik.nextval, 80, 20); select * from zlec; select licznik.currval from sys.dual select * from sys.dual drop table zlec create table zlec (idzlec int primary key, idosoba int, opis varchar2(22) default 'Brak' not null, v_m int, v_mm int, datazlec date default sysdate ) insert into zlec (idzlec, idosoba, opis, v_m, v_mm) values (licznik.nextval,1,'Zlec 1', 20, 80); --insert into zlec values (licznik.nextval,2,'Zlec 2', 30, 70); --insert into zlec values (licznik.nextval,99,'Zlec 3', 40, 60); insert into zlec (idzlec, v_m, v_mm) values (licznik.nextval, 20, 80); insert into zlec (idzlec, v_m, v_mm) values (licznik.nextval, 5, 80); insert into zlec (idzlec, v_m, v_mm) values (licznik.nextval, 20, 200); insert into zlec (idzlec, v_m, v_mm) values (licznik.nextval, 80, 20); select * from zlec; create table alfa( idalfa int primary key, beta int, gamma int, constraint spr1 check (beta > idalfa), constraint spr2 check (gamma > beta)) drop table alfa create table alfa( idalfa int primary key, beta int, gamma int, constraint spr1 check (beta > idalfa and gamma > beta)) insert into alfa (idalfa, beta, gamma) values (1, 2, 3) ; select * from alfa drop table zebra create table zebra( idnova int primary key, omega varchar2(10), delta varchar2(10), constraint spr2 check(length(omega) = length(delta) and (lower(substr(omega, 1, 1)) = lower(substr(delta, 1, 1)) ))) insert into zebra (idnova, omega, delta) values (1, 'Ala', 'aza'); insert into zebra (idnova, omega, delta) values (1, 'Ala', 'Eza');--violate drop table novabeta create table novabeta( nr1 int, nr2 int, nr3 int, constraint spr4 check(((nr2 is not null) and (nr3 is null)) or (nr2 is null) and (nr3 is not null))) modyfikacja kolumn alter table dzialy add kod number(3) update dzialy set kod=6 alter table dzialy modify kod varchar2(3) --niedozwolone alter table dzialy modify kod number(5)--mozna tylko rozszerzac, nie mozna zmniejszac zakresu wartosci alter table dzialy drop column kod alter table dzialy add constraint spr11 unique (kod) --DUPLIKACJA blokuje nalozenie ograniczenia alter table dzialy drop constraint spr11 alter table dzialy drop column kod cascade constraints --kasuje kolumne z ograniczeniami alter table dzialy add kod1 varchar2(3) update dzialy set kod1=kod + 1 select * from dzialy ---------------------------------WIDOKI create or replace view dane as select idosoby, nazwisko, imie, rokurodz from osoby insert into dane values(44, 'Kowalski', 'Marian', 1980); insert into dane values(43, 'Nowak', 'Benek', 1970); update dane set rokurodz = 1960 where idosoby = 40; select * from dane; select * from osoby; create or replace view dane as select idosoby, nazwisko, imie, rokurodz from osoby where rokurodz > 1970 --warunek tylko przy wyswietlaniu perspektywy create or replace view dane as select idosoby, nazwisko, imie, rokurodz from osoby where rokurodz > 1970 with check option --wymusza sprawdzanie warunku przy modyfikacji i dopisywaniu rekordu with read only insert into dane values(45, 'Kowalski', 'Marian', 1980); insert into dane values(46, 'Nowak', 'Benek', 1970); update dane set rokurodz = 1960 where idosoby = 45; create or replace view testv as select dzialy.iddzialu, nazwa, idosoby, nazwisko from dzialy full join osoby on dzialy.iddzialu = osoby.iddzialu select * from testv --nie mozna dopisywac badz modyfikowac perspektyw zlozonych create or replace view testv as select dzialy.iddzialu as id1, osoby.iddzialu as id2, nazwa, nazwisko --as konieczne do rozroznienia kolumn from dzialy full join osoby on dzialy.iddzialu = osoby.iddzialu --perspektywa systemowa: select * from user_tables; select * from all_tables; --wszystkie tabele widoczne dla uzytkownika select * from dba_tables; select * from dictionary select * from dict_columns select * from user_tab_columns select nazwisko, subr, linag from (select idosoby, nazwisko, sum(brutto) as subr from osoby join zarobki using (idosoby) group by nazwisko, idosoby) join (select idosoby, count(idnagrody) as linag from osoby join nagrody using(idosoby) group by idosoby) using (idosoby) create or replace procedure up (fun varchar2) as zap varchar2(111); begin zap:='update osoby set nazwisko='||fun||'(nazwisko)'; execute immediate zap; end; / begin up('lower'); end; / select * from osoby; create or replace procedure up (fun varchar2, pole varchar2) as zap varchar2(111); begin zap:='update osoby set '||pole||'='||fun||'('||pole||')'; dbms_output.put_line(zap); execute immediate zap; end; / begin up('lower', 'imie'); end; / select * from osoby; create or replace procedure up (fun varchar2, pole varchar2, tabela varchar2) as zap varchar2(111); begin zap:='update '||tabela||' set '||pole||'='||fun||'('||pole||')'; execute immediate zap; end; / begin up('lower', 'imie', 'osoby'); end; / select * from osoby; create or replace procedure up (fun varchar2 default 'upper', pole varchar2, tabela varchar2 default 'Osoby') as zap varchar2(111); begin zap:='update '||tabela||' set '||pole||'='||fun||'('||pole||')'; execute immediate zap; end; / Begin up(pole=>'imie'); --wywolanie z pojedynczym, niedomyslnym parametrem end; / select * from osoby; create or replace procedure up (fun varchar2 default 'upper', pole varchar2 default 'imie', tabela varchar2 default 'Osoby') as zap varchar2(111); begin zap:='update '||tabela||' set '||pole||'='||fun||'('||pole||')'; execute immediate zap; end; / Begin up(); end; / select * from osoby; SET SERVEROUTPUT ON; / create or replace procedure up (fun varchar2 default 'upper', pole varchar2 default 'imie', tabela varchar2 default 'Osoby') as zap varchar2(111); begin ZAP:='update '||TABELA||' set '||POLE||'='||FUN||'('||POLE||')'; DBMS_OUTPUT.PUT_LINE(ZAP); Execute Immediate Zap; EXCEPTION WHEN OTHERS THEN NULL; --DBMS_OUTPUT.PUT_LINE('Blad'); end; / Begin up('blabla', 'imie', 'osoby'); end; / select * from osoby; SET SERVEROUTPUT ON; / Create Or Replace Procedure Licz (Ile out Int, Mini Real) As Begin Select Count (Idosoby) Into Ile from osoby where wzrost > mini; End; / Show Errors; / Declare Ile Int; Begin Licz(Ile, 1.8); Dbms_Output.Put_Line(Ile); End; SET SERVEROUTPUT ON; / Create Or Replace Procedure Licz (Ile out Int, Mini Real default 0) As Begin Select Count (Idosoby) Into Ile from osoby where wzrost > mini; End; / Show Errors; / Declare Ile Int; Begin Licz(Ile, 1.8); Dbms_Output.Put_Line(ile); End; SET SERVEROUTPUT ON; ---bledy / Create Or Replace Procedure Licz (Mini Real default 0, Ile out Int) As Begin Select Count (Idosoby) Into Ile from osoby where wzrost > mini; End; / Show Errors; / Declare Ile1 Int; Begin Licz(ile=>ile1); Dbms_Output.Put_Line(Ile); End; SET SERVEROUTPUT ON; / Create Or Replace Procedure Licz (Ile out Int, Mini Real default 0, sr out real) As Begin Select Count (Idosoby), avg(wzrost) into ile, sr from osoby where wzrost > mini; End; / Show Errors; / SET SERVEROUTPUT ON; / Declare sr real; Ile Int; Begin Licz(Ile, 1.8, sr); Dbms_Output.Put_Line(Ile); Dbms_Output.Put_Line(sr); End; SET SERVEROUTPUT ON; / Create Or Replace Procedure Licz (Ile out Int, sr out real, numer int) As Begin --liczba osob i srednia ich wzrostu w dziale, ktorego numer jest dany parametrem Select Count (Idosoby), Avg(Wzrost) Into Ile, Sr from osoby where iddzialu=numer; End; / Show Errors; / SET SERVEROUTPUT ON; / Declare sr real; Ile Int; Begin Licz(ile, sr, 2); Dbms_Output.Put_Line(Ile); Dbms_Output.Put_Line(Sr); End; Create Or Replace Procedure WDziale (Ile in out Int, sr out real) --zastapienie dwoch zmienych we i wy, jedna zmienna we-wy As Begin --liczba osob i srednia ich wzrostu w dziale, ktorego numer jest dany parametrem Select Count (Idosoby), Avg(Wzrost) Into Ile, Sr from osoby where iddzialu=Ile; End; / Show Errors; / SET SERVEROUTPUT ON; / Declare sr real; Ile Int; Begin ile := 2; WDziale(ile, sr); Dbms_Output.Put_Line(Ile); Dbms_Output.Put_Line(Sr); End; Set Serveroutput On; Create Or Replace Procedure Wspolcz(Dzial Int, Wsp Out Real) As Mini Real; Maksi Real; Begin Select Min(Wzrost), Max(Wzrost) Into Mini, Maksi From Osoby Where Iddzialu = Dzial; Wsp := Mini /(Maksi - Mini); Exception --tu juz nie dziala When Zero_Divide Then Dbms_Output.Put_Line(Sqlcode); Dbms_Output.Put_Line(Sqlcode ||' z others'); End; / Show Errors; / SET SERVEROUTPUT ON; / Declare wsp real; Begin Wspolcz(6, Wsp); Dbms_Output.Put_Line(wsp); End; Set Serveroutput On; Create Or Replace Procedure Wspolcz(Dzial Int, Wsp Out Real) As Blad Exception; pragma exception_init (blad, -2345); --pragma to bledy systemowe Mini Real; Maksi Real; czy int; Begin Select Count(Iddzialu) Into czy From Dzaly Where Iddzialu=Dziel; If (Czy=1) Select Min(Wzrost), Max(Wzrost) Into Mini, Maksi From Osoby Where Iddzialu = Dzial; Wsp := Mini /(Maksi - Mini); Exception When Blad Then dbms_output.put_line('Blad uzytkownika'); When Zero_Divide Then Dbms_Output.Put_Line(Sqlcode); Dbms_Output.Put_Line(Sqlcode ||' z others'); Else Raise Blad; end if; End; / Show Errors; / SET SERVEROUTPUT ON; / Declare wsp real; Begin Wspolcz(6, Wsp); Dbms_Output.Put_Line(wsp); End; --funkcje 18.12.2011 create or replace function Liczf (mini real default 0, ilep out int) return int --uzycie zmiennej out uniemozliwia stosowanie funkcji w zapytaniu as ile int; begin select count(idosoby) into ile from osoby where wzrost > mini; ilep := ile; return ile; exception when others then raise; end; / SET SERVEROUTPUT ON; / declare ile int; ilep int; begin ile:=Liczf(1.8, ilep); --mozna uzyc wartosci domyslnej dla ile przez pominiecie jej: ile:=Liczf(ilep=>ilep); DBMS_OUTPUT.PUT_LINE(ile); DBMS_OUTPUT.PUT_LINE(ilep); end; / create or replace function Liczf (mini real default 0) return int as ile int; begin select count(idosoby) into ile from osoby where wzrost > mini; return ile; exception when others then raise; end; select Nazwisko, Wzrost, Liczf(Wzrost) from osoby; --przerobka poprzedniej procedury na funkcje Set Serveroutput On; Create Or Replace function WspolczF(Dzial Int) return real As Mini Real; Maksi Real; Begin Select Min(Wzrost), Max(Wzrost) Into Mini, Maksi From Osoby Where Iddzialu = Dzial; return (Mini /(Maksi - Mini)); End; / Show Errors; / SET SERVEROUTPUT ON; / declare wsp real; Begin wsp := WspolczF(4); Dbms_Output.Put_Line(wsp); End; --cos nowego: pakiet create or replace package Pakiet as globalna int; function Liczf(mini real, ile out int) return int; function Liczf(ile out int) return int; procedure Zmien(kod varchar2); end; / create or replace package body Pakiet as globalna_wew int; --wewnetrzna globalna function Liczf(mini real, ile out int) return int as begin select count(idosoby) into ile from osoby where wzrost > mini; return 0; exception when others then return 1; end; function Liczf(ile out int) return int as begin select count(idosoby) into ile from osoby where wzrost > (select avg(wzrost) from osoby); return 0; exception when others then return 1; end; procedure dekod(kod varchar2, fun out varchar2); --deklaracja/forward procedure Zmien(kod varchar2) as zap varchar2(111); fun varchar2(11); begin dekod(kod, fun); zap := 'update osoby set nazwisko='||fun||'(nazwisko)'; execute immediate zap; exception when others then raise; end; procedure dekod(kod varchar2, fun out varchar2) as begin if upper(kod) = 'U' then fun:='UPPER'; elsif upper(kod)='L' then fun:='LOWER'; else fun:=null; end if; end; begin --blok inicjalny wykonujacy sie raz przy pierwszym odwolania sesji do pakietu globalna := 11; globalna_wew := 12; exception when others then raise; end; / show errors; / SET SERVEROUTPUT ON; / declare ile int; ok int; begin ok := pakiet.Liczf(1.8,ile); dbms_output.put_line(pakiet.globalna); dbms_output.put_line(ile); ok := pakiet.Liczf(ile); dbms_output.put_line(ile); pakiet.Zmien('l'); end; / show errors; --trigger create or replace trigger upT before insert on osoby begin update osoby set Nazwisko=upper(Nazwisko), Imie=upper(Imie); end; create or replace trigger upT after insert or update on osoby begin update osoby set Nazwisko=upper(Nazwisko), Imie=upper(Imie); end; insert into osoby(idosoby, nazwisko) values(45, 'Nowy'); update osoby set nazwisko=lower(nazwisko); select * from osoby; CREATE OR REPLACE TRIGGER upT --nie dziala before insert or update --before czy after on osoby for each row begin update osoby set Nazwisko=upper(Nazwisko), Imie=upper(Imie) where idosoby = :NEW.idosoby; end; CREATE OR REPLACE TRIGGER upT before insert or update on osoby for each row begin :NEW.nazwisko := upper(:new.nazwisko); :NEW.imie := upper(:new.imie); end; INSERT INTO osoby(idosoby, nazwisko) VALUES(49, 'Jeszcze nowszy'); SELECT * FROM osoby; update osoby set nazwisko=lower(nazwisko); --warunkowe wykonanie triggera tylko przy zmianie imienia badz nazwiska CREATE OR REPLACE TRIGGER upT before insert or update of nazwisko, imie ON osoby for each row when ((new.nazwisko <> upper(new.nazwisko)) or (new.imie <> upper(new.imie))) begin :NEW.nazwisko := upper(:new.nazwisko); :NEW.imie := upper(:NEW.imie); dbms_output.put_line('wykonalem'); end; UPDATE osoby SET rokurodz=0 WHERE rokurodz IS NULL; SELECT * FROM osoby; SET SERVEROUTPUT ON; / insert into osoby(idosoby, nazwisko) values (54, 'babab'); --nowa tabela CREATE TABLE TestT (ID INT PRIMARY KEY, opis VARCHAR2(11)); INSERT INTO TestT VALUES(1, 'bbb'); select * from testt; CREATE OR REPLACE TRIGGER AutoT BEFORE INSERT ON TestT FOR EACH ROW DECLARE Nr int; BEGIN SELECT nvl(MAX(ID)+1, 0) INTO nr FROM TestT; :new.id := Nr; END; --NVL zastępuje ewentualnego NULLa wartością domyślną delete from TestT; -----testy wlasne drop trigger ZarobkiTri --wstawia bieżącą datę desc Zarobki CREATE OR REPLACE TRIGGER ZarobkiTri BEFORE INSERT ON Zarobki FOR EACH ROW DECLARE Nr int; BEGIN SELECT nvl(MAX(IDZAROBKU)+1, 0) INTO nr FROM Zarobki; :NEW.idZarobku := Nr; :NEW.datawyp := to_char(SYSDATE, 'DD/MM/YY'); IF :NEW.brutto < 100 THEN :NEW.brutto := 100; END IF; END; INSERT INTO Zarobki VALUES(41, 2, 50, '01/10/11'); INSERT INTO Zarobki(idosoby, brutto) VALUES(5, 10); SELECT * FROM zarobki --biezaca data: SELECT to_char(SYSDATE, 'Dy DD-Mon-YYYY HH24:MI:SS') AS "Current Time" FROM dual; SELECT to_char(SYSDATE, 'DD/MM/YY') AS "Current Time" from dual; --------15.01.2011 ALTER TRIGGER autot1 DISABLE / DROP SEQUENCE Licznik; CREATE SEQUENCE Licznik START WITH 7 INCREMENT BY 2 MINVALUE 1 MAXVALUE 100 CYCLE CACHE 10; -- magazyn zrobionych na zapas wartosci --noorder - domyslnie / CREATE OR REPLACE TRIGGER autot2 BEFORE INSERT ON TestT FOR EACH ROW --DECLARE Nr int; BEGIN :NEW.ID := Licznik.Nextval;--dziala od wersji oracle 11 --SELECT Licznik.currval FROM dual; --SELECT Licznik.nextval INTO Nr FROM Dual; --:New.Id := Nr; END; / INSERT INTO TestT(Opis) VALUES('xxx'); CREATE OR REPLACE VIEW Dane AS SELECT IdOsoby, Nazwisko, Imie FROM Osoby / CREATE OR REPLACE TRIGGER zamiast INSTEAD OF DELETE ON Dane BEGIN --set serverouput on DBMS_OUTPUT.PUT_LINE('ZAKAZ WJAZDU'); END; / SELECT * FROM Dane delete from dane where idosoby = 54; CREATE OR REPLACE TRIGGER zamiast INSTEAD OF DELETE ON Dane --trigger typu instead of można tworzyć tylko na perspektywach FOR EACH ROW DECLARE ile INT; BEGIN SELECT count(IdZarobku) INTO ile from zarobki WHERE IdOsoby = :OLD.IdOsoby; if (ile > 0) then DBMS_OUTPUT.PUT_LINE('ZAKAZ WJAZDU') ELSE DELETE FROM Oosby WHERE IdOsoby = :OLD.IdOsoby; END IF; END; / SELECT * FROM Dane DELETE FROM dane WHERE idosoby = 54; -----------TRIGGER SYSTEMOWY CREATE OR REPLACE TRIGGER Dla_system AFTER CREATE ON DATABASE --[schema] BEGIN DBMS_OUTPUT.PUT_LINE('Utworzono'); end; / SET SERVEROUTPUT ON; / CREATE OR REPLACE VIEW Dane2 AS SELECT IdOsoby, Nazwisko, Imie FROM Osoby CREATE OR REPLACE TRIGGER Dla_system --trigger systemowy AFTER CREATE ON DATABASE --[schema] DECLARE KTO VARCHAR2(33); KTO1 VARCHAR2(33); BEGIN SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') INTO KTO FROM DUAL;--OS_USER - nazwa użytkownika systemu operacyjnego SELECT SYS_CONTEXT('USERENV', 'OS_USER') INTO KTO1 FROM DUAL; DBMS_OUTPUT.PUT_LINE('Utworzone przez '||KTO||' '||KTO1); --inne zmienne: TERMINAL, HOST, IP_ADDRESS, CURRENT_SQL END; create table Logowanie(kto varchar2(33), kto1 varchar2(33)); create or replace trigger dla_logowania --trigger systemowy AFTER LOGON ON DATABASE --[schema] DECLARE KTO VARCHAR2(33); KTO1 VARCHAR2(33); BEGIN SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') INTO KTO FROM DUAL;--OS_USER - nazwa użytkownika systemu operacyjnego SELECT sys_context('USERENV', 'OS_USER') INTO kto1 FROM dual; insert into Logowanie values (kto, kto1); --inne zmienne: TERMINAL, HOST, IP_ADDRESS, CURRENT_SQL END; ----------KURSORY SET serveroutput ON / DECLARE CURSOR sledz IS SELECT Nazwisko, RokUrodz FROM osoby; Naz VARCHAR2(22); Rok int; BEGIN OPEN sledz; fetch sledz INTO Naz, Rok; while sledz % found loop DBMS_OUTPUT.PUT_LINE(Naz||' '||Rok); DBMS_OUTPUT.PUT_LINE(sledz %rowcount); fetch sledz INTO Naz, Rok; end loop; CLOSE sledz; END; SET serveroutput ON / DECLARE CURSOR sledz IS SELECT * FROM osoby; rec Osoby %ROWTYPE; BEGIN OPEN sledz; fetch sledz INTO rec; while sledz % found loop DBMS_OUTPUT.PUT_LINE(rec.Nazwisko||' '||rec.RokUrodz); DBMS_OUTPUT.PUT_LINE(sledz %rowcount); fetch sledz INTO rec; end loop; CLOSE sledz; END; SET serveroutput ON / DECLARE CURSOR sledz IS SELECT * FROM osoby; BEGIN for rec in sledz loop DBMS_OUTPUT.PUT_LINE(rec.Nazwisko||' '||rec.RokUrodz); DBMS_OUTPUT.PUT_LINE(sledz %rowcount); end loop; END; SET serveroutput ON / DECLARE CURSOR sledz (dzial INT := 2) IS SELECT * FROM osoby --int := 2 - wartosc domyslna parametru WHERE idDzialu = dzial; BEGIN for rec in sledz(3) --bez (3) uruchamia kursor z domyślnym parametrem loop DBMS_OUTPUT.PUT_LINE(rec.Nazwisko||' '||rec.RokUrodz); DBMS_OUTPUT.PUT_LINE(sledz %rowcount); END loop; END; --kursor niejawny SET serveroutput ON / BEGIN for rec in (select * from osoby) loop DBMS_OUTPUT.PUT_LINE(rec.Nazwisko||' '||rec.RokUrodz); END loop; END; --kursor referencyjny SET SERVEROUTPUT ON / DECLARE TYPE cur_t IS REF CURSOR RETURN osoby %rowtype; CUR CUR_T; rec Osoby %RowTYPE; BEGIN OPEN cur FOR SELECT * FROM Osoby; fetch cur INTO rec; WHILE CUR %FOUND LOOP DBMS_OUTPUT.PUT_LINE(REC.NAZWISKO); FETCH CUR into REC; END LOOP; CLOSE CUR; END; SET SERVEROUTPUT ON / DECLARE TYPE cur_t IS REF CURSOR; --ucięte, a dziala CUR CUR_T; rec Osoby %RowTYPE; BEGIN OPEN cur FOR SELECT * FROM Osoby; fetch cur INTO rec; WHILE CUR %FOUND LOOP DBMS_OUTPUT.PUT_LINE(REC.NAZWISKO); FETCH CUR into REC; END LOOP; CLOSE CUR; END; SET SERVEROUTPUT ON / DECLARE TYPE cur_t IS REF CURSOR; --ucięte, a dziala CUR CUR_T; rec Osoby %RowTYPE; BEGIN OPEN cur FOR SELECT * FROM Osoby where IdDzialu = 2; fetch cur INTO rec; WHILE CUR %FOUND LOOP DBMS_OUTPUT.PUT_LINE(REC.NAZWISKO); FETCH CUR into REC; END LOOP; CLOSE CUR; OPEN cur FOR SELECT * FROM Osoby where IdDzialu = 3; --nie wiem po co END; SET SERVEROUTPUT ON / DECLARE TYPE cur_t IS REF CURSOR; CUR CUR_T; REC OSOBY %ROWTYPE; DZIAL INT := 2; zap varchar2(111); BEGIN ZAP := 'SELECT * FROM OSOBY WHERE iddzialu='||DZIAL; OPEN cur FOR ZAP; fetch cur INTO rec; WHILE CUR %FOUND LOOP DBMS_OUTPUT.PUT_LINE(REC.NAZWISKO); FETCH CUR into REC; END LOOP; CLOSE CUR; END; --kursor w procedurze SET SERVEROUTPUT ON / create or replace procedure procur (nrdzial int) as TYPE cur_t IS REF CURSOR; CUR CUR_T; REC OSOBY %ROWTYPE; DZIAL INT := 2; zap varchar2(111); BEGIN ZAP := 'SELECT * FROM OSOBY WHERE iddzialu='||nrdzial; OPEN cur FOR ZAP; fetch cur INTO rec; WHILE CUR %FOUND LOOP DBMS_OUTPUT.PUT_LINE(REC.NAZWISKO); FETCH CUR into REC; END LOOP; CLOSE CUR; END; / BEGIN procur(2); end; / create or replace procedure up (fun varchar2, pole varchar2) as zap varchar2(111); begin zap:='update osoby set '||pole||'='||fun||'('||pole||')'; dbms_output.put_line(zap); execute immediate zap; end; / begin up('lower', 'imie'); end; / Zadanie domowe (sam muszę połapać się w tym jeszcze, so don't ask me ) SIEC ======================= OD | DO | WAGA A | B | 1 A | C | 3 A | D | 5 B | D | 7 C | D | 8 B | A | 1 A <---------> B | \ | C <---------> D DLA WYBRANEGO WEZLA PROCEDURA WYSWIETLA WSZYSTKIE POLACZANIA procedure Wezly (od, ) |
| Aby pisac na forum musisz sie zalogować !!! |