包含游标、数组的例子
设计分析：
代码：
1 create or replace package PACK_ZD is 2 3 --网上商城平台ID 4 ZD_ONLINE_SHOP_MAIMAICHA CONSTANT VARCHAR2(10) := 'shopup008'; --买卖茶商城ID 5 6 --网上商城平台付款方式ID 7 ZD_PAYMENT_CASH_ON_DELIVERY CONSTANT VARCHAR2(10) := '001'; --货到付款ID 8 9 --快递公司的名称 10 ZD_GLOGISTICS_COM_ZHAIJISONG CONSTANT VARCHAR2(20) := '宅急送'; 11 ZD_GLOGISTICS_COM_ZHONGTONG CONSTANT VARCHAR2(20) := '中通'; 12 ZD_GLOGISTICS_COM_YUANTONG CONSTANT VARCHAR2(20) := '圆通'; 13 ZD_GLOGISTICS_COM_PINGYOU CONSTANT VARCHAR2(20) := '平邮'; 14 ZD_GLOGISTICS_COM_YUNDA CONSTANT VARCHAR2(20) := '韵达'; 15 16 17 --说明：在字典档案管理的物流公司配送范围中，找出首重费用最低的快递公司 18 --作者：杨斌 19 --日期：2013-02-19 20 Procedure PRO_ZD_GET_GLOGISTICS_ID_NAME( 21 prm_pro_id varchar2, --省份编号 22 prm_online_shop_id varchar2, --网上商城平台ID 23 prm_payment_style_id varchar2, --网上商城平台付款方式ID 24 25 out_log_com_id out varchar2, --物流公司编号 26 out_log_com_name out varchar2, --物流公司名称 27 out_error_row out number, --错误行 28 out_error_msg out varchar2 --错误信息 29 ); 30 31 end PACK_ZD; 32 / 33 create or replace package body PACK_ZD is 34 --说明：在字典档案管理的物流公司配送范围中，找出首重费用最低的快递公司 35 --作者：杨斌 36 --日期：2013-02-19 37 Procedure PRO_ZD_GET_GLOGISTICS_ID_NAME( 38 prm_pro_id varchar2, --省份编号 39 prm_online_shop_id varchar2, --网上商城平台ID 40 prm_payment_style_id varchar2, --网上商城平台付款方式ID 41 42 out_log_com_id out varchar2, --物流公司编号 43 out_log_com_name out varchar2, --物流公司名称 44 out_error_row out number, --错误行 45 out_error_msg out varchar2 --错误信息 46 ) 47 AS 48 49 tmp_pro_log_id t_base_provincewithlogistics.pro_log_id%type; --声明[省份快递公司标识符编号],临时存放 50 --initial_fee_one t_base_logistics_fare.initial_fee%type; --声明[首重费用] 51 52 CURSOR provincewithlogistics_cur IS --声明一个游标，用于从[省份与快递公司关联表]中读取[省份快递公司标识符编号]集 53 select t.pro_log_id from t_base_provincewithlogistics t 54 where t.pro_id = prm_pro_id; 55 56 TYPE logistics_fare_record is RECORD --声明一个记录,用于存取[物流公司资费表]的[省份快递公司标识符编号]、[首重费用] 57 ( 58 pro_log_id t_base_provincewithlogistics.pro_log_id%type, --[省份快递公司标识符编号] 59 initial_fee t_base_logistics_fare.initial_fee%type --[首重费用] 60 ); 61 62 --声明一个数组,用于存取[物流公司资费表]的[省份快递公司标识符编号]、[首重费用] 63 TYPE logistics_fare_array is TABLE of logistics_fare_record INDEX BY BINARY_INTEGER; 64 65 --实例对象 66 logistics_fare_rec_array logistics_fare_array; 67 logistics_fare_rec logistics_fare_record; 68 69 indexLogistics number(5); --用于存取数组'logistics_fare_array'的索引 70 71 --str_initial_fee t_base_logistics_fare.initial_fee%type; --临时变量，[首重费用] 72 --str_pro_log_id t_base_provincewithlogistics.pro_log_id%type; --临时变量，[省份快递公司标识符编号] 73 mix_fee t_base_logistics_fare.initial_fee%type; --临时变量，[首重费用]最小值] 74 mix_fee_pro_log_id t_base_provincewithlogistics.pro_log_id%type; --临时变量，[省份快递公司标识符编号] 75 76 BEGIN 77 out_error_row := 0; 78 out_error_msg := ''; 79 indexLogistics := 1; 80 mix_fee := 0; 81 82 83 --网上商城平台ID是“买卖茶商城ID”并且付款方式ID是“货到付款”对应的ID 84 if (prm_online_shop_id = ZD_ONLINE_SHOP_MAIMAICHA) and ( prm_payment_style_id = ZD_PAYMENT_CASH_ON_DELIVERY) then 85 86 select NVL(t.log_com_id,''),NVL(t.log_com_name,'') into out_log_com_id,out_log_com_name from t_base_logisticscominfo t 87 where t.log_com_name like '%' || ZD_GLOGISTICS_COM_ZHAIJISONG || '%' and 88 rownum=1 89 order by t.log_com_id; 90 return; 91 92 else 93 94 --省份编号有值的情况 95 if prm_pro_id is not null then 96 97 OPEN provincewithlogistics_cur; 98 LOOP 99 100 FETCH provincewithlogistics_cur into tmp_pro_log_id; 101 102 EXIT WHEN NOT provincewithlogistics_cur%FOUND; 103 104 select NVL(min(t.initial_fee),0.0000) into logistics_fare_rec.initial_fee from t_base_logistics_fare t 105 where t.pro_log_id = tmp_pro_log_id; 106 107 logistics_fare_rec.pro_log_id := tmp_pro_log_id; 108 109 --保存到数组'logistics_fare_rec_array'里 110 logistics_fare_rec_array(indexLogistics) := logistics_fare_rec; 111 indexLogistics := indexLogistics + 1; 112 END LOOP; 113 114 CLOSE provincewithlogistics_cur; 115 end if; 116 117 --从数组'logistics_fare_rec_array'中查找出，首重费用最低的记录 118 mix_fee := logistics_fare_rec_array(1).initial_fee; 119 mix_fee_pro_log_id := logistics_fare_rec_array(1).pro_log_id; 120 FOR i IN 2..logistics_fare_rec_array.count LOOP 121 122 if mix_fee > logistics_fare_rec_array(i).initial_fee then 123 mix_fee := logistics_fare_rec_array(i).initial_fee; 124 mix_fee_pro_log_id := logistics_fare_rec_array(i).pro_log_id; 125 end if; 126 127 END LOOP; 128 129 --获取首重费用最低的快递公司Id和对应的快递公司名称 130 select t.log_com_id,t.log_com_name into out_log_com_id,out_log_com_name from t_base_logisticscominfo t 131 where t.log_com_id = ( 132 select t.log_com_id from t_base_provincewithlogistics t 133 where t.pro_log_id = mix_fee_pro_log_id and 134 rownum=1 135 ); 136 137 end if; 138 139 out_error_row := 100; 140 141 exception 142 when others then 143 out_error_msg := '数据库错误:' || sqlerrm; 144 145 END PRO_ZD_GET_GLOGISTICS_ID_NAME; 146 end PACK_ZD; 147 /
③ 所用到的表的SQL语句：
1 -- Create table 2 create table T_BASE_PROVINCEWITHLOGISTICS 3 ( 4 PRO_LOG_ID VARCHAR2(20) not null, 5 PRO_ID VARCHAR2(6), 6 LOG_COM_ID VARCHAR2(60), 7 REMARK VARCHAR2(200) 8 ) 9 tablespace USERS 10 pctfree 10 11 initrans 1 12 maxtrans 255 13 storage 14 ( 15 initial 64K 16 next 8K 17 minextents 1 18 maxextents unlimited 19 ); 20 -- Add comments to the table 21 comment on table T_BASE_PROVINCEWITHLOGISTICS 22 is '省份与快递公司关联表'; 23 -- Add comments to the columns 24 comment on column T_BASE_PROVINCEWITHLOGISTICS.PRO_LOG_ID 25 is '省份快递公司标识符编号'; 26 comment on column T_BASE_PROVINCEWITHLOGISTICS.PRO_ID 27 is '省份编号'; 28 comment on column T_BASE_PROVINCEWITHLOGISTICS.LOG_COM_ID 29 is '物流公司编号'; 30 comment on column T_BASE_PROVINCEWITHLOGISTICS.REMARK 31 is '备注'; 32 -- Create/Recreate primary, unique and foreign key constraints 33 alter table T_BASE_PROVINCEWITHLOGISTICS 34 add constraint PK_PRO_LOG_ID primary key (PRO_LOG_ID) 35 using index 36 tablespace USERS 37 pctfree 10 38 initrans 2 39 maxtrans 255 40 storage 41 ( 42 initial 64K 43 next 1M 44 minextents 1 45 maxextents unlimited 46 ); 47 alter table T_BASE_PROVINCEWITHLOGISTICS 48 add constraint FK_PRO_LOG_LOG_COM_ID foreign key (LOG_COM_ID) 49 references T_BASE_LOGISTICSCOMINFO (LOG_COM_ID); 50 alter table T_BASE_PROVINCEWITHLOGISTICS 51 add constraint FK_PRO_LOG_PRO_ID foreign key (PRO_ID) 52 references T_BASE_PROVINCE (PRO_ID);
1 -- Create table 2 create table T_BASE_LOGISTICSCOMINFO 3 ( 4 LOG_COM_ID VARCHAR2(60) not null, 5 LOG_COM_NAME VARCHAR2(90), 6 LINKMAN VARCHAR2(30), 7 PHONE VARCHAR2(15), 8 MOBILE VARCHAR2(15), 9 EMAIL VARCHAR2(150), 10 FAX VARCHAR2(20), 11 ADDRESS VARCHAR2(150), 12 SHORT VARCHAR2(20), 13 REMARK VARCHAR2(600), 14 LOG_STA_ID VARCHAR2(20), 15 NOW_OFF_ON CHAR(1) 16 ) 17 tablespace SYSTEM 18 pctfree 10 19 pctused 40 20 initrans 1 21 maxtrans 255 22 storage 23 ( 24 initial 64K 25 next 8K 26 minextents 1 27 maxextents unlimited 28 ); 29 -- Add comments to the columns 30 comment on column T_BASE_LOGISTICSCOMINFO.LOG_COM_ID 31 is '物流公司编号'; 32 comment on column T_BASE_LOGISTICSCOMINFO.LOG_COM_NAME 33 is '物流公司名称'; 34 comment on column T_BASE_LOGISTICSCOMINFO.LINKMAN 35 is '联系人'; 36 comment on column T_BASE_LOGISTICSCOMINFO.PHONE 37 is '固定电话'; 38 comment on column T_BASE_LOGISTICSCOMINFO.MOBILE 39 is '手机'; 40 comment on column T_BASE_LOGISTICSCOMINFO.EMAIL 41 is '邮箱'; 42 comment on column T_BASE_LOGISTICSCOMINFO.FAX 43 is '联系传真'; 44 comment on column T_BASE_LOGISTICSCOMINFO.ADDRESS 45 is '地址'; 46 comment on column T_BASE_LOGISTICSCOMINFO.SHORT 47 is '拼音简称'; 48 comment on column T_BASE_LOGISTICSCOMINFO.REMARK 49 is '备注'; 50 comment on column T_BASE_LOGISTICSCOMINFO.LOG_STA_ID 51 is '物流状态编号'; 52 comment on column T_BASE_LOGISTICSCOMINFO.NOW_OFF_ON 53 is '当前是否可用(0可用/1)'; 54 -- Create/Recreate primary, unique and foreign key constraints 55 alter table T_BASE_LOGISTICSCOMINFO 56 add constraint PK_BASE_LOGISTICSCOMINFO primary key (LOG_COM_ID) 57 using index 58 tablespace SYSTEM 59 pctfree 10 60 initrans 2 61 maxtrans 255 62 storage 63 ( 64 initial 64K 65 next 1M 66 minextents 1 67 maxextents unlimited 68 ); 69 -- Create/Recreate indexes 70 create index IDX_LOGISTICSNAME on T_BASE_LOGISTICSCOMINFO (LOG_COM_NAME) 71 tablespace SYSTEM 72 pctfree 10 73 initrans 2 74 maxtrans 255 75 storage 76 ( 77 initial 64K 78 next 1M 79 minextents 1 80 maxextents unlimited 81 ); 82 create index IDX_LOGISTICSSHORT on T_BASE_LOGISTICSCOMINFO (SHORT) 83 tablespace SYSTEM 84 pctfree 10 85 initrans 2 86 maxtrans 255 87 storage 88 ( 89 initial 64K 90 next 1M 91 minextents 1 92 maxextents unlimited 93 );
1 -- Create table 2 create table T_BASE_LOGISTICS_FARE 3 ( 4 LOGISTICS_FARE_ID VARCHAR2(20) not null, 5 PRO_LOG_ID VARCHAR2(20), 6 INITIAL_WEIGHT NUMBER(18,4), 7 INITIAL_FEE NUMBER(18,4), 8 ADD_WEIGHT NUMBER(18,4), 9 ADD_FEE NUMBER(18,4), 10 LOST_PAYMENT NUMBER(18,4), 11 DAMAGE_PAYMENT NUMBER(18,4), 12 PRIME_COST NUMBER(18,4), 13 ADD_COST NUMBER(18,4), 14 REMARK VARCHAR2(200) 15 ) 16 tablespace SYSTEM 17 pctfree 10 18 pctused 40 19 initrans 1 20 maxtrans 255 21 storage 22 ( 23 initial 64K 24 next 8K 25 minextents 1 26 maxextents unlimited 27 ); 28 -- Add comments to the columns 29 comment on column T_BASE_LOGISTICS_FARE.LOGISTICS_FARE_ID 30 is '资费编号'; 31 comment on column T_BASE_LOGISTICS_FARE.PRO_LOG_ID 32 is '省份快递公司标识符编号'; 33 comment on column T_BASE_LOGISTICS_FARE.INITIAL_WEIGHT 34 is '首重 （单位：克）'; 35 comment on column T_BASE_LOGISTICS_FARE.INITIAL_FEE 36 is '首费 （单位：元）'; 37 comment on column T_BASE_LOGISTICS_FARE.ADD_WEIGHT 38 is '续重（单位：克）'; 39 comment on column T_BASE_LOGISTICS_FARE.ADD_FEE 40 is '续费 （单位：元）'; 41 comment on column T_BASE_LOGISTICS_FARE.LOST_PAYMENT 42 is '丢单赔付'; 43 comment on column T_BASE_LOGISTICS_FARE.DAMAGE_PAYMENT 44 is '破损赔付'; 45 comment on column T_BASE_LOGISTICS_FARE.PRIME_COST 46 is '首重成本价格（单位：元）'; 47 comment on column T_BASE_LOGISTICS_FARE.ADD_COST 48 is '续重成本价格（单位：元）'; 49 comment on column T_BASE_LOGISTICS_FARE.REMARK 50 is '备注'; 51 -- Create/Recreate primary, unique and foreign key constraints 52 alter table T_BASE_LOGISTICS_FARE 53 add constraint PK_BASE_LOGISTICSFARE_ID primary key (LOGISTICS_FARE_ID) 54 using index 55 tablespace SYSTEM 56 pctfree 10 57 initrans 2 58 maxtrans 255 59 storage 60 ( 61 initial 64K 62 next 1M 63 minextents 1 64 maxextents unlimited 65 ); 66 alter table T_BASE_LOGISTICS_FARE 67 add constraint FK_BASE_LOGISTICSFARE_ID foreign key (PRO_LOG_ID) 68 references T_BASE_PROVINCEWITHLOGISTICS (PRO_LOG_ID);
1 -- Create table 2 create table T_BASE_LOGISTICSCOMINFO 3 ( 4 LOG_COM_ID VARCHAR2(60) not null, 5 LOG_COM_NAME VARCHAR2(90), 6 LINKMAN VARCHAR2(30), 7 PHONE VARCHAR2(15), 8 MOBILE VARCHAR2(15), 9 EMAIL VARCHAR2(150), 10 FAX VARCHAR2(20), 11 ADDRESS VARCHAR2(150), 12 SHORT VARCHAR2(20), 13 REMARK VARCHAR2(600), 14 LOG_STA_ID VARCHAR2(20), 15 NOW_OFF_ON CHAR(1) 16 ) 17 tablespace SYSTEM 18 pctfree 10 19 pctused 40 20 initrans 1 21 maxtrans 255 22 storage 23 ( 24 initial 64K 25 next 8K 26 minextents 1 27 maxextents unlimited 28 ); 29 -- Add comments to the columns 30 comment on column T_BASE_LOGISTICSCOMINFO.LOG_COM_ID 31 is '物流公司编号'; 32 comment on column T_BASE_LOGISTICSCOMINFO.LOG_COM_NAME 33 is '物流公司名称'; 34 comment on column T_BASE_LOGISTICSCOMINFO.LINKMAN 35 is '联系人'; 36 comment on column T_BASE_LOGISTICSCOMINFO.PHONE 37 is '固定电话'; 38 comment on column T_BASE_LOGISTICSCOMINFO.MOBILE 39 is '手机'; 40 comment on column T_BASE_LOGISTICSCOMINFO.EMAIL 41 is '邮箱'; 42 comment on column T_BASE_LOGISTICSCOMINFO.FAX 43 is '联系传真'; 44 comment on column T_BASE_LOGISTICSCOMINFO.ADDRESS 45 is '地址'; 46 comment on column T_BASE_LOGISTICSCOMINFO.SHORT 47 is '拼音简称'; 48 comment on column T_BASE_LOGISTICSCOMINFO.REMARK 49 is '备注'; 50 comment on column T_BASE_LOGISTICSCOMINFO.LOG_STA_ID 51 is '物流状态编号'; 52 comment on column T_BASE_LOGISTICSCOMINFO.NOW_OFF_ON 53 is '当前是否可用(0可用/1)'; 54 -- Create/Recreate primary, unique and foreign key constraints 55 alter table T_BASE_LOGISTICSCOMINFO 56 add constraint PK_BASE_LOGISTICSCOMINFO primary key (LOG_COM_ID) 57 using index 58 tablespace SYSTEM 59 pctfree 10 60 initrans 2 61 maxtrans 255 62 storage 63 ( 64 initial 64K 65 next 1M 66 minextents 1 67 maxextents unlimited 68 ); 69 -- Create/Recreate indexes 70 create index IDX_LOGISTICSNAME on T_BASE_LOGISTICSCOMINFO (LOG_COM_NAME) 71 tablespace SYSTEM 72 pctfree 10 73 initrans 2 74 maxtrans 255 75 storage 76 ( 77 initial 64K 78 next 1M 79 minextents 1 80 maxextents unlimited 81 ); 82 create index IDX_LOGISTICSSHORT on T_BASE_LOGISTICSCOMINFO (SHORT) 83 tablespace SYSTEM 84 pctfree 10 85 initrans 2 86 maxtrans 255 87 storage 88 ( 89 initial 64K 90 next 1M 91 minextents 1 92 maxextents unlimited 93 );
