Oracle PL/SQL Tutorial/Table/References

Материал из SQL эксперт

Перейти к: навигация, поиск

Reference column from another column

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>


Refernece a table

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>