oracle-PLSQL-语法详细手册

1、SQL PL/SQL语法手册第一部分 SQL语法部分Create table 语句语句: CREATE TABLE schema.table_name ( column datatype DEFAULT expr column_constraint . | table_constraint , column datatype DEFAULT expr column_constraint . | table_constraint .) PCTFREE integer PCTUSED integer INITRANS integer MAXTRANS integer TABLESPACE tabl

2、espace STORAGE storage_clause RECOVERABLE | UNRECOVERABLE PARALLEL ( DEGREE integer | DEFAULT INSTANCES integer | DEFAULT ) | NOPARALLEL CACHE | NOCACHE | CLUSTER cluster (column , column.) ENABLE enable_clause | DISABLE disable_clause .AS subquery表是Oracle中最重要的数据库对象,表存储一些相似的数据集合,这些数据描述成若干列或字段.create

3、 table 语句的基本形式用来在数据库中创建容纳数据行的表.create table 语句的简单形式接收表名,列名,列数据类型和大小.除了列名和描述外,还可以指定约束条件,存储参数和该表是否是个cluster的一部分.Schema 用来指定所建表的owner,如不指定则为当前登录的用户.Table_name 用来指定所创建的表名,最长为30个字符,但不可以数字开头(可为下划线),但不可同其它对象或Oracle的保留字冲突. Column 用来指定表中的列名,最多254个.Datatype 用来指定列中存储什么类型的数据,并保证只有有效的数据才可以输入.column_constraint 用来

4、指定列约束,如某一列不可为空,则可指定为not null.table_constraint 用来指定表约束,如表的主键,外键等.Pctfree 用来指定表中数据增长而在Oracle块中预留的空间. DEFAULT为10%,也就是说该表的每个块只能使用90%,10%给数据行的增大时使用.Pctused 用来指定一个水平线,当块中使用的空间低于该水平线时才可以向该中加入新数据行.Parallel 用来指定为加速该表的全表扫描可以使用的并行查询进程个数.Cache 用来指定该表为最应该缓存在SGA数据库缓冲池中的候选项.Cluster 用来指定该表所存储的 cluster.Tablespace 用来

5、指定用数据库的那个分区来存储该表的数据.Recoverable|Unrecoverable 用来决定是否把对本表数据所作的变动写入Redo 文件.以恢复对数据的操作.As 当不指定表的各列时,可利用As子句的查询结果来产生数据库结构和数据.例: 1) create table mytab1e(mydec decimal, myint inteter) tablespace user_data pctfree 5 pctused 30; 2) create table mytable2 as ( select * from mytable1);create sequence语句语句: CREAT

6、E SEQUENCE schema.sequence_nameINCREMENT BY integer START WITH integer MAXVALUE integer | NOMAXVALUE MINVALUE integer | NOMINVALUE CYCLE | NOCYCLE CACHE integer | NOCACHE ORDER | NOORDER序列用来为表的主键生成唯一的序列值.Increment by 指定序列值每次增长的值Start with 指定序列的第一个值Maxvalue 指定产生的序列的最大值Minvalue 指定产生的序列的最小值Cycle 指定当序列值

7、逵到最大或最小值时,该序列是否循环.Cache 指定序列生成器一次缓存的值的个数Order 指定序列中的数值是否按访问顺序排序.例: 1) create sequence myseq increment by 4 start with 50 maxvalue 60 minvalue 50 cycle cache 3;2) sql create sequence new_s; sqlinsert into new (new_id,last_name,first_name) values(new_s.nextval,daur,permit);create view语句 语句: CREATE OR

8、REPLACE FORCE | NOFORCE VIEW schema.view_name (alias ,alias.) AS subquery WITH CHECK OPTION CONSTRAINT constraint视图实际上是存储在数据库上旳 select语句.每次在sql语句中使用视图时,表示该视图的select语句就用来得到需要的数据.Or replace 创建视图时如果视图已存在,有此选项,新视图会覆盖旧的 视图.Force 如有此选项,当视图基于的表不存在或在该模式中没有创建视图的权限时,也可以建立视图.As subquery 产生视图的select查询语句With che

