ABD - Ficha 7

CREATE OR REPLACE FUNCTION irss(sal IN NUMBER) RETURN NUMBER AS
  res NUMBER;
BEGIN
  IF sal > 1000 THEN
    res := sal*0.2;
  ELSE
    res := sal*0.1;
  END IF;
  RETURN res;
END;

DECLARE x NUMBER;
BEGIN
x:=irss(2000);
DBMS_OUTPUT.PUT_LINE(x);
END;

SELECT ename, sal, irss(sal) FROM emp;

/*1*/
CREATE OR REPLACE PROCEDURE atsal(nemp NUMBER, valsub NUMBER) IS
  salar NUMBER;
  mger NUMBER;
  encsal NUMBER;
  salario_superior EXCEPTION;
BEGIN
  SELECT sal,mgr INTO salar,mger FROM emp WHERE empno = nemp;
  SELECT sal INTO encsal FROM emp WHERE empno = mger;
  IF salar+valsub < encsal THEN
    UPDATE emp SET sal = salar+valsub WHERE empno = nemp;
  ELSE
    RAISE salario_superior;
  END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line('Erro: Empregado errado.');
  WHEN salario_superior THEN
    DBMS_OUTPUT.put_line('Erro: o salário seria maior que o encarregado.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Erro: UNKNOWN.');
END;

BEGIN
  atsal(1693,40);
  atsal(1693,4000);
  atsal(5,40);
END;
BEGIN
  atsal(1693,40);
  atsal(1693,4000);
  atsal(5,40);
END;
/*2*/
CREATE SEQUENCE seq_emp INCREMENT BY 1 START WITH 8000;

CREATE OR REPLACE PROCEDURE criaremp(enam VARCHAR2,jo VARCHAR2, mg NUMBER,hire DATE, sal NUMBER, com NUMBER, deptno NUMBER, div VARCHAR2) IS

BEGIN
  INSERT INTO emp VALUES(seq_emp.NEXTVAL, enam, jo, mg, hire, sal, com, deptno, div);
END;

BEGIN
criaremp('Luis', 'Nothing', 1839, '15.11.23', 2500, 20, 10, 'EUR');
criaremp('Pedro', 'Nothing', 1839, '15.11.23', 2500, 20, 10, 'EUR');
END;