PL/SQL

1. PL/SQL

2. PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages. PL/SQL bridges the gap between database technology and procedural programming languages. With PL/SQL, you can use SQL statements to manipulate Oracle data and flow-of-control statements to process the data. You can also declare constants and variables, define procedures and functions, and trap runtime errors. Introduction

3. PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages: Support for SQL Support for object-oriented programming Better performance Higher productivity Full portability Tight integration with Oracle Tight security Advantages of PL/SQL

4. PL/SQL is a block-structured language. blocks are basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program . Can contain any number of nested sub-blocks. A block (or sub-block) lets you group logically related declarations and statements. you can place declarations close to where they are used. The declarations are local to the block and cease to exist when the block completes. What is Block Structure?

5. PL/SQL block has three parts: a declarative part executable part exception-handling part. Only the executable part is required. The order of the parts is logical. First comes the declarative part, in which items can be declared. Once declared, items can be manipulated in the executable part. Exceptions raised during execution can be dealt with in the exception-handling part. Components of PL/SQL Block

6. Declaration Part Example: declare a variable of type NUMBER to store the quantity of tennis rocket in stock. Execution Part Example: retrieves the quantity of items on hand from a database table named inventory. If the quantity is greater than zero updates the table inserts a purchase record into another table named purchase_record. Otherwise, inserts an out-of-stock record into the purchase_record table. Building Blocks of PL/SQL

7. DECLARE qty_on_hand NUMBER(5); BEGIN SELECT quantity INTO qty_on_hand FROM inventory WHERE product = 'TENNIS RACKET‘; IF qty_on_hand > 0 THEN UPDATE inventory SET quantity = quantity - 1 WHERE product = 'TENNIS RACKET'; INSERT INTO purchase_record VALUES ('Tennis racket purchased', SYSDATE); ELSE INSERT INTO purchase_record VALUES ('Out of tennis rackets', SYSDATE); END IF; COMMIT; END; Overview of PL/SQL

8. PL/SQL lets you declare constants and variables, then use them in SQL and procedural statements anywhere an expression can be used. However, forward references are not allowed. So, you must declare a constant or variable before referencing it in other statements, including other declarative statements. Variables and Constants

9. Variables can have any SQL datatype, such as CHAR DATE NUMBER Or any PL/SQL datatype, such as: BOOLEAN BINARY_INTEGER … Example part_no NUMBER(4); in_stock BOOLEAN; Declaring Variables

10. Some examples of identifiers follow: X t2 phone# credit_limit LastName oracle$number Examples of Identifiers

11. Numeric Literals: 34569 -89 Character Literals : 'Z' '%' '7' 'z' '(' String Literals 'Hello, world!‘ 'XYZ Corporation' All string literals except the null string ('') have datatype CHAR. Boolean Literals TRUE, FALSE, and NULL (which stands for a missing, unknown, or inapplicable value). Boolean literals are values, not strings. Literals

12. Datetime Literals '1998-12-25‘ '1997-10-22 13:01:01' Literals

13. Single-Line Comments -- begin processing Multi-line Comments Multi-line comments begin with a slash-asterisk (/*), end with an asterisk-slash (*/), and can span multiple lines. Comments

14. An identifier is visible only in the regions from which you can reference the identifier. Identifiers declared in a PL/SQL block are considered local to that block and global to all its sub-blocks. Scope and Visibility

15. One way to assign values to variables is to use the assignment operator (:=): Examples tax := price * tax_rate; valid_id := FALSE; bonus := current_salary * 0.10; wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions; Assigning Values

16. Another way is by fetching database values into it. Example: compute a 10% bonus when you select the salary of an employee and store the value into the the variable bonus. SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id; Assigning Values

17. The third way is by passing it as an OUT or INOUT parameter to a subprogram. Example of INOUT parameter: DECLARE my_sal REAL(7,2); PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ... BEGIN SELECT AVG(sal) INTO my_sal FROM emp; adjust_salary(7788, my_sal); -- assigns a new value to my_sal … … … Assigning Values

18. You must add the keyword CONSTANT and assign a value to the constant. In the following example, you declare a constant named credit_limit: credit_limit CONSTANT REAL := 5000.00; Declaring Constants

19. You can use the keyword DEFAULT instead of the assignment operator to initialize variables. blood_type CHAR := 'O'; can be rewritten as follows: blood_type CHAR DEFAULT 'O'; Use DEFAULT for variables that have a typical value. You can also use DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record. Using DEFAULT

