易截截图软件、单文件、免安装、纯绿色、仅160KB

partition outer join in oracle 10g

Partition outer join is a new mechanism in 10g to "invent" data to fill the gaps in non-contiguous results. In 10g there are many methods to deal with such a problem (including the awe-inspiring, but equally terrifying, MODEL clause). In older versions of Oracle, "data-densification" was not as simple and certainly less efficient than it has now become.
the problem
This article has been motivated by a response I gave to a problem raised on an Oracle developer forum. Our requirement is to produce a report that details customer spending for each month of the year. Our database only records actual spend, so for any given month, data for dormant or idle customers will have to be generated.
setup
First, we'll create a mock CUSTOMER_ORDERS table with sparse data to represent customer spending. To keep the example simple, we'll denormalise the customer name onto the orders table.
SQL> CREATE TABLE customer_orders (name, dt, amt)
  2  AS
  3     SELECT *
  4     from  (
  5            SELECT owner
  6            ,      TRUNC(created) + MOD(ROWNUM,6)
  7            ,      TRUNC(object_id/ROWNUM)
  8            from   all_objects
  9            WHERE  created > TRUNC(SYSDATE,'YEAR')
10            AND    owner IN ('ORDSYS','WKSYS')
11            ORDER  BY
12                   DBMS_RANDOM.RANDOM
13  &nbs


相关文档:

Oracle多行记录合并/连接/聚合字符串的几种方法

什么是合并多行字符串(连接字符串)呢,例如:
SQL> desc test;
Name     Type          Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y       &nb ......

转:ORACLE 多表关联 UPDATE 语句


转:ORACLE
多表关联 UPDATE 语句
原帖:http://www.cnblogs.com/miley/archive/2010/04/15/1712617.html
  为了
方便起见
,建立了以下简单模型
,和构造了部分测试数据
:
   在某个业
务受理子系统
BSS中,
  --客户资
料表
  create table customers
   (
 & ......

Oracle Database中DBA常用的表和视图


dba_开头
   dba_users     数据库用户信息
   dba_segments  表段信息
   dba_extents    数据区信息
   dba_objects    数据库对象信息
   dba_tablespaces   数据库表空间信息
 &nbs ......

Oracle编程常见错误和分析

1.        资源正忙错误
在pl/sql developer开一个sql window,执行如下sql不提交
Create Table aa(a Int);
Insert Into aa Values(234);
再开一个sqlwindow执行
Drop Table aa
就会报ora-00054错误。
Oracle锁的知识:
此时Oracle已经对返回的结果集上加了排它的行级锁,所有其 ......

oracle 存储过程 交集

create or replace procedure TestJiaoJi
  is
   type nt_table_type is table of number;
   nt1 nt_table_type:=nt_table_type(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
   nt2 nt_table_type:=nt_table_type(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 ......
© 2009 ej38.com All Rights Reserved. 关于E健网联系我们 | 站点地图 | 赣ICP备09004571号