PROCEDURE WITH MEDIUM

Question

Good morning, everyone,

I have the following tables:

Create table FUNCIONARIOS (num number, nome varchar2(30), custo_hora number);
Create table PROJETOS (cod number, nome varchar2(15), custo_acc number(10,2), horas_acc(10,2));
Create table ALOCACAO_REAL (semana number, horas_trab number, cod_proj number, num_func number);
Create table ALOCACAO_PREV (horas_prev_sem number, cod_proj number, num_func number);

I need to create a process that: check and point out which employees are working on each project in the average of the last 4 weeks, above the amount of hours scheduled for that employee within that Project.

I’ve reached this point, but it’s making a mistake:

create or replace procedure func_horas_excedentes as
/* set serveroutput on; */
begin
    for x in (select * from ALOCACAO_PREV)
    loop
        x.num_func;
        x.cod_proj;
        x.horas_prev_sem;
        select max semana into ult_semana from ALOCACAO_REAL where num_func = x.num;
        select avg (horas_trab) into media from ALOCACAO_REAL where semana > (ult_semana -4);
        select * from ALOCACAO_REAL where num_func = x.num_func and cod_proj = x.cod_proj and media > x.horas_prev_sem;
        /* dbms_output.put_line ('Func: ','Projeto: ','Media Horas Trabalhadas: ','Horas Previstas: ',|| x.num_func, x.cod_proj, media, x.horas_prev_sem); */
    end loop;
    commit;
end;

/

Error:

Errors: PROCEDURE FUNC_HORAS_EXCEDENTES
Line/Col: 12/13 PL/SQL: SQL Statement ignored
Line/Col: 12/17 PL/SQL: ORA-00922: missing or invalid option
Line/Col: 13/104 PLS-00103: Encountered the symbol "|" when expecting one of the following:

   ( - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
   continue avg count current exists max min prior sql stddev
   sum variance execute forall merge time timestamp interval
   date <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
   <an alternatively-quoted string literal with character set specification>
   <an alternatively

With "set server..." before Begin, the most error.

Someone has a suggestion?

Hugs.