?? ch18.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Ch 18 -- PL/SQL: An Introduction</TITLE></HEAD><BODY TEXT="#000000" BGCOLOR="#FFFFFF"><CENTER><H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR><FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1></CENTER><CENTER><P><A HREF="../ch17/ch17.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch19/ch19.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <HR></CENTER><CENTER><H1><FONT COLOR="#000077">- Day 18 -<BR>PL/SQL: An Introduction</FONT></H1></CENTER><H2><FONT COLOR="#000077">Objectives</FONT></H2><P>PL/SQL is the Oracle technology that enables SQL to act like a procedural language.By the end of today, you should<UL> <LI>Have a basic understanding of PL/SQL <P> <LI>Understand the features that distinguish PL/SQL from standard SQL <P> <LI>Have an understanding of the basic elements of a PL/SQL program <P> <LI>Be able to write a simple PL/SQL program <P> <LI>Understand how errors are handled in PL/SQL programs <P> <LI>Be aware of how PL/SQL is used in the real world</UL><H2><FONT COLOR="#000077">Introduction</FONT></H2><P>One way to introduce PL/SQL is to begin by describing standard Structured QueryLanguage, or SQL. SQL is the language that enables relational database users to communicatewith the database in a straightforward manner. You can use SQL commands to querythe database and modify tables within the database. When you write an SQL statement,you are telling the database what you want to do, not how to do it. The query optimizerdecides the most efficient way to execute your statement. If you send a series ofSQL statements to the server in standard SQL, the server executes them one at a timein chronological order.</P><P>PL/SQL is Oracle's procedural language; it comprises the standard language ofSQL and a wide array of commands that enable you to control the execution of SQLstatements according to different conditions. PL/SQL can also handle runtime errors.Options such as loops and <TT>IF...THEN</TT> statements give PL/SQL the power ofthird-generation programming languages. PL/SQL allows you to write interactive, user-friendlyprograms that can pass values into variables. You can also use several predefinedpackages, one of which can display messages to the user.</P><P>Day 18 covers these key features of PL/SQL:<UL> <LI>Programmers can declare variables to be used during statement processing. <P> <LI>Programmers can use error-handling routines to prevent programs from aborting unexpectedly. <P> <LI>Programmers can write interactive programs that accept input from the user. <P> <LI>Programmers can divide functions into logical blocks of code. Modular programming techniques support flexibility during the application development. <P> <LI>SQL statements can be processed simultaneously for better overall performance.</UL><H2><FONT COLOR="#000077">Data Types in PL/SQL</FONT></H2><P>Most data types are obviously similar, but each implementation has unique storageand internal-processing requirements. When writing PL/SQL blocks, you will be declaringvariables, which must be valid data types. The following subsections briefly describethe data types available in PL/SQL.</P><P>In PL/SQL Oracle provides subtypes of data types. For example, the data type <TT>NUMBER</TT>has a subtype called <TT>INTEGER</TT>. You can use subtypes in your PL/SQL programto make the data types compatible with data types in other programs, such as a COBOLprogram, particularly if you are embedding PL/SQL code in another program. Subtypesare simply alternative names for Oracle data types and therefore must follow therules of their associated data type.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>As in most implementations of SQL, case sensitivity is not a factor in the syntax of a statement. PL/SQL allows either uppercase or lowercase with its commands. <HR></BLOCKQUOTE><H3><FONT COLOR="#000077">Character String Data Types</FONT></H3><P>Character string data types in PL/SQL, as you might expect, are data types generallydefined as having alpha-numeric values. Examples of character strings are names,codes, descriptions, and serial numbers that include characters.</P><P><TT>CHAR</TT> stores fixed-length character strings. The maximum length of <TT>CHAR</TT>is 32,767 bytes, although it is hard to imagine a set of fixed-length values in atable being so long.</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">CHAR ( max_length )</FONT></PRE><P>Subtype: <TT>CHARACTER</TT></P><P><TT>VARCHAR2</TT> stores variable-length character strings. You would normallyuser <TT>VARCHAR2</TT> instead of <TT>CHAR </TT>to store variable-length data, suchas an individual's name. The maximum length of <TT>VARCHAR2</TT> is also 32,767 bytes.</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">VARCHAR2 ( max_length )</FONT></PRE><P>Subtypes: <TT>VARCHAR</TT>, <TT>STRING</TT></P><P><TT>LONG</TT> also stores variable-length character strings, having a maximumlength of 32,760 bytes. <TT>LONG</TT> is typically used to store lengthy text suchas remarks, although <TT>VARCHAR2</TT> may be used as well.<H3><FONT COLOR="#000077">Numeric Data Types</FONT></H3><P><TT>NUMBER</TT> stores any type of number in an Oracle database.</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">NUMBER ( max_length )</FONT></PRE><P>You may specify a <TT>NUMBER</TT>'s data precision with the following syntax:</P><PRE><FONT COLOR="#0066FF">NUMBER (precision, scale)</FONT></PRE><P>Subtypes: <TT>DEC</TT>, <TT>DECIMAL</TT>, <TT>DOUBLE PRECISION</TT>, <TT>INTEGER</TT>,<TT>INT</TT>, <TT>NUMERIC</TT>, <TT>REAL</TT>, <TT>SMALLINT</TT>, <TT>FLOAT</TT></P><P><TT>PLS_INTEGER</TT> defines columns that may contained integers with a sign,such as negative numbers.<H3><FONT COLOR="#000077">Binary Data Types</FONT></H3><P>Binary data types store data that is in a binary format, such as graphics or photographs.These data types include <TT>RAW</TT> and <TT>LONGRAW</TT>.<H3><FONT COLOR="#000077">The DATE Data Type</FONT></H3><P><TT>DATE</TT> is the valid Oracle data type in which to store dates. When youdefine a column as a <TT>DATE</TT>, you do not specify a length, as the length ofa <TT>DATE</TT> field is implied. The format of an Oracle date is, for example, 01-OCT-97.<H3><FONT COLOR="#000077">BOOLEAN</FONT></H3><P><TT>BOOLEAN</TT> stores the following values: <TT>TRUE</TT>, <TT>FALSE</TT>, and<TT>NULL</TT>. Like <TT>DATE</TT>, <TT>BOOLEAN</TT> requires no parameters when definingit as a column's or variable's data type.<H3><FONT COLOR="#000077">ROWID</FONT></H3><P><TT>ROWID</TT> is a pseudocolumn that exists in every table in an Oracle database.The <TT>ROWID</TT> is stored in binary format and identifies each row in a table.Indexes use <TT>ROWID</TT>s as pointers to data.<H2><FONT COLOR="#000077">The Structure of a PL/SQL Block</FONT></H2><P>PL/SQL is a block-structured language, meaning that PL/SQL programs are dividedand written in logical blocks of code. Within a PL/SQL block of code, processes suchas data manipulation or queries can occur. The following parts of a PL/SQL blockare discussed in this section:<UL> <LI>The <TT>DECLARE</TT> section contains the definitions of variables and other objects such as constants and cursors. This section is an optional part of a PL/SQL block. <P> <LI>The <TT>PROCEDURE</TT> section contains conditional commands and SQL statements and is where the block is controlled. This section is the only mandatory part of a PL/SQL block. <P> <LI>The <TT>EXCEPTION</TT> section tells the PL/SQL block how to handle specified errors and user-defined exceptions. This section is an optional part of a PL/SQL block.</UL><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>A block is a logical unit of PL/SQL code, containing at the least a <TT>PROCEDURE</TT> section and optionally the <TT>DECLARE</TT> and <TT>EXCEPTION</TT> sections. <HR></BLOCKQUOTE><P>Here is the basic structure of a PL/SQL block:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">BEGIN -- optional, denotes beginning of block DECLARE -- optional, variable definitions BEGIN -- mandatory, denotes beginning of procedure section EXCEPTION -- optional, denotes beginning of exception section END -- mandatory, denotes ending of procedure sectionEND -- optional, denotes ending of block</FONT></PRE><P>Notice that the only mandatory parts of a PL/SQL block are the second <TT>BEGIN</TT>and the first <TT>END</TT>, which make up the <TT>PROCEDURE</TT> section. Of course,you will have statements in between. If you use the first <TT>BEGIN</TT>, then youmust use the second <TT>END</TT>, and vice versa.<H3><FONT COLOR="#000077">Comments</FONT></H3><P>What would a program be without comments? Programming languages provide commandsthat allow you to place comments within your code, and PL/SQL is no exception. Thecomments after each line in the preceding sample block structure describe each command.The accepted comments in PL/SQL are as follows:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">-- This is a one-line comment.</FONT></PRE><PRE><FONT COLOR="#0066FF">/* This is amultiple-line comment.*/</FONT></PRE><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>PL/SQL directly supports Data Manipulation Language (DML) commands and database queries. However, it does not support Data Dictionary Language (DDL) commands. You can generally use PL/SQL to manipulate the data within database structure, but not to manipulate those structures. <HR></BLOCKQUOTE><H2><FONT COLOR="#000077">The DECLARE Section</FONT></H2><P>The <TT>DECLARE</TT> section of a block of PL/SQL code consists of variables,constants, cursor definitions, and special data types. As a PL/SQL programmer, youcan declare all types of variables within your blocks of code. However, you mustassign a data type, which must conform to Oracle's rules of that particular datatype, to every variable that you define. Variables must also conform to Oracle'sobject naming standards.<H3><FONT COLOR="#000077">Variable Assignment</FONT></H3><P>Variables are values that are subject to change within a PL/SQL block. PL/SQLvariables must be assigned a valid data type upon declaration and can be initializedif necessary. The following example defines a set of variables in the <TT>DECLARE</TT>portion of a block:</P><PRE><FONT COLOR="#0066FF">DECLARE owner char(10); tablename char(30); bytes number(10); today date;</FONT></PRE><H5>ANALYSIS:</H5><P>The <TT>DECLARE</TT> portion of a block cannot be executed by itself. The <TT>DECLARE</TT>section starts with the <TT>DECLARE</TT> statement. Then individual variables aredefined on separate lines. Notice that each variable declaration ends with a semicolon.</P><P>Variables may also be initialized in the <TT>DECLARE</TT> section. For example:</P><PRE><FONT COLOR="#0066FF">DECLARE customer char(30); fiscal_year number(2) := '97';</FONT></PRE><P>You can use the symbol <TT>:=</TT> to initialize, or assign an initial value,to variables in the <TT>DECLARE</TT> section. You must initialize a variable thatis defined as <TT>NOT NULL</TT>.</P><PRE><FONT COLOR="#0066FF">DECLARE customer char(30); fiscal_year number(2) NOT NULL := '97';</FONT></PRE><H5>ANALYSIS:</H5><P>The <TT>NOT NULL</TT> clause in the definition of <TT>fiscal_year</TT> resemblesa column definition in a <TT>CREATE TABLE</TT> statement.<H3><FONT COLOR="#000077">Constant Assignment</FONT></H3><P>Constants are defined the same way that variables are, but constant values arestatic; they do not change. In the previous example, <TT>fiscal_year</TT> is probablya constant.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>You must end each variable declaration with a semicolon. <HR></BLOCKQUOTE><H3><FONT COLOR="#000077">Cursor Definitions</FONT></H3><P>A cursor is another type of variable in PL/SQL. Usually when you think of a variable,a single value comes to mind. A cursor is a variable that points to a row of datafrom the results of a query. In a multiple-row result set, you need a way to scrollthrough each record to analyze the data. A cursor is just that. When the PL/SQL blocklooks at the results of a query within the block, it uses a cursor to point to eachreturned row. Here is an example of a cursor being defined in a PL/SQL block:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF"><B>DECLARE cursor employee_cursor is select * from employees;</B></FONT></PRE><P>A cursor is similar to a view. With the use of a loop in the <TT>PROCEDURE</TT>section, you can scroll a cursor. This technique is covered shortly.<H3><FONT COLOR="#000077">The %TYPE Attribute</FONT></H3><P><TT>%TYPE</TT> is a variable attribute that returns the value of a given columnof a table. Instead of hard-coding the data type in your PL/SQL block, you can use<TT>%TYPE</TT> to maintain data type consistency within your blocks of code.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF"><B>DECLARE cursor employee_cursor is select emp_id, emp_name from employees; id_num employees.emp_id%TYPE; name employees.emp_name%TYPE;</B></FONT></PRE><H5>ANALYSIS:</H5><P>The variable <TT>id_num</TT> is declared to have the same data type as <TT>emp_id</TT>in the <TT>EMPLOYEES</TT> table. <TT>%TYPE</TT> declares the variable <TT>name</TT>to have the same data type as the column <TT>emp_name</TT> in the <TT>EMPLOYEES</TT>table.<H3><FONT COLOR="#000077">The %ROWTYPE Attribute</FONT></H3><P>Variables are not limited to single values. If you declare a variable that isassociated with a defined cursor, you can use the <TT>%ROWTYPE</TT> attribute todeclare the data type of that variable to be the same as each column in one entirerow of data from the cursor. In Oracle's lexicon the <TT>%ROWTYPE</TT> attributecreates a record variable.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF"><B>DECLARE cursor employee_cursor is select emp_id, emp_name from employees; employee_record employee_cursor%ROWTYPE;</B></FONT></PRE><H5>ANALYSIS:</H5><P>This example declares a variable called <TT>employee_record.</TT> The <TT>%ROWTYPE</TT>attribute defines this variable as having the same data type as an entire row ofdata in the <TT>employee_cursor</TT>. Variables declared using the <TT>%ROWTYPE</TT>attribute are also called aggregate variables.<H3><FONT COLOR="#000077">The %ROWCOUNT Attribute</FONT></H3>?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -