oracle 基本知识示例

创建行类型

创建行类型
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;