SET SERVEROUTPUT ON;

--CREATE PROCEDURE get_medicines
--IS
--BEGIN
--DECLARE medicamente_cursor CURSOR FOR
--  SELECT nume, concentratie
--  FROM MEDICAMENT
--  INNER JOIN LOT_FABRICATIE
--  ON id_medicament = cod_medicament
--  WHERE nr_bucati > 200
--
--DEFINE
--v_nume_medicament medicament.nume%ROWTYPE
--v_counter BINARYINTEGER:=0
--BEGIN
--  FETCH medicamente_cursor LOOP v_counter
--  INTO v_nume_medicament


CREATE OR REPLACE PROCEDURE get_medics
(ID number) AS nr
DECLARE
  BEGIN

    SELECT nume, concentratie
    FROM MEDICAMENT
    INNER JOIN LOT_FABRICATIE
    ON id_medicament = cod_medicament
    WHERE nr_bucati > 200
    INTO v_nume_medicament

  END;
  /



DEFINE
  v_nou_gramaj_activ MEDICAMENT.gramaj_activ%ROWTYPE:=0

--TRIGGER ASSIGN recalculate_composition

DEFINE recalculate_composition PROGRAM BOOLEAN
PROGRAM
  SELECT SUM(gramaj_per_bucata)
  AS nou_gramaj_activ
  FROM COMPOZITIE
  INNER JOIN MEDICAMENT
  ON MEDICAMENT.id_medicament = COMPOZITIE.cod_medicament
  FOR UPDATE
  INTO v_nou_gramaj_activ

  UPDATE MEDICAMENT
  SET gramaj_activ = v_nou_gramaj_activ
  WHERE id_medicament = TRIGGER(EVENT)
RETURN TRUE
END;
/




DEFINE
  v_counter BINARYINTEGER:=0;
  v_nume_medicament MEDICAMENT.nume%ROWTYPE:=null;
BEGIN
  PROCEDURE get_medics()
  ALTER TABLE MEDICAMENT


  UPDATE COMPOZITIE
  SET gramaj_per_bucata = 20
  WHERE cod_medicament = 15

END;

EXCEPTION


SET SERVEROUTPUT OFF;