-- kurzor
-- Beadott foglalkozásúak (Pl. ’CLERK’-ek) közül hány keresete több mint a beadott érték (pl. 1000)

set serveroutput on

accept m prompt ’Foglalkozás’

DECLARE
      V_EMP emp%ROWTYPE;
      N1 NUMBER :=0; N2 NUMBER :=0;
      CURSOR c1(pjob CHAR)  IS SELECT *  FROM emp WHERE job=pjob ;
BEGIN
      OPEN c1('&M') ;
      DBMS_OUTPUT.PUT_LINE('M = '||'&M');
      LOOP
      	FETCH c1 INTO  V_EMP;
      	EXIT WHEN C1%NOTFOUND;
      	IF V_EMP.SAL< &HATAR THEN
      		N1:=N1+1; 
	ELSE
      		N2:=N2+1 ;
      	END IF;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('KISEBB = '||N1);
      DBMS_OUTPUT.PUT_LINE('NAGYOBB ='||N2);
      CLOSE c1;
END;
/
show errors

-- -------------------------------------------
-- mennyit keresnek a 20-as osztályon (környezeti változót használunk)

set serveroutput on

VARIABLE X number
DECLARE
      CURSOR  C1(P_DEPTNO NUMBER )  IS
      SELECT ENAME,EMPNO,SAL FROM EMP WHERE DEPTNO=P_DEPTNO;
      V_EMP C1%ROWTYPE;
      S NUMBER :=0;
BEGIN
      :X := 20;
      
      OPEN C1(:X);
      LOOP
      	FETCH C1 INTO V_EMP;
      	EXIT WHEN C1%NOTFOUND;
      	S:=S+V_EMP.SAL;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(:X||' OSZTALY ' ||'OSSZEG= '||S);
      CLOSE C1;
END;
/
show errors




select sum(sal) from emp where deptno=20;

-- -------------------------------------------
-- ciklusban kigyűjti mennyit keresnek a különböző (10,20,30,50) osztályokon

set serveroutput on

DECLARE
      CURSOR  C1  IS
      SELECT ENAME,EMPNO,SAL,DEPTNO FROM EMP ORDER BY DEPTNO;
      V_EMP C1%ROWTYPE;
      S NUMBER :=0;
      R_DEPT NUMBER;
BEGIN
      OPEN C1;
      FETCH C1 INTO V_EMP;
      LOOP
      	R_DEPT:=V_EMP.DEPTNO;
      	S:=0;
      	WHILE R_DEPT=V_EMP.DEPTNO and c1%found LOOP
      		S:=S+V_EMP.SAL;
      		FETCH C1 INTO V_EMP;
      	END LOOP;
      	DBMS_OUTPUT.PUT_LINE(R_DEPT||' OSZTALY ' ||'OSSZEG= '||S);
      	EXIT WHEN c1%notfound;
      END LOOP;
      CLOSE C1;
END;
/
show errors

-- ----------------------------------------
-- adott foglalkozasuak (pl SALESMAN) jutaleka 10% nöhet

select * from emp;
/

set serveroutput on
DECLARE
      CURSOR C1(PJOB CHAR) IS
      SELECT * FROM EMP WHERE JOB=PJOB  FOR UPDATE;
      V_EMP C1%ROWTYPE;
BEGIN
      OPEN C1('&MUNKA');
      FETCH C1 INTO V_EMP;
      WHILE C1%FOUND LOOP
      	UPDATE EMP SET COMM=COMM*1.1 WHERE CURRENT OF C1;
      	FETCH C1 INTO V_EMP;
      END LOOP;
END;
/
show errors

select * from emp;

UPDATE EMP SET COMM=COMM*1.1 WHERE JOB=’SALESMAN’;

-- ------------------------------------------------
-- a kovetkezoben nem hasznalhato az open, fetch, close utasitasok

set serveroutput on


DECLARE
      a number default 0; b number default 0; c number default 0;
      CURSOR k IS select * from emp;

BEGIN
      for sor in k loop
      	if sor.sal<1500 then a:=a+1;
      		elsif sor.sal<3000 then b:=b+1;
      		else c:=c+1;
      	end if;
      end loop;
      dbms_output.put_line('a='||a||' b='||b||' c='||c);
end;
/
show error