Introduction to PL/SQL

1. Introduction to PL/SQL

2. PL /SQL BLOCK • Anonymous block /Labeled blocks: • Executed only once • Subprograms : • Procedures Function Stored as DB standalone objects ,part of package. • Executed by explicit call • Triggers : • Associated with occurrence of an event

3. PL/SQL Program Blocks • Declaration section • Optional • Execution section • Required • Exception section • Optional • Comment statements • Enclosed within /* and */ for several lines’ comments • -- for single line comments /* Purpose: to enroll students in class */ -- Script: Student register

4. PL/SQL Variables and Data Types • Variable names must follow the Oracle naming standard (Example: current_s_id, not $current_s_id) • Strongly typed language • Explicitly declare each variable including data type before using variable • Variable declaration syntax: <variable_name>datatype [CONSTANT] [NOT NULL] [:= value]; • Default value always NULL

5. Fundamentals of PL/SQLCONVENTION

6. Scalar Variables • Reference single value such as number, date, string DEC – precision of 38 decimal digits

7. PL/SQL Charater datatype and subtypes

8. PL/SQL LOB • Data items such as text, graphic images, video clips, and sound waveforms. • Allow efficient, random, piecewise access to this data. • Following are the predefined PL/SQL LOB data types:

9. PL/SQL Literals Explicit numeric, character, string, or Boolean value not represented by an identifier. For example, TRUE, 786, PL/SQL supports • Numeric Literals • Character Literals • String Literals • BOOLEAN Literals • Date and Time Literals

10. ARRAY Composite Variables • Data object made up of multiple individual data elements • Data structure contains multiple scalar variables • Composite variable data types include: • RECORD (multiple scalar values similar to a table’s record) • TABLE (tabular structure with multiple columns and rows) • VARRAY (variable-sized array. Tabular structure that can expand or contract based on data values)

11. Reference Variables • Directly reference specific database column or row • Assume data type of associated column or row • %TYPE data declaration syntax: variable_nametablename.fieldname%TYPE; • %ROWTYPE data declaration syntax: variable_nametablename%ROWTYPE;

12. PL/SQL Arithmetic Operators in Describing Order of Precedence • Parentheses are used to force PL/SQL interpreter to evaluate operations in a certain order total_hours_worked - 40 * over_time_rate(total_hours_worked – 40) * over_time_rate Questions: 2 * 2 ** 2 = ? 100 / 2 * 5 = ?

13. Assignment Statements DECLARE variable1 NUMBER := 0; variable2 NUMBER := 0; BEGIN variable2 := variable1 +1; END; • Assigns value to variable • Operator: := • Syntax: variable_name := value; • String literal within single quotation mark • Examples: current_s_first_name := ‘Tammy’; current_student_ID NUMBER := 100; • Result of adding a value to a NULL value is another NULL value • DEFAULT keyword can be used instead of assignment operator Q: What is the final value of variable2?

14. Displaying PL/SQL Program Output in SQL*Plus • PL/SQL output buffer • Memory area on database server • Stores program’s output values before they are displayed to user • Default buffer size is 2000 bytes • Should increase size if you want to display more than a few lines in SQL Plus to avoid buffer overflow error • Syntax: SET SERVEROUTPUT ON SIZE buffer_size • Example: SET SERVEROUTPUT ON SIZE 4000

15. Displaying PL/SQL Program Output in SQL*Plus (continued) • DBMS_OUTPUT • is an Oracle built-in package • Consists of a set of programs for processing output • PUT_LINE is the DBMS_OUTPUT procedure for displaying output • Syntax:DBMS_OUTPUT.PUT_LINE('display_text'); • Example: DBMS_OUTPUT.PUT_LINE(current_s_first); • Displays maximum of 255 characters of text data • If try to display more than 255 characters, error occurs

16. Writing a PL/SQL Program • Write PL/SQL program in Notepad or another text editor • Indenting commands within each section is a good programming practice. Will loose points if code is not indented • Copy and paste program commands from text editor into SQL*Plus • Press Enter after last program command • Type front slash ( / ) • Then press Enter again

17. PL/SQL Program Commands

18. PL/SQL Data Conversion Functions WHERE O_DATE = TO_DATE (‘29/05/2006’, ‘DD/MM/YYYY’) WHERE O_DATE = ‘29/05/2006’ • Implicit conversions • Interpreter automatically converts value from one data type to another • If PL/SQL interpreter unable to implicitly convert value error occurs • Explicit conversions • Convert variables to different data types • Using data conversion functions

