📄 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="sams.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/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.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch17.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="ch19.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch19.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/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 Query
Language, or SQL. SQL is the language that enables relational database users to communicate
with the database in a straightforward manner. You can use SQL commands to query
the 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 optimizer
decides the most efficient way to execute your statement. If you send a series of
SQL statements to the server in standard SQL, the server executes them one at a time
in chronological order.</P>
<P>PL/SQL is Oracle's procedural language; it comprises the standard language of
SQL and a wide array of commands that enable you to control the execution of SQL
statements 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 of
third-generation programming languages. PL/SQL allows you to write interactive, user-friendly
programs that can pass values into variables. You can also use several predefined
packages, 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 storage
and internal-processing requirements. When writing PL/SQL blocks, you will be declaring
variables, which must be valid data types. The following subsections briefly describe
the 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 program
to make the data types compatible with data types in other programs, such as a COBOL
program, particularly if you are embedding PL/SQL code in another program. Subtypes
are simply alternative names for Oracle data types and therefore must follow the
rules 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 generally
defined 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 a
table 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 normally
user <TT>VARCHAR2</TT> instead of <TT>CHAR </TT>to store variable-length data, such
as 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 maximum
length of 32,760 bytes. <TT>LONG</TT> is typically used to store lengthy text such
as 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 you
define a column as a <TT>DATE</TT>, you do not specify a length, as the length of
a <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 defining
it 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 divided
and written in logical blocks of code. Within a PL/SQL block of code, processes such
as data manipulation or queries can occur. The following parts of a PL/SQL block
are 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 section
END -- 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 you
must 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 commands
that allow you to place comments within your code, and PL/SQL is no exception. The
comments 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 a
multiple-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, you
can declare all types of variables within your blocks of code. However, you must
assign a data type, which must conform to Oracle's rules of that particular data
type, to every variable that you define. Variables must also conform to Oracle's
object 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/SQL
variables must be assigned a valid data type upon declaration and can be initialized
if 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 are
defined 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 that
is 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> resembles
a 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 are
static; they do not change. In the previous example, <TT>fiscal_year</TT> is probably
a 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 data
from the results of a query. In a multiple-row result set, you need a way to scroll
through each record to analyze the data. A cursor is just that. When the PL/SQL block
looks at the results of a query within the block, it uses a cursor to point to each
returned 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 column
of 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 is
associated with a defined cursor, you can use the <TT>%ROWTYPE</TT> attribute to
declare the data type of that variable to be the same as each column in one entire
row of data from the cursor. In Oracle's lexicon the <TT>%ROWTYPE</TT> attribute
creates 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 of
data 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 + -