Introduction to PL/SQL: Procedural Language Extension for Oracle Databases

1. SUCHITA M. DAKI

2. Introduction to PL/SQL • Procedural Language extension for SQL • Oracle Proprietary • 3GL Capabilities • Integration of SQL • Portable within Oracle data bases • Callable from any client

3. Structure of PL/SQL • PL/SQL is Block Structured A block is the basic unit from which all PL/SQL programs are built. A block can be named (functions and procedures) or anonymous • Sections of block 1- Header Section 2- Declaration Section 3- Executable Section 4- Exception Section Guide to Oracle 10g

4. Structure of PL/SQL HEADER Type and Name of block DECLARE Variables; Constants; Cursors; BEGIN PL/SQL and SQL Statements EXCEPTION Exception handlers END; Guide to Oracle 10g

5. Structure of PL/SQL DECLARE a number; text1 varchar2(20); text2 varchar2(20) := “HI”; BEGIN ---------- ---------- ---------- END; • Important Data Types in PL/SQL include NUMBER, INTEGER, CHAR, VARCHAR2, DATE etc • to_date(‘02-05-2007','dd-mm-yyyy') { Converts String to Date}

6. IF/THEN • Decision control structures • Alter order in which statements execute • Based on values of certain variables • Syntax: IF condition THEN commands that execute if condition is TRUE; END IF; • Condition • Expression evaluates to TRUE or FALSE • If TRUE commands execute

7. Question: What two lines do you need to change to make the program display Today is not Friday when the current day is different than Friday? 7

8. IF/THEN/ELSE • Syntax: IF condition THEN commands that execute if condition is TRUE; ELSE commands that execute if condition is FALSE; END IF; • Evaluates ELSE command if condition FALSE Nested IF/THEN/ELSE • Placing one or more IF/THEN/ELSE statements within program statements that execute after IF or ELSE command • Important to properly indent program lines

9. IF/THEN/ELSE

10. Nested IF/THEN/ELSE

11. IF/ELSIF

12. Logical Operators AND, OR, and NOT • Create complex expressions for decision control structure condition • AND: Expressions on both sides of operator must be true for combined expression to be TRUE • OR: Expressions on either side of operator must be true for combined expression to be TRUE • Order of evaluation (precedence): • NOT • AND • OR • Parentheses can be used to override precedence and force the program to evaluate the OR first

13. Using SQL Queries in PL/SQL Programs • Use SQL action query • Put query or command in PL/SQL program • Use same syntax as the syntax used to execute query or command in SQL*Plus • Can use variables instead of literal values like ‘Tammy’ • To specify data values INSERT INTO Student (s_first)VALUES (curr_first_name); WHERE s_first = curr_first_name;

14. Changing the values one by one and adding them to the table requires a lot of coding. What is the best way of handling this kind of repetitive job in programming?

15. Loops • Systematically executes program statements • Periodically evaluates exit condition to determine if loop should repeat or exit • Pretest loop • Evaluates exit condition before any program commands execute • Posttest loop • Executes program commands before loop evaluates exit condition for first time • PL/SQL has 5 types of loop structures: • LOOP…EXIT - WHILE…LOOP • LOOP…EXIT WHEN - Numeric FOR loop • Cursor FOR loop

16. Syntax SQL statement • LOOP • [program statements] • IF condition THEN • EXIT; • END IF; • [additional program statements] • END LOOP; • CREATE TABLE count_table(counter NUMBER(2)); The LOOP...EXIT Loop • Pretest or posttest • Syntax:

17. Syntax (posttest loop) • LOOP • programstatements • EXIT WHENcondition; • END LOOP; The LOOP...EXIT WHEN Loop

18. Syntax • WHILEconditionLOOP • program statements • END LOOP; The WHILE...LOOP • WHILE…LOOP is a Pretest loop Q: Why the SELECT * FROM count_table shows only counter 6 to 10?

19. Syntax FOR counter_variable IN start_value .. end_value LOOP program statements END LOOP; The Numeric FOR Loop • Does not require explicit counter increment

20. Cursor • A pointer to memory location on database server • Used to: • Retrieve and manipulate database data in PL/SQL programs • Types: • Implicit cursor • Explicit cursor

21. Implicit Cursors • Context area • A memory location created by INSERT, UPDATE, DELETE, or SELECT • Contains information about query (# rows, etc.) • Active set • Set of data rows that query retrieves when a SELECT query is issued • Implicit cursor • A pointer to the context area • Called so, because you do not need to write codeto explicitly create the cursor or retrieve its values • Used to assign output of SELECT query to PL/SQL program variables when query will return only one record* * Error occurs if query returns no records or more than one record

22. Implicit Cursors (continued) • To retrieve data using implicit cursor in PL/SQL, you add an INTO clause to the SELECT query • Syntax: SELECT field1, field2, ... INTO variable1, variable2, ... FROM table1, table2, ... WHERE join_conditions AND search_condition_to_retrieve_1_record; • Variables must be declared in Declaration section • Variables must have same data types as fields • To avoid errors, %TYPE reference data type should be used

23. Implicit Cursors (continued)

24. Explicit Cursors • Retrieve and display data in PL/SQL programs for query that might • Retrieve multiple records • Return no records at all • Must explicitly write the code to • Declare cursor • Open cursor • Fetch data rows • Close cursor

25. Explicit Cursors (continued) • Declare explicit cursor syntax: • CURSOR cursor_name IS select_query; • Open explicit cursor syntax: • OPEN cursor_name; • Fetch values using LOOP…EXIT WHEN loop: LOOP FETCH cursor_name INTO variable_name(s); EXIT WHEN cursor_name%NOTFOUND; • Close cursor syntax: • CLOSE cursor_name; Note: When the cursor is declared, system doesn’t check errors in the query. It creates the memory structure to store the active set. The PL/SQL interpreter checks for error and interprets the query when opening the cursor

26. Q: At this point, what is the value for current_bldg_code? The declared variable is used to retrieve the cursor content and to display

27. Using a cursor and a single variable to retrieve multiple fields values • Using %ROWTYPE variable to display explicit cursor values

28. Handling Runtime Errors in PL/SQL Programs • Runtime errors • Occur when an exception (unwanted event) is raised • Cause program to fail during execution • Possible causes (exceptions): • Division by zero - inserting incompatible data • Constraint violation - retrieving 0/several rows with implicit cursor • Exception handling • Programmers place commands in EXCEPTION section • Handle exception options • Correct error without notifying user of problem • Inform user of error without taking corrective action • After exception handler executes • Program ends DECLARE variable declarationsBEGIN program statementsEXCEPTION error-handling statementsEND;

29. Handling error procedure depends the type of exception: • Predefined exception - undefined exception • User-defined exception

30. Predefined Exceptions • Most common errors that occur in programs • PL/SQL language: • Assigns exception name • Provides built-in exception handler for each predefined exception • System automatically displays error message informing user of nature of problem

31. Exception Handler Syntax • Can create exception handlers to display alternate error messages

33. Using the WHEN OTHERS exception • The SQLERRM built-in function is used to handle other exception • To use the SQLERRM function, you must • Declare a VARCHAR2 variable • Assign the declared variable the error’s text and code

34. Undefined Exceptions • Less common errors • Do not have predefined names • Must explicitly declare exception in program’s declaration section • Associate new exception with specific Oracle error code • Create exception handler in exception section • Using same syntax as for predefined exceptions

35. Loc_id 60 doesn’t exist in LOCATION Example of undefined exception • The ORA-02291 exception is not predefined. • Need to explicitly declare the exception and write a handler

36. Syntax DECLARE e_exception_name EXCEPTION; PRAGMA EXCEPTION_UNIT(e_exception_name, -Oracle_error_code); Creating an exception handler

37. User-defined Exceptions • Used to handle an exception that • Does not raise Oracle runtime error • But requires exception handling to • Enforce business rules or • Ensure integrity of database • Example: • Internal Northwoods’ rule is “Users can delete row from the ENROLLMENT table only if s_grade is NULL” • Trying to delete a delete an ENROLLMENT row where the s_grade is not NULL will raise an exception that needs to be handled