1 / 67

Chapter 2

Chapter 2. SQL*Plus Overview. SQL*Plus Fundamentals.

orde
Download Presentation

Chapter 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 2 SQL*Plus Overview

  2. SQL*Plus Fundamentals • When you start SQL*Plus on Windows, it prompts you for the username, password, and host string. The host, or connect, string is the database alias name. If you omit the connect string, SQL*Plus tries to connect you to the local database defined in the ORACLE_SID variable.

  3. SQL*Plus Fundamentals:Connecting to SQL*Plus (1/4) • Once you are in SQL*Plus, you can connect to another database or change your connection by using the CONNECT command, with this syntax: • CONNECT <username>/<password>@<connectstring> • The slash separates the username and password. The connect string following @ is the database alias name. If you omit the password, you will be prompted to enter it. You may omit the connect string to connect to a local database.

  4. SQL*Plus Fundamentals:Connecting to SQL*Plus (2/4) • For DOS and Unix platforms, SQL*Plus comes in character mode. You can invoke and connect to SQL*Plus using the sqlplus command, with this syntax: • sqlplus username/password@connectstring • sqlplusw username/password@connectstring • If you invoke the tool with just sqlplus, you will be prompted for username and password. If you invoke SQL*Plus with a username, you will be prompted for a password.

  5. SQL*Plus Fundamentals:Connecting to SQL*Plus (3/4) • Use the DISCONNECT command to disconnect your session from the database. To connect again, use the CONNECT command. • SQL> DISCONNECT • To exit from SQL*Plus, use the EXIT command. On platforms where a return code is used, you can provide a return code while exiting. You may also use the QUIT command to complete the session. EXIT and QUIT are synonymous.

  6. SQL*Plus Fundamentals:Connecting to SQL*Plus (4/4) • To change the password, you can use the PASSWORD command. The password will not be echoed to the screen. The username argument is optional. If it is not included, the command changes the current user’s password. To change another user’s password, you must have the ALTER USER privilege. • SQL> PASSWORD scottChanging password for SCOTTNew password: *****Retype new password: *****Password changed

  7. SQL*Plus Fundamentals:Using SQL*Plus (1/15) • Once you are connected to SQL*Plus, you get the SQL> prompt. This is the default prompt, which can be changed using the SET SQLPROMPT command. Type the command you wish to execute at this prompt. With SQL*Plus, you can enter, edit, and execute SQL statements, perform database administration, and execute statements interactively by accepting user input. You can also format query results and perform calculations.

  8. SQL*Plus Fundamentals:Using SQL*Plus (2/15) • Entering SQL Statements (1/2) • A SQL statement can spread across multiple lines, and the commands are case insensitive. The previously executed SQL statement will always be available in the SQL buffer. The buffer can be edited or saved to a file. You can terminate a SQL statement in any of the following ways: • End with a semicolon (;). The statement is completed and executed. • Enter a slash (/) on a new line by itself. The statement in the buffer is executed. • Enter a blank line. The statement is saved in the buffer. • The RUN command can be used instead of a slash to execute a statement in the buffer. The SQL prompt returns when the statement has completed execution. You can enter your next command at the prompt.

  9. SQL*Plus Fundamentals:Using SQL*Plus (3/15) • Entering SQL Statements (2/2) • Only SQL statements and PL/SQL blocks are stored in the SQL buffer; SQL*Plus commands are not stored in the buffer.

  10. SQL*Plus Fundamentals:Using SQL*Plus (4/15) • Entering SQL*Plus Commands • SQL*Plus has its own commands to perform specific tasks on the database, as well as to format the query results. Unlike SQL statements, which are terminated with a semicolon or a blank line, SQL*Plus commands are entered on a single line. Pressing Enter executes the SQL*Plus command. • If you wish to continue a SQL*Plus command onto the next line, you must end the current line with a hyphen (-), which indicates command continuation. This is in contrast to SQL statements, which can be continued to the next line without a continuation operator.

  11. SQL*Plus Fundamentals:Using SQL*Plus (5/15) • Getting Information with the DESCRIBE Command (1/3) • The DESCRIBE command is used to get information on the database objects. Using DESCRIBE on a table or view shows the columns, its datatypes, and whether or not each column can be NULL. Using DESCRIBE on a stored program such as procedure or function shows the parameters that need to be passed in out, its datatype, and if there is a default value. You can abbreviate this command to the first four characters or more—DESC, DESCR, and DESCRIB are valid.

  12. SQL*Plus Fundamentals:Using SQL*Plus (6/15) • Getting Information with the DESCRIBE Command (2/3) • If you’re connected to the HR schema, and need to see the tables and views in this schema, use the following query: • SQL> SELECT * FROM tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------COUNTRIES TABLEDEPARTMENTS TABLEEMPLOYEES TABLEEMP_DETAILS_VIEW VIEWJOBS TABLEJOB_HISTORY TABLELOCATIONS TABLEREGIONS TABLE

  13. SQL*Plus Fundamentals:Using SQL*Plus (7/15) • Getting Information with the DESCRIBE Command (3/3) • The following example uses the DESCRIBE command on a table and on a procedure. • SQL> DESC countriesName Null? Type------------------------------- -------- ------------COUNTRY_ID NOT NULL CHAR(2)COUNTRY_NAME VARCHAR2(40)REGION_ID NUMBER • SQL> DESCRIB Add_Job_HistoryPROCEDURE Add_Job_History Argument Name Type In/Out Default? ------------------- ---------------- ------ -------- P_EMP_ID NUMBER(6) IN P_START_DATE DATE IN P_END_DATE DATE IN P_JOB_ID VARCHAR2(10) IN P_DEPARTMENT_ID NUMBER(4) IN

  14. SQL*Plus Fundamentals:Using SQL*Plus (8/15) • Editing the SQL Buffer (1/8) • The most recent SQL statement executed or entered is stored in the SQL buffer of SQL*Plus. You can run the command in this buffer again by simply typing a slash or using the RUN command. • SQL*Plus provides a set of commands to edit the buffer. Suppose that you want to add another column or add an ORDER BY condition to the statement in the buffer. You do not need to type the entire SQL statement again. Instead, just edit the existing statement in the buffer. • One way to edit the SQL*Plus buffer is to use the EDIT command to write the buffer to an operating system file named afiedt.buf (this is the default filename, which can be changed), and then use a system editor to make changes. • You can use your favorite text editor by defining it in SQL*Plus. For example, to make Notepad your favorite editor, just issue the command DEFINE_EDITOR = NOTEPAD. You need to provide the entire path if the program is not available in the search path.

  15. SQL*Plus Fundamentals:Using SQL*Plus (9/15) • Editing the SQL Buffer (2/8) • Another way to edit the buffer is to use the SQL*Plus editing commands. You can make changes, delete lines, add text, and list the buffer contents using the commands described in the following sections. Most editing commands operate on the current line. You can change the current line simply by typing the line number. All commands can be abbreviated except DEL (which is already abbreviated).

  16. SQL*Plus Fundamentals:Using SQL*Plus (10/15) • Editing the SQL Buffer (3/8) • LIST • The LIST command lists the contents of the buffer. The asterisk indicates the current line. The abbreviated command for LIST is L. • SQL> L 1 SELECT empno, ename 2* FROM emp • SQL> LIST LAST 2* FROM emp • The command LIST m n displays lines from m through n. If you substitute * for m or n, it implies the current line. The command LIST LAST displays the last line.

  17. SQL*Plus Fundamentals:Using SQL*Plus (11/15) • Editing the SQL Buffer (4/8) • APPEND • The APPEND text command adds text to the end of line. The abbreviated command is A. • SQL> A WHERE empno <> 7926 2* FROM emp WHERE empno <> 7926

  18. SQL*Plus Fundamentals:Using SQL*Plus (12/15) • Editing the SQL Buffer (5/8) • CHANGE • The CHANGE /old/new command changes an old entry to a new entry. The abbreviated command is C. If you omit new, old will be deleted. • SQL> C /<>/= 2* FROM emp WHERE empno = 7926 • SQL> C /7926 2* FROM emp WHERE empno =

  19. SQL*Plus Fundamentals:Using SQL*Plus (13/15) • Editing the SQL Buffer (6/8) • INPUT • The INPUT text command adds a line of text. Its abbreviation is I. If text is omitted, you can add as many lines you wish. • SQL> I 3 7777 AND 4 empno = 4354 5SQL> I ORDER BY 1SQL> L 1 SELECT empno, ename 2 FROM emp WHERE empno = 3 7777 AND 4 empno = 4354 5* ORDER BY 1

  20. SQL*Plus Fundamentals:Using SQL*Plus (14/15) • Editing the SQL Buffer (7/8) • DEL • The DEL command used alone or with * deletes the current line. The DEL m n command deletes lines from m through n. If you substitute * for m or n, it implies the current line. The command DEL LAST deletes the last line. • SQL> 3 3* 7777 ANDSQL> DELSQL> L 1 SELECT empno, ename 2 FROM emp WHERE empno = 3 empno = 4354 4* ORDER BY 1SQL> DEL 3 *SQL> L 1 SELECT empno, ename 2* FROM emp WHERE empno =SQL>

  21. SQL*Plus Fundamentals:Using SQL*Plus (15/15) • Editing the SQL Buffer (8/8) • CLEAR BUFFER • The CLEAR BUFFER command (abbreviated CL BUFF) clears the buffer. This deletes all lines from the buffer. • SQL> L 1 SELECT empno, ename 2* FROM emp WHERE empno =SQL> CL BUFFbuffer clearedSQL> LNo lines in SQL buffer.

  22. SQL*Plus Fundamentals:Using Script Files (1/3) • SQL*Plus provides commands to save the SQL buffer to a file, as well as to run SQL statements from a file. SQL statements saved in a file are called a script file. • You can work with script files as follows: • To save the SQL buffer to an operating system file, use the command SAVE filename. If you do not provide an extension, the saved file will have an extension of .sql. • By default, the SAVE command will not overwrite an existing file. If you wish to overwrite an existing file, you need to use the keyword REPLACE. • To add the buffer to the end of an existing file, use the SAVE filename APPEND command. • You can edit the saved file using the EDIT filename command. • You can bring the contents of a script file to the SQL buffer using the GET filename command. • If you wish to run a script file, use the command START filename. You can also run a script file using @filename. • An @@filename used inside a script file looks for the filename in the directory where the parent script file is saved and executes it.

  23. SQL*Plus Fundamentals:Using Script Files (2/3) • Saving Query Results to a File • You can use the SPOOL filename command to save the query results to a file. By default, the SPOOL command creates an .lst file extension. SPOOL OFF stops writing the output to the file. SPOOL OUT stops the writing of output and sends the output file to the printer.

  24. SQL*Plus Fundamentals:Using Script Files (3/3) • Adding Comments to a Script File • Having comments in the script file improves the readability and understanding of the code. You can enter comments in SQL*Plus using the REMARKS (abbreviated REM) command. Lines in the script file beginning with the keyword REM are comments and are not executed. You can also enter a comment between /* and */. • While executing a script file with comments, the remarks entered using the REMARKS command are not displayed on the screen, but the comments within /* and */ are displayed on the screen with prefix DOC> when there is more than one line between /* and */. SET DOCUMENT OFF turns this off.

  25. SQL*Plus Fundamentals:Customizing the SQL*Plus Environment (1/4) • SQL*Plus has a set of environment variables that control the way that SQL*Plus displays data and assigns special characters. The SHOW ALL command lists the current environment. • If you are using SQL*Plus for Windows, you can set the environment by choosing Options from the menu bar and then selecting the Environment option.

  26. SQL*Plus Fundamentals:Customizing the SQL*Plus Environment (2/4) • Using the SET Command • You can customize the environment by using the SET command to change the values of environment variables. The syntax is as follows: • SET variable value • More than one variable can be set using a single SET command (or SHOW command). For example, you might issue the following commands: SET TIME ON SET PAGESIZE 24 SET LINESIZE 80However, it’s easier to specify all three variables in one SET command: SET TIME ON PAGESIZE 24 LINESIZE 80 • You may review all the available SET commands using the HELP SET command in SQL*Plus. The HELP command provides help on all the SQL*Plus commands.

  27. SQL*Plus Fundamentals:Customizing the SQL*Plus Environment (3/4) • Using the SHOW Command • The SHOW command is used to display the value of a SQL*Plus environment variable. All the variables available for use with the SET command can also be used with the SHOW command to see their current value. For example, this query shows the current value of the PAGESIZE and LINESIZE variables: • SQL> SHOW LINESIZE PAGESIZElinesize 80pagesize 14 • SHOW ALL lists the values of all variables. • The SHOW command can also display values of other variables, such as the current username and Oracle release.

  28. SQL*Plus Fundamentals:Customizing the SQL*Plus Environment (4/4) • Saving the Environment • You can save the current SQL*Plus environment using the command STORE SET filename. SQL*Plus creates a .sql file. You may run this file at any time to set up your customized environment. • Wouldn’t it be nice to have the environment set the way you like it when you log in to SQL*Plus? Well, there is a way to do this. Create a login.sql file in the current directory of your SQL*Plus executable or in the search path of Oracle. This file will be executed when you log in to SQL*Plus. For example, to display the name and username when connected and display the current time at the prompt, create a login.sql file using the following lines: • SET PAGES 0 FEEDBACK OFFPROMPT Welcome to SQL*Plus!SELECT'You are connected to ' || GLOBAL_NAME || ' as ' || USERFROM GLOBAL_NAME;SET TIME ON PAGESIZE 24 LINESIZE 80 HEADING ON FEEDBACK ON

  29. Producing More Readable Output • Often, the results returned from SQL*Plus wrap to the next line or do not have the proper formatting. You can use simple SQL*Plus formatting commands to produce more readable output and better-looking reports. • Imagine that you have been asked to produce a report of all employees in department 50 with information on the employee ID, name, job ID, salary, • and hire date from the EMPLOYEES table of the HR schema. You issue the following query and get output similar to that shown in the next slide. • SQL> SELECT employee_id, first_name || ' ' || 2 last_name emp_name, job_id, salary, manager_id 3 FROM employees 4* WHERE department_id = 50; • Obviously, this output is not in a pretty format. You certainly would not want to present this listing as a report. Let’s format this listing to make it more visually appealing.

  30. Producing More Readable Output:Setting Page and Line Sizes • First, check the settings, using the SHOW command to find the values of the PAGESIZE and LINESIZE environment variables: • SQL> SHOW PAGESIZE LINESIZEpagesize 14linesize 80 • Let’s adjust these settings to format a page size of 54 lines and a line length of 55 using the SET command. We’ll also turn off the “45 rows selected” feedback. • SQL> SET PAGESIZE 55 LINESIZE 54 • SQL> SET FEEDBACK OFF

  31. Producing More Readable Output:Formatting Columns (1/4) • You can use the COLUMN command to format column headings and display column data. To display a different heading for the EMP_NAME column, you can use this syntax: • COLUMN oldname HEADING "newname" • You can change the column display width using the FORMAT command. In our sample output, you see a lot of spaces after the employee name. Let’s reduce the column display width for the name column and change the column heading used for the display: • SQL> COLUMN emp_name HEADING "Employee Name" FORMAT A20

  32. Producing More Readable Output:Formatting Columns (2/4) • Now we have many columns, but not enough space to display a whole row in one line. Let’s make the display of the heading in two lines using the default head separator (HEADSEP) character, which is |: • COLUMN employee_id HEADING "Emp|ID" FORMAT 0999 • To format the data display in the SALARY column, use the FORMAT command with the money format: • COLUMN sal FORMAT "$9,999.99"

  33. Producing More Readable Output:Formatting Columns (3/4) • If you format a character column with an insufficient width, the data wraps to the next line, you can change the wrapping behavior using the option WRAPPED (default), WORD_WRAPPED, or TRUNCATED in the COLUMN command. You can also specify column justification using the JUSTIFY value option. Justification values available are RIGHT, LEFT, CENTER. For example, if you want to format the COMMENTS column to have a display width of 30, word-wrapped, and right-justified, with the column heading Comments, use the following command: • SQL> COLUMN comments HEADING "Comments" WORD_WRAPPED –> JUSTIFY RIGHT FORMAT A30

  34. Producing More Readable Output:Formatting Columns (4/4) • To display the current settings for a column, use the COLUMN command and column name without any options: • SQL> COLUMN commentsCOLUMN COMMENTS ONHEADING 'Comments‘FORMAT A30JUSTIFY right word_wrap • To copy the characteristics of a column to another column, use the LIKE option. In the report we are formatting, let’s copy the characteristics of the EMPLOYEE_ID column to the MANAGER_ID column and give it a different heading: • SQL> COLUMN manager_id LIKE employee_id HEADING "Mgr|Id"

  35. Producing More Readable Output:Suppressing Duplicate Values • You can suppress the display of duplicate column values using the BREAK ON column_name command. The BREAK command has options to skip lines, pages, and so on, along with the NODUPLICATE option. Let’s sort our report listing in the order of JOB_ID and group each JOB_ID together to prepare for using the BREAK ON command. Here is our query: • SQL> SELECT job_id, employee_id, first_name || ' ' || 2 last_name emp_name, salary, manager_id 3 FROM employees 4 WHERE department_id = 50 5* ORDER BY job_id, emp_name; • To introduce breaks on the JOB_ID column and suppress the display of duplicate JOB_ID values, use the following command: • SQL> BREAK ON job_id SKIP 2 NODUPLICATES

  36. Producing More Readable Output:Adding Headers and Footers (1/8) • SQL*Plus provides commands for adding a header and footer to the report, as well as headers and footers to each page. The TTITLE and BTITLE commands insert page headers and footers, respectively. The REPHEADER and REPFOOTER commands add a report title and footer, respectively. • You can specify the following formatting specifications for headers and footers: • COL <n> begins the header/footer at column <n>. • SKIP <n> skips <n> lines. • TAB <n> inserts <n> tab characters. • BOLD displays the header/footer in bold. • LEFT aligns the header/footer to the left of the page. • RIGHT aligns the header/footer to the right of the page. • CENTER aligns the header/footer to the center of the page.

  37. Producing More Readable Output:Adding Headers and Footers (2/8) • You can display a page heading using the TTITLE command. The heading will be repeated for each page of the report. The page size is determined by the PAGESIZE variable. If the TTITLE command is followed by just the text in quotation marks, the current date and page number are displayed a line above the title text. Here is an example: • SQL> TTITLE "Current Date and Time“SQL> SELECT SYSTIMESTAMP FROM dual;Tue Nov 27 page 1 Current Date and TimeSYSTIMESTAMP------------------------------------------------------27-NOV-01 07.54.18.000000 PM -08:00

  38. Producing More Readable Output:Adding Headers and Footers (3/8) • If you add more formatting information to the TTITLE command, the page number and date display above the title are turned off. Here is an example: • SQL> TTITLE CENTER "Current Date and Time“SQL> SELECT SYSTIMESTAMP FROM dual; Current Date and TimeSYSTIMESTAMP------------------------------------------------------27-NOV-01 07.57.06.000000 PM -08:00

  39. Producing More Readable Output:Adding Headers and Footers (4/8) • The BTITLE command is used to set up a page footer (bottom title) for every page of the report. BTITLE works in a manner similar to the TTILE command. Here is an example that inserts the page number in the right corner of each page: • SQL> BTITLE RIGHT "PAGE " SQL.PNO.

  40. Producing More Readable Output:Adding Headers and Footers (5/8) • The REPHEADER command adds a title to the report. This title will appear below the TTITLE and only on the first page. Here is an example: • SQL> REPHEADER "This is the report header" • Similarly, REPFOOTER is used to display a report footer at the end of the report, like this: • SQL> REPFOOTER "This is the report footer"

  41. Producing More Readable Output:Adding Headers and Footers (6/8) • Using the title or footer command by itself will display the current setting of these variables. Alternatively, you can use the SHOW command to display the header and footer settings.

  42. Producing More Readable Output:Adding Headers and Footers (7/8) • Here is an example of a three-line page title. The first line is aligned to the center, the second line starts at column 10, and the third line is aligned to the right. The title begins after three blank lines and leaves two blank lines after. Notice that the command is continued to the next lines using the continuation character. • SQL> TTITLE SKIP 3 –> CENTER "First line of title aligned center" –> SKIP 1 COL 10 "Second line begins at col 10" SKIP 1 –> RIGHT "Third line aligned right" SKIP 3SQL> LIST1* SELECT SYSTIMESTAMP FROM dualSQL> / First line of title aligned center Second line begins at col 10 Third line aligned rightSYSTIMESTAMP-------------------------------------------------------27-NOV-01 08.38.42.000001 PM -08:00

  43. Producing More Readable Output:Adding Headers and Footers (8/8) • This example adds a report title with a page number: • SQL> TTITLE CENTER "Employee Information" SKIP 1 –> CENTER ==================== -> SKIP 1 LEFT "Dept 50" –> RIGHT 'PAGE: ' SQL.PNO SKIP 2

  44. Producing More Readable Output:Clearing Formatting (1/3) • To clear the customizations on a column, use the CLEAR option. To turn off the column characteristics, use the OFF option. To turn column characteristics on, use the ON option. Here are some examples: • SQL> COLUMN COMMENTS OFFSQL> COLUMN COMMENTS ONSQL> COLUMN COMMENTS CLEARSQL> COLUMN COMMENTS SP2-0046: COLUMN 'COMMENTS' not defined

  45. Producing More Readable Output:Clearing Formatting (2/3) • The CLEAR command can be used to clear the formatting applied to columns, clear the breaks and computations, clear the screen, or clear the SQL buffer. Here are some examples: • SQL> CLEAR BREAKSbreaks clearedSQL> CLEAR COLUMNScolumns clearedSQL> CLEAR BUFFERbuffer clearedSQL> CLEAR COMPUTEScomputes clearedSQL> CLEAR SCREEN

  46. Producing More Readable Output:Clearing Formatting (3/3) • The following commands will turn off the display of headers and footers. • SQL> TTITLE OFFSQL> BTITLE OFFSQL> REPHEADER OFFSQL> REPFOOTER OFF

  47. Producing More Readable Output:Using a Script File to Create a Report • Now you have a pretty report. You have entered all these formatting commands to produce your report. What about the next time? You can save the formatting and query in a script file and just run the file to produce the report whenever you want to.

  48. Producing More Readable Output:Performing Summary Operations (1/2) • COMPUTE is a SQL*Plus command to perform any summary operation on the grouped columns. Normally, BREAK and COMPUTE appear together in the script files. The summary operations available with COMPUTE are SUM, MINIMUM, MAXIMUM, AVG, STD, VARIANCE, COUNT, and NUMBER. The LABEL clause in the COMPUTE command provides a label for the summary result.

More Related