9、ck option 如果视图是基于单表的且表中所有的非空列都包含在视图中时,该视图可用于insert和update语句中,本 选项保证在每次插入或更新数据后,该数据可以在视 图中查到例:create or place view new_v as select substr(d.d_last_name,1,3), d.d_lastname,d.d_firstname,b.b_start_date,b.b_location from new1 d, new2 b where d.d_lastname=b.b_lastname;INSERT语句:语法INSERT INTO schema.table

10、| view | subquery dblink (column , column .) VALUES (expr , expr .) | subquery WHERE condition插入单行使用VALUES关键词为新行的每一列指定一个值.如果不知道某列的值,可以使用NULL关键词将其值设为空值(两个连续的逗号也可以表示空值,也可使用NULL关键词)插入一行时试图为那些NOT NULL的列提供一个NULL值,会返回错误信息.举例:插入一条记录到DEPARTMENT表中INSERT INTO DEPARTMENT (DEPARTMENT_ID,NAME,LOCATION_ID)VALUES

11、(01,COMPUTER,167)插入多行将SELECT语句检索出来的所有数据行都插入到表中.这条语句通常在从一个表向另一个表快速复制数据行.举例:INSERT INTO ORDER_TEMPSELECT A.ORDER_ID,B.ITEM_ID,C.NAME,E.FIRST_NAME|.|E.LAST_NAME, A.ORDER_DATE,A.SHIP_DATE,D.DESCRIPTION,B.ACTUAL_PRICE, B.QUANTITY,B.TOTAL FROM SALES_ORDER A, ITEM B, CUSTOMER C, PRODUCT D, EMPLOYEE E WHERE

