关于delphi调用oracle函数的问题.急呀!(100分)

lytwyx · 2006-10-09

delphi调用代码如下:      pro_mc := ADOQuery1.FieldValues['procedure'];       ADOStoredProc1.ProcedureName := 'dbo.'+pro_mc;       ADostoredProc1.Parameters.Clear;       with ADostoredProc1.Parameters.AddParameter do       begin         DataType :=ftString;         Direction := pdInput;         Value := pro_id;       end;       with ADostoredProc1.Parameters.AddParameter do       begin         DataType :=ftString;         Direction := pdInput;         Value := jq;       end;       with ADostoredProc1.Parameters.AddParameter do       begin         DataType :=ftString;         Direction := pdInput;         Value := bq;       end;       with ADostoredProc1.Parameters.AddParameter do       begin         DataType :=ftString;         Direction := pdInput;         Value := swjg_dm;       end;       with ADostoredProc1.Parameters.AddParameter do       begin         DataType :=ftString;         Direction := pdInput;         Value := gydm;       end;       with ADostoredProc1.Parameters.AddParameter do       begin         DataType :=ftString;         Direction := pdInput;         Value := zsxmdm;       end;       with ADostoredProc1.Parameters.AddParameter do       begin         DataType :=ftString;         Direction := pdInput;         Value := djzclxdm;       end;       //ADostoredProc1.Open;       ADostoredProc1.Prepared;       ADostoredProc1.ExeExecProc; 函数内容如下

