How to create this procedure

Question

How to create this procedure

Navigation

#1 by (3 votes)

0

I have these two tables in the bank

cadcha (Cadastro de chamadas)

cadram(Cadastroderamal)

Allconnectionsmadetoacertaincompanywillbesavedinthistablecadcha.Thedestinationextensionfieldistheextensionthatansweredormadethisconnection.Ialsohaveasecondtable,calledcadram,wheretheextensionrecordsareperformedmanually,andthesystemreportsaregeneratedfromthiscadramtable.Iwouldliketoimplementaprocedurethat,whentriggered,wouldregistertheextensionsfoundinthebranchnamefieldofthetagtableinthecadramtable(Obviouslyextensionsthathavenotyethasregistered).Example:Wehavethereinthetabletheramalorigem4554,butinthecadramtablewedonothavethisextensionyet.Iwouldliketheproceduretodothis.

WhatI'veachievedsofar:

SELECTthatreturnsallextensionsofconnectionsthathavenotyetbeenregistered:

SELECTDISTINCT(RAMALDESTINO)FROMCADCHAWHERERAMALDESTINONOTIN(SELECTRAMCODFROMCADRAM)

Scopeoftheprocedure,whichisthepartwhereIcouldnotgoforward:Note:Infirebirdprocedures,isitnecessarytopassonevalueperparameterandreturnsomething?

CREATEPROCEDURESP_CADRAM_AUTOMATICO(testeVARCHAR(1))RETURNS(EXCEPTION"Procedure Acionada"
)
AS 
BEGIN
    FOR
        SELECT DISTINCT (RAMALDESTINO)
        FROM CADCHA
        WHERE RAMALDESTINO 
        NOT IN 
        (SELECT RAMCOD FROM CADRAM)
    DO  
    BEGIN   
        INSERT INTO CADCHA (RAMCOD, RAMDES) VALUES (RAMALDESTINO, 'RAMAL');
    END
END

Enforcing: I need the procedure to register the extensions in the table cadram automatically for me, using as base the table of connections (cadcha) in the branch field.

sql procedure firebird

asked by anonymous 17.12.2015 / 16:11

1 answer

URL rewriting in HTACCESS ignoring existing files and folders How to reuse data in a class without having to repeat this data?

score 3 · Accepted Answer

a procedure to do what you want to look like this:

create procedure SP_CADRAM_AUTOMATICO
as
declare variable VRAMAL_DESTINO integer;
begin
  for
      select distinct
          (RAMALDESTINO)
      from
          CADCHA
      where
          RAMALDESTINO not in (select
                                   RAMCOD
                               from
                                   CADRAM)
      into
          VRAMAL_DESTINO
  do
  begin
    insert into CADRAM (
        RAMCOD,
        RAMDES)
    values (
        VRAMAL_DESTINO,
        'RAMAL');
  end
end