CREATE OR REPLACE PACKAGE P_VIEW_PARAM_FXJK IS
--机构代码
FUNCTION SET_ORG_ID(ORGID VARCHAR2) RETURN VARCHAR2;
FUNCTION GET_ORG_ID RETURN VARCHAR2;
--产品编号
FUNCTION SET_PRODUCT_ID(PRODUCTID VARCHAR2) RETURN VARCHAR2;
FUNCTION GET_PRODUCT_ID RETURN VARCHAR2;
END P_VIEW_PARAM_FXJK;
使用函数创建参数,设置查询条件
创建视图如下:
SELECT
P2.PRODUCT_ID /*产品编号*/
,P2.PROD_NAME /*销售产品*/
,P6.ORG_ID /*预约机构*/
,SUM(P1.BUY_MONEY/10000) AS RESERVE_MONEY /*预约总金额(万元)*/
FROM
OCRM_F_PD_SALE_RESE P1
INNER JOIN
OCRM_F_PD_PROD_INFO P2
ON P1.PRODUCT_ID = P2.PRODUCT_ID
AND P2.PRODUCT_ID = P_VIEW_PARAM_FXJK.GET_PRODUCT_ID
INNER JOIN
(SELECT ORG_ID,ORG_NAME FROM ADMIN_AUTH_ORG
START WITH ORG_ID = P_VIEW_PARAM_FXJK.GET_ORG_ID
CONNECT BY PRIOR ORG_ID = UP_ORG_ID) P6
ON P6.ORG_ID = P1.ORG_ID
WHERE P1.RESERVE_STATUS IN ('1','2')
GROUP BY GROUPING SETS((p6.org_id,P2.PRODUCT_ID,P2.PROD_NAME,) ,P2.PROD_NAME));查询视图,并传入参数时,查询没有结果,但是如果视图仅使用group by是可以查询到数据的
SELECT *
FROM V_C_PD_SALE_FXJK T
WHERE P_VIEW_PARAM_FXJK.SET_PRODUCT_ID('ZH0809') = 'ZH0809'
AND P_VIEW_PARAM_FXJK.SET_ORG_ID('0_000000') = '0_000000'
--机构代码
FUNCTION SET_ORG_ID(ORGID VARCHAR2) RETURN VARCHAR2;
FUNCTION GET_ORG_ID RETURN VARCHAR2;
--产品编号
FUNCTION SET_PRODUCT_ID(PRODUCTID VARCHAR2) RETURN VARCHAR2;
FUNCTION GET_PRODUCT_ID RETURN VARCHAR2;
END P_VIEW_PARAM_FXJK;
使用函数创建参数,设置查询条件
创建视图如下:
SELECT
P2.PRODUCT_ID /*产品编号*/
,P2.PROD_NAME /*销售产品*/
,P6.ORG_ID /*预约机构*/
,SUM(P1.BUY_MONEY/10000) AS RESERVE_MONEY /*预约总金额(万元)*/
FROM
OCRM_F_PD_SALE_RESE P1
INNER JOIN
OCRM_F_PD_PROD_INFO P2
ON P1.PRODUCT_ID = P2.PRODUCT_ID
AND P2.PRODUCT_ID = P_VIEW_PARAM_FXJK.GET_PRODUCT_ID
INNER JOIN
(SELECT ORG_ID,ORG_NAME FROM ADMIN_AUTH_ORG
START WITH ORG_ID = P_VIEW_PARAM_FXJK.GET_ORG_ID
CONNECT BY PRIOR ORG_ID = UP_ORG_ID) P6
ON P6.ORG_ID = P1.ORG_ID
WHERE P1.RESERVE_STATUS IN ('1','2')
GROUP BY GROUPING SETS((p6.org_id,P2.PRODUCT_ID,P2.PROD_NAME,) ,P2.PROD_NAME));查询视图,并传入参数时,查询没有结果,但是如果视图仅使用group by是可以查询到数据的
SELECT *
FROM V_C_PD_SALE_FXJK T
WHERE P_VIEW_PARAM_FXJK.SET_PRODUCT_ID('ZH0809') = 'ZH0809'
AND P_VIEW_PARAM_FXJK.SET_ORG_ID('0_000000') = '0_000000'
FROM V_C_PD_SALE_FXJK T有结果吗?