Here is a sample of Stored procedures showing how to run it in Oracle. They store in the database. The return keyword you must not use in stored procedures. Why because only functions support the return keyword.
The stored procedure starts with the ‘create or replace procedure [procedure name]. In three steps, you will know how to create/verify/run the procedure.
Stored procedure example
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';
How to call a 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