Oracle 英文教材-16

• Execute a PL/SQL block
16-2 Copyright Ó Oracle Corporation, 1998. All rights reserved.
About PL/SQL
• PL/SQL is an extension to SQL with design features of programming languages. • Data manipulation and query statements of SQL are included within procedural units of code.
Atlanta
Copyright Ó Oracle Corporation, 1998. All rights reserved.
Base Scalar Datatypes
• VARCHAR2 (maximum_length) • NUMBER [(precision, scale)] • DATE • CHAR [(maximum_length)]
Program Constructs
Anonymous block
DECLARE
Application trigger
Stored procedure/ function
Application procedure/ function
BEGIN EXCEPTION
Database trigger
END;
Oracle Server
Benefits of PL/SQL
Modularize program development
DECLARE
BEGIN
EXCEPTION
END;
16-5
Copyright Ó Oracle Corporation, 1998. All rights reserved.
Benefits of PL/SQL
•
•
DECLARE – Optional
– Variables, cursors, user-defined
exceptions BEGIN – Mandatory
– SQL statements – PL/SQL statements • •
EXCEPTION – Optional
– Actions to perform when
errors occur END; – Mandatory
DECLARE BEGIN EXCEPTION
END;
16-7
Copyright Ó Oracle Corporation, 1998. All rights reserved.
PL/SQL Block Structure
DECLARE v_variable VARCHAR2(5); BEGIN SELECT column_name INTO v_variable FROM table_name; EXCEPTION WHEN exception_name THEN ... END;
• DEFAULT keyword • NOT NULL constraint
16-19
Copyright Ó Oracle Corporation, 1998. All rights reserved.
Scalar Datatypes
• Hold a single value
• Have no internal components
16
Declaring Variables
Copyright Ó Oracle Corporation, 1998. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:
• View results through output variables.
16-12
Copyright Ó Oracle Corporation, 1998. All rights reserved.
Types of Variables
TRUE
25-OCT-99
“Four score and seven years ago our fathers brought forth upon this continent, a new nation, conceived in LIBERTY, and dedicated
• Initialize identifiers by using the assignment operator (:=) or the DEFAULT reserved word.
• Declare at most one identifier per line.
16-16 Copyright Ó Oracle Corporation, 1998. All rights reserved.
Assigning Values to Variables
Syntax
identifier := expr;
Examples Set a predefined hiredate for new employees.
v_hiredate := '31-DEC-98';
Set the employee name to “Maduro.”
• You can program with procedural language control structures. • It can handle errors.
16-6
Copyright Ó Oracle Corporation, 1998. All rights reserved.
PL/SQL Block Structure
• List the benefits of PL/SQL
• Recognize the basic PL/SQL block and its sections
• Describe the significance of variables in PL/SQL • Declare PL/SQL variables
256120.08
to the proposition that all men
are created equal.”
Atlanta
16-14 Copyright Ó Oracle Corporation, 1998. All rights reserved.
Declaring PL/SQL Variables
16-15
Copyright Ó Oracle Corporation, 1998. All rights reserved.
Declaring PL/SQL Variables
Guidelines
• Follow naming conventions.
• Initialize variables designated as NOT NULL.
• LONG
• LONG RAW
• BOOLEAN
• BINARY_INTEGER • PLS_INTEGER
16-21 Copyright Ó Oracle Corporation, 1998. All rights reserved.
Scalar Variable Declarations
Examples
Syntax
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];
Examples
Declare v_hiredate v_deptno v_location c_comm DATE; NUMBER(2) NOT NULL := 10; VARCHAR2(13) := 'Atlanta'; CONSTANT NUMBER := 1400;
• Declare and initialize variables in the declaration section.
• Assign new values to variables in the executable section. • Pass values into PL/SQL blocks through parameters.
Packaged procedure/ function
16-10
Copyright Ó Oracle Corporation, 1998. All rights reserved.
Use of Variables
Use variables for:
• Temporary storage of data
16-3
Copyright Ó Oracle Corporation, 1998. All rights reserved.
Benefits of PL/SQL
Integration
Application
Shared library
16-4 Copyright Ó Oracle Corporation, 1998. All rights reserved.
Function
FUNCTION name RETURN datatype IS BEGIN --statements RETURN value; [EXCEPTION] END;
BEGIN --statements [EXCEPTION] END;le Corporation, 1998. All rights reserved.
Naming Rules
• Two variables can have the same name, provided they are in different blocks. • The variable name (identifier) should not be the same as the name of table columns used in the block.
25-OCT-99 and seven years “Four score
ago our fathers brought new nation, conceived in
forth upon this continent, a
TRUE
256120.08
16-20
LIBERTY, and dedicated to the proposition that all men are created equal.”
DECLARE BEGIN EXCEPTION END;
16-8
Copyright Ó Oracle Corporation, 1998. All rights reserved.
Block Types
Anonymous
[DECLARE]
Procedure
PROCEDURE name IS BEGIN --statements [EXCEPTION] END;
• Manipulation of stored values • Reusability • Ease of maintenance
16-11
Copyright Ó Oracle Corporation, 1998. All rights reserved.
Handling Variables in PL/SQL
v_ename := 'Maduro';
16-18
Copyright Ó Oracle Corporation, 1998. All rights reserved.
Variable Initialization and Keywords
Using:
• Assignment operator (:=)
v_job v_count v_total_sal v_orderdate c_tax_rate v_valid VARCHAR2(9); BINARY_INTEGER := 0; NUMBER(9,2) := 0; DATE := SYSDATE + 7; CONSTANT NUMBER(3,2) := 8.25; BOOLEAN NOT NULL := TRUE;
DECLARE empno NUMBER(4); BEGIN SELECT empno INTO empno FROM emp WHERE ename = 'SMITH'; END;
16-17 Copyright Ó Oracle Corporation, 1998. All rights reserved.