Datentypen - Sie haben das Ende des Internets erreicht

Datentypen
Number(4,2)
Varchar2(10)
Long
Date
xx,xx
round (257,82 , 1) = 257,8
round (257.82 , -2) = 300
Tabelle erstellen  Check (nur angegebene Werte möglich)
Create table personal
(
Personal_nr
number(6)
NOT NULL,
vorname
varchar2(15)
DEFAULT 'Standardname',
constraint PK_Personal
primary key (personal_nr),
constraint FK_Personal_Abteilung
foreign key (abteilung_nr) references abteilung (abteilung_nr) on delete cascade,
constraint CK_Personal_Kz_Weiblich
check (kz_weiblich in (0,1))
);
Tabelle löschen  cascade (alle Fremdschlüssel werden aus den anderen Tabellen gelöscht)
Drop table personal cascade constraints;
Spalten hinzufügen
alter table personal add (
Kz_Geschlecht
);
Datentyp, Feldgröße ändern
alter table personal modify (
Kz_Geschlecht
);
number(1)
Not Null
number(2)
Not Null
Integritätsregeln Nachträglich Hinzufügen
alter table personal
(
add constraint UQ_personal
drop
unique (vorname)
);
löschen ausschalten
disable
enable
Einfügen von Daten
Insert into personal
(personal_nr, vorname)
values
(12, 'Hugo', to_date('09.06.1999' , 'dd.mm.yyyy') );
Daten löschen  keinen * verwenden  ohne where (alle Zeilen gelöscht)
Delete from personal
Where personal_nr = 1;
Daten der Tabelle löschen
Truncate table personal;
Daten ändern
Update personal
Set vorname = 'Hans',
Name = 'Meier'
Where personal_nr = 5;
einschalten
Daten Anzeigen  distinct (gruppieren)
Select distinct personal_nr, count (*) as Anzahl
to_char (geburtsdatum, 'dd.mm.yyyy') Geburtsdatum
From personal
Where Kz_geschlecht = 1
and Geburtsdatum = to_date('01.01.1999' , 'dd.mm.yyyy')
or trunc (Geburtsdatum ) = trunc (sysdate)
Group by name
Having count (*) <= 1
Order by vorname desc, name asc
desc = absteigend
Where: =,<,>,<>,!=,<=,>=,is null, is not null, like, in, between, not, and, or
% = wie Dos *, _ = wie Dos ?
like 'M%'
between 1 and 5
in (3,5) = nur 3 und 5
Datum: Eingabe:
Ausgabe:
Abfrage:
Format:
Runden:
Differenz:
Letzter Tag:
Nächster Tag:
to_date ('01.01.1999' , 'dd.mm.yyyy')
to_char (sysdate, 'dd.mm.yyyy hh24:mi:ss')
trunc (datum) = to_date ('01.01.1999' , 'dd.mm.yyyy')
'dd.mm.yyyy hh24:mi:ss'
to_char (round (to_date ('01.01.1999' , 'dd.mm.yyyy') , 'year') , 'dd.mm.yyyy')
months_between(to_date ('01.01.1999' , 'dd.mm.yyyy') , to_date ('01.01.1999' , 'dd.mm.yyyy'))
to_char (last_day (sysdate) , 'dd.mm.yyyy hh24:mi:ss')
to_char (next_day (sysdate, 'monday') , 'dd.mm.yyyy hh24:mi:ss')
Aggregatfunktionen:
count (*/spalte/distinct)
Max (spalte), min (spalte)
Sum (spalte)
Avg (spalte)
Nur bei:
Nummerisch:
= Anzahl
= größter Wert / kleinster Wert
= Summe
= Durchschnitt
distinct
select count (distinct Einstelldatum) from personal;
Special
where to_char(datum,'yyyy') = '1999'
select name, 6.5 * 2 as Glückszahl
 distinct da gleich gruppiert werden soll
 Auf Jahr 1999 abfragen
 Es wird die Berechnung als Konstante (13) ausgegeben
Unterabfragen
select name, gehalt from personal
where gehalt < (select avg(gehalt) from personal);
... in (select ...);
 bei 1 Rückgabewert: <, >, =, ...
 bei mehreren Rückgabewerten