12、 MONTHS_BETWEEN(TO_DATE(A.ORDER_DATE),TO_DATE(01-7月-91)0 AND A.CUSTOMER_ID=C.CUSTOMER_ID AND C.SALESPERSON_ID=E.EMPLOYEE_ID AND A.ORDER_ID=B.ORDER_ID AND B.PRODUCT_ID=D.PRODUCT_ID从其它表复制数据:要快速地从一个表向另一个尚不存在的表复制数据,可以使用CREATE TABLE语句定义该表并同时将SELECT语句检索的结果复制到新表中.CREATE TABLE EMPLOYEE_COPY AS SELECT * FROM

13、 EMPLOYEEUPDATE语句:语法UPDATE schema.table | view | subquerydblink alias SET (column , column .) = (subquery) | column = expr | (subquery) , (column , column .) = (subquery) | column = expr | (subquery) . WHERE conditionUPDATE语句更新所有满足WHERE子句条件的数据行.同样,该语句可以用SELECT语句检索得到.但SELECT必须只检索到一行数据值.否则报错.而且每更新一行数据

14、,均要执行一次SELECT语句.举例:UPDATE EMPLOYEE_COP SET SALARY= SALARY-400 WHERE TO_NUMBER(TO_CHAR(HIRE_DATE,YYMMDD)=5050 then raise error_message; /*引发错误处理*/ else insert into c_nt(c_t) values(i); end if;exception when error_message then insert into c_nt(c_t) values(0);end;从上例中可以看出PL/SQL语法的一般规则.PL/SQL中语句以分号(;)结尾

15、.开始程序块的PL/SQL语句(如IF或BEGIN语句)没有分句.文本值括在单引号( )内,而不是(“ “).过程只允许最后有一个出口.PL/SQL程序可以分为三个部分DECLARE部分用于变量、常量、函数、过程、Cursor.BEGIN部分包含PL/SQL块中要执行的代码 用于程序处理,其中可以调用函数、过程.Exception 部分用于出错处理.下面我们再看一个例子:declarei number :=1;t number :=1;p number :=1;/*create table c_ny(c_t number,cou_t number);*/function aa(xx numbe

16、r)return number is /* define function*/ tt number; ct number:=1; j number:=1;begin while j=xx loop ct:=ct+j; j:=j+1; end loop; return ct;end aa;begin /*create table c_nt(c_t number,cou_t number);*/ commit; while i10 then Raise out_of_bounds;End if;另:在声明一个变量时,PL/SQL提供两种变量类型:%TYPE,%ROWTYPE.1.%TYPE使用%T

17、YPE时,可以有种用法:一用法见下例:declare Balancenumber(7,2);Minimum_balancebalance%type:=10.00;在上例中,minimum_balance数据类型为number(7,2)具默认值为10.00.二用法见下例(将数据类型与table中一column datatype相对应起来,如果table中column datatype变更,则在运行时,上数据类型会自动的变换上):declaremy_dname empc.empto%type;2.%rowtype使用%rowtype数据类型用于将table和cursor中一数据行相对起来.见下例:

18、Declare定義一個%rowtype類型,與my_cursor中記錄行對應.Cursor my_cursor is select sal+nvl(comm,0) salcomm,wages,ename from emp;My_rec my_cursor%rowtype;BeginOpen my_cursor; 將my_cursor中之記錄寫到my_rec變量中去.loopFetch my_cursor into my_rec ;Exit when my_cursor%notfound;If my_rec.wages200 thenInsert into temp values (null,m

19、y_rec.wages,my_rec.ename);End if;End loop;Close my_cursor;End;变量说明在PL/SQL中包括以下几种常见的变量类型:CHAR-存储定长的ASCII字符串,允许存储数字,文本文符等,最长可255个字符.VARCHAR2-存储变长的字符串,尽管伋按符串的最大长度来定义,但VARCHAR2和CHAR的区别在于如果达不到定义的长度,下的空间不会自动的填写为空格,VARCHAR2最大可以放入2000个字符.DATE-实际上是存储时间信息的日期/时间戳,在使用日期时,应考虑怎样使用日期函数.有关日期函数的格式见函数说明部分.127NUMBER-存

20、储数值数据,包括整数和浮点数、数据范围可以从110 到3810 ,而且,你有很大的数据空间.BOOLENA-存储布尔值.它表示是/否,真/假,1/0之类的东西.LONG-这是一种文本字符串,其长度大于VARCHAR2字段的2000个字符.该类最多可储存2 GB个字符,与原始二进制数据相比,它只能存储字符信息.RAW-用来存储操作系统使用的原始二进制数据,可用于存储像图像或声音记录这样的信息,但这种数据长度最长度只有255字节.LONG RAW-与LONG类型等价,但存储二进制数据,最长可达2GB个字节.3131BINARY_INTEGER-这个字段按计算器使用的二进制格式存储信息,从 -2 到

21、2 -1.另:PL/SQL提位两种复合类型:TABLE和RECORD1.TABLE要定义一个数组,你使用表类型定义语句,例如要定义Last_name数组,可以使用下述语句:type last_name_list is table of varchar2(22)index by binary_integer;last_name last_name_list;当定义一个长类型时,就涉及到一个删除表的问题,PL/SQL表不能用Delete语句来删除,但可以将每一行空值如下所示:sal_tab(3):=null;另一种法是定义两个相同类型的表类型,如果要将另一表清空,只需将空表给要清空的表即可.如下所

22、示.declare type numtabtype is table of numberindex by binary_integer;sal_tab numbertype;empty_tab numbertype;beginfor I in 1.100 loopsal_tab(I):=I;end loop;.End;2.RECORDDeclare Type deptrectype is record(deptnonumber(2), dnamechar(14), locchar(14),);dept_recdeptrectype;beginselect deptno,dname,loc in

23、to dept_rec from dept where deptno=30;end;与所有的编程语言一样,定义一个变量时,同样存在变量作用范围问题:如下所示:變量x為 real類型.declare x real;function function_name(variant type)return type is declare在此範圍之內變量x為char x char; begin end function_name;變量x為real類型beginx:=expression1end;如果想引用另一block之变量时,可以加上block label,如下所示:Block label;declar

24、ebirthdate date;begin declare birthdate date;引用outer block塊所定義之變量.begin.If birthdate=outer.birthdate then.End if;End;End outer;PL/SQL控制程序流IF THENELSIFELSE.END IFIF THEN STATMENT1;elseifthen statment2elseifthen statment3ELSE STATMENT4;end if;例:for ctr in 1.20 loop for ctr in 1.10 loop if outer.ctrctr

25、 then end loop inner;end loop outer;2.loopexitend loop loop sequence_of_statement; exit;end loop;例: loop fetch c1 into exit when c1%notfound;end loop;close c1;另:加loop label;例: loop loop exit outer when end loop; end loop outer;3.whileloopend loop结构如下所示:例 while total=25000 loop . Select sa1 into sala

26、ry from emp where Total:=total+salary; End loop;4.forloop 例1. select count(empno) into emp_count from emp; for L in 1.emp_count loop end loop;例2. for step in 1.25 loop for step in 1.10 loop if outer.step15 then end loop; end loop outer;cursor.loopdeclaresursor c1 is select ename from emp;name varcha

27、r2(100);beginfor p_c1_rec in c1 loopname := p_c1_rec.ename;end loop;存储过程要创建存储过程,可以使用下面的SQL和PL/SQL语句:CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(parameter list)ASBEGIN(SQL AND PL/SQL COMMANDS)END;存储函数create or replace function function_name (parameter list) return type is.Begin end;PackagePackage分为两部分

28、:Specific and Body在包说明部分中,主要将此包中所含的过程和Function的调用参数说明清楚,如:CREATE OR REPLACE PACKAGE ZDL_JOB_PKGASPROCEDURE ZDL_INSERT_JOB(p_bkc_id in number,p_item_id in number,p_job_number in out varchar2,p_group_id in number,p_lead_day in number,p_load_type in number,P_STATUS_TYPE IN NUMBER,P_USER_ID IN NUMBER,P_

29、JOB_TYPE IN VARCHAR2);PROCEDURE ZDL_UPDATE_JOB(P_BKC_ID IN NUMBER,P_GROUP_ID IN NUMBER,P_STATUS_TYPE IN NUMBER,P_USER_ID IN NUMBER);FUNCTION WIP_MASS_LOAD(P_GROUP_ID IN NUMBER,P_USER_ID IN NUMBER) RETURN NUMBER;PROCEDURE ZDL_PRE_UPDATE;FUNCTION ZDL_UPDATE_ORACLE_WIP(P_USER_ID IN NUMBER) RETURN NUMBE

30、R;FUNCTION ZDL_JOB_STATUS (P_JOB_NUMBER IN VARCHAR2) RETURN NUMBER;END ZDL_JOB_PKG;在包体部分,主要将包说明部分之过程及Function之代码写出来,如:CREATE OR REPLACE PACKAGE BODY ZDL_JOB_PKGASPROCEDURE ZDL_INSERT_JOB (p_bkc_id in number,p_item_id in number,p_job_number in out varchar2,p_group_id in number,p_lead_day in number,P_

31、LOAD_TYPE IN NUMBER,P_STATUS_TYPE IN NUMBER,P_USER_ID IN NUMBER,P_JOB_TYPE IN VARCHAR2) isbeginend ZDL_INSERT_JOB;PROCEDURE ZDL_UPDATE_JOB(P_BKC_ID IN NUMBER,P_GROUP_ID IN NUMBER,P_STATUS_TYPE IN NUMBER,P_USER_ID IN NUMBER)ASBEGINEND ZDL_UPDATE_JOB;FUNCTION WIP_MASS_LOAD(P_GROUP_ID IN NUMBER,P_USER_

32、ID IN NUMBER) RETURN NUMBER asbeginEND WIP_MASS_LOAD;PROCEDURE ZDL_PRE_UPDATE ISbeginend ZDL_PRE_UPDATE;FUNCTION ZDL_UPDATE_ORACLE_WIP(P_USER_ID IN NUMBER) RETURN NUMBERISbeginend ZDL_UPDATE_ORACLE_WIP;FUNCTION ZDL_JOB_STATUS (P_JOB_NUMBER IN VARCHAR2) RETURN NUMBERASBEGINEND ZDL_JOB_STATUS;END ZDL_

33、JOB_PKG;触发器所需系统权限要为某表创建触发器,必须能改变这个表,因此不仅要拥有表,并且要具有这个表的 alter权限,或者具有alter any table系统权限,除此之外,必须有create triger系统权限,若要在另一个用户账号(account)(也称之为模式(schema)上创建触发器,就必具有create any trigger系统权限.所需表权限触发器可以引用的表并不是初始化触发事件的表.触发器触发器有十二种类型.一个触发器的类型由执行触发器的层次位置和触发事务的类型定义.行级触发器在某个事务中,行级触发器行执行,对于上述ledger表中记例子而言,触发器.行级触发器是

34、在create trigger命令中通过用for each row 子句创建的.合法的触发器类型当两种不同类型之触发动作相结合时,有十二种可能的配置:Before insert行级触发器before insert 语句级触发器after insert 行级触发器after insert 语句级触发器before update行级触发器before update语句级触发器after update 行级触发器after update 语句级触发器before delete行级触发器before delete语句级触发器after delete行级触发器after delete语句级触发器例:CR

35、EATE OR REPLACE TRIGGER APPS.ZDL_BKC_JOB_BODY_AFI AFTER INSERT ON APPS.ZDL_BKC_JOB_BODY REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN Insert into audit_tbl values(:new.id,:new);UPDATE ZDL_BKC_JOB_HEAD SET UPDATE_DATE = SYSDATE WHERE ZDL_BKC_JOB_HEAD_ID = :NEW.ZDL_BKC_JOB_BODY_ID;END;应用实例下面以电算

36、部开发出之程序之各类程序为例:Create table, index, sequence, table trigger首先清除原先已有之重名table,Sequence等:DROP TABLE ZDL_BKC_JOB_BODY;DROP TABLE ZDL_BKC_JOB_HEAD;DROP TABLE ZDL_BKC_JOB_UPDATE;drop sequence zdl_bkc_job_s;建立table, sequence以及Indexcreate table zdl_bkc_job_head (zdl_bkc_job_head_id number not null, assembly

37、 varchar2(9) not null, lot_no varchar2(240), job_no varchar2(240), OL_DATE date, quantity number, line_code varchar2(240), created_date date, update_date date, primary key(zdl_bkc_job_head_id); create table zdl_bkc_job_body (zdl_bkc_job_body_id number not null references zdl_bkc_job_head(zdl_bkc_job

38、_head_id), level1 VARCHAR2(15), job1 varchar2(240), level2 VARCHAR2(15), job2 varchar2(240), level3 VARCHAR2(15), job3 varchar2(240), level4 VARCHAR2(15), job4 varchar2(240), level5 VARCHAR2(15), job5 varchar2(240);create table ZDL_BKC_JOB_UPDATE(BKC_ID NUMBER NOT NULL,LOCATION_ID NUMBER NOT NULL,AC

39、TION_ID NUMBER NOT NULL,JOB_NUMBER VARCHAR2(240),UPDATED_FLAG VARCHAR2(1),CREATION_DATE DATE,UPDATED_DATE DATE);create sequence zdl_bkc_job_s;CREATE INDEX ZDL_BKC_JOB_HEAD_N1 ON ZDL_BKC_JOB_HEAD(ZDL_BKC_JOB_HEAD_ID,ASSEMBLY);CREATE INDEX ZDL_BKC_JOB_BODY_N1 ON ZDL_BKC_jOB_BODY(ZDL_BKC_JOB_BODY_ID,LE

40、VEL1);CREATE INDEX ZDL_BKC_JOB_BODY_N2 ON ZDL_BKC_jOB_BODY(ZDL_BKC_JOB_BODY_ID,LEVEL2);CREATE INDEX ZDL_BKC_JOB_BODY_N3 ON ZDL_BKC_jOB_BODY(ZDL_BKC_JOB_BODY_ID,LEVEL3);CREATE INDEX ZDL_BKC_JOB_UPDATE_N1 ON ZDL_BKC_JOB_UPDATE(ACTION_ID,LOCATION_ID);COMMIT;建立table Trigger:- Trigger head after updateCR

41、EATE OR REPLACE TRIGGER APPS.ZDL_BKC_JOB_HEAD_AFU AFTER UPDATE OF LINE_CODE, LOT_NO, OL_DATE, QUANTITY ON APPS.ZDL_BKC_JOB_HEAD REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGININSERT INTO ZDL_BKC_JOB_UPDATE (BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)VALUES(:

42、OLD.ZDL_BKC_JOB_HEAD_ID,1,3,:OLD.JOB_NO,N,SYSDATE,SYSDATE);END;- Trigger body before deleteCREATE OR REPLACE TRIGGER APPS.ZDL_BKC_JOB_BODY_BRD BEFORE DELETE ON APPS.ZDL_BKC_JOB_BODY REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGINif :old.job1 is not null thenINSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID

43、,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB1,N,SYSDATE,SYSDATE);END IF;if :old.job2 is not null thenINSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)VALUES(:OLD.ZD

44、L_BKC_JOB_BODY_ID,2,7,:old.JOB2,N,SYSDATE,SYSDATE);END IF;if :old.job3 is not null thenINSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB3,N,SYSDATE,SYSDATE);END IF;if :old.job4 is not null thenI

45、NSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB4,N,SYSDATE,SYSDATE);END IF;if :old.job5 is not null thenINSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREAT

46、ION_DATE,UPDATED_DATE)VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB5,N,SYSDATE,SYSDATE);END IF;UPDATE ZDL_BKC_JOB_HEAD SET UPDATE_DATE = SYSDATE WHERE ZDL_BKC_JOB_HEAD_ID = :OLD.ZDL_BKC_JOB_BODY_ID;END;- Trigger head after deleteCREATE OR REPLACE TRIGGER APPS.ZDL_BKC_JOB_HEAD_BRD BEFORE DELETE ON APP

