شرح كيفية تكبير عرض النتائج ب Sqlplus

[أسامة موسى]

Dear all this is the good solution

The document list the features of Oracle extensions to the interactive SQL language. The extensions largely relate to the output formatting, interactive SQL environment settings, interface between SQL statements and users.



1. SQL Commands and Command File

SQL commands can be used to

create database, tables, indexes;

grant/revoke user s privileges of accessing oracle databases

retrieve update, delete and query data in tables

many other database management, system maintenance chords.

One or more SQL commands make a command file, usually named with .sql extension. Store a SQL commands in a file is recommended since the file can be run, modified easily.



(1) Two ways of Running a SQL Command File

SQL > start command_file_name -- No extension needed if the file is extended with .sql

SQL > @command_file_name -- No extension needed if



(2) Run command File Substitution Variables

SQL Statements in command file may contain substitution variables. If numbers (1, 2, 3, and etc.) are used to name the variable, then user can provide arguments for the substitution variables at command line:

* SQL > start command_file arg1 arg2

assuming that the command file has two substitution variables named as 1 and 2 and prefixed with &.

No semi-colon necessary after the last argument.



(3) Rerun the commands in the Buffer

After you type a SQL command or run a SQL command file, the command(s) are stored in buffer. To run the command again, you can

Type run : SQL > run

Type slash: SQL > /



(4) Save Tested Query into File



SAV[E] file_name[.ext] CRE[ATE] | REP[LACE] | APP[END]



2. Output Formatting



The COLUMN ( or COL ) command defines the display attributes of a field. After the display attributes are defined, the column values will be displayed in the format defined in COL command. The display attributes can be cleared, turned on or off, and also it can be used by other columns.



(1) The COLUMN command can be used to format the output

COLUMN ename HEADING Employee | Name FORMAT model

where

ename : the name of field/column of a table.

Employee

Name : the new field title when query result is displayed.

model: A10: print a text field with at most 10 columns

$9,999.990 : put dollar sign before value of a numeric column. Use comma after each 3

digits and 3 digits after decimal point.

The order of HEADING and FORMAT clause in column command are not important. Moreover, either of the two clauses can be used independently. The vertical bar, '|', in string represent a new line character.



(2) Display, Clear, Turn Off and On Column Display Attributes

SQL > COLUMN -- show the display attributes of all fields and ON or OFF

SQL> COLUMN ename CLEAR -- clear the display attributes of ename

SQL > COL ename OFF -- turn off the display attributes

SQL > COL ename ON -- turn on the display attributes



(3) Apply display format of a Field to Other Field



The following example show how to apply the format of one field to another field:



SQL > COLUMN price HEADING Retail Price format $9,999.99

SQL > COLUMN total LIKE price HEADING Total



Remember that you have to have HEADING. Otherwise the Retail|Price will be used as heading for the total field.

(4) Wrap Field In Word

If the field width is set short than some field value of a record, the value will be wrapped to next line. If you don t want the word to be cut in the middle, use

SQL > COL field_name WORD_WRAPPED



3. Interacting with User



You can use a substitution variable to accept value from user in interactive SQL. The substitution variables may or may not defined before they are used in the SQL statements.



(1) Define a Substitution Variable:

SQL > DEFINE supName = John



Define variable supName and initialize the variable John. The DEFINE cannot be added in the SQL buffer while you can use it interactively and used in SQL script file.



(2) How to use variable in the SQL statement?

If a variable is used in a SQL statement, the variable is called substitution variable since the value of the variable will be used to replace the variable name. The substitution variables in SQL statements must be prefixed with & sign. If the value in a variable is to be string, enclose &variable with single quotes. If the value does not need the single quotes, you shout not enclose the variable within quotes. For example



SQL > SELECT * FROM s where sname = &supName ;

SQL > SELECT * FROM s where status > &st ;



While the first query will retrive supplier(s) whose name is John and second query retrieve all

suppliers which status is larger than the value in the substitution variable st.



(3) Use Undefined Variables



