1 / 73

Introduction to PL/SQL

Introduction to PL/SQL. Part I. 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. SQL Vs. PL/SQL. SQL No Procedural Capability Time Consuming Processing

Download Presentation

Introduction to PL/SQL

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

More Related