Download - SQL and SQL*Plus Interaction
![Page 1: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/1.jpg)
SQL and SQL*Plus Interaction
SQL*PlusSQL*Plus
SQL StatementsSQL StatementsBufferBuffer
SQL StatementsSQL Statements
Server
Query ResultsSQL*PlusSQL*Plus CommandsCommands
Formatted ReportFormatted Report
![Page 2: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/2.jpg)
SQL Statements Vs SQL*Plus Commands
SQLSQLstatementsstatements
SQL
• A command language used for communication with the Oracle Server to access data
• When a SQL statement is entered, it is stored in a part of memory called the SQL buffer and remains there until a new statement is entered
SQL*Plus
• An environment
• SQL*Plus is an Oracle tool that recognizes and submits SQL statements to Oracle Server for execution
• Provides a line editor for modifying SQL statements
• Is entered one line at a time, not stored in the SQL buffer
SQLSQLbufferbuffer
SQL*PlusSQL*Pluscommandscommands
SQL*PlusSQL*Plusbufferbuffer
![Page 3: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/3.jpg)
SQL Statements Vs SQL*Plus Commands SQL
• It is based on ANSI standard SQL
• Keyword can’t be abbreviated
• Statements manipulate data and table definitions in the database
• It does not have a continuation character
• Uses a termination character to execute command immediately
• Uses functions to perform some formatting
SQL*Plus
• Oracle proprietary interface for executing SQL statements
• Keywords can be abbreviated
• Commands do not allow manipulation of values in the database
• Has a dash(-) as a continuation character if the command is longer than one line
• Does not require a termination characters. Commands are executed immediately
• Uses commands to format data
![Page 4: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/4.jpg)
• Log in to SQL*Plus.• Describe the table structure.• Edit your SQL statement.• Execute SQL statements from SQL*Plus
to:– Retrieve, modify, add, and remove data from the
database
• Format, perform calculations on, store, and print query results in the form of reports
• Save SQL statements to files– Create script files to store SQL statements for
repetitive use in the future
• Append SQL statements to files.• Execute saved files.• Load commands from file to buffer to
edit.
Overview of SQL*Plus
![Page 5: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/5.jpg)
Logging In to SQL*Plus
![Page 6: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/6.jpg)
Displaying Table StructureUse the SQL*Plus Use the SQL*Plus DESCRIBEDESCRIBE command to command to display the structure of a table.display the structure of a table.
DESC[RIBE] tablenameDESC[RIBE] tablename
SQL> DESCRIBE deptSQL> DESCRIBE dept
Name Null? Type----------------- -------- ------------DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)
Name Null? Type----------------- -------- ------------DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)
Example:
The result of the command is to see the column The result of the command is to see the column names, data types, as well as whether a column names, data types, as well as whether a column must contain data.must contain data.
![Page 7: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/7.jpg)
Displaying Table StructureSQL> DESCRIBE deptSQL> DESCRIBE dept
Name Null? Type----------------- -------- ------------DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)
Name Null? Type----------------- -------- ------------DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)
Data TypeData Type DescriptionDescriptionNUMBER(p, s)NUMBER(p, s) Number value having a maximum number of digits Number value having a maximum number of digits pp, the , the
number of digits to the right of the decimal point number of digits to the right of the decimal point ss
VARCHAR2(S)VARCHAR2(S) Variable-length character value of maximum size Variable-length character value of maximum size ss
DATE Date and time value between January 1, 4712 BC and Date and time value between January 1, 4712 BC and December 31, 9999 ADDecember 31, 9999 AD
CHAR(S) Fixed-length character value of size Fixed-length character value of size ss
![Page 8: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/8.jpg)
SQL*Plus Editing Commands
• A[PPEND] text
• C[HANGE] / old / new
• C[HANGE] / text /
• CL[EAR] BUFF[ER]
• DEL
• DEL n
• DEL m n
• I[NPUT]
• I[NPUT] text
• L[IST]
• L[IST] n
• L[IST] m n
• R[UN]
• n
• n text
• 0 text
![Page 9: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/9.jpg)
SQL*Plus Editing Commands
• A[PPEND] text• Adds text to the end of the current line
• C[HANGE] / old / new• Changes old text to new text in the current line
• C[HANGE] / text /• Deletes text from the current line
• CL[EAR] BUFF[ER]• Deletes all lines from the SQL buffer
• DEL• Deletes current line
• DEL n• DEL m n
![Page 10: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/10.jpg)
SQL*Plus Editing Commands• I[NPUT]
• Inserts an indefinite number of lines• I[NPUT] text
• Inserts a line consisting of text• L[IST]
• Lists all lines in the SQL buffer• L[IST] n
• Lists one line (specified by n)• L[IST] m n
• Lists a range of lines (from m to n)• R[UN]
• Displays and runs the current SQL statement in the buffer• n
• Specifies the line to make the current line• n text
• Replaces line n with text• 0 text
• Inserts a line before line 1
![Page 11: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/11.jpg)
SQL*Plus Editing Commands
SELECT FEATUER, PAGEFROM NEWSPAPERWHERE SECTION = ‘F’;
ORACLE responds:ERROR at line 1: ORA-0704: invalid column name
TWO Solutions:
1. Retype the whole query2. Use command to correct spelling of FEATUER
![Page 12: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/12.jpg)
SQL*Plus Editing CommandsSELECT FEATUER, PAGEFROM NEWSPAPERWHERE SECTION = ‘F’;
Solution:Name of command: list
SQL>list
Result:1 SELECT FEATUER, PAGE2 FROM NEWSPAPER3* WHERE SECTION = ‘F’
SQL*Plus shows all three lines, and numbered them. It also places * next to line 3, which means it is the line your editing commands are able to affect. But we want to change line 1, so type:
![Page 13: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/13.jpg)
SQL*Plus Editing CommandsSELECT FEATUER, PAGEFROM NEWSPAPERWHERE SECTION = ‘F’;
Solution:Name of command: list 1
SQL>list 1
Result:1* SELECT FEATUER, PAGE
Line 1 is displayed and is now the current line. We change it by typing:
CHANGE /FEATUER/FEATURE
Result:1* SELECT FEATURE, PAGE
![Page 14: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/14.jpg)
SQL*Plus Editing Commands
Check the whole query again with:
list
1 SELECT FEATUER, PAGE2 FROM NEWSPAPER3* WHERE SECTION = ‘F’;
Place / at SQL> prompt and query will be executed.
![Page 15: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/15.jpg)
SQL*Plus Editing CommandsCheck the whole query again with:
list
1 SELECT FEATUER, PAGE2 FROM NEWSPAPER3* WHERE SECTION = ‘F’;
To delete the current line:
del
Check the whole query again with:
list
1 SELECT FEATUER, PAGE2* FROM NEWSPAPER
![Page 16: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/16.jpg)
SQL*Plus Editing CommandsTo clear the whole select statement completely, type:
clear buffer
If you like to append something to the current line, type:
SQL>list 1
1* SELECT FEATUER, PAGE
SQL>append “where it is”
1* SELECT FEATUER, PAGE “where it is”
append places its text right up against the end of the current line, with no spaces in between. To put a space in, type two spaces between the word append and the text.
![Page 17: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/17.jpg)
SQL*Plus Editing CommandsWe may also input a whole new line after the current line:
SQL> list
1 SELECT FEATUER, PAGE2* FROM NEWSPAPER
SQL>input where section = ‘A’
SQL> list
1 SELECT FEATUER, PAGE2 FROM NEWSPAPER3* where section = ‘A’
Run it SQL> /
![Page 18: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/18.jpg)
SQL*Plus File Commands
• SAVE filename
• GET filename
• START filename
• @ filename
• EDIT filename
• SPOOL filename
![Page 19: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/19.jpg)
SQL*Plus File Commands• SAV[E] filename[.ext] [ REPLACE | APP[END] ]
• Saves current contents of SQL buffer to a file. Use APPEND to add to an existing file; use REPLACE to overwrite an existing file. The default extension is .sql.
• GET filename[.ext]• Writes the contents of a previously saved file to the SQL buffer. The default
extension is .sql.• START filename[.ext]
• Runs a previously saved command file.• @ filename
• Runs a previously saved command file (same as START).• ED[IT]
• Invokes the editor and saves the buffer contents to a file named afiedt.buf• ED[IT] filename[.ext]
• Invokes editor to edit contents of a saved file• SPO[OL] [ filename[.ext] | OFF OUT ]
• Stores query results in a file. OFF closes the spool file. OUT closes the spool file and sends the file results to the system printer.
• EXIT• Leaves SQL*Plus.
![Page 20: SQL and SQL*Plus Interaction](https://reader033.vdocuments.us/reader033/viewer/2022061508/568145d8550346895db2db7c/html5/thumbnails/20.jpg)
SQL*Plus File Commands
We can save the SQL created so far:
SQL> save abc.sql
SQL*PLUS responds:
Wrote file abc.sql
Now, the SQL statement is in file abc.sql.