Skip to Main Content

  Vergleich PL/SQL und PG/SQL

    Zu unseren Schulungen

Hier veröffentlichen wir regelmäßig kurze Postgres PG/SQL-Code-Snippets im Vergleich zu Oracle PL/SQL.

Wir unterstützen Sie gerne bei einer Oracle zu Postgres Migration und haben dafür auch einige praktische Tools geschrieben.

Datentypen CAST  Tipp 111

     Thema: DEKLARATION      Datenbank-Version: 12.1, 12.2
     Erstellt am 03.12.19      Bearbeitet am 03.12.19
Oracle hat leider (noch ) keinen Datentyp Boolean.
SELECT CAST('123,45' AS BINARY_DOUBLE) FROM dual;
SELECT CAST('123,45' AS BINARY_FLOAT) FROM dual;
SELECT CAST('123,45' AS NUMBER ) FROM dual;
SELECT CAST('123' AS INTEGER) FROM dual;

SELECT CAST ('31.12.2019' as DATE) FROM dual;
SELECT CAST ('31.12.2019' AS TIMESTAMP) FROM dual;

SELECT CAST(123.45 AS VARCHAR(8)) FROM dual;
SELECT CAST(123.45 AS VARCHAR2(8)) FROM dual;
SELECT CAST(123.45 AS CHAR(7)) FROM dual;

SELECT CAST('ABC' AS raw(8)) from dual;
SELECT CAST ('true' as BOOLEAN),
CAST ('false' as BOOLEAN),
CAST ('T' as BOOLEAN),
CAST ('F' as BOOLEAN);

SELECT CAST('123.45' AS DOUBLE PRECISION);
SELECT CAST('123' AS INTEGER);

SELECT CAST ('2019-12-31' as DATE);
SELECT CAST ('31-DEC-2019' as DATE);
SELECT CAST ('31.12.2019' AS TIMESTAMP);

SELECT CAST(123.45 AS VARCHAR);
SELECT CAST(123.45 AS TEXT);
SELECT CAST(123.45 AS CHAR(4));
SELECT CAST('ABC' AS bytea);
Aber Postgres hat sogar noch eine praktische und kurze CAST Funktion verfügbar:
SELECT
'T'::BOOLEAN,
123::VARCHAR,
123::VARCHAR(2), -- => 12
'123'::INTEGER,
'31-12-2019'::DATE,
'31-12-2019'::TIMESTAMP
'ABC'::BYTEA; -- =>\x414243 

Exceptionhandling  Tipp 108

     Thema: EXCEPTION      Datenbank-Version: 12.1, 12.2
     Erstellt am 27.11.19      Bearbeitet am 27.11.19
BEGIN
    EXECUTE IMMEDIATE(
    'CREATE TABLE yyy (a INT)');
    EXECUTE IMMEDIATE(
   'CREATE TABLE yyy (a INT)');
EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line(
   'SQLCode: '||
    SQLCODE|| ' // SQLErrm: ' ||
    SQLERRM);
END;
DO $$
BEGIN
    CREATE TABLE yyy (a INT);
    CREATE TABLE yyy (a INT);

EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE '% %', SQLERRM, SQLSTATE;
END; $$ language 'plpgsql';

Ab Version 9.2 wird empfohlen für das Exceptionhandling "GET STACKED DIAGNOSTICS" zu verwenden.
DO $$
DECLARE
    v_state   TEXT;
    v_msg     TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;

BEGIN

    CREATE TABLE yyy (a INT);
    CREATE TABLE yyy (a INT);

EXCEPTION WHEN OTHERS THEN

    GET STACKED DIAGNOSTICS
      v_state   = returned_sqlstate,
      v_msg     = message_text,
      v_detail  = pg_exception_detail,
      v_hint    = pg_exception_hint,
      v_context = pg_exception_context;

    RAISE NOTICE 'Exception:
      state   : %
      message : %
      detail  : %
      hint    : %
      context : %', v_state, v_msg, v_detail, v_hint, v_context;