函数在pl/sql中测试通过) create or replace function pro_yyhy_zyywsrbdl(zbid in string,jq in string, bq in string,swjgdm in string,zggy in string,zsxm in string,djzclx in string) return number is  Result number;  CURSOR nsrfw (curswjgdm string,curzggydm string,curzsxm string,curdjzclx string) is    --确定纳税人范围  select distinct(a.nsrsbh) from dj_nsrxx a,dj_sz b  where a.nsr_swjg_dm like trim(curswjgdm) and a.zg_swgy_dm like trim(curzggydm) and  a.nsrsbh=b.nsrsbh and b.zsxm_dm like trim(curzsxm) and a.nsr_swjg_dm=b.nsr_swjg_dm and  a.djzclx_dm like trim(curdjzclx) and (a.nsrmc like '%医药%' or a.hy_dm='2700') and a.nsrzt_dm in  ('10','21','22','23','71');  /*  目前纳税人的范围还没有确定  */  --CURSOR nsrfw is    --确定纳税人范围   --select nsrsbh from dj_nsrxx where nsrsbh='620103756556236';  nsrsbh    VARCHAR2(20);  --纳税人识别号  jqnd      char(4);               --基期年  jqyq      char(10);              --基期起  jqyz      char(8);               --基期止  jqsr      number;             --基期收入  jqljsr    number;             --基期累计收入  bqnd      char(4);               --本期年  bqyq      char(8);              --本期起  bqyz      char(8);               --本期止  bqsr      number;             --本期收入  bqljsr    number;             --本期累计收入  pzxh      CHAR(16);            --sb_zzs_2003_fb.pzxh  bz        number(4,2);          --基期本期比值  pgjgxh    char(8);               --评估结果id  pgsj      date;                  --评估时间    tablejq   char(9);  tablebq   char(9);  cszggy    char(11);  cszsxm    char(2);  csdjzclx  char(2);  inswjgdm  char(11);  CURSOR jqsrpzxh(curjqyq string,curjqyz string,curnsrsbh string) is   --确定基期收入pzxh   select pzxh from sb_zzs_2003_fb where fblx='FB6' and to_date(to_char(sssq_q,'yyyy-mm'),'yyyy-mm') between to_date(curjqyq,'yyyy-mm') and to_date(curjqyz,'yyyy-mm') and nsrsbh=curnsrsbh;  CURSOR bqsrpzxh (curbqyq string,curbqyz string,curnsrsbh string) is  --确定基期收入pzxh   select pzxh from sb_zzs_2003_fb where fblx='FB6' and to_date(to_char(sssq_q,'yyyy-mm'),'yyyy-mm') between to_date(curbqyq,'yyyy-mm') and to_date(curbqyz,'yyyy-mm') and nsrsbh=curnsrsbh;  CURSOR jqsrls(curpzxh string) is     --确定主营业务收入   select bys from sb_zzs_2003_fb6 where pzxh= curpzxh and fbxm_dm='6101';   begin  --首先确定游标是否有记录  if zggy  ='全部' then    cszggy   := '%';  end if;  if zsxm  ='全部' then    cszsxm   := '%';  end if;  if djzclx='全部' then    csdjzclx := '%';  end if;  OPEN nsrfw(swjgdm,cszggy,cszsxm,csdjzclx);  FETCH nsrfw into nsrsbh;  IF (nsrfw%NOTFOUND) then    Result :=100;  --没有发现符合范围的纳税人  ELSE    jqnd := trim(substr(jq,1,4));    jqyq := jqnd ||'-'|| trim(substr(jq,5,2));    jqyz := jqnd ||'-'|| trim(substr(jq,7,2));    bqnd := substr(bq,1,4);    bqyq := jqnd ||'-'|| trim(substr(bq,5,2));    bqyz := jqnd ||'-'|| trim(substr(bq,7,2));    --处理税务机关代码   if length(swjgdm)<11 then     inswjgdm:=substr(swjgdm,1,length(swjgdm)-1);     while not(length(inswjgdm)<11) loop       inswjgdm:= inswjgdm || '0';     end loop;   else     inswjgdm := swjgdm;   end if;      select FBXH.NEXTVAL into pgjgxh from dual;    select sysdate into pgsj from dual;              insert into nspg_pgjg values(pgjgxh,zbid,jq,bq,inswjgdm,zggy,zsxm,djzclx,pgsj);    WHILE not(nsrfw%NOTFOUND) LOOP      jqsr := 0;      jqljsr := 0;      --提取基期的主营业务收入      OPEN jqsrpzxh(jqyq,jqyz,nsrsbh);      FETCH jqsrpzxh into pzxh;      if (jqsrpzxh%NOTFOUND) then        Result := 100;      ELSE        WHILE NOT(jqsrpzxh%NOTFOUND) LOOP          OPEN jqsrls(pzxh);          FETCH jqsrls into jqsr;          if (jqsrls%NOTFOUND) then            Result := 100;          ELSE            WHILE NOT(jqsrls%NOTFOUND) LOOP              jqljsr := jqljsr + jqsr;              FETCH jqsrls into jqsr;            END LOOP;                      END IF;          CLOSE jqsrls;          FETCH jqsrpzxh into pzxh;        END LOOP;            END IF;      CLOSE jqsrpzxh;      --提取本期的主营业务收入      bqsr := 0;      bqljsr := 0;        OPEN bqsrpzxh(bqyq,bqyz,nsrsbh);      FETCH bqsrpzxh into pzxh;      if (bqsrpzxh%NOTFOUND) then        Result := 100;      ELSE        WHILE NOT(bqsrpzxh%NOTFOUND) LOOP          OPEN jqsrls(pzxh);          FETCH jqsrls into bqsr;          if (jqsrls%NOTFOUND) then            Result := 100;          ELSE            WHILE NOT(jqsrls%NOTFOUND) LOOP              bqljsr := bqljsr + bqsr;              FETCH jqsrls into bqsr;            END LOOP;            --计算基期和本期比值            if jqljsr=0 then              bz := 0;            else              bz := (bqljsr-jqljsr)/jqljsr;            end if;            --插入nspg_pgjg、nspg_pgjg_mx表中            --SET TRANSACTION READ WRITE;            insert into nspg_pgjg_mx values(pgjgxh,nsrsbh,bqljsr-jqljsr,bqljsr,bz);                        Result := 10;          END IF;          CLOSE jqsrls;          FETCH bqsrpzxh into pzxh;        END LOOP;      END IF;      CLOSE bqsrpzxh;        FETCH nsrfw into nsrsbh;    END LOOP;    commit;    CLOSE nsrfw;      END IF;  return(Result); end pro_yyhy_zyywsrbdl; / 错误提示: Project project1.exe raised exception class EOLeEcception with message 'ORA-06550' line 1 column 7: PLS-00221 :'PRO_YYHY_ZYYWSRBDL' is not a procedure or is undefined ORA_06550: line1 column7: PL/SQL:statement ignored process stopped use step or run to continue

lytwyx · 2006-10-09

没人顶,自己先

沉香屑 · 2006-10-10

不用proc控件 执行select 函数名() from dual

lytwyx · 2006-10-10

我是要执行我自己写的一个函数: create or replace function pro_yyhy_zyywsrbdl(zbid in string,jq in string, bq in string,swjgdm in string,zggy in string,zsxm in string,djzclx in string)

关于delphi调用oracle函数的问题.急呀!(100分)

lytwyx

Unregistered / Unconfirmed

lytwyx

Unregistered / Unconfirmed

沉香屑

Unregistered / Unconfirmed

lytwyx

Unregistered / Unconfirmed

Similar threads