CREATE OR REPLACE PACKAGE CURSPKG AS
TYPE refCursorType IS REF CURSOR;
procedure sp_Page(p_PageSize int, --每页记录数
p_PageNo int, --当前页码,从 1 开始
p_SqlSelect varchar2, --查询语句,含排序部分
p_SqlCount varchar2, --获取记录总数的查询语句
p_OutRecordCount out int, --返回总记录数
test out varchar,
p_OutCursor out refCursorType);
--4.1客户信息校验接口
procedure YG001_Login(CustomerinId in varchar,
p_PageNo in int,
Rd1 in varchar,
Rd2 in varchar,
test out varchar,
p_OutCursor out refCursorType);
procedure YG002_GetSecCustInfo(CustomerinId in varchar,
p_PageNo in int,
Rd1 in varchar,
Rd2 in varchar,
test out varchar,
p_OutCursor out refCursorType);
procedure YG003_GetCustInfo(p_PageNo in int,
Rd1 in varchar,
Rd2 in varchar,
test out varchar,
p_OutCursor out refCursorType);
procedure YG101_GetProductInfo(p_PageNo in int,
Rd1 in varchar,
Rd2 in varchar,
test out varchar,
p_OutCursor out refCursorType);
---4.5企业产品分类日终同步接口D00100
procedure YG100_GetCategoryInfo(p_PageNo in int,
Rd1 in varchar,
Rd2 in varchar,
test out varchar,
p_OutCursor out refCursorType) ;
procedure YG404_InsertPayInfo(SecCustomerId in varchar,
SecCustomerName in varchar,
PaymentId in varchar,
PaymentDate in varchar,
PayMethod in varchar,
PayAmount in varchar,
Bank in varchar,
AccountNo in varchar,
Remark in varchar,
Rd1 in varchar,
Rd2 in varchar,
icbcSecCustomerId out varchar,
icbcPaymentId out varchar);
procedure YG301_InsertOnlineOrder(partPopedom_xml in clob,test out varchar,res out int);
procedure YG302_GetOrderInfo(OrderId in varchar,
Rd1 in varchar,
Rd2 in varchar,
test out varchar,
p_OutCursor out refCursorType);
procedure YG501_GetCustomerBalance(customerid in varchar,
startdate in varchar,
enddate in varchar,
test out clob,
p_OutCursor out refCursorType);
end;
create or replace package body CURSPKG is
procedure sp_Page(p_PageSize int, --每页记录数
p_PageNo int, --当前页码,从 1 开始
p_SqlSelect varchar2, --查询语句,含排序部分
p_SqlCount varchar2, --获取记录总数的查询语句
p_OutRecordCount out int, --返回总记录数
test out varchar,
p_OutCursor out refCursorType) is
v_sql varchar2(3000);
v_count int;
v_heiRownum int;
v_lowRownum int;
begin
----取记录总数
execute immediate p_SqlCount
into v_count;
p_OutRecordCount := v_count;
----执行分页查询
v_heiRownum := p_PageNo * p_PageSize;
v_lowRownum := v_heiRownum - p_PageSize + 1;
v_sql := 'SELECT * FROM (SELECT A.*, rownum rn
FROM (' || p_SqlSelect || ') A
WHERE rownum <= ' || to_char(v_heiRownum) || ') B
WHERE rn >= ' || to_char(v_lowRownum);
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
OPEN p_OutCursor FOR v_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE(SQLERRM);
test:=SQLERRM;
end sp_Page;
--4.1客户信息校验接口
procedure YG001_Login(CustomerinId in varchar,
p_PageNo in int,
Rd1 in varchar,
Rd2 in varchar,
test out varchar,
p_OutCursor out refCursorType)is
v_sql varchar2(3000);
v_count int;
begin
v_sql := 'select distinct nvl(a.fnumber,'''') CustomerId,nvl(a.fname_l2,'''') CustomerName,nvl(c.FContactPerson,'''') ContactName,nvl(c.FMobile,'''') CustomerTel from t_bd_customer a
left join t_bd_customersaleinfo b on a.fid = b.fcustomerid
left join T_BD_CustomerLinkMan c on b.fid = c.FCustomerSaleID
where a.fparentid is null and a.fnumber=''' || CustomerinId || '''' ;
sp_page(p_pagesize => 100,
p_pageno => p_PageNo,
p_sqlselect => v_sql,
p_sqlcount => 'select 1 from dual ',
p_outrecordcount => v_count,
test => test,
p_outcursor => p_OutCursor);
test := v_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE(SQLERRM);
test:=SQLERRM;
end YG001_Login;
procedure YG002_GetSecCustInfo(CustomerinId in varchar,
p_PageNo in int,
Rd1 in varchar,
Rd2 in varchar,
test out varchar,
p_OutCursor out refCursorType) is
v_sql varchar2(3000);
v_count int;
begin
v_sql := 'select distinct nvl(a.fnumber,'''') CustomerId,0 CustomerDebt ,
nvl(sec.fnumber,'''') SecCustomerId,nvl(sec.fname_l2,'''') SecCustomerName,
nvl(c.FContactPerson,'''') SecContactName,nvl(c.FMobile,'''') SecCustomerTel ,
0 SecCustomerDebt ,nvl(e.fname_l2,'''') Province,nvl(f.fname_l2,'''') Area
from t_bd_customer a
left join t_bd_customer sec on sec.fparentid = a.fid
left join t_bd_customersaleinfo b on sec.fid = b.fcustomerid
left join T_BD_CustomerLinkMan c on b.fid = c.FCustomerSaleID
left join T_BD_Province e on sec.FProvince = e.fid
left join T_BD_Region f on sec.FRegionID = f.fid
where a.fcontrolunitid in (''00000000-0000-0000-0000-000000000000CCE7AED4'',''sREAAAAAx2PM567U'') and b.fcontrolunitid in (''00000000-0000-0000-0000-000000000000CCE7AED4'',''sREAAAAAx2PM567U'') and a.fnumber=''' || CustomerinId || '''' ;
sp_page(p_pagesize => 100,
p_pageno => p_PageNo,
p_sqlselect => v_sql,
p_sqlcount => 'select 1 from dual ',
p_outrecordcount => v_count,
test => test,
p_outcursor => p_OutCursor);
test := v_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE(SQLERRM);
test:=SQLERRM;
end YG002_GetSecCustInfo;
procedure YG003_GetCustInfo(p_PageNo in int,
Rd1 in varchar,
Rd2 in varchar,
test out varchar,
p_OutCursor out refCursorType) is
v_sql varchar2(6000);
v_count int;
begin
v_sql := 'select distinct nvl(a.fnumber,'''') CustomerId,nvl(a.fname_l2,'''') CustomerName ,
nvl(e.fname_l2,'''') Province,nvl(f.fname_l2,'''') Area ,
nvl(sec.fnumber,'''') ParentId,nvl(sec.fname_l2,'''') ParentName,
nvl(c.FContactPerson,'''') ContactName,nvl(c.FMobile,'''') CustomerTel,
nvl(e.fname_l2,'''') fProvince,nvl(f.fname_l2,'''') fArea
from t_bd_customer a
left join t_bd_customer sec on a.fparentid = sec.fid
left join t_bd_customersaleinfo b on a.fid = b.fcustomerid
left join T_BD_CustomerLinkMan c on b.fid = c.FCustomerSaleID
left join T_BD_Province e on a.FProvince = e.fid
left join T_BD_Region f on a.FRegionID = f.fid
where a.fcontrolunitid in (''00000000-0000-0000-0000-000000000000CCE7AED4'',''sREAAAAAx2PM567U'') and b.fcontrolunitid in (''00000000-0000-0000-0000-000000000000CCE7AED4'',''sREAAAAAx2PM567U'') ' ;
sp_page(p_pagesize => 100,
p_pageno => p_PageNo,
p_sqlselect => v_sql,
p_sqlcount => 'select 1 from dual ',
p_outrecordcount => v_count,
test => test,
p_outcursor => p_OutCursor);
test := v_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE(SQLERRM);
test:=SQLERRM;
end YG003_GetCustInfo;
procedure YG101_GetProductInfo(p_PageNo in int,
Rd1 in varchar,
Rd2 in varchar,
test out varchar,
p_OutCursor out refCursorType) is
v_sql varchar2(3000);
v_count int;
begin
v_sql := 'select nvl(a.fnumber,'''') ProductId,nvl(a.fname_l2,'''') ProductName,nvl(FModel,'''') Specification,a.falias falias,
nvl(unit1.fname_l2,'''') Unit,unit2.fname_l2 baseUnit,a.FHelpCode StandardLen ,nvl(mgroup.fnumber,'''') CategoryId,
nvl(a.fname_l2,'''') Brand
from t_bd_material a
left join T_BD_MeasureUnit unit1 on a.FAssistUnit = unit1.fid
left join T_BD_MeasureUnit unit2 on a.FBaseUnit = unit2.fid
left join T_BD_MaterialGroup mgroup on a.FMaterialGroupID = mgroup.fid
left join T_BD_MaterialGroupStandard c on mgroup.FGroupStandard = c.fid and c.FStandardType=1
left join T_BD_MaterialSales d on d.FMaterialID = a.fid
where a.FStatus = 1 and a.fcontrolunitid in (''00000000-0000-0000-0000-000000000000CCE7AED4'',''sREAAAAAx2PM567U'')
and d.fcontrolunitid = ''sREAAAAAx2PM567U'' ';
sp_page(p_pagesize => 100,
p_pageno => p_PageNo,
p_sqlselect => v_sql,
p_sqlcount => 'select 1 from dual ',
p_outrecordcount => v_count,
test => test,
p_outcursor => p_OutCursor);
test := v_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE(SQLERRM);
test:=SQLERRM;
end YG101_GetProductInfo;
---4.5企业产品分类日终同步接口D00100
procedure YG100_GetCategoryInfo(p_PageNo in int,
Rd1 in varchar,
Rd2 in varchar,
test out varchar,
p_OutCursor out refCursorType) is
v_sql varchar2(3000);
v_count int;
begin
v_sql := 'select distinct nvl(a.fnumber,'''') CategoryId,nvl(a.fname_l2,'''') CategoryName,nvl(a.FLevel,'''') fLevel, case when b.fid is null then 1 else 0 end HasChild
from T_BD_MaterialGroup a
left join T_BD_MaterialGroup b on b.FParentID = a.fid and b.FDeletedStatus =1
left join T_BD_MaterialGroupStandard c on a.FGroupStandard = c.fid and c.FStandardType=1
where a.FDeletedStatus =1 and a.fcontrolunitid in (''00000000-0000-0000-0000-000000000000CCE7AED4'',''sREAAAAAx2PM567U'') ' ;
sp_page(p_pagesize => 100,
p_pageno => p_PageNo,
p_sqlselect => v_sql,
p_sqlcount => 'select 1 from dual ',
p_outrecordcount => v_count,
test => test,
p_outcursor => p_OutCursor);
test := v_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE(SQLERRM);
test:=SQLERRM;
end YG100_GetCategoryInfo;
--工行提交订单接口D00301
/*procedure YG301_InsertOnlineOrder_old(OrderId in varchar,
CustomerId in varchar,
SecCustomerId in varchar,
SecCustomerName in varchar,
CreateTime in varchar,
Address in varchar,
ShipMethod in varchar,
Contact in varchar,
ContactNum in varchar,
ToDate in varchar,
Remark in varchar,
Rd1 in varchar,
Rd2 in varchar,
OrderEntry in varchar,
ProductId in varchar,
ProductName in varchar,
Specification in varchar,
Unit in varchar,
StandardLen in varchar,
icbcCount in varchar,
res out int)is
v_sql varchar2(3000);
v_count int;
begin
v_sql := 'select 1 from dual ' ;
\* IF OrderEntry=1 THEN
;
ELSE
;
END IF;*\
end YG301_InsertOnlineOrder_old; */
procedure YG302_GetOrderInfo(OrderId in varchar,
Rd1 in varchar,
Rd2 in varchar,
test out varchar,
p_OutCursor out refCursorType) is
v_sql varchar2(3000);
v_count int;
begin
v_sql := 'select a.cforderid OrderId,a.cfbillstate billstatus,
c.fnumber OrderCode,
c.FBaseStatus OrderStatus,
c.FCreateTime CreateTime,
c.FBizDate BusinessDate,
h.fnumber SecCustomerId,
h.fname_l2 SecCustomerName,
i.fnumber CompanyId,
i.fname_l2 CompanyName,
c.FTotalAmount SumTotal,
c.FLinkMan Contact,
c.FCustomerPhone ContactNum,
c.fdescription Remark,
f.fnumber DeliveryOrderId,
d.fseq OrderEntry,
j.fnumber ProductId,
j.fname_l2 ProductName,
j.FModel Specification,
k.fname_l2 Unit,
j.FHelpCode StandardLen,
d.FQty fCount,
d.FPlanDeliveryQty ArrangedQty,
d.FTotalIssueBaseQty OutedQty
from CT_CUS_ICBC_SALEORDER a
left outer join T_BOT_Relation b on a.fid = b.fsrcobjectid
left outer join T_SD_SaleOrder c on b.fdestobjectid = c.fid
left outer join t_sd_saleorderentry d on c.fid = d.fparentid
left outer join T_BOT_Relation e on c.fid = e.fsrcobjectid
left outer join T_DT_CarryBill f on e.fdestobjectid = f.fid
left outer join T_BD_Customer h on c.FOrderCustomerID = h.fid
left outer join T_ORG_Sale i on c.fsaleorgunitid = i.fid
left outer join t_bd_material j on j.fid = d.fmaterialid
left outer join T_BD_MeasureUnit k on j.FAssistUnit = k.fid
left outer join T_BD_MeasureUnit l on j.FBaseUnit = l.fid
where a.cforderid=''' || OrderId || ''' order by c.fnumber,d.fseq ' ;
sp_page(p_pagesize => 100,
p_pageno => 1,
p_sqlselect => v_sql,
p_sqlcount => 'select 1 from dual ',
p_outrecordcount => v_count,
test => test,
p_outcursor => p_OutCursor);
test := v_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE(SQLERRM);
test:=SQLERRM;
end YG302_GetOrderInfo;
procedure YG404_InsertPayInfo(SecCustomerId in varchar,
SecCustomerName in varchar,
PaymentId in varchar,
PaymentDate in varchar,
PayMethod in varchar,
PayAmount in varchar,
Bank in varchar,
AccountNo in varchar,
Remark in varchar,
Rd1 in varchar,
Rd2 in varchar,
icbcSecCustomerId out varchar,
icbcPaymentId out varchar)is
v_sql varchar2(3000);
v_count int;
exist int;
begin
select count(fid) into exist from CT_CUS_Icbc_payment where cfpaymentid = paymentid ;
if exist is not null and exist > 0 then
icbcSecCustomerId := 'error.billno exist';
else
insert into CT_CUS_Icbc_payment(fid,CFSecCustomerId,cfPaymentId,
cfPaymentDate,cfPayMethod,cfPayAmount,cfBank,cfAccountNo,cfRemark,cfRd1,cfRd2,
FCREATORID,FCREATETIME,FLASTUPDATEUSERID,FLASTUPDATETIME,FCONTROLUNITID)
values(newbosid('B33B6367'),
''||SecCustomerId||'',
''||PaymentId||'',
''||PaymentDate||'',
''||PayMethod||'',
''||PayAmount||'',
''||Bank||'',
''||AccountNo||'',
''||Remark||'',
''||Rd1||'',
''||Rd2||'',
'256c221a-0106-1000-e000-10d7c0a813f413B7DE7F',
sysdate,'256c221a-0106-1000-e000-10d7c0a813f413B7DE7F',
sysdate,'00000000-0000-0000-0000-000000000000CCE7AED4');
commit;
icbcSecCustomerId := SecCustomerId;
icbcPaymentId := PaymentId;
end if;
Exception
When others then
Dbms_output.put_line(sqlcode||sqlerrm(sqlcode));
icbcPaymentId := SQLERRM;
end YG404_InsertPayInfo;
procedure YG301_InsertOnlineOrder(partPopedom_xml in clob,test out varchar,res out int)
as
OrderId varchar2(100);
CustomerId varchar2(100);
SecCustomerId varchar2(100);
SecCustomerName varchar2(100);
CreateTime varchar2(100);
Address varchar2(100);
ShipMethod varchar2(100);
Contact varchar2(100);
ContactNum varchar2(100);
ToDate varchar2(100);
Remark varchar2(100);
Rd1 varchar2(100);
Rd2 varchar2(100);
OrderEntry varchar2(100);
ProductId varchar2(100);
ProductName varchar2(100);
Specification varchar2(100);
Unit varchar2(100);
StandardLen varchar2(100);
icbcCount varchar2(100);
materialid varchar2(100);
PARENTID varchar2(100);
exist int;
fcustmoerid varchar2(100);
--XML解析器
xmlPar XMLPARSER.parser := XMLPARSER.NEWPARSER;
--//DOM文档对象
doc xmldom.DOMDocument;
len Integer;
personNodes xmldom.DOMNodeList;
chilNodes xmldom.DOMNodeList;
tempNode xmldom.DOMNode;
tempArrMap xmldom.DOMNamedNodeMap;
--================================
--以下变量用于获取XML节点的值
/* partNum varchar2(50); --角色编号
menuNum varchar2(50); --菜单编号
operateNum varchar2(50); --操作编号*/
tmp Integer;
rootnode dbms_xmldom.domnode;
--================
BEGIN
xmlPar := xmlparser.newParser;
xmlparser.parseClob(xmlPar,partPopedom_xml);
doc := xmlparser.getDocument(xmlPar);
-- 释放解析器实例
xmlparser.freeParser(xmlPar);
personNodes := xmldom.getElementsByTagName(doc, 'ProductInfo');
len := xmldom.getLength( personNodes );
SELECT EXTRACTVALUE(VALUE(t),'/in/OrderId'),
EXTRACTVALUE(VALUE(t),'/in/CustomerId'),
EXTRACTVALUE(VALUE(t),'/in/SecCustomerId'),
EXTRACTVALUE(VALUE(t),'/in/SecCustomerName'),
EXTRACTVALUE(VALUE(t),'/in/CreateTime'),
EXTRACTVALUE(VALUE(t),'/in/Address'),
EXTRACTVALUE(VALUE(t),'/in/ShipMethod'),
EXTRACTVALUE(VALUE(t),'/in/Contact'),
EXTRACTVALUE(VALUE(t),'/in/ContactNum'),
EXTRACTVALUE(VALUE(t),'/in/ToDate'),
EXTRACTVALUE(VALUE(t),'/in/Remark'),
EXTRACTVALUE(VALUE(t),'/in/Rd1'),
EXTRACTVALUE(VALUE(t),'/in/Rd2')
INTO OrderId,
CustomerId,
SecCustomerId,
SecCustomerName,
CreateTime,
Address,
ShipMethod,
Contact,
ContactNum,
ToDate,
Remark,
Rd1,
Rd2
FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(partPopedom_xml), '/in'))) t;
select count(fid) into exist from CT_CUS_ICBC_SALEORDER where CFORDERID = OrderId ;
select fid into fcustmoerid from T_BD_Customer where fnumber=SecCustomerId and fcontrolunitid in ('00000000-0000-0000-0000-000000000000CCE7AED4','sREAAAAAB9LM567U');
if exist is not null and exist > 0 then
test := 'error.billno exist';
else
PARENTID := newbosid('D1F43888');
--insert maintable data
insert into CT_CUS_ICBC_SALEORDER(fid,CFORDERID,CFICBCCUSTOMERID,
CFSECCUSTOMERID,CFICBCCREATETIME,CFADDRESS,CFSHIPMETHOD,CFCONTACT,
CFCONTACTNUM,CFTODATE,CFREMARK,CFRD1,CFRD2,FBIZDATE,CFREQDATE,
CFCUSTOMERID,
FCREATORID,FCREATETIME,FLASTUPDATEUSERID,FLASTUPDATETIME,FCONTROLUNITID)
values(PARENTID,
''||OrderId||'',
''||CustomerId||'',
''||SecCustomerId||'',
''||CreateTime||'',
''||Address||'',
''||ShipMethod||'',
''||Contact||'',
''||ContactNum||'',
''||ToDate||'',
''||Remark||'',
''||Rd1||'',
''||Rd2||'',
sysdate,
to_timestamp(ToDate,'''yyyyMMdd'''),
''||fcustmoerid||'',
'256c221a-0106-1000-e000-10d7c0a813f413B7DE7F',
sysdate,'256c221a-0106-1000-e000-10d7c0a813f413B7DE7F',
sysdate,'00000000-0000-0000-0000-000000000000CCE7AED4');
--遍历所有PERSON元素
FOR i in 0..len-1
LOOP
--获取第i个
tempNode := xmldom.item(personNodes, i);
--所有属性
tempArrMap := xmldom.getAttributes(tempNode);
--获取PERSONID的值
--pid := xmldom.getNodeValue(xmldom.getNamedItem(tempArrMap,'POPEDOM'));
--获取子元素的值
chilNodes := xmldom.getChildNodes(tempNode);
tmp := xmldom.GETLENGTH(chilNodes);
OrderEntry := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 0)));
ProductId := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 1)));
ProductName := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 2)));
Specification := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 3)));
Unit := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 4)));
StandardLen := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 5)));
icbcCount := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 6)));
--translate number to id
select fid into materialid from t_bd_material where fnumber=ProductId and fcontrolunitid in ('00000000-0000-0000-0000-000000000000CCE7AED4','sREAAAAAB9LM567U');
--插入数据
insert into CT_CUS_ICBC_SALEORDERENTRY(fid,FPARENTID,FSEQ,CFORDERENTRY,CFPRODUCTID,CFSTANDARDLEN,CFUNIT,CFCOUNT,Cfnum,CFMATERIALID)
values(newbosid('C201F4CA'),
''||PARENTID||'',
i+1,
''||OrderEntry||'',
''||ProductId||'',
''||StandardLen||'',
''||Unit||'',
icbcCount,
icbcCount,
''||materialid||'');
END LOOP;
COMMIT;
end if ;
-- 释放文档对象
xmldom.freeDocument(doc);
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE(SQLERRM);
test:=SQLERRM;
END YG301_InsertOnlineOrder;
procedure YG501_GetCustomerBalance(customerid in varchar,
startdate in varchar,
enddate in varchar,
test out clob,
p_OutCursor out refCursorType) is
v_sql clob;
v_count int;
begin
v_sql := 'select fcussnum,fcussname,sum(FStartBalanceAmt) FStartBalanceAmt, sum(FDebitAmt) FDebitAmt, sum(FCreditAmt) FCreditAmt, sum(FBalanceAmt) FBalanceAmt
from (
select t4.FName_l2 FCurrency, t2.FId FCussTypeId, t2.FName_l2 FCussType
, t3.FNumber FCussNum, t3.FName_l2 FCussName, t3.FId FCussAcctId, t1.FCurrencyId FCurrencyId
, sum(t1.FBeginBalanceFor) FStartBalanceAmt, 0 FDebitAmt, 0 FCreditAmt, 0 FBalanceAmt
from t_ar_arBalance t1 left join t_bd_AsstActType t2 on t2.FId = t1.FAsstActTypeId
inner join t_bd_customer t3 on t3.FId = t1.FAsstActId left join t_bd_currency t4 on t4.FId = t1.FCurrencyId
where 1 = 1 and FBalType IN (70,100) and (t1.FAsstActTypeId = ''YW3xsAEJEADgAAUWwKgTB0c4VZA='') and t1.FCompanyid = ''sREAAAAAx2PM567U''
and t1.FPeriodId = ''sREAAAAmbTOCOIxM''
group by t2.FId, t2.FName_l2 , t3.FNumber, t3.FName_l2 , t1.FAsstActId, t4.FName_l2 , t1.FCurrencyId, t3.FId
union all
select t4.FName_l2 FCurrency, t2.FId FCussTypeId, t2.FName_l2 FCussType
, t3.FNumber FCussNum, t3.FName_l2 FCussName, t3.FId FCussAcctId, t1.FCurrencyId FCurrencyId
, -1 * sum(t1.FBeginBalanceFor) FStartBalanceAmt, 0 FDebitAmt, 0 FCreditAmt, 0 FBalanceAmt
from t_ap_apBalance t1 left join t_bd_AsstActType t2 on t2.FId = t1.FAsstActTypeId
inner join t_bd_customer t3 on t3.FId = t1.FAsstActId left join t_bd_currency t4 on t4.FId = t1.FCurrencyId
where 1 = 1 and FBalType IN (70,100) and (t1.FAsstActTypeId = ''YW3xsAEJEADgAAUWwKgTB0c4VZA='') and t1.FCompanyid = ''sREAAAAAx2PM567U''
and t1.FPeriodId = ''sREAAAAmbTOCOIxM''
group by t2.FId, t2.FName_l2 , t3.FNumber, t3.FName_l2 , t1.FAsstActId, t4.FName_l2 , t1.FCurrencyId, t3.FId
union all
select t4.FName_l2 FCurrency, t2.FId FCussTypeId, t2.FName_l2 FCussType
, t3.FNumber FCussNum, t3.FName_l2 FCussName, t3.FId FCussAcctId, t1.FCurrencyId FCurrencyId
, sum(t1.FAmounts) FStartBalanceAmt, 0 FDebitAmt, 0 FCreditAmt, 0 FBalanceAmt
from T_AR_InitBadAccount t1 left join t_bd_AsstActType t2 on t2.FId = t1.FAsstActTypeID
left join t_bd_customer t3 on t3.FId = t1.FAcctCussentID
left join t_bd_currency t4 on t4.FId = t1.FCurrencyId
where 1= 1 and t1.FAcctCussentID in (select FId from t_bd_customer)
and (t1.FAsstActTypeID = ''YW3xsAEJEADgAAUWwKgTB0c4VZA='') and t1.FCompanyid = ''sREAAAAAx2PM567U''
group by t2.FId, t2.FName_l2 , t3.FNumber, t3.FName_l2 , t3.FId, t4.FName_l2 , t1.FCurrencyId
union all
select t4.FName_l2 FCurrency, t2.FId FCussTypeId
, t2.FName_l2 FCussType, t3.FNumber FCussNum, t3.FName_l2 FCussName, t3.FId FCussAcctId
, t4.FId FCurrencyId, 0 FStartBalanceAmt, sum(t1.FReceiving) FDebitAmt, sum(t1.FActual) FCreditAmt, 0 FBalanceAmt
from
(select t1.FCompanyId FCompanyId, t1.FAsstActID FCUSTOMERID, t1.FCurrencyId FCURRENCYID, t1.FBillDate FBILLDATE
, t1.FAmount FRECEIVING, 0 FActual , t1.FAuditorID FAUDITORID, t1.FAsstActTypeID FASSTACTTYPEID
from T_AR_OTHERBILL t1
inner join t_bd_customer tCus on tCus.FId = t1.FAsstActID
where 1= 1 and t1.FBillStatus = 3 AND t1.FIsBizBill = 0 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBillDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBillDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select t1.FCompanyId FCompanyId, t1.FPayerID FCUSTOMERID, t1.FCurrencyID FCURRENCYID, t1.FBizDate FBILLDATE
, 0 FRECEIVING, t1.FAmount FActual , t1.FAuditorID FAUDITORID, t1.FPayerTypeID FASSTACTTYPEID
from T_CAS_ReceivingBill t1
inner join t_bd_customer tCus on tCus.FId = t1.FPayerID
where 1= 1 and t1.FBillStatus >= 14 and t1.FSourceType = 100 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Main FCUSTOMERID, t1.FCurrencyID_Main FCURRENCYID, t1.FBizDate FBILLDATE
, 0 FRECEIVING, t1.FThisVerificateAmt_Main FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Main FASSTACTTYPEID
from T_AR_VerificationBill t1
inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Main
where 1= 1 and ((t1.FVerificationType = 101 or t1.FVerificationType = 107 or t1.FVerificationType = 103 or t1.FVerificationType = 109) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Second FCUSTOMERID, t1.FCurrencyID_Second FCURRENCYID, t1.FBizDate FBILLDATE
, t1.FThisVerificateAmt_Second FRECEIVING, 0 FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Second FASSTACTTYPEID
from T_AR_VerificationBill t1
inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Second
where 1= 1 and ((t1.FVerificationType = 101 or t1.FVerificationType = 107 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Main FCUSTOMERID, t1.FCurrencyID_Main FCURRENCYID, t1.FBizDate FBILLDATE
, t1.FThisVerificateAmt_Main FRECEIVING, 0 FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Main FASSTACTTYPEID
from T_AR_VerificationBill t1
inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Main
where 1= 1 and ((t1.FVerificationType = 104 or t1.FVerificationType = 110 or t1.FVerificationType = 113 or t1.FVerificationType = 114) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Second FCUSTOMERID, t1.FCurrencyID_Second FCURRENCYID, t1.FBizDate FBILLDATE
, 0 FRECEIVING, t1.FThisVerificateAmt_Second FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Second FASSTACTTYPEID
from T_AP_VerificationBill t1
inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Second
where 1= 1 and ((t1.FVerificationType = 203 or t1.FVerificationType = 209 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Second FCUSTOMERID, t1.FCurrencyID_Second FCURRENCYID, t1.FBizDate FBILLDATE
, t1.FThisVerificateAmt_Second FRECEIVING, 0 FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Second FASSTACTTYPEID
from T_AP_VerificationBill t1
inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Second
where 1= 1 and ((t1.FVerificationType = 204 or t1.FVerificationType = 210 or t1.FVerificationType = 211 or t1.FVerificationType = 212) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select A.FCompanyId FCompanyId, B.FAsstActId FCUSTOMERID, A.FCurrencyId FCURRENCYID, A.FBillDate FBILLDATE,
0 FRECEIVING, A.FAmount FActual , A.FAuditorID FAUDITORID, B.FAsstActTypeId FASSTACTTYPEID
from T_AR_OTHERBILL A INNER JOIN T_AR_OTHERBILL B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId
inner join t_bd_customer tCus on tCus.FId = B.FAsstActId
where 1=1 and A.FCompanyId = ''sREAAAAAx2PM567U''
and A.FBillDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and A.FBillDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
and A.FCurrencyId = B.FCurrencyId and B.FCompanyId = ''sREAAAAAx2PM567U'' and B.FBillDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
and B.FBillStatus = 3 and (A.FIsReverseBill = 0 and A.FIsTransBill = 1 and A.FBillStatus = 3 )
union all
select A.FCompanyId FCompanyId, B.FPayerID FCUSTOMERID, A.FCurrencyId FCURRENCYID, A.FBizDate FBILLDATE,
A.FAmount FRECEIVING, 0 FActual , A.FAuditorID FAUDITORID, B.FPayerTypeID FASSTACTTYPEID
from T_CAS_ReceivingBill A INNER JOIN T_CAS_ReceivingBill B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId
inner join t_bd_customer tCus on tCus.FId = B.FPayerID
where 1=1 and A.FCompanyId = ''sREAAAAAx2PM567U''
and A.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and A.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
and A.FCurrencyId = B.FCurrencyId and B.FCompanyId = ''sREAAAAAx2PM567U'' and B.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
and B.FSourceType= 100 and B.FBillStatus>= 14 AND A.FSourceType= 100 and A.FBillStatus>= 14 and A.FIsTransBill = 1
union all
select A.FCompanyId FCompanyId, B.FAsstActID FCUSTOMERID, A.FCurrencyId FCURRENCYID, A.FBizDate FBILLDATE,
0 AS FRECEIVING, A.FAmount As FActual , A.FAuditorID FAUDITORID, B.FAsstActTypeID FASSTACTTYPEID
from T_CAS_PaymentBill A INNER JOIN T_AR_OtherBill B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId
inner join t_bd_customer tCus on tCus.FId = B.FAsstActID
where 1=1 and A.FCompanyId = ''sREAAAAAx2PM567U''
and A.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and A.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
and A.FCurrencyId = B.FCurrencyId and B.FCompanyId = ''sREAAAAAx2PM567U'' and B.FBillDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
and A.FSourceType= 101 and A.FBillStatus>= 15 AND B.FBillStatus= 3 and A.FIsTransOtherBill = 1
) t1 left join t_bd_AsstActType t2 on t2.FId = t1.FAsstActTypeId
left join t_bd_customer t3 on t3.FId = t1.FCustomerId left join t_bd_currency t4 on t4.FId = t1.FCurrencyId
group by t2.FId, t2.FName_l2 , t3.FNumber, t3.FName_l2 , t3.FId, t4.FName_l2 , t4.FId
union all
select t4.FName_l2 FCurrency, t2.FId FCussTypeId
, t2.FName_l2 FCussType, t3.FNumber FCussNum, t3.FName_l2 FCussName, t3.FId FCussAcctId
, t4.FId FCurrencyId, 0 FStartBalanceAmt, sum(t1.FActual) FDebitAmt, sum(t1.FPayment) FCreditAmt, 0 FBalanceAmt
from
(select t1.FCompanyId FCompanyId, t1.FAsstActID FSUPPLIERID, t1.FCurrencyID FCURRENCYID, t1.FBillDate FBILLDATE
, t1.FAmount FPayment, 0 FActual , t1.FAuditorID FAUDITORID, t1.FAsstActTypeID FASSTACTTYPEID
from T_AP_OTHERBILL t1
inner join t_bd_customer tCus on tCus.FId = t1.FAsstActID
where 1= 1 and t1.FBillStatus = 3 AND t1.FIsBizBill = 0 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBillDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBillDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select t1.FCompanyId FCompanyId, t1.FPayeeID FSUPPLIERID, t1.FCurrencyID FCURRENCYID, t1.FBizDate FBILLDATE
, 0 FPAYMENT, t1.FAmount FActual , t1.FAuditorID FAUDITORID, t1.FPayeeTypeID FASSTACTTYPEID
from T_CAS_PaymentBill t1
inner join t_bd_customer tCus on tCus.FId = t1.FPayeeID
where 1= 1 and t1.FBillStatus >= 15 and t1.FSourceType = 101 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Main FSUPPLIERID, t1.FCurrencyID_Main FCURRENCYID, t1.FBizDate FBILLDATE
, 0 FPayment, t1.FThisVerificateAmt_Main FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Main FASSTACTTYPEID
from T_AP_VerificationBill t1
inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Main
where 1= 1 and ((t1.FVerificationType = 201 or t1.FVerificationType = 207 or t1.FVerificationType = 203 or t1.FVerificationType = 209) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Second FSUPPLIERID, t1.FCurrencyID_Second FCURRENCYID, t1.FBizDate FBILLDATE
, t1.FThisVerificateAmt_Second FPayment, 0 FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Second FASSTACTTYPEID
from T_AP_VerificationBill t1
inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Second
where 1= 1 and ((t1.FVerificationType = 201 or t1.FVerificationType = 207 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Main FSUPPLIERID, t1.FCurrencyID_Main FCURRENCYID, t1.FBizDate FBILLDATE
, t1.FThisVerificateAmt_Main FPayment, 0 FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Main FASSTACTTYPEID
from T_AP_VerificationBill t1
inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Main
where 1= 1 and ((t1.FVerificationType = 204 or t1.FVerificationType = 210 or t1.FVerificationType = 211 or t1.FVerificationType = 212) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Second FSUPPLIERID, t1.FCurrencyID_Second FCURRENCYID, t1.FBizDate FBILLDATE
, 0 FPayment, t1.FThisVerificateAmt_Second FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Second FASSTACTTYPEID
from T_AR_VerificationBill t1
inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Second
where 1= 1 and ((t1.FVerificationType = 103 or t1.FVerificationType = 109 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Second FSUPPLIERID, t1.FCurrencyID_Second FCURRENCYID, t1.FBizDate FBILLDATE
, t1.FThisVerificateAmt_Second FPayment, 0 FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Second FASSTACTTYPEID
from T_AR_VerificationBill t1
inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Second
where 1= 1 and ((t1.FVerificationType = 104 or t1.FVerificationType = 110 or t1.FVerificationType = 113 or t1.FVerificationType = 114) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
and t1.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and t1.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
union all
select A.FCompanyId FCompanyId, B.FAsstActId FSUPPLIERID, A.FCurrencyId FCURRENCYID, A.FBillDate FBILLDATE,
0 FPayment, A.FAmount FActual , A.FAuditorID FAUDITORID, B.FAsstActTypeId FASSTACTTYPEID
from T_AP_OTHERBILL A INNER JOIN T_AP_OTHERBILL B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId
inner join t_bd_customer tCus on tCus.FId = B.FAsstActId
where 1=1 and A.FCompanyId = ''sREAAAAAx2PM567U''
and A.FBillDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and A.FBillDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
and A.FCurrencyId = B.FCurrencyId and B.FCompanyId = ''sREAAAAAx2PM567U'' and B.FBillDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
and B.FBillStatus = 3 and (A.FIsReverseBill = 0 and A.FIsTransBill = 1 and A.FBillStatus = 3 )
union all
select A.FCompanyId FCompanyId, B.FPayeeID FSUPPLIERID, A.FCurrencyId FCURRENCYID, A.FBizDate FBILLDATE,
A.FAmount FPayment, 0 FActual , A.FAuditorID FAUDITORID, B.FPayeeTypeID FASSTACTTYPEID
from T_CAS_PaymentBill A INNER JOIN T_CAS_PaymentBill B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId
inner join t_bd_customer tCus on tCus.FId = B.FPayeeID
where 1=1 and A.FCompanyId = ''sREAAAAAx2PM567U''
and A.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and A.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
and A.FCurrencyId = B.FCurrencyId and B.FCompanyId = ''sREAAAAAx2PM567U'' and B.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
and B.FSourceType= 101 and B.FBillStatus>= 15 AND A.FSourceType= 101 and A.FBillStatus>= 15 and A.FIsTransBill = 1
union all
select A.FCompanyId FCompanyId, B.FAsstActID FSUPPLIERID, A.FCurrencyId FCURRENCYID, A.FBizDate FBILLDATE,
0 AS FPayment, A.FAmount As FActual , A.FAuditorID FAUDITORID, B.FAsstActTypeID FASSTACTTYPEID
from T_CAS_ReceivingBill A INNER JOIN T_AP_OtherBill B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId
inner join t_bd_customer tCus on tCus.FId = B.FAsstActID
where 1=1 and A.FCompanyId = ''sREAAAAAx2PM567U''
and A.FBizDate >= to_date(''' || startdate || ''',''yyyy-mm-dd'') and A.FBizDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
and A.FCurrencyId = B.FCurrencyId and B.FCompanyId = ''sREAAAAAx2PM567U'' and B.FBillDate < to_date(''' || enddate || ''',''yyyy-mm-dd'')
and A.FSourceType= 100 and A.FBillStatus>= 14 AND B.FBillStatus= 3 and A.FIsTransOtherBill = 1
) t1 left join t_bd_AsstActType t2 on t2.FId = t1.FAsstActTypeId
left join t_bd_customer t3 on t3.FId = t1.FSupplierId left join t_bd_currency t4 on t4.FId = t1.FCurrencyId
group by t2.FId, t2.FName_l2 , t3.FNumber, t3.FName_l2 , t3.FId, t4.FName_l2 , t4.FId
)
where fcussnum = '''|| customerid || '''
group by fcussnum,fcussname ' ;
OPEN p_OutCursor FOR v_sql;
test := v_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE(SQLERRM);
test:=SQLERRM;
end YG501_GetCustomerBalance;
end;
相关推荐
SAP HANA是一个高性能的内存数据库系统，它提供了一系列功能强大的工具来进行数据分析、应用开发、存储过程编写等操作。其中，对于存储过程的调试是开发者日常开发工作中的一个重要环节，SAP HANA为存储过程提供了...
在IT领域，尤其是在数据库管理与优化中，存储过程的解锁是一项关键技能，尤其对于Oracle数据库而言。当存储过程被锁定时，可能会影响系统的性能和稳定性，因此掌握如何解锁存储过程至关重要。以下是对“oracle存储...
SqlServer存储过程及调试指南的知识点如下： 1. 存储过程概念：存储过程是一组为完成特定功能的SQL语句集，这些语句经过编译后存储在数据库中，供用户通过指定存储过程名和参数（如有）来执行。存储过程被称作...
SQL存储过程试题及答案 SQL存储过程是数据库中的一种程序单元，能够完成特定的数据库操作。今天，我们将讨论三道关于SQL存储过程的试题，这些试题涵盖了存储过程的创建、调用和参数传递等方面。 1. 创建分数存储...
在IT行业中，数据库操作是日常开发中的重要环节，而存储过程是数据库中一种高效、封装性强的预编译语句集合。本问题涉及到的是在PowerBuilder（简称Pb）环境中如何调用Oracle或SQL Server等数据库中的存储过程。以下...
在SQL Server中，存储过程分为不同种类，包括用户定义的存储过程、系统存储过程、临时存储过程以及扩展存储过程。 1. **用户定义的存储过程**：由开发人员或DBA创建，可以根据业务需求定制各种复杂操作，如数据处理...
PL/SQL Developer 调试存储过程及调试包中创建的存储过程 PL/SQL Developer 调试存储过程是指使用 PL/SQL Developer 工具来调试 Oracle 数据库中的存储过程。调试存储过程可以帮助开发者快速地定位和解决存储过程...
### 存储过程的优点 #### 一、提升执行效率 1. **编译优势**：存储过程在创建时仅编译一次，之后每次执行时都无需再次编译。相比之下，一般的SQL语句每次执行都需要重新编译。这种差异使得存储过程能够显著提高...
MySQL存储过程是数据库管理系统中的一种重要功能，它允许开发者预编译一系列SQL语句并封装成一个可重复使用的单元，从而提高数据处理的效率和代码的复用性。本教程将深入探讨MySQL存储过程的创建、调用以及相关概念...
本文将从 Oracle 存储过程的基础知识开始，逐步深入到 Oracle 存储过程的高级应用，包括 Hibernate 调用 Oracle 存储过程和 Java 调用 Oracle 存储过程的方法。 Oracle 存储过程基础知识 Oracle 存储过程是 Oracle...
内容概要：简单的C# winform调用存储过程实例，创建存储过程入参，通过SqlConnection对象和SqlCommand对象调用存储过程，获取存储过程的出参并显示出来，详细代码注释，希望对用到C#调用存储过程的小伙伴有帮助 ...
在MySQL中，存储过程是一种预编译的SQL代码集合，它可以执行复杂的操作并提供更好的性能。在编写存储过程时，异常处理是确保程序稳定性和健壮性的重要环节。本实例展示了如何在MySQL存储过程中实现异常处理，以捕获...
《用友R9知识存储过程详解》 在IT行业中，特别是在企业级财务系统中，数据库的高效管理和数据处理是至关重要的。用友R9作为一款先进的财务管理系统，它利用存储过程这一强大的数据库功能来优化账务处理，提高系统...
在数据库管理中，存储过程和触发器是两个重要的概念，它们极大地增强了数据库的功能和效率。存储过程是一组预先编写的SQL语句，可以被多次调用，减少了网络流量，提高了性能，并提供了更好的安全性。而触发器则是一...
存储过程文档--MySQL 存储过程是 MySQL 中的一个强大功能，它允许用户预先将常用的或复杂的工作写入 SQL 语句，并将其存储起来，以便在以后的数据库操作中可以快速调用和执行。存储过程可以提高数据库的执行速度，...
在Oracle数据库环境中，事务管理与存储过程的结合是实现数据一致性、事务回滚以及错误处理的关键技术之一。本文将深入探讨“Oracle事务回滚存储过程”这一主题，旨在理解其核心概念、工作原理以及实际应用。 ### ...
本篇文章将深入探讨如何对PostgreSQL的存储过程进行调试，这对于优化数据库性能和解决复杂问题至关重要。 PostgreSQL的存储过程是由一系列SQL语句组成的代码块，可以被多次调用并执行，类似于编程语言中的函数。...
在数据库管理中，存储过程（Stored Procedure）是一种预编译的SQL代码集合，它封装了特定的功能，并在需要时由应用程序调用执行。存储过程在大型数据库系统中扮演着重要角色，因为它能提高性能、增强安全性、减少...