Browse by Tags

• 当心 Oracle Procedure 中的 IN 参数也会被你无意中修改了它的值

  在 Oracle Procedure 中使用 IN 参数似乎很好地对该参数起到保护作用, 其实不然! 请看如下 Code -- Spec Part PACKAGE TEST IS g1 NUMBER; PROCEDURE proc1; PROCEDURE proc2 (p1 IN NUMBER); END; -- Body Part PACKAGE BODY TEST IS PROCEDURE proc1 IS BEGIN g1 := 1; proc2 (g1); DBMS_OUTPUT.put_line ('g1=' || g1); END; PROCEDURE proc2 ( p1 IN NUMBER ) IS c1 CONSTANT NUMBER := p1; BEGIN --p1 := p1 + 1; 这句是编译不通过的, 似乎已对 IN 参数保护了. DBMS_OUTPUT.put_line ('p1=' || p1); DBMS_OUTPUT.put_line ('c1=' || c1); g1 := g1 + 1; -- 这句间接地改变了... Posted Oct 08 2008, 03:29 PM by wego with | with no comments Filed under: Oracle

• Oracle 处理数据高并发响应

  某些系统可能对系统应付数据高并发的响应要求比较高 (如: 飞行航空订票系统). 本文采用 Oracle 的 lock table 解决之. Procedure 中关键 code 如下: ------------------------------------------------------------- lock table tb_book_ticket in share mode; ... insert into tb_book_ticket (id, scheduled_flight, ticket_no, book_time) values (ID.nextval, v_scheduled_flight, v_ticket_no, SYSDATE); -- mark1 ... -- mark2 select count(*) into v_book_count from tb_book_ticket where scheduled_flight = v_scheduled_flight; -- mark3 if v_book_count <= v_book_max_count... Posted Jul 03 2008, 09:00 AM by wego with | with no comments Filed under: Oracle

• Oracle 泛型使用

  ------------------------------------------------------------------------------------------------------------- -- procedure part FUNCTION IIF (p_bool IN BOOLEAN, p_result1 IN ANYDATA, p_result2 IN ANYDATA) RETURN ANYDATA IS atype ANYTYPE; data_type_argument EXCEPTION; BEGIN IF p_result1.gettype (atype) <> p_result2.gettype (atype) THEN RAISE data_type_argument; END IF; CASE p_result1.gettype (atype) WHEN DBMS_TYPES.typecode_number THEN RETURN CASE WHEN p_bool THEN ANYDATA.convertnumber (p_result1... Posted May 07 2008, 11:48 AM by wego with | with no comments Filed under: Oracle

• Oracle竖变横一句通

  设有如下表 T A B ----------- 1 01 1 02 1 03 2 04 2 05 3 06 执行如下 sql select a,replace(max(sys_connect_by_path(b,';')),';','') b from (select a,b, (row_number() over(order by a,b) + dense_rank() over(order by a)) rn, min(b) over(partition by a) mb from t ) start with b = mb connect by rn-1 = prior rn group by a 可得如下结果 A B -------------------- 1 010203 2 0405 3 06 Posted Sep 19 2007, 02:32 PM by wego with | with no comments Filed under: Oracle