19. Manipulating Character Strings • Concatenating • Joining two separate strings • Operator: || (i.e. double bar) • Syntax: new_string := string1 || string2; • Example: s_fullname := s_first || s_last; • Parse • Separate single string consisting of two data items separated by commas or spaces s_fullname := s_first ||‘ ’|| s_last; room_message := bldg_code || ‘ Room ’ || room_num || ‘ has ’ || TO_CHAR(room_capacity) || ‘seats.’;

20. Removing Blank Leading and Trailing Spaces from Strings • LTRIM function • Remove blank leading spaces • string := LTRIM(string_variable_name); • RTRIM function • Remove blank trailing spaces • string := RTRIM(string_variable_name); DECLARE s_address CHAR(20) := ‘Nirma University ’; BEGIN s_address := RTRIM(s_address); END;

21. Finding the Length of Character Strings • LENGTH function syntax • string_length := LENGTH(string_variable_name); • Example: • code_length as NUMBER(3):= LENGTH(bldg_code); What will be the value of code_length if bldg_code’s value is ‘Data base design and administration’?

22. Character String Case Functions • Modify case of character strings • Functions and syntax: • string := UPPER(string_variable_name); • string := LOWER(string_variable_name); • string := INITCAP(string_variable_name); • Example: • s_full_name := UPPER(s_full_name);

23. Parsing Character Strings • INSTR function • Searches string for specific substring • Returns an integer representing starting position of the substring within the original string • Syntax: start_position := INSTR(original_string, substring); • Example: blank_position := INSTR(blank_space, ‘ ’); • SUBSTR function • Extracts specific number of characters from character string starting at given point. • Syntax: extracted_string := SUBSTR(string_variable,starting_point, number_of_characters); curr_course_no := ‘ITNU 15’

24. Guide to Oracle 10g

25. PL/SQL Decision Control Structures • Sequential processing • Processes statements one after another • Decision control structures • Alter order in which statements execute • Based on values of certain variables

26. IF/THEN • Syntax: IF condition THEN commands that execute if condition is TRUE; END IF; • Condition • Expression evaluates to TRUE or FALSE • If TRUE commands execute

27. PL/SQL Comparison Operators

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

29. IF/ELSIF • Syntax: IFcondition1THEN commands that execute if condition1 is TRUE; ELSIFcondition2THEN commands that execute if condition2 is TRUE; ... ELSE commands that execute if no conditions TRUE; END IF; • Note there is no E fire IF in ELSIF, and no spaces. • Order of evaluation: • NOT (evaluated first) • AND • OR

30. Evaluating AND and OR in an Expression

31. Using SQL Queries in PL/SQL Programs • Use SQL action query • Can use variables instead of literal values to specify data values • Ex: • curr_f_name := ‘Ahmad’ • We need to insert ‘Ahmad’ into a column S_FIRST • INSERT INTO student (s_first) • VALUES (curr_f_name); • Ex: WHERE s_first = curr_f_name; PL/SQL Command

32. Using SQL Commands in PL/SQL Programs

33. 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 • In PL/SQL there are FIVE loop structures.

34. The LOOP...EXIT Loop • This kind of loop can be either Pretest or posttest • Syntax: LOOP [program statements] IFcondition THEN EXIT; END IF; [additional program statements] END LOOP;

35. The LOOP...EXIT Loop • LOOP keyword signals the beginning of the loop. • If the IF/THEN decision structure is the first code in the loop, it is a pretest loop. • If the IF/THEN decision structure is the last code in the loop, it is a posttest loop.

36. The LOOP...EXIT WHEN Loop • Can be either a Pretest or posttest • Syntax: LOOP programstatements EXIT WHENcondition; END LOOP;

37. The WHILE...LOOP • It is a Pretest loop that test the condition before it execute any program statements. • Syntax: WHILEcondition LOOP program statements END LOOP;

38. The Numeric FOR Loop • In the previous LOOP structures we had to increment the counter variable. • The numeric FOR does not require explicit counter increment • Automatically increments counter • Syntax: FORcounter_variableINstart_value ... end_value LOOP program statements END LOOP; • Start_value and end_value must be integer. The increment is always done by 1.

39. SWITCH Statement CASE Syntax CASE grade WHEN 'A' THEN dbms_output.put_line('Excellent'); /* write as many cases as required */ ELSE dbms_output.put_line('No such grade'); END CASE; Searched CASE Statement CASE WHEN grade = 'A' THEN dbms_output.put_line('Excellent'); /* write as many cases as required */ ELSE dbms_output.put_line('No such grade'); END CASE; Guide to Oracle 10g

40. SQL in PL/SQL • DDL • Create, drop, alter, grant, revoke • DML- • select, insert, update, delete, explain plan • TCL • Commit, rollback, savepoint, set transaction • Session control • Alter session, set role • System control • Alter system