Oracle PL/SQL/Table/Describe

Материал из SQL эксперт
Перейти к: навигация, поиск

Check the view structure

   <source lang="sql">
  

SQL> SQL> SQL> SQL> create table emp(

 2           emp_id                integer         primary key
 3          ,lastname               varchar2(20)    not null
 4          ,firstname              varchar2(15)    not null
 5          ,midinit                varchar2(1)
 6          ,street                 varchar2(30)
 7          ,city                   varchar2(20)
 8          ,state                  varchar2(2)
 9          ,zip                    varchar2(5)
10          ,shortZipCode                   varchar2(4)
11          ,area_code              varchar2(3)
12          ,phone                  varchar2(8)
13          ,company_name           varchar2(50));

Table created. SQL> SQL> SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values

 2                      (1,"Jones","Joe","J","1 Ave","New York","NY","11202","1111","212", "221-4333","Big Company");

1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values

 2                      (2,"Smith","Sue","J","1 Street","New York","NY","11444","1111","212", "436-6773","Little Company");

1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values

 2                      (3,"X","Peggy","J","1 Drive","New York","NY","45502","2222","212", "234-4444","Medium Company");

1 row created. SQL> SQL> create or replace view phone_list as

 2  select emp_id, firstname || " " || midinit || ". " || lastname as name,
 3   "(" || area_code || ")" || phone as telephone#
 4  from emp;

View created. SQL> SQL> SQL> SQL> desc phone_list

Name                                                                                                  Null?    Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
EMP_ID                                                                                                NOT NULL NUMBER(38)
NAME                                                                                                   VARCHAR2(39)
TELEPHONE#                                                                                             VARCHAR2(13)

SQL> select * from phone_list;

   EMP_ID NAME                                    TELEPHONE#

--------------------------------------- -------------
        1 Joe J. Jones                            (212)221-4333
        2 Sue J. Smith                            (212)436-6773
        3 Peggy J. X                              (212)234-4444

3 rows selected. SQL> SQL> drop view phone_list; View dropped. SQL> SQL> drop table emp; Table dropped.


 </source>
   
  


describe a procedure

   <source lang="sql">
 

SQL> SQL> describe dbms_output PROCEDURE DISABLE PROCEDURE ENABLE

Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
BUFFER_SIZE                    NUMBER(38)              IN     DEFAULT

PROCEDURE GET_LINE

Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
LINE                           VARCHAR2                OUT
STATUS                         NUMBER(38)              OUT

PROCEDURE GET_LINES

Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
LINES                          TABLE OF VARCHAR2(32767) OUT
NUMLINES                       NUMBER(38)              IN/OUT

PROCEDURE GET_LINES

Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
LINES                          DBMSOUTPUT_LINESARRAY   OUT
NUMLINES                       NUMBER(38)              IN/OUT

PROCEDURE NEW_LINE PROCEDURE PUT

Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
A                              VARCHAR2                IN

PROCEDURE PUT_LINE

Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
A                              VARCHAR2                IN

SQL> SQL>

 </source>
   
  


describe user_tables

   <source lang="sql">
 

SQL> SQL> SQL> describe user_tables

Name                                                                                                      Null?    Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
TABLE_NAME                                                                                                NOT NULL VARCHAR2(30)
TABLESPACE_NAME                                                                                                    VARCHAR2(30)
CLUSTER_NAME                                                                                                       VARCHAR2(30)
IOT_NAME                                                                                                           VARCHAR2(30)
STATUS                                                                                                             VARCHAR2(8)
PCT_FREE                                                                                                           NUMBER
PCT_USED                                                                                                           NUMBER
INI_TRANS                                                                                                          NUMBER
MAX_TRANS                                                                                                          NUMBER
INITIAL_EXTENT                                                                                                     NUMBER
NEXT_EXTENT                                                                                                        NUMBER
MIN_EXTENTS                                                                                                        NUMBER
MAX_EXTENTS                                                                                                        NUMBER
PCT_INCREASE                                                                                                       NUMBER
FREELISTS                                                                                                          NUMBER
FREELIST_GROUPS                                                                                                    NUMBER
LOGGING                                                                                                            VARCHAR2(3)
BACKED_UP                                                                                                          VARCHAR2(1)
NUM_ROWS                                                                                                           NUMBER
BLOCKS                                                                                                             NUMBER
EMPTY_BLOCKS                                                                                                       NUMBER
AVG_SPACE                                                                                                          NUMBER
CHAIN_CNT                                                                                                          NUMBER
AVG_ROW_LEN                                                                                                        NUMBER
AVG_SPACE_FREELIST_BLOCKS                                                                                          NUMBER
NUM_FREELIST_BLOCKS                                                                                                NUMBER
DEGREE                                                                                                             VARCHAR2(10)
INSTANCES                                                                                                          VARCHAR2(10)
CACHE                                                                                                              VARCHAR2(5)
TABLE_LOCK                                                                                                         VARCHAR2(8)
SAMPLE_SIZE                                                                                                        NUMBER
LAST_ANALYZED                                                                                                      DATE
PARTITIONED                                                                                                        VARCHAR2(3)
IOT_TYPE                                                                                                           VARCHAR2(12)
TEMPORARY                                                                                                          VARCHAR2(1)
SECONDARY                                                                                                          VARCHAR2(1)
NESTED                                                                                                             VARCHAR2(3)
BUFFER_POOL                                                                                                        VARCHAR2(7)
ROW_MOVEMENT                                                                                                       VARCHAR2(8)
GLOBAL_STATS                                                                                                       VARCHAR2(3)
USER_STATS                                                                                                         VARCHAR2(3)
DURATION                                                                                                           VARCHAR2(15)
SKIP_CORRUPT                                                                                                       VARCHAR2(8)
MONITORING                                                                                                         VARCHAR2(3)
CLUSTER_OWNER                                                                                                      VARCHAR2(30)
DEPENDENCIES                                                                                                       VARCHAR2(8)
COMPRESSION                                                                                                        VARCHAR2(8)
DROPPED                                                                                                            VARCHAR2(3)
  
 </source>
   
  


Show the table structure

   <source lang="sql">

SQL> SQL> create table employee_history ( employee_id number(6) not null,

 2                                  salary           number(8,2),
 3                                  hire_date        date default sysdate,
 4                                  termination_date date,
 5                                  termination_desc varchar2(4000),
 6                                  constraint       emphistory_pk
 7                                  primary key (employee_id, hire_date)
 8                                );

Table created. SQL> describe employee_history;

Name                                        Null?    Type
------------------------------------------- -------- -------------
EMPLOYEE_ID                                 NOT NULL NUMBER(6)
SALARY                                       NUMBER(8,2)
HIRE_DATE                                   NOT NULL DATE
TERMINATION_DATE                             DATE
TERMINATION_DESC                             VARCHAR2(4000)

SQL> SQL> drop table employee_history;


 </source>