Oracle EBS PLSQL高级使用及相关技巧

Oracle PL/SQL相关技术整理

Oracle PL/SQL

相关技术整理

作 者：

时 间：2009-9-23

（不断更新完善中）

第1页/共18页

Oracle PL/SQL相关技术整理

目录

1

2

概述 ................................................................................................................................... 3

PL/SQL常用开发技巧 ................................................................................................... 3

2.1 异常处理和日志 ................................................................................................... 3

2.2 使用动态SQL ...................................................................................................... 5

2.3 使用动态CURSOR ............................................................................................. 7

2.4 Oracle 分析函数的使用 ..................................................................................... 9

2.5 使用PL/SQL开发基于EBS的HTML格式的报表 ..................................... 11

2.6 使用PL/SQL输出XML格式的数据 ............................................................. 12

注册步骤 ......................................................................................................................... 16

3.1 注册 17

3.2 注册Concurrent Program ............................................................................... 17

3.3 分配Request Group ......................................................................................... 18

3

第2页/共18页

Oracle PL/SQL相关技术整理

1 概述

PL/SQL是Oracle专有的SQL扩展语言，应用在Oracle数据库系统。PL/SQL 不是一个独立的产品，他是一个整合到ORACLE服务器和ORACLE工具中的技术，可以把PL/SQL看作ORACLE服务器内的一个引擎，SQL语句执行者处理单个的SQL语句，PL/SQL引擎处理PL/SQL程序块。当PL/SQL程序块在PL/SQL引擎处理时，ORACLE服务器中的SQL语句执行器处理PL/SQL程序块中的SQL语句。PL/SQL具有高性能、易使用、无缝地与SQL结合，以及强壮性等多项优点。

Oracle 数据库11g的新特性下PL/SQL的高效使用：















复合触发器（compound trigger）

触发器中的按序执行

Loop中CONTINUE的使用

创建无效触发器(Disabled Trigger)

在非DML语句中使用序列（sequence）

动态游标和 REF CURSOR 的可交换性

PLSQL_Warning

 只读表（read-only table）

2 PL/SQL常用开发技巧

2.1 异常处理和日志

由于设计的不足，编码的失误，硬件的错误或者其他的一些原因，程序在运行的过程中会出现一些错误，因此异常处理和日志就变得很重要。通过对一些特定异常的处理，可以提高程序的稳定性和可持续性。

PL/SQL通过EXCEPTION来处理异常，不需要专门为监控异常编写大量的代码。

使用PL/SQL内置的标准Exception

PL/SQL提前定义好了很多内置的Exception供大家使用，在STANDARD程序包中可以查到。下面用到的三个Exception是在编程过程中最经常使用的异常处理。

Begin

Select a,b

Into :variable

From table

Where xxxxxx;

Exception

when no_data_found then -- 获取‘没有找到数据’这个错误

第3页/共18页

Oracle PL/SQL相关技术整理

fnd__line(‘No_Data_Found Error!’);

when too_many_rows then -- 获取‘存在多行记录’这个错误

fnd__line(‘Too_Many_Rows Error!’);

when others then -- 获取前面没有监控的所有其他错误

fnd__line(‘Other Error!’);

End;

下图中是一些标准的exception以及它们对应的Oracle错误编码和SQLCODE得值。

使用自定义的异常处理（user-defined exception）

除了系统提供的标准exception，根据实际需求我们也可以自定义exception来使用。自定义的Exception需要注意两点，一是必须在Declare段申明，二是在处理异常的时候，必须显性的使用Raise来获取异常。

下面是一个例子：

DECLARE

out_of_balance EXCEPTION;

BEGIN

...

BEGIN ---------- 子程序块开始

...

IF ... THEN

RAISE out_of_balance; -- 申报异常

END IF;

EXCEPTION

WHEN out_of_balance THEN

-- 处理异常错误

RAISE; -- 重新申报本次的异常给外层程序块

...

第4页/共18页

Oracle PL/SQL相关技术整理

END; ------------ 子程序块结束

EXCEPTION

WHEN out_of_balance THEN

-- 用另外的方式重新处理异常

Raise_application_error(20010,’out of balances’);

...

When others then

-- 处理程序可能存在的其他异常

…

END;

在异常处理中记录日志

在程序中处理异常是一个很好的编程习惯，而记录日志更是一种有效的编程方法。它在程序调试过程中特别重要，也利于错误原因的分析和程序的改进。

在PL/SQL程序中，可通过fnd__line(fnd_,’xxxxx’)来记录日志。在SQL PLUS中可通过dbms__line(‘xxxxx’)来记录。

2.2 使用动态SQL

PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。

Oracle中动态SQL可以通过本地动态SQL来执行，也可以通过DBMS_SQL包来执行。