When you use undefined variables in a SQL statement, you will be prompt automatically for values for each of the variables used in the statement when the statement is executed. While the defined variable in the SQL statement will use the value you gave to the variable previously until you have the value by DEFINE, UNDEFINE or ACCEPT command is applied to the variable.



(4) Use Number as Substitution Variable



Number can be used to define user variables in the following way:

SELECT * FROM S WHERE Status BETWEEN &1 AND &2 ;

When numbers used as user variables in a SQL file, the arguments passed to the command file will replace the number substitution variables.



(5) Concatenated Variable with Other Alpha-numeric characters



DEFINE col= B

SELECT * FROM p WHERE color = &col.lue // will list blue color parts.



(6) Start Command File with Parameter



Syntax: STA[RT] file_name[.ext] [arg1 arg2 ]



The argument I will replace number substitution variable i. As for the non digital substitution variables in the file, SQLPLUS will prompt for their values:



Tmp.sql contains:

SELECT * FROM s where Status BETWEEN &1 AND &2 AND City = %city ;



Run the file with

START tmp 10 20

Will substitute variable 1 with 10 and 2 with 20 and prompt for value for variable city.



(7) Prompt User



To prompt user what to do, you can use PROMPT command followed by text without included in single quotes.



PROMPT Enter a value for status like 20, 50



You cannot add prompt command in the buffer. It can only be used in SQL file, and be tested interactively.



(8) Accept Value from User



Syntax: ACC[EPT] variable [NUM[bER] ] | [CHAR ]

