Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
59
lượt xem
8

Mô tả tài liệu

Oracle SQL Jumpstart with Examples- P2: Review "As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single...

Chủ đề:

Bình luận(0)

Lưu

1. 20 1.6 SQL Tools Before you begin the following steps, you will need these two pieces of information about your database: The database name or network name. If you are running the database on your own computer, this is the name you gave the data- base when it was created. If you are not sure what you named it, go to a command prompt (see step 1 to learn how) and then type this command: lsnrctl status Look for a line that begins like this. The word in quotation marks is your database name. In this example, the database SID name is OLTP. Instance "oltp", status READY, ... If you are running from a client computer and using a remote database on the network, you must use the network name deﬁned in your local Oracle Net conﬁguration. The conﬁguration ﬁle named TNSNAMES.ORA has all of the network names available to you. The ﬁle is located in $ORACLE_HOME/network/admin directory. Here is an example of the text found in the TNSNAMES.ORA ﬁle for the OLTP network name: OLTP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 1300server) (PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oltp))) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 2. 1.6 SQL Tools 21 Figure 1.11 Win2K Registry ORACLE_SID Variable. The password for the users named SYS and SYSTEM. The Oracle Database Conﬁguration Assistant in Oracle Database 10g allows set- ting of SYS and SYSTEM passwords to the same value. If you are running a database on your own computer or on a database server, you can reach the database directly by omitting the database name. When you omit the name, Oracle uses the bequeth protocol and the cur- rent$ORACLE_SID variable setting to access the database. In Windows the $ORACLE_SID variable is set in the registry, and on UNIX or Linux in a user or root proﬁle. Figure 1.11 shows a Win2K registry location. When you use the database name, Oracle uses the transmission control protocol (TCP). Follow these steps to start up SQL*Plus Command Line and run an SQL command: Note: The steps here, and throughout the book, use the sample tables and data created especially for this book. Appendix A contains instructions for locating and installing all of the sample tables. 1. Go to a command-line prompt on your computer. If you are using Windows, click on Start/Programs/Accessories/Command Prompt. A window appears with a blinking cursor. This is your Chapter 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 3. 22 1.6 SQL Tools Figure 1.12 SQL*Plus Command Line in Windows 2000. command prompt. If you are using UNIX, you may be at the command prompt when you log in. It looks like a dollar sign ($). If you are not already at the UNIX command prompt, select Ter- minal Window from your Utilities menu or execute an operating system shell. 2. Type the following command, replacing pwd with the password for the SYSTEM user and replacing name with your appropriate network name, and press Enter. sqlplus system/pwd@name 3. You will see status information about SQL*Plus and the database and a message stating you are connected. Then your display’s prompt changes to “SQL>”, indicating that you are now in SQL*Plus. Figure 1.12 shows an example of the command prompt window after starting up SQL*Plus. 4. Type the following SQL*Plus commands, and press Enter after each line. These set up the column width displayed for the query that follows. (More on SQL*Plus commands in Chapter 8.) COL PRODUCT FORMAT A35 COL VERSION FORMAT A15 COL STATUS FORMAT A15 5. Type the following query and press Enter: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
4. 1.6 SQL Tools 23 Figure 1.13 SQL*Plus SQL Commands Return Instant Results. SELECT * FROM PRODUCT_COMPONENT_VERSION; The asterisk represents all of the columns. Thus all columns are displayed in this query. Figure 1.13 shows the results. The actual data may be different, depending on the shape of your Oracle Database 10g installation. 6. Exit SQL*Plus by typing EXIT and pressing Enter. This returns you to your command prompt. 7. Exit from the command prompt by typing EXIT and pressing Enter. One of the disadvantages of using the command-line mode of SQL*Plus is the inability to use the mouse to correct your typing. You must erase using the backspace key. Table 1.1 shows the editing commands you can use. An advantage of the command-line mode is the ability to add parame- ters to the sqlplus command. For example, you can run a script immedi- ately upon startup, or start SQL*Plus without logging into any database instance (this is useful for issuing SQL commands for starting and stopping the database). SQL*Plus does have a line editor built into it that you can use instead of starting up an editor. Chapter 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
6. 1.6 SQL Tools 25 Figure 1.14 Log into Your Database as a Valid User. an SQL> prompt telling you that SQL*Plus is ready to accept commands. 4. Type the following command and press Enter. This is an SQL*Plus command that tells the database to list the structure of the table or view that you name. A view is a query stored with a name in the database. It acts like a table but does not store any data. (Chapter 19 covers views in detail). DESC DBA_USERS 5. The screen shows the names and datatypes of all the columns in this view. This is very useful when you are about to write an SQL command and you need a quick reminder of the exact column names in a table. Now type this query and press Enter after each line. Notice that the prompt changed from “SQL>” to “2” on the second line. This indicates that SQL*Plus knows you have started a command and you are continuing it on the next line. The semi- colon at the end of the second line signals to SQL*Plus that the command is complete and should be immediately executed. SELECT USERNAME, ACCOUNT_STATUS, CREATED FROM DBA_USERS; 6. The results scroll by, and you can use the scroll bar on the right side of the window to move up or down and view the results. Fig- ure 1.15 shows the results from the query. The column headings Chapter 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
8. 1.6 SQL Tools 27 Figure 1.16 Invoking (Opening) the Editor. until you turn it off, or until you exit SQL*Plus. This is a handy way to record your work. In addition, in Chapter 8, you will ﬁnd out how to write reports using this spooling technique. Navigate to a directory of your choosing, such as C:\TEMP in Windows, and then type “testing” as the ﬁle name and click Save. The ﬁle will automatically receive a sufﬁx of “.LST” on Windows and of “.lis” on UNIX and other operating systems. 11. Type / (a forward slash) to run the query. The forward slash and the semi-colon both tell SQL*Plus to execute a command. The forward slash must be used alone on a line by itself, whereas the semi-colon is used at the end of a line of code. The semi-colon terminates and submits a single-line SQL command to the data- base. The forward slash does the same and additionally compiles and executes blocked sections of PL/SQL code. 12. The results scroll into the window as before. 13. Type the letter L and press Enter. This is the LIST command of SQL*Plus. It displays whatever SQL command is currently in the SQL*Plus buffer. 14. Select File/Spool/Spool off to end the spooling of data to the ﬁle. This closes the ﬁle that has been receiving data from the SQL*Plus session. Your spool ﬁle will be empty until this com- mand is executed or you exit SQL*Plus. Chapter 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.