摘要：
Our technologist caches scalar subqueries, votes for SQL, and recommends technology and community.One of the talks I gave recently at the Oracle Benelux User Group (OBUG) conference in Belgium was regarding techniques—some SQL tricks, if you will—you can use when writing SQL. One of the techniques I 阅读全文
随笔分类 - Oracle's PL/SQL
DBMS_LOB.LOADFROMFILE
2011-09-28 14:05 by Tracy., 1018 阅读, 收藏, 编辑
摘要：
This procedure copies all, or a part of, a source external LOB (BFILE) to a destination internal LOB.SyntaxDBMS_LOB.LOADFROMFILE ( dest_lob IN OUT NOCOPY BLOB, src_file IN BFILE, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN ... 阅读全文
TOAD SQL Editor
2010-08-12 14:42 by Tracy., 614 阅读, 收藏, 编辑
摘要：
Predefined Shortcuts Keyboard shortcuts are one of the features that make TOAD so powerful and easy to use. TOAD comes with a host of predefined shortcuts. These shortcuts save keystrokes and mouse a... 阅读全文
转Asktom:Oracle中怎么处理in的动态SQL语句.
2010-04-21 16:15 by Tracy., 777 阅读, 收藏, 编辑
摘要：
You Asked I have a simple stored procedure, that I would like to have a passed in string(varchar2) for used in select from where col1 in (var1) in a stored procedure. I've tried everything but doesn't... 阅读全文
转:Oracle中的带参数的视图
2010-04-15 15:45 by Tracy., 572 阅读, 收藏, 编辑
摘要：
创建存储过程createorreplacepackagepkg_pvisprocedureset_pv(pvvarchar2);functionget_pvreturnvarchar2;end;createorreplacepackagebodypkg_pvisvvarchar2(20);procedureset_pv(pvvarchar2)isbeginv:=pv;end;functionget... 阅读全文
转:Oracle 10g批量绑定forall bulk collect
2010-04-15 15:37 by Tracy., 459 阅读, 收藏, 编辑
摘要：
批量绑定可以通过减少在PL/SQL和SQL引擎之间的上下文切换(context switches )以此提高性能。批量绑定（Bulk binds）主要包括：(1) Input collections, use the FORALL statement，用来改善DML（INSERT、UPDATE和DELETE) 操作的性能。(2) Output collections, use BULK COLLE... 阅读全文
转：Oracle中自助事务的解释及示例.
2010-04-15 15:02 by Tracy., 538 阅读, 收藏, 编辑
摘要：
Autonomous transactions allow you to leave the context of the calling transaction, perform an independant transaction, and return to the calling transaction without affecting it's state. The autonomou... 阅读全文
转:Oracle如何使用pipeline function获得实时输出
2010-04-15 14:32 by Tracy., 739 阅读, 收藏, 编辑
摘要：
如何使用pipelinefunction获得实时输出create type lookup_row as record ( idx number, text varchar2(20) );create type lookups_tab as table of lookup_row;create or replace function Lookups_Fn return lookups_tab pi... 阅读全文
转:EXECUTE IMMEDIATE用法小解
2010-04-15 13:54 by Tracy., 703 阅读, 收藏, 编辑
摘要：
EXECUTE IMMEDIATE用法小解 EXECUTE IMMEDIATE 代替了以前Oracle8i中DBMS_SQL package包.它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前，EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能，较之以前它相当容易编码.尽管DBMS_SQL仍然可用，但是推荐使用EXECUTE I... 阅读全文
博主一个删除download记录的小function,注意其中bulk collect into跟forall的利用.
2010-04-15 12:25 by Tracy., 557 阅读, 收藏, 编辑
摘要：
PROCEDURE delete_route_download_bol (device_id_in VARCHAR2, bol_id_in INTEGER) AS TYPE t_rowids IS TABLE OF ROWID INDEX BY PLS_INTEGER; v_rowids t_rowids; TYPE t_routedownload IS TABLE OF tms.tms_rout... 阅读全文
博主写的Route收费代码,随笔记录下.
2010-04-15 12:21 by Tracy., 354 阅读, 收藏, 编辑
摘要：
CREATE OR REPLACE PACKAGE BODY TMS.tms_intf_outbound/* Version 1.8.0 */AS --The procedue is to charge drop off items which routes are finished and not be charged yet. --The parameter p_bol is used whe... 阅读全文
博主写的Route安排的pkb源代码,注意关键部位的日期排序算法。
2010-04-15 12:18 by Tracy., 348 阅读, 收藏, 编辑
摘要：
没有什么注释，大家可能看不太清楚。就当一个随笔吧。CREATE OR REPLACE PACKAGE BODY TMS.tms_rescheduleAS PROCEDURE reschedule_po IS v_range_date DATE := SYSDATE - 20 / 60 / 24; v_cnt INTEGER; v_cnt2 INTEGER; BEGIN EXECUTE IMMEDI... 阅读全文
Oracle中列转行的2种方法.
2010-04-15 11:41 by Tracy., 8957 阅读, 收藏, 编辑
摘要：
列转行主要讨论sys_connect_by_path的使用方法。1、带层次关系SQL> create table dept(deptno number,deptname varchar2(20),mgrno number);Table created.SQL> insert into dept values(1,'总公司',null);1 row created.SQL> ins... 阅读全文
Oracle删除重复记录的几种常用方法
2010-04-15 11:18 by Tracy., 268 阅读, 收藏, 编辑
摘要：
方法1： DELETE FROM tms_temp_trailer_item tmp WHERE ROWID = (SELECT ROWID FROM (SELECT ROWID, ROW_NUMBER () OVER ( PARTITION BY stop_id, po_id ORDER BY stop_id, po_id ) rn FROM tms_temp_trailer_item) tmp... 阅读全文
求列最高薪水5人及其他的SQL写法.
2010-04-15 10:58 by Tracy., 677 阅读, 收藏, 编辑
摘要：
求列最高薪水5人及其他的SQL写法.有表emp:name sal---------------SMITH 800ALLEN 1600WARD 1250JONES 2975MARTIN 1250BLAKE 2850CLARK 2450SCOTT 3000KING 5000TURNER 1500ADAMS 1100JAMES 950FORD 3000MILLER 1300求一窗口函数能出以下结果:na... 阅读全文