3-4.模式对象管理(过程、函数、包).doc

SRC: Oracle8i Concepts(17)Procedures and Packages Introduction to Stored Procedures and Packages Procedures and Functions Packages How Oracle Stores Procedures and Packages How Oracle Executes Procedures and PackagesIntroduction to Stored Procedures and Packagesl Oracle allows you to access and manipulate database information using procedural schema objects called PL/SQL program units.l Procedures, functions, and packages are all examples of PL/SQL program units.Stored Procedures and Functionsn Procedures and functions are schema objects that logically group a set of SQL and other PL/SQL programming language statements together to perform a specific task.n You can execute a procedure or function interactively by:1. Using an Oracle tool, such as SQL*Plus2. Calling it explicitly in the code of a database application3. Calling it explicitly in the code of another procedure or triggern Procedures and functions are identical except that functions always return a single value to the caller, while procedures do not.n Alternatively, a privileged user might use Oracle Enterprise Manager or SQL*Plus to execute the HIRE_EMP procedure using the following statement:EXECUTE hire_emp (TSMITH, CLERK, 1037, SYSDATE,500, NULL, 20);Packagesn A package is a group of related procedures and functions, together with the cursors and variables they use, stored together in the database for continued use as a unit.n Oracle Enterprise Manager or SQL*Plus might issue the following statement to execute the HIRE_EMP package procedure:EXECUTE emp_mgmt.hire_emp (TSMITH, CLERK, 1037, SYSDATE, 500, NULL, 20); Procedures and Functionsn A procedure or function is a schema object that consists of a set of SQL statements and other PL/SQL constructs.n For example, the following statement creates the CREDIT_ACCOUNT procedure, which credits money to a bank account:CREATE PROCEDURE credit_account(acct NUMBER, credit NUMBER) AS old_balance NUMBER; new_balance NUMBER; BEGIN SELECT balance INTO old_balance FROM accounts WHERE acct_id = acct FOR UPDATE OF balance; new_balance := old_balance + credit; UPDATE accounts SET balance = new_balance WHERE acct_id = acct; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO accounts (acct_id, balance) VALUES(acct, credit); WHEN OTHERS THEN ROLLBACK;END credit_account;Benefits of Proceduresn Procedures provide advantages in the following areas:1. Security with Definer-Rights Procedures2. Inherited Privileges and Schema Context with Invoker-Rights Procedures3. Performance4. Memory Allocation5. Productivity6. IntegritySecurity with Definer-Rights Proceduresn You can restrict the database operations that users can perform by allowing them to access data only through procedures and functions that execute with the definers privileges.Inherited Privileges and Schema Context with Invoker-Rights Proceduresn An invoker-rights procedure inherits privileges and schema context from the procedure that calls it.Performancen Stored procedures can improve database performance in several ways:1. The amount of information that must be sent over a network is small.2. A procedures compiled form is readily available in the database, so no compilation is required at execution time.3. If the procedure is already present in the shared pool of the SGA, retrieval from disk is not required.Memory Allocationn Because stored procedures take advantage of the shared memory capabilities of Oracle, only a single copy of the procedure needs to be loaded into memory for execution by multiple users.Productivityn Stored procedures increase development productivity.Integrityn Stored procedures improve the integrity and consistency of your applications. By developing all of your applications around a common group of procedures, you can reduce the likelihood of committing coding errors.Procedure Guidelinesn Use the following guidelines when designing stored procedures:1. Define procedures to complete a single, focused task.2. Do not define procedures that duplicate the functionality already provided by other features of Oracle.Anonymous PL/SQL Blocks versus Stored Proceduresn Oracle compiles the PL/SQL block and places the compiled version in the shared pool of the SGA, but does not store the source code or compiled version in the database for reuse beyond the current instance.n Shared SQL allows anonymous PL/SQL blocks in the shared pool to be reused and shared until they are flushed out of the shared pool.External Proceduresn A PL/SQL procedure executing on an Oracle server can call an external procedure or function that is written in the C programming language and stored in a shared library.n The C routine executes in a separate address space from that of the Oracle server.Packagesn Packages encapsulate related procedures, functions, and associated cursors and variables together as a unit in the database.n You create a package in two parts: the specification and the body.1. The developer has more flexibility in the development cycle.2. You can alter procedure bodies contained within the package body separately from their publicly declared specifications in the package specification.CREATE PACKAGE bank_transactions (null) AS minimum_balance CONSTANT NUMBER := 100.00; PROCEDURE apply_transactions; PROCEDURE enter_transaction (acct NUMBER, kind CHAR, amount NUMBER);END bank_transactions; CREATE PACKAGE BODY bank_transactions AS new_status CHAR(20); PROCEDURE do_journal_entry (acct NUMBER,kind CHAR) IS BEGIN INSERT INTO journal VALUES (acct, kind, sysdate); IF kind = D THEN new_status := Debit applied; ELSIF kind = C THEN new_status := Credit applied; ELSE new_status := New account; END IF; END do_journal_entry; END bank_transactions; Benefits of Packagesn Packages provide advantages in the following areas:1. Encapsulation of related procedures and variables2. Declaration of public and private procedures, variables, constants, andcursors3. Better performanceEncapsulationn Stored packages provides better organization during the development process.n Encapsulation of procedural constructs in a package also makes privilege management easier.Public and Private Data and ProcedurespublicDirectly accessible to the user of a package.privateHidden from the user of a package.Performance Improvementn An entire package is loaded into memory when a procedure within the package is called for the first time.n A package body can be replaced and recompiled without affecting the specification.Oracle Supplied Packagesn Oracle supplies many PL/SQL packages that contain procedures for extending the functionality of the database or PL/SQL.1. DBMS_ prefix, such as DBMS_SQL, DBMS_LOCK, and DBMS_JOB2. UTL_ prefix, such as UTL_HTTP and UTL_FILE3. DEBUG_ prefix4. OUTLN_ prefixHow Oracle Stores Procedures and Packagesn When you create a procedure or package, Oracle:1. Compiles the procedure or package2. Stores the compiled code in memory3. Stores the procedure or package in the databaseCompiling Procedures and Packagesn The PL/SQL compiler is part of the PL/SQL engine contained in Oracle.Storing the Compiled Code in Memoryn Oracle caches the compiled procedure or package in the shared pool of the system global area (SGA).Storing Procedures or Packages in Databasen At creation and compi