觉得一条sql语句实现不了你的全部功能,可能要自己写函数来处理参数问题,然后用sql语句取得数据,用pl./sql将得到的数据保存到文件,这是我的思路.
解决方案 »
- ORA-00909:参数个数无效
- oracle数据查询(union语句)出现ORA-03212错误,请指教!
- oracle11gXE在导入dump文件时一直报表空间不足,请问怎么解决呀?
- 这样写SQL语句查找每个公司的最新的更新时间
- ado 读取oracle number型字段的问题
- 一道数据库设计的笔试题
- linux下,orcale9 狂吃内存,shutdown之后也没有释放内存,怎么办
- 请大家推荐些比较好的,适合oracle学习和资料下载的网站。
- 高分求解select查询问题~
- 一个简单的问题,可是我就是不知道,有那位可以帮帮我呀。
- 菜鸟哭求一个SQL语句!!!对于高手来说是很容易.....
- 查找表中某列相同的数据!
先求出超过最大库存的纪录(CURSOR保存,按差值排序),再求出少于最小库存的纪录(CURSOR保存 按差值排序),然后对2个CURSOR操作(生成
MP3 A D 4
类似的记录插入临时表
),任何一个没有记录了,就退出
over_temp(inv_code varchar2(10),qty number) 存放超过最大库存量的记录
lack_temp(inv_code varchar2(10),qty number) 存放小雨最小库存量的记录2 建立存储过程:
create or replace procedure shiftgoods(p_1 varchar2,p_2 varchar2) is
i number;
pos number;
cur_qty number;
min_qty number;
max_qty number;
lack_qty number;
over_qty number;
Inv varchar2(10);
str varchar2(100);
Item varchar2(10);
Inv_List varchar2(1000);
Item_List varchar2(100);
lack_invcode varchar2(10);
over_invcode varchar2(10);
fHandle utl_File.File_Type;
cursor over_cur is select inv_code,qty from over_temp order by qty desc;
cursor lack_cur is select inv_code,qty from lack_temp order by qty desc;
over_rec over_temp%rowtype;
lack_rec lack_temp%rowtype;
begin
fHandle:=utl_file.fopen('e:\','example.txt','w');
str:='物品 搬出仓库 搬入仓库 移动数量';
utl_file.put_line(fHandle,str);
Item_List:=p_2;
loop
delete from lack_temp;
delete from over_temp;
commit;
pos:=Instr(Item_List,',');
if pos=0 then
Item:=Item_List;
else
Item:=substr(Item_List,1,pos-1);
end if;
Inv_List:=p_1;
loop
i:=Instr(Inv_List,',');
if i=0 then
Inv:=Inv_List;
else
Inv:=substr(Inv_List,1,i-1);
end if;
select oh_qty into cur_qty from b where inv_code=Inv and item_code=item;
select min_qty,max_qty into min_qty,max_qty from a where inv_code=Inv;
if cur_qty<min_qty then
insert into lack_temp values(inv,min_qty-cur_qty);
elsif cur_qty>max_qty then
insert into over_temp values(inv,cur_qty-max_qty);
end if;
commit;
if i=0 then
exit;
end if;
Inv_List:=substr(Inv_List,i+1);
end loop;
open over_cur;
fetch over_cur into over_rec;
over_qty:=over_rec.qty;
over_invcode:=over_rec.inv_code;
open lack_cur;
fetch lack_cur into lack_rec;
lack_qty:=lack_rec.qty;
lack_invcode:=lack_rec.inv_code;
loop
if over_qty>lack_qty then
str:=Item||' '||over_invcode||' '||lack_invcode||' '||to_char(lack_qty);
over_qty:=over_qty-lack_qty;
dbms_output.put_line(str);
utl_file.put_line(fHandle,str);
fetch lack_cur into lack_rec;
exit when lack_cur%notfound;
lack_qty:=lack_rec.qty;
lack_invcode:=lack_rec.inv_code;
else
str:=Item||' '||over_invcode||' '||lack_invcode||' '||to_char(over_qty);
lack_qty:=lack_qty-over_qty;
dbms_output.put_line(str);
utl_file.put_line(fHandle,str);
fetch over_cur into over_rec;
exit when over_cur%notfound;
over_qty:=over_rec.qty;
over_invcode:=over_rec.inv_code;
end if;
end loop;
close lack_cur;
close over_cur;
if pos=0 then
exit;
end if;
Item_List:=substr(Item_List,pos+1);
end loop;
utl_file.fclose(fHandle); exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;