1 / 61

Database Systems

Database Systems. Introduction to PL/SQL. 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. PL/SQL Variables.

kathy
Download Presentation

Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Database Systems Introduction to PL/SQL

  2. 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

  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. PL/SQL Program Structure DECLARE Variable declarations BEGIN Program statements EXCEPTION Error-handling statements END; Variable Declarations Body Exception Section

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

  11. Comment Statements • 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

  12. Arithmetic Operators Example Result

  13. 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;

  14. Displaying PL/SQL Outputin 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;

  15. Displaying PL/SQL ProgramOutput 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:’);

  16. Executing a PL/SQL Programin SQL*Plus • Copy program code from Notepad to SQL*Plus • Type / to execute

  17. 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’);

  18. 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

  19. 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);

  20. 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’, ‘ ’);

  21. 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);

  22. 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

  23. 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

  24. Locating and Fixing Logic Errors • Identify the output variable(s) that have the error. • Identify the inputs and calculations that contribute to the error. • Display the values of the inputs using DBMS_OUTPUT commands. • Take a break and look at it again later. • Ask a fellow student for help. • Ask your instructor for help.

  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. PL/SQL Selection Structures • 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;

  27. PL/SQL Selection Structures • IF/ELSIF: IF condition1 THEN program statements; ELSIF condition2 THEN alternate program statements; ELSIF condition3 THEN alternate program statements; . . . ELSE alternate program statements; END IF;

  28. 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. Using SQL Commands in PL/SQL Programs

  31. 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

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

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

  34. WHILE Loop WHILE condition LOOP program statements END LOOP; Pretest • WHILE … LOOP

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

  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. Cursors Database Server Memory Cursor Number of rows processed Parsed command statement context area active set

  38. Types of Cursors • Implicit • Explicit

  39. 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

  40. Implicit Cursor Syntax SELECT field1, field2, … INTO variable1, variable2, … FROM tablename WHERE search_condition_that_will_ return_a_single_record;

  41. 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

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

  43. Declaring an Explicit Cursor DECLARE CURSOR cursor_name IS SELECT_statement;

  44. Opening an Explicit Cursor OPEN cursor_name;

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

  46. Closing an Explicit Cursor CLOSE cursor_name;

  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. Explicit Cursor Attributes

More Related