ORACLE8i- P2

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

0
41
lượt xem
4
download

ORACLE8i- P2

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

ORACLE8i- P2: We want information… information...” Possibly you recognize these words as the primary interest of a somewhat clandestine group, and as told by a character called Number 2 to Patrick McGoohan’s character Number 6 (in the old TV show The Prisoner). Indeed, in this day, information is king, and the speedy, accurate, and reliable retrieval of this information is paramount.

Chủ đề:
Lưu

Nội dung Text: ORACLE8i- P2

  1. 28 CHAPTER 1 • ELEMENTS OF ORACLE DATABASE MANAGEMENT 3 where hiredate < sysdate 4* SQL> 2 2* form emp SQL> c/form/from 2* from emp To add additional text to the SQL statement, use the A command and then specify the additional text. For example: A from dba_data_files, as shown here: SQL> select empno, ename 2 from emp 3 where hiredate < sysdate 4 SQL> 1 1* select empno, ename SQL> a , hiredate 1* select empno, ename, hiredate To insert a line, use the I command. SQL*Plus will prompt you to insert additional test at the location of the current pointer. A SQL*Plus command must be on one line only (this is not true for a SQL state- ment). If you explicitly want to use two or more lines to express the command, you must end each line with a space and then a dash ( - ). Then you can continue the command on the next line (which starts with a > symbol). Here’s an example: Column bytes - > format 999,999,999 SQL*Plus also provides other features for the DBA. You can • Format the output, including page breaks and totals summation on columns. • Control the format of specific columns, including length of the output dis- played in the column, and whether the output wraps or not. • Supply title, header, and footer output for your SQL statements. • Clear the screen. • Calculate and print summary totals of columns. All SQL*Plus commands are documented in Appendix C. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  2. USING ORACLE SQL 29 PA R T NOTE Several database management commands have been added to SQL*Plus since Oracle7, to facilitate the removal of Server Manager at some point in the future. These I commands include starting up, shutting down, and recovering the database. Starting and stopping the database is discussed in Chapter 7. Setting up the database (including starting it without using CONNECT INTERNAL) is covered in Chapter 3. Database security relating Oracle Essentials to CONNECT INTERNAL is discussed in Chapter 21. TNSPING The Oracle utility called TNSPING helps you determine if your Oracle networking is set up properly. Here is the syntax for the command to run TNSPING: TNSPING (network_service_name) [count] The network_service_name is the name of the database you wish to check on. The COUNT parameter is the number of “pings” you wish to send to the database. NOTE The TNSPING command does not help you determine if your database is up and running. It only tells you whether the listener for that database is up and running. If the TNSPING command is successful, Oracle responds with a display showing how long it took the ping to be returned by the Oracle listener. Following is an example: C:\>tnsping ora816 TNS Ping Utility for 32-bit Windows: Version 8.1.6.0.0 - Production on 07-JAN-20 01 13:10:43 (c) Copyright 1997 Oracle Corporation. All rights reserved. Attempting to contact (ADDRESS=(PROTOCOL=TCP) (HOST=ws-jax-w2820)(PORT=1521)) OK (650 msec) Using Oracle SQL SQL is the language of Oracle databases and the DBA’s path into the database. You use SQL to store, remove, and retrieve information at will. This section provides a basic introduction to SQL, the fundamental precepts of SQL that you will see used throughout the rest of the book. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  3. 30 CHAPTER 1 • ELEMENTS OF ORACLE DATABASE MANAGEMENT NOTE Don’t forget to check the reading list at the end of this chapter if you need addi- tional help working with SQL. You will have the opportunity to study the many SQL com- mands used throughout this book, and when you need more information about a command, you can look in Oracle’s SQL Reference Guide. Also, there’s a SQL quick refer- ence guide in Appendix E of this book. Datatypes in Oracle8i When you query a table with a SQL statement, you access one or more columns in that table. The types of data stored in these columns are defined when the table is created. Each column is defined to be a particular datatype. This might be a scalar (native) datatype such as VARCHAR2, which stores characters. Or it might be a user- defined datatype. We will explore the Oracle datatypes throughout this book; Table 1.3 is a summarized list. TABLE 1.3: NATIVE ORACLE DATATYPES Datatype Description CHAR Stores up to 2000 characters of data in a fixed-length format. VARCHAR2 Stores up to 4000 variable characters of data. (Though it’s not often used, Oracle still offers the VARCHAR datatype, which is the same as a VARCHAR2.) NCHAR and NVARCHAR2 These datatypes store National Language Support (NLS) character data. They store data similarly to the CHAR and VARCHAR2 datatypes. LOB and NCLOB Can store up to 4GB of character data. These are part of the fam- ily of LOB data types. BLOB Stores up to 4GB of binary, unformatted data. BFILE Pointer to an outside operating system file. LONG and LONGRAW Stores up to 2GB of raw, unformatted data. Support for these data types will be dropped at some point in favor of LOBs. DATE Stores dates in Oracle. Stores both the date and time, up to hun- dredths of seconds. NUMBER Number datatype. ROWID and UROWID Stores Oracle ROWIDs. UROWID supports a wider variety of ROWIDs. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  4. USING ORACLE SQL 31 The DML and DDL Languages PA R T In this section we’ll look at Oracle’s Data Manipulation Language (DML) and Data I Definition Language (DDL), starting with the common syntax conventions. First, all commands begin with a keyword. These are words that imply the action to be performed—for example, INSERT, UPDATE, and DELETE. Oracle Essentials In SQL, certain characters have a special meaning, as follows: ; End of SQL statement / End of SQL statement -- Comment /* */ Comment So, for example, when you complete a DML or DDL statement, you end it with a semicolon to indicate that the statement is completed. DML DML statements are designed to display or modify database data. Following is a list of DML statements that you will be using in Oracle: Statement Purpose Example SELECT The most common SQL state- SELECT * FROM emp WHERE ment; used to query the data empno < 2000; in the database INSERT Adds new rows into a database INSERT INTO emp (empno, table name) VALUES (1,'Robert'); UPDATE Updates rows in the database UPDATE emp table SET ename='Davis' WHERE empno=12345; DELETE Removes one or more rows DELETE FROM emp from a database table WHERE empno=12345; NOTE SELECT queries are sometimes considered a distinct type of statement, but in this book we will consider them DML. DDL Oracle’s Data Definition Language (DDL) comprises every remaining SQL command that is not DML. In general, these are the statements that manipulate the database itself. DDL statements include CREATE TABLE, CREATE INDEX, and CREATE DATABASE. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  5. 32 CHAPTER 1 • ELEMENTS OF ORACLE DATABASE MANAGEMENT Set Processing Oracle SQL statements work with one group of records at a time. Unlike most stan- dard programming languages that require you to loop through a set of records and process them one record at a time, Oracle facilitates what is called set processing. Many new developers who come from a background of COBOL or C have difficulty grasping this paradigm change, and at first tend to write inefficient code. For example, con- sider the following pseudocode: For loop until EOF Do Get record If column_to_change=’2’ then Change record so column_to_change=’1’ End if Write changed record End of loop This code works fine, of course, but in Oracle a simple SQL statement will generally work much faster: UPDATE my_table SET column_to_change=1 WHERE column_to_change=2; Not only will it work much faster, but it’s more compact as well. Set processing allows Oracle to perform one or more operations collectively. The result from one operation can interact with previous operations, and all of this is done via one SQL statement. Let’s look at a specific example. You need to collect a set of employee records for all employees of Department 7, sort them by hire date, and then remove from the result set all employees who happen to be retired. This type of logic would take several lines of other code but is done quickly in SQL by issuing a statement such as this: SELECT employee_name, address, hire_date FROM employee_data WHERE status != ‘Retired’ AND dept_no=7 SORT BY hire_date; WARN I NG SQL statements and set processing are more direct but can get compli- cated fast. The Oracle database engine is designed around set operations. As DBA, when reviewing programmers’ code, be on the lookout for code that interacts with the database in a way that does not fully take advantage of set processing. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  6. USING ORACLE SQL 33 The SQL Query PA R T A SQL query has several parts to it, some required and some optional. Its basic com- I ponents are described in the following paragraphs. The SQL Quick Reference in Appendix F can be of assistance to you in crafting the perfect SQL statement. Oracle Essentials The SELECT Clause This clause starts with the SELECT keyword, followed by a comma-separated list of columns to display as a part of the query results. If you wish to bring back all columns of the table, use an asterisk (*). You can also include functions (your own or one of the many supplied by Oracle) to manipulate the columns appearing in the SELECT clause. For example, to display only a part of a character column, you might use the SUBSTR function in the SELECT query to limit the number of characters returned. Throughout this book we will look at and use many of the native Oracle functions. In addition, a quick reference to functions can be found in Appendix F. The FROM Clause The FROM clause contains one or more of the following: • A list of tables to be accessed as a part of the query • An inline view that is to be accessed as part of the query The WHERE Clause This clause consists of several predicates, separated by AND keywords, that control the results of the SQL statement. The WHERE clause serves several purposes, including • Making the query selective. Instead of bringing back all rows from the tables queried, the criteria in the WHERE clause’s predicates serve to restrict the num- bers of rows. • Joining rows together. If several tables appear in the FROM clause, it is the WHERE clause that defines the related columns of the tables. In the following example of a basic SQL statement: SELECT empno, ename FROM emp WHERE empno=1; the following is true: • The SELECT clause will cause Oracle to return two columns, EMPNO and ENAME. • The FROM clause identifies what table (EMP) we wish to bring back from the EMPNO and ENAME columns. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  7. 34 CHAPTER 1 • ELEMENTS OF ORACLE DATABASE MANAGEMENT • The WHERE clause, with one predicate (empno=1), restricts the rows returned to just the row(s) where empno=1. These are just the basic parts of a SQL statement, of course. There are other clauses, such as GROUP BY and HAVING, that format grouped results—for instance, a summa- tion of values in the columns of a table. The ORDER BY clause sorts the result set. All of these commands and others are documented in the SQL Quick Reference in Appen- dix E, and you will find plenty of examples in this book. SQL Operations Several different kinds of operations are possible when issuing SQL statements. Subqueries A subquery is a SQL statement within a SQL statement. This allows nesting of queries in your SQL statement. The following example of a subquery returns a count of all employees who are paid less than the average salary: SELECT count(*) FROM employee WHERE salary
  8. USING ORACLE SQL 35 Equijoin and Inner Join PA R T An equijoin is a join involving a direct equivalence between the key columns of the I tables involved in the join. In this example: SELECT a.ename, a.deptno, b.dname FROM emp a, dept b Oracle Essentials WHERE a.deptno=b.deptno the equijoin is between the EMP table and the DEPT table. The join is on the like or equivalent (hence the word equijoin) DEPTNO keys in both tables. From this join, we get the department number from the EMP table, and the name of the department is derived from the DEPT table. An inner join is probably the most common join operation. It is the join of the rows from one table to those of others based on common key values. If a row in one table has a key that does not have a match in another table, then that row will not be dis- played. For example, if an employee is assigned to a department that does not exist in the department table, then that employee will not appear in the results displayed by the following (a good example of referential integrity’s importance in a database): SELECT a.ename, a.deptno, b.dname FROM employee a, dept b WHERE a.deptno=b.deptno Theta Joins A theta join (also called a non-equijoin) is a join using an operator that involves equiv- alence along a range of values. SQL statement operators include < and >, BETWEEN, and others. Here is an example of a theta join: SELECT a.ename, b. grade FROM employee a, salgrade b WHERE a.sal BETWEEN b.losal AND b.hisal Outer Joins An outer join causes rows in one table to be included in the resulting join, even if there is no matching row in the other joined table. Oracle uses a non-ANSI standard (+) operator to designate an outer join. The (+) operator goes in the WHERE clause, by the column/table combination that might not have the associated rows in it. Let’s say you are looking for employees and what they were paid last week, but you also want to see employees who were not paid anything last week. The PAY_TABLE table (which contains pay records) might not have rows for all the employees, since they might not all have been paid. To list all employees, then, including those not paid, we put the (+) in the WHERE clause next to the EMPNO column for the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  9. 36 CHAPTER 1 • ELEMENTS OF ORACLE DATABASE MANAGEMENT PAY_TABLE table. So the query would look something like this (the outer join is in the last line): SELECT a.empno, a.ename, b.pay_day, b.pay_total FROM employee a, pay_table b WHERE a.empno = b.empno (+) Anti-Joins An anti-join makes possible the selection of all rows in one table that do not have rows that match in another table. Specifically, you might want to find all the rows in one table that do not have the selected column values of those rows in another table. Anti-joins are typically performed using the NOT IN and NOT EXIST clauses. Let’s say you want a list of employees who have not been assigned a department. You can do that using an anti-join with a query such as this one: SELECT a.empno, a.ename, a.deptno FROM emp a WHERE a.deptno NOT IN (SELECT deptno FROM dept); Self Joins When you join a table to itself, it’s called a self join. In a self join, the same table will appear twice (or more) in the FROM clause. Here’s an example: SELECT a.empno, a.ename, a.mgr, b.ename “Manager Name” FROM employee a, employee b WHERE b.empno=a.mgr; Cartesian Joins Cartesian joins are the result of a join between two tables when no join condition is defined the query’s WHERE clause. Oracle will occasionally decide to do a Cartesian join when executing a SQL statement if it thinks that the join will provide the required row sources faster than other access methods. The Effects of NULL Columns on Joins The presence of NULLs in relational databases means we have a third logical value to deal with. Instead of just TRUE and FALSE, for instance, you now also have to con- sider NULL, which simply means that the data is missing or undefined. This causes some additional complications. The impact of NULL values on queries is fairly easy to demonstrate. Here is an example: SQL> SELECT COUNT(*) FROM emp WHERE job=’CLERK’ 2 union Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  10. USING ORACLE SQL 37 3 SELECT COUNT(*) FROM emp WHERE job!=’CLERK’; PA R T I COUNT(*) ---------- 4 11 Oracle Essentials SQL> SELECT COUNT(*) FROM emp; COUNT(*) ---------- 16 This example perfectly demonstrates three-valued logic. In the first query, we have 15 total rows reported; yet when we issue a statement to count all rows, we get a total of 16 rows. What’s up? The issue is that in one row the job column is set to NULL. The NULL is essentially a third value and will not evaluate to TRUE or FALSE. Thus, it fails both the test of being equal to ‘CLERK’ and the test of being unequal to ‘CLERK’. The NULL is an unknown, and a special test must be done to check for that logical condition. In other words, neither the = nor the != test will find NULL values! The presence of NULL values in data can have some interesting impact on join results, and you’ll need to consider this when developing your queries. For example, take another look at our anti-join query: SELECT a.empno, a.ename, a.deptno FROM emp a WHERE a.deptno NOT IN (SELECT deptno FROM dept); This gives the following output: EMPNO ENAME DEPTNO ----- ------------- ------------- 1 Freeman 99 What we want here is a listing of all employees who are assigned a department number that is not in the DEPT department. Only problem is, if an employee has NULL assigned to the DEPTNO column of the EMP table, that employee record will not appear—because the NULL is not considered a value at all. Thus, when a NULL column appears, it doesn’t satisfy the NOT IN check and does not appear as part of the result set. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  11. 38 CHAPTER 1 • ELEMENTS OF ORACLE DATABASE MANAGEMENT In this case, we might wish to rewrite the SQL slightly to take NULLs into consider- ation. To do this, we would use the NVL statement: SELECT a.empno, a.ename, a.deptno FROM emp a WHERE nvl(a.deptno,-1) NOT IN (SELECT deptno FROM dept); This time the results are as follows: EMPNO ENAME DEPTNO ----- ------------- ------------- 1 Freeman 99 2 Freeman We can also use the IS NULL and IS NOT NULL to check for this condition and thus use NULLs to our advantage. For instance: SELECT COUNT(*) from emp WHERE job=’CLERK’ UNION SELECT COUNT(*) from emp WHERE job!=’CLERK’ OR job IS NULL; In this case, we have added the OR job IS NULL clause to check for the additional log- ical possibility of a NULL, so we get the correct result when we run this query. The NOT NULL makes sure that columns that are NULL are not included in a given result set, such as in this example: SELECT COUNT(*) FROM emp WHERE job IS NOT NULL; Here we have indicated that we want to count all rows unless the job column is set to NULL, in which case we will not count it. Aggregation Oracle provides aggregation operations (returning a single result based on an operation performed on a group of rows) on columnar values. This is facilitated by various aggregation functions used in the SELECT clause. Aggregation options include func- tions such as SUM(), AVG(), and COUNT(). Aggregation often requires use of the GROUP BY clause to define the columns that are being aggregated. Also, you can include the HAVING clause to restrict the aggregate results being reported. See Appen- dix F for a list of Oracle’s aggregation functions. In the following example of aggregation, we determine the total size of the SYS- TEM tablespace in bytes: SELECT SUM(bytes) FROM dba_data_files Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  12. ORACLE PL/SQL 39 WHERE tablespace_name = ‘SYSTEM’ PA R T GROUP BY tablespace_name; I Oracle PL/SQL Oracle Essentials SQL has little procedural structure. You execute a statement and bang, you get the results. Some functions do some logical processing (such as the DECODE and CASE expressions), but they still run in just one statement. SQL has no procedural process- ing to handle, for example, the result set of a given SQL statement. Oracle solves this problem with the Procedural Structured Query Language (PL/SQL) engine in the Oracle database. PL/SQL supplies procedural constructs for use by the DBA and developer in creating complex database interface logic. NOTE This is a brief introduction to PL/SQL. In Appendix F you’ll find in-depth coverage. Basic PL/SQL Structure A stored procedure is PL/SQL that is stored in the database. This may be a function, pro- cedure, package, or trigger. A PL/SQL anonymous block, on the other hand, is not stored in the database and therefore must always be loaded from the operating system (unless it has just run and you want to run it again). PL/SQL programs generally have these parts: • The DECLARE section, which stores the variables to be used in the PL/SQL code and is only present for anonymous blocks • The BEGIN keyword • The PL/SQL code in the body of the anonymous block • The END keyword PL/SQL code also contains several procedural constructs, including these: • Looping constructs • IF/THEN/ELSE structures • Handling of statement results a row at a time, rather than relying on set processing Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  13. 40 CHAPTER 1 • ELEMENTS OF ORACLE DATABASE MANAGEMENT • Error checking • Variable assignment • Calling of other functions and procedures PL/SQL Program Units This section contains simple examples of PL/SQL anonymous blocks, procedures, func- tions, packages, and triggers, leaving more comprehensive discussion to Chapter 20. Anonymous Blocks An anonymous block is a block of PL/SQL that is loaded into the Oracle instance and executed. Unlike other types of PL/SQL (procedures, functions, and so forth) anony- mous blocks are not stored in the database for easy recall. They do not return any kind of value. To execute an anonymous block, you must load it from an operating system file; if it’s the last operation to execute, however, it gets stored in the SQL buffer and is available for recall and editing. Listing 1.1 is an example of an anony- mous block. Listing 1.1: An Anonymous PL/SQL Block -- Two dashes indicate a remark in PL/SQL -- The SET SERVEROUTPUT ON command is a SQL*Plus command; -- not a part of PL/SQL anonymous block SET SERVEROUTPUT ON -- This is the start of the DECLARE section (or Declaration -- section, if you prefer). Note this section is ONLY required -- for anonymous blocks! DECLARE -- This next line defines V_Num_rows as a variable of type number V_Num_rows number; -- The BEGIN section is next. The body of the PL/SQL code begins -- after the BEGIN keyword. BEGIN SELECT count(*) INTO v_num_rows FROM dba_tables; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  14. ORACLE PL/SQL 41 -- The Dbms_output.put_line package will output a message to the PA R T -- screen. I Dbms_output.put_line(‘The number of rows is ’||v_num_rows); END; -- The END declaration ends the body of the PL/SQL program. Oracle Essentials This example introduces several concepts. First of all, you know this is an anony- mous block because it includes a DECLARE section, which doesn’t appear in any other type of PL/SQL (or SQL for that matter). Note the commands that come before the DECLARE section. They are both used to set up the SQL*Plus environment so that the DBMS_OUPUT.PUT_LINE package will work later in the anonymous block. You will find the SET SERVEROUTPUT command in the SQL*Plus command reference in Appendix C. The DBMS_OUTPUT package is an Oracle-supplied package that displays output to your screen from a PL/SQL block. (Oracle packages are discussed in Chapter 20.) Note that we declared a variable called V_NUM_ROWS as a number type. Then comes the BEGIN keyword, followed by the PL/SQL block. In the PL/SQL block we do a simple SELECT INTO operation which causes the results of the SELECT to be loaded into the V_NUM_ROWS variable created in the DECLARE section. Finally, we output the line with the DBMS_OUTPUT package and end the PL/SQL block with the END keyword. Procedures Procedures are stored in the database but otherwise are much like anonymous blocks. Procedures do not contain a DECLARE statement, but rather are started with the key- words CREATE PROCEDURE or CREATE OR REPLACE PROCEDURE. Procedures can take parameters, as well. They do not return any values. Listing 1.2 is the anonymous block from Listing 1.1, after it was turned into a procedure. Listing 1.2: A PL/SQL Stored Procedure -- This is a SQL*Plus command CONNECT sys/change_on_install CREATE OR REPLACE PROCEDURE my_procedure AS -- This next line defines V_Num_rows as a variable of type number V_Num_rows number; -- The BEGIN section starts next. The body of the PL/SQL code begins -- after the BEGIN keyword. BEGIN SELECT count(*) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  15. 42 CHAPTER 1 • ELEMENTS OF ORACLE DATABASE MANAGEMENT INTO v_num_rows FROM dba_tables; -- The dbms_output.put_line package will output a message to the -- screen. Dbms_output.put_line(‘The number of rows is ’||v_num_rows); END; -- The END declaration ends the body of the PL/SQL program. You would run this procedure using the EXEC command as shown here: SET SERVEROUTPUT ON EXEC my_procedure The number of rows is 199 Java Stored Procedures Oracle8i introduced the capability of storing Java code in a logical unit called a Java stored procedure. Running a Java stored procedure requires that Java be installed in the database. You can use the CREATE FUNCTION or CREATE PROCEDURE command with the AS LANGUAGE JAVA keywords to create a Java stored procedure. See Chapter 27 for details. Functions A function differs from a procedure in that the function returns a value. A function is run slightly differently, as part of a SELECT statement. To create a function, you use the CREATE FUNCTION or CREATE OR REPLACE FUNCTION command. Listing 1.3 contains our anonymous block, now written as a function. Listing 1.3: A PL/SQL Stored Function CREATE OR REPLACE FUNCTION my_function Return NUMBER IS -- This next line defines V_Num_rows as a variable of type number V_Num_rows number; -- The BEGIN section starts next. The body of the PL/SQL code begins -- after the BEGIN keyword. BEGIN SELECT count(*) INTO v_num_rows FROM dba_tables; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  16. MOVING ON 43 -- The dbms_output.put_line package will output a message to the PA R T -- screen. I Dbms_output.put_line(‘The number of rows is ’||v_num_rows); END; -- The END declaration ends the body of the PL/SQL program. You would run this procedure using the EXEC command as shown here: Oracle Essentials SET SERVEROUTPUT ON SELECT my_function from dual; MY_FUNCTION ----------- 199 Notice that we execute this function as part of a SELECT statement. Packages Packages are a combination of several procedures and functions that are contained in a package body. These procedures and functions are all predefined along with any global variables in a separate package header. You’ll find plenty of examples of pack- ages in Chapter 20. Triggers Triggers are PL/SQL programs that are executed after certain actions take place on a table. For example, when a new employee record is inserted in an EMPLOYEE table, the MEDICAL table must be updated accordingly, because the business makes medical insurance coverage effective on the day of hire. To support this MEDICAL table update, an INSERT trigger is written on the EMPLOYEE table to create the row in the insurance table when the employee record is added. Moving On Well, that’s a brief overview of this database we call Oracle. In the chapters to come we will review in much more detail nearly all of the topics outlined in this first chap- ter. We trust this book will provide you with lots of help in managing your database. Now, let’s move on to the topic of installing the Oracle software. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  17. CHAPTER 2 Installing, Upgrading, and Migrating Oracle F E AT U R I N G : Introducing the Universal Installer 47 Preinstall steps 48 Installing the database software 50 Patching the Oracle software 64 Removing the Oracle software 66 Migrating/updating to Oracle8i 70 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  18. I f you are installing Oracle for the first time, congratulations! You are imple- menting one of the finest relational database systems in the world. Whether you’re new to Oracle, a veteran DBA with numerous installs under your belt, or preparing to migrate or upgrade an existing Oracle database to Oracle8i, don’t worry. This chapter is your complete guide to Oracle software installs and upgrades. We will show you what to do and—just as important—what not to do. There are three primary steps in the process of installing the Oracle software. The first step is preparing for the installation of the Oracle software. The second phase is the actual installation. Following that, the postinstallation stage includes such things as the actual creation of the database, setup of Oracle networking, and other tasks. This chapter goes hand in hand with Chapter 3, which discusses creation of an Oracle database. NOTE This chapter covers installation of the basic Oracle8i RDBMS software package from the Oracle CD-ROM. For installation information on a specific Oracle feature (such as Java in the database), refer to the chapter dedicated to that feature. Oracle Platforms Covered in This Chapter Oracle provides its database software for a vast number of different platforms. It runs on everything from HP-MP to Windows NT, on various flavors of Unix, and even on mainframes such as VAX and VMS (cough, cough, hack). Some ports of Oracle run on platforms so unique that only 200 or so licenses have ever been sold. We have made this chapter as platform independent as possible, but that becomes dif- ficult when you are discussing installation issues in particular. Therefore, when platform issues are of concern, we’ll limit our discussions to the NT and Unix platforms. Generally, you’ll get the details you need in your Oracle platform-specific documenta- tion, so you can refer to that if something doesn’t work or doesn’t translate into the ver- nacular of your particular operating system. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  19. INTRODUCING THE UNIVERSAL INSTALLER 47 Introducing the Universal Installer PA R T I The Oracle Universal Installer (UI) was introduced in Oracle8i to simplify the installa- tion process. Unfortunately, with simplification sometimes come complications, and the UI is no exception to this rule. In this section we will briefly look at the UI. Oracle Essentials The Oracle Universal Installer Earlier versions of Oracle used one of two kinds of installation programs: a character- based installer or a platform-specific installation GUI. For Oracle8i, the installer has been “centralized” by switching to a Java-based program called the Universal Installer (UI). On a few platforms that don’t support Java, Oracle still provides a character- based installer. Switching to a Java-based installer doesn’t come without a price to the user of the system. The first requirement is, of course, a system that includes the Java Runtime Environment (JRE). For a Unix system, running the UI may require incorporating an X Windows client that supports Java. Windows (NT/2000) gets the JRE on the Oracle install CD. In addition, the absence of a simple character-based installer sometimes creates additional configuration problems. Depending on the type of system that’s getting the Oracle software, you may need the assistance of your system or network adminis- trator to get the installation utility running. In addition, there are much greater memory, CPU, and disk requirements for the UI than for the previous installers. This larger resource requirement becomes even more significant when you are also running the memory-hungry Database Configura- tion Assistant (DCA, discussed shortly). WARN I NG Do not try to install a newer version of Oracle using an older version of the Universal Installer. Also, make sure you use an unmodified (except for Oracle-approved patches) Oracle-supplied JRE when running the Installer. The Universal Installer, initially slightly buggy, is much improved in Oracle 8.1.6 and is excellent in 8.1.7. (One drawback, on some platforms such as Solaris, is that two CDs are required to install Oracle 8.1.7.) In spite of its shortcomings, the Univer- sal Installer does provide a number of benefits. These include • An easy-to-use common install/upgrade interface to Oracle products • Support for Oracle patch-set installs Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  20. 48 CHAPTER 2 • INSTALLING, UPGRADING, AND MIGRATING ORACLE • Support for National Language Support (NLS), which provides for multiple lan- guages • Support for dependency resolution between products (Thus, if successful instal- lation of one product depends on the existence of another, the UI will notify you of this dependency.) • Support for multiple ORACLE_HOME locations • A silent-mode installation process that does not need user interaction The UI is started differently on various platforms. See “Starting the UI for Installa- tion” later in this chapter. Preinstall Steps This section describes the steps necessary to prepare your system and environment for an Oracle installation. The general preparation items we cover first apply to installa- tions on all platforms. Other than these general steps, NT installations require little else—but in a Unix system you’ll need to meet several preinstall requirements. Of course, if you are using Oracle on some other platform, everything you need to do may not be included here. Each platform has its own installation and administration guides to which you should refer. General Preinstallation Steps for All Platforms Complete the following checks for any operating system before you install Oracle. NOTE Many of the following items will be OS specific. Oracle includes both generic and OS-specific documentation with all installs. Depending on your operating system, you may find it on the documentation CD, in $ORACLE_HOME/doc, $ORACLE_HOME/rdbms/doc, $ORACLE_HOME\relnotes, or even in accompanying hard documentation. • Read all the Oracle documentation that is specific to your system. • Make sure the system hardware will support the Oracle database server and the expected user process loads. • Make sure the operating system level, with all required patch sets, is supported by the Oracle version you are going to install. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
Đồng bộ tài khoản