1 introduction to pl/sql. 2 procedural programming language uses detailed instructions processes...
TRANSCRIPT
2
Procedural programming language Uses detailed instructions Processes statements sequentially
Combines SQL commands with procedural instructions
Used to perform sequential processing using an Oracle database
What is PL/SQL?
3
PL/SQL Variables Variable names must follow the Oracle
naming standard Can use reserved words (BEGIN, NUMBER)
and table names for variable names, but is not a good practice
Make variable names descriptive Use lower-case letters, and separate
words with underscores Example: current_s_id
4
Declaring PL/SQL Variables PL/SQL is a strongly-typed
language All variables must be declared prior to
use Syntax for declaring a variable:variable_name data_type_declaration;
Example:current_s_id NUMBER(6);
5
PL/SQL Data Types Scalar
References a single value Composite
References a data structure Reference
References a specific database item LOB
References a large binary object
6
Scalar Data Types Database scalar data types:
VARCHAR2 CHAR DATE LONG NUMBER
Non-database scalar data types: Integers: BINARY_INTEGER, INTEGER, INT,
SMALLINT Decimal numbers: DEC, DECIMAL, DOUBLE,
PRECISION, NUMERIC, REAL BOOLEAN
7
Composite Data Types Reference multiple data elements,
such as a record Types:
RECORD TABLE VARRAY
Tabular structure that can expand or contract as needed
8
Reference Data Types Reference a database item Assume data type of item
%TYPE: assumes data type of field %ROWTYPE: assumes data type of
entire row
9
DECLAREVariable declarations
BEGINProgram statements
EXCEPTIONError-handling statements
END;
BodyBody
Variable Declarations
Variable Declarations
ExceptionSection
ExceptionSection
PL/SQL Program Structure
10
May span multiple text editor lines
Each line ends with a semicolon
Text is not case sensitive
PL/SQL Program Lines
11
Block of comments are delimited with /* */
/* <comment that spans more than one line of code> */
Single comment line starts with 2 hyphens-- comment on a single line
Comment Statements
12
** Exponentiation 2 ** 3 8
* Multiplication 2 * 3 6
/ Division 9/2 4.5
+ Addition 3 + 2 5
- Subtraction 3 – 2 1
- Negation -5 Negative 5
Example ResultArithmetic Operators
13
Assignment operator: := Variable being assigned to a new
value is on left side of assignment operator
New value is on right side of operator
student_name := ‘John Miller’;
student_name := current_student;
Assignment Statements
14
Normally PL/SQL is used with other Oracle utilities such as forms or reports
You will learn to use PL/SQL in SQL*Plus Command to activate memory buffer in
SQL*Plus to enable output from PL/SQL programs:
SQL> SET SERVEROUTPUT ON SIZE buffer_size;
SQL> SET SERVEROUTPUT ON SIZE 4000;
Displaying PL/SQL Outputin SQL*Plus
15
Command to output data from a PL/SQL program in SQL*Plus:
DBMS_OUTPUT.PUT_LINE(‘output string’);
DBMS_OUTPUT.PUT_LINE(‘Current Output:’);
Displaying PL/SQL ProgramOutput in SQL*Plus
16
Copy program code from Notepad to SQL*Plus
Type / to execute
Executing a PL/SQL Programin SQL*Plus
17
TO_DATE: character string to DATETO_DATE(‘07/14/01’, ‘MM/DD/YY’);
TO_NUMBER: character string to NUMBERTO_NUMBER(‘2’);
TO_CHAR: NUMBER or DATE to character string
TO_CHAR(2);
TO_CHAR(SYSDATE, ‘MM/DD/YYYY HH:MI’);
PL/SQL Data Type Conversion Functions
18
Concatenating strings: joining 2 or more character strings into a single string
Concatenation operator: ||s_first_name := ‘Sarah’
s_last_name := ‘Miller’
s_full_name := s_first_name || ‘ ’ || s_last_name
Character String Functions
19
RTRIM: removes blank trailing spacescust_address := RTRIM(cust_address);
LENGTH: returns string length (number of characters)
address_length := LENGTH(cust_address); UPPER, LOWER: changes characters to all
upper or lower cases_name := UPPER(s_name);s_name := LOWER(s_name);
PL/SQL Character String Functions
20
INSTR: searches a string and looks for a matching substring and returns its starting positionstarting_position := INSTR(string_being_searched, search_string>);
blank_position := INSTR(‘Sarah Miller’, ‘ ’);
PL/SQL Character String Functions
21
SUBSTR: extracts a specific number of characters from a string, starting at a given pointextracted_string := SUBSTR(string_being_searched, starting_point, number_of_characters_to_extract);
s_first_name := SUBSTR(‘Sarah Miller’, 1,5);
PL/SQL Character String Functions
22
Syntax error Does not follow language guidelines Causes a PLS- compile error Examples: misspelling a reserved
word, using a function incorrectly Logic error
Program compiles correctly, but does not give correct output
Debugging PL/SQL Programs
23
Isolate the line that is causing the error This may be before or after the line
that is flagged by the compiler Comment out lines as necessary
until program runs One error may cause several
cascading errors, so re-run program after fixing each error
Locating and Correcting Syntax Errors
24
1. Identify the output variable(s) that have the error.
2. Identify the inputs and calculations that contribute to the error.
3. Display the values of the inputs using DBMS_OUTPUT commands.
4. Take a break and look at it again later.5. Ask a fellow student for help.6. Ask your instructor for help.
Locating and Fixing Logic Errors
25
NULL Values in Assignment Statements Until a value is assigned to a
variable, the variable’s value is NULL Performing an arithmetic value on a
NULL value always results in a NULL value
Advice: Always initialize variable values
26
IF/END IF:IF condition THEN
program statementsEND IF;
IF/ELSE/END IF:IF condition THEN
program statementsELSE
alternate program statements
END IF;
IF/THEN
PL/SQL Selection Structures
27
IF/ELSIF:IF condition1 THEN
program statements;ELSIF condition2 THENalternate program statements;
ELSIF condition3 THENalternate program statements;
. . .ELSEalternate program statements;
END IF;
PL/SQL Selection Structures
28
Operator Description Example
= Equal Count = 5
<>, != Not Equal Count <> 5
> Greater Than Count > 5
< Less Than Count < 5
>= Greater Than or Equal To
Count >= 5
<= Less Than or Equal To Count <= 5
PL/SQL Comparison Operators
29
Evaluating NULL Conditions in IF/THEN Structures If a condition evaluates as NULL,
then it is FALSE How can a condition evaluate as
NULL? It uses a BOOLEAN variable that has
not been initialized It uses any other variable that has not
been initialized
30
SQL Command Category
Purpose Examples Can be used in PL/SQL
Data Definition Language (DDL)
Change database structure
CREATE, ALTER, GRANT, REVOKE
No
Data Manipulation Language (DML)
View or change data
SELECT, INSERT, UPDATE, DELETE
Yes
Transaction Control
Create logical transactions
COMMIT, ROLLBACK
Yes
Using SQL Commands in PL/SQL Programs
31
Loop: repeats one or more program statements multiple times until an exit condition is reached Pretest loop: exit condition is tested
before program statements are executed
Posttest loop: exit condition is tested after program statements are executed
PL/SQL Loops
32
LOOP
program statements
IF condition THEN
EXIT;
END IF;
more program statements
END LOOP;
LOOP … EXIT
Pretest OR Posttest
Pretest OR Posttest
LOOP … EXIT Loop
35
FOR counter_variable IN start_value .. end_valueLOOP program statementsEND LOOP;
Preset number of iterations
Preset number of iterations
Numeric FOR Loop
36
Cursors Pointer to a server memory
location Contains information about a SQL
command in a PL/SQL program Called the command’s context area
37
Cursor
contextarea
active setCID CALLID CNAME CCREDIT
1 MIS 101 Intro. to Info. Systems 32 MIS 301 Systems Analysis 33 MIS 441 Database Management 34 CS 155 Programming in C++ 35 MIS 451 Client/Server Systems 3
Number of rows
processed
Parsed commandstatement
Database Server Memory
Cursors
39
Created automatically every time you use an INSERT, UPDATE, DELETE, or SELECT command
Doesn’t need to be declared Can be used to assign the output of a
SELECT command to one or more PL/SQL variables
Can only be used if query returns one and only one record
Implicit Cursors
40
SELECT field1, field2, …
INTO variable1, variable2, …
FROM tablename
WHERE search_condition_that_will_
return_a_single_record;
Implicit Cursor Syntax
41
Must be declared in program DECLARE section
Can be used to assign the output of a SELECT command to one or more PL/SQL variables
Can be used if query returns multiple records or no records
Explicit Cursors
42
Declare the cursor Open the cursor Fetch the cursor result into
PL/SQL program variables Close the cursor
Using an Explicit Cursor
47
Processing an Explicit Cursor LOOP ..EXIT WHEN approach:OPEN cursor_name;
LOOP
FETCH cursor_name INTO variable_name(s);
EXIT WHEN cursor_name%NOTFOUND:
END LOOP;
CLOSE cursor_name;
48
Processing an Explicit Cursor Cursor FOR Loop approach:
FOR variable_name(s) in cursor_name LOOP
additional processing statements;
END LOOP;
49
Using Reference Data Types in Explicit Cursor Processing
Declaring a ROWTYPE reference variable:
DECLARE
reference_variable_name cursor_name%ROWTYPE;
Referencing a ROWTYPE reference variable:
reference_variable_name.database_field_name
50
Attribute Return Value
%NOTFOUND TRUE when no rows left to fetch; FALSE when rows left to fetch
%FOUND TRUE when rows left to fetch; FALSE when no rows left to fetch
%ROWCOUNT Number of rows a cursor has fetched so far
%ISOPEN TRUE if cursor is open and FALSE is cursor is closed
Explicit Cursor Attributes
51
Data structure that contains multiple data items that are the same data type
Each table item has a key and a value
Key values do not need to be sequential
Used to create a lookup table that is stored in memory to improve processing speed
PL/SQL TablesKey Value
1 Shadow
2 Dusty
3 Sassy
52
PL/SQL Table of Records PL/SQL table that can store
multiple values that are referenced by a key
Usually used to store database records that need to be processed by a PL/SQL program
Improves performance by limiting number of database retrievals
53
PL/SQL Exception Handling All error handling statements are
placed in the EXCEPTION program block
Exception handler: program command that provides information about an error, and suggest correction actions
54
Common errors that have been given predefined names that appear instead of error numbers
Error Code Exception Name Description ORA-00001 DUP_VAL_ON_INDEX Unique constraint violated ORA-01001 INVALID_CURSOR Illegal cursor operation
ORA-01403 NO_DATA_FOUND Query returns no records
ORA-01422 TOO_MANY_ROWS Query returns more rows than expected
ORA-01476 ZERO_DIVIDE Division by zero ORA-01722 INVALID_NUMBER Invalid numeric conversion
ORA-06502 VALUE_ERROR Error in arithmetic or numeric function operation
Predefined Exceptions
55
Exception Handler SyntaxFor Predefined Exceptions
WHEN exception1_name THEN
exception handling statements;
WHEN exception2_name THEN
exception handling statements;
…
WHEN OTHERS THEN
exception handling statements;
56
Less-common errors that have not been given predefined names
ORA- error code appears Exception handler tests for ORA-
error code and provides alternate error message
Undefined Exceptions
57
Errors that will not cause a run-time error, but will violate business rules
Programmer creates a custom error message
User-Defined Exceptions
58
Nested PL/SQL Program Blocks An inner
program block can be nested within an outer program block
DECLARE variable declarationsBEGIN program statements
EXCEPTION error handling statementsEND;
DECLARE variable declarationsBEGIN program statementsEXCEPTION error handling statementsEND;
Inner block
Outer block
59
Exception Handling in Nest Program Blocks If an exception is raised and
handled in an inner block, program execution resumes in the outer block
60
DECLARE variable declarationsBEGIN program statements
additional program statementsEXCEPTION error handling statementsEND;
Exception Handling in Nested Program Blocks
DECLARE exception_ABEGIN RAISE exception_AEXCEPTION exception_A error handlerEND;
Exception is raised andhandled in inner block
Program executionresumes here