syncronisierte Unterabfragen
select p1.personal_nr, p1.name
from personal p1
where gehalt > (select avg(gehalt)
from personal p2
where p2.filiale_nr = p1.filiale_nr);
select f.filiale_nr, f.bezeichnung
from filiale f
where not exists (select * from personal p
where p.filiale_nr = f.filiale_nr and
p.geschlecht = 'M');
Joins
Vorgehensweise
select p.personal_nr, f.bezeichnung
from personal p, filiale f
where p.filiale_nr = f.filiale_nr;
Reihenfolge abarbeitung: 1. From
select filiale_nr, bezeichnung
from filiale
where filiale_nr in (select filiale_nr
from personal
where geschlecht = 'M');
1. Welche Tabellen
3. Welche Spalten
5. Gruppierung
2. Where
3. Group
2. Verknüpfung d. Tab
4. Einschränkungen (where)
6. Sortierung
4. Having
5. Select
Views  insert, update, delete möglich
Create or replace view v_personal
(name, filiale_nr, geburtsdatum)
as select p.name, f.filiale_nr, to_char(p.geburtsdatum,'dd.mm.yyyy')
from filiale f, personal p
where f.personal_nr = p.personal_nr
with check option
with read only  Daten nicht änderbar
Spaltenüberschrift (optional + beliebig)
kein Order by möglich
zum abprüfen der where-Klausel
Outer Joins
Select f. filiale, f.bezeichnung, p.personal_nr
From personal p, filiale f
where f.filiale_nr = p.filiale_nr (+);
Haupttabelle
Nachgeordnete Tabelle
(hier sollen alle DS angezeigt werden)
(hier stehen nicht alle DS / hier müssten DS hinzugefügt werden)
 wenn FS keine Not-Null Spalte ist
 bei 1:n Beziehungen
Mengenoperation
UNION
Vereinigung
select filiale_nr, bezeichnung, ort
from filiale
where Ort = 'München'
UNION
select filiale_nr, null, null
from personal
where name = is null
order by 1, 2
 Doppelte DS nur 1 x angezeigt
 gleiche Anzahl an Spalten
 gleicher Datentyp
UNION ALL
 wie oben aber DS doppelt angezeigt
INTERSECT Durchschnitt
 Schnittmenge
MINUS
A1
Restmenge
A2
 DS die in A1 aber nicht in A2 vorkommen
Rechnen mit Tabellenspalten (+ - * /)
Wenn Spalte Null  Ergebnis auch Null
 Lösung:
NVL(Gehalt, 1000)  falls Gehalt NULL, dann Ersatzwert 1000 verwenden
select sum(nvl(Kaufpreis,0)) from fahrzeug;
PL/SQL kein Create, alter
Zahl2 constant number(2) := 13;
 Konstante
Name varchar2(20) default substr(vorname,1,20);
Name2 varchar2(20) not null default 'Buchung';
 initialisierung  nie auf null
If Bed1 then
Elseif Bed2 then
Else
End if
 wie case
 default
Loop
I:= i+1
If i > 100 then
Exit;
End if;
End loop;
 oder
Exit when i > 100
For i in reverse 1..100 loop
...;
end loop;
 nicht
1000..1
for i in 5..50 loop
if mod(i,5) = 0 then
...;
end if;
end loop;
loop
while Bed loop
...;
end loop;
if Bed
Exit;
End if;
End loop;
Cursor
%Notfound
 kein DS gefunden
 Null, False, True
%found
 DS gefunden
 Null, False, True
%Rowcount
 anzahl eingelesener DS  0, 1, ...
%Isopen
 Cursor geöffnet
 False, True
Open c_personal;
Fetch c_personal into l_personal_nr, l_gehalt;
Struktur
rec_personal
c_personal%Rowtype;
Close c_personal;
Fetch c_personal into rec_personal;
DECLARE
Cursor c_filiale is
Select filiale_nr, bezeichnung
From filiale
Order by filiale_nr;
Cursor c_personal (p_filiale_nr number,
p_gehalt number := 3500) is
 Defaultwert 3500
Select personal_nr, name, vorname, gehalt
From personal
Where filiale_nr = p_filiale_nr
And gehalt = p_gehalt;
L_min_gehalt
personal_gehalt%type;
BEGIN
For rec_filiale in c_filiale loop
Dbms_output.put_line(to_char(rec_filiale.filiale_nr) || ' ' || rec_filiale.bezeichnung);
Select min(gehalt)
Into l_min_gehalt
 Ergebnis darf nur 1 DS zurückliefern
