procedure get_detail_sellinfo(p_businessidlist varchar2,
p_enum_name varchar2,
p_year varchar2,
p_month varchar2,
p_name varchar2,
p_out_detail out p_cursor) is
v_mindate date;
v_maxdate date;
enumno number;
v_sql varchar2(4096);
v_product_type number;
begin
v_mindate := to_date(p_year || '-' || p_month || '-1', 'yyyy-mm-dd');
v_maxdate := to_date(p_year || '-' || to_char(to_number(p_month) + 1) || '-1','yyyy-mm-dd');
select m.serialno
into enumno
from maplayer_enum m
where p_enum_name = m.name;
select t.serialno
into v_product_type
from product_info t
where p_name = t.name;
if enumno = 3 then
v_sql := 'select t.name,s.amount,s.price,sum(s.amount*s.price) from town_count t,sell_info s,sell_business_info r where t.serialno in (' ||
p_businessidlist || ') ' || 'and r.town=t.serialno ' ||
'and s.business_no=r.serialno' || 'and s.product_no=' ||
v_product_type || 'and s.date_time >= ' || v_mindate ||
'and s.date_time < ' || v_maxdate;
open p_out_detail for v_sql;
end if;
end get_detail_sellinfo;错误:ORA-00933:SQL命令未正确结束
p_enum_name varchar2,
p_year varchar2,
p_month varchar2,
p_name varchar2,
p_out_detail out p_cursor) is
v_mindate date;
v_maxdate date;
enumno number;
v_sql varchar2(4096);
v_product_type number;
begin
v_mindate := to_date(p_year || '-' || p_month || '-1', 'yyyy-mm-dd');
v_maxdate := to_date(p_year || '-' || to_char(to_number(p_month) + 1) || '-1','yyyy-mm-dd');
select m.serialno
into enumno
from maplayer_enum m
where p_enum_name = m.name;
select t.serialno
into v_product_type
from product_info t
where p_name = t.name;
if enumno = 3 then
v_sql := 'select t.name,s.amount,s.price,sum(s.amount*s.price) from town_count t,sell_info s,sell_business_info r where t.serialno in (' ||
p_businessidlist || ') ' || 'and r.town=t.serialno ' ||
'and s.business_no=r.serialno' || 'and s.product_no=' ||
v_product_type || 'and s.date_time >= ' || v_mindate ||
'and s.date_time < ' || v_maxdate;
open p_out_detail for v_sql;
end if;
end get_detail_sellinfo;错误:ORA-00933:SQL命令未正确结束
--
在这里调用end;
p_year in varchar2,
p_month in varchar2,
p_name in varchar2,
p_out_detail in out varchar2) as
v_mindate date;
v_maxdate date;
enumno number;
v_sql varchar2(4096);
v_product_type number;
TYPE CurCheck IS REF CURSOR;
p_out_detail CurCheck;
begin
v_mindate := to_date(p_year || '- ' || p_month || '-1 ', 'yyyy-mm-dd ');
v_maxdate := to_date(p_year || '- ' || to_char(to_number(p_month) + 1) ||
'-1 ',
'yyyy-mm-dd ');
select m.serialno
into enumno
from maplayer_enum m
where p_enum_name = m.name;
select t.serialno
into v_product_type
from product_info t
where p_name = t.name;
if enumno = 3 then
v_sql := 'select t.name,s.amount,s.price,sum(s.amount*s.price) from town_count t,sell_info s,sell_business_info r where t.serialno in ( ' ||
p_businessidlist || ') ' || 'and r.town=t.serialno ' ||
'and s.business_no=r.serialno ' || 'and s.product_no= ' ||
v_product_type || 'and s.date_time > = ' || v_mindate ||
'and s.date_time < ' || v_maxdate;
open p_out_detail for v_sql;
end if;
end get_detail_sellinfo;
Procedure Get_Detail_Sellinfo(p_Businessidlist In Varchar2,
p_Enum_Name In Varchar2,
p_Year In Varchar2,
p_Month In Varchar2,
p_Name In Varchar2,
p_Out_Detail Out 过程包.t_Refcur) As
v_Mindate Date;
v_Maxdate Date;
Enumno Number;
v_Sql Varchar2(4096);
v_Product_Type Number;
Begin
v_Mindate := To_Date(p_Year || '- ' || p_Month || '-1 ', 'yyyy-mm-dd ');
v_Maxdate := To_Date(p_Year || '- ' || To_Char(To_Number(p_Month) + 1) ||'-1 ','yyyy-mm-dd ');
Select m.Serialno
Into Enumno
From Maplayer_Enum m
Where p_Enum_Name = m.Name;
Select t.Serialno
Into v_Product_Type
From Product_Info t
Where p_Name = t.Name;
If Enumno = 3 Then
v_Sql := 'select t.name,s.amount,s.price,sum(s.amount*s.price) from town_count t,sell_info s,sell_business_info r where t.serialno in ( ' ||
p_Businessidlist || ') and r.town=t.serialno and s.business_no=r.serialno and s.product_no= ' ||
v_Product_Type || ' and s.date_time > = ' || v_Mindate ||' and s.date_time < ' || v_Maxdate;
End If;
Open p_Out_Detail For v_Sql;
End Get_Detail_Sellinfo;
(p_busdlist in varchar2,
p_enum_name in varchar2,
p_year in varchar2,
p_month in varchar2,
p_name in varchar2,
p_out_detail in out varchar2)
is
v_mindate date;
v_maxdate date;
enumno number;
v_sql varchar2(4096);
v_product_type number;
TYPE CurCheck IS REF CURSOR;
--这去掉了个 p_out_detail 定义
begin
v_mindate := to_date(p_year|| '- '|| p_month|| '-1 ', 'yyyy-mm-dd ');
v_maxdate := to_date(p_year|| '- '|| to_char(to_number(p_month) + 1)||
'-1 ',
'yyyy-mm-dd ');
select m.serialno
into enumno
from maplayer_enum m
where p_enum_name = m.name;
select t.serialno
into v_product_type
from product_info t
where p_name = t.name;
if enumno = 3 then
v_sql := 'select t.name,s.amount,s.price,sum(s.amount*s.price) from town_count t,sell_info s,sell_business_info r where t.serialno in ( '||
p_businessidlist|| ') '|| 'and r.town=t.serialno '||
'and s.business_no=r.serialno '|| 'and s.product_no= '||
v_product_type|| 'and s.date_time > = '|| v_mindate||
'and s.date_time < '|| v_maxdate;
open p_out_detail for v_sql;
end if;
end get_detail_sellinfo;
/
CREATE OR REPLACE procedure get_detail_sellinfo
(p_busdlist in varchar2,
p_enum_name in varchar2,
p_year in varchar2,
p_month in varchar2,
p_name in varchar2,
p_out_detail in out varchar2)
is
v_mindate date;
v_maxdate date;
enumno number;
v_sql varchar2(4096);
v_product_type number;
TYPE CurCheck IS REF CURSOR;
--这去掉了个 p_out_detail 定义
begin
v_mindate := to_date(p_year|| '- '|| p_month|| '-1 ', 'yyyy-mm-dd ');
v_maxdate := to_date(p_year|| '- '|| to_char(to_number(p_month) + 1)||
'-1 ',
'yyyy-mm-dd ');
select m.serialno
into enumno
from maplayer_enum m
where p_enum_name = m.name;
select t.serialno
into v_product_type
from product_info t
where p_name = t.name;
if enumno = 3 then
v_sql := 'select t.name,s.amount,s.price,sum(s.amount*s.price) from town_count t,sell_info s,sell_business_info r where t.serialno in ( '||
p_businessidlist|| ') '|| 'and r.town=t.serialno '||
'and s.business_no=r.serialno '|| 'and s.product_no= '||
v_product_type|| 'and s.date_time > = '|| v_mindate||
'and s.date_time < '|| v_maxdate;
open p_out_detail for v_sql;
end if;
end get_detail_sellinfo;
/