20. You can declare a variable as NOTNULL: acct_id INTEGER(4) NOT NULL := 9999; The NOTNULL constraint must be followed by an initialization clause: Using NOT NULL

21. Arithmetic + - / * ** (exponent) Comparison =, <, >, <+, >=, <>, !=, IS NULL, LIKE, BETWEEN IN Logical: AND, OR, NOT Other: || (concatenation), Operators

22. Predefined Data Types User-Defined Data Types Predefined Data types A scalar type NUMBER, CHAR, VARCHAR2, NCHAR, NVARCHAR2, … A composite type RECORD A reference type REF PL/SQL Data types

23. A percent sign that precedes a table attribute serves as a type identifier. In other words, the %TYPE provides the datatype of a database column. Example: Consider column named title in a table named books. To declare a variable named my_title that has the same datatype as column title, use: my_title books.title%TYPE; %TYPE

24. Declaring my_title with %TYPE has two advantages. You need not know the exact datatype of title. If you change the database definition of title the datatype of my_title changes accordingly at run time. %TYPE

25. IF clause defines what to do if the conditions is TRUE the ELSE clause defines what to do if the condition is false or null. IF condition1 THEN sequence_of_statements1 ELSIF condition2 THEN sequence_of_statements2 ELSE sequence_of_statements3 END IF; IF - ELSIF - ELSE

26. BEGIN IF sales > 50000 THEN bonus := 1500; ELSIF sales > 35000 THEN bonus := 500; ELSE bonus := 100; END IF; INSERT INTO payroll VALUES (emp_id, bonus, ...); END; IF – ELSIF - ELSE

27. DECLARE acct_balance NUMBER(11,2); acct CONSTANT NUMBER(4) := 3; debit_amt CONSTANT NUMBER(5,2) := 500.00; BEGIN SELECT bal INTO acct_balance FROM accounts WHERE account_id = acct FOR UPDATE OF bal; IF acct_balance >= debit_amt THEN UPDATE accounts SET bal = bal - debit_amt WHERE account_id = acct; ELSE INSERT INTO temp VALUES (acct, acct_balance, 'Insufficient funds'); END IF; COMMIT; END;  IF-THEN-ELSE

28. CASE grade WHEN 'A' THEN dbms_output.put_line('Excellent'); WHEN 'B' THEN dbms_output.put_line('Very Good'); WHEN 'C' THEN dbms_output.put_line('Good'); WHEN 'D' THEN dbms_output.put_line('Fair'); WHEN 'F' THEN dbms_output.put_line('Poor'); ELSE dbms_output.put_line('No such grade'); END CASE; CASE Statement

29. Using the following syntax, the CASE statement can evaluate a condition and returns a value for each case: CASE WHEN shape = 'square' THEN area := side * side; WHEN shape = 'circle' THEN BEGIN area := pi * (radius * radius); DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is irrational.'); END; WHEN shape = 'rectangle' THEN area := length * width; ELSE BEGIN DBMS_OUTPUT.PUT_LINE('No formula to calculate area of a' || shape); RAISE PROGRAM_ERROR; END; END CASE; Case Statement

30. LOOP statements let you execute a sequence of statements multiple times. You place the keyword LOOP before the first statement in the sequence and the keywords ENDLOOP after the last statement in the sequence. Example:  LOOP -- sequence of statements END LOOP; Iterative Control

31. LOOP ... IF credit_rating < 3 THEN EXIT; -- exit loop immediately END IF; … END LOOP; SIMPLE LOOP

32. LOOP ... EXIT WHEN credit_rating < 3 ; END LOOP; USING EXIT - WHEN

33. WHILE condition LOOP sequence_of_statements END LOOP; Example: Declare counter number (5):=0; Begin WHILE counter < 3 LOOP dbms_output.put_line(counter); counter:=counter+1; END LOOP; END; WHILE-LOOP

34. The FOR-LOOP statement lets you specify a range of integers, then execute a sequence of statements once for each integer in the range. For example, the following loop inserts 500 numbers and their square roots into a database table: FOR num IN 1..500 LOOP INSERT INTO roots VALUES (num, SQRT(num)); END LOOP;  FOR LOOP

35. GOTO transfers the control to a statement preceded by a label, unconditionally. IF rating > 90 THEN GOTO calc_raise; END IF; ... <<calc_raise>> IF job_title = 'SALESMAN' THENZ amount := commission * 0.25; ELSE amount := salary * 0.10; END IF; Not allowed to branch into an if, loop, or an inner block. GOTO

36. PL/SQL has two types of subprograms procedures functions, Subprograms are named PL/SQL blocks that can take parameters and be invoked. Like unnamed or anonymous PL/SQL blocks, subprograms have a declarative part, an executable part, and an optional exception-handling part. Subprograms

37. [CREATE [OR REPLACE]] PROCEDURE procedure_name[(parameter[, parameter]...)] [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name]; Procedures

38. CREATE or REPLACE PROCEDURE valid_bonus(emp_id NUMBER) AS bonus NUMBER :=100; BEGIN SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id; IF bonus IS NULL THEN DBMS_OUTPUT.PUT_LINE('Bonus is NULL'); ELSE DBMS_OUTPUT.PUT_LINE('Bonus is: '|| bonus); END IF; END valid_bonus; / Example of a Procedure

39. CREATE OR REPLACE PROCEDURE raise_salary (emp_id emp.empno%TYPE, amount REAL) IS current_salary REAL; BEGIN CURRENT_SALARY := 1000; SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN DBMS_OUTPUT.PUT_LINE('SALARY IS NULL'); ELSE UPDATE emp SET sal = sal + amount WHERE empno = emp_id; END IF; END raise_salary; / Another Example

40. A procedure can be called within a PL/SQL blocks. For example, you might call the procedure raise_salary as follows: DECLARE X NUMBER := 7369; Y NUMBER:= 123; BEGIN … Raise_salary(X, Y); … END; / Procedures also can be called with an EXEC command, or as standalone statement within a block: EXEC raise_salary(7369, 123); Calling Procedures

41. PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS old_balance REAL; new_balance REAL; BEGIN SELECT bal INTO old_balance FROM accts WHERE acct_no = acct_id; new_balance := old_balance - amount; IF new_balance < 0 THEN EXIT; ELSE UPDATE accts SET bal = new_balance WHERE acct_no = acct_id; END IF; END debit_account; Another Example

42. A function is a subprogram that computes a value. Functions and procedures are similar, except that functions have a RETURN clause. [CREATE [OR REPLACE ] ] FUNCTION function_name [ ( parameter [ , parameter ]... ) ] RETURN datatype {IS | AS} [ local declarations ] BEGIN executable statements [ EXCEPTION exception handlers ] END [ name ]; / Functions

43. CREATE OR REPLACE FUNCTION sal_ok (salary REAL, title VARCHAR2) RETURN BOOLEAN IS min_sal REAL; max_sal REAL; BEGIN SELECT losal, hisal INTO min_sal, max_sal FROM sals WHERE job = title; RETURN (salary >= min_sal) AND (salary <= max_sal); END sal_ok; Example of a Function

44. A user defined function can be called in an SQL statements similar to built-in functions, or inside other expressions: SELECT sal_ok (sal) FROM emp WHERE empno = 7396; Calling Funtions

45. The three parameter modes, IN (the default), OUT, and IN OUT, can be used with anysubprogram. A void using the OUT and IN OUT modes with functions. The purpose of a function is to take zero or more arguments (actual parameters) and return a single value. Subprogram Parameter Modes

46. An IN parameter lets you pass values to the subprogram being called. An IN parameter acts like a constant. Example: A compilation error: PROCEDURE debit_account (acct_id IN INTEGER, amount IN REAL) IS BEGIN IF amount < 888 THEN amount := amount + 5; -- causes compilation error END IF; ... END debit_account; IN Mode

47. OUT parameter lets you return values to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like a variable: An OUT actual parameter can have a value before the subprogram is called. However, when you call the subprogram, the value is lost. The actual parameter of an out parameter cannot be a constant or an expression. The following statement fails: myfucntion(x + 2); OUT MODE

48. PROCEDURE calc_bonus (emp_id IN INTEGER, bonus OUT REAL) IS hire_date DATE; BEGIN SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp WHERE empno = emp_id; IF bonus IS NULL THEN Dbms_output.put_line(‘bonus is null’); END IF; IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN bonus := bonus + 500; END IF; … END calc_bonus; Out Mode - Example

49. An IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller. An IN OUT parameter acts like an initialized variable. It can be assigned a value andits value can be assigned to another variable. IN OUT Mode

50. Oracle cursors are work areas to allow to execute SQL statements and store processing information. There are two kinds of cursors: Implicit explicit PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. Cursors