Here are the steps to run the stored procedure in Oracle. They reside in the database. The return keyword you must not use in stored procedures. Why because only functions support the return keyword.
The stored procedure begins with create or replace procedure [procedure name]. You’ll learn in three steps create/verify/run the procedure.
Create Stored procedure
create or replace procedure mytestproc (in_dt DATE)
is
v_out_tx VARCHAR2(10);
v_flag_b BOOLEAN;
begin
if to_char(in_dt,'d')=1 then
v_out_tx:='Y';
dbms_output.put_line(v_out_tx);
else
v_out_tx:='N';
dbms_output.put_line(v_out_tx);
end if;
end;
Input for the procedure, you can supply in the parenthesis ( the variables and their data types). The other variables you can declare in the declaration section ( after the ‘is’).
Verify stored procedure is created
Here is a sample SQL query to verify if the procedure is created.
select *
from user_procedures
where object_type='PROCEDURE';
Call stored procedure
Now, this is the time to call. The CALL keyword is the first one you need to give. Then supply the date. The to_char function checks the day of the week supplied is numeric (since the ‘d’ checks for day of the week).
If the Date is numeric, you can see Y else N.
SQL query to call procedure
CALL mytestproc('27-NOV-21');
The output
N
Related posts