创建行类型
创建行类型
CREATE OR REPLACE TYPE "ROW_PRODUCTC" as object
(
rowsCount int,
floor varchar2(10), -- ???????n????
cargo_group_id varchar2(20), -- ???????n???M????
cargo_class varchar2(20), -- ???????n???e
model varchar2(20), -- ????Model
model_cell varchar2(20), -- ????????
goodsShelves_id varchar2(20), -- ???????n??????
task_no varchar2(20),
bin_id varchar2(20),
bin_qty varchar2(2),
qty varchar2(10),
createDate varchar2(30),
object_type varchar2(10)
)
创建表类型
创建表类型 ROW_PRODUCTC 是 行类型名称
CREATE OR REPLACE TYPE "TABLE_PRODUCTC" as table of ROW_PRODUCTC
创建存储过程
创建存储过程
create or replace procedure IMPORT_ERP_BOM(CopEmsNo varchar2,PLANT varchar2) is
begin
delete from custom_erp_bom b
where b.cop_ems_no = CopEmsNo and b.plant = plant;
end;
创建方法
创建方法
create or replace function escape(ConventString in nvarchar2)
return varchar2 is
FunctionResult varchar2(50);
begin
FunctionResult := utl_url.escape(ConventString, TRUE, 'GB2312');
return FunctionResult;
end escape;
方法中使用表数据类型, 将表类型的数据输出
create or replace function Query_Table_ProductC
(
p_floor in varchar2 := ':?',
p_cargogroup in varchar2 := ':?',
p_cargoclass in varchar2 := ':?',
p_model in varchar2 := ':?',
p_modelcell in varchar2 := ':?',
p_goodsShelves_id in varchar2 := ':?',
p_task_no in varchar2 := ':?',
p_bin_id in varchar2 := ':?',
p_bin_qty in varchar2 := ':?',
p_qty in varchar2 := ':?',
"limit" int := 1,
page int := 10
)
return TABLE_PRODUCTC pipelined as
type_row ROW_PRODUCTC;
begin
declare rowsCount int;
begin
select count(*) into rowsCount from product_c p where 1=1
and (p_floor = ':?' OR p.floor like '%' || p_floor || '%')
and (p_cargogroup = ':?' OR p.cargogroup like '%' || p_cargogroup || '%')
and (p_cargoclass = ':?' OR p.cargoclass like '%' || p_cargoclass || '%')
and (p_model = ':?' OR p.model like '%' || p_model || '%')
and (p_modelcell = ':?' OR p.modelcell like '%' || p_modelcell || '%')
and (p_goodsShelves_id = ':?' OR p.Goodsshelves_Id like '%' || p_goodsShelves_id || '%')
and (p_task_no = ':?' OR p.task_no like '%' || p_task_no || '%')
and (p_bin_id = ':?' OR p.bin_id like '%' || p_bin_id || '%')
and (p_bin_qty = ':?' OR p.bin_qty like '%' || p_bin_qty || '%')
and (p_qty = ':?' OR p.qty like '%' || p_qty || '%')
;
DBMS_OUTPUT.PUT_LINE(rowsCount || p_bin_id);
for r in (select * from(
select p.*,rownum rn from product_c p where 1=1
and (p_floor = ':?' OR p.floor like '%' || p_floor || '%')
and (p_cargogroup = ':?' OR p.cargogroup like '%' || p_cargogroup || '%')
and (p_cargoclass = ':?' OR p.cargoclass like '%' || p_cargoclass || '%')
and (p_model = ':?' OR p.model like '%' || p_model || '%')
and (p_modelcell = ':?' OR p.modelcell like '%' || p_modelcell || '%')
and (p_goodsShelves_id = ':?' OR p.Goodsshelves_Id like '%' || p_goodsShelves_id || '%')
and (p_task_no = ':?' OR p.task_no like '%' || p_task_no || '%')
and (p_bin_id = ':?' OR p.bin_id like '%' || p_bin_id || '%')
and (p_bin_qty = ':?' OR p.bin_qty like '%' || p_bin_qty || '%')
and (p_qty = ':?' OR p.qty like '%' || p_qty || '%')
) p2 where to_number(p2.rn) > (("limit"-1) * page)
and to_number(p2.rn) <= ("limit" * page)
order by CREATEDATE desc
)
loop
DBMS_OUTPUT.PUT_LINE(rowsCount);
type_row := ROW_PRODUCTC(rowsCount,r.floor,r.cargogroup,r.cargoclass,r.model,r.modelcell,r.goodsshelves_id,r.task_no,r.bin_id,r.bin_qty,r.qty,r.createdate,r.object_type);
--type_row := ROW_PRODUCTC('1','2','3','4','5','6','7','8','9','10');
pipe row(type_row);
end loop;
end;
return;
end;
使用存储过程
使用存储过程
declare
copemsno varchar(50) := 'H221820A0014';
plant varchar(50) := 'WE01';
begin
import_erp_bom( copemsno,
plan);
end;