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
– Ricardo Pontual
What’s the point of you applying the function
DAY
the result of the functionMONTH
? Maybe you want to:DAY(DataAniversario) = DAY(GETDATE()) AND MONTH(DataAniversario) = MONTH(GETDATE())
.– anonimo
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.
– Leo
this condition DAY(Databirthday) = DAY(GETDATE()) AND MONTH(Databirthday) = MONTH(GETDATE()) is the same as: Where DAY(MONTH(Databirthday)) = DAY(MONTH(GETDATE())) ?
– Leo
The function
DAY
, as well asMONTH
, expects to receive a parameter of typeDATE
but both functions return aINTEGER
, so using the result of the MONTH function as a parameter of the DAY function will certainly not give the expected result.– anonimo
anonimo, ok, I get it, can you help me ? what about this PLSQL process?
– Leo