Űrlap

Táblalétrehozás

declare
  	emptabla number;
  	depttabla number;
begin
  	select COUNT(table_name) into emptabla from user_tables where table_name='VNEV';
  	select COUNT(table_name) into depttabla from user_tables where table_name='KNEV';
  	if emptabla = 0 then
  		EXECUTE IMMEDIATE 'create table VNEV as SELECT * FROM emp';
  		EXECUTE IMMEDIATE 'alter table VNEV add primary key (empno);'; 
  		EXECUTE IMMEDIATE 'alter table VNEV add foreign key (deptno) references KNEV (deptno);';
 	 	EXECUTE IMMEDIATE 'alter table VNEV add foreign key (mgr) references VNEV (empno);';
  	end if;
  
	if depttabla = 0 then
  	
  		EXECUTE IMMEDIATE 'create table KNEV as SELECT * FROM dept';
  		EXECUTE IMMEDIATE 'alter table KNEV add primary key (deptno)'; 
  
	end if;
end;

Tábla törlése

declare
	sajat number;
begin
	select count (table_name) into sajat from user_tables where table_name='VNEV';

    if sajat > 0 then
        EXECUTE IMMEDIATE 'drop table VNEV;';
        EXECUTE IMMEDIATE 'drop table KNEV;';
    end if;
end;

Drill down

select VNEV.EMPNO as EMPNO,
    VNEV.ENAME as Nev,
    VNEV.JOB as Allas,
    VNEV.MGR as FonokAzon,
    VNEV.HIREDATE as Belepes,
    VNEV.SAL as Fizetes,
    VNEV.COMM as Jutalek,
KNEV.DNAME as Telephely
 from VNEV, KNEV
 where VNEV.deptno = KNEV.deptno and 
VNEV.DEPTNO = :P2_DEPTNO

Felhasználókezelés

begin
	if :APP_USER= 'RENDSZERGAZDA' then
		return true;
    else
    	return false;
    end if;
end;

EMPNO Növelése

declare
 uj_empno number(5);
begin
 select max(empno) into uj_empno from VNEV;
 return uj_empno + 1;
end;

Főnök select list LOV-hoz

select ename, empno from VNEV
where empno in (select mgr from VNEV)

Hasznos függvények

NVL()
	:UTOLSO_LAP := NVL(:AKTUALIS_LAP, :APP_PAGE_ID);
INSTR()
	INSTR(:P1_VALASZT, "ALKALMAZOTT"."JOB") > 0

DLL Import

CREATE OR REPLACE LIBRARY KulsoLibrary AS
   'C:\app\Oracle\product\11.2.0\dbhome_1\BIN\[konyvtar].dll';

CREATE OR REPLACE FUNCTION FUGGVENYNEV
(a IN BINARY_INTEGER, b IN BINARY_INTEGER)
 RETURN BINARY_INTEGER
  AS EXTERNAL
     LIBRARY KulsoLibrary
     NAME "FuggvenyNev"
     LANGUAGE C
     PARAMETERS (a int, b int);

DLL-ek