END; $$ language 'plpgsql';

Collections bzw. Arrays  Tipp 109

     Thema: COLLECTION      Datenbank-Version: 12.1, 12.2
     Erstellt am 28.11.19      Bearbeitet am 28.11.19
DECLARE
    TYPE array_t IS VARRAY(3) OF VARCHAR2(10);
    array_v array_t := array_t('Matt', 'Joanne', 'Robert');
BEGIN
    FOR i IN 1..array_v.count LOOP
        dbms_output.put_line(array_v(i));
    END LOOP;
END;
/
DO $$ DECLARE
    array_v VARCHAR[] = array['Matt', 'Joanne', 'Robert'];
BEGIN
    FOR i IN 1..array_upper(array_v, 1) LOOP
        RAISE NOTICE '%', array_v[i];
    END LOOP;
END; $$ language plpgsql;

DBMS_STATS  Tipp 92

     Thema: PACKAGES      Datenbank-Version: 12.1, 12.2
     Erstellt am 18.11.19      Bearbeitet am 18.11.19
Wer bei Oracle schon mal das Package dbms_stats verwendet hat, weiß um dessen Mächtigkeit.
Leider hat Postgres nur einen Bruchteil der Möglichkeiten bei der Statistikerfassung, aber wir versuchen uns mal eine eigene Procedure zu bauen.

Oracle Package dbms_stats.gather_table_stats:
BEGIN dbms_stats.gather_table_stats(
'SCOTT','EMP');
END;

und Oracle Package dbms_stats.gather_schema_stats:
BEGIN 
dbms_stats.gather_schema_stats(
'SCOTT');
END;

 
CREATE SCHEMA dbms_stats;
CREATE OR REPLACE PROCEDURE dbms_stats.gather_table_stats(
    ownname varchar(128) DEFAULT NULL,
    tabname varchar(128) DEFAULT NULL)
    LANGUAGE 'plpgsql'
AS $$
DECLARE
str VARCHAR(200);
BEGIN
IF tabname IS NULL THEN
    RAISE INFO 'Tabllenname wurde nicht angegeben';
    RETURN;
END IF;
IF ownname IS NULL THEN
    str:= 'ANALYZE '||tabname;
ELSE
    str :='ANALYZE '||ownname||'.'||tabname;
END IF;
RAISE INFO '%',str;
EXECUTE str;
RAISE INFO 'OK';
EXCEPTION WHEN OTHERS THEN
RAISE INFO 'Error Name:%',SQLERRM;
RAISE INFO 'Error State:%', SQLSTATE;
-- call dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'emp');
end; $$;
CREATE OR REPLACE PROCEDURE dbms_stats.gather_schema_stats(
    ownname varchar(128) DEFAULT NULL)
    LANGUAGE 'plpgsql' AS $$
DECLARE
  tab RECORD;
BEGIN
  FOR tab IN (select t.relname::varchar AS table_name
                FROM pg_class t
                JOIN pg_namespace n ON n.oid = t.relnamespace
                WHERE t.relkind = 'r' and n.nspname::varchar = ownname
                ORDER BY 1)
  LOOP
    RAISE NOTICE 'ANALYZE %1.%2', ownname, tab.table_name;
    EXECUTE 'ANALYZE '||ownname||'.'||tab.table_name;
  END LOOP;
  -- CALL dbms_stats.gather_schema_stats('scott');
end $$;

 

ROWCOUNT  Tipp 93

     Thema: Allgemeines      Datenbank-Version: 12.1, 12.2
     Erstellt am 18.11.19      Bearbeitet am 18.11.19
CREATE OR REPLACE PROCEDURE oracle_test
as
BEGIN
DELETE FROM t WHERE id = 1;
dbms_output.put_line('DELETES:'||SQL%ROWCOUNT);

INSERT INTO t SELECT * FROM tt;
dbms_output.put_line('INSERTS:'||SQL%ROWCOUNT);

UPDATE t SET c=1;
dbms_output.put_line('UPDATES:'||SQL%ROWCOUNT);

END;
/
CREATE OR REPLACE PROCEDURE pg_test()
LANGUAGE plpgsql  
as $$
Declare
cnt INT;
BEGIN

WITH a AS (DELETE FROM t WHERE id = 1 RETURNING 1)
    SELECT count(*) INTO cnt FROM a;
RAISE NOTICE 'DELETES:%',cnt;

WITH a AS (INSERT INTO t SELECT * FROM tt RETURNING 1)
    SELECT count(*) INTO cnt FROM a;
RAISE NOTICE 'INSERTS:%',cnt;

WITH a AS (UPDATE t SET c=1 RETURNING 1)
    SELECT count(*) INTO cnt FROM a;
RAISE NOTICE 'UPDATES:%',cnt;

END;
$$;

Cursor auf Tabelle  Tipp 94

     Thema: Cursor      Datenbank-Version: 12.1, 12.2
     Erstellt am 18.11.19      Bearbeitet am 18.11.19


BEGIN
FOR emp_tab IN 
(select * from scott.emp) LOOP
 dbms_output.put_line(
  emp_tab.ename||' '||
  emp_tab.job||' '||
  emp_tab.sal||' '||
  emp_tab.deptno);
END LOOP;
END;
DO $$ DECLARE
  emp_tab RECORD;
BEGIN
  FOR emp_tab IN 
  (select * from scott.emp) LOOP
    RAISE NOTICE '% % % %', 
     emp_tab.ename,
     emp_tab.job,
     emp_tab.sal,
     emp_tab.deptno;
  END LOOP;
END $$;

Datentypen in PG/SQL und PL/SQL im Deklarationsteil  Tipp 95

     Thema: DEKLARATION      Datenbank-Version: RDBMS 12.x
     Erstellt am 19.11.19      Bearbeitet am 19.11.19
DECLARE
var_a NUMBER:=1;
var_b VARCHAR2(2001):='ABCDEF';
var_c CHAR(100):='ABCDEF';
var_d DATE:=sysdate;
var_e CLOB;
var_f BLOB;
var_g BOOLEAN:=TRUE;
var_h BINARY_FLOAT;
var_i BINARY_DOUBLE;
var_j ROWID;
BEGIN
NULL;
END;
DO $$
DECLARE
var_a integer :=1;
var_b VARCHAR(2001):='ABCDEF';
var_c CHAR(100):='ABCDEF';
var_d DATE:=now();
var_e text;
var_f bytea;
var_g BOOLEAN:=TRUE;
var_h numeric;
var_i numeric;
BEGIN
NULL;
END $$;

Schleifen mit LOOP...END LOOP  Tipp 97

     Thema: SCHLEIFEN      Datenbank-Version: RDBMS 12.x
     Erstellt am 19.11.19      Bearbeitet am 19.11.19
DECLARE
i NUMBER:=0;
BEGIN
LOOP
    i:=i+1;
    EXIT WHEN i>10; -- Schleife bei i=10 verlassen
