代码之家  ›  专栏  ›  技术社区  ›  Shekhar Nalawade

从Excel VBA调用Oracle PL SQL函数

plsql excel-vba oracle vba excel
  •  2
  • Shekhar Nalawade  · 技术社区  · 7 年前

    我试图从Excel VBA调用pl-sql函数。当我在OracleSQL开发人员上执行它时,它运行得很好。但当我尝试从Excel VBA运行相同的程序时,会出现以下错误。

    请看下面的代码段。 

    对象变暗
将RS变暗为对象
将命令变暗为对象
暗淡的尖叫声
设置con=createObject(“adodb.connection”)。
设置rs=createObject(“adodb.recordset”)。
set cmd=createObject(“adodb.command”)。

strcon=“provider=oraoledb.oracle;data source=10.1.2.238:1521/oracle.multiacttrade.local;user id=;password=;persistent security info=true”
开放式斯特朗

squery=“select*from table(ma_dwm_avg(to_char('l48d88-s-in'),to_number(2),to_number(11),to_number(40))”
cmd.activeConnection=连接
cmd.commandText=尖叫
设置rs=cmd.execute()

工作表(“Sheet1”)。单元格(2,1)。复制自记录集rs

RS关闭
结束子
< /代码> 


    即使使用了如下的squery,错误仍然存在。变量数据类型已经得到很好的处理。没有类型不匹配。
    


    select*from table(ma_dwm_avg('l48d88-s-in',2,11,40))
< /代码> 


    下面是我面临错误的功能代码。函数代码非常大,因此只给出所需的代码片段。
    


    创建或替换函数ma_dwm_avg(fid in varchar,choice int,period1 int,period2 int)
返回温度嵌套为

--为我们创建的函数创建一个单独的转换。
pragma自治事务;
嵌套的温度;

开始
--截断函数中使用的GTT表
立即执行'truncate table gtt_dwm_stats';
--如果需要详细的统计数据,则choice=1
--我们计算行数、fs-perm-sec-id、日期、2个期间的收盘价、开盘价、当日高价、当日低价。

--如果choice=2,则计算每周统计数据。
如果choice=2和period1<gt;0和period2<gt;0,则
--首先,我们计算两个期间的行数、fs-perm-sec-id、周末日期、收盘价及其平均值。


插入到gtt_dwm_stats(行数、fid、关闭日期、价格关闭、移动平均时间段1、移动平均时间段2)
选择rownum,fs perm,sec,id,“日期”,pprice,
案例
当计数(pprice)结束(按“日期”排序)时,请在后面描述当前行和Period1-1之间的行)>=Period1
然后是AVG(pprice)over(按“日期”排序,描述当前行和后面1-1个周期之间的行)
否则为零
结束为“移动平均周期1”,
案例
当计数(pprice)结束(按“日期”排序)时,请在后面描述当前行和Period2-1之间的行)>=Period2
然后是AVG(pprice)over(按“日期”排序,描述当前行和后面2-1个期间之间的行)
否则为零
以“移动平均周期2”结束
从(
选择fs perm_sec_id,“日期”,p_price作为pprice,p_volume,
案例
当(to_char(“日期”,'d')>=avg(to_char(“日期”,'d”))超过(按“日期”排序,描述前一行和当前行之间的行)和rownum>=1时
或to_char(“日期”,'d')=6
然后1
其他0
作为周旗结束
从fp_basic_bd开始,其中fs_perm_sec_id=fid和p_volume<>0按“日期”描述排序),其中weekflag=1;
--获取周开始日期
--89号线到了
更新gtt-dwm-u统计
设置开始日期=
(从fp_basic_bd中选择“日期”,其中fp_basic_bd.fs_perm_sec_id=fid和fp_basic_bd。“日期”>=到\u char(trunc(to_date(gtt_dwm_stats.closingdate,'dd-mon-yy')、'iw')、'dd-mon-yy')和p_volume<>0和rownum=1)
存在的地方(从fp_basic_bd中选择fp_basic_bd.“日期”,其中fp_basic_bd.“日期”=gtt_dwm_stats.closingdate);
--获取本周开盘价
更新gtt-dwm-u统计
开盘价=
(选择fp_basic_bd.p_price_open from fp_basic_bd,其中fp_basic_bd.fs_perm_sec_id=gtt_dwm_stats.fid和fp_basic_bd.“日期”=gtt_dwm_stats.startdate);
--在一周的时间内获得很高的价格
更新gtt-dwm-u统计
设置价格=高=
(从fp_basic_bd中选择max(p_price_high),其中fp_basic_bd.fs_perm_sec_id=fid和fp_basic_bd.date介于gtt_dwm_stats.startdate和gtt_dwm_stats.closingdate之间)
存在的地方(从fp_basic_bd中选择p_price_high,其中fp_basic_bd.fs_perm_sec_id=gtt_dwm_stats.fid);
--在一周内获得低价
更新gtt-dwm-u统计
设定价格
(从fp_basic_bd中选择Min(P_price_low),其中fp_basic_bd.fs_perm_sec_id=fid,以及gtt_dwm_stats.startdate和gtt_dwm_stats.closingdate之间的fp_basic_bd和fp_basic_bd.fs_perm_sec_id=gtt_dwm_stats.fid)存在(从fp_basic_bd中选择P_price_low);


如果结束;


--将gtt值获取到从对象类型派生的表中并返回它。
选择
演员(
多重集

从gtt_dwm_stats中选择*,其中gtt_dwm_stats.fid=fid order by closingdate desc

)当温度从双通道嵌套到V通道时;

承诺;
返回VYRET;

结束;
< /代码> 


    还可以参考DDL获取全局临时表,如下所示。
    


    create global temporary table“ma_factset”.“gtt_dwm_stats”
(“RowNumber”编号(*0),
“fid”varchar2(20字节)
“关闭日期”日期,
“价格关闭”浮动(126)
“移动平均周期1”浮动(126)
“移动平均周期2”浮动(126)
“开始日期”日期,
“价格开放”浮动(126)
“价格高”浮动(126)
“价格低”浮动(126)
)提交时保留行;
< /代码> 


    请不要建议我将函数用作过程。因为函数正在使用一个全局临时表,最后返回完整的表。因此,将其作为函数强制使用。


    enter image description here
    


    请看下面的代码段。
    


    Private Sub CommandButton1_Click()

    Dim con As Object
    Dim rs As Object
    Dim cmd As Object
    Dim sQuery As String
    Set con = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set cmd = CreateObject("ADODB.Command")

    strcon = "Provider=OraOledb.Oracle;Data Source=10.1.2.238:1521/oracle.MultiActTrade.LOCAL; User ID=; Password=;Persist Security Info=True"
    con.Open strcon

    sQuery = "SELECT * FROM TABLE(MA_DWM_AVG(TO_CHAR('L48D88-S-IN'),TO_NUMBER(2),TO_NUMBER(11),TO_NUMBER(40)))"
    cmd.ActiveConnection = con
    cmd.CommandText = sQuery
    Set rs = cmd.Execute()

    Sheets("Sheet1").Cells(2, 1).CopyFromRecordset rs

    rs.Close
End Sub

    


    即使使用了如下的squery,错误仍然存在。变量数据类型已经得到很好的处理。没有类型不匹配。
    


    SELECT * FROM TABLE(MA_DWM_AVG('L48D88-S-IN',2,11,40))

    


    下面是我面临错误的功能代码。函数代码非常大,所以只给出所需的代码片段。
    


    create or replace function MA_DWM_AVG(FID IN VARCHAR, CHOICE INT, PERIOD1 INT,PERIOD2 INT)
return TEMP_NESTED as

 --to create a seperate transcion for the function we have created.
  PRAGMA AUTONOMOUS_TRANSACTION;
  V_RET TEMP_NESTED;

begin
--TRUNCATING GTT TABLE USED IN A FUNCTION
EXECUTE IMMEDIATE 'TRUNCATE TABLE GTT_DWM_STATS';
--IF DAILTY STATISTICS ARE NEEDED THEN CHOICE=1
--We calculate rownumber,fs_perm_sec_id, date ,closing value its avearages    for 2 periods, open price, high price for the day, low price for the day

--weekly statistics are allculated if choice=2
IF CHOICE =2 AND PERIOD1<>0 AND PERIOD2<>0 THEN
--First we calculate rownumber,fs_perm_sec_id, week end date, Closing price and its average for 2 periods


INSERT INTO GTT_DWM_STATS(ROWNUMBER,FID,CLOSINGDATE,PRICE_CLOSE,MOVINGAVERAGE_PERIOD1,MOVINGAVERAGE_PERIOD2) 
    SELECT ROWNUM,FS_PERM_SEC_ID,"DATE",PPRICE,
        CASE 
            WHEN COUNT(PPRICE) OVER (ORDER BY "DATE" DESC ROWS BETWEEN CURRENT ROW AND PERIOD1-1 FOLLOWING) >= PERIOD1
        THEN AVG(PPRICE) OVER (ORDER BY "DATE" DESC ROWS BETWEEN CURRENT ROW AND PERIOD1-1 FOLLOWING)
        ELSE NULL
        END AS "Moving Average Period 1",
        CASE 
            WHEN COUNT(PPRICE) OVER (ORDER BY "DATE" DESC ROWS BETWEEN CURRENT ROW AND PERIOD2-1 FOLLOWING) >= PERIOD2
        THEN AVG(PPRICE) OVER (ORDER BY "DATE" DESC ROWS BETWEEN CURRENT ROW AND PERIOD2-1 FOLLOWING)
        ELSE NULL
        END AS "Moving Average Period 2"
        FROM(
                SELECT FS_PERM_SEC_ID,"DATE",P_PRICE AS PPRICE,P_VOLUME,
                    CASE 
                        WHEN (TO_CHAR("DATE",'D') >= AVG(TO_CHAR("DATE",'D')) OVER (order by "DATE" DESC rows between 1 preceding and current row) and ROWNUM>=1) 
                            or TO_CHAR("DATE",'D')=6
                    THEN 1
                    ELSE 0
                    END AS WEEKFLAG
                FROM FP_BASIC_BD WHERE FS_PERM_SEC_ID=FID AND P_VOLUME<>0 ORDER BY "DATE" DESC) WHERE WEEKFLAG=1;
--get week start date
--line 89 is here
UPDATE GTT_DWM_STATS 
    SET STARTDATE = 
        (SELECT "DATE" FROM FP_BASIC_BD  WHERE FP_BASIC_BD.FS_PERM_SEC_ID=FID AND FP_BASIC_BD."DATE">=TO_CHAR(TRUNC(TO_DATE(GTT_DWM_STATS.CLOSINGDATE,'DD-MON-YY'), 'IW'),'DD-MON-YY') AND P_VOLUME<>0 AND ROWNUM=1) 
        WHERE EXISTS (SELECT FP_BASIC_BD."DATE" FROM FP_BASIC_BD WHERE FP_BASIC_BD."DATE"=GTT_DWM_STATS.CLOSINGDATE );
--get opening price for the week
UPDATE GTT_DWM_STATS
    SET PRICE_OPEN = 
        (SELECT FP_BASIC_BD.P_PRICE_OPEN FROM FP_BASIC_BD WHERE FP_BASIC_BD.FS_PERM_SEC_ID=GTT_DWM_STATS.FID AND FP_BASIC_BD."DATE"=GTT_DWM_STATS.STARTDATE);
--get high value of p_price_high for week's duration
UPDATE GTT_DWM_STATS 
    SET PRICE_HIGH= 
        (SELECT MAX(P_PRICE_HIGH) FROM FP_BASIC_BD WHERE FP_BASIC_BD.FS_PERM_SEC_ID=FID AND FP_BASIC_BD."DATE" BETWEEN GTT_DWM_STATS.STARTDATE AND GTT_DWM_STATS.CLOSINGDATE)
        WHERE EXISTS(SELECT P_PRICE_HIGH FROM FP_BASIC_BD WHERE FP_BASIC_BD.FS_PERM_SEC_ID=GTT_DWM_STATS.FID);
--get low value of p_price_low for week's duration
UPDATE GTT_DWM_STATS 
    SET PRICE_LOW= 
        (SELECT MIN(P_PRICE_LOW) FROM FP_BASIC_BD WHERE FP_BASIC_BD.FS_PERM_SEC_ID=FID AND FP_BASIC_BD."DATE" BETWEEN GTT_DWM_STATS.STARTDATE AND GTT_DWM_STATS.CLOSINGDATE) WHERE EXISTS(SELECT P_PRICE_LOW FROM FP_BASIC_BD WHERE FP_BASIC_BD.FS_PERM_SEC_ID=GTT_DWM_STATS.FID);


END IF;


--get the GTT values into table derived from object type and return it.
select 
    cast(
    multiset(

                select * from GTT_DWM_STATS WHERE GTT_DWM_STATS.FID=FID ORDER BY CLOSINGDATE DESC

             )as TEMP_NESTED) into v_ret from dual;

   COMMIT;
   return V_RET;

end MA_DWM_AVG;

    


    您还可以为全局临时表引用DDL,如下所示。
    


    CREATE GLOBAL TEMPORARY TABLE "MA_FACTSET"."GTT_DWM_STATS" 
(   "ROWNUMBER" NUMBER(*,0), 
    "FID" VARCHAR2(20 BYTE), 
    "CLOSINGDATE" DATE, 
    "PRICE_CLOSE" FLOAT(126), 
    "MOVINGAVERAGE_PERIOD1" FLOAT(126), 
    "MOVINGAVERAGE_PERIOD2" FLOAT(126), 
    "STARTDATE" DATE, 
    "PRICE_OPEN" FLOAT(126), 
    "PRICE_HIGH" FLOAT(126), 
    "PRICE_LOW" FLOAT(126)
   ) ON COMMIT PRESERVE ROWS ;      

    


    请不要建议我将函数用作过程。因为函数正在使用一个全局临时表,最后返回完整的表。所以把它作为一个强制函数使用。
    1 回复  |  直到 7 年前
        1
  •  3
  •   Dmitriy rajalaxmi    7 年前

    你的日期工作完全不正确。很难指出确切的地点,因为有一些潜在的地点。我将解释这段代码有什么问题(摘自您的“第89行”声明): 

    ... FP_BASIC_BD."DATE">=TO_CHAR(TRUNC(TO_DATE(GTT_DWM_STATS.CLOSINGDATE,'DD-MON-YY'), 'IW'),'DD-MON-YY') ...

    GTT_DWM_STATS.CLOSINGDATE 具有日期格式(根据DDL语句)。但是 to_date 函数将字符串作为第一个参数。在这里,Oracle执行以下操作:

    • 将日期隐式转换为字符串(使用会话格式)
    • 然后 十月一日 函数试图将其转换回日期,将字符串视为写入的日期。 'DD-MON-YY' 格式
    • 然后 trunc 截断日期
    • 然后 to_char 再次将其转换为字符串,然后将字符串与日期进行比较(我猜) FP_BASIC_BD."DATE"
    • 如果 “日期” 是日期,Oracle将表达式的结果再次隐式转换为等号右侧的日期。
    • 或如果 “日期” 是字符串,Oracle根据字符串比较规则对字符串进行比较。

    您需要做的是消除所有不必要的转换: 

    FP_BASIC_BD."DATE" >= TRUNC(GTT_DWM_STATS.CLOSINGDATE, 'IW')

    在那之后,你必须仔细检查你正在处理日期的所有其他声明。如果函数接受日期作为输入参数,则必须传递日期;如果函数接受字符串,则必须传递字符串。与比较相同:将字符串与字符串进行比较,将日期与日期进行比较。

    推荐文章
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号