[ PROMPT text | NOPR[OMPT]

[ HIDE ]

Accept value for a variable with either numerical or character type, with or without prompt. It also allow you hide the string typed on the screen.



4. Environment Variables



(1) Change Page Size

The Page size determine the number of rows after which the table heading will reprinted. If you set the page size to 20, then the table heading will be printed for each 20 rows in the select statement.



SQL > set pagesize 20 -- set page size

SQL > set linesize 100 -- 100 column per line.



(2) Set Underline Character



In the table heading, each field name is underlined. The default the under line character is - and can be changed by setting the following environment variable:



SQL > set underline =



After the above command, the table heading will be underlined with = instead of - .

(3) Set Record Separator

The records from select statement can be separated by a line of character:



SQL > SET RECSEPCHAR - -- records may be separated by a line of -



Whether the record will be separated is determined by the value of anther variable, called record separator.

SQL > SET RECSEP WRAPPED -- a record separating line will be printed if a record is

wrapped. Otherwise no line separator will be printed out.

SQL > SET RECSEP EACH -- line separator is printed out after each record.

SQL > SET RECSEP OFF -- set no record separator line.



5. Organizing Report



The result from a select statement can be formatted somehow by SQL* Plus extension. You can add title to your report, suppress duplicated value in rows and computer summary data from the selected record.



(1) Setting the Title of Your Report



(2) Suppressing Duplicate Values

When a record set selected and sorted by some column, the values in the sorted column may repeated in different records. The duplicated value can be suppressed by the following command:



BREAK ON col_name [sKIP n | PAGE] [ ON col_name [sKIP n | PAGE] ]



If the col_name is used in the ORDER BY clause, then the duplicated value on the column will be suppressed. When a different value appear in that column, n rows or a page will be skipped before the records with different value being printed.



(3) Compute Summary Data



To apply one summary function to one or more columns after certain break, use compute command:



COMPUTE function_name OF col, col, , ON break_col | REPORT



Function name is one the following function names:

SUM, MIN, MAX, AVG, STD, VAR, COUNT, NUM

If break_col is used then the computation is done at each break and if REPORT is used then

the computation is done at the end of the report.



Example:



break on color skip 1

compute avg of pnum weight on color;

compute count of pnum on color;

select color, pnum, weight from p

order by color ;



SQL> @tmp2



COLOR PNUM WEIGHT

---------- ----- ----------

Blue p3 17

p5 12

********** ----- ----------

avg 14.5

count 2



Green p2 17

********** ----- ----------

avg 17

count 1



Red p4 14

p1 12

p6 19

********** ----- ----------

avg 15

count 3



Yellow p7 40

p8 3

********** ----- ----------

avg 21.5

count 2





8 rows selected.



* Each compute command can add only one function to multiple columns.

(4) List and Clear Breaks and Computes

To list all breaks and computes use the following commands:



SQL > BREAK -- list all breaks

SQL > CLEAR BREAK -- clear all breaks

SQL > COMPUTE -- list all summary functions

SQL > CLEAR COMPUTE -- clear all computes



(5) Set Time On and OFF

SQL > SET TIME ON | OFF

12:03:10 SQL > will be displayed as prompt instead of SQL > if time variable is set to on.

(6)



6. Report Titles



The output from SQL SELECT statement is a report. You can give title on the top of each page or a title on the bottom of each page depending on whether TTITLE (top title) or BTITLE (bottom title) command is used. The top title can also contain the value in some ( break ) column and the page number.



Example 1:

SQL > TTITLE CENTER Supplies Report on March-3-1997 SKIP 1

SQL > SELECT * FROM sp



The sp table contents are display with line Supplies Report on March-3-1997 displayed on the top-center of each page of the report. Between the report title and contents, there 1 blank line.



Example 2:

SQL > TTITLE OFF -- turn off the top title

SQL > BTITLE SKIP 2 LEFT Supplies Report RIGHT Page No. FORMAT999 SQL.Pno

SQL > SELECT * FROM sp



The contents of sp table is displayed in pages. At the end of each page, a page title is displayed at the left-bottom of the page. Between the contents and the page title, there are 2 blank lines. On the left is the report title is on the left. On the right, Page No. 1 will be displayed on the bottom of the first page and the number for the page is formatted with 3 columns.



Example 3: Put field name in the report title:



SQL > BTITLE OFF -- turn off bottom title

SQL > BREAK ON snum SKIP PAGE -- suppress the duplicate value in sunum

SQL > COL snum NEW_VALUE varsnum -- save every value value in varsnum

SQL > TTITLE LEFT Report on Suppliers skip 1 Supplier: varsnum Skip 2

SQL > SELECT * from sp order by snum, pnum ;



Report on Supplies

Supplier: s1



PNUM QTY

----- ----------

p1 300

p2 200

p3 400

p4 200

p5 100

p6 100



Report on Supplies

Supplier: s2



PNUM QTY

----- ----------

p1 300

p2 400



Report on Supplies

Supplier: s3



PNUM QTY

----- ----------

p2 200



Report on Supplies

Supplier: s4



PNUM QTY

----- ----------

p2 200

p4 300

p5 400



Report on Supplies

Supplier: s6



PNUM QTY

----- ----------

p1 1

p2 2

p3 3

p4 4

p5 5

p5 10

p5 10

p6 6



20 rows selected.



SQL> spool off





7. Save and Print SQL Result



SQL > SPOOL tmp.txt -- save everything displayed on the screen into tile tmp.txt

SQL > select * from s ; -- sql command and contents of s will be saved into text file

SQL > SPOOL OFF -- close the file.



Before you turn off the spooling, you can send the whatever in the file into printer by



SQL > SPOOL OUT





8. Connect to Remote Database



Connect SQLPLUS command can be used to connect a user to any local or remote database if SQL*NET and appropriate driver is installed.



9. Copy Table from One Database to Another Database



A table in one database can be copied into another database by the following command:



SQL> COPY FROM scott/tiger@TEST -

SQL> TO wang/wang -

SQL> Create Emp -

SQL> USING SELECT * FROM EMP ;



The above example copy the table EMP from scott s database into wang s database. Both the table structure and data are copied from the source to the destination.



Both the source and the destination databases could be remote databases. TEST in the example above is the UID for the scott s database. If database located at other location, not on the same machine, then use the full database specification string. The database specification string is different depending on the SQL*NET protocol used. Use SQL*NET related document.



TO CLAUSE: The TO clause is optional in COPY command if you want to copy the table from another database to the default database. The default database is the database you are connected to.



CREATE CLAUSE: Create TableName ( list_of_field_name )

If you want to use the field names of source table for destination table, you don t need to list the field names in the CREATE clause.



USING clause: Using clause consists of USING and a select_statement.

The select statement determine the destination table s structure and contents.



10. SQL/PLUS login File and ORACLE_PATH



A login.sql file can be added to your homework directory so that the commands in the file will be executed every time you start SQL*PLUS. To make login.sql run every time you start SQLPLU in any sub-directory, you need put your home directory in the ORACLE_HOME environment variable in the .profile for

kshell.



The following example sets the pagesize and define a variable, named _date and holding the date when you start the SQLPLUS:



login.sql:

set pagesize 22

set termout off

break on today

column today new_value _date -- associate col. Name with a variable

select to_char( sysdate, fmMonth DD, YYYY ) today

from dual ;

clear breaks

set termout on



Usually, a SQL command file is executable from the current working directory. However, if you have a set of SQL command files which need to be executed from time to time, then this is what you may do:

1. Put the frequently used SQL command files in one sub-directory, and

2. Put the path in the ORACLE_PATH variable in the .profile file.

3. Log out and log in the system again, all the SQL command files in that sub-directory will be executable from the any sub-directory.





11. More SQL/PLUS Environmental Variables



The Session environment can set by SET commands on the following arguments:



SET ARRAY [ 20 | n ]



Set number of rows SQL*Plus fetched at one time. Larger value will increate the efficiency, not the result. N is between 1 to 5000.



SET AUTO[COMMIT] { OFF | ON | IMM[EDIATE] }

OFF: no commit after each SQL statement

ON : automatically commit pending changes to database.

IMM: Same as ON.



SET BLO[CKTERMINATOR] { | c }



Set a non-alphanumeric character used to end the block to c.



SET COM[PATIBILITY] {V5 | V6 }



Determine whether COMMIT and ROLLBACK command will be save in the buffer or not. V5 don t store those two command while V6 does.



SET CON[CAT] { . | c | OFF | ON }



Set the character which ends the substitution variable and other string to different character than . or turns the separator on or off.



SET COPYC[OMMIT] { 0 | n }



Set the number of batches before the SQLPLUS commit the changes when copy command is used to copy records from one database to another database. If 0 is used, then no commit until the end of copy command. The number of records before commit is determined by n and the array size.



SET DEF[iNE] { & | c | OFF | ON }



Set the character used to prefix the substitution variable to c. Off and On will ask SQLPLUS to scan or not to scan SQL statement for substitution variable in the statement.



SET HEA[DING] { ON | OFF }



Set table heading on or off when records are displayed on screen.



SET HEADS[EP]] { | | OFF | ON }



Change the character used to separate the field/column names in the table heading.



SET HEADS[EP]] { 80 | n }



Change the line size and 1 <= n < 32,767





SET NEWP[AGE] (1 | n }



Set the number of blank lines between the beginning of the page the top line of that page.



SET NULL text



Set the text string used for displaying NULL values.



SET NUMF[ORMAT] format



Set the default format for numbers.



SET NUM[WIDTH] { 10 | n }



Set default width for displaying numbers.



SET PAGES[iZE] { 14 | n }



Set the number of lines from the top title to the end of page.



SET SHOW [MODE] { ON | OFF }



Controls whether SQL*Plus lists the old and new settings of SQL*Plus system variable when you change the setting with SET command.





Many more variables that Set command can set.



SET SERVEROUTPUT ON SIZE 5000


It will allow you to print message from PL/SQL with DBMS_OUTPUT package. There is a buffer size limit (default to 2000 byte). If the number is exceeded, error ORA-20000 ORU-10027, Buffer overflow, limit of 2000 byte.



Use the following command to show the server output setting:



SHOW SERVEROUTPUT



to see mode, buffer size and format of server output.