Nội dung

18 Overview of PL/SQL 18Ć2 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Objectives This lesson provides an overview of how to create and use PL/SQL program units and subprograms using Oracle Procedure Builder. At the end of this lesson, you should be able to D Determine the benefits of accessing the Oracle7 database with PL/SQL. D Describe basic PL/SQL program constructs. D Describe the Oracle Procedure Builder tool. Overview of PL/SQL 18Ć3 18Ć4 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Overview What Is PL/SQL? PL/SQL (Procedural Language/SQL) is an extension to SQL, incorporating many of the design features of programming languages of recent years. It allows the data manipulation and query statements of SQL to be included within block-structured and procedural units of code, making PL/SQL a powerful transaction processing language. Benefits from PL/SQL You can take advantage of the procedural capabilities of PL/SQL, which are unavailable in SQL. Modularize Program Development D Group logically related statements within blocks. D Nest sub-blocks inside larger blocks to build powerful programs. D Break down a complex problem into a set of manageable, well-defined, logical modules, and implement them with blocks. Declare Identifiers D Declare variables, constants, cursors, and exceptions, and then use them in SQL and procedural statements. D Declare variables belonging to simple and composite datatypes. D Declare variables dynamically based upon the data structure of tables and columns in the database. Program with Procedural Language Control Structures D Execute a sequence of statements conditionally. D Execute a sequence of statements iteratively in a loop. D Process individually the rows returned by a multiple-row query with an explicit cursor. D Combine PL/SQL with Oracle tools, such as Developer/2000 Forms, to group associated commands together to control execution. Handle Errors D Process Oracle7 Server errors with exception handling routines. Declare user-defined error conditions and process them with exception handling routines. Continued D Overview of PL/SQL 18Ć5 18Ć6 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Overview continued Benefits of PL/SQLĊcontinued Portability D Since PL/SQL is native to Oracle, you can move programs to any host environment that supports Oracle and PL/SQL. D You can also move code between the Oracle7 Server and your application environment using Procedure Builder’s drag-and-drop capabilities. Integration D PL/SQL plays a central role to both the Oracle7 Server (through stored procedures, database triggers, and packages) and Oracle development tools (through Developer/2000 component triggers). D Variables and datatypes in PL/SQL and SQL are compatible. Therefore, PL/SQL bridges the gap between convenient access to database technology and the need for procedural programming capabilities. Improve Performance D PL/SQL can improve the performance of an application. The benefits differ depending upon the execution environment. D PL/SQL groups SQL statements together within a single block and sends the entire block to the server in a single call, therefore reducing network traffic. Overview of PL/SQL 18Ć7 18Ć8 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder PL/SQL Block Structure A basic PL/SQL block of code can contain up to three parts. The order in which the sections of the blocks are written is shown below. Section Description Inclusion Declarative Contains all variables, constants, cursors, and user-defined exceptions that will be referenced within the Executable section. Optional Executable Contains SQL statements to manipulate data in the database and PL/SQL statements to manipulate data in the block. Mandatory Exception Handling Specifies the actions to perform when errors and abnormal conditions arise within the Executable section. Optional Note: In PL/SQL, an error or warning is called an exception. Section keywords DECLARE, BEGIN, and EXCEPTION are not followed by semicolons. However, END and all other PL/SQL statements do require a semicolon to terminate the statement. You can string statements together on the same line. However, this method is not recommended for clarity or editing. Overview of PL/SQL 18Ć9 18Ć10 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder