в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:01Z

1 версия

Новая страница

== Reference column from another column==



<source lang="sql">
SQL>
SQL> CREATE TABLE compact_discs (
2 ID INTEGER NOT NULL,
3 title VARCHAR2(60),
4 artist VARCHAR2(60),
5 label VARCHAR2(60),
6 PRIMARY KEY (ID)
7 );
Table created.
SQL>
SQL> INSERT INTO compact_discs VALUES (100, "W", "B", "A");
1 row created.
SQL>
SQL> CREATE TABLE cd_keywords (
2 cd_id INTEGER NOT NULL REFERENCES compact_discs (id),
3 keyword VARCHAR2(60) NOT NULL,
4 PRIMARY KEY (cd_id, keyword)
5 );
Table created.
SQL>
SQL> INSERT INTO cd_keywords VALUES (100, "L");
1 row created.
SQL> INSERT INTO cd_keywords VALUES (100, "M");
1 row created.
SQL>
SQL> drop table cd_keywords;
Table dropped.
SQL> drop table compact_discs;
Table dropped.
SQL></source>

== Refernece a table==



<source lang="sql">
SQL> set echo on
SQL>
SQL> create table p ( x int primary key );
Table created.
SQL>
SQL> create table c ( y references p );
Table created.
SQL>
SQL>
SQL> insert into p values ( 1 );
1 row created.
SQL>
SQL>
SQL> insert into p values ( 2 );
1 row created.
SQL>
SQL>
SQL> column columns format a30 word_wrapped
SQL> column tablename format a15 word_wrapped
SQL> column constraint_name format a15 word_wrapped
SQL>
SQL> select table_name, constraint_name,
2 cname1 || nvl2(cname2,","||cname2,null) ||
3 nvl2(cname3,","||cname3,null) || nvl2(cname4,","||cname4,null) ||
4 nvl2(cname5,","||cname5,null) || nvl2(cname6,","||cname6,null) ||
5 nvl2(cname7,","||cname7,null) || nvl2(cname8,","||cname8,null)
6 columns
7 from ( select b.table_name,
8 b.constraint_name,
9 max(decode( position, 1, column_name, null )) cname1,
10 max(decode( position, 2, column_name, null )) cname2,
11 max(decode( position, 3, column_name, null )) cname3,
12 max(decode( position, 4, column_name, null )) cname4,
13 max(decode( position, 5, column_name, null )) cname5,
14 max(decode( position, 6, column_name, null )) cname6,
15 max(decode( position, 7, column_name, null )) cname7,
16 max(decode( position, 8, column_name, null )) cname8,
17 count(*) col_cnt
18 from (select substr(table_name,1,30) table_name,
19 substr(constraint_name,1,30) constraint_name,
20 substr(column_name,1,30) column_name,
21 position
22 from user_cons_columns ) a,
23 user_constraints b
24 where a.constraint_name = b.constraint_name
25 and b.constraint_type = "R"
26 group by b.table_name, b.constraint_name
27 ) cons
28 where col_cnt > ALL
29 ( select count(*)
30 from user_ind_columns i
31 where i.table_name = cons.table_name
32 and i.column_name in (cname1, cname2, cname3, cname4,
33 cname5, cname6, cname7, cname8 )
34 and i.column_position <= cons.col_cnt
35 group by i.index_name
36 )
37 /
TABLE_NAME CONSTRAINT_NAME COLUMNS
------------------------------ --------------- ------------------------------
C SYS_C009529 Y
SQL>
SQL>
SQL> drop table c;
Table dropped.
SQL>
SQL> drop table p;
Table dropped.
SQL>
SQL>
SQL>
SQL>
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Table/References - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия