Oracle PL/SQL/Table/Describe
Содержание
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>