|
Sysdate Oracle передаю в VB, ошибка литерала?
#35259044
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Россия, Белгород
Сообщения: 267
|
|
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
procedure recalc_number
(
stelnumber varchar,
smonth number,
syear number)
as
ncost number;
sstartsaldo number;
sendsaldo number;
ppaid number;
ftelrn number;
endmonth number;
endyear number;
gen_rn number;
date_sys date;
ttable varchar( 14 );
begin
if smonth = 1 then
endyear:=syear- 1 ;
endmonth:= 12 ;
else
endmonth:=smonth- 1 ;
endyear:=syear;
end if;
ttable:='table'||syear||lpad(to_char(smonth),2,'0');
if length(stelnumber)= 7 then
for i in (select s.telnumber, s.date_from, s.date_to, s.rn, s.pa_id
from subscr_num s where s.telnumber=stelnumber) loop
if i.date_to is null then date_sys:=to_date(sysdate,'dd.mm.yyyy'); end if;
begin
Execute immediate 'select nvl(sum(c.cost),0) from '||ttable||' c where c.telfrom='''||i.telnumber||'''
and to_date(c.starttime, ''dd.mm.yyyy'') >= to_date('''||i.date_from||''',''dd.mm.yyyy'')
and (to_date(c.starttime, ''dd.mm.yyyy'') <= to_date('''||i.date_to||''',''dd.mm.yyyy'')
or to_date(c.starttime, ''dd.mm.yyyy'') <= to_date('''||date_sys||''',''dd.mm.yyyy''))'
into ncost;
exception when others then
ncost:= 0 ;
end;
begin
select nvl(sum(p.nsum), 0 ) into ppaid from payments_num p
where p.num_rn=i.rn and p.nmonth=smonth and p.nyear=syear;
exception when others then
ppaid:= 0 ;
end;
if ppaid is null then ppaid:= 0 ; end if;
begin
select a.num_rn into ftelrn from accounts_num a
where a.num_rn=i.rn and a.nmonth=smonth and a.nyear=syear;
exception when others then
if ncost is null then ncost:= 0 ; end if;
if ppaid is null then ppaid:= 0 ; end if;
if sendsaldo is null then sendsaldo:= 0 ; end if;
begin
select max(a.rn) into gen_rn from accounts_num a;
exception when others then gen_rn:= 0 ;
end;
gen_rn:=gen_rn+ 1 ;
if gen_rn is null then gen_rn:= 1 ; end if;
insert into accounts_num a values (gen_rn, i.rn, i.pa_id, smonth
, syear, sendsaldo, ncost, ppaid
, sendsaldo+ncost- 0 , '1'
, i.date_from, i.date_to);
end;
update accounts_num a set a.pa_id=ppaid
where a.num_rn=i.rn and a.nmonth=smonth and a.nyear=syear;
begin
select nvl(a.endsaldo, 0 ) into sendsaldo from accounts_num a
where a.nmonth=endmonth and a.nyear=endyear and a.num_rn=i.rn;
exception when others then sendsaldo:= 0 ;
end;
begin
select nvl(a.startsaldo, 0 ) into sstartsaldo from accounts_num a
where a.nmonth=smonth and a.nyear=syear and a.num_rn=i.rn;
exception when others then sstartsaldo:= 0 ;
end;
update accounts_num a set a.startsaldo=sendsaldo where a.num_rn=i.rn and a.nmonth=smonth and a.nyear=syear;
if ncost is null then ncost:= 0 ; end if;
update accounts_num a set a.accrued=ncost where a.num_rn=i.rn and a.nmonth=smonth and a.nyear=syear;
begin
if ncost is null then ncost:= 0 ; end if;
if ppaid is null then ppaid:= 0 ; end if;
select nvl(a.endsaldo, 0 ) into sstartsaldo from accounts_num a where a.nmonth=endmonth and a.nyear=endyear and a.num_rn=i.rn;
sendsaldo:=sstartsaldo+ncost-ppaid;
exception when others
then sendsaldo:= 0 ;
end;
update accounts_num a set a.endsaldo=(sstartsaldo+ncost-ppaid)
where a.num_rn=i.rn and a.nmonth=smonth and a.nyear=syear;
end loop;
end if;
if stelnumber = '000' then
for i in (select s.telnumber, s.date_from, s.date_to, s.rn, s.pa_id
from billdba.subscr_num s) loop
if i.date_to is null then date_sys:=to_date(sysdate,'dd.mm.yyyy'); end if;
begin
Execute immediate 'select sum(c.cost) from '||ttable||' c
where c.telfrom ='''||i.telnumber||'''
and to_date(c.starttime, ''dd.mm.yyyy'') >= to_date('''||i.date_from||''',''dd.mm.yyyy'')
and (to_date(c.starttime, ''dd.mm.yyyy'') <= to_date('''||i.date_to||''',''dd.mm.yyyy'')
or to_date(c.starttime, ''dd.mm.yyyy'') <= to_date('''||date_sys||''',''dd.mm.yyyy''))'
into ncost;
exception when others then
ncost:= 0 ;
end;
begin
select sum(p.nsum) into ppaid from payments_num p
where p.num_rn=i.rn and p.nmonth=smonth and p.nyear=syear;
exception when others then
ppaid:= 0 ;
end;
if ppaid is null then ppaid:= 0 ; end if;
begin
select a.num_rn into ftelrn from accounts_num a
where a.num_rn=i.rn and a.nmonth=smonth and a.nyear=syear;
exception when others then
if ncost is null then ncost:= 0 ; end if;
if ppaid is null then ppaid:= 0 ; end if;
if sendsaldo is null then sendsaldo:= 0 ; end if;
begin
select max(a.rn) into gen_rn from accounts_num a;
exception when others then gen_rn:= 0 ;
end;
gen_rn:=gen_rn+ 1 ;
if gen_rn is null then gen_rn:= 1 ; end if;
insert into accounts_num a values (gen_rn, i.rn, i.pa_id, smonth
, syear, sendsaldo, ncost, ppaid
, sendsaldo+ncost- 0 , '1'
, i.date_from, i.date_to);
end;
update accounts_num a set a.pa_id=ppaid
where a.num_rn=i.rn and a.nmonth=smonth and a.nyear=syear;
begin
select a.endsaldo into sendsaldo from accounts_num a
where a.nmonth=endmonth and a.nyear=endyear and a.num_rn=i.rn;
exception when others then sendsaldo:= 0 ;
end;
begin
select a.startsaldo into sstartsaldo from accounts_num a
where a.nmonth=smonth and a.nyear=syear and a.num_rn=i.rn;
exception when others then sstartsaldo:= 0 ;
end;
update accounts_num a set a.startsaldo=sendsaldo where a.num_rn=i.rn and a.nmonth=smonth and a.nyear=syear;
if ncost is null then ncost:= 0 ; end if;
update accounts_num a set a.accrued=ncost where a.num_rn=i.rn and a.nmonth=smonth and a.nyear=syear;
begin
if ncost is null then ncost:= 0 ; end if;
if ppaid is null then ppaid:= 0 ; end if;
select a.endsaldo into sstartsaldo from accounts_num a where a.nmonth=endmonth and a.nyear=endyear and a.num_rn=i.rn;
sendsaldo:=sstartsaldo+ncost-ppaid;
exception when others
then sendsaldo:= 0 ;
end;
update accounts_num a set a.endsaldo=(sstartsaldo+ncost-ppaid)
where a.num_rn=i.rn and a.nmonth=smonth and a.nyear=syear;
end loop;
end if;
end;
|
|
|
