Database Systems Introduction to PLSQL 1 What is

Database Systems Introduction to PL/SQL 1

What is PL/SQL? § Procedural programming language § Uses detailed instructions § Processes statements sequentially § Combines SQL commands with procedural instructions § Used to perform sequential processing using an Oracle database 2

PL/SQL Variables n Variable names must follow the Oracle naming standard n n n 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 n Example: current_s_id 3

Declaring PL/SQL Variables n PL/SQL is a strongly-typed language n n All variables must be declared prior to use Syntax for declaring a variable: variable_name data_type_declaration; n Example: current_s_id NUMBER(6); 4

PL/SQL Data Types n Scalar n n Composite n n References a data structure Reference n n References a single value References a specific database item LOB n References a large binary object 5

Scalar Data Types n Database scalar data types: n n n VARCHAR 2 CHAR DATE LONG NUMBER Non-database scalar data types: n n n Integers: BINARY_INTEGER, INT, SMALLINT Decimal numbers: DEC, DECIMAL, DOUBLE, PRECISION, NUMERIC, REAL BOOLEAN 6

Composite Data Types n n Reference multiple data elements, such as a record Types: n n n RECORD TABLE VARRAY n Tabular structure that can expand or contract as needed 7

Reference Data Types n n Reference a database item Assume data type of item n n %TYPE: assumes data type of field %ROWTYPE: assumes data type of entire row 8

PL/SQL Program Structure DECLARE Variable declarations Variable Declarations BEGIN Program statements Body EXCEPTION Error-handling statements END; Exception Section 9

PL/SQL Program Lines § May span multiple text editor lines § Each line ends with a semicolon § Text is not case sensitive 10

Comment Statements § Block of comments are delimited with /* */ /* <comment that spans more than one line of code> */ 4 Single comment line starts with 2 hyphens -- comment on a single line 11

Arithmetic Operators Example Result 12

Assignment Statements § 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; 13

Displaying PL/SQL Output in SQL*Plus § 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; 14

Displaying PL/SQL Program Output in SQL*Plus § Command to output data from a PL/SQL program in SQL*Plus: DBMS_OUTPUT. PUT_LINE(‘output string’); DBMS_OUTPUT. PUT_LINE(‘Current Output: ’); 15

Executing a PL/SQL Program in SQL*Plus § Copy program code from Notepad to SQL*Plus § Type / to execute 16

PL/SQL Data Type Conversion Functions § TO_DATE: character string to DATE TO_DATE(‘ 07/14/01’, ‘MM/DD/YY’); § TO_NUMBER: character string to NUMBER TO_NUMBER(‘ 2’); § TO_CHAR: NUMBER or DATE to character string TO_CHAR(2); TO_CHAR(SYSDATE, ‘MM/DD/YYYY HH: MI’); 17

Character String Functions § 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 18

PL/SQL Character String Functions § RTRIM: removes blank trailing spaces cust_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 case s_name : = UPPER(s_name); s_name : = LOWER(s_name); 19

PL/SQL Character String Functions § INSTR: searches a string and looks for a matching substring and returns its starting position starting_position : = INSTR(string_being_searched, search_string>); blank_position : = INSTR(‘Sarah Miller’, ‘ ’); 20

PL/SQL Character String Functions § SUBSTR: extracts a specific number of characters from a string, starting at a given point extracted_string : = SUBSTR(string_being_searched, starting_point, number_of_characters_to_extract); s_first_name : = SUBSTR(‘Sarah Miller’, 1, 5); 21

Debugging PL/SQL Programs § 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 22

Locating and Correcting Syntax Errors § 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 23

Locating and Fixing Logic Errors 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. 24

NULL Values in Assignment Statements n 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 n Advice: Always initialize variable values n 25

PL/SQL Selection Structures 4 IF/THEN § IF/END IF: IF condition THEN program statements END IF; § IF/ELSE/END IF: IF condition THEN program statements ELSE alternate program statements END IF; 26

PL/SQL Selection Structures § IF/ELSIF: IF condition 1 THEN program statements; ELSIF condition 2 THEN alternate program statements; ELSIF condition 3 THEN alternate program statements; . . . ELSE alternate program statements; END IF; 27

PL/SQL Comparison Operators 28

Evaluating NULL Conditions in IF/THEN Structures n n If a condition evaluates as NULL, then it is FALSE How can a condition evaluate as NULL? n n It uses a BOOLEAN variable that has not been initialized It uses any other variable that has not been initialized 29

Using SQL Commands in PL/SQL Programs 30

PL/SQL Loops § 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 31

LOOP … EXIT Loop 4 LOOP … EXIT LOOP program statements IF condition THEN Pretest EXIT; OR Posttest END IF; more program statements END LOOP; 32

LOOP … EXIT WHEN Loop LOOP program statements EXIT WHEN condition; END LOOP; Posttest 33

WHILE Loop WHILE condition LOOP program statements END LOOP; Pretest 4 WHILE … LOOP 34

Numeric FOR Loop FOR counter_variable IN start_value. . end_value LOOP Preset program statements number of END LOOP; iterations 35

Cursors n n Pointer to a server memory location Contains information about a SQL command in a PL/SQL program n Called the command’s context area 36

Cursors Database Server Memory Cursor context area Number of rows processed Parsed command statement active set 37

Types of Cursors § Implicit § Explicit 38

Implicit Cursors § 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 39

Implicit Cursor Syntax SELECT field 1, field 2, … INTO variable 1, variable 2, … FROM tablename WHERE search_condition_that_will_ return_a_single_record; 40

Explicit Cursors § 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 41

Using an Explicit Cursor § Declare the cursor § Open the cursor § Fetch the cursor result into PL/SQL program variables § Close the cursor 42

Declaring an Explicit Cursor DECLARE CURSOR cursor_name IS SELECT_statement; 43

Opening an Explicit Cursor OPEN cursor_name; 44

Fetching Explicit Cursor Records FETCH cursor_name INTO variable_name(s); 45

Closing an Explicit Cursor CLOSE cursor_name; 46

Processing an Explicit Cursor n 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; 47

Processing an Explicit Cursor n Cursor FOR Loop approach: FOR variable_name(s) in cursor_name LOOP additional processing statements; END LOOP; 48

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 49

Explicit Cursor Attributes 50

PL/SQL Tables § 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 Key Value 1 Shadow 2 Dusty 3 Sassy 51

PL/SQL Table of Records n n n 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 52

PL/SQL Exception Handling n n 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 53

Predefined Exceptions § Common errors that have been given predefined names that appear instead of error numbers 54

Exception Handler Syntax For Predefined Exceptions WHEN exception 1_name exception handling WHEN exception 2_name exception handling … WHEN OTHERS THEN exception handling THEN statements; 55

Undefined Exceptions § 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 56

User-Defined Exceptions § Errors that will not cause a run-time error, but will violate business rules § Programmer creates a custom error message 57

Nested PL/SQL Program Blocks n An inner program block can be nested within an outer program block DECLARE variable declarations BEGIN program statements EXCEPTION error handling statements END; Outer block Inner block EXCEPTION error handling statements END; 58

Exception Handling in Nest Program Blocks n If an exception is raised and handled in an inner block, program execution resumes in the outer block 59

Exception Handling in Nested Program Blocks DECLARE variable declarations BEGIN program statements DECLARE exception_A BEGIN RAISE exception_A EXCEPTION exception_A error handler END; additional program statements EXCEPTION error handling statements END; Exception is raised and handled in inner block Program execution resumes here 60

Exception Handling in Nested Program Blocks n Exceptions raised in inner blocks can be handled by exception handlers in outer blocks 61