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;

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

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;

/

 

SELECT sys.dbms_crypto.hash(
utl_raw.cast_to_raw('hundkatzemaus'), 3 
/*sys.dbms_crypto.hash_sh1*/)
from dual;

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

CREATE EXTENSION pgcrypto;

SELECT encode(digest('XXfdgdgX', 'sha1'), 'hex');

SELECT pgp_sym_encrypt('hundkatzemaus','AES_KEY');

SELECT pgp_sym_decrypt('\xc30d04070302054c0c72adf4838673d23e01c4d85b254aa242608c46deb203609bd4676dc20bc6feef468b23dd90e804b6d38c6dbbc866a849a8a4e0f4584ee4bc4b74c8a422072b3e743c9096eb9d','AES_KEY');

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

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

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;

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

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

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

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