oracle包程序编写

最新推荐文章于 2022-07-19 21:00:00 发布

LuckyBoy_Bob

最新推荐文章于 2022-07-19 21:00:00 发布

阅读量692

点赞数

CC 4.0 BY-SA版权

分类专栏： ORACLE 文章标签： oracle structure list function assembly date

本文链接：https://blog.youkuaiyun.com/shcy0524/article/details/7060214

ORACLE 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了如何编写Oracle包程序，包括定义全局变量、存储过程和函数。示例中展示了TravesalManuf_、CreateBomTable_、ComputePartNum_和GetLastLaneDate_等函数和过程的实现，涉及数据操作、树形结构查询和动态SQL语句的使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

由于工作关系，好久没有进行知识整理了，近期正好在进行oracle 编程，那就对这方面内容进行整理吧！

根据以下程序来说明介绍：

CREATE OR REPLACE PACKAGE RN_DEFINE_API AS ---这句是创建包头的开始

---定义全局变量

contract_ CONSTANT VARCHAR(5) := 'CHENYING';

---在包头定义存储过程和函数名，没有具体的过程

PROCEDURE TravesalManuf_(product_ VARCHAR2);

PROCEDURE CreateBomTable_(partno_ IN VARCHAR2);

FUNCTION ComputePartNum_(str_ IN VARCHAR2) RETURN NUMBER;

FUNCTION GetLastLaneDate_(orderNo_ IN VARCHAR2) RETURN DATE;

END; ---包头的结束

/ ---在PL/SQL中运行程序必写

CREATE OR REPLACE PACKAGE BODY RN_DEFINE_API IS

PROCEDURE TravesalManuf_(product_ VARCHAR2) IS
BEGIN

---以表字段类型来定义变量

DECLARE v_partno manuf_structure.part_no%TYPE;

---定义游标

CURSOR list_Cursor IS
SELECT DISTINCT part_no FROM manuf_structure t WHERE t.part_no like(product_);
BEGIN
DELETE FROM RN_BOM_RESOLVE_TAB;
OPEN list_cursor;
LOOP
FETCH list_cursor
INTO v_partno;
EXIT WHEN list_cursor%NOTFOUND;
CreateBomTable_(v_partno);
END LOOP;
Exception
WHEN others THEN CLOSE list_cursor; IF list_cursor%ISOPEN THEN CLOSE list_cursor;
END IF;
END;
END TravesalManuf_;

PROCEDURE CreateBomTable_(partno_ IN VARCHAR2) IS
BEGIN
INSERT INTO RN_BOM_RESOLVE_TAB

---查询oracle树写法
SELECT partno_,
         COMPONENT_PART,
         t.consumption_item,
         '',
         ComputePartNum_(SUBSTR(SYS_CONNECT_BY_PATH(qty_per_assembly, '*'),
                                2)),
         'CHENYING'
    FROM manuf_structure t
--WHERE CONNECT_BY_ISLEAF =1
--START WITH NOT EXISTS
--(
--      SELECT 1 FROM manuf_structure
--      WHERE t.part_no = COMPONENT_PART
--)
START WITH t.part_no = partno_ CONNECT BY NOCYCLE PRIOR COMPONENT_PART = part_no;

UPDATE RN_BOM_RESOLVE_TAB t
   SET t.description =
       (SELECT t1.description
          FROM INVENTORY_PART t1
         WHERE t1.part_no = t.part_no);

END CreateBomTable_;

FUNCTION ComputePartNum_(str_ IN VARCHAR2) RETURN NUMBER AS
PARTNUM NUMBER;
BEGIN

---动态SQL语句写法
EXECUTE IMMEDIATE 'SELECT ' || str_ || ' FROM dual ' INTO PARTNUM; RETURN PARTNUM;
END ComputePartNum_;

FUNCTION GetLastLaneDate_(orderNo_ IN VARCHAR2) RETURN DATE AS
LASTDATE DATE;
BEGIN

SELECT to_date(substr(max(lane_no), 5, 6), 'yyMMdd') INTO LASTDATE FROM shop_barcode_hist WHERE order_no = orderNo_;

RETURN LASTDATE;

END GetLastLaneDate_;

END RN_DEFINE_API;
/

程序中语句写法请查看相关资料，将在以后资料中补充

2011-12-10日