|
Oracle
|
PostgreSQL
|
Bemerkung
|
|
RETURN
|
RETURN,
RETURNS
|
|
|
IS
|
AS
|
|
|
BEGIN
|
BEGIN
|
|
|
COMMIT
|
COMMIT;
|
|
|
VARCHAR2
|
VARCHAR
|
|
|
NUMBER
|
NUMERIC,
INTEGER
|
|
|
sysdate
|
now(),
CURRENT_TIMESTAMP, timeofday()
|
Timeofday()
in pgsql einzig wirkliche akt. Zeit, sonst Transaktionsbeginn.
|
|
cErrCode
NUMBER := SQLCODE;
|
cErrCode
VARCHAR := SQLSTATE;
|
|
|
cErrMsg
VARCHAR2(512) := TRIM(SQLERRM);
|
cErrMsg
VARCHAR(512) := TRIM(SQLERRM);
|
|
|
to_char(nInteger)
|
to_char(nInteger,''9999999999'')
|
|
|
to_number(cZeichenkette)
|
to_number(cZeichenkette,''9999999999'')
|
|
|
cursor
CurMainLogPatient IS SELECT PatientID FROM P_LogPatient
|
CurMainLogPatient
Cursor IS SELECT PatientID FROM P_LogPatient
|
|
|
CurMemQuellen
CurQuellen%ROWTYPE;
....
OPEN
CurQuellen;
LOOP
FETCH CurQuellen INTO CurMemQuellen;
|
CurMemQuellen
P_Domaenen%ROWTYPE;
...
OPEN
CurQuellen;
LOOP
FETCH CurQuellen INTO CurMemQuellen;
|
Es
können nur existierende Tabellen oder Views benutzt
werden!
|
|
TYPE
RefCurQuelleS_Patient IS REF CURSOR;
CurQuelleS_Patient
RefCurQuelleS_Patient;
|
CurQuelleS_Patient
REFCURSOR;
CurQuelleS_Patient RefCurQuelleS_Patient;
|
Erzeuge
ungebunden Cursor und ordne
|
|
DBMS_OUTPUT.PUT_LINE
(für Exeption)
|
RAISE level
level := DEBUG -Meldung in Serverlog
LOG -Meldung mit höherer Priorität
in Serverlog
NOTICE -Meld. untersch. Prio. in Serverlog &
an Client senden
WARNING -Meld. untersch. Prio. in Serverlog &
an Client senden
EXCEPTION -Error erzeugen und die akt.
Transaktion abbrechen
|
|
|
EXIT
WHEN CurTicketStatus%NOTFOUND;
|
IF
NOT FOUND THEN
EXIT;
END
IF;
|
|
|
tGiveTime
TIMESTAMP := SYSDATE-(nMinuteWaitForTicket*(1/24/60));
|
tGiveTime
TIMESTAMP := now()- (nMinuteWaitForTicket||' minutes')::Interval;
alternativ
mit days, months, hours, years & seconds
|
|
|
create
or replace PROCEDURE ADM_Autom_TicketGive (nDaysTicketValid
in NUMBER,nMinuteWaitForTicket IN NUMBER) AS
cursor
CurTicketStatus IS SELECT TicketID
FROM
OPENSC.P_TicketStatus WHERE ...
|
CREATE
OR REPLACE FUNCTION adm_autom_ticketgive(integer, integer)
RETURNS
void AS $$
DECLARE
nDaysTicketValid ALIAS FOR $1;
nMinuteWaitForTicket
ALIAS FOR $2;
CurTicketStatus
CURSOR FOR SELECT TicketID FROM OPENSC.P_TicketStatus WHERE ...
|
|
|
EXECUTE
'UPDATE tbl SET ' || spaltenname||' = '|| neuerwert
||
' WHERE ...';
|
EXECUTE
''UPDATE tbl SET '' || quote_ident(spaltenname) || '' = ''
||
quote_literal(neuerwert) || '' WHERE ...'';
quote_ident()
für Spaltennamen
quote_literal()
für Zuweisungen von Literalen zu dyn.Feldern
|
|
|
|
create
or replace function testProcedure(varchar) returns char as $$
Declare
text alias for $1;
Begin
perform
adm_autom_ticketupdate();
raise
notice 'Done! %', text;
return
'k';
end;
$$
language plpgsql;
-----------------------------------------------
select
testProcedure('^^')
|
|
|
|
create
or replace function testProcedure(varchar) returns void as $$
Declare
text alias for $1;
Begin
perform
adm_autom_ticketupdate();
raise
notice 'Done! %', text;
end;
$$
language plpgsql;
-----------------------------------------------
select
testProcedure('^^')
|
'Returns
void... -- dann keine Return;
Aufruf
mit PERFORM ....
|
|
FETCH
CurMainLogPatient INTO nMainPatientID; kein Satz ->
Ergenisvariable bleibt wie sie war!
|
FETCH
CurMainLogPatient INTO nMainPatientID; kein Satz ->
Ergenisvariable NULL
|
Ergebnis
unterschiedlich
|
|
open
cur_viewtable;
if
cur_viewtable%isopen then
loop
fetch
cur_viewtable into ...
|
open
cur_viewtable;
while
found = true
loop
fetch
cur_viewtable into
|
Überprüfung,
ob Cursor offen
|
|
IF
err_num = -955 THEN RAISE EXCEPTION ...
|
IF
err_num = '42P07' THEN RAISE EXCEPTION ...
|
|
