PL SQL Block Structures

1. PL SQL Block Structures

2. What is PL SQL • A good way to get acquainted with PL/SQL is to look at a sample program. • PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages.

3. Block Structure • PL/SQL is a block-structured language. • The basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested sub-blocks.

4. Logical Blocks • Each logical block corresponds to a problem or subproblem to be solved. • A block (or sub-block) lets you group logically related declarations and statements. • The declarations are local to the block and cease to exist when the block completes.

5. Declarative • First comes the declarative part, in which items can be declared. • Once declared, items can be manipulated in the executable part.

6. Nesting • You can nest sub-blocks in the executable and exception-handling parts of a PL/SQL block or subprogram but NOT in the declarative part.

7. Image

8. Variables and Constants • PL/SQL lets you declare constants and variables, then use them in SQL and procedural statements anywhere an expression can be used.

9. SQL Datatypes • Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or any PL/SQL datatype, such as BOOLEAN or BINARY_INTEGER

10. Assigning Values • You can assign values to a variable in three ways. • The first way uses the assignment operator (:=), a colon followed by an equal sign. • Examples: tax := price * tax_rate; valid_id := FALSE;

11. 2nd Way to Assign Values • The second way to assign values to a variable is by selecting (or fetching) database values into it. • Example: (Compute a 10% bonus when you select the salary of an employee. ) SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;

12. 3rd Way to Assign a Value • The third way to assign values to a variable is by passing it as an OUT or IN OUT parameter to a subprogram. • Example: 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

13. Declaring Constants • Declaring a constant is like declaring a variable except that you must add the keyword CONSTANT and immediately assign a value to the constant. Thereafter, no more assignments to the constant are allowed. Example: credit_limit CONSTANT REAL := 5000.00;

14. Cursors • Oracle uses work areas to execute SQL statements and store processing information.

15. 2 types of Cursors • A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors: implicit and explicit.

16. Implicit Cursor • PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row.

17. Explicit Cursor • For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. • Example: DECLARE CURSOR c1 IS SELECT empno, ename, job FROM emp WHERE deptno = 20;

18. Cursor For Loops • A cursor FOR loop implicitly declares its loop index as a record that represents a row fetched from the database. DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, deptno FROM emp;

19. Fetch • Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record. BEGIN FOR emp_rec IN c1 LOOP ... salary_total := salary_total + emp_rec.sal;

20. Closes Loop • Then closes the cursor when all rows have been processed. END LOOP;

21. Full Example of Cursor For Loop DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, deptno FROM emp; ... BEGIN FOR emp_rec IN c1 LOOP ... salary_total := salary_total + emp_rec.sal; END LOOP;

22. Cursor Variable • A cursor variable points to the current row in the result set of a multi-row query. • Unlike a cursor, a cursor variable can be opened for any type-compatible query. It is not tied to a specific query. • Cursor variables are true PL/SQL variables, to which you can assign new values and which you can pass to subprograms stored in an Oracle database.

23. Example for Cursor Variable • The following procedure opens the cursor variable generic_cv for the chosen query: PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice NUMBER) IS BEGIN IF choice = 1 THEN OPEN generic_cv FOR SELECT * FROM emp; ELSIF choice = 2 THEN OPEN generic_cv FOR SELECT * FROM dept; ELSIF choice = 3 THEN OPEN generic_cv FOR SELECT * FROM salgrade; END IF; ... END;

24. The program below processes an order for a tennis racket.

25. DECLARE • First, it declares a variable of type NUMBER to store the quantity of tennis rackets on hand. DECLARE qty_on_hand NUMBER(5);

26. Then, it retrieves the quantity on hand from a database table named inventory. BEGIN SELECT quantity INTO qty_on_hand FROM inventory WHERE product = 'TENNIS RACKET' FOR UPDATE OF quantity;

27. (condition) IF THEN • If the quantity is greater than zero, the program updates the table and inserts a purchase record into another table named purchase_record. IF qty_on_hand > 0 THEN -- check quantity UPDATE inventory SET quantity = quantity – 1 WHERE product = 'TENNIS RACKET'; INSERT INTO purchase_record VALUES ('Tennis racket purchased', SYSDATE);

28. ELSE • Otherwise, the program inserts an out-of-stock record into the purchase_record table. ELSE INSERT INTO purchase_record VALUES ('Out of tennis rackets', SYSDATE); END IF; COMMIT; END;