From personal
Where filiale_nr = rec_filiale.filiale_nr;
For rec_personal in c_personal (rec_filiale.filiale_nr, L_min_gehlat) loop
Dbms_output.put_line(....);
If c_personal%Rowcount > = 3 then
Exit;
End if;
End loop;
End loop;
END;
/
Fehlerbehandlung
Exception
when No_Data_Found then
Statements;
when others then
Statements;
1.
Too_Many_Rows
Einen Namen für eine best. Fehlernummer vergeben
filiale_noch_vorhanden
exception;
pragma exception _init(filiale_noch_vorhanden, -2292);
when filiale noch_vorhanden then ...
Deklaration
""
Exception
2.
Selbsterstellte System-Fehlermeldung Werfen
when No_Data_Found then
raise_application_error(-20001, 'Personal_nr nicht vorhanden');
3.
Selbsterstellte Fehlermeldung erstellen / prüfen
ort_pruefung
exception;
if l-ort = 'New York' then
raise ort_prüfung
when ort_pruefung then ...
SQLERRM
SQLCODE
SQLERRM(SQLCODE)
Deklaration
Begin
Exception
Fehlermeldung des zuletzt aufgetretenen Fehlers
Fehlernummer
Fehlermeldung zur Fehlernummer
Prozeduren
Funktionen
PROCEDURE maxgehalt(p_filiale_nr IN number) FUNCTION maxgehalt(p_filiale_nr IN number)
RETURN number
IS
IS
l_maximum
personal.gehalt%type;
l_maximum personal.gehalt%type;
BEGIN
BEGIN
select ...
select ...
return l_maximum;
EXCEPTION
EXCEPTION
when ...
when ...
return l_maximum;
END;
END;
Werden definiert im Deklarationsteil im PL/SQL
Stored Procedures
Prozeduren
Funktionen
wie oben aber:
CREATE or replace PROCEDURE ....
wie oben aber:
CREATE or replace FUNCTION ....
show errors PROCEDURE maxgehalt;
show errors FUNCTION maxgehalt;
Verwendung: es können Funktionen verwendet werden, die Wert zurückliefern u. keinen OUT-Parameter haben.
select filiale_nr, max_gehalt(filiale_nr ) from filiale;
Parameter
Übergabe
IN (Default)
OUT
IN OUT
Positional: maxgehalt(5)
Namentlich: maxgehalt(p_filiale_nr => 5, ...)
Gemischt: maxgehalt(p_filiale_nr => 5,p_personal_nr, ...)
Abhängigkeiten
select object_name, object_type, status
from obj
where status = 'INVALID';
Objectstatus prüfen: VALID, INVALID
alter procedure/function maxgehalt compile;
SEQUENCER
create sequence seq_filiale_nr
increment by 1
start with 20
nomaxvalue / maxvalue 50
nominvalue / minvalue 10
nocycle / nocycle
cache 10 / nocache
order / noorder;
erste Zahl
höchste Zahl
kleinste Zahl
Neuanfang wenn Max erreicht
Nr im Hauptspeicher
Reihenfolge der Anforderungen
alter sequence seq_filiale_nr ...;
Start with  steht nicht zur Verfügung
drop sequence seq_filiale_nr;
select seq_filiale_nr.currval from dual;
select seq_filiale_nr.nextval from dual;
aktueller Zählerstand
nächster Zählerstand
für Insert
Package
create or replace package pack_max
is
FUNCTION maxgehalt(p_filiale_nr IN number)
RETURN number;
END pack_max;
/
show errors package pack_max;
create or replace package body pack_max
is
g_max
number;
FUNCTION maxgehalt(p_filiale_nr IN number)
RETURN number
IS
l_maximum personal.gehalt%type;END pack_max;
BEGIN/
select ...
return l_maximum;
EXCEPTION
when ...
return l_maximum;
END maxgehalt;
BEGIN
g_max := 20;
END pack_max;
/
show errors package body pack_max;
Globale Variable
Trigger
nur für Tabellen, nicht für Views
create or replace trigger Personal_bar_uid
before delete or insert or update
on personal
for each row
declare
alter_wert
varchar2(30);
neuer_wert
varchar2(30);
begin
if deleting then
alter_wert := to_char(:old.personal_nr);
neuer_wert := null;
end if;
if inserting then
alter_wert := null;
neuer_wert := to_char(:new.personal_nr);
end if;
if updating ('Filiale_nr‘) then
alter_wert := to_char(:old.personal_nr);
neuer_wert := to_char(:new.personal_nr);
end if;
insert into protokoll
(p_user, datum, alter_wert, neuer_wert)
values
(user, sysdate, alter_wert, neuer_wert);
Z
u
s
at
z
b
ei
s
pi
el
before after eachrow
update insert delete
nur wenn mehrere Ereignisse (delete, insert, update)
nur wenn "for each row":
:old  Wert vor Änderung
:new  Wert nach Änderung
if user != 'SYSTEM‘ then
if to_char(sysdate, 'hh24:mi‘) not between ’08:00‘ and ’17:00‘ then
raise_application_error(-20001, 'Dateneingabe nur' || 'zwischen 08:00 und 17:00 erlaubt!');
end if;
end if;
select seq_personal_nr. nextval
into l_personal_nr
from dual;
:new.personal_nr := l_personal_nr;
 alte Nr = null; neue Nr = z.B. 13, wird in Funktion
eingesetzt
end;
/
show errors trigger Personal_bar_uid;
Datenbanksperren
lock table personal
in row share/exclusive mode;
Sicherheit
Objekt-Privilegien (Kaskadenförmig)
System-Privilegien
(nicht Kaskadenförmig)
Grant select, update(personal_nr), ... / Grant all
on personal
to wenz
with grant option;
Grant create session / Grant any privilege
to wenz
with admin option;
Revoke select / Revoke all
on personal
from wenz;
Revoke create session
from wenz;
create role app_dba
identified by passwort;
grant select, update
on wenz to app_user
grant create session, app_user
to app_dba
grant app_dba
to wenz;