Oracle之存储过程和ADO.NET的结合
在利用WCF服务模式实现数据层和数据库交换中，输入参数和输出参数也是要注意的一部分，这次主要是记录在一个输入参数和一个数组形式的输出参数的存储过程如何与ADO.NET实现数据信息交互。
C#实现的底层服务，代码如下：
/// <summary>
/// 撤销合并账单
/// </summary>
/// <param name="tgtBillId">目标账单</param>
/// <returns>撤销之后生成的账单</returns>
public string[] RestoreBill(string tgtBillId)
{
List<string> billIds = new List<string>();
P2Logger.Debug("SettlementBill", "RestoreBill begin...");
using (OracleConnection cnn = ConnectionExpert.GetConnection())
{
OracleCommand updCmd = cnn.CreateCommand();
updCmd.CommandType = CommandType.StoredProcedure;
updCmd.CommandText = "FMS_SETTLEMENT.p_restroe_bill";
int[] bindSize = new int[500];
string[] tblData = new string[500];
for (int ii = 0; ii < 500; ii++)
{
tblData[ii] = ii.ToString();//数值
bindSize[ii] = 500;//数值的大小
}
updCmd.Parameters.Add("VA_BILL_ID", OracleDbType.Varchar2, 2048);
updCmd.Parameters["VA_BILL_ID"].Direction = ParameterDirection.Output;
updCmd.Parameters["VA_BILL_ID"].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
updCmd.Parameters["VA_BILL_ID"].Size = 500;
updCmd.Parameters["VA_BILL_ID"].ArrayBindSize = bindSize;
updCmd.Parameters.Add("vv_bill_id", OracleDbType.Varchar2).Value = tgtBillId;
OracleTransaction tran = cnn.BeginTransaction();
try
{
ConnectionExpert.ExecuteNonQuery(updCmd);
tran.Commit();
foreach (string item in (Oracle.DataAccess.Types.OracleString[])updCmd.Parameters["VA_BILL_ID"].Value)
{
billIds.Add(item);
}
P2Logger.Info("SettlementBill", "RestoreBill success!");
return billIds.ToArray();
}
catch (Exception ex)
{
tran.Rollback();
P2Logger.Debug("SettlementBill", ex.Message);
P2Logger.Debug("SettlementBill", ex.StackTrace);
FreightException fe = new FreightException(FreightException.E_DATABASE, "合并账单失败。");
throw new FaultException<FreightException>(fe, fe.Message);
}
}
}
相关的数据库存储过程如下所示;
----恢复账单
PROCEDURE p_restroe_bill (va_bill_id OUT str_array, vv_bill_id VARCHAR2)
AS
vr_new_bill fi_acc_bill%ROWTYPE;
CURSOR c
IS
SELECT bill_id FROM fi_acc_bill; --第一行数据
BEGIN
---查询被合并的账单编号
SELECT DISTINCT b.bill_id
BULK COLLECT INTO va_bill_id
FROM fi_acc_fee_bak b, fi_acc_fee f
WHERE b.fee_id = f.fee_id AND f.bill_id = vv_bill_id;
---循环处理被合并账单编号
FOR i IN 1 .. va_bill_id.COUNT
LOOP
---从备份账单表移动到账单表
INSERT INTO fi_acc_bill (bill_id,
job_id,
job_type,
confirm_flag,
confirm_time,
confirm_oper,
cust_id,
pay_period,
bill_type,
direction,
inv_num,
invoice_rise,
currency,
amount,
verify_balance,
fx_amount,
fiscal_period,
blunt_flag,
fx_rate,
firm,
bank_acc,
dept_id,
book_flag,
book_date,
remark,
create_by,
create_date,
verify_time,
pay_id,
security,
profit_loses,
verify_count,
inv_cargo,
inv_fee,
inv_unit_price,
inv_quantity,
inv_currency,
inv_amount,
inv_currency_convert,
inv_amout_convert,
inv_remark,
inv_oper,
inv_require,
inv_vessel,
inv_voyage,
inv_mblno,
inv_hblno,
inv_loading,
inv_discharge,
inv_etd,
prefix)
SELECT bill_id,
job_id,
job_type,
confirm_flag,
confirm_time,
confirm_oper,
cust_id,
pay_period,
bill_type,
direction,
inv_num,
invoice_rise,
currency,
amount,
verify_balance,
fx_amount,
fiscal_period,
blunt_flag,
fx_rate,
firm,
bank_acc,
dept_id,
book_flag,
book_date,
remark,
create_by,
create_date,
verify_time,
pay_id,
security,
profit_loses,
verify_count,
inv_cargo,
inv_fee,
inv_unit_price,
inv_quantity,
inv_currency,
inv_amount,
inv_currency_convert,
inv_amout_convert,
inv_remark,
inv_oper,
inv_require,
inv_vessel,
inv_voyage,
inv_mblno,
inv_hblno,
inv_loading,
inv_discharge,
inv_etd,
prefix
FROM fi_acc_bill_bak
WHERE bill_id = va_bill_id (i);
----拆分账单费用的账单编号
UPDATE fi_acc_fee f
SET f.bill_id =
(SELECT bill_id
FROM fi_acc_fee_bak b
WHERE f.fee_id = b.fee_id)
WHERE f.fee_id IN (SELECT fee_id
FROM fi_acc_fee_bak b
WHERE bill_id = va_bill_id (i));
----拆分费用的账单编号
UPDATE fe_fee f
SET f.bill_id =
(SELECT bill_id
FROM fi_acc_fee_bak b
WHERE f.fee_id = b.fee_id)
WHERE f.fee_id IN (SELECT fee_id
FROM fi_acc_fee_bak b
WHERE bill_id = va_bill_id (i));
---删除备份账单费用表（从表）
DELETE fi_acc_fee_bak b
WHERE bill_id = va_bill_id (i);
---删除备份账单表（主表）
DELETE fi_acc_bill_bak
WHERE bill_id = va_bill_id (i);
END LOOP;
---更新指定账单编号的金额
UPDATE fi_acc_bill
SET pay_period = fms_const.pay_period_singlejob,
amount =
(SELECT ROUND (SUM (f.amount), 2)
FROM fe_fee f, fi_acc_fee i
WHERE i.bill_id = vv_bill_id AND f.fee_id = i.fee_id)
WHERE bill_id = vv_bill_id;
---更新指定账单编号的外币金额
UPDATE fi_acc_bill
SET pay_period = fms_const.pay_period_singlejob,
fx_amount =
(SELECT ROUND (SUM (f.fx_amout), 2)
FROM fe_fee f, fi_acc_fee i
WHERE i.bill_id = vv_bill_id AND f.fee_id = i.fee_id)
WHERE bill_id = vv_bill_id;
---更新指定账单编号的核销余额
UPDATE fi_acc_bill
SET pay_period = fms_const.pay_period_singlejob,
verify_balance =
(SELECT ROUND (SUM (f.verify_balance), 2)
FROM fe_fee f, fi_acc_fee i
WHERE i.bill_id = vv_bill_id AND f.fee_id = i.fee_id)
WHERE bill_id = vv_bill_id;
-- DELETE fi_acc_fee_bak
-- WHERE bill_id IN
-- (SELECT DISTINCT b.bill_id
-- FROM fi_acc_fee_bak b, fi_acc_fee f
-- WHERE b.fee_id = f.fee_id AND f.bill_id = vv_bill_id);
-- --改变账单付款周期状态：月结改为单票
-- UPDATE fi_acc_bill
-- SET pay_period = fms_const.pay_period_singlejob
-- WHERE bill_id = vv_bill_id;
--
-- ---根据账单编号查询被合并的账单编号
-- SELECT bill_id
-- INTO v_content
-- FROM fi_acc_fee_bak
-- WHERE fee_id IN (SELECT fee_id
-- FROM fi_acc_fee
-- WHERE bill_id = vv_bill_id);
--
--
--
-- IF va_bill_id.COUNT > 0
-- THEN
-- FOR i IN 1 .. va_bill_id.COUNT
-- LOOP
-- ---循环把查询出被合并的账单编号添加到FI_ACC_BILL表中，将每条账单的付款周期改为单票
-- SELECT *
-- INTO vr_new_bill
-- FROM fi_acc_bill_bak
-- WHERE bill_id = va_bill_id (i);
--
-- ---向备份账单表添加源账单数据
-- INSERT INTO fi_acc_bill (bill_id,
-- job_id,
-- job_type,
-- confirm_flag,
-- confirm_time,
-- confirm_oper,
-- cust_id,
-- pay_period,
-- bill_type,
-- direction,
-- inv_num,
-- invoice_rise,
-- currency,
-- amount,
-- verify_balance,
-- fx_amount,
-- fiscal_period,
-- blunt_flag,
-- fx_rate,
-- firm,
-- bank_acc,
-- dept_id,
-- book_flag,
-- book_date,
-- remark,
-- create_by,
-- create_date,
-- verify_time,
-- pay_id,
-- security,
-- profit_loses,
-- verify_count,
-- inv_cargo,
-- inv_fee,
-- inv_unit_price,
-- inv_quantity,
-- inv_currency,
-- inv_amount,
-- inv_currency_convert,
-- inv_amout_convert,
-- inv_remark,
-- inv_oper,
-- inv_require,
-- inv_vessel,
-- inv_voyage,
-- inv_mblno,
-- inv_hblno,
-- inv_loading,
-- inv_discharge,
-- inv_etd,
-- prefix)
-- SELECT bill_id,
-- job_id,
-- job_type,
-- confirm_flag,
-- confirm_time,
-- confirm_oper,
-- cust_id,
-- pay_period,
-- bill_type,
-- direction,
-- inv_num,
-- invoice_rise,
-- currency,
-- amount,
-- verify_balance,
-- fx_amount,
-- fiscal_period,
-- blunt_flag,
-- fx_rate,
-- firm,
-- bank_acc,
-- dept_id,
-- book_flag,
-- book_date,
-- remark,
-- create_by,
-- create_date,
-- verify_time,
-- pay_id,
-- security,
-- profit_loses,
-- verify_count,
-- inv_cargo,
-- inv_fee,
-- inv_unit_price,
-- inv_quantity,
-- inv_currency,
-- inv_amount,
-- inv_currency_convert,
-- inv_amout_convert,
-- inv_remark,
-- inv_oper,
-- inv_require,
-- inv_vessel,
-- inv_voyage,
-- inv_mblno,
-- inv_hblno,
-- inv_loading,
-- inv_discharge,
-- inv_etd,
-- prefix
-- FROM fi_acc_bill_bak
-- WHERE bill_id = va_bill_id (i);
--
-- -------并减去每个被合并账单的本位币金额、外币金额、核销余额
-- ---更新账单表
-- UPDATE fi_acc_bill
-- SET amount = amount - vr_new_bill.amount,
-- verify_balance =
-- verify_balance - vr_new_bill.verify_balance,
-- fx_amount = fx_amount - vr_new_bill.fx_amount
-- WHERE bill_id = vv_bill_id;
--
-- --更新FI_ACC_FEE和FE_FEE中对应的BILL_ID
-- ---根据从备份费用表FI_ACC_FEE_BAK中查询所得的BILL_ID ,然后查询出Fee_ID
-- UPDATE fi_acc_fee
-- SET bill_id = va_bill_id (i)
-- WHERE fee_id = (SELECT fee_id
-- FROM fi_acc_fee_bak
-- WHERE bill_id = va_bill_id (i));
--
-- UPDATE fe_fee
-- SET bill_id = va_bill_id (i)
-- WHERE fee_id = (SELECT fee_id
-- FROM fi_acc_fee_bak
-- WHERE bill_id = va_bill_id (i));
--
-- --删除备份账单费用FI_ACC_FEE中被合并的数据信息
-- DELETE FROM fi_acc_fee_bak
-- WHERE fee_id = (SELECT fee_id
-- FROM fi_acc_fee_bak
-- WHERE bill_id = va_bill_id (i));
--
-- --删除备份账单表FI_ACC_BILL中被合并的数据信息
-- DELETE FROM fi_acc_bill_bak
-- WHERE bill_id = va_bill_id (i);
-- END LOOP;
--
-- NULL;
-- END IF;
END;
一些相关PL/SQL查询语句，鉴之勉之:)
/* Formatted on 2012/3/23 11:24:36 (QP5 v5.149.1003.31008) */
SELECT *
FROM mat_inventory
WHERE (quot_id || loc_id) IN (SELECT (quot_id || loc_id)
FROM (SELECT quot_id, loc_id, ROWNUM rn
FROM ( SELECT quot_id, loc_id
FROM mat_inventory
ORDER BY quot_id ASC))
WHERE rn = 5);
--固定数组
declare
type type_array is varray(10) of varchar2(20);
var_array type_array:=type_array('ggs','jjh','wsb','csl','dd','bb');
.begin
for i in 1..var_array.count loop
dbms_output.put_line(var_array(i));
end loop;
end;
--可变数组
declare
type type_array is table of varchar2(20) index by binary_integer;
var_array type_array;
begin
var_array(1):='aa';
var_array(2):='bb';
for i in 1..var_array.count loop
dbms_output.put_line( var_array(i));
end loop;
end;
--可变数组取表
declare
begin
end;
create or replace procedure proc_stock(n number)
as
var_stock_code varchar2(10);
var_stock_price number;
begin
for i in 1..n loop
var_stock_code:= lpad(STR1 =>i ,LEN =>6 ,PAD =>'0' ) ;
var_stock_price:=trunc(dbms_random.value*100)+1;
--dbms_output.put_line(var_stock_code);
--dbms_output.put_line(var_stock_price);
insert into t_stock (stockcode,stockprice)
values(var_stock_code,var_stock_price);
commit;
end loop;
end;
declare
begin
proc_stock(1000000);
end;
--用游标访问 14.578秒 13.5 13.8
declare
cursor cur is select * from t_stock;
row_stock t_stock%rowtype;
begin
open cur;
loop
fetch cur into row_stock;
exit when cur%notfound;
null;
end loop;
close cur;
end;
--用数组实现 4.813 1.953 2
declare
type type_array is table of t_stock%rowtype index by binary_integer;
var_array type_array;
begin
select * bulk collect into var_array from t_stock;
for i in 1..var_array.count loop
null;
end loop;
end;
--访问自定义表
declare
type type_record is record(
username varchar2(20),
sex varchar2(2)
);
type_record_user type_record;
type type_array is table of type_record_user%type index by binary_integer;
var_array type_array;
begin
select username,sex bulk collect into var_array from tuser;
for i in 1..var_array.count loop
dbms_output.put_line(var_array(i).username);
dbms_output.put_line(var_array(i).sex);
end loop;
end;
浙公网安备 33010602011771号