Example 1: Specifying a comment for columns and tables
SQL> -- Change the comment for the WORK_STATUS table:
SQL> COMMENT ON TABLE WORK_STATUS IS
cont> 'Links a status code with 1 of 3 statuses' ;
SQL> SHOW TABLE WORK_STATUS
Information for table WORK_STATUS
Comment on table WORK_STATUS: Links a status code with 1 of 3 statuses
.
.
.
SQL> -- Create a comment for the DEPARTMENT_CODE
SQL> -- column in the DEPARTMENTS table:
SQL> COMMENT ON COLUMN DEPARTMENTS.DEPARTMENT_CODE IS
cont> 'Also used in JOB_HISTORY table';
SQL> SHOW TABLE DEPARTMENTS
Information for table DEPARTMENTS
Comment on table DEPARTMENTS:
information about departments in corporation
Columns for table DEPARTMENTS:
Column Name Data Type Domain
----------- --------- ------
DEPARTMENT_CODE CHAR(4) DEPARTMENT_CODE_DOM
Comment: Also used in JOB_HISTORY table
.
.
.
Example 2: Specifying a comment containing more than one string
literal
SQL> COMMENT ON COLUMN EMPLOYEES.EMPLOYEE_ID IS
cont> '1: Used in SALARY_HISTORY table as Foreign Key constraint' /
cont> '2: Used in JOB_HISTORY table as Foreign Key constraint';
SQL> SHOW TABLE (COL) EMPLOYEES;
Information for table EMPLOYEES
Columns for table EMPLOYEES:
Column Name Data Type Domain
----------- --------- ------
EMPLOYEE_ID CHAR(5) ID_DOM
Comment: 1: Used in SALARY_HISTORY table as Foreign Key constraint
2: Used in JOB_HISTORY table as Foreign Key constraint
Primary Key constraint EMPLOYEES_PRIMARY_EMPLOYEE_ID
LAST_NAME CHAR(14) LAST_NAME_DOM
FIRST_NAME CHAR(10) FIRST_NAME_DOM
MIDDLE_INITIAL CHAR(1) MIDDLE_INITIAL_DOM
ADDRESS_DATA_1 CHAR(25) ADDRESS_DATA_1_DOM
ADDRESS_DATA_2 CHAR(20) ADDRESS_DATA_2_DOM
CITY CHAR(20) CITY_DOM
STATE CHAR(2) STATE_DOM
POSTAL_CODE CHAR(5) POSTAL_CODE_DOM
SEX CHAR(1) SEX_DOM
BIRTHDAY DATE VMS DATE_DOM
STATUS_CODE CHAR(1) STATUS_CODE_DOM
Example 3: Adding a Comment to a Trigger
SQL> COMMENT ON TRIGGER EMPLOYEE_ID_CASCADE_DELETE IS
cont> 'When an employee is deleted from EMPLOYEES, delete'/
cont> 'corresponding records from the other tables in the'/
cont> 'database.';
SQL> SHOW TRIGGER EMPLOYEE_ID_CASCADE_DELETE
EMPLOYEE_ID_CASCADE_DELETE
Source:
EMPLOYEE_ID_CASCADE_DELETE
BEFORE DELETE ON EMPLOYEES
(DELETE FROM DEGREES D WHERE D.EMPLOYEE_ID =
EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
(DELETE FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID =
EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
(DELETE FROM SALARY_HISTORY SH WHERE SH.EMPLOYEE_ID =
EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
-- Also, if an employee is terminated and that employee
-- is the manager of a department, set the manager_id
-- null for that department.
(UPDATE DEPARTMENTS D SET D.MANAGER_ID = NULL
WHERE D.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW.
.
.
.
Comment: When an employees is deleted from EMPLOYEES, delete
corresponding records from the other tables in the
database.
Example 4: Adding Comments to Multiple Columns in a Table
SQL> COMMENT ON JOBS (JOB_CODE is 'Required column',
cont> WAGE_CLASS is 'Valid values are: 1, 2, 3, or 4');
SQL> SHOW TABLE (COLUMNS) JOBS;
Information for table JOBS
Columns for table JOBS:
Column Name Data Type Domain
----------- --------- ------
JOB_CODE CHAR(4) JOB_CODE
Comment: Required column
Missing Value: None
WAGE_CLASS CHAR(1) WAGE_CLASS
Comment: Valid values are: 1, 2, 3, or 4
JOB_TITLE CHAR(20) JOB_TITLE
Missing Value: None
MINIMUM_SALARY INTEGER(2) SALARY
MAXIMUM_SALARY INTEGER(2) SALARY