How to create Procedure in PL/SQL, Passing CPF as Paramento and returns 1 if it is the birthday date of the CPF informed and 0 if it is not

Asked

Viewed 65 times

0

How I created this project in PL/SQL?

i created in SQL SERVER, is working however, I need to create also in PL/SQL.

CREATE PROCEDURE [dbo].[SP_DataAniversario]
(
     @CPF VARCHAR(20)
    
)
AS
  SELECT * FROM Clientes 
             where CPF = @CPF
      
  IF(@CPF='1')

    BEGIN

    SELECT * from Clientes where DAY(MONTH(DataAniversario)) = DAY(MONTH(GETDATE()))
    and CPF = @CPF
    
        RETURN 1
    END
ELSE
    BEGIN
        RETURN 0
    END

I pass the CPF, and check if today is CPF’s birthday date informed.

to test I am providing the tables with data

 CREATE TABLE "SYSTEM"."TB_CLIENTES" 
   (    "ID_CLIENTE" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE , 
    "CPF" VARCHAR2(20 BYTE), 
    "NOME" VARCHAR2(100 BYTE), 
    "ENDERECO1" VARCHAR2(150 BYTE), 
    "ENDERECO2" VARCHAR2(150 BYTE), 
    "BAIRRO" VARCHAR2(50 BYTE), 
    "CIDADE" VARCHAR2(50 BYTE), 
    "ESTADO" VARCHAR2(5 BYTE), 
    "CEP" VARCHAR2(8 BYTE), 
    "IDADE" NUMBER(*,0), 
    "DATA_ANIVERSARIO" DATE, 
    "SEXO" VARCHAR2(1 BYTE), 
    "LIMITE_CREDEITO" FLOAT(126), 
    "VOLUME_COMPRA" FLOAT(126), 
    "PRIMEIRA_COMPRA" NUMBER(1,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
REM INSERTING into SYSTEM.TB_CLIENTES
SET DEFINE OFF;
Insert into SYSTEM.TB_CLIENTES (ID_CLIENTE,CPF,NOME,ENDERECO1,ENDERECO2,BAIRRO,CIDADE,ESTADO,CEP,IDADE,DATA_ANIVERSARIO,SEXO,LIMITE_CREDEITO,VOLUME_COMPRA,PRIMEIRA_COMPRA) values ('1','87220630050','Jose Cipriano',null,null,null,null,null,null,null,to_date('11/07/84','DD/MM/RR'),null,null,null,null);
Insert into SYSTEM.TB_CLIENTES (ID_CLIENTE,CPF,NOME,ENDERECO1,ENDERECO2,BAIRRO,CIDADE,ESTADO,CEP,IDADE,DATA_ANIVERSARIO,SEXO,LIMITE_CREDEITO,VOLUME_COMPRA,PRIMEIRA_COMPRA) values ('2','78301914092','Fuampa Bezerra',null,null,null,null,null,null,null,to_date('06/11/84','DD/MM/RR'),null,null,null,null);
--------------------------------------------------------
--  DDL for Index SYS_C007302
--------------------------------------------------------

  CREATE UNIQUE INDEX "SYSTEM"."SYS_C007302" ON "SYSTEM"."TB_CLIENTES" ("ID_CLIENTE") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  Constraints for Table TB_CLIENTES
--------------------------------------------------------

  ALTER TABLE "SYSTEM"."TB_CLIENTES" MODIFY ("ID_CLIENTE" NOT NULL ENABLE);
  ALTER TABLE "SYSTEM"."TB_CLIENTES" ADD PRIMARY KEY ("ID_CLIENTE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE;

  • this code even in SQL does not do what it says to the title of the question, it is not validating whether it found the CPF and the date is today

  • What’s the point of you applying the function DAY the result of the function MONTH? Maybe you want to: DAY(DataAniversario) = DAY(GETDATE()) AND MONTH(DataAniversario) = MONTH(GETDATE()).

  • Ricardo, you are right, but the query in SQL server meets, because it checks the day and Month, as well as your function that reported above also, will return correctly. Can you help me with PL SQL.

  • this condition DAY(Databirthday) = DAY(GETDATE()) AND MONTH(Databirthday) = MONTH(GETDATE()) is the same as: Where DAY(MONTH(Databirthday)) = DAY(MONTH(GETDATE())) ?

  • The function DAY, as well as MONTH, expects to receive a parameter of type DATE but both functions return a INTEGER, so using the result of the MONTH function as a parameter of the DAY function will certainly not give the expected result.

  • anonimo, ok, I get it, can you help me ? what about this PLSQL process?

Show 1 more comment
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.