《3-4模式对象管理解析.docx》由会员分享，可在线阅读，更多相关《3-4模式对象管理解析.docx（8页珍藏版）》请在三一办公上搜索。
1、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 andPackages Oracle allows you to access and ma
2、nipulate database information using procedural schema objects called pl/sql program units . Procedures, functions, and packages are all examples of PL/SQL program units.Stored Procedures and Functions Procedures and functions are schema objects that logically group a set of SQL and other PL/SQL prog
3、ramming language statements together to perform a specific task. 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 trigger Proced
4、ures and functions are identical except that functions always return a single value to the caller, while procedures do not. Alternatively, a privileged user might use Oracle Eor SQL*Plus to execute the HIRE_EMP procedure using the following statement:EXECUTE hire_emp (TSMITH, CLERK, 1037, SYSDATE,50
5、0, NULL, 20);Packages 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. Oracle Enterprise Manager or SQL*Plus might issue statement to execute the HIRE_EMP package proceduthe i foll
6、ow e:ngEXECUTE emp_mgmt.hire_emp (TSMITH, CLERK, 1037, SYSDATE, 500, NULL, 20);Procedures and Functions A procedure or function is a schema object that consists ofa set of SQL statements and other PL/SQL constructs. For example, the following statement creates the CREDIT_ACCOUNT procedure, which cre
7、dits money to a bank account:CREATE PROCEDURE credit_account (acct NUMBER, credit NUMBER) AS old_balance NUMBER; new_balance NUMBER;BEGINSELECT balance INTO old_balance FROM accountsWHERE acct_id = acctFOR UPDATE OF balance;new_balance := old_balance + credit;UPDATE accounts SET balance = new_balanc
8、e WHERE acct_id = acct;COMMIT;EXCEPTIONWHEN NO_DATA_FOUND THENINSERT INTO accounts (acct_id, balance) VALUES(acct, credit);WHEN OTHERS THEN ROLLBACK;END credit_account;Benefits of Procedures Procedures provide advantages in the following areas:1. Security with Defineir-Rights Procedures2. Inherited
9、Privileges and Schema Context with Invoker-Rights Procedures3. Performance4. Memory Allocation5. Productivity6. IntegritySecurity with Definer-Rights Procedures You can restrict the database operations that users can perform by allowing them to access data only through procedures and functions that
10、execute with the definers privileges.Inherited Privileges and Schema Context with Invoker-Rights Procedures An invoker-rights procedure inherits privileges and schema context from the procedure that calls it.Performance Stored procedures can improve database performance in several ways:1. The amount
11、 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 Allocation Be
12、cause 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.Productivity Stored procedures increase development productivity.Integrity Stored procedures improve the integrity an
13、d 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 Guidelines Use the following guidelines when designing stored procedures:1. Define procedures to complete a single, focu
14、sed task.2. Do not define procedures that duplicate the functionality already provided by other features of Oracle.Anonymous PL/SQL Blocks versus Stored Procedures 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 co
15、mpiled version in the database for reuse beyond the current instance. 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 Procedures A PL/SQL procedure executing on an Oracle server can call an external procedure
16、 or function that is written in the C programming languageand stored in a shared library. The C routine executes in a separate address space from that of the Oracle server.Packages Packages encapsulate related procedures, functions, and associated cursors and variables together as a unit in the data
17、base. 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 PACKAG
18、E 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 C
19、HAR) IS BEGININSERT 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 Packages Packages provide advantages in the foll
20、owing areas:1. Encapsulation of related procedures and variables2. Declaration of public and private procedures, variables, constants, and cursors3. Better performanceEncapsulation Stored packages provides betterorganization during the development process. Encapsulation of procedural constructs in a
21、 package also makes privilege management easier.Public and Private Data and Procedurespublic Directly accessible to the user of a package.private Hidden from the user of a package.Performance Improvement An entire package is loaded into memory when a procedure within the package is called for the fi
22、rst time. A package body can be replaced and recompiled without affecting the specification.Oracle Supplied Packages 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
23、_ prefix, such as UTL_HTTP and UTL_FILE3. DEBUG_ prefix4. OUTLN_ prefixHow Oracle Stores Procedures and Packages 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 Procedu
24、res and Packages The PL/SQL compiler is part of the PL/SQL engine contained in Oracle.Storing the Compiled Code in Memory Oracle caches the compiled procedure or package in the shared pool of the system global area (SGA).Storing Procedures or Packages in Database At creation and compile time, Oracle
25、 automatically stores the following information about stored procedures or packages in the database:*ma object This name identifies the procedure or package.The PL/SQL compiler parses the source code and produces source code anda parsed representation of the source code, called a parse parse treetre
26、e.The PL/SQL compiler generates the pseudocode or P code, pseudocode (P code)based on the parsed code. The PL/SQL engine executes thiswhen the procedure or package is invoked.Oracle might generate errors during the compilation of error messagesa procedure or package.How Oracle Executes Procedures and Packages When you invoke a standalone or packaged procedure, Oracle performs the following tasks:1. Verifying User Access2. Verifying Procedure Validity3. Executing a Procedure
