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');

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; 
/

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;

SELECT * FROM dual;

CREATE TABLE dual (dummy char(1)); 
INSERT INTO dual VALUES ('X');

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;

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');

LOOP 
<hier können Ihre Statements stehen>; 
END LOOP;

LOOP 
<hier können Ihre Statements stehen>; 
END LOOP;

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;

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

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';

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;

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 $$; 
/

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('\xc30d04070302054c0c72adf4838673d23e01c4d85b
254aa242608c46deb203609bd4676dc20bc6feef468b23dd90e804b6d38c6dbbc866
a849a8a4e0f4584ee4bc4b74c8a422072b3e743c9096eb9d','AES_KEY');

CREATE OR REPLACE FUNCTION ora_func (
   p_eins IN INTEGER DEFAULT 42,
   p_zwei IN INTEGER DEFAULT 4711,
   p_drei IN VARCHAR2 DEFAULT 'test'
) RETURN VARCHAR2 AS
BEGIN
   RETURN 'p_eins='
          || p_eins
          || ', p_zwei='
          || p_zwei
          || ', p_drei='
          || p_drei;
END; 
/SELECT ora_func(p_eins=>42, p_zwei=>4711, p_drei=>test ) from dual;
SELECT ora_func(p_eins=>null) FROM dual; 

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();Hinweise: Bei Postgres muss, wenn die Funktion ohne Parameter augefrufen wird, trotzdem funktionname() benutzt werden. Hinweis: Bei Postgres können mehrere Funktionen mit gleichem Namen aber unterschiedlichen Parameteranzahl oder Parametertypen im gleichen Schema existieren.(Overloading)

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;  $$;

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  $$;

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 $$ ;

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;

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;

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;  

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);

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;

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;

SELECT * FROM dual;

CREATE TABLE dual (dummy char(1));
SELECT * FROM dual;

SELECT nvl(null,'X') FROM dual;

SELECT coalesce(null,'X');

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);