请教:
ss varchar2(100);
zz number(16,4);
有这样一个字符串如:ss:='(2)*(-2)+10.2';
我在过程中向执行这个操作:
select ss into zz from dual;
即将ss运算后的结果6.2放入zz中请问有什么方法可以实现。
谢谢
ss varchar2(100);
zz number(16,4);
有这样一个字符串如:ss:='(2)*(-2)+10.2';
我在过程中向执行这个操作:
select ss into zz from dual;
即将ss运算后的结果6.2放入zz中请问有什么方法可以实现。
谢谢
解决方案 »
- 已知几个离散点XY坐标,如何用SQL/PLS判断其变化趋势(递增、递减 、先增后减...等~~~)
- 两个或多个会话同时向一个表插入会怎么样?
- 急,求助一条SQL语句写法!!!
- Oracle,toad问题,为什么我在toad里sql Editor编辑器里写了sql语句,我们不能删除和更改了?
- 我是64bit的centos,我怎么看这几包是32bit的呢?怎么回事?
- 关于Access导入Oracle的问题
- 如何获取update所操作的条数
- 关于数据导出和导入
- ORACLE中位运算的问题
- 求救:在P4机器上如何安装Oracle8i数据库
- 问题真难,高手来帮助一下吧!送1000分都可以。
- 求教c语言调用oracle存储过程问题
14:23:47 2 as
14:23:47 3 ss varchar2(100);
14:23:47 4 zz number(16,4);
14:23:47 5 v_sql varchar2(100);
14:23:47 6 begin
14:23:47 7 ss:='(2)*(-2)+10.2';
14:23:47 8 execute immediate 'select '||ss||' from dual' into zz;
14:23:47 9 dbms_output.put_line(zz);
14:23:47 10 end;
14:23:47 11 /过程已创建。已用时间: 00: 00: 00.30
14:23:47 SQL> exec test;
6.2PL/SQL 过程已成功完成。已用时间: 00: 00: 00.40
大大给看看我这几个过程。能不能帮忙给优化一下。谢谢先。如果分不够的话我再加。
以下是几个用到的过程:一:入口。CREATE OR REPLACE PROCEDURE PROC_PG_TEXT(startDate Char) As
yf Varchar2(2);
nd Varchar2(4);
startD Varchar2(7);
endD Varchar(7);
Begin
nd:=to_char(to_date(startDate,'yyyy-mm-dd'),'yyyy');
yf:=to_char(to_date(startDate,'yyyy-mm-dd'),'mm');
endD:=nd||yf;
For i In 1..yf Loop
startD:=nd||ltrim(to_char(i,'00'));
--startD:=i;
proc_pg_date_etl(startD,endD);
End Loop;
End;
二:5个中间计算调用的过程:CREATE OR REPLACE PROCEDURE PROC_PG_getGsxmz0(nsrdzdah Char,pgrq_q Char,pgrq_z Char) As
hao Varchar2(21);
q Varchar2(10);
z Varchar2(10);
Begin
hao:=nsrdzdah;
q:=pgrq_q;
z:=pgrq_z;
Insert Into pg_text (a1,a2) SELECT substr(DM_GSXM,1,1)||'0'||substr(DM_GSXM,3) As gsdm ,sum(GSXMZ) AS MC FROM PG_LZ_GSXMZ_QC
WHERE NSRDZDAH=hao and SSSQ_Q >=TO_DATE(q,'yyyy-mm-dd')
AND (SSSQ_Z <= ADD_MONTHS (to_date (z,'yyyy-mm-dd'),1)-1)
AND DM_GSXM IN
(SELECT DISTINCT substr(b.gsxm_dm,1,1)||'0'||substr(b.gsxm_dm,3) FROM pg_sr_codeitem_gsxm b Where substr(b.gsxm_dm,2,1)='0' And b.zb_dm IN
(SELECT a.zb_dm FROM pg_sr_codeitem a WHERE a.gslb='0' AND a.zb_fw IS NOT NULL )
)
GROUP BY DM_GSXM
;
Commit;End;
2:
CREATE OR REPLACE PROCEDURE PROC_PG_getGsxmzA(nsrdzdah Char,pgrq_q Char,pgrq_z Char) As
hao Varchar2(21);
q Varchar2(10);
z Varchar2(10);
Begin
hao:=nsrdzdah;
q:=pgrq_q;
z:=pgrq_z;
Insert Into pg_text (a1,a2) SELECT substr(DM_GSXM,1,1)||'A'||substr(DM_GSXM,3) As gsdm ,sum(GSXMZ) AS MC FROM PG_LZ_GSXMZ_QC
WHERE NSRDZDAH=hao and SSSQ_Q >=ADD_MONTHS (TO_DATE(q,'yyyy-mm-dd'),-1)
AND (SSSQ_Z <= to_date (z,'yyyy-mm-dd')-1)
AND DM_GSXM IN
(SELECT DISTINCT substr(b.gsxm_dm,1,1)||'0'||substr(b.gsxm_dm,3) FROM pg_sr_codeitem_gsxm b Where substr(b.gsxm_dm,2,1)='A' And b.zb_dm IN
(SELECT a.zb_dm FROM pg_sr_codeitem a WHERE a.gslb='0' AND a.zb_fw IS NOT NULL )
)
GROUP BY DM_GSXM
;
Commit;End;
3:
CREATE OR REPLACE PROCEDURE PROC_PG_getGsxmzB(nsrdzdah Char,pgrq_q Char,pgrq_z Char) As
hao Varchar2(21);
q Varchar2(10);
z Varchar2(10);
Begin
hao:=nsrdzdah;
q:=pgrq_q;
z:=pgrq_z;
Insert Into pg_text (a1,a2) SELECT substr(DM_GSXM,1,1)||'B'||substr(DM_GSXM,3) As gsdm ,sum(GSXMZ) AS MC FROM PG_LZ_GSXMZ_QC
WHERE NSRDZDAH=hao and SSSQ_Q >=ADD_MONTHS(TO_DATE(q,'yyyy-mm-dd'),-12)
AND (SSSQ_Z <= ADD_MONTHS (to_date (z,'yyyy-mm-dd'),-11)-1)
AND DM_GSXM IN
(SELECT DISTINCT substr(b.gsxm_dm,1,1)||'0'||substr(b.gsxm_dm,3) FROM pg_sr_codeitem_gsxm b Where substr(b.gsxm_dm,2,1)='B' And b.zb_dm IN
(SELECT a.zb_dm FROM pg_sr_codeitem a WHERE a.gslb='0' AND a.zb_fw IS NOT NULL )
)
GROUP BY DM_GSXM
;
Commit;End;4:CREATE OR REPLACE PROCEDURE PROC_PG_getGsxmzD(nsrdzdah Char,pgrq_q Char,pgrq_z Char) As
hao Varchar2(21);
q Varchar2(10);
z Varchar2(10);
Begin
hao:=nsrdzdah;
q:=pgrq_q;
z:=pgrq_z;
Insert Into pg_text (a1,a2) SELECT substr(DM_GSXM,1,1)||'D'||substr(DM_GSXM,3) As gsdm ,sum(GSXMZ) AS MC FROM PG_LZ_GSXMZ_QC
WHERE NSRDZDAH=hao
AND (SSSQ_Z = ADD_MONTHS (to_date (z,'yyyy-mm-dd'),1)-1)
AND DM_GSXM IN
(SELECT DISTINCT substr(b.gsxm_dm,1,1)||'0'||substr(b.gsxm_dm,3) FROM pg_sr_codeitem_gsxm b Where substr(b.gsxm_dm,2,1)='D' And b.zb_dm IN
(SELECT a.zb_dm FROM pg_sr_codeitem a WHERE a.gslb='0' AND a.zb_fw IS NOT NULL )
)
GROUP BY DM_GSXM
;
Commit;End;
5:CREATE OR REPLACE PROCEDURE PROC_PG_getGsxmzE(nsrdzdah Char,pgrq_q Char,pgrq_z Char) As
hao Varchar2(21);
q Varchar2(10);
z Varchar2(10);
Begin
hao:=nsrdzdah;
q:=pgrq_q;
z:=pgrq_z;
Insert Into pg_text (a1,a2) SELECT substr(DM_GSXM,1,1)||'E'||substr(DM_GSXM,3) As gsdm ,sum(GSXMZ) AS MC FROM PG_LZ_GSXMZ_QC
WHERE NSRDZDAH=hao
AND (SSSQ_Z = ADD_MONTHS (to_date (z,'yyyy-mm-dd'),1)-1)
AND DM_GSXM IN
(SELECT DISTINCT substr(b.gsxm_dm,1,1)||'0'||substr(b.gsxm_dm,3) FROM pg_sr_codeitem_gsxm b Where substr(b.gsxm_dm,2,1)='E' And b.zb_dm IN
(SELECT a.zb_dm FROM pg_sr_codeitem a WHERE a.gslb='0' AND a.zb_fw IS NOT NULL )
)
GROUP BY DM_GSXM
;
Commit;End;
三:主过程CREATE OR REPLACE PROCEDURE PROC_PG_date_etl(startDate Char,endDate Char) As
sPgrq_Q Varchar2(10);
sPgrq_Z Varchar2(10);
s Varchar2(2);
Strnsrdzdah Varchar2(21);
Strnsrmc Varchar2(200);
StrZbdm Varchar2(200);
Zbz Number(16,2);
StrZbfw Varchar2(200);
StrGsTemp Varchar2(500);
StrGs Varchar2(4);
b Varchar2(1);
j Int;
NumGsxm Integer;
cursor cur_xmz(dm Varchar2) Is
Select a2 From pg_text Where a1=dm; Cursor cur_nsr Is
select t.nsrdzdah,t.nsrmc from pg_swdj_jbxx t where t.nsrdzdah ='999904000011144854' ORDER BY t.nsrdzdah; Cursor cur_zb Is
SELECT a.zb_dm,a.zb_fw FROM pg_sr_codeitem a WHERE a.gslb='0' AND a.zb_fw IS NOT null ORDER BY a.zb_dm ;Begin
sPgrq_Q:=startDate||'01';
sPgrq_Z:=endDate||'01';
Delete pg_nsr_zbz_qc Where pgsq_q=startDate And pgsq_z=endDate And zlyf=endDate;
Commit;
Open cur_nsr;
Loop
Fetch cur_nsr Into Strnsrdzdah,Strnsrmc;
Exit When cur_nsr%Notfound; Delete pg_text;
Commit;
PROC_PG_getGsxmz0(Strnsrdzdah,sPgrq_Q,sPgrq_Z);
PROC_PG_getGsxmzA(Strnsrdzdah,sPgrq_Q,sPgrq_Z);
PROC_PG_getGsxmzB(Strnsrdzdah,sPgrq_Q,sPgrq_Z);
PROC_PG_getGsxmzD(Strnsrdzdah,sPgrq_Q,sPgrq_Z);
PROC_PG_getGsxmzE(Strnsrdzdah,sPgrq_Q,sPgrq_Z);
StrGsTemp:='';
StrGs:='';
Select Count(*) Into NumGsxm From pg_text;
If NumGsxm<>0 Then
Open cur_zb ;
Loop
Fetch cur_zb Into StrZbdm,StrZbfw;
Exit When cur_zb%Notfound;
--For j In 1..length(StrZbfw) Loop
j:=1;
strGsTemp:='';
While j<=length(StrZbfw) Loop
--Exit When i>length(StrZbfw)-4;
s:=substr(StrZbfw,j,1);
If s='△' Then
StrGs:=substr(StrZbfw,j+1,4);
j:=j+5;
Select Count(*) Into Zbz From pg_text Where a1=StrGs;
If Zbz=0 Then
b:='N';
Exit;
Else
Open cur_xmz(StrGs);
Loop
Fetch cur_xmz Into Zbz;
Exit When cur_xmz%Notfound;
strGsTemp := strGsTemp || '(' || to_char(Zbz) || ')';
b:='Y';
End Loop;
Close cur_xmz;
End If;
Else
j:=j+1;
strGsTemp:=strGsTemp||s;
End If;
End Loop;
If b='Y' Then
If strGsTemp Is Not Null Then
strGsTemp:=Replace(strGsTemp,'@','');
strGsTemp:=Replace(strGsTemp,'$','+');
--Select strGsTemp Into zbz From dual;
--Select strGsTemp Into ss From dual;
--dbms_output.put_line(ss);
--Zbz:=88;
execute immediate 'select '||strGsTemp||' from dual' into Zbz;
/*这个语句出现除数为零的错误。也给帮帮忙抛出一下~-~。*/
Insert Into pg_nsr_zbz_qc(pgsq_q,pgsq_z,nsrdzdah,zb_dm,zbz,zlyf) Values(startDate,endDate,Strnsrdzdah,StrZbdm,Zbz,endDate);
Commit;
End If;
End If;
End Loop;
Close cur_zb;
End If;
End Loop;
Close cur_nsr;
End;
谢谢先