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
相关文档:
如果web方式的em,isqlplus访问不了。
1. 检查主机名/IP、端口是否正确
安装时的主机名/IP、端口记录在$ORACLE_HOME/install/portlist.ini 文件中。
缺省是:
一般用户
http://ip:5560/isq ......
记录一下以备下次快速找到。。。
往tb_wf_privgrant表中插入一条记录,workflow_id字段值从tb_wf_workflow 表中获取workflow_name='知识审核'的所有记录中workflow_id最大值。
--oracle
declare a NUMBER(10);
begin
select max(wo ......
select round(sum(case when o.close_date is null then 1 else 0 end)/count(*),3)*100||'%' SCALE from order o
说明:使用sum对一定条件的数据求和后,与总数进行比较。
||'%' 为以的百分号形式输出结果(例:2%) ......
dba_开头
dba_users 数据库用户信息
dba_segments 表段信息
dba_extents 数据区信息
dba_objects 数据库对象信息
dba_tablespaces 数据库表空间信息
&nbs ......
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 ......