存储过程和函数模板范例
1.函数：
FUNCTION Fun_BuyerNameGet(ps_CardNo IN VARCHAR2) RETURN VARCHAR2
IS
vs_result VARCHAR2(80);
BEGIN
--如果存在该业务员的工号，则返回他的姓名，否则返回空
IF exist('SELECT full_name FROM kxstepiii.ba_person_info WHERE card_no = ''' || ps_CardNo || '''') THEN
BEGIN
SELECT full_name INTO vs_result FROM kxstepiii.ba_person_info WHERE card_no = ps_CardNo;
END;
ELSE
vs_result := '';
END IF;
RETURN vs_result;
END Fun_BuyerNameGet;
2.存储过程
a.插入操作：
PROCEDURE Pro_StockIns(ps_SupplierName IN VARCHAR2,
pn_ItemQty IN NUMBER,
ps_ItemDesc IN VARCHAR2,
ps_ComeDate IN VARCHAR2,
ps_AttachedFile IN VARCHAR2,
ps_SupAddress IN VARCHAR2,
ps_SupPhone IN VARCHAR2,
ps_SupFax IN VARCHAR2,
ps_SupEmail IN VARCHAR2,
ps_UserNo IN VARCHAR2,
ps_HKStockItemNo OUT VARCHAR2)
IS
vn_Count NUMBER; --记录新生成的发布存货单号是否存在
BEGIN
--初始化返回结果为空字符串
ps_HKStockItemNo := '';
--计算存货号
ps_HKStockItemNo := pkg_pu_public.fun_pu_BillNoGet(25);
--插入新发布存货信息
SELECT COUNT(*) INTO vn_Count FROM PU_HK_STOCK WHERE hkstockitem_no = ps_HKStockItemNo;
IF vn_Count = 0 THEN
INSERT INTO PU_HK_STOCK(hkstockitem_no, item_desc, status, item_qty, come_date,
attached_file, supplier_name, sup_address, sup_phone, sup_fax,
sup_email, created_by, creation_date, last_updated_by, last_update_date)
VALUES(ps_HKStockItemNo, ps_ItemDesc, 'RELEASED', pn_ItemQty, to_date(ps_ComeDate,'YYYY-MM-DD'),
ps_AttachedFile, ps_SupplierName, ps_SupAddress, ps_SupPhone, ps_SupFax,
ps_SupEmail, ps_UserNo, to_date(to_char(SYSDATE,'yyyy-mm-dd'),'yyyy-mm-dd'), ps_UserNo, to_date(to_char(SYSDATE,'yyyy-mm-dd'),'yyyy-mm-dd'));
END IF;
COMMIT;
RETURN;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20005,'保存数据出错!');
END Pro_StockIns;
b.更新操作：
PROCEDURE Pro_ClaimStock(ps_HKStockItemNo IN VARCHAR2,
ps_Buyer IN VARCHAR2,
ps_DeliNo IN VARCHAR2,
ps_UserNo IN VARCHAR2)
IS
vn_Count NUMBER; --记录是否存在该存货单号
BEGIN
--认领存货时，更新存货信息表的认领相关字段
SELECT COUNT(*) INTO vn_Count FROM PU_HK_STOCK WHERE hkstockitem_no = ps_HKStockItemNo;
--如果查询到了该笔存货单就更新，否则提示出错信息
IF (vn_Count != 0) THEN
UPDATE PU_HK_STOCK
SET status = 'CLAIMED', buyer = ps_Buyer, deli_no = ps_DeliNo, claim_date = SYSDATE, last_updated_by = ps_UserNo, last_update_date = SYSDATE
WHERE hkstockitem_no = ps_HKStockItemNo;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20005,'更新数据出错!');
END Pro_ClaimStock;
c.查询操作：
包头里面定义： TYPE t_cursor IS REF CURSOR; ----ref cursor
PROCEDURE Pro_StockQry(ps_HKStockItemNo IN VARCHAR2,
ps_Status IN VARCHAR2,
ps_SupplierName IN VARCHAR2,
ps_ItemDesc IN VARCHAR2,
ps_BeginDate IN VARCHAR2,
ps_EndDate IN VARCHAR2,
returncur OUT t_Cursor)
IS
vs_HKStockItemNo VARCHAR(22); --存货号
vs_SupplierName VARCHAR(202); --供应商名称
vs_ItemDesc VARCHAR(402); --货物描述
vs_Status VARCHAR(12); --存货状态
vs_Sql VARCHAR(3000); --存储动态SQL语句
BEGIN
--构造模糊查询的变量
vs_HKStockItemNo := '%' || ps_HKStockItemNo || '%';
vs_SupplierName := '%' || ps_SupplierName || '%';
vs_ItemDesc := '%' || ps_ItemDesc || '%';
--当存货状态为空时，查出所有状态下的存货记录
IF ps_Status = 'All' THEN
vs_Status := '%';
ELSE
vs_Status := ps_Status;
END IF;
--组织动态SQL语句
vs_Sql := 'SELECT A.hkstockitem_no, A.supplier_name, A.item_desc, A.item_qty, pu_codeinfo.code_desc AS status,
A.attached_file, (kxstepiii.ba_person_info.full_name || A.buyer) AS buyer
FROM PU_HK_STOCK A
LEFT JOIN pu_codeinfo ON pu_codeinfo.code_type = ''存货状态'' AND A.status = pu_codeinfo.en_code_desc
LEFT JOIN kxstepiii.ba_person_info ON A.buyer = kxstepiii.ba_person_info.card_no
WHERE (A.status LIKE ''' || vs_Status || ''') AND (A.hkstockitem_no LIKE ''' || vs_HKStockItemNo || ''')
AND (A.supplier_name LIKE ''' || vs_SupplierName || ''')
AND (A.item_desc LIKE ''' || vs_ItemDesc || ''')';
--根据开始时间和结束时间来添加查询条件
IF ps_BeginDate IS NOT NULL THEN --开始时间是否为空
vs_Sql := vs_Sql || ' AND (A.creation_date >= TO_DATE(''' || ps_BeginDate || ''', ''YYYY-MM-DD''))';
END IF;
IF ps_BeginDate IS NOT NULL THEN --结束时间是否为空
vs_Sql := vs_Sql || ' AND (A.creation_date < TO_DATE(''' || ps_EndDate || ''', ''YYYY-MM-DD'') + 1)';
END IF;
--查询数据
OPEN returncur FOR vs_Sql;
END Pro_StockQry;
