oracle存储过程权限不足

╰+哭是因爲堅強的太久メ 2022-01-23 10:29 309阅读 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][https_blog.csdn.net_qq_34424343_article_details_79052819]

[https_blog.csdn.net_qq_34424343_article_details_79052819]: https://blog.csdn.net/qq_34424343/article/details/79052819