END LOOP;
END;
/
CREATE OR REPLACE FUNCTION f ( v IN NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
i NUMERIC:=0;
BEGIN
LOOP
    i:=i+1;
    EXIT WHEN i>10; -- Schleife bei i=10 verlassen
END LOOP;
END;
$$ LANGUAGE plpgsql;

Basis ist eine Tabelle  Tipp 98

     Thema: DEKLARATION      Datenbank-Version: RDBMS 12.x
     Erstellt am 19.11.19      Bearbeitet am 19.11.19
DECLARE
TYPE emp_type IS TABLE OF scott.emp%ROWTYPE
INDEX BY BINARY_INTEGER;
l_data  emp_type;
BEGIN
    l_data(1).empno:=8000;
    l_data(1).ename:='Patzwahl';
    l_data(5).empno:=8001;
    l_data(5).ename:='Huberl';
END;
DO $$
DECLARE
l_data text[];
BEGIN
    l_data[1]:=8000;
    l_data[2]:='Patzwahl';
    l_data[3]:=8001;
    l_data[4]:='Huberl';
END;
$$ LANGUAGE plpgsql;

BULK - einlesen IN Record - Array einer PL/SQL Tabelle  Tipp 99

     Thema: Allgemeines      Datenbank-Version: RDBMS 12.x
     Erstellt am 19.11.19      Bearbeitet am 19.11.19
DECLARE
    CURSOR emp_cur IS
        SELECT * FROM scott.emp;
    TYPE emp_tab_type IS TABLE OF emp_cur%ROWTYPE;
    emp_tab emp_tab_type;
BEGIN
    OPEN emp_cur;
    FETCH emp_cur BULK COLLECT
    INTO emp_tab;    
       CLOSE emp_cur;
END;
/

 

CREATE OR REPLACE FUNCTION get_emp_tab()
RETURNS setof emp as $$
--declare a emp[] = (select array(select emp from emp));
begin
  --RAISE NOTICE '%',a.count;
  return query SELECT * FROM emp;
END;
$$ language plpgsql;



 

Return Datentyp NUMBER  Tipp 101

     Thema: Allgemeines      Datenbank-Version: RDBMS 12.x
     Erstellt am 19.11.19      Bearbeitet am 19.11.19
CREATE OR REPLACE FUNCTION f
  ( v IN NUMBER) RETURN NUMBER IS
BEGIN
    RETURN mod(var,2) ;
END;
/
CREATE OR REPLACE FUNCTION f
  ( v IN NUMERIC) RETURNS NUMERIC AS $$
BEGIN
    RETURN (v+1);
END;
$$ LANGUAGE plpgsql;
Aufruf:
SELECT f(1);

Return Datentyp DATE  Tipp 102

     Thema: Allgemeines      Datenbank-Version: RDBMS 12.x
     Erstellt am 19.11.19      Bearbeitet am 19.11.19
CREATE OR REPLACE FUNCTION f
  ( v IN DATE) RETURN DATE IS
BEGIN
    RETURN (v+1);
END;
/

SELECT f(sysdate) FROM dual;
CREATE OR REPLACE FUNCTION f
  ( v IN DATE) RETURNS DATE AS $$
BEGIN
    RETURN (v+1);
END;
$$ LANGUAGE plpgsql;

Funktionen mit Rekusion  Tipp 103

     Thema: FUNKTIONEN      Datenbank-Version: RDBMS 12.x
     Erstellt am 19.11.19      Bearbeitet am 19.11.19
CREATE OR REPLACE FUNCTION fac (n integer)
RETURN NUMBER
IS
BEGIN
     IF n=1 THEN
        RETURN  1;
     ELSE
        RETURN n*fac(n-1);
     END IF;
END;
/
CREATE OR REPLACE FUNCTION fac (n integer)
RETURNS NUMERIC
AS $$
BEGIN
     IF n=1 THEN
        RETURN  1;
     ELSE
        RETURN n*fac(n-1);
     END IF;
END;
$$ LANGUAGE plpgsql;

 

Dual Tabelle  Tipp 104

     Thema: Allgemeines      Datenbank-Version: RDBMS 12.x
     Erstellt am 19.11.19      Bearbeitet am 19.11.19
SELECT * FROM dual;
Create TABLE dual (dummy char(1));

NVL Funktionen  Tipp 105

     Thema: Allgemeines      Datenbank-Version: RDBMS 12.x
     Erstellt am 19.11.19      Bearbeitet am 19.11.19
SELECT nvl(null,'X') FROM dual;
SELECT coalesce(null,'X');

Pipelined Table Function  Tipp 110

     Thema: FUNKTIONEN      Datenbank-Version: 12.1, 12.2
     Erstellt am 29.11.19      Bearbeitet am 29.11.19
CREATE OR REPLACE TYPE emp_type
AS OBJECT (EMPNO NUMBER(4,0),
    ENAME       VARCHAR2(10 BYTE),
    JOB         VARCHAR2(9 BYTE),
    HIREDATE    DATE,
    SAL         NUMBER(7,2),
    DEPTNO      NUMBER(2,0));
/
 
CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_type;
 
CREATE OR REPLACE FUNCTION  get_emp_tab
RETURN  emp_tab_type PIPELINED IS
BEGIN
    FOR rec IN (SELECT * FROM emp) LOOP
        PIPE ROW (emp_type(
      rec.empno,
      rec.ename,
      rec.job,
      rec.hiredate,
      rec.sal,
      rec.deptno)) ;
    END LOOP;   
END;
/
 
SELECT * FROM table(get_emp_tab);
CREATE OR REPLACE FUNCTION get_emp (p_deptno INT)
   RETURNS TABLE (
      empno     scott.emp.empno%TYPE,
      ename     scott.emp.ename%TYPE,
      job         scott.emp.job%TYPE,
      hiredate     scott.emp.hiredate%TYPE,
      sal        scott.emp.sal%TYPE
      deptno     scott.emp.deptno%TYPE
)
AS $$
BEGIN
   RETURN QUERY
   SELECT e.empno,
   e.ename,
   e.job,
   e.hiredate,
   e.deptno
   FROM scott.emp e
   WHERE e.deptno = p_deptno ;
END; $$
LANGUAGE 'plpgsql';

select * from get_emp(10);

Trigger  Tipp 107

     Thema: TRIGGER      Datenbank-Version: 12.1, 12.2
     Erstellt am 19.11.19      Bearbeitet am 19.11.19
CREATE OR REPLACE TRIGGER trg_ins
BEFORE INSERT
   ON t
   FOR EACH ROW
BEGIN

IF :NEW.ID IS NULL THEN   
  :NEW.ID:= ADRESS_SEQ.NEXTVAL;
END IF;
IF :NEW.c_time IS NULL THEN
  :NEW.c_time := SYSDATE;
  :NEW.l_update := SYSDATE;
END IF;
END;
CREATE OR REPLACE TRIGGER trg_ins
BEFORE 
INSERT ON t
FOR EACH ROW
EXECUTE PROCEDURE t_tf() ;

CREATE OR REPLACE FUNCTION t_tf()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.ID IS NULL THEN 
  NEW.ID:= ADRESS_SEQ.NEXTVAL;
END IF;
IF NEW.C_TIME IS NULL THEN
  NEW.C_TIME := now();
  NEW.L_UPDATE :=now();
END IF;
END;
$$ LANGUAGE plpgsql;

Dual Tabelle  Tipp 86

     Thema: Allgemeines      Datenbank-Version: 12.1, 12.2
     Erstellt am 07.11.19      Bearbeitet am 07.11.19
SELECT * FROM dual;
CREATE TABLE dual (dummy char(1));
INSERT INTO dual VALUES ('X');

INSTR  Tipp 88

     Thema: FUNKTIONEN      Datenbank-Version: 12.1, 12.2
     Erstellt am 08.11.19      Bearbeitet am 08.11.19
instr (<col|string>,<suchstring>,<anf_pos>)
CREATE FUNCTION instr(
string      varchar,
such_string varchar,
beg_index   integer)
RETURNS integer 
AS $$
DECLARE
pos integer NOT NULL DEFAULT 0;
temp_str varchar;
beg integer;
length integer;
ss_length integer;

BEGIN
IF beg_index > 0 THEN
temp_str := substring(string FROM beg_index);
pos := position(such_string IN temp_str);
IF pos = 0 THEN
  RETURN 0;
ELSE
  RETURN pos + beg_index - 1;
END IF;
ELSIF beg_index < 0 THEN
ss_length := char_length(such_string);
length := char_length(string);
beg := length + 1 + beg_index;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length);
IF such_string = temp_str THEN
  RETURN beg;
