易截截图软件、单文件、免安装、纯绿色、仅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中的单记录函数
1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
        A         A      ZERO   &nbs ......

Oracle数据块损坏恢复总结(转帖)

查看数据坏块所在数据文件号及块号可以对表进行一次全表扫描,如:
  select count(*) from tablename;
  1、没有备份的情况下:
  1.1、使用exp/imp恢复
  在这种情况下肯定会造成数据的丢失,在这种情况下应采取将数据导出然后重建表再进行导入的方法,来尽量恢复损坏数据块中的数据,但是在有坏块的情况下
......

Oracle中TO_DATE格式 懒猫星空

TO_DATE格式(以时间:2007-11-02   13:45:25为例)
Year:     
yy two digits 两位年                显示值:07
yyy three digits 三位年           ......

oracle_百分比计算

select round(sum(case when o.close_date is null then 1 else 0 end)/count(*),3)*100||'%'  SCALE from order o
说明:使用sum对一定条件的数据求和后,与总数进行比较。
         ||'%' 为以的百分号形式输出结果(例:2%) ......

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号