Function / Procedure

--------------------------------------
SET SERVEROUTPUT ON
DECLARE
cod_Dep DEPARTMENTS.DEPARTMENT_ID%TYPE;

CURSOR c1 IS SELECT d.Department_id, d.department_name, e.salary
FROM DEPARTMENTS d join employees e
ON d.DEPARTMENT_ID=e.DEPARTMENT_ID;

salary employees.salary%TYPE;
nam_dep departments.department_name%TYPE;

BEGIN
salary :=0;
cod_dep := 20;
FOR i IN c1 LOOP
IF cod_dep = i.department_id THEN
  salary := salary + i.salary;
END IF;
nam_dep := i.department_name;
END LOOP;
DBMS_OUTPUT.put_line('El departamento ' || nam_dep || ' de codigo: ' || cod_dep ||' tiene como suma de salario: ' || salary );
END;

--v2
SET SERVEROUTPUT ON
DECLARE
cod_Dep DEPARTMENTS.DEPARTMENT_ID%TYPE;

CURSOR c1 IS SELECT d.Department_id, d.department_name, e.salary
FROM DEPARTMENTS d join employees e
ON d.DEPARTMENT_ID=e.DEPARTMENT_ID;

salary employees.salary%TYPE;
nam_dep departments.department_name%TYPE;
r_department c1%ROWTYPE;
BEGIN
salary :=0;
cod_Dep := 20;
OPEN c1;
FETCH c1 INTO r_department;
WHILE (c1%FOUND)
    LOOP
      EXIT WHEN c1%NOTFOUND;
      IF (r_department.department_id = cod_Dep) THEN
        salary := salary + r_department.salary;
      END IF;
      nam_dep := r_department.department_name;
      FETCH c1 INTO r_department;
    END LOOP;
    DBMS_OUTPUT.put_line('El departamento ' || nam_dep || ' de codigo: ' || cod_Dep ||' tiene como suma de salario: ' || salary );
CLOSE c1;
END;


---


DECLARE  nume NUMBER (2);
text VARCHAR2 (100);
prin VARCHAR2 (200);
BEGIN nume:= 1;
FOR i IN 1..5 LOOP text:= 'Create table TEMP_TAB_' || nume;  prin := text|| '( EMPLOYEE_ID   NUMBER(6), FIRST_NAME    VARCHAR2(20), LAST_NAME     VARCHAR2(25) )';
EXECUTE IMMEDIATE prin;
nume := nume+1;
END LOOP;
END;


CREATE OR REPLACE FUNCTION Pais_nombre (f_country_id IN VARCHAR2)
RETURN VARCHAR2 AS
vcountry_name countries.country_id%TYPE;
BEGIN
  SELECT country_name INTO vcountry_name
  FROM countries
  WHERE country_id = f_country_id
  RETURN vcountry_name;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN RETURN 'No existe Dpto';
END Pais_nombre;

SELECT Pais_nombre('AR') FROM DUAL;

CREATE OR REPLACE FUNCTION NOMBRE_DEPTO (fdepartment_id IN NUMBER)
RETURN VARCHAR2 AS
vdepartment_name departments.department_name%TYPE;
BEGIN
SELECT department_name INTO vdepartment_name
FROM departments
WHERE department_id = fdepartment_id;
RETURN vdepartment_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 'No existe Dpto';
END NOMBRE_DEPTO;

SELECT NOMBRE_DEPTO(70) FROM DUAL;