使用本地动态SQL

 执行DDL语句

str_sql:=’create

table ’||table_name||’(’||field1||’ ’||datatype1||’,’||field2||’ ’||datatype2||’)’;

execute immediate str_sql; --动态执行DDL语句

 执行DML语句

str_sql:=’insert into dinya_test values(:1,:2)’;

execute immediate str_sql using id,name; --动态执行插入操作,id和name为参数, using子句，按顺序将输入的值绑定到变量

 执行带输出的DML语句

v_string:=’select count(*) from table_name a where =:id’;

execute immediate v_string into v_count using p_id;

使用DBMS_SQL包

使用DBMS_SQL包实现动态SQL的步骤如下：

A、先将要执行的SQL语句或一个语句块放到一个字符串变量中。

第5页/共18页

Oracle PL/SQL相关技术整理

B、使用DBMS_SQL包的parse过程来分析该字符串。

C、使用DBMS_SQL包的bind_variable过程来绑定变量。

D、使用DBMS_SQL包的execute函数来执行语句。

下面是详细的例子：

 执行DDL语句

create or replace procedure proc_dbms_sql

( table_name in varchar2, --表名

field_name1 in varchar2, --字段名

datatype1 in varchar2, --字段类型

field_name2 in varchar2, --字段名

datatype2 in varchar2 --字段类型

) as

v_cursor number; --定义光标

v_string varchar2(200); --定义字符串变量

v_row number; --行数

begin

v_cursor:=dbms__cursor; --为处理打开光标

v_string:=’create table ’||table_name||’(’||field_name1||’ ’||datatype1||’,’||field_name2||’ ’||datatype2||’)’;

dbms_(v_cursor,v_string,dbms_); --分析语句

v_row:=dbms_e(v_cursor); --执行语句

dbms__cursor(v_cursor); --关闭光标

exception

when others then

dbms__cursor(v_cursor); --关闭光标

raise;

end;

 执行DML语句

第6页/共18页

Oracle PL/SQL相关技术整理

create or replace procedure proc_dbms_sql_update

( id number,

name varchar2

)as

v_cursor number; --定义光标

v_string varchar2(200); --字符串变量

v_row number; --行数

begin

v_cursor:=dbms__cursor; --为处理打开光标

v_string:=’update dinya_test2 a set =:p_name where =:p_id’;

dbms_(v_cursor,v_string,dbms_); --分析语句

dbms__variable(v_cursor,’:p_name’,name); --绑定变量

dbms__variable(v_cursor,’:p_id’,id); --绑定变量

v_row:=dbms_e(v_cursor); --执行动态ＳＱＬ

dbms__cursor(v_cursor); --关闭光标

exception

when others then

dbms__cursor(v_cursor); --关闭光标

raise;

end;

2.3 使用动态CURSOR

动态CURSOR，也就是REF CURSOR,它可以分为两类，一为强类型（限制），规定了返回的类型，还有一类为弱类型（非限制），不规定返回类型，可以获取任何结果集。

动态CURSOR的特点：

 PL/SQL静态光标不能返回到客户端，只有PL/SQL才能利用它。ref光标能

够被返回到客户端，这就是从Oracle的存储过程返回结果集的方式。

 PL/SQL静态光标可以是全局的，而ref光标则不是。 也就是说，不能在包

说明或包体中的过程或函数之外定义ref光标。 只能在定义ref光标的过程中处理它，或返回到客户端应用程序。

 使用静态光标--通过静态SQL（但不用ref光标）--比使用ref光标效率高，而

ref光标的使用仅限于这几种情况：把结果集返回给客户端；在多个子例程之间共享光标；没有其他有效的方法来达到你的目标时，则使用ref光标，正如必须用动态SQL时那样；

注意：在写程序的过程中，应首先考虑使用静态CURSOR，只有绝对必须使用ref光标时才使用ref光标，使用静态游标时建议尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。

第7页/共18页

Oracle PL/SQL相关技术整理

弱类型的REF CURSOR

Declare

type rc is ref cursor;

cursor c is select * from dual;

l_cursor rc;

begin

if (to_char(sysdate,'dd') = 30) then

-- ref cursor with dynamic sql

open l_cursor for 'select * from emp';

elsif (to_char(sysdate,'dd') = 29) then

-- ref cursor with static sql

open l_cursor for select * from dept;

else

-- with ref cursor with static sql

open l_cursor for select * from dual;

end if;

-- the "normal" static cursor

open c;

end;

从上面的例子中可以明细看到cursor C是个静态Cursor，l_cursor则为动态cursor，它可以是任何结果集。

强类型的REF CURSOR

declare

type gsm_rec is record(

gsmno varchar2(11),

status varchar2(1),

price number(8,2));

my_rec gsm_rec;

