-
-
Save balidani/81cf5fe6f777c418d4f0 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
PACKAGE BODY shop_pkg AS | |
FUNCTION GET_CITY_BRANCHES | |
( | |
P_CITY SHOPS.CITY%TYPE | |
) | |
RETURN SYS_REFCURSOR | |
AS | |
L_QUERY VARCHAR2(32767); | |
C_SHOPS SYS_REFCURSOR; | |
BEGIN | |
L_QUERY := 'select ''Zip code '' || s.zip_code || ''<br />'' || | |
''Subway '' || s.subway || ''<br />'' || | |
''Address '' || s. street || '' '' || s.house || ''<br />'' || | |
''Tel. '' || ''+7'' || '' ('' || s.phone_code || '') '' || | |
regexp_substr(s.phone_number, ''(\d{3})(\d{2})(\d{2})'', 1, 1, ''i'', 1) || ''-'' || | |
regexp_substr(s.phone_number, ''(\d{3})(\d{2})(\d{2})'', 1, 1, ''i'', 2) || ''-'' || | |
regexp_substr(s.phone_number, ''(\d{3})(\d{2})(\d{2})'', 1, 1, ''i'', 3) || ''<br />'' || | |
''Timesheet '' || s.work_time_from || ''-'' || s.work_time_to || '' h'' as address | |
from shops s | |
where 1 = 1 | |
and sysdate between nvl(start_date, sysdate - 1) and nvl(end_date, sysdate + 1) | |
and city = ''' || P_CITY || ''''; | |
DBMS_OUTPUT.PUT_LINE(L_QUERY); | |
OPEN C_SHOPS FOR L_QUERY; | |
RETURN C_SHOPS; | |
EXCEPTION | |
WHEN NO_DATA_FOUND THEN | |
RAISE_APPLICATION_ERROR(EX, 'There are no active branches for the city :('); | |
WHEN OTHERS THEN | |
RAISE_APPLICATION_ERROR(EX, SQLERRM); | |
END GET_CITY_BRANCHES; | |
FUNCTION GET_BRANCH_INFO | |
( | |
P_BRANCH_ID SHOPS.ID%TYPE | |
) | |
RETURN VARCHAR2 | |
AS | |
L_BRANCH VARCHAR2(32767); | |
BEGIN | |
SELECT 'Zip code ' || S.ZIP_CODE || '<br />' || | |
'Metro ' || S.SUBWAY || '<br />' || | |
'Address ' || S. STREET || ' ' || S.HOUSE || '<br />' || | |
'Tel. ' || '+7' || '(' || S.PHONE_CODE || ')' || | |
REGEXP_SUBSTR(S.PHONE_NUMBER, '(\d{3})(\d{2})(\d{2})', 1, 1, 'i', 1) || '-' || | |
REGEXP_SUBSTR(S.PHONE_NUMBER, '(\d{3})(\d{2})(\d{2})', 1, 1, 'i', 2) || '-' || | |
REGEXP_SUBSTR(S.PHONE_NUMBER, '(\d{3})(\d{2})(\d{2})', 1, 1, 'i', 3) || '<br />' || | |
'Timesheet ' || S.WORK_TIME_FROM || '-' || S.WORK_TIME_TO AS ADDRESS | |
INTO L_BRANCH | |
FROM SHOPS S | |
WHERE 1 = 1 | |
AND S.ID = P_BRANCH_ID | |
ORDER BY 1; | |
RETURN L_BRANCH; | |
EXCEPTION | |
WHEN NO_DATA_FOUND THEN | |
RAISE_APPLICATION_ERROR(EX, 'There is no active branch with id ' || P_BRANCH_ID || ' :('); | |
WHEN OTHERS THEN | |
RAISE_APPLICATION_ERROR(EX, SQLERRM); | |
END; | |
END SHOP_PKG; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment