Introduction to PL/SQL

1. Introduction to PL/SQL Part I

2. Objectives • SQL Vs. PL/SQL • PL/SQL Fundamentals • PL/SQL Block Structure • PL/SQL Control Structures • Database Interaction in PL/SQL • Exception Handling PL/SQL

3. SQL Vs. PL/SQL • SQL • No Procedural Capability • Time Consuming Processing • No Error Handling Procedures • No Sharing of code • Low performance

4. SQL Vs. PL/SQL • PL/SQL • Highly structured, readable, and accessible • Error Handling Procedures • Facilitate Sharing of code • Improved Transaction Performance • Portable and standard Code

5. PL/SQL Program Blocks • Comments: • Not executed by interpreter • Enclosed between /* and */ • On one line beginning with --

6. Executing a PL/SQL Program in SQL*Plus • Create program in text editor • Paste into SQL*Plus window • Press Enter, type / then enter to execute

7. Fundamentals of PL/SQL • Full-featured programming language • An interpreted language • Type in editor, execute in SQL*Plus

8. Variables and Data Types • Variables • Used to store numbers, character strings, dates, and other data values • Avoid using keywords, table names and column names as variable names • Must be declared with data type before use: variable_name data_type_declaration; stu_id CHAR(9);

9. Scalar Data Types • Represent a single value

10. Scalar Data Types

11. Assignment Statements Assigns a value to a variable variable_name := value; Value can be a literal: current_s_first_name := 'John'; Value can be another variable: current_s_first_name := s_first_name; num1:=num1+num2+(num3*3);school:=‘kv2ocfspn’;flag:=TRUE; 11

12. Declare Variable • Syntax identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; • Examples Declare birthday DATE; age NUMBER(2) NOT NULL := 27; name VARCHAR2(13) := 'Levi'; magic CONSTANT NUMBER := 77; valid BOOLEAN NOT NULL := TRUE;

13. Composite and Reference Variables • Composite variables • RECORD: contains multiple scalar values, similar to a table record • TABLE: tabular structure with multiple columns and rows • VARRAY: variable-sized array • Reference variables • Directly reference a specific database field or record and assume the data type of the associated field or record • %TYPE: same data type as a database field • %ROWTYPE: same data type as a database record

14. Record • DECLARE • TYPE sailor_record_type IS RECORD • (sname VARCHAR2(10), • sid VARCHAR2(9), • age NUMBER(3), • rating NUMBER(3)); • sailor_record sailor_record_type; • ... • BEGIN • Sailor_record.sname:=‘peter’; • Sailor_record.age:=45; • …

15. CREATE TABLE Books ( book_id INTEGER, isbn VARCHAR2(13), title VARCHAR2(100), summary VARCHAR2(2000), author VARCHAR2(200), date_published DATE, page_count NUMBER);

16. Record • Declare my_book books%ROWTYPE; BEGIN SELECT * INTO my_book FROM books WHERE title=‘Oracle PL/SQL Programming’; DBMS_OUTPUT.PUT_LINE(my_book.summary); …

17. Declaring Variables with the %TYPE Attribute • fav_boat VARCHAR2(30); my_fav_boat fav_boat%TYPE := 'Pinta'; • book_name books.title%TYPE;

18. Arithmetic Operators

19. PL/SQL Data Conversion Functions

20. Manipulating Character Strings with PL/SQL • To concatenate two strings in PL/SQL, you use the double bar (||) operator: • new_string := string1 || string2; • To remove blank leading spaces use the LTRIM function: • string := LTRIM(string_variable_name); • To remove blank trailing spaces use the RTRIM function: • string := RTRIM(string_variable_name); • To find the number of characters in a character string use the LENGTH function: • string_length := LENGTH(string_variable_name);

21. Manipulating Character Strings with PL/SQL • To change case, use UPPER, LOWER, INITCAP • INSTR function searches a string for a specific substring: • start_position := INSTR(original_string, substring); • SUBSTR function extracts a specific number of characters from a character string, starting at a given point: • extracted_string := SUBSTR(string_variable, starting_point, number_of_characters);

22. PL/SQL Decision Control Structures • Use IF/THEN structure to execute code if condition is true • IF condition THEN commands that execute if condition is TRUE; END IF; • If condition evaluates to NULL it is considered false • Use IF/THEN/ELSE to execute code if condition is true or false • IF condition THEN commands that execute if condition is TRUE; ELSE commands that execute if condition is FALSE; END IF; • Can be nested – be sure to end nested statements

23. IF/ELSE Example • DECLAREa NUMBER;b NUMBER;BEGINSELECT e, f INTO a, b FROM T1 WHERE e>1;IF b=1 THEN INSERT INTO T1 VALUES(b,a);ELSE INSERT INTO T1 VALUES(b+10,a+10);END IF;END; /

24. PL/SQL Decision Control Structures • Use IF/ELSIF to evaluate many conditions: • IF condition1 THEN commands that execute if condition1 is TRUE; ELSIF condition2 THEN commands that execute if condition2 is TRUE; ELSIF condition3 THEN commands that execute if condition3 is TRUE; ... ELSE commands that execute if none of the conditions are TRUE; END IF;

25. IF/ELSIF Example 1 . . . IF rating > 7 THEN v_message := 'You are great'; ELSIF rating >= 5 THEN v_message := 'Not bad'; ELSE v_message := 'Pretty bad'; END IF; . . .

26. IF/ELSIF Example 2

27. DECLARE male_avg number; female_avg number; BEGIN SELECT AVG(months_between(employment_date, birth_date)/12) INTO male_avg FROM Employee WHERE gender=‘M’; SELECT AVG(months_between(employment_date, birth_date)/12) INTO female_avg FROM Employee WHERE gender=‘F’; IF (male_avg > female_avg) THEN DBMS_OUTPUT.PUT_LINE(‘Males have the greater avg hiring age’); DBMS_OUTPUT.PUT_LINE(‘With the avg age of ‘ || to_char(male_avg)); IF(male_avg > female_avg + 10) THEN DBMS_OUTPUT.PUT_LINE(‘The male average is greater than 10 years’); END IF; ELSE DBMS_OUTPUT.PUT_LINE(‘Females have the greater avg hiring age’); DBMS_OUTPUT.PUT_LINE(‘With the avg age of ‘ || to_char(female_avg)); END IF; END; /

28. Complex Conditions • Created with logical operators AND, OR and NOT • AND is evaluated before OR • Use () to set precedence

29. Using SQL Queries in PL/SQL Programs • Action queries can be used as in SQL*Plus • May use variables in action queries • DDL commands may not be used in PL/SQL

30. Loops • Program structure that executes a series of program statements, and periodically evaluates an exit condition to determine if the loop should repeat or exit • Pretest loop:evaluates the exit condition before any program commands execute • Posttest loop:executes one or more program commands before the loop evaluates the exit condition for the first time

31. Loops • PL/SQL has 3 loop structures • loop … end loop; • while condition loop … end loop; • for variable in low_bound . . upper_bound loop … end loop;

32. The LOOP...EXIT Loop LOOP [program statements] IF condition THEN EXIT; END IF; [additional program statements] END LOOP

33. /*program to find the sum of first 10 natural numbers */Declare nsum number(6):=0; i number:=1;Begin Loop nsum:=nsum+ i; i:=i+1; if i>10 then Exit; End If; End Loop; dbms_output.put_line (‘The sum is:’ || nsum);End; /

34. The LOOP...EXIT WHEN Loop LOOP program statements EXIT WHEN condition; END LOOP;

35. /* Program to find the sum of first 10 natural number */DECLARE nsum nubmer(6):=0; i number:=1; BEGIN LOOP nsum:=nusm+1; i:=i+1; EXIT WHEN i=11; -- Because I will process till 10 END LOOP;dbms_output.put_line(‘The sum is: ‘|| nsum);END; /

36. The WHILE...LOOP WHILE condition LOOP program statements END LOOP;

37. /* Program to find the sum of first 10 natural numbers */DECLARE nsum number(6):=0; i number:=1;BEGIN WHILE i<11 LOOP --because it will execute till 10 nsum:=nsum+i; i:=i+1; END LOOP;DBMS_OUTPUT.PUT_LINE (‘The sum is :’||nsum); END; /

38. The Numeric FOR Loop FOR counter_variable IN start_value .. end_value LOOP program statements ENDLOOP;

39. /* Program to find the sum of first 10 natural numbers */DECLARE nsum number:=0; i number:=1;BEGIN FOR i IN 1..10 LOOP nsum:=nsum + i; END LOOP;DBMS_OUTPUT.PUT_LINE (‘The sum is: ’ || nsum);End; /