type app_ref_cur_type is ref cursor /*return gsm_rec可加可不加，不影响执行结果*/;

my_cur app_ref_cur_type;

begin

open my_cur for select gsmno,status,price from gsm_resource where

store_id='.01';

fetch my_cur into my_rec;

while my_cur%found loop

dbms__line(my_||'#'||my_||'#'||my_);

fetch my_cur into my_rec;

end loop;

close my_cur;

end;

第8页/共18页

Oracle PL/SQL相关技术整理

2.4 Oracle 分析函数的使用

分析函数为我们分析数据提供了一种简单高效的处理方式。在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的SQL语句就可以实现了,而且在执行效率方面也有相当大的提高。

下面是几种常用的分析函数：

自动汇总函数：Rollup，Cube

 ROLLUP是GROUP BY子句的扩展，它是为每一个分组返回一条合计记录，并为全部分组返回总计。

select division_id, SUM(salary) from employees2

group by ROLLUP(division_id);

 CUBE子句也是对GROUP BY子句进行扩展，返回CUBE中所有列组合的小计信息，同时在最后显示总计信息。CUBE的使用与ROLLUP基本相同，但CUBE的合计更加详细，它能够显示次分组字段的合计信息

select division_id,job_id,SUM(salary) from employees2

group by CUBE(division_id,job_id)

order by division_id;);

 GROUPING()函数与ROLLUP、CUBE的结合使用：GROUPING（）可接受一个列值，当列值为空时，函数返回1；如果列值非空，则返回0。

select

DECODE(GROUPING(division_id),1,'All divisions',division_id)AS div,

division_id,SUM(salary)

from employees2

group by ROLLUP(division_id);

rank 函数：rank, dense_rank, row_number

 rank：排序。rank如果出现两个相同的排名,那么后面的数据就会直接跳过这个排名，就如考试排名次的时候并列2个第一名之后 是第3名。

第9页/共18页

Oracle PL/SQL相关技术整理

例子：下例中计算每个员工按部门分区再按薪水排序，注意薪水相同的时候序号一样，后面的序号跳过。

SELECT

ment_id , _name, ,

RANK() OVER (PARTITION BY ment_id ORDER BY ) as drank

FROM employees e, departments d

WHERE ment_id = ment_id

AND ment_id IN ('60', '90');

 dense_rank：跟rank一样也是排序，区别就是就算出现相同的排名，后面的排名也不会被跳过。

例子：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与RANK函数的区别）

SELECT

ment_id , _name, ,

DENSE_RANK() OVER (PARTITION BY ment_id ORDER BY ) as drank

FROM employees e, departments d

WHERE ment_id = ment_id

AND ment_id IN ('60', '90');

 row_number：row_number哪怕是两个数据完全相同,排名也按照行号排序。没有重复值。

例子：下例返回每个员工再在每个部门中按员工号排序后的顺序号

SELECT

department_id, last_name, employee_id,

ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id

FROM employees

WHERE department_id < 50;

lag, lead函数

 Lag：可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标是一个数组一样。可以访问组中当前行之前的行。

例子：下面的例子中列prev_sal返回按hire_date排序的前1行的salary值

SELECT

last_name, hire_date, salary,

LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal

FROM employees

WHERE job_id = 'PU_CLERK';

 Lead：lead与lag刚好相反，可以访问组中当前行之后的行。

第10页/共18页

Oracle PL/SQL相关技术整理

例子：下面的例子中每行的"NextHired"返回按hire_date排序的下一行的hire_date值

SELECT last_name, hire_date,

LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired"

FROM employees

WHERE department_id = 30;

2.5 使用PL/SQL开发基于EBS的HTML格式的报表

使用PL/SQL开发HTML格式的报表并不复杂，可以通过下面2种方式实现：

使用fnd__line

Fnd_file是EBS自带的函数包，可以实现日志和并发请求结果的输出，fnd__line可以实现按行输出信息，并自动换行。

写入输出文件：

fnd__line（fnd_, ’xxxx’）;

写入日志文件：

Fnd__line(fnd_, ’xxxx’);

使用htp.p

Htp.p是Oracle数据库自带的函数包，专门针对HTML进行操作，通过该过程可以输出结果。

Htp.p(’xxxx’);

在有中文字输出的HTML格式报表中，要想正确显示中文字符，最好在输出HTML标记的时候设置字符集为UTF8.

fnd__line（fnd_,

''）

定义并发程序的时候，将输出格式定义为HTML.

第11页/共18页

Oracle PL/SQL相关技术整理

2.6 使用PL/SQL输出XML格式的数据

随着BI Publisher越来越被广泛的使用， 利用PL/SQL输出XML数据源也越来越多的被用到。Oracle 10g中有两个包，专门用来处理XML格式，它们是dbms_xmlgen 和 dbms_xmlstore。

