--创建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;
相关资料: