Instantly share code, notes, and snippets.

@balidani /shop_pkg.sql Secret
Created Jan 27, 2014

Embed
What would you like to do?
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 &nbsp;&nbsp;&nbsp;'' || s.zip_code || ''<br />'' ||
''Subway &nbsp;&nbsp;&nbsp;&nbsp;'' || s.subway || ''<br />'' ||
''Address &nbsp;&nbsp;&nbsp;&nbsp;'' || s. street || '' '' || s.house || ''<br />'' ||
''Tel. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'' || ''+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 &nbsp;'' || 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 &nbsp;&nbsp;&nbsp;' || S.ZIP_CODE || '<br />' ||
'Metro &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' || S.SUBWAY || '<br />' ||
'Address &nbsp;&nbsp;&nbsp;&nbsp;' || S. STREET || ' ' || S.HOUSE || '<br />' ||
'Tel. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' || '+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 &nbsp;' || 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