47、S.ZDL_BKC_JOB_HEAD REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGININSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)VALUES(:OLD.ZDL_BKC_JOB_HEAD_ID,1,7,:OLD.JOB_NO,N,SYSDATE,SYSDATE);END;- Trigger body after insertCREATE OR REPLACE TR

48、IGGER APPS.ZDL_BKC_JOB_BODY_AFI AFTER INSERT ON APPS.ZDL_BKC_JOB_BODY REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGINUPDATE ZDL_BKC_JOB_HEAD SET UPDATE_DATE = SYSDATE WHERE ZDL_BKC_JOB_HEAD_ID = :NEW.ZDL_BKC_JOB_BODY_ID;END;建立两个主要之package:ZDL_BKC_APP_PKGPackage SpecificCREATE OR REPLACE PACKAGE

49、 ZDL_BKC_APP_PKGAS/*BOM之展开*/PROCEDURE ZDL_BOM_EXPLOSION(P_ITEM_ID IN NUMBER,p_Organization_id IN NUMBER,P_BOM_GROUP_ID IN NUMBER,P_EXPL_QTY IN NUMBER,P_ERROR_CODE OUT NUMBER);/*展开之半制品放于ZDL_JOB_BKC_HEAD AND ZDL_JOB_BKC_BODY中,并调用ZDL_JOB_PKG中的相閞过程实现JOB之产生及LOAD入Oracle MFG中*/PROCEDURE ZDL_PROCESS_BOM(P_I

50、TEM_ID in number,P_BKC_ID OUT NUMBER,P_BOM_GROUP_ID IN NUMBER,P_GROUP_ID IN NUMBER,P_SCHEDULE_COMMENTS IN VARCHAR2,P_SCHEDULE_QUANTITY IN NUMBER,P_SCHEDULE_DATE IN DATE,P_ORGANIZATION_ID IN NUMBER,P_USER_ID IN NUMBER,P_ERROR_CODE OUT NUMBER,P_JOB_TYPE IN VARCHAR2);END ZDL_BKC_APP_PKG;Package BodyCRE

51、ATE OR REPLACE PACKAGE BODY ZDL_BKC_APP_PKGASPROCEDURE ZDL_BOM_EXPLOSION(P_ITEM_ID IN NUMBER,p_Organization_id IN NUMBER,P_BOM_GROUP_ID IN NUMBER,P_EXPL_QTY IN NUMBER,P_ERROR_CODE OUT NUMBER) AS l_seq_id NUMBER; l_bom_or_eng NUMBER:=1; l_err_msg VARCHAR2(80); l_err_code NUMBER; exploder_error EXCEPT

52、ION; loop_error EXCEPTION; table_name VARCHAR2(20); item_id_null EXCEPTION; p_revision_date varchar2(15); P_EXPLODE_OPTION_TYPE varchar2(100);BEGIN P_ERROR_CODE := 0; SELECT BOM_LISTS_S.NEXTVAL INTO l_seq_id FROM DUAL; TABLE_NAME := BUILD SQL; INSERT INTO BOM_LISTS (SEQUENCE_ID, ASSEMBLY_ITEM_ID, AL

53、TERNATE_DESIGNATOR) SELECT DISTINCT l_seq_id,P_ITEM_ID, bbom.alternate_bom_designator FROM bom_bill_of_materials bbom WHERE anization_id = 102 AND bbom.assembly_item_id = P_ITEM_ID AND (bbom.alternate_bom_designator IS NULL) AND (bbom.assembly_type = 1); commit; TABLE_NAME := EXECUTE SQL;/* Call BOM

54、 exploder */ TABLE_NAME := CALL EXPLODER; - bug 519321 P_REVISION_DATE := to_char(sysdate,DD-MON-YY HH24:MI); bompexpl.explosion_report ( org_id = p_Organization_id, order_by = 2, list_id = l_seq_id, grp_id = P_BOM_GROUP_ID, session_id = -1, levels_to_explode = 15, bom_or_eng = 1, impl_flag = 1, exp

55、lode_option = 2, module = 2, cst_type_id = -1, std_comp_flag = -1, expl_qty = P_EXPL_QTY, report_option = -1, req_id = 0, lock_flag = -1, rollup_option = -1, alt_rtg_desg = , alt_desg = , rev_date = P_REVISION_DATE, err_msg = l_err_msg, error_code = l_err_code, verify_flag =0, cst_rlp_id = 0, plan_f

56、actor_flag = 2, incl_lt_flag = 2 ); commit; TABLE_NAME := EXPLODE COMPLETE; if l_err_code = 9999 then raise loop_error; end if; if l_err_code 0 then raise exploder_error; end if; commit; -save DELETE FROM BOM_LISTS WHERE SEQUENCE_ID = L_SEQ_ID; COMMIT;EXCEPTION WHEN exploder_error THEN P_ERROR_CODE