END IF;
beg := beg - 1;
END LOOP;
  RETURN 0;
ELSE
  RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

NVL Funktion  Tipp 89

     Thema: Allgemeines      Datenbank-Version: 12.1, 12.2
     Erstellt am 08.11.19      Bearbeitet am 08.11.19
SELECT nvl(null,'X') FROM dual;
CREATE OR REPLACE FUNCTION nvl(p1 text,p2 text)
  RETURNS text AS $$
  SELECT coalesce($1, $2);
  $$
LANGUAGE sql;

SELECT NVL(null,'X');

Schleifen (Loop)  Tipp 85

     Thema: Allgemeines      Datenbank-Version: 12.1, 12.2
     Erstellt am 07.11.19      Bearbeitet am 07.11.19
LOOP
    <hier können Ihre Statements stehen>;
END LOOP;
LOOP
    <hier können Ihre Statements stehen>;
END LOOP;

dbms_output  Tipp 90

     Thema: Allgemeines      Datenbank-Version: 12.1, 12.2
     Erstellt am 08.11.19      Bearbeitet am 08.11.19
EXEC dbms_output.put_line('Hello');
CREATE SCHEMA dbms_output;
CREATE OR REPLACE PROCEDURE dbms_output.put_line(txt text) AS
$$
begin
 raise notice '%', txt;
