CREATE SCHEMA IF NOT EXISTS COMMON_PKG;
-- TYPE VARCHAR2_AARAY IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
DROP TYPE IF EXISTS COMMON_PKG.GL_VAR_TYPE CASCADE;
CREATE type COMMON_PKG.GL_VAR_TYPE
as(CUSTOMER_ID BIGINT,
ADDRES_LST VARCHAR(50)[]);
CREATE OR REPLACE FUNCTION COMMON_PKG.INIT_GL_VAR()
RETURNS VOID LANGUAGE plpgsql
AS $$
BEGIN
CREATE TEMPORARY TABLE COMMON_PKG_GL_VAR AS SELECT(array[]:: VARCHAR(50)[]):: COMMON_PKG.GL_VAR_TYPE AS SWV_GL_VAR_VAL;
RETURN;
EXCEPTION
WHEN SQLSTATE '42P07' THEN
NULL;
END; $$;
CREATE OR REPLACE FUNCTION COMMON_PKG.GET_GL_VAR()
RETURNS COMMON_PKG.GL_VAR_TYPE LANGUAGE plpgsql
AS $$
DECLARE
SWV_GL_VAR COMMON_PKG.GL_VAR_TYPE;
BEGIN
RETURN(select SWV_GL_VAR_VAL:: COMMON_PKG.GL_VAR_TYPE from COMMON_PKG_GL_VAR);
EXCEPTION
WHEN OTHERS THEN
PERFORM COMMON_PKG.INIT_GL_VAR();
RETURN(select SWV_GL_VAR_VAL:: COMMON_PKG.GL_VAR_TYPE from COMMON_PKG_GL_VAR);
END; $$;
CREATE OR REPLACE PROCEDURE COMMON_PKG.SET_GL_VAR(SWP_GLVAR COMMON_PKG.GL_VAR_TYPE)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE COMMON_PKG_GL_VAR SET SWV_GL_VAR_VAL = SWP_GLVAR;
END; $$;
CREATE OR REPLACE PROCEDURE COMMON_PKG.GET_CUSTID_BY_PARAMS(CITY_NAME VARCHAR,
CUST_ID_CD VARCHAR,
OUT CUST_ID NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
SWV_GL_VAR COMMON_PKG.GL_VAR_TYPE DEFAULT COMMON_PKG.GET_GL_VAR();
IN_ID BIGINT;
BEGIN
select CUST_ID into STRICT IN_ID
from CUSTOMER
where CITY = CITY_NAME and CUST_TYPE_CD = CUST_ID_CD LIMIT 1;
CUST_ID := IN_ID;
END; $$;
CREATE OR REPLACE PROCEDURE COMMON_PKG.PREPARE_CUSTOMER_ADRESSS_BY_ID(CITY_NAME VARCHAR, CUSTTYPE_ID VARCHAR)
LANGUAGE plpgsql
AS $$
DECLARE
SWV_GL_VAR COMMON_PKG.GL_VAR_TYPE DEFAULT COMMON_PKG.GET_GL_VAR();
CUR_ADDR VARCHAR(500);
CUSTM_ID NUMERIC;
v_int NUMERIC;
V_RES VARCHAR(4000)[] default array[]:: VARCHAR(4000)[];
l_clob TEXT;
CUS_ADDR cursor(cur_id NUMERIC) FOR select ADDRESS from CUSTOMER where cust_id = cur_id or cust_id < 5;
BEGIN
l_clob := '';
select CUST_ID into STRICT CUSTM_ID
from CUSTOMER
where CITY = CITY_NAME and CUST_TYPE_CD = CUSTTYPE_ID LIMIT 1;
open CUS_ADDR(CUSTM_ID);
fetch CUS_ADDR into CUR_ADDR;
WHILE FOUND
loop
l_clob := CONCAT(l_clob,CUR_ADDR,',');
fetch CUS_ADDR into CUR_ADDR;
end loop;
if EXISTS(SELECT 1 FROM pg_cursors WHERE NAME ilike 'CUS_ADDR') then
close CUS_ADDR;
end if;
RAISE NOTICE '%',NULLIF(rtrim(l_clob,','),'');
END; $$;
CREATE OR REPLACE PROCEDURE COMMON_PKG.GET_FIST_NAME_OF_EMPLOYEE_BY_START_DATE(STRT_DATE TIMESTAMP)
LANGUAGE plpgsql
AS $$
DECLARE
SWV_GL_VAR COMMON_PKG.GL_VAR_TYPE DEFAULT COMMON_PKG.GET_GL_VAR();
EMPLOYEE_REC EMPLOYEE%ROWTYPE;
CUR_START_DATE cursor FOR select * from EMPLOYEE;
BEGIN
open CUR_START_DATE;
fetch CUR_START_DATE into EMPLOYEE_REC;
WHILE FOUND
loop
<< loop_again >>
BEGIN
if EMPLOYEE_REC.START_DATE < STRT_DATE THEN
EXIT loop_again;
end if;
RAISE NOTICE '%',CONCAT(EMPLOYEE_REC.FIRST_NAME,' ',EMPLOYEE_REC.LAST_NAME);
END;
fetch CUR_START_DATE into EMPLOYEE_REC;
end loop;
if EXISTS(SELECT 1 FROM pg_cursors WHERE NAME ilike 'CUR_START_DATE') then
close CUR_START_DATE;
end if;
END; $$;