57、:= 1; dbms_output.put_line(l_err_msg); WHEN loop_error THENP_ERROR_CODE := 2; dbms_output.put_line(aaa); WHEN item_id_null THENP_ERROR_CODE := 3; dbms_output.put_line(Item is is null); WHEN NO_DATA_FOUND THENP_ERROR_CODE := 4; dbms_output.put_line(TABLE_NAME |SQLERRM); WHEN OTHERS THENP_ERROR_CODE :

58、= 5; dbms_output.put_line(TABLE_NAME | SQLERRM);END ZDL_BOM_EXPLOSION;/* Process data of bom_explosion_temp */PROCEDURE ZDL_PROCESS_BOM(P_ITEM_ID in number,P_BKC_ID OUT NUMBER,P_BOM_GROUP_ID IN NUMBER,P_GROUP_ID IN NUMBER,P_SCHEDULE_COMMENTS IN VARCHAR2,P_SCHEDULE_QUANTITY IN NUMBER,P_SCHEDULE_DATE

59、IN DATE,P_ORGANIZATION_ID IN NUMBER,P_USER_ID IN NUMBER,P_ERROR_CODE OUT NUMBER,P_JOB_TYPE IN VARCHAR2) ASCURSOR C1 ISSELECT BET.ASSEMBLY_ITEM_ID, MSI.SEGMENT1,BET.COMPONENT_ITEM_ID,BET.PLAN_LEVELFROM BOM.BOM_EXPLOSION_TEMP BET, INV.MTL_SYSTEM_ITEMS MSIWHERE BET.COMPONENT_ITEM_ID = MSI.INVENTORY_ITE

60、M_ID AND BET.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MSI.ORGANIZATION_ID = P_ORGANIZATION_ID ANDBET.GROUP_ID = P_BOM_GROUP_ID ANDBET.TOP_ITEM_ID = P_ITEM_ID AND (MSI.ITEM_TYPE = SA OR MSI.ITEM_TYPE = FG)ORDER BY BET.PLAN_LEVEL;P_C1 C1%ROWTYPE;R_ITEM VARCHAR2(15);P_JOB_NUMBER NUMBER;BEGINP_ERROR_CO