oracle存储过程权限不足

最新推荐文章于 2024-07-07 23:27:24 发布

小码蚁啊

最新推荐文章于 2024-07-07 23:27:24 发布

阅读量1.3k

点赞数

分类专栏：其他

本文链接：https://blog.csdn.net/qq_36802726/article/details/90754478

版权

其他专栏收录该内容

8 篇文章 0 订阅

订阅专栏

create or replace procedure RYTJ(year in varchar2,orgCode in varchar2,data out sys_refcursor)

begin

  open data for  
            SELECT
        C.RY_XZJB_NAME,
        RY_XZJB_COUNT,
        SUM( CASE WHEN D.RY_ID IS NOT NULL THEN 1 ELSE 0 END ) RY_ZG,
        SUM( CASE WHEN RY_XL_CODE = '01' THEN 1 ELSE 0 END ) RY_XL_BS,
        SUM( CASE WHEN RY_XL_CODE = '02' THEN 1 ELSE 0 END ) RY_XL_SS,
        SUM( CASE WHEN RY_XL_CODE = '03' THEN 1 ELSE 0 END ) RY_XL_BK,
        SUM( CASE WHEN RY_XL_CODE = '04' THEN 1 ELSE 0 END ) RY_XL_DZ,
        SUM( CASE WHEN RY_XL_CODE = '05' THEN 1 ELSE 0 END ) RY_XL_ZZ,
        SUM( CASE WHEN RY_XL_CODE = '06' THEN 1 ELSE 0 END ) RY_XL_JX,
        SUM( CASE WHEN RY_XL_CODE = '07' OR RY_XL_CODE = '08' OR RY_XL_CODE = '09' OR RY_XL_CODE = '10' THEN 1 ELSE 0
        END ) RY_XL_GZ,
        SUM( CASE WHEN ( SYSDATE - RY_BIRTH ) / 365 <35 THEN 1 ELSE 0 END ) RY_AGE_35,
        SUM( CASE WHEN ( SYSDATE - RY_BIRTH ) / 365 >= 35 AND ( SYSDATE - RY_BIRTH ) / 365 < 55 THEN 1 ELSE 0 END )
        RY_AGE_45,
        SUM( CASE WHEN ( SYSDATE - RY_BIRTH ) / 365 >= 55 AND ( SYSDATE - RY_BIRTH ) / 365 < 60 THEN 1 ELSE 0 END )
        RY_AGE_55,
        SUM( CASE WHEN ( SYSDATE - RY_BIRTH ) / 365 >= 60 AND ( SYSDATE - RY_BIRTH ) / 365 < 61 THEN 1 ELSE 0 END )
        RY_AGE_60,
        SUM( CASE WHEN ( SYSDATE - RY_BIRTH ) / 365 >= 61 THEN 1 ELSE 0 END ) RY_AGE_61,
        SUM( CASE WHEN RY_ZC_CODE = '01' AND RY_ZCXL_CODE = '01' THEN 1 ELSE 0 END ) RY_GC_ZG,
        SUM( CASE WHEN RY_ZC_CODE = '02' AND RY_ZCXL_CODE = '01' THEN 1 ELSE 0 END ) RY_GC_GJ,
        SUM( CASE WHEN RY_ZC_CODE = '03' AND RY_ZCXL_CODE = '01' THEN 1 ELSE 0 END ) RY_GC_ZJ,
        SUM( CASE WHEN RY_ZC_CODE = '04' AND RY_ZCXL_CODE = '01' THEN 1 ELSE 0 END ) RY_GC_CJ,
        SUM( CASE WHEN RY_ZC_CODE = '01' AND RY_ZCXL_CODE = '02' THEN 1 ELSE 0 END ) RY_JJ_ZG,
        SUM( CASE WHEN RY_ZC_CODE = '02' AND RY_ZCXL_CODE = '02' THEN 1 ELSE 0 END ) RY_JJ_GJ,
        SUM( CASE WHEN RY_ZC_CODE = '03' AND RY_ZCXL_CODE = '02' THEN 1 ELSE 0 END ) RY_JJ_ZJ,
        SUM( CASE WHEN RY_ZC_CODE = '04' AND RY_ZCXL_CODE = '02' THEN 1 ELSE 0 END ) RY_JJ_CJ,
        SUM( CASE WHEN RY_ZC_CODE = '01' AND RY_ZCXL_CODE = '03' THEN 1 ELSE 0 END ) RY_CH_ZG,
        SUM( CASE WHEN RY_ZC_CODE = '02' AND RY_ZCXL_CODE = '03' THEN 1 ELSE 0 END ) RY_CH_GJ,
        SUM( CASE WHEN RY_ZC_CODE = '03' AND RY_ZCXL_CODE = '03' THEN 1 ELSE 0 END ) RY_CH_ZJ,
        SUM( CASE WHEN RY_ZC_CODE = '04' AND RY_ZCXL_CODE = '03' THEN 1 ELSE 0 END ) RY_CH_CJ,
        SUM( CASE WHEN RY_ZC_CODE = '01' AND RY_ZCXL_CODE = '04' THEN 1 ELSE 0 END ) RY_QT_ZG,
        SUM( CASE WHEN RY_ZC_CODE = '02' AND RY_ZCXL_CODE = '04' THEN 1 ELSE 0 END ) RY_QT_GJ,
        SUM( CASE WHEN RY_ZC_CODE = '03' AND RY_ZCXL_CODE = '04' THEN 1 ELSE 0 END ) RY_QT_ZJ,
        SUM( CASE WHEN RY_ZC_CODE = '04' AND RY_ZCXL_CODE = '04' THEN 1 ELSE 0 END ) RY_QT_CJ,
        SUM( CASE WHEN RY_SEX = '女' THEN 1 ELSE 0 END ) RY_NV,
        SUM( CASE WHEN RY_ZW_CODE = '07' THEN 1 ELSE 0 END ) RY_JC
        FROM
        (SELECT F_NAME RY_XZJB_NAME,RY_XZJB_COUNT FROM YDGL.T_ZHGL_RYDY A
        RIGHT JOIN ( SELECT * FROM YDZD.T_BASE_DIC WHERE F_TYPE = 'XZJB' AND F_CODE != '00' AND F_YEAR = year ) B ON
        A.RY_XZJB_CODE = B.F_CODE
        AND A.YEAR =year ) C
        LEFT JOIN YDGL.T_ZHGL_RYINFO D ON C.RY_XZJB_NAME = D.RY_XZJB_NAMEA AND D.RY_XZ_CODE ='02'
      
            and
         
            D.ORG_CODE like '%orgCode%'
        
        GROUP BY
        C.RY_XZJB_NAME,
        RY_XZJB_COUNT;

end RYTJ;

然后报错权限不足，百度说加上Authid Current_User但是没作用，最后看了这个文章解决https://blog.csdn.net/qq_34424343/article/details/79052819