Page 77
SQL*PLUS contains several pseudo columns that provide additional information for a report or listing. You can list these columns in the select, where, order by, or group by clauses. The following contains these columns.
The null places a null value into the column. Alternatively, you can use it in a where condition to evaluate another argument as null. Sysdate is the current date. Rownum is the sequential number of the row, indicating the order in which it was retrieved.
The dual table is used when a value must be introduced and an existing table does not contain that value. This could occur when a date is needed in a report, or possibly in a SQL*FORM. The example shown in Listing 3.53 uses the dual table to select and display the current date.
Listing 3.53L_03_53.TXTExample of a Pseudo Column and the Dual Table
31-SQL> select sysdate from dual; SYSDATE MAY-97
The Describe or Desc command is used to list the contents of a table. It is an extremely useful command when you are not certain of a table's column names or the format of a column. Listing 3.54 includes an example of a Describe command in use.
Listing 3.54L_03_54.TXTExample of the Describe Command
SQL> describe employee Name Null? Type --------------------------------------- ---- PAYROLL_NUMBER NOT NULL NUMBER(4) LAST_NAME VARCHAR2(15) FIRST_NAME VARCHAR2(15) ABSENCES NUMBER(2) WAGES NUMBER(8,2) STREET VARCHAR2(20) CITY VARCHAR2(15) STATE CHAR(2) PHONE CHAR(13) SOCIAL_SECURITY_NUMBER CHAR(11) EMPLOYMENT_DATE DATE
continues
Page 78
Listing 3.54Continued
BIRTH_DATE DATE CURRENT_POSITION VARCHAR2(15) FK_DEPARTMENT CHAR(4) GENDER CHAR(2)
The Tab table contains the names of the tables to which you have access to on your Oracle id. The table is a view of the data dictionary and contains several fields about the table that may be of interest. When you need to know the name of a table, this view may be of interest. Listing 3.55 shows the results of a select statement using this table.
Listing 3.55L_03_55.TXTExample of the Tab Table
SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- --------- BONUS TABLE CUSTOMER TABLE DEPARTMENT TABLE DEPT TABLE DEPTCOST VIEW DUMMY TABLE EMP TABLE EMPLOYEE TABLE EMP_DEPT TABLE EMP_GLASSES TABLE EMP_HIST TABLE EMP_TOOLS TABLE GLASSES TABLE ITEM TABLE LOADTAB TABLE ORD TABLE PRICE TABLE PRODUCT TABLE SALES VIEW SALGRADE TABLE SECTAB TABLE TNAME TABTYPE CLUSTERID ------------------------------ ------- --------- TESTLOAD TABLE TOOLCOST VIEW TOOLS TABLE WGE_MAINT TABLE 25 rows selected.
Page 79
This chapter introduced you to the SQL language. The first SQL command is
select, which acquires data from the Oracle7 database.
Select is used in all the Oracle products. The
command has five clauses. The from clause identifies the database tables that contain the data.
The where clause identifies the selected records. The
group by clause determines the set of records to perform
group by functions on. The having clause identifies selected records.
This functionality is similar to the where clause, except it uses group by functions to
identify records. The order by clause sorts the records for display in the specified manner.
The select command has a large array of functions. The date functions enable you to perform arithmetic on two dates. The character functions enable you to combine, extract, and modify character expressions. You can use the number functions to format numbers. The decode function enables you to substitute one value for one that is selected. Finally, you have pseudo columns such as sysdate and rownum that return special values.
You should now be comfortable with extracting data by using the select command. The next chapter shows you how to create formatted reports in SQL*PLUS. The Developer 2000 tool set has Oracle Reports 2.5, which is a more powerful tool. If you do not have access to Oracle Reports 2.5, you will find SQL*PLUS a good report writing tool.
Page 80
Page 81
4 Formatting Reports in SQL*PLUS83
5 Combining Tables and Rows103
6 Defining the Database Objects119
7 Modifying Your Tables with DML Commands151
Page 82