dbms_xmlgen：用来输出XML格式的数据

常用功能：

dbms_text('select * from xmldemo'); 处理SQL

dbms_(xmlhdl); 输出XML格式

getnumrowsprocessed(xml handle):=getxml所处理的实际行数

dbms_Rows(xmlhdl,10):=允许处理最大行数

dbms_settag(xmlhdl,'Packet')：＝重置行集标签rowset

dbms_tag(xmlhdl,'Record'):=重置行标签row

dbms_lhandling(xmlhdl,2):=列空值如何表示,如：

用一个例子来说明用法：

第12页/共18页

Oracle PL/SQL相关技术整理

1.创建临时表,写入两条数据:

create table xmldemo (

a number,

b varchar2(10)

);

insert into xmldemo values (10,'first line');

insert into xmldemo values (20,'line 2');

commit;

2.使用dbms_xmlgen生成数据表的xml格式

declare

xmlhdl dbms_e;

line varchar2(200);

xmltext varchar2(32767);

begin

-- create ctxhandle

xmlhdl := dbms_text('select * from xmldemo');

-- generate xml format data into clob

xmltext := dbms_(xmlhdl);

-- display the xml content

loop

exit when xmltext is null;

line := substr(xmltext,1,instr(xmltext,chr(10))-1);

dbms__line(line);

xmltext := substr(xmltext,instr(xmltext,chr(10))+1);

end loop;

-- close ctxhandle

dbms_ontext(xmlhdl);

end;

输出结果：

10

first line

20

line 2

第13页/共18页

Oracle PL/SQL相关技术整理

dbms_xmlstore：用来增加，修改或者删除XML文件中的数据

新增数据的例子：

Declare

l_sales_emp xmltype;

l_ctx dbms_e;

l_rows pls_integer;

begin

-- get all the sales employees into an xml document

select xmlelement("ROWSET",

xmlagg(

xmlelement("ROW",

xmlforest(,,,,

te,,,)

)

)

)

into l_sales_emp

from emp e, dept d

where =

and ='SALES';

-- setup our dbms_xmlstore context

l_ctx := dbms_text('SALES_EMP');

l_rows := dbms_xml(

L_ctx, l_sales_bVal());

-- how many rows were inserted?

dbms__line(l_rows || ' rows inserted into SALES_EMP.');

-- clean up

dbms_ontext(l_ctx);

end;

更改数据的例子：

第14页/共18页

Oracle PL/SQL相关技术整理

declare

l_sales_emp xmltype;

l_ctx dbms_e;

l_rows pls_integer;

begin

-- simulate the updates to make

l_sales_emp := xmltype('

7499ALLENMANAGER

76982600

7521WARDMANAGER

76982250

');

-- setup our dbms_xmlstore context

l_ctx := dbms_text('SALES_EMP');

-- setup the columns to be updated

dbms_pdatecolumnlist(l_ctx);

dbms_atecolumn(l_ctx, 'ENAME');

dbms_atecolumn(l_ctx, 'JOB');

dbms_atecolumn(l_ctx, 'MGR');

dbms_atecolumn(l_ctx, 'SAL');

-- setup the key columns to update by

dbms_column(l_ctx, 'EMPNO');

-- perform the update

l_rows := dbms_xml(l_ctx, l_sales_bVal());

-- how many rows were updated?

dbms__line(l_rows || ' rows updated in SALES_EMP.');

-- clean up

dbms_ontext(l_ctx);

第15页/共18页

Oracle PL/SQL相关技术整理

删除数据的例子：

declare

l_sales_emp xmltype;

l_ctx dbms_e;

l_rows pls_integer;

begin

-- simulate the updates to make

l_sales_emp := xmltype('

7499

7698

');

-- setup our dbms_xmlstore context

l_ctx := dbms_text('SALES_EMP');

-- setup the key column to delete by

dbms_column(l_ctx, 'EMPNO');

-- perform the delete

l_rows := dbms_xml(l_ctx, l_sales_bVal());

-- how many rows were deleted?

dbms__line(l_rows || ' rows deleted fm SALES_EMP.');

-- clean up

dbms_ontext(l_ctx);

end;

3 注册步骤

PL/SQL可作为程序包被储存在数据库里被其他程序（Report， Form， Unix shell，

discoverer， Java等）直接调用，也可以被注册在EBS系统里成为一个独立的并发请求。

注册步骤包括：

第16页/共18页

Oracle PL/SQL相关技术整理

3.1 注册Executable

N:Application Developer> Concurrent >Program >Executable

3.2 注册Concurrent Program

N:Application Developer> Concurrent>Program >Define

第17页/共18页

Oracle PL/SQL相关技术整理

3.3 分配Request Group

N:System Administrator> Security>Responsibility >Define

第18页/共18页