PL/SQL 训练13--plsql 优化
--数据缓存技术
--PGA和SGA
---SGA：系统全局区域
--PGA:Process Global Area是为每个连接到Oracle的用户进程保留的内存。
---PLSQL从PGA获取信息的速度要比从SGA快
--基于PGA的缓存为改善性能提供了许多有趣的机会
--基于包的缓存
--基于包的缓存使用的是包级别的一个或多个变量，而不是在包中子程序的任何子程序里声明的变量
--包级别的数据会一直存在于会话的整个生命期
--如果在包级别声明了一个变量，一旦给这个变量赋了值，就会一直保持这个值，直到断开连接
--或者重编译这个包或者修改变量的值
--什么时候使用基于包的缓存？
--所要缓存的数据在数据使用期间不会发生变化。比如说参照表或者批处理脚本需要的从脚本开始
--到脚本结束期间的一致性数据“快照”
--数据库有足够的内存，足以连接到实例的每个会话都保留一份缓存的备份
--哪些情况不使用包的缓存？
--缓存的数据在使用期间可能会变化
--每个会话需要缓存的数据太多，如果有大量用户就会导致内存错误
---示例 --先来看这个函数 select user from dual ; --返回当前连接会话的名字 function user return varchar2 is c varchar2(255); begin select user into c from dual ; return c ; end ; / --每次调用USER时，其实都是在执行一个查询
--再来看下面这个包
create or replace package thisuser is cname constant varchar2(30):= user; function name return varchar2; end thisuser; / create or replace package body thisuser is g_user varchar2(30) := user; function name return varchar2 is begin return g_user; end name; end thisuser; / --这里使用了两种不同的方式来缓存USER的返回值 --在包级别定义了一个常量：当包初始化时，PL/SQL运行引擎调用USER初始化这个常量 --一个函数：这个函数返回“这个用户”的名字——函数返回值是一个私有变量，也是在包初始化时 --把USER函数的返回值赋给这个变量
--现在我们来测试下，这几个方法的性能怎么样？
--开始测试前，先构造一个包来计算消耗时间
create or replace package sf_timer is procedure start_timer; procedure show_elapsed_time(i_procedure_name in varchar2); end sf_timer; / create or replace package body sf_timer is g_start_time pls_integer; procedure start_timer is begin g_start_time := dbms_utility.get_time; end start_timer; procedure show_elapsed_time(i_procedure_name in varchar2) is c_big_number number := power(2, 32); begin dbms_output.put_line(i_procedure_name || 'Elapsed:' || to_char(mod((dbms_utility.get_time - g_start_time)+c_big_number,c_big_number)/100)||'s'); end show_elapsed_time; end sf_timer; / begin sf_timer.start_timer(); dbms_lock.sleep(1); sf_timer.show_elapsed_time('test'); end; / declare v_start_time number := dbms_utility.get_time; begin dbms_lock.sleep(1); dbms_output.put_line(dbms_utility.get_time-v_start_time); end ; / create or replace procedure test_thisuser(in_count in number) is v_name varchar2(30); begin sf_timer.start_timer(); for i in 1 .. in_count loop v_name := user; end loop; sf_timer.show_elapsed_time('user function'); sf_timer.start_timer(); for i in 1 .. in_count loop v_name := thisuser.cname; end loop; sf_timer.show_elapsed_time('user constant'); sf_timer.start_timer(); for i in 1 .. in_count loop v_name := thisuser.name; end loop; sf_timer.show_elapsed_time('name function'); end test_thisuser; / begin test_thisuser(100000); end ; /
--在包中缓存表的内容
--如果程序中使用到了某个表，而且表的内容在正常的工作时间从来不会被修改
--可以创建包来缓存表的全部内容，大幅度地提升查询的性能
--现在假设有个产品表 create table ma_products (product_id integer primary key ,description varchar2(100)); create or replace package products_cache is function with_sql(i_product_number in ma_products.product_id%type) return ma_products%rowtype ; function from_cache(i_product_number in ma_products.product_id%type) return ma_products%rowtype ; end products_cache; / create or replace package body products_cache is type cache_t is table of ma_products%rowtype index by pls_integer; g_cache cache_t; function with_sql(i_product_number in ma_products.product_id%type) return ma_products%rowtype is l_row ma_products%rowtype; begin select * into l_row from ma_products m where m.product_id = i_product_number; return l_row; end with_sql; function from_cache(i_product_number in ma_products.product_id%type) return ma_products%rowtype is begin return g_cache(i_product_number); end from_cache; begin for v_product in (select * from ma_products) loop g_cache(v_product.product_id) := v_product; end loop; end products_cache; / delete from ma_products where 1=1; commit; begin for i in 1 .. 500 loop insert into ma_products (product_id, description) values (i, 'test' || i); end loop; commit ; end; / select * from ma_products; declare v_product ma_products%rowtype; in_count number := 100000; begin sf_timer.start_timer(); for i in 1 .. in_count loop v_product := products_cache.with_sql(25); end loop; sf_timer.show_elapsed_time('with_sql function'); sf_timer.start_timer(); for i in 1 .. in_count loop v_product := products_cache.from_cache(25); end loop; sf_timer.show_elapsed_time('from_cache function'); end; /
--及时缓存表中的数据
--如果静态表的数据多，比如有100000行数据，一次性加载到PGA显然不现实，
--浪费CPU又浪费内存
--考虑及时缓存方案：不加载整个数据，先在缓存中找，找不到再从数据表中查询，然后放到缓存里
function jit_from_cache(i_product_number in ma_products.product_id%type) return ma_products%rowtype is l_row ma_products%rowtype; begin if g_cache.exists(i_product_number) then l_row := g_cache(i_product_number); else l_row := with_sql(i_product_number); g_cache(i_product_number) := l_row; end if; return l_row; end jit_from_cache; --如果在数据处理中，需要循环查询某个配置表中的数据，如果配置表中数据不变 --不妨考虑使用这种缓存的方式
---确定性函数的缓存
--什么是确定性函数？如果对于相同的in和IN OUT参数，函数的返回结果也相同，这个函数就是确定性的
--所以由于确定函数表现的一致性，可以利用函数的输入输出构建一个缓存
--看一个例子
create or replace function betwnstr(in_string varchar2, in_start in pls_integer, in_end in pls_integer) return varchar2 deterministic is begin return(substr(in_string, in_start, in_end - in_start + 1)); end betwnstr; --可以在一个查询里调用这个函数 select betwnstr(t.user_name,1,5) from ma_users t ; --这样调用betwnstr函数时，数据库会为输入值和返回结果构造一个缓存 --如果用相同的输入调用这个函数，数据库无需执行这个函数就能返回结果 --可以演示下这个优化结果
create or replace function betwnstr(in_string varchar2, in_start in pls_integer, in_end in pls_integer) return varchar2 deterministic is begin dbms_lock.sleep(0.01); --暂停0.01秒 return(substr(in_string, in_start, in_end - in_start + 1)); end betwnstr; --- grant execute on dbms_lock to scott; --8.44 declare v_string varchar2(30); begin sf_timer.start_timer(); for v in (select * from ma_products) loop v_string := betwnstr('ttestdssss',1,5); end loop; sf_timer.show_elapsed_time('deterministic function'); end ; / declare v_string varchar2(30); begin sf_timer.start_timer(); for v in (select betwnstr('ttestdssss',1,5) from ma_products) loop null; end loop; sf_timer.show_elapsed_time('deterministic function'); end ; /
--测试结果告诉我们，确定性函数缓存确实是一个有效的优化方法，但需要注意两点
--声明一个确定性函数，一定要确保它的确是确定性函数，一旦加上了DETERMINISTIC关键字
--如果不能保证是确定性函数，获得的数据可能是脏数据
--要使用确定性函数的缓存结果，就必须在SQL语句中调用函数
--这个约束也限制了这种缓存技术的应用
--函数结果缓存
--基于包的缓存的限制性
--11g实现了函数结果缓存
--oracle会用一个单独的缓存区为每个函数保留输入值和返回值。
--这个缓存区是被连接到这个数据库实例的全部会话所共享，而不再是每个会话都赋值一份副本
--每当调用函数时，数据库会做检查，看相同的输入值是否已经缓存了。如果已经缓存了，则函数就不用执行了
--返回缓存中的结果就可以了
--一旦缓存所依赖的数据表发生了修改，数据库会自动的把缓存置为失效
--后续的函数调用会重新用一致的数据再次更新缓存
--缓存发生在函数被调用时刻，我们无需在一个SQL语句内调用它
--不再需要编写代码声明或者更新一个集合；相反，可以通过在函数头部的声明式语法指明缓存的
--如何使用函数结果缓存
--只需要在函数头加上RESULT_CACHE子句
--result_cache子句语法如下 RESULT_CACHE [RELIES_ON (TABLE_OR_VIEW[,TABLE_OR_VIEW2...])] --RELIES_ON告诉ORACLE缓存的内容是依赖那个表和视图的。 --这个子句只可以添加到模式级别函数的头部以及包函数的实现部分（也是在包体呢）
--例子
--不带有RELIES_ON 的模式级别函数 CREATE OR REPLACE FUNCTION SESSION_CONSTANT RETURN VARCHAR2 RESULT_CACHE; --用RELIES_ON 子句说明缓存依赖于产品表的模式级别函数 CREATE OR REPLACE FUNCTION GET_PRODUCT(IN_PRODUCT_ID VARCHAR2) RETURN VARCHAR2 RESULT_CACHE ON (MA_PRODUCTS); --不带有RELIES_ON 子句的包函数（包规范和包体中都需要） create or replace package get_data is function session_constant return varchar2 result_cache; end get_data ; / create or replace package body get_data is function session_constant return varchar2 result_cache is begin null ; end session_constant ; end get_data; / --带有RELIES_ON子句的包函数（只能出现在包体中） create or replace package get_data is FUNCTION GET_PRODUCT(IN_PRODUCT_ID VARCHAR2) RETURN VARCHAR2 RESULT_CACHE; end get_data; / create or replace package body get_data is FUNCTION GET_PRODUCT(IN_PRODUCT_ID VARCHAR2) RETURN VARCHAR2 RESULT_CACHE RELIES_ON (MA_PRODUCTS) is begin null; end GET_PRODUCT; end get_data; / --带有多个对象列表的RELIES_ON create or replace package body get_data is FUNCTION GET_PRODUCT(IN_PRODUCT_ID VARCHAR2) RETURN VARCHAR2 RESULT_CACHE ON (MA_PRODUCTS,ma_orders...) is begin null; end GET_PRODUCT; end get_data; /
---确定性函数 create or replace function betwnstr(in_string varchar2, in_start in pls_integer, in_end in pls_integer) return varchar2 result_cache is begin dbms_output.put_line('betwnstr for ' || in_string || '-' || in_start || '-' || in_end); return(substr(in_string, in_start, in_end - in_start + 1)); end betwnstr; declare v_string varchar2(30); begin sf_timer.start_timer(); for v in (select * from ma_products where rownum < 11) loop v_string := case mod(v.product_id, 2) when 0 then betwnstr(v.description, 1, 5) else betwnstr('feuerstein', 1, 5) end; dbms_output.put_line(v_string); end loop; sf_timer.show_elapsed_time('deterministic function'); end; /
-- 从运行结果可以看到，‘feuerstein’看起来只运行了一次，这个证明函数结果缓存确实发挥了作用
--示例：从表中查询数据
--假设我们有一个汇率表，存放汇率数据，可能隔天才更新一次
DROP TABLE currency_tab; create table currency_tab( deal_date date , ori_currency_code varchar2(5), obj_currency_code varchar2(5), exch_rate number ); begin insert into currency_tab values(trunc(sysdate),'RMB','USD',0.1492); insert into currency_tab values(trunc(sysdate),'USD','RMB',6.7); insert into currency_tab values(trunc(sysdate),'RMB','HKD',1.25); insert into currency_tab values(trunc(sysdate),'HKD','RMB',0.8); COMMIT; end ; / --获取某天汇率 CREATE OR REPLACE FUNCTION GET_EXCH_RATE(I_DATE IN DATE, I_ORI_CUR IN VARCHAR2, I_OBJ_CUR IN VARCHAR2) RETURN CURRENCY_TAB.EXCH_RATE%TYPE RESULT_CACHE RELIES_ON (CURRENCY_TAB) IS V_EXCH_RATE CURRENCY_TAB.EXCH_RATE%TYPE; BEGIN DBMS_OUTPUT.put_line('>GET EXCH RATE FROM ' || I_ORI_CUR || ' TO ' || I_OBJ_CUR); SELECT T.EXCH_RATE INTO V_EXCH_RATE FROM CURRENCY_TAB T WHERE T.DEAL_DATE = I_DATE AND T.ORI_CURRENCY_CODE = I_ORI_CUR AND T.OBJ_CURRENCY_CODE = I_OBJ_CUR AND ROWNUM = 1; RETURN V_EXCH_RATE; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN -1; END GET_EXCH_RATE; / DECLARE V_EXCH_RATE CURRENCY_TAB.EXCH_RATE%TYPE; BEGIN DBMS_OUTPUT.put_line('GET EXCH RATE FROM RMB TO USD '); V_EXCH_RATE := GET_EXCH_RATE(TRUNC(SYSDATE), 'RMB', 'USD'); DBMS_OUTPUT.put_line(V_EXCH_RATE); DBMS_OUTPUT.put_line('GET EXCH RATE FROM USD TO RMB '); V_EXCH_RATE := GET_EXCH_RATE(TRUNC(SYSDATE), 'USD', 'RMB'); DBMS_OUTPUT.put_line(V_EXCH_RATE); DBMS_OUTPUT.put_line('GET EXCH RATE FROM RMB TO USD '); V_EXCH_RATE := GET_EXCH_RATE(TRUNC(SYSDATE), 'RMB', 'USD'); DBMS_OUTPUT.put_line(V_EXCH_RATE); /*UPDATE CURRENCY_TAB CT SET CT.EXCH_RATE = 7 WHERE CT.DEAL_DATE = TRUNC(SYSDATE) AND CT.ORI_CURRENCY_CODE = 'USD' and ct.obj_currency_code = 'RMB'; commit; DBMS_OUTPUT.put_line('AFTER COMMIT,GET EXCH RATE FROM USD TO RMB '); V_EXCH_RATE := GET_EXCH_RATE(TRUNC(SYSDATE), 'USD', 'RMB'); DBMS_OUTPUT.put_line(V_EXCH_RATE);*/ END; /
--从这个结果看出了什么，因为有依赖关系，所有当表中数据发生变化时，程序重新从表中获取数据然后再缓存
--缓存一个数据集合 create or replace package pkg_collection is type currency_t is table of number index by pls_integer; end pkg_collection; create or replace function load_currency return pkg_collection.currency_t result_cache relies_on(currency_tab) is v_cur_t pkg_collection.currency_t; begin dbms_output.put_line('> load currency'); select t.exch_rate bulk collect into v_cur_t from currency_tab t; return v_cur_t; end load_currency; declare v_cur_t pkg_collection.currency_t; begin dbms_output.put_line('first time load currency'); v_cur_t := load_currency(); dbms_output.put_line('second time load currency'); v_cur_t := load_currency(); UPDATE CURRENCY_TAB CT SET CT.EXCH_RATE = 7 WHERE CT.DEAL_DATE = TRUNC(SYSDATE) AND CT.ORI_CURRENCY_CODE = 'USD' and ct.obj_currency_code = 'RMB'; commit; dbms_output.put_line('after commit,third time load currency'); v_cur_t := load_currency(); end; /
--什么时候使用函数结果缓存呢?
--对表中数据的查询操作要比更新操作更频繁
--虽然函数没有查询任何数据，但也用相同的输入重复调用（通常是在递归场合）
--应用程序依赖于一套在应用的使用过程中保存静态的配置值
--什么时候不该使用函数结果缓存
--函数定义在一个匿名块的声明单元
--函数是一个管道化的表函数
--函数有out或者IN OUT参数
--函数的任意一个IN参数属于以下类型:BCLOB,CLOB,NCLOB,REF CURSOR
--函数的返回值类型属于以下类型:BCLOB,CLOB,NCLOB,REF CURSOR 或者带有这些类型的集合或者记录
--函数是调用者权限模型的函数，即使用AUTHID CURRENT_USER定义的函数
--函数有副作用
--函数中查询的表使用了虚拟私有数据安全策略。
--使用函数缓存结果细节
--对NULL和NULL值做相等处理
--用户从来不会看到脏数据
--当把一个函数的缓存定义为依赖某个特定表的时候，一旦这个表被标识成无效状态，函数也会被标识为无效
--如果函数抛出了一个未处理的异常，数据库不过缓存这次执行的输入值
---管理函数结果缓存
--函数结果缓存是SGA的一块内存区
--初始化参数 RESULT_CACHE_MAX_SIZE --函数结果缓存可以使用的最大数量的SGA内存 --数据库管理员感兴趣的一个包 DBMS_RESULT_CACHE --动态性能视图 sys用户查看 SELECT * FROM V$RESULT_CACHE_STATISTICS; SELECT * FROM V$RESULT_CACHE_MEMORY; SELECT * FROM V$RESULT_CACHE_OBJECTS; SELECT * FROM V$RESULT_CACHE_DEPENDENCY;
--多行SQL的批处理
--ORACLE为PL/SQL中相关的SQL相关功能提供了FORALL语句和BULK COLLECT子句
--PL/SQL运行引擎执行一个代码块时，引擎本身只会处理过程语句，而SQL语句
--是交给SQL引擎执行。SQL语句的执行是由SQL层负责，如果有必要的话，再把信息返回给PL/SQL引擎
--上下文切换: pl/sql和SQL引擎之间的控制转移。切换发生时，都会有额外的开销
--有些场景切换太多会导致性能下降
--FORALL 和BULK COLLECT ，可以把两个引擎的通信进行微调
--让PL/SQL更有效的把多个上下文切换压缩成一个切换，从而提升应用程序的性能
--通过BULK COLLECT 加速查询
--不管用显示游标或者是隐式游标，都可以通过BULK COLLECT在与数据库的单次
--交互中获取多行数据。BULK COLLECT减少了PL/SQL引擎和SQL引擎之间的切换次数
--因此也减少了提取数据时的额外开销
DECLARE TYPE product_t is table of ma_products.description%type; v_product product_t := product_t(); cursor cur_products is select * from ma_products where rownum < 1000; BEGIN sf_timer.start_timer(); for v in cur_products loop v_product.extend(); v_product(cur_products%rowcount) := v.description; end loop; --接下来使用集合中的数据 sf_timer.show_elapsed_time('collect data'); END; /
--如果表中数据量大的话，PL/SQL引擎就要向SGA中的游标发出多个FETCH操作
--为了解决这个问题，可以在查询语句中的INTO元素使用BULK COLLECT子句
--对游标使用这个子句是告诉SQL引擎把查询提取出来的多行数据批量
--绑定到指定的集合中，然后再把控制返回个PL/SQL引擎。
--子句的语法
BULK COLLECT INTO collection_name[,collection_name]...
--collection_nam代表一个集合
--使用BULK COLLECT，有以下规则和限制
--在ORACLE 9I 前只能在静态SQL中使用BULK COLLECT 。现在无论动态还是静态SQL可以使用BULK COLLECT
--可以在以下子句中使用BULK COLLECT：SELECT INTO,FETCH INTO 和RETURNING INTO
--对于在BULK COLLECT中使用的集合，SQL引擎会自动进行初始化及扩展
--它会从索引1开始填充集合，连续的插入元素，把之前已经被使用的元素的值覆盖
--不能在FORALL子句中使用SELECT ... BULK COLLECT 子句
--如果select...bulk collect没有找到任何行，不会抛出no_data_found异常
--如果查询没有返回任何行，集合的count方法将返回0
--例子
DECLARE TYPE product_t is table of ma_products.description%type; v_product product_t; BEGIN sf_timer.start_timer(); select t.description bulk collect into v_product from ma_products t where rownum < 1000; --接下来使用集合中的数据 sf_timer.show_elapsed_time('collect data'); END; /
--重写
DECLARE TYPE product_t is table of ma_products.description%type; v_product product_t; cursor cur_products is select t.description from ma_products t where rownum < 1000; BEGIN sf_timer.start_timer(); OPEN cur_products; fetch cur_products bulk collect into v_product; close cur_products; --接下来使用集合中的数据 sf_timer.show_elapsed_time('collect data'); END; /
--利用记录集合进一步简化代码
DECLARE TYPE product_t is table of ma_products%rowtype index by pls_integer; v_product product_t; BEGIN sf_timer.start_timer(); select * bulk collect into v_product from ma_products t where rownum < 1000; --接下来使用集合中的数据 sf_timer.show_elapsed_time('collect data'); END; /
--限制BULK COLLECT 提取的记录数
--可以使用LIMIT限制从数据库提取的行数量
--语法：
fetch cursor bulk collect into ... [limit rows];
--其中，rows可以是直接量、变量或者求值的结果是整数的表达式（否则数据库会抛出VALUE ERROR异常）
--limit可以帮我们控制程序用多大内存来处理数据，PGA内存
DECLARE TYPE product_t is table of ma_products%rowtype index by pls_integer; v_product product_t; cursor cur_products is select * from ma_products t; BEGIN open cur_products; loop fetch cur_products bulk collect into v_product limit 100; --exit when cur_products%notfound; exit when v_product.count = 0; for i in 1 .. v_product.count loop update ma_products t set t.description = t.description || 'tt' where t.product_id = v_product(i).product_id; end loop; commit; exit when cur_products%notfound; end loop; close cur_products; END; /
--批量提取多列，上述例子中使用记录集合，对于记录集合可以使用LIMIT子句
--对批量操作使用RETURNING子句
--BULK COLLECT既可以用于显示游标也可以用于隐式查询游标
--也可以在FORALL语句中使用BULK COLLECT，从而利用RETURNING子句
--通过RETURNING子句获取一个DML语句的信息,有了RETURNING子句后，可以轻松
--的确定刚刚完成的DML操作的结果，无需再做额外的查询工作
DECLARE TYPE product_t is table of ma_products%rowtype index by pls_integer; TYPE desc_t is table of ma_products.description%type index by pls_integer; v_product product_t; v_new_product desc_t; cursor cur_products is select * from ma_products t; BEGIN open cur_products; fetch cur_products bulk collect into v_product; close cur_products; forall i in v_product.first .. v_product.last update ma_products r set r.description = 'hello' || r.product_id where r.product_id = v_product(i).product_id returning r.description bulk collect into v_new_product; dbms_output.put_line(v_new_product.count); END; /
--通过FORALL 加速DML
--BULK COLLECT 用于对查询加速，FORALL会对插入、更新、删除以及合并做同样的事情
--FORALL 告诉PL/SQL引擎要先把一个或者多个集合的所有成员都绑定到SQL语句
--然后再把语句发送给SQL引擎
--基于SQL所处的核心地位，forall可能是PL/SQL中最重要的优化特性
--FORALL语句的语法 FORALL idx IN [lower_bound..uper_bound|indices of indexing_collection |values of indexing_collection] [save exceptions ] sql_statement; --idx是整数，隐式声明，并被定义做集合的索引值 --lower_bound操作开始的索引值（行或者是集合元素） --upper_bound操作结束的索引值 --sql_statement 将对每一个集合元素执行的SQL语句 --indexing_collection: pl/sql集合，是一个指向sql_statement所使用的绑定数组的索引的集合 --INDICES OF 和VALUES of都是从10g开始就有的 --SAVE EXCEPTIONS: 可选子句，告诉FORALL处理全部行，不过把发生的任何异常保存下来
----使用规则
--FORALL语句的主体必须是一个单独的DML语句，可以是一个插入、更新、删除或者合并操作
--DML语句使用的必须是用FORALL语句的INDEX_ROW变量索引的集合元素，index_row作用范围仅限于FORALL语句
--不要为index_row声明变量
--下边界和上边界对于SQL语句所使用的集合来说，必须是一个有效的连续索引值范围
--DML语句中使用的集合下标不能是表达式
--注意，11g之前，如果集合的元素是记录,DML语句是不能使用集合记录的字段
--只能整体使用集合中的一行。但这个限制在11g就被去掉了
---例子
--修改订单中产品价格
ma_orders ma_order_items create type order_type is table of varchar2(200); create or replace procedure modify_order(i_order_t in order_type, i_product_t in order_type) is begin forall idx in i_order_t.first .. i_order_t.last update ma_order_items t set t.product_new_price = t.product_ori_price * 0.5 where t.id_ma_orders = (select m.id_ma_orders from ma_orders m where m.order_no = i_order_t(idx)) and t.product_name = i_product_t(idx); end; / --跟FOR循环相比，FOR改成了FORALL,且去掉了LOOP和END LOOP create type product_t is VARRAY(255) of varchar2(200); CREATE TABLE TEST_ORDER(ORDER_NO VARCHAR2(20),PRICE NUMBER ,PRODUCTS product_t); forall idx in v_order_t.first..v_order_t.last insert into test_order values(v_order_t(idx),v_price_t(idx),v_products_t); --这个例子使用三个集合，最后一列没有使用下标，所以每一行的每一列都会填充这个集合 --其它两个集合的每个元素都会传递给每一个INSERT 语句
---FORALL语句中使用RETURNING子句，前面的例子，RETURNING子句必须使用BULK COLLECT INTO
DECLARE TYPE product_t is table of ma_products%rowtype index by pls_integer; TYPE desc_t is table of ma_products.description%type index by pls_integer; v_product product_t; v_new_product desc_t; cursor cur_products is select * from ma_products t; BEGIN open cur_products; fetch cur_products bulk collect into v_product; close cur_products; forall i in v_product.first .. v_product.last update ma_products r set r.description = 'hello' || r.product_id where r.product_id = v_product(i).product_id returning r.description bulk collect into v_new_product; dbms_output.put_line(v_new_product.count); END; /
---在一个集合中定义一系列的索引值，然后用这个集合确定把绑定数组中的那些行用在动态INSERT中 forall idx in indices of v_order_no_t execute immediate 'insert into '|| v_talbe ' values(:order_no,:order_price)' using v_order(idx).order_no,v_order(idx).total_price;
--FORALL的游标属性
--执行了FORALL语句后，可以通过游标属性来获得通过FORALL执行的DML操作的信息
--SQL%FOUND:如果SQL语句的最后一次执行修改了一行或者多行记录则返回TRUE
--SQL%NOTFOUND :如果DML语句没有修改任何行则返回true
--SQL%ROWCOUNT: 返回SQL语句全部执行所处理的总行数，不仅仅是最后一个语句
--SQL%isopen :这个属性总是返回FALSE，不要使用这个属性
--SQL%BULK_ROWCOUNT:返回一个伪集合，记录FORALL所执行的每个SQL语句处理的行数
--SQL%BULK_EXCEPTIONS:返回一个伪集合，提高的是带有SAVE EXCEPTIONS子句的FORALL语句所抛出的每个异常信息
--其它的属性都比较熟悉了，先来看下SQL%BULK_ROWCOUNT DECLARE TYPE product_t is table of number; TYPE desc_t is table of ma_products.description%type index by pls_integer; v_product product_t := product_t(1, 501, 200, 33, 2349); v_new_product desc_t; BEGIN forall i in v_product.first .. v_product.last update ma_products r set r.description = 'hello' || r.product_id where r.product_id = v_product(i) returning r.description bulk collect into v_new_product; dbms_output.put_line(sql%rowcount); if sql%bulk_rowcount(2) = 0 then dbms_output.put_line('什么都没有更新到'); end if; END; /
--FORALL语句和%BULK_ROWCOUNT使用相同的集合下标或者行数
--如果INSERT只影响了一行，%BULK_ROWCOUNT中的行值就会等于1
--对于删除、更新以及INSERT ...SELECT操作来说，%BULK_ROWCOUTN这个伪数组
--每行的值可以是任何自然数
---FORALL的回滚 --FORALL语句跟SQL引擎之间，只有一个上下文切换，如果DML语句中有一个失败了怎么办? DECLARE TYPE product_t is table of number; TYPE desc_t is table of ma_products.description%type; v_product product_t := product_t(502, 501, 200, 503, 504); v_new_desc desc_t := desc_t('hi', 'oh', 'apple', 'papper', 'book'); BEGIN /* update ma_products t set t.description = 'chick' where t.product_id = 234;*/ forall i in v_product.first .. v_product.last insert into ma_products values (v_product(i), v_new_desc(i)); forall i in v_product.first .. v_product.last execute immediate 'begin insert into ma_products values (:1, :2);commit; end;' using v_product(i), v_new_desc(i); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLCODE); END; / select * from ma_products t where t.product_id = 502;
--抛出异常的DML语句会回滚到这个语句执行之前由PL/SQL引擎标识的一个隐式保存点，这个语句已经修改的全部行
--都会回滚
--FORALL语句中任何之前已经完成的DML操作不会回滚
--如果没有采取特殊操作，这个FORALL语句停止运行，剩下的语句也不再执行
--利用SAVE EXCEPTIONS跳过异常继续执行
--通过在FORALL语句头部加上SAVE EXCEPTIONS子句，告诉ORACLE即使发生了某个异常也要继续处理
--数据库会报存这个异常结束后会抛出ORA-24381异常，当DML语句，通过SQL%BULK_EXCEPTIONS这个伪集合访问异常信息
DECLARE TYPE product_t is table of number; TYPE desc_t is table of ma_products.description%type; v_product product_t := product_t(502, 501, 200, 503, 504); v_new_desc desc_t := desc_t('hi', 'oh', 'apple', 'papper', 'book'); BEGIN forall i in v_product.first .. v_product.last SAVE EXCEPTIONS insert into ma_products values (v_product(i), v_new_desc(i)); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLCODE); for i in 1 .. SQL%BULK_EXCEPTIONS.count LOOP DBMS_OUTPUT.put_line(SQL%BULK_EXCEPTIONS(i) .error_index || sqlerrm(-1 * SQL%BULK_EXCEPTIONS(i).error_code)); END LOOP; END; / select * from ma_products t where t.product_id = 503
--用非顺序数组驱动FORALL语句
--10g前，FORALL语句使用的集合必须是紧凑的或者连续填充的,否则就会抛出异常
--10g后，可以使用INDICES OF 和VALUES OF子句，这两个子句都可以用于指定FORALL语句
--要处理的绑定数组的片段
--INDICES OF
--如果有一个集合（索引数组），这个集合中定义的行指定了绑定数组中哪些行是要处理的
--可以使用这个子句
--VALUES of
--如果有一个整数集合，这个集合的内容表明了，我们希望FORALL语句处理的绑定数组的位置，就使用这个子句
DECLARE TYPE product_t is table of ma_products.product_id%type index by pls_integer; TYPE desc_t is table of ma_products.description%type index by pls_integer; v_product product_t; v_new_product desc_t; type product_indices_t is table of boolean index by pls_integer; v_product_indices product_indices_t; BEGIN v_product(2) := 299; v_product(100) := 233; v_product(10) := 300; v_product_indices(2) := true; v_product_indices(100) := true; v_product_indices(189) := true; forall i in indices of v_product_indices between 2 and 100 update ma_products r set r.description = 'test indices' || r.product_id where r.product_id = v_product(i); END; / select * from ma_products t where t.product_id = 300； DECLARE TYPE product_t is table of ma_products.product_id%type index by pls_integer; TYPE desc_t is table of ma_products.description%type index by pls_integer; type product_indices_t is table of pls_integer index by pls_integer; v_product product_t; v_new_product desc_t; v_product_indices product_indices_t; BEGIN v_product(2) := 299; v_product(100) := 233; v_product(10) := 300; v_product_indices(22) := 10; v_product_indices(39) := 100; forall i in values of v_product_indices --必须是整数集合 update ma_products r set r.description = 'test indices' || r.product_id where r.product_id = v_product(i); END; / select * from ma_products t where t.product_id = 233;
---------------------------------------------------------------------------------
还是订单的问题，用户下了订单，后台就需要人进行处理，比如捡货、打包等等，如果订单少， 还好说，如果订单太多，根本忙不过来，需要多招人手，这样就有了订单派工的需求，派工给谁可以进行配置，比如说某个区域的订单由系统派工给某个人去处理。 1. 请分析这个需求，给出一个需求设计文档，当然，这是个可选的 2. 我们有累计一百万个订单还没有派工，请大家帮忙处理这些数据！派工规则已经配置后，可以根据快递地址进行派工，每个省份或区域都有一个处理人 --delivery order form job --tables create table order_delivery (created_by varchar2(100) default 'system' not null, created_date date default sysdate not null, updated_by varchar2(100) default 'system' not null, updated_date date default sysdate not null, id_order_delivery varchar2(32) default sys_guid() not null primary key, order_id varchar2(32),--reference order id shopping_address_id varchar2(32),--reference shopping_address_id id_delivery_config varchar2(32),--reference appoint config delivery_status number,--1 appointing,2 appointed,0 appoint failed delivery_time date, delivery_mark varchar2(100)--user order appoint mark message ); comment on table order_delivery is '订单配送表'; comment on column order_delivery.order_id is '订单号'; comment on column order_delivery.shopping_address_id is '订单配送地址'; comment on column order_delivery.id_delivery_config is '订单配送配置'; comment on column order_delivery.delivery_status is '订单配送状态'; comment on column order_delivery.delivery_time is '配送时间'; comment on column order_delivery.delivery_mark is '配送描述'; create table delivery_config (created_by varchar2(100) default 'system' not null, created_date date default sysdate not null, updated_by varchar2(100) default 'system' not null, updated_date date default sysdate not null, id_delivery_config varchar2(32) default sys_guid() not null primary key, delivery_staffname varchar2(50), delivery_area varchar2(100), delivery_mobile number, config_status number,--1 enable,0 disable config_description varchar2(100) ); comment on table delivery_config is '配送配置表'; comment on column delivery_config.delivery_staffname is '配送人员名字'; comment on column delivery_config.delivery_area is '配送区域'; comment on column delivery_config.id_delivery_config is '订单配送人员'; comment on column delivery_config.staffname_mobile is '配送联系电话'; comment on column delivery_config.config_status is '配送配置状态'; comment on column delivery_config.config_description is '配送配置描述'; --订单配送 --可以根据订单配送表的购物车里面的地址匹配配送配置表的配置区域来进行分配订单。 --根据用户订单选择的送货地址，再选择最近的有货的仓库，再选择可派送的人员(可能分多次派送，每次人员不一样) 学员参考 系统设计： 订单表 ma_orders 主要包括客户ID，订单号ID，distribution_area 客户收件地区，is_distributed 是否已派工(Y-已派工，N-未派工)等字段，其中订单ID是主键。 订单派工规则表 ORDER_AREA_DEALER_CONFIG 是 distribution_area '派工地区' 和 deal_user '处理人'的对照关系（简单处理，一个省就一个处理人） 订单派工表 ORDER_DISTRIBUTE 是id_ma_orders '订单号' 和deal_user '处理人' 的对应关系。 取订单表中未派工（is_distributed ='N'）的订单，根据 distribution_area 客户收件地区，对照订单派工规则表 中的 地区--处理人对应关系，生成订单派工表。同时将订单表中is_distributed 置为 'Y' 。 drop table MA_ORDERS; -- Create table create table MA_ORDERS ( id_ma_users VARCHAR2(32), id_ma_orders VARCHAR2(32) not null, order_no NUMBER(20), total_price NUMBER, distribution_area VARCHAR2(32), is_distributed VARCHAR2(2) default 'N' ); -- Add comments to the columns comment on column MA_ORDERS.id_ma_users is '客户号'; comment on column MA_ORDERS.id_ma_orders is '订单号'; comment on column MA_ORDERS.total_price is '总价'; comment on column MA_ORDERS.distribution_area is '客户收件地区'; comment on column MA_ORDERS.is_distributed is '是否已派工(Y-已派工，N-未派工)'; -- Create/Recreate indexes create index IDX_ID_MA_ORDERS on MA_ORDERS (ID_MA_ORDERS); -- Create/Recreate primary, unique and foreign key constraints alter table MA_ORDERS add constraint PK_MA_ORDERS primary key (ID_MA_ORDERS); --插入100万条数据 declare type id_user_t is table of varchar2(32) index by pls_integer; --保存user_id v_id_user_t id_user_t; v_id_user_t_1 id_user_t; type id_order_t is table of varchar2(32) index by pls_integer; --保存order_id v_id_order_t id_order_t; type number_t is table of number; v_number number_t; begin /* 生成10000个用户号*/ select sys_guid() bulk collect into v_id_user_t from dual connect by rownum <= 10000; select trunc(dbms_random.value(1, 10000 + 1)) bulk collect into v_number from dual connect by rownum <= 1000000; for i in v_number.first .. v_number.last loop v_id_user_t_1(i) := v_id_user_t(v_number(i)); end loop; /* 生成1000000个订单号*/ select to_char(rownum) bulk collect into v_id_order_t from dual connect by rownum <= 1000000; /*插入订单表--1000000条数据*/ forall i in 1 .. 1000000 insert into ma_orders (id_ma_users, id_ma_orders, order_no, total_price, distribution_area) values (v_id_user_t_1(i), v_id_order_t(i), null, trunc(dbms_random.value(199, 1000)), case mod(abs(dbms_random.random), 32) when 0 then '北京市' when 1 then '广东省' when 2 then '山东省' when 3 then '江苏省' when 4 then '河南省' when 5 then '上海市' when 6 then '河北省' when 7 then '浙江省' when 8 then '陕西省' when 9 then '湖南省' when 10 then '重庆市' when 11 then '福建省' when 12 then '天津市' when 13 then '云南省' when 14 then '四川省' when 15 then '广西壮族自治区' when 16 then '安徽省' when 17 then '海南省' when 18 then '江西省' when 19 then '湖北省' when 20 then '山西省' when 21 then '辽宁省' when 22 then '台湾省' when 23 then '黑龙江' when 24 then '内蒙古自治区' when 25 then '贵州省' when 26 then '甘肃省' when 27 then '青海省' when 28 then '新疆维吾尔自治区' when 29 then '西藏区' when 30 then '吉林省' when 31 then '宁夏回族自治区' end); end; --commit; select * from ma_orders; drop table ORDER_AREA_DEALER_CONFIG; create table ORDER_AREA_DEALER_CONFIG ( distribution_area VARCHAR2(32), deal_user VARCHAR2(32) ); -- Add comments to the table comment on table ORDER_AREA_DEALER_CONFIG is '订单派工规则表'; -- Add comments to the columns comment on column ORDER_AREA_DEALER_CONFIG.distribution_area is '派工地区'; comment on column ORDER_AREA_DEALER_CONFIG.deal_user is '处理人'; insert into order_area_dealer_config values ('北京市', '员工1 '); insert into order_area_dealer_config values ('广东省', '员工2 '); insert into order_area_dealer_config values ('山东省', '员工3 '); insert into order_area_dealer_config values ('江苏省', '员工4 '); insert into order_area_dealer_config values ('河南省', '员工5 '); insert into order_area_dealer_config values ('上海市', '员工6 '); insert into order_area_dealer_config values ('河北省', '员工7 '); insert into order_area_dealer_config values ('浙江省', '员工8 '); insert into order_area_dealer_config values ('陕西省', '员工9 '); insert into order_area_dealer_config values ('湖南省', '员工10'); insert into order_area_dealer_config values ('重庆市', '员工11'); insert into order_area_dealer_config values ('福建省', '员工12'); insert into order_area_dealer_config values ('天津市', '员工13'); insert into order_area_dealer_config values ('云南省', '员工14'); insert into order_area_dealer_config values ('四川省', '员工15'); insert into order_area_dealer_config values ('广西壮族自治区', '员工16'); insert into order_area_dealer_config values ('安徽省', '员工17'); insert into order_area_dealer_config values ('海南省', '员工18'); insert into order_area_dealer_config values ('江西省', '员工19'); insert into order_area_dealer_config values ('湖北省', '员工20'); insert into order_area_dealer_config values ('山西省', '员工21'); insert into order_area_dealer_config values ('辽宁省', '员工22'); insert into order_area_dealer_config values ('台湾省', '员工23'); insert into order_area_dealer_config values ('黑龙江', '员工24'); insert into order_area_dealer_config values ('内蒙古自治区', '员工25'); insert into order_area_dealer_config values ('贵州省', '员工26'); insert into order_area_dealer_config values ('甘肃省', '员工27'); insert into order_area_dealer_config values ('青海省', '员工28'); insert into order_area_dealer_config values ('新疆维吾尔自治区', '员工29'); insert into order_area_dealer_config values ('西藏区', '员工30'); insert into order_area_dealer_config values ('吉林省', '员工31'); insert into order_area_dealer_config values ('宁夏回族自治区','员工32'); commit; -- Create table drop table ORDER_DISTRIBUTE; create table ORDER_DISTRIBUTE ( id_ma_orders VARCHAR2(32), deal_user VARCHAR2(32) ); -- Add comments to the table comment on table ORDER_DISTRIBUTE is '订单派工表'; -- Add comments to the columns comment on column ORDER_DISTRIBUTE.id_ma_orders is '订单号'; comment on column ORDER_DISTRIBUTE.deal_user is '处理人'; /*派工程序*/ create or replace package distribute_orders is function get_area_dealer(in_area varchar2) return varchar2 result_cache; procedure distribute_orders; end distribute_orders; / create or replace package body distribute_orders is type cache_t is table of order_area_dealer_config.deal_user%type index by varchar2(32); g_cache cache_t; function get_area_dealer(in_area varchar2) return varchar2 result_cache RELIES_ON(order_area_dealer_config) is v_dealer order_area_dealer_config.deal_user%type; begin if g_cache.exists(in_area) then v_dealer := g_cache(in_area); else select deal_user into v_dealer from order_area_dealer_config where distribution_area = in_area and rownum = 1; g_cache(in_area) := v_dealer; end if; return v_dealer; end get_area_dealer; procedure distribute_orders is cursor cur_ma_orders is select * from ma_orders where is_distributed = 'N'; v_dealer order_area_dealer_config.deal_user%type; begin for v in cur_ma_orders loop v_dealer := get_area_dealer(v.distribution_area); insert into order_distribute (id_ma_orders, deal_user) values (v.id_ma_orders, v_dealer); update ma_orders t set t.is_distributed = 'Y' where t.id_ma_orders = v.id_ma_orders; end loop; commit; end; end distribute_orders; / begin distribute_orders.distribute_orders; end; select * from ma_orders; select * from ORDER_DISTRIBUTE;
