Oracle使用游标给Rscript传参

--创建R脚本

BEGIN
sys.rqScriptDrop('TEST_SCRIPT');
sys.rqScriptCreate('TEST_SCRIPT', 
'function(x, param)   #param这个参数是可选的
   {
        job_date <- as.numeric(as.matrix(x))
        resultOfAll <- data.frame(job_date=job_date,stringsAsFactors=F) 
        resultOfAll      
    }');
end;

--使用游标给R脚本传参

select * from table(rqTableEval(  
   cursor(select DATE from t_table),
   NULL,
   'select 1 job_date from dual t',
   'TEST_SCRIPT' ));

--指定参数进行传值

select * from table(rqTableEval(  
   cursor(select 20191225 FROM dual),
   NULL,
   'select 1 job_date from dual t',
   'TEST_SCRIPT' ));

实例：

--创建R脚本

BEGIN
  SYS.RQSCRIPTDROP('TEST_SCRIPT');
  SYS.RQSCRIPTCREATE('TEST_SCRIPT', --param是可选参数
                     'function(job_date, param) {
  library("ORE")
  library(ROracle)
  con <- dbConnect(Oracle(), "etl", "ETL")
    sql <- sprintf("select
     L_DATE,
     POSITIONNAME
     B_ANAL_CURVETERM from CONV_BOND_SS_MC_TMP t where  t.L_DATE = ''%s''",job_date)
  date2 <- dbGetQuery(con,sql)
  dbDisconnect(con)
  
  l_date <- date2[1]                        #l_date
  positionname <- date2[2]                    #positionname

  
  resultOfAll <- data.frame(L_DATE=l_date,POSITIONNAME=positionname)
  #resultOfAll$POSITIONNAME
  resultOfAll
 
}');
END;

--使用游标给R脚本传参

SELECT L_DATE,POSITIONNAME 
  FROM TABLE(rqTableEval(cursor(select 20191225 FROM dual),
                         NULL, 
                         'select cast(''a'' as varchar2(200)) L_DATE, 
                                 cast(''a'' as varchar2(200)) POSITIONNAME 
                            from dual','TEST_SCRIPT'));

--实现调用R脚本并传参，然后更新表

DECLARE
  V_SQL                 VARCHAR(2000);
  V_B1                  NUMBER(3) := 3;
  V_L_DATE              VARCHAR2(200);
  V_POSITIONNAME        VARCHAR2(200);
  V_CB_ANAL_STRBPREMIUM NUMBER(20, 10);
  V_MARKET_CAP          NUMBER(20, 10);
  CUR_STU               SYS_REFCURSOR;
  job_date              VARCHAR2(8):= '20191225';

BEGIN

  V_SQL := 'SELECT L_DATE,POSITIONNAME,CB_ANAL_STRBPREMIUM,MARKET_CAPITALIZA FROM TABLE(rqTableEval(cursor(select '||job_date||' FROM dual),NULL, ''select cast(''''a'''' as varchar2(200)) L_DATE, cast(''''a'''' as varchar2(200))  POSITIONNAME, 3 CB_ANAL_STRBPREMIUM, 4 MARKET_CAPITALIZA from dual'', ''TEST_SCRIPT''))';
  OPEN CUR_STU FOR V_SQL;
    --USING V_B1; -- 绑定变量
  LOOP
    FETCH CUR_STU
      INTO V_L_DATE, V_POSITIONNAME, V_CB_ANAL_STRBPREMIUM, V_MARKET_CAP;
    EXIT WHEN CUR_STU%NOTFOUND;
    dbms_output.put_line(V_L_DATE||','||V_POSITIONNAME||','||V_CB_ANAL_STRBPREMIUM||','||V_MARKET_CAP);
    UPDATE ETL.CONV_BOND_SS_MC T
       SET T.market_capitaliza = V_MARKET_CAP,t.cb_anal_strbpremium = V_CB_ANAL_STRBPREMIUM
     WHERE T.POSITIONNAME = V_POSITIONNAME
       AND T.L_DATE = V_L_DATE; 
   
                           
  END LOOP;

  CLOSE CUR_STU;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' : ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;

相关资料：

嵌入式R执行-SQL API.pdf