end;
$$ language plpgsql;
call dbms_output.put_line('Hello');

Schleife mit while loop  Tipp 106

     Thema: SCHLEIFEN      Datenbank-Version: RDBMS 12.x
     Erstellt am 19.11.19      Bearbeitet am 19.11.19
DECLARE
i NUMBER(5,2):=0.00;

BEGIN
    WHILE i<5.00 LOOP -- Schleife bei i=5 verlassen
        dbms_output.put_line('i='||i);
        i:=i+0.50;
    END LOOP;
END;
/
DO $$
DECLARE
i NUMERIC(5,2):=0.00;
BEGIN
    WHILE i<5.00 LOOP -- Schleife bei i=5 verlassen
        RAISE NOTICE 'i=%',i;
        i:=i+0.50;
    END LOOP;
END;
$$ LANGUAGE plpgsql;
/

Anonymer Block  Tipp 87

     Thema: Allgemeines      Datenbank-Version: 12.1, 12.2
     Erstellt am 07.11.19      Bearbeitet am 07.11.19
DECLARE
  zahl integer := 0;
BEGIN
   zahl := zahl + 1;
   dbms_output.put_line('Zahl steht auf '|| zahl);
END;
DO $$
<<my_block>>
DECLARE
  zahl integer := 0;
BEGIN
   zahl := zahl + 1;
   RAISE NOTICE 'Zahl steht auf %', zahl;
END my_block;
$$ LANGUAGE plpgsql;

Verschlüsselung und Hash Packages in Postgres  Tipp 91

     Thema: PACKAGES      Datenbank-Version: 12.1, 12.2
     Erstellt am 18.11.19      Bearbeitet am 18.11.19
Oracle Hashing:
SELECT sys.dbms_crypto.hash(
utl_raw.cast_to_raw('hundkatzemaus'), 3 
/*sys.dbms_crypto.hash_sh1*/)
from dual;

