Sql.docx

SQL*PLUS IS A DEVELOPMENT ENVIRONMENT Develop and test SQL Programmers use SQL*Plus to query test or production data and to test SQL statements. It provides a development environment for quick interactive access to Oracle data. Manage databases DBAs use SQL*Plus to build and maintain databases. Write scripts Scripts are sets of SQL and SQL*Plus commands. Programmers and DBAs use SQL*Plus to write and execute scripts designed to create reports, process data, and maintain databases. SQL*Plus scripts can be used to generate and run other SQL commands. This is a particularly useful feature. For example, an SQL*Plus script can prompt a user for information and then, using that input, write another SQL*Plus script which deletes all of a user’s test tables and recreates them. Generate basic reports Programmers and DBA’s use SQL*Plus to generate reports. These reports are “quick and dirty” non-GUI reports meant to manage a database. They are not designed as end user reports, although they are used by some end users for ad hoc reporting.

Start SQL*Plus Double click the SQL*Plus icon or type the command SQLPLUS at a command prompt. You will need a userid/password pair to get started. A new Oracle installation will come with this pair: SYSTEM/MANAGER. This login has full DBA rights to the database. You have successfully connected if you get the SQL> prompt. Type any SQL or SQL*Plus command Once you have the SQL> prompt, you may execute any SQL or SQL*Plus command. SQL commands are either ANSI standard SQL or Oracle extensions to SQL. SQL*Plus commands are not SQL commands. They only work within the SQL*Plus product. SQL commands need a termination character to execute. The termination character for SQL commands in SQL*Plus is either a semicolon at the end of the SQL command, or a / at the next SQL> prompt.

SQL*Plus commands do not need a termination character to execute. Therefore, they must be typed on a single line. If they cannot fit on a single line, you may end a line with the continuation character “-”. For example: SQL> describe customers is equivalent to: SQL> describe > customers DESCRIBE a Table This command describes the columns of the table specified. Describe displays the NAME, NULL attribute, and datatype of each column. The DESCRIBE command may be abbreviated as DESC. Note: Many SQL*Plus commands may be similarly abbreviated. See the SQL*Plus User's Guide and Reference documentation for the appropriate abbreviations and options. Each column in a table contains one type of data. A column can contain numbers, characters, dates, or several other data types. If a column of a specified row has no data, it is said to be null. Null represents the absence of data. Null is not the same as a numeric zero (0) or a blank (space) character. If a column’s NULL attribute is NOT NULL, then it cannot have a null value. Nulls take up essentially no storage. For example, the ORDER_DATE column above contains DATE data, and must contain a non-NULL value. The ORDER_PRICE column above contains a NUMBER consisting of a maximum of 7 digits, five before the decimal point and 2 after the decimal point. It may contain a NULL value. Connect to a different user while inside SQL*Plus Rather than disconnect from SQL*Plus and then reconnect as a different user, you can reconnect directly from SQL*Plus with the SQL*Plus CONNECT command. If you omit the password, SQL*Plus will prompt you for it. To determine the current user, type SHOW USER from the SQL> prompt. SQL> show user USER is “PEGGY”

Exit from SQL*Plus This terminates the current SQL*Plus session. All database changes made in this session that are not yet committed (i.e. made permanent) will be committed at this time. We will discuss this issue in more detail later in the course. All changes to the current SQL*Plus session (not to the database) will be lost. For example, if you used the DEFINE_EDITOR command to change the default editor for this session, the change is lost as soon as the session is closed. We will learn how to save these changes in a future lesson.