1、Oracle EBS Development,Introduction to commonly used technologies (一) PL/SQL BasicBy Xu, 2009.06,PL/SQL Basic,Procedural Language/SQL,过程性语言结构 + SQL 如：变量和类型（包括预定义的以及用户自定义的）控制结构（IF THEN ELSE）过程和函数 （Function , Procedure）对象类型和方法 （Object ） 使应用程序的执行更快多个SQL语句捆绑为一个PL/SQL块并做为一个单独的单位发往服务器，减少网络的通信,PL/SQL Basic
2、 - Command,DDL (Data Definition Language)Create, Alter, Drop, Truncate DML (Data Manipulation Language)Insert, Update, Delete DQL (Data Query Language)Select DCL (Data Control Language)Commit, Rollback, Grant,PL/SQL Basic - Program,PL/SQL块结构PL/SQL是一种块结构语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL程序包含了一个或多个逻辑块，每个块都
3、包含三个部分 DECLARE -declaration statements BEGIN -executable statements EXCEPTION -exception statements END,声明部分(Declaration Section) 变量和常量的数据类型和初始值,Cursor声明.可忽略,执行部分(Executable Section) 所有的可执行语句都放在这一部分,其他PL/SQL块 也可放在此部分(以BEGINEND标记),异常处理部分(Exception section) 所有的可执行語语句放在这一部分，其他PL/SQL块 也可放在此部分(以BEGINEND标
4、记).可忽略,PL/SQL Basic - Program,PL/SQL程序PL/SQL程序块可被独立编译并存储在DB中，任何与DB相连接的应用程序都可以访问这些存储的PL/SQL程序。Oracle提供了四种类型的可存储程序：Function, Procedure, Package, TriggerFunction接受0个或多个输入参数,有一个返回值,返回值的数据类型在创建函数时定义 FUNCTION name parameter ,parameter,.) RETURN datatypes IS local declarations BEGIN execute statements EXCE
5、PTION exception handlers END name;,PL/SQL Basic - Program,Procedure 接受0个或多个参数作为输入(INPUT)或输出(OUTPUT)，或即作输入又作输出(INOUT),与Function不同，Procedure没有返回值，且不能由SQL语句直接调用,只能通过Execute命令或PL/SQL程序块内部调用 PROCEDURE name (parameter ,parameter,.) IS local declarations BEGIN execute statements EXCEPTION exception handler
6、s END name; Package 是组合在一起的相关对象的集合，可包含多个Function或Procedure。Package由两个部分组成： Spec 声明变量,常量，游标，子程序 Body 定义子程序和游标,PL/SQL Basic - Program,Trigger与一个Table或DB事件联系在一起，当对Table进行操作时，定义Table上的触发器被触发。(Fires: Before, After, Instead of; Events: Insert, Update, Delete)create or replace trigger trigger_namebefore in
7、sert of column name on table_namereferencing new as new old as old for each row when ()declare- variance declare- begin- SQL statement -exception- error exception -end trigger_name;,PL/SQL Basic - Functions,1.Max 2.Min 3.Sum 4.Count 5.Avg 6.Group by 7.Havingselect sum(moq.transaction_quantity),moq.i
8、nventory_item_id, moq.subinventory_code from mtl_onhand_quantities moq where moq.organization_id = 394 group by moq.inventory_item_id,moq.subinventory_code having sum(moq.transaction_quantity) 0;-查找存在于多个 Organization 的 Item Select MAX(Segment1) ITEM from mtl_system_items_b msi Group by Organization_
9、ID Having Count(X)2 -除Mater Org 外，至少存在于两个ORG中,PL/SQL Basic - Functions,8.Anyselect Y from dual where 1 any (select 1 from dual union select 2 from dual) 9.Allselect Y from dual where 1 all (select 1 from dual union select 2 from dual)10.Exists vs Inselect * from oe_order_headers_all oha where oha.he
10、ader_id in (select ola.header_id from oe_order_lines_all ola where ola.org_id = 386) -include null valueselect * from oe_order_headers_all oha where exists (select ola.header_id from oe_order_lines_all ola where ola.org_id = 67and ola.header_id = oha.header_id) -not include null value,11.(+)select *
11、 from (select 1 id,A val from dual union select 2 id,B val from dual) a, (select 1 id,C val from dual) b where a.id = b.id(+) 12.Nvlselect nvl(null,NVL_EXAMPLE) from dual 13.Decodeselect decode(wdj.job_type , 1 ,standard,non-standard), wdj.* from wip_discrete_jobs wdj 14.To_Dateselect to_date(2005-5
12、-1,yyyy/mm/dd),to_date(1-MAY-2005,dd-MON-yyyy),from dual 15.To_Charselect to_char(3.1415126),to_char(sysdate, yyyy/mm/dd HH24:MI:SS),to_char(sysdate,DAY)from dual,PL/SQL Basic - Functions,PL/SQL Basic - Functions,16. Union 17. Union All 18. Minus 19. Intersect,A,B,A Union B,A Union All B,A Intersect
13、 B,A Minus B,表示結果集,PL/SQL Basic - Functions,19.Upper 20.Lower 21.Trim 22.Replace vs Translateselect replace(aabbcdefa,ab,st) rp_result,translate(aabbcdefa,ab,st) tl_result from dual 23.Lpad/Rpadlpad(AAA,5,0) =00AAA 24. Substrsubstr(123456,0,3) , substr(123456,1,3) =123substr(123456,3) =3456 25.Instr
14、instr(abdedcdf,d,4,2) =7,PL/SQL Basic - Functions,26. Length 27. Roundselect round(111.11232,2),round(111.11232) from dual; 28. Trunc (date/number)select trunc(sysdate),trunc(sysdate,MM),trunc(sysdate,YY),trunc(sysdate,DAY) from dual; 29. Sysdate 30. RownumSelect user_name,rownum from fnd_user where
15、 rownum11 order by user_name ; 31. Rowid-删除重复数据delete from gupt_test_table twhere rowid not in(select max(rowid) from gupt_test_table group by col1, col2),PL/SQL Basic IFEND IF,If Then Else End If IF condition THEN Statements 1; Statements 2; ELSE Statements 1; Statements 2; END IF; If Then Elseif E
16、nd If IF condition1 THEN statement1; ELSIF condition2 THEN statement2; ELSE statement4; END IF;,PL/SQL Basic Loop, Cursor,Loop End LoopX:=100; LOOP X:=X+10; IF X1000 THEN EXIT; END IF END LOOP; For LoopX:=100; FOR v_counter in 110 LOOP x:=x+10; END LOOP; WHILELOOPX:=100; WHILE X=1000 LOOP X:=X+10; E
17、ND LOOP;,PL/SQL Basic Loop, Cursor,DECLARE CURSOR cursor_name(variable data_type) ISselect_statement;DECLARE CURSOR item_cursor (v_org_id NUMBER,v_item_id NUMBER) IS SELECT segment1 FROM mtl_system_items_b WHERE organization_id = v_org_id AND inventory_item_id = v_item_id;,PL/SQL Basic Loop, Cursor,
18、Explicit Cursor DECLAREv_item_no mtl_system_items_b.segment1%TYPE;CURSOR item_cursor ISSELECT segment1FROM mtl_system_items_b; BEGINOPEN item_cursor;LOOPFETCH item_cursor INTO v_item_no ;IF item_cursor %NOTFOUND THENEXIT LOOP;END IF;END LOOP; END; %ISOPEN (BOOLEAN) %NOTFOUND (BOOLEAN) %FOUND (BOOLEA
19、N) %ROWCOUNT (NUMBER),PL/SQL Basic Loop, Cursor,Implicit CursorDECLAREv_item_no mtl_system_items_b.segment1%TYPE; CURSOR item_cursor ISSELECT segment1FROM mtl_system_items_b;BEGINFOR item IN item_cursor LOOP - implicit open and implicit fetch occurv_item_no := item. Segment1;END LOOP; - implicit clo
20、se occurs END;,PL/SQL Basic Loop, Cursor,DECLAREType t_cursor is ref CURSOR;v_item_no mtl_system_items_b.segment1%TYPE; item_cursor t_cursor ;BEGINopen item_cursor for SELECT segment1 FROM mtl_system_items_b; LOOPFETCH item_cursor INTO v_item_no ;IF item_cursor %NOTFOUND THENEXIT LOOP;END IF;END LOO
21、P; END; END;,PL/SQL Basic Loop, Cursor,DECLAREType t_cursor is ref CURSOR;v_item_no mtl_system_items_b.segment1%TYPE; item_cursor t_cursor ;v_sql varchar2(100) :=SELECT segment1 FROM mtl_system_items_b where organization_id=:a;v_org_id number :=394;BEGINopen item_cursor for SELECT segment1 FROM mtl_
22、system_items_b;-open item_cursor for SELECT segment1 FROM mtl_system_items_b where-organization_id=:a using 394;-open item_cursor for v_sql using v_org_id; LOOPFETCH item_cursor INTO v_item_no ;IF item_cursor %NOTFOUND THENEXIT LOOP;END IF;END LOOP; END; END;,END Next “Develop PL/SQL Stored Procedure Report”,
