set serveroutput on
begin
dbms_output.put_line('szia');
end;
/
select 1+1 from dual;



set serveroutput on
declare
x number;
begin
if x is null then dbms_output.put_line('null vagyok');end if;
end;
/

/*

NULL ;

GOTO label ;

assignment_statement_target := expression ;

SELECT [ { DISTINCT | UNIQUE } | ALL ] 
  { * | select_item [[,] select_item ]... }
    { into_clause | bulk_collect_into_clause }
        FROM { table_reference | [ THE ] ( subquery ) } [ alias ]
          [ { table_reference | [ THE ] ( subquery ) } [ alias ] ]...
            rest-of-statement ;
into_clause ::= INTO { variable [, variable ]... | record )
bulk_collect_into_clause ::= BULK COLLECT INTO { collection | :host_array }
  [, { collection | :host_array } ]...


IF boolean_expression THEN statement [ statement ]...
 [ ELSIF boolean_expression THEN statement [ statement ]... ]...
   [ ELSE statement [ statement ]... ] END IF ;

[ <<label>> ] CASE case_operand
  WHEN boolean_expression THEN statement ;
    [ WHEN boolean_expression THEN statement ; ]...
      [ ELSE statement [ statement ]... ;
        END CASE [ label ] ;

[ <<label>> ] CASE
  WHEN boolean_expression THEN statement ;
    [ WHEN boolean_expression THEN statement ; ]...
      [ ELSE statement [ statement ]... ;
        END CASE [ label ];

[ label ] LOOP
  statements
END LOOP [ label ];

[ label ] FOR index IN [ REVERSE ] lower_bound..upper_bound LOOP
  statements
END LOOP [ label ];

[ FOR record IN
  { cursor [ ( cursor_parameter_dec [ [,] cursor_parameter_dec ]... )]
  | ( select_statement )
  }
    LOOP statement... END LOOP [label] ;


[ label ] WHILE condition LOOP
  statements
END LOOP [ label ];

EXIT [ label ] [ WHEN boolean_expression ] ;

CONTINUE [ label ] [ WHEN boolean_expression ] ;
*/

/
set serveroutput on
<<cimke1>>
DECLARE
  a  NUMBER;
BEGIN
  a:=2;
  <<cimke2>>
  DECLARE
    a number;
  BEGIN
    a:=4;
    dbms_output.put_line(cimke1.a);
    dbms_output.put_line(cimke2.a);
    dbms_output.put_line(a);
  END;
  dbms_output.put_line(a);
END;
/






set serveroutput on
declare
a number :=0;b number :=0; c number :=0;
x number;
cursor k is select sal from emp;
begin
open k;
loop
fetch k into x;
exit when k%notfound;
  if x<1500 then a:=a+1;
    elsif x<3000 then b:=b+1;
    else c:=c+1;
  end if;
end loop;
dbms_output.put_line('a='||a||' b='||b||' c='||c);
close k;
end;
/
-----------------------------------------------------
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;
/


select '&m','&hatar' from dual;
/


--------------------------------------------------------
set serveroutput on
VARIABLE X number
DECLARE
      CURSOR C1(P_DEPTNO NUMBER ) IS
                       SELECT ENAME,EMPNO,SAL FROM EMP WHERE DEPTNO=P_DEPTNO;
      V_c1 C1%ROWTYPE;
      S NUMBER :=0;
BEGIN
      :X := 30;
      
      OPEN C1(:X);
      LOOP
      	FETCH C1 INTO V_c1;
      	EXIT WHEN C1%NOTFOUND;
      	S:=S+V_c1.SAL;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(:X||' OSZTALY OSSZEGe= '||S);
      CLOSE C1;
END;
/
----------------------------------------------------
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;
/
----------------------------------------------

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






set serveroutput on
Accept m prompt 'foglalkozas'
Declare
x varchar(20);
function atlag(fogl in out varchar) return number
 is
   eredm number;
 begin
   select avg(sal) into eredm from emp where job=fogl;
   return eredm;
end;
begin
x:='&m';
dbms_output.put_line('x='||x);
dbms_output.put_line(x||' foglalkozasuak atlagfizetese: '||atlag(x));
end;
/

-----
   case fogl
   when 'CLERK' then fogl:='Titkár';
   when 'SALESMAN' then fogl:='Üzletkötő';
   else fogl:='Egyéb';
   end case;
----
create function atlag(fogl in varchar) return number
is
eredm number;
begin
select avg(sal) into eredm from emp where job=fogl;
return eredm;
end;
/

select atlag('CLERK') from dual;