Oracle & Starcraft

这个也是从asktom上看到的，绝对还不错

Crete table temp_exception (field1 varchar2(20),
field2 varchar2(240);

Record1 Skipped because of the record status
Record1 No valid value on social security column
Record2 Skipped, no matching record found
Record2 Validation failed for some reason
…

I want to get an output as

Record1 Skipped because of the record status
No valid value on social security column
Record2 Skipped, no matching record found
Validation failed for some reason

tom巧妙运用了lag函数实现了功能

ops$tkyte@ORA10GR2> edit
Wrote file afiedt.buf

1 select decode( lag(deptno) over (order by deptno), deptno, to_number(null),
deptno ) new_deptno,
2 ename
3 from emp
4* order by deptno
ops$tkyte@ORA10GR2> /

NEW_DEPTNO ENAME
———- ——————————
10 CLARK
KING
MILLER
20 JONES
FORD
ADAMS
SMITH
SCOTT
30 WARD
TURNER
ALLEN
JAMES
BLAKE
MARTIN

14 rows selected.

原文链接

查询n天没有业务的销售人员的例子

SQL 10G>CREATE TABLE EMP
2 (EMPid NUMBER PRIMARY KEY,name VARCHAR2(50));

INSERT INTO emp VALUES(1,’A');

INSERT INTO emp VALUES(2,’B');

INSERT INTO emp VALUES(3,’C');

commit;

Table created.

SQL 10G>SQL 10G>
1 row created.

SQL 10G>SQL 10G>
1 row created.

SQL 10G>SQL 10G>
1 row created.

SQL 10G>SQL 10G>
Commit complete.

SQL 10G>
SQL 10G>CREATE TABLE TRANS
2 (ID NUMBER PRIMARY KEY,
3 EMPID NUMBER REFERENCES emp(empid),
4 TRANS_DT DATE
5 );

INSERT INTO TRANS
SELECT ROWNUM, 1, TO_DATE(’01-Oct-2005′,’dd-mon-yyyy’)+ROWNUM FROM ALL_OBJECTS
WHERE ROWNUM < 14;

Table created.

SQL 10G>SQL 10G> 2 3
INSERT INTO TRANS
SELECT -1*ROWNUM, 1, TO_DATE(’01-Nov-2005′,’dd-mon-yyyy’)-ROWNUM FROM
ALL_OBJECTS WHERE ROWNUM < 11;

commit;

13 rows created.

SQL 10G>SQL 10G> 2 3
10 rows created.

SQL 10G>SQL 10G>
Commit complete.

SQL 10G>
SQL 10G>
SQL 10G>
SQL 10G>
SQL 10G>select * from emp’
2 ;
ERROR:
ORA-01756: quoted string not properly terminated

SQL 10G>select * from emp;

EMPID NAME
———- ————————————————–
1 A
2 B
3 C

SQL 10G>select * from TRANS;

ID EMPID TRANS_DT
———- ———- ————
1 1 02-OCT-05
2 1 03-OCT-05
3 1 04-OCT-05
4 1 05-OCT-05
5 1 06-OCT-05
6 1 07-OCT-05
7 1 08-OCT-05
8 1 09-OCT-05
9 1 10-OCT-05
10 1 11-OCT-05
11 1 12-OCT-05

ID EMPID TRANS_DT
———- ———- ————
12 1 13-OCT-05
13 1 14-OCT-05
-1 1 31-OCT-05
-2 1 30-OCT-05
-3 1 29-OCT-05
-4 1 28-OCT-05
-5 1 27-OCT-05
-6 1 26-OCT-05
-7 1 25-OCT-05
-8 1 24-OCT-05
-9 1 23-OCT-05

ID EMPID TRANS_DT
———- ———- ————
-10 1 22-OCT-05

23 rows selected.

SQL 10G>select empid, decode( last_td, NULL,
2 trans_dt-to_date(’01-Oct-2005′,’dd-mon-yyyy’) +1,
3 trans_dt-last_td+1 ) x,
4 trans_dt, last_td
5 from (
6 select empid, trans_dt, lag(trans_dt) over (partition by empid order by
7 trans_dt) last_td
8 from trans
9 where trans_dt between to_date(’01-Oct-2005′,’dd-mon-yyyy’) and
10 to_date(’01-NOV-2005′,’dd-mon-yyyy’)
11 )
12 where decode( last_td, NULL, trans_dt-to_date(’01-Oct-2005′,’dd-mon-yyyy’) +1,
13 trans_dt-last_td+1 ) >= 5
14 /

EMPID X TRANS_DT LAST_TD
———- ———- ———— ————
1 9 22-OCT-05 14-OCT-05

SQL 10G>
SQL 10G>select empid, trans_dt, lag(trans_dt) over (partition by empid order by
2 trans_dt) last_td
3 from trans;

EMPID TRANS_DT LAST_TD
———- ———— ————
1 02-OCT-05
1 03-OCT-05 02-OCT-05
1 04-OCT-05 03-OCT-05
1 05-OCT-05 04-OCT-05
1 06-OCT-05 05-OCT-05
1 07-OCT-05 06-OCT-05
1 08-OCT-05 07-OCT-05
1 09-OCT-05 08-OCT-05
1 10-OCT-05 09-OCT-05
1 11-OCT-05 10-OCT-05
1 12-OCT-05 11-OCT-05

EMPID TRANS_DT LAST_TD
———- ———— ————
1 13-OCT-05 12-OCT-05
1 14-OCT-05 13-OCT-05
1 22-OCT-05 14-OCT-05
1 23-OCT-05 22-OCT-05
1 24-OCT-05 23-OCT-05
1 25-OCT-05 24-OCT-05
1 26-OCT-05 25-OCT-05
1 27-OCT-05 26-OCT-05
1 28-OCT-05 27-OCT-05
1 29-OCT-05 28-OCT-05
1 30-OCT-05 29-OCT-05

EMPID TRANS_DT LAST_TD
———- ———— ————
1 31-OCT-05 30-OCT-05

23 rows selected.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:51662368324802

17:10 | 固定链接 | 评论 (0) | 引用通告 (0) | 记录它 | 计算机与 Internet

固定链接 关闭

http://spaces.msn.com/members/wzwanghai/Blog/cns!1p6cztYuyVBgutMjvxSWkuhw!249.entry

Index-by-tables，nested table,varray的区别

asktom上有一篇讲述这3种类型的区别的帖子，觉得不错，转载一下

The major difference between:

(index by tables) and (nested tables/varrays)

is that index by tables are only available in PLSQL, nested tables/varrays are
avaialable in both PLSQL *and* SQL.

Index by tables are basically “sparse” arrays that need no allocation. For
example:

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 type array is table of number index by binary_integer;
3 data array;
4 begin
5 data(1000) := 1;
6 end;
7 /

PL/SQL procedure successfully completed.

Here plsql gladly accepts an entry in the 1,000′th slot — without doing
anything else. There is nothing in slots 1, 2, … 999 (or 1001 or -1, -2,….
)

That array has allocated space for 1 element in the 1,000th position. Nested
tables/varrays do not behave that way:

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 type array is table of number ;
3 data array := array();
4 begin
5 data(1000) := 1;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 5

Here it is saying “you haven’t allocated me any space up there…”. So, we use
the .extend attribute:

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 type array is table of number ;
3 data array := array();
4 begin
5 data.extend(1000);
6 data(1000) := 1;
7 end;
8 /

PL/SQL procedure successfully completed.

and we can do so.

Note that we do have to allocate 1,000 entries:

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 type array is table of number ;
3 data array := array();
4 begin
5 data.extend(1);
6 data(1000) := 1;
7 end;
8 /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 6

the nested table/varray type isn’t “sparse” like the index by table.

Day to day, in plsql code, i generally use index by tables exclusively. They
are a little faster, a little more flexible. It is when I need to use the table
type in SQL that I use a nested table (see

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:110612348061
for an example of what I mean by that)….

There are other differences between varrays and nested tables when you use them
for storage in a database table. For example — varrays maintain their “order”.
Nested tables do not. Varrays are stored in lobs — nested tables in separate
tables. There are certain DML operations available for nested tables that are
not avaialable for varrays. See

http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76976/toc.htm
for more details.