Useful Oracle SQL PL/SQL Code Snippets for my Reference

A sample PL / SQL that has 2 Input Paremeters and 2 output parameters that intake and return the values.

CREATE OR REPLACE PROCEDURE SP_WEEKDAYS(START_DT IN DATE, END_DT IN DATE, CALENDARID IN CHAR, TOTALDAYS OUT NUMBER, WORKINGDAYS OUT NUMBER)
IS
BEGIN
SELECT COUNT(WORKDAY_FLG)INTO TOTALDAYS FROM RIP_CAL_FACTORY_CALENDAR
WHERE CALENDAR_DATE BETWEEN START_DT AND END_DT AND (CALENDAR_ID = CALENDARID) ;
SELECT SUM(WORKDAY_FLG) INTO WORKINGDAYS FROM RIP_CAL_FACTORY_CALENDAR
WHERE CALENDAR_DATE BETWEEN START_DT AND END_DT AND (CALENDAR_ID = CALENDARID) ;
END ;

The followng PL/SQL displays the values returned by the above code:

DECLARE
RET_TOTALDAYS NUMBER;
RET_WORKINGDAYS NUMBER;
BEGIN
SP_WEEKDAYS('06-JUN-08','05-DEC-08', 'H9', RET_TOTALDAYS, RET_WORKINGDAYS);
Dbms_Output.Put_line('Working Days = ' || RET_WORKINGDAYS || ' Total_days = ' || RET_TOTALDAYS ) ;
END ;