Oracle Verschlüsselung und Entschlüsselungsfunktion:
CREATE OR REPLACE FUNCTION crypt (
text         IN     VARCHAR2,
cryptmode    IN     VARCHAR2 DEFAULT 'E',
key        IN    VARCHAR2 DEFAULT 'MuniQSoft_Key') 
RETURN VARCHAR2 IS
p_typ PLS_INTEGER:=4360; 
--ENCRYPT_AES256+chain_cbc+ pad_pkcs5;
p_key VARCHAR2(512);
BEGIN
p_key := sys.UTL_I18N.STRING_TO_RAW(lpad(key,32,'-+'));
  IF substr(upper(cryptmode),1,1)='E' THEN -- Verschlüsselung
      RETURN (sys.dbms_crypto.encrypt(
        src => sys.UTL_I18N.STRING_TO_RAW(text,'AL32UTF8'),    typ => p_typ, key => p_key));
  ELSE -- Entschlüsselung
      RETURN sys.UTL_I18N.RAW_TO_CHAR(
        sys.dbms_crypto.decrypt(
        src => text, typ => p_typ, key => p_key));
  END IF;
END;
/
Zuerst muss eine Extension installiert werden:
CREATE EXTENSION pgcrypto;

Postgres Hashing:
SELECT encode(digest('XXfdgdgX', 'sha1'), 'hex');
Zur Wahl würde auch noch md5 als Parameter stehen, sollte aus Sicherheitsgründen nicht mehr verwendet werden.

Postgres Verschlüsselung:
SELECT pgp_sym_encrypt('hundkatzemaus','AES_KEY');

Postgres Entschlüsselung:
SELECT pgp_sym_decrypt('\xc30d04070302054c0c72adf4838673d23e01c4d85b254aa242608c46deb203609bd4676dc20bc6feef468b23dd90e804b6d38c6dbbc866a849a8a4e0f4584ee4bc4b74c8a422072b3e743c9096eb9d','AES_KEY');

Vergleich der Funktionen  Tipp 112

     Thema: FUNKTIONEN      Datenbank-Version: 12.1, 12.2
     Erstellt am 10.12.19      Bearbeitet am 10.12.19
CREATE OR REPLACE FUNCTION ora_func(p_eins IN integer default 42,
                    p_zwei integer default 4711,
                    p_drei varchar2 default 'test')
  RETURN VARCHAR2 AS 
BEGIN
  RETURN 'p_eins='|| p_eins ||', p_zwei='|| p_zwei ||', p_drei=' || p_drei;
END;

Mögliche Aufrufe:
SELECT ora_func FROM dual;
=>p_eins=42, p_zwei=4711, p_drei=test
SELECT ora_func(p_eins=>null) FROM dual;
p_eins=, p_zwei=4711, p_drei=test
Hinweise:
Bei Oracle muss in einem SQL Kontext für den Funktionaufruf auch eine Tabelle verwendet werden (meist die DUAL Tabelle), bei Postgres ist das nicht notwendig
Sowohl Oracle als auch Postgres unterstützen auch OUT und INOUT Parameter, nur schreibt Oracle IN OUT und Postgres INOUT.
Procedure unterstützen nur IN und INOUT in Postgres
CREATE OR REPLACE FUNCTION pg_func(p_eins IN integer default 42,
                    p_zwei integer default 4711,
                    p_drei varchar default 'test')
  RETURNS TEXT AS $$
BEGIN
    RETURN FORMAT('p_eins=%s, p_zwei=%s, p_drei=%s', p_eins, p_zwei, p_drei);
END; $$
LANGUAGE plpgsql;

Mögliche Aufrufe:
SELECT pg_func();
=>"p_eins=42, p_zwei=4711, p_drei=test"
SELECT pg_func(p_eins=>null);
=>"p_eins=, p_zwei=4711, p_drei=test"
Hinweise
Bei Postgres muss, wenn die Funktion ohne Parameter augefrufen wird, trotzdem funktionname() benutzt werden.

   Wussten Sie, dass die Muniqsoft Training seit Jahrzehnten Schulungen zu Oracle-Datenbanken und PL/SQL anbietet? Erfahren Sie, was wir auch für Ihre Firma tun können.

exploreKurse ansehen