下面是包头
CREATE OR REPLACE PACKAGE REPORTUSER.REPORT_CN_UA IS PROCEDURE SALES_BYGROUP (
V_REPORT_DATE_DRG IN VARCHAR2,
V_SBS_NO_LOK IN VARCHAR2,
V_STORE_NO_LOK IN VARCHAR2,
"REPORT_CURSOR" OUT SYS_REFCURSOR
);
END REPORT_CN_UA;下面是包体
CREATE OR REPLACE PACKAGE BODY REPORTUSER.REPORT_CN_UA IS FUNCTION COMMON_FILTERS
(
V_REPORT_DATE_DRG IN VARCHAR2,
DATE_COL IN VARCHAR2,
V_SBS_NO_LOK IN VARCHAR2,
SBS_COL IN VARCHAR2,
V_STORE_NO_LOK IN VARCHAR2,
STORE_COL IN VARCHAR2
)
RETURN LONG
AS
TMPSTRSQL LONG ;
BEGIN
IF TRIM(V_REPORT_DATE_DRG) IS NOT NULL THEN
TMPSTRSQL := TMPSTRSQL || ' AND ( ' || PKG_PARSER.GETRANGESHORTDATESTR(V_REPORT_DATE_DRG, DATE_COL, DATE_COL) || ')';
END IF;
IF TRIM(V_SBS_NO_LOK) IS NOT NULL THEN
TMPSTRSQL := TMPSTRSQL || ' AND ' || SBS_COL || ' IN ' || V_SBS_NO_LOK;
END IF;
IF TRIM(V_STORE_NO_LOK) IS NOT NULL THEN
TMPSTRSQL := TMPSTRSQL || ' AND (' || SBS_COL || ',' || STORE_COL || ') IN ' || V_STORE_NO_LOK;
END IF;
RETURN TMPSTRSQL;
END COMMON_FILTERS;PROCEDURE SALES_BYGROUP (
V_REPORT_DATE_DRG IN VARCHAR2,
V_SBS_NO_LOK IN VARCHAR2,
V_STORE_NO_LOK IN VARCHAR2,
"REPORT_CURSOR" OUT SYS_REFCURSOR
) IS TMPSTRSQL CLOB;
ARRDATE TOKENTABLETYPE;
DATEFROM DATE;
DATETILL DATE;
DATEFROMSTR VARCHAR2(100);
DATETILLSTR VARCHAR2(100);
BEGIN
IF TRIM(V_REPORT_DATE_DRG) IS NOT NULL THEN
ARRDATE := PKG_PARSER.GETTOKEN(V_REPORT_DATE_DRG,',');
IF ARRDATE.LAST = 3 THEN
DATEFROM := TO_DATE(TRIM('''' FROM ARRDATE(2)),PKG_PARSER.SHORT_DATE_FORMATE);
DATETILL := TO_DATE(TRIM('''' FROM ARRDATE(3)),PKG_PARSER.SHORT_DATE_FORMATE);
DATEFROMSTR := ARRDATE(2);
DATETILLSTR := ARRDATE(3);
ELSE
DATEFROM := TRUNC(SYSDATE());
DATETILL := TRUNC (SYSDATE());
DATEFROMSTR := '''' || TO_CHAR(SYSDATE(), PKG_PARSER.SHORT_DATE_FORMATE) || '''';
DATETILLSTR := '''' || TO_CHAR(SYSDATE(), PKG_PARSER.SHORT_DATE_FORMATE) || '''';
END IF;
ELSE
DATEFROM := TRUNC(SYSDATE());
DATETILL := TRUNC (SYSDATE());
DATEFROMSTR := '''' || TO_CHAR(SYSDATE(), PKG_PARSER.SHORT_DATE_FORMATE) || '''';
DATETILLSTR := '''' || TO_CHAR(SYSDATE(), PKG_PARSER.SHORT_DATE_FORMATE) || '''';
END IF; TMPSTRSQL:='
SELECT ';
IF TRIM(V_REPORT_DATE_DRG) IS NOT NULL THEN
TMPSTRSQL := TMPSTRSQL || DATEFROMSTR || ' AS START_DATE, ';
TMPSTRSQL := TMPSTRSQL || DATETILLSTR || ' AS END_DATE, ';
END IF;
TMPSTRSQL := TMPSTRSQL ||
'
INVC.SBS_NO AS PARTNER,
STO.STORE_NAME AS STORE_NAME,
STO.UDF1_VALUE AS STORE_CODE,
CASE WHEN STO.PRICE_LVL = 1 THEN ''BH'' ELSE ''FH'' END as STORE_TYPE,
STO.ADDRESS6 AS CITY,
STO.TOTAL_SURFACE AS SQM,
SUM(CASE WHEN INVC.INVC_TYPE=2 THEN ITEM.QTY * -1 ELSE ITEM.QTY END) AS UNITS,
SUM(ITEM.PRICE * ITEM.QTY * CASE WHEN INVC.INVC_TYPE=2 THEN -1 ELSE 1 END) AS SALES,
SUM(ITEM.ORIG_PRICE * ITEM.QTY * CASE WHEN INVC.INVC_TYPE=2 THEN -1 ELSE 1 END) AS ORIG_SALES,
STO.GLOB_STORE_CODE AS STORE_CODE1,
DECODE(SUBSTR(DCS_CODE,0,3),''101'',''App'',''102'',''Footware'',''104'',''Acc'') AS GROUP_TYPE
FROM INVOICE_V INVC, INVC_ITEM ITEM, STORE_V STO, INVENTORY_V INVEN
WHERE
INVC.INVC_SID=ITEM.INVC_SID
AND INVC.STORE_NO = STO.STORE_NO AND INVC.SBS_NO = STO.SBS_NO
AND INVEN.ITEM_SID=ITEM.ITEM_SID AND INVEN.SBS_NO=INVC.SBS_NO
AND INVC.HISEC_TYPE IS NULL
AND INVC.STATUS2 = 0 ';
TMPSTRSQL := TMPSTRSQL || COMMON_FILTERS(V_REPORT_DATE_DRG, 'TRUNC(INVC.CREATED_DATE)', V_SBS_NO_LOK, 'INVC.SBS_NO', V_STORE_NO_LOK, 'INVC.STORE_NO');
TMPSTRSQL := TMPSTRSQL || '
GROUP BY
INVC.SBS_NO,
STO.STORE_NAME,
STO.UDF1_VALUE,
CASE WHEN STO.PRICE_LVL = 1 THEN ''BH'' ELSE ''FH'' END,
STO.ADDRESS6,
STO.TOTAL_SURFACE,
STO.GLOB_STORE_CODE,
DECODE(SUBSTR(DCS_CODE,0,3),''101'',''App'',''102'',''Footware'',''104'',''Acc'')
ORDER BY INVC.SBS_NO, STO.STORE_NAME
';
OPEN "REPORT_CURSOR" FOR
TMPSTRSQL;
END SALES_BYGROUP;END REPORT_CN_UA;
我该怎么调用我这个函数,我也试了我的调用,可以不行,你们能写个这个怎么调用的sql语句么?附上我写的调用sqlDECLARE
"REPORT_CURSOR" SYS_REFCURSOR;
V_REPORT_DATE_DRG varchar2(10);
V_SBS_NO_LOK varchar2(100);
V_STORE_NO_LOK varchar2(100);
begin
V_REPORT_DATE_DRG := TO_CHAR(sysdate, 'MM/DD/YYYY');
dbms_output.put_line(V_REPORT_DATE_DRG);
V_SBS_NO_LOK := '-1';
V_STORE_NO_LOK := '0';
-- Call the procedure
REPORT_CN_UA.SALES_BYGROUP(V_REPORT_DATE_DRG,V_SBS_NO_LOK,V_STORE_NO_LOK,"REPORT_CURSOR");
if REPORT_CURSOR%isopen then
dbms_output.put_line('deptlist opened');
close REPORT_CURSOR;
end if;
end;
CREATE OR REPLACE PACKAGE REPORTUSER.REPORT_CN_UA IS PROCEDURE SALES_BYGROUP (
V_REPORT_DATE_DRG IN VARCHAR2,
V_SBS_NO_LOK IN VARCHAR2,
V_STORE_NO_LOK IN VARCHAR2,
"REPORT_CURSOR" OUT SYS_REFCURSOR
);
END REPORT_CN_UA;下面是包体
CREATE OR REPLACE PACKAGE BODY REPORTUSER.REPORT_CN_UA IS FUNCTION COMMON_FILTERS
(
V_REPORT_DATE_DRG IN VARCHAR2,
DATE_COL IN VARCHAR2,
V_SBS_NO_LOK IN VARCHAR2,
SBS_COL IN VARCHAR2,
V_STORE_NO_LOK IN VARCHAR2,
STORE_COL IN VARCHAR2
)
RETURN LONG
AS
TMPSTRSQL LONG ;
BEGIN
IF TRIM(V_REPORT_DATE_DRG) IS NOT NULL THEN
TMPSTRSQL := TMPSTRSQL || ' AND ( ' || PKG_PARSER.GETRANGESHORTDATESTR(V_REPORT_DATE_DRG, DATE_COL, DATE_COL) || ')';
END IF;
IF TRIM(V_SBS_NO_LOK) IS NOT NULL THEN
TMPSTRSQL := TMPSTRSQL || ' AND ' || SBS_COL || ' IN ' || V_SBS_NO_LOK;
END IF;
IF TRIM(V_STORE_NO_LOK) IS NOT NULL THEN
TMPSTRSQL := TMPSTRSQL || ' AND (' || SBS_COL || ',' || STORE_COL || ') IN ' || V_STORE_NO_LOK;
END IF;
RETURN TMPSTRSQL;
END COMMON_FILTERS;PROCEDURE SALES_BYGROUP (
V_REPORT_DATE_DRG IN VARCHAR2,
V_SBS_NO_LOK IN VARCHAR2,
V_STORE_NO_LOK IN VARCHAR2,
"REPORT_CURSOR" OUT SYS_REFCURSOR
) IS TMPSTRSQL CLOB;
ARRDATE TOKENTABLETYPE;
DATEFROM DATE;
DATETILL DATE;
DATEFROMSTR VARCHAR2(100);
DATETILLSTR VARCHAR2(100);
BEGIN
IF TRIM(V_REPORT_DATE_DRG) IS NOT NULL THEN
ARRDATE := PKG_PARSER.GETTOKEN(V_REPORT_DATE_DRG,',');
IF ARRDATE.LAST = 3 THEN
DATEFROM := TO_DATE(TRIM('''' FROM ARRDATE(2)),PKG_PARSER.SHORT_DATE_FORMATE);
DATETILL := TO_DATE(TRIM('''' FROM ARRDATE(3)),PKG_PARSER.SHORT_DATE_FORMATE);
DATEFROMSTR := ARRDATE(2);
DATETILLSTR := ARRDATE(3);
ELSE
DATEFROM := TRUNC(SYSDATE());
DATETILL := TRUNC (SYSDATE());
DATEFROMSTR := '''' || TO_CHAR(SYSDATE(), PKG_PARSER.SHORT_DATE_FORMATE) || '''';
DATETILLSTR := '''' || TO_CHAR(SYSDATE(), PKG_PARSER.SHORT_DATE_FORMATE) || '''';
END IF;
ELSE
DATEFROM := TRUNC(SYSDATE());
DATETILL := TRUNC (SYSDATE());
DATEFROMSTR := '''' || TO_CHAR(SYSDATE(), PKG_PARSER.SHORT_DATE_FORMATE) || '''';
DATETILLSTR := '''' || TO_CHAR(SYSDATE(), PKG_PARSER.SHORT_DATE_FORMATE) || '''';
END IF; TMPSTRSQL:='
SELECT ';
IF TRIM(V_REPORT_DATE_DRG) IS NOT NULL THEN
TMPSTRSQL := TMPSTRSQL || DATEFROMSTR || ' AS START_DATE, ';
TMPSTRSQL := TMPSTRSQL || DATETILLSTR || ' AS END_DATE, ';
END IF;
TMPSTRSQL := TMPSTRSQL ||
'
INVC.SBS_NO AS PARTNER,
STO.STORE_NAME AS STORE_NAME,
STO.UDF1_VALUE AS STORE_CODE,
CASE WHEN STO.PRICE_LVL = 1 THEN ''BH'' ELSE ''FH'' END as STORE_TYPE,
STO.ADDRESS6 AS CITY,
STO.TOTAL_SURFACE AS SQM,
SUM(CASE WHEN INVC.INVC_TYPE=2 THEN ITEM.QTY * -1 ELSE ITEM.QTY END) AS UNITS,
SUM(ITEM.PRICE * ITEM.QTY * CASE WHEN INVC.INVC_TYPE=2 THEN -1 ELSE 1 END) AS SALES,
SUM(ITEM.ORIG_PRICE * ITEM.QTY * CASE WHEN INVC.INVC_TYPE=2 THEN -1 ELSE 1 END) AS ORIG_SALES,
STO.GLOB_STORE_CODE AS STORE_CODE1,
DECODE(SUBSTR(DCS_CODE,0,3),''101'',''App'',''102'',''Footware'',''104'',''Acc'') AS GROUP_TYPE
FROM INVOICE_V INVC, INVC_ITEM ITEM, STORE_V STO, INVENTORY_V INVEN
WHERE
INVC.INVC_SID=ITEM.INVC_SID
AND INVC.STORE_NO = STO.STORE_NO AND INVC.SBS_NO = STO.SBS_NO
AND INVEN.ITEM_SID=ITEM.ITEM_SID AND INVEN.SBS_NO=INVC.SBS_NO
AND INVC.HISEC_TYPE IS NULL
AND INVC.STATUS2 = 0 ';
TMPSTRSQL := TMPSTRSQL || COMMON_FILTERS(V_REPORT_DATE_DRG, 'TRUNC(INVC.CREATED_DATE)', V_SBS_NO_LOK, 'INVC.SBS_NO', V_STORE_NO_LOK, 'INVC.STORE_NO');
TMPSTRSQL := TMPSTRSQL || '
GROUP BY
INVC.SBS_NO,
STO.STORE_NAME,
STO.UDF1_VALUE,
CASE WHEN STO.PRICE_LVL = 1 THEN ''BH'' ELSE ''FH'' END,
STO.ADDRESS6,
STO.TOTAL_SURFACE,
STO.GLOB_STORE_CODE,
DECODE(SUBSTR(DCS_CODE,0,3),''101'',''App'',''102'',''Footware'',''104'',''Acc'')
ORDER BY INVC.SBS_NO, STO.STORE_NAME
';
OPEN "REPORT_CURSOR" FOR
TMPSTRSQL;
END SALES_BYGROUP;END REPORT_CN_UA;
我该怎么调用我这个函数,我也试了我的调用,可以不行,你们能写个这个怎么调用的sql语句么?附上我写的调用sqlDECLARE
"REPORT_CURSOR" SYS_REFCURSOR;
V_REPORT_DATE_DRG varchar2(10);
V_SBS_NO_LOK varchar2(100);
V_STORE_NO_LOK varchar2(100);
begin
V_REPORT_DATE_DRG := TO_CHAR(sysdate, 'MM/DD/YYYY');
dbms_output.put_line(V_REPORT_DATE_DRG);
V_SBS_NO_LOK := '-1';
V_STORE_NO_LOK := '0';
-- Call the procedure
REPORT_CN_UA.SALES_BYGROUP(V_REPORT_DATE_DRG,V_SBS_NO_LOK,V_STORE_NO_LOK,"REPORT_CURSOR");
if REPORT_CURSOR%isopen then
dbms_output.put_line('deptlist opened');
close REPORT_CURSOR;
end if;
end;
FROM INVOICE_V INVC, INVC_ITEM ITEM, STORE_V STO, INVENTORY_V INVEN