40. The CONTINUE Statement BEGIN FOR i_index IN 1..10 LOOP CONTINUE WHEN MOD(i_index, 2) = 0; DBMS_OUTPUT.PUT_LINE(‘Loop index = ‘ || TO_CHAR(l_index)); END LOOP; END; /

41. Cursors • Pointer to a memory location that the DBMS uses to process a SQL query • Use to retrieve and manipulate database data

42. Implicit Cursor

43. Using an Implicit Cursor • Executing a SELECT query creates an implicit cursor • To retrieve it into a variable use INTO: • SELECT field1, field2, ... INTO variable1, variable2, ... FROM table1, table2, ... WHERE join_ conditions AND search_condition_to_retrieve_1_record; • Can only be used with queries that return exactly one record

44. Using an Implicit Cursor DECLARE v_sname VARCHAR2(10); v_rating NUMBER(3); BEGIN SELECT sname, rating INTO v_sname, v_rating FROM Sailors WHERE sid = '112'; END; / • INTO clause is required. • Query must return exactly one row. • Otherwise, a NO_DATA_FOUND or TOO_MANY_ROWS exception is thrown.

45. Explicit Cursor • Use for queries that return multiple records or no records • Must be explicitly declared and used

46. Using an Explicit Cursor • Declare the cursor • CURSOR cursor_name IS select_query; • Open the cursor • OPEN cursor_name; • Fetch the data rows • LOOP FETCH cursor_name INTO variable_name(s); EXIT WHEN cursor_name%NOTFOUND; • Close the cursor • CLOSE cursor_name;

47. Using an Explicit Cursor sailorData is a variable that can hold a ROW from the sailors table DECLARE CURSOR c IS SELECT * FROM sailors; sailorData sailors%ROWTYPE; BEGIN OPEN c; FETCH c INTO sailorData; Here the first row of sailors is inserted into sailorData

48. Example DECLARE Pi CONSTONT NUMBER(8,7) := 3.1415926; area NUMBER(14,2); cursor rad_cursor IS SELECT * FROM Rad_Vals; rad_value rad_cursor%ROWTYPE; BEGIN open rad_cursor; fetch rad_cursorinto rad_val; area:=pi*power(rad_val.radius,2); insert into AREAS values (rad_val.radius, area); close rad_cursor; END; / Rad_Vals Rad_cursor fetch Rad_val AREAS Radius Area 3 28.27

49. DECLARE … cursor rad_cursor is select * from RAD_VALS; rad_value rad_cursor%ROWTYPE; BEGIN open rad_cursor; fetch rad_cursor into rad_val; area:=pi*power(rad_val.radius,2); insert into AREAS values (rad_val.radius, area); … DECLARE … cursor rad_cursor is select* from RAD_VALS; rad_value RAD_VALS.radius%TYPE; BEGIN open rad_cursor; fetch rad_cursor into rad_val; area:=pi*power(rad_val,2); insert into AREAS values (rad_val, area); … 1 4 2 3 DECLARE … cursor rad_cursor is select * from RAD_VALS; rad_valueRAD_VALS%ROWTYPE; BEGIN open rad_cursor; fetch rad_cursor into rad_val; area:=pi*power(rad_val.radius,2); insert into AREAS values (rad_val.radius, area); … DECLARE … cursor rad_cursor is selectradiusfrom RAD_VALS; rad_value RAD_VALS.radius%TYPE; BEGIN open rad_cursor; fetch rad_cursor into rad_val; area:=pi*power(rad_val,2); insert into AREAS values (rad_val, area); …

50. DECLARE oldest_birth_date DATE; lname Employee.last_name%TYPE; fname Employee.first_name%TYPE; CURSOR find_old_b_day IS SELECT min(birth_date) FROM Employee; CURSOR id_employee IS SELECT last_name, first_name FROM Employee WHERE birth_date = oldest_birth_date; BEGIN OPEN find_old_b_day; FETCH find_old_b_day INTO oldest_birth_date; CLOSE find_old_b_day; OPEN id_employee; FETCH id_employee into lname, fname; CLOSE id_employee; DBMS_OUTPUT.PUT_LINE(‘The oldest employee is’ || lname || ‘, ‘ || fname); END / The oldest employee is Johnson, Andrew