Introduction to PL/SQL

1. Introduction to PL/SQL

2. What is PL/SQL? • Tight Integration with SQL • PL/SQL is tightly integrated with SQL, the most widely used database manipulation language.

3. What is PL/SQL? • PL/SQL lets you use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators, and pseudocolumns. • PL/SQL fully supports SQL data types.

4. What is PL/SQL? • High Performance • PL/SQL lets you send a block of statements to the database, significantly reducing traffic between the application and the database.

5. What is PL/SQL? • High Productivity • PL/SQL lets you write compact code for manipulating data. • Just as a scripting language, we can read, transform, and write data in files, PL/SQL can query, transform, and update data in a database.

6. What is PL/SQL? • Portability • You can run PL/SQL applications on any operating system and platform where Oracle Database runs.

7. What is PL/SQL? • Scalability • PL/SQL stored subprograms increase scalability by centralizing application processing on the database server. 

8. What is PL/SQL? • Manageability • PL/SQL stored subprograms increase manageability because you can maintain only one copy of a subprogram, on the database server, rather than one copy on each client system. • Any number of applications can use the subprograms, and you can change the subprograms without affecting the applications that invoke them

9. What is PL/SQL? • Support for Object-Oriented Programming • Support for Developing Web Applications • Support for Developing Server Pages

10. PL/SQL Advantage

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

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

13. PL/SQL Data Types • Scalar • Data type that store a single value • Data type that do not have any internal component • Composite • References a data structure • Which has internal components

14. PL/SQL Data Types • Reference • References a specific database item • These data types are also considered pointers. • LOB • References a large object

15. PL/SQL Data Types • Image Source: docs.oracle.com

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

17. Composite Data Types • Reference multiple data elements, such as a record • Types: • RECORD • TABLE • VARRAY • Tabular structure that can expand or contract as needed

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

19. Large Object (LOB) • Oracle provides the LOB data type for storing large data up to 4GB. • LOB can be used to store text, images, sound file, video file. • LOBs are either internal or external depending on their location with respect to the database.

20. Large Object (LOB) • Internal LOBs (BLOBs, CLOBs, and NCLOBs) • Internal LOBs are stored inside database tablespaces in a way that optimizes space and enables efficient access. 

21. Large Object (LOB) • BLOB: A LOB whose value is composed of unstructured binary (raw) data • CLOB: A LOB whose value is composed of character data that corresponds to the database character set defined for the Oracle database • NCLOB: A LOB whose value is composed of character data that corresponds to the national character set defined for the Oracle database (For supporting National Languages such as French, Italian, Hindi, etc….)

22. PL/SQL Program Structure DECLARE Variable declarations BEGIN Program statements EXCEPTION Error-handling statements END; Variable Declarations Body Exception Section

23. PL/SQL Program Lines declare a number(4); b number(4); BEGIN a:=4; b:=0; DBMS_OUTPUT.PUT_LINE(a+b); END;

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

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

26. Arithmetic Operators Example Result

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

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

29. Character String Functions • Concatenating strings: joining 2 or more character strings into a single string • Concatenation operator: || s_first_name := ‘BScIT’ s_last_name := ‘MScIT’ s_full_name := s_first_name || ‘ ’ || s_last_name

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

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

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

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

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

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

36. PL/SQL Comparison Operators

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

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

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

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

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

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

43. Using SQL Commands in PL/SQL Programs

44. The Client/Server Computing Model A Client/Server system has three distinct components, each focusing on a specific job. (1) Database server (2) Client application (3) Network

45. The Client/Server Computing Model Database servers tasks: (1) Managing a single database of information among many concurrent users. (2) Controlling database access and other security requirements. (Cntd….)

46. The Client/Server Computing Model Database servers tasks: (3) Protecting database information with backup and recovery features. (4) Centrally enforcing global data integrity rules across all client applications.

47. The Client/Server Computing Model Client Application tasks: • Presenting interface a user can interact with to accomplish work. • Managing presentation logic such as pop-up list on a data-entry form or bar graphs in a graphical data presentation tool. • Performing application logic, such as calculating field in a data entry form.

48. The Client/Server Computing Model Client Application tasks: • Validating data entry. • Requesting and receiving information from a database server.

49. The Client/Server Computing Model Network: The Network and Communication software are the vehicles that transmit data between the client and the server in a system. Both the clients and the server run communication software that allows them to talk across the network.

50. Transaction Control • A transaction is a series of SQL statements that either succeeds or fails as a unit. Transactions are a standard part of relational databases and prevent inconsistent data.