An introduction to using Oracle PL/SQL

1. An introduction to using Oracle PL/SQL Definition: PL/SQL is an Oracle’s procedural extension of SQL, it is an advanced fourth-generation programming language (4GL). .

2. Oracle PL/SQL Objective: PL/SQL offers modern features such as data encapsulation, overloading, collection types, exception handling, and information hiding. improve database performance, make your applications simpler and easier to maintain for an Oracle developer or DBA.

3. Oracle PL/SQL Advantages of Oracle PLSQL PL/SQL has over other programming languages its tight coupling with the Oracle database. This makes PL/SQL programs easier to write and very efficient at accessing the database. Reduced Network Traffic

4. Oracle PL/SQL Advantages of Oracle PLSQL PLSQL is also fully portable. You can use PL/SQL on any platform which runs Oracle and you can transfer PLSQL programs from one platform to another without having to recompile them.

5. Oracle PL/SQL Disadvantages of Oracle PLSQL Proprietary to Oracle PL/SQL is proprietary to Oracle (means if you change database then you would have to re-write all your Oracle PL/SQL programs or write your applications in a database-neutral language like Java, VB or C and use the APIs to access the database.

6. Oracle PL/SQL Disadvantages of Oracle PLSQL Poor I/O Features Oracle PL/SQL has very little support for i/o either to read/write files or to read from or write to a user interface. PLSQL is designed for manipulating information from the database and not for processing files or communicating with users.

7. PL/SQL Data Types Composite Types RECORD TABLE VARRAY LOB Types BFILE BLOB CLOB NCLOB Reference Types REF CURSOR REF object_type Numeric types • BINARY_INTEGER • DEC • DECIMAL • FLOAT • INT • INTEGER • NATURAL • NUMBER • NUMERIC • PLS_INTEGER • POSITIVE • REAL Character types • CHAR • CHARACTER • LONG • LONG RAW • NCHAR • NVARCHAR2 • RAW • ROWID • STRING • UROWID • VARCHAR • VARCHAR2

8. PL/SQL Data Types 1- Built-inDatatypes The built-in data types comprise integer, real (floating point), character, date/time and Boolean and support the manipulation of LOB (large object) data types. NUMBER(precision, scale) REAL FLOAT(binary precision).

9. PL/SQL Data Types Character types are available as:- fixed length (CHAR(length)) variable length (VARCHAR2(max length)) 2- User-Defined Subtypes The standard PL/SQL data types can be extended by defining your own types in the form of records and PLSQL collections.

10. Oracle PL/SQL Language Elements PL/SQL the language elements consist of:- 1- loops • numeric and cursor FOR loops (the latter act on the results of queries) • WHILE LOOPs • Simple LOOPs

11. Oracle PL/SQL Language Elements 2-Conditional and sequential control statements • IF THEN and IF THEN ELSE • GOTO (but the use of this is usually not necessary) • simple and searched CASE statements 3- Anonymous blocks 4- Named blocks • PROCEDURES • FUNCTIONS

12. Oracle PL/SQL block The general syntax is …

13. Oracle PL/SQL block The general syntax is ... DECLARATIONS BEGIN/* start of anonymous block or procedure/function */ < PL/SQL statements> EXCEPTION WHEN <exception> THEN <PL/SQL statements> ; END ;/*end of anonymous block or procedure/function */

14. Variables and Constants Examples of variable and constant declarations: credit_limit CONSTANT NUMBER := 5000; invalid BOOLEAN := FALSE; acct_id INTEGER(4) NOT NULL DEFAULT 9999; pi CONSTANT REAL := 3.14159; postal_code VARCHAR2(20); last_name VARCHAR2(20 CHAR); my_enameemp.ename%TYPE;

15. Variables and Constants %TYPE This attribute provides the datatype of a previously declared collection, cursor variable, field, object, record, database column, or variable. Example: DECLARE my_empnoemp.empno%TYPE; ... BEGIN my_empno := NULL;

16. Variables and Constants %ROWTYPE This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor. Fields in the record and corresponding columns in the row have the same names and data types.

17. Variables and Constants %ROWTYPE Example: DECLARE emp_recemp%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec c1%ROWTYPE; BEGIN SELECT * INTO emp_rec FROM emp WHERE ...

18. 1- Implicit 2- Explicit. Implicitly for all SQL data manipulation statements, including queries that return only one row. For queries that return more than one row, you must declare an explicit cursor, use a cursor FOR loop. PL/SQL uses two types of cursors

19. Cursor declarations • Cursor variable is used a to access the information after executing a multi-row query (Oracle opens an unnamed work area that stores processing information).

20. Cursor declarations Examples of cursor declarations : • CURSOR c1 IS SELECT empno, ename, job, sal FROM emp WHERE sal > 2000; • CURSOR c2 RETURN dept%ROWTYPE IS SELECT * FROM dept WHERE deptno = 10;

21. Every explicit cursor and cursor variable has four characteristics: %FOUND %ISOPEN %NOTFOUND %ROWCOUNT. Cursor

22. %ISOPEN • This is a cursor attribute that can be appended to the name of a cursor or cursor variable. • If a cursor is open, cursor_name%ISOPEN yields TRUE; otherwise, it yields FALSE.

23. %NOTFOUND This is a cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open cursor, cursor_name%NOTFOUND yields NULL. Thereafter, it yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.

24. %ROWCOUNT • This is a cursor attribute that can be appended to the name of a cursor or cursor variable. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far.

25. Example declare CURSOR my_cursor IS SELECT sal *12 anual , ename FROM emp; my_recmy_cursor%ROWTYPE; BEGIN OPEN my_cursor; LOOP FETCH my_cursor INTO my_rec; EXIT WHEN my_cursor%NOTFOUND; IF my_rec.anual > 2000 THEN INSERT INTO temp VALUES (NULL, my_rec.anual, my_rec.ename); END IF; END LOOP; CLOSE my_cursor; END;

26. Conditional Statements in PL/SQL • IF THEN ELSE STATEMENT IF condition THEN statement 1; ELSE statement 2; END IF;

27. IF THEN ELSIF ELSE STATEMENT • 2)IF condition 1 THEN  statement 1;  statement 2; ELSIF condtion2 THEN  statement 3; ELSE  statement 4; END IF

28. CASE statements • CASE • WHEN x = 1 THEN sequence_of_statements_1; • WHEN x = 2 THEN sequence_of_statements_2; • WHEN x = 3 THEN sequence_of_statements_3; • WHEN x = 4 THEN sequence_of_statements_4; • WHEN x = 5 THEN sequence_of_statements_5; • ELSEsequence_of_statements_N; • END CASE;

29. CASE statement can be used with predefined selector CASEx WHEN 1 THEN sequence_of_statements_1; WHEN 2 THEN sequence_of_statements_2; WHEN 3 THEN sequence_of_statements_3; WHEN 4 THEN sequence_of_statements_4; WHEN 5 THEN sequence_of_statements_5; ELSEsequence_of_statements_N; END CASE;

30. examples CASEWHEN a < b THEN 'hello' WHEN d < e THEN 'goodbye‘ END CASE WHENsupplier_name = 'IBM' andsupplier_type = 'Hardware' THEN 'North office‘ WHENsupplier_name = 'IBM' andsupplier_type = 'Software' THEN 'South office‘ END

31. PL/SQ Simple LOOP The simplest form of LOOP statement is the basic (or infinite) loop, with the keywords LOOP and END LOOP, as follows: LOOP sequence_of_statements END LOOP;

32. EXIT The EXIT statement forces a loop to complete unconditionally. An example follows: LOOP <statements> IF credit_rating < 3 THEN <statements> EXIT; -- exit loop immediately END IF; END LOOP; -- control resumes here

33. EXIT WHEN LOOP FETCH c1 INTO ... EXIT WHEN c1%NOTFOUND; -- exit loop if condition is true ... END LOOP; CLOSE c1;

34. PL/SQL WHILE-LOOP The WHILE-LOOP statement associates a condition with a sequence of statements enclosed by the keywords LOOP and END LOOP, as follows: WHILE condition LOOP sequence_of_statements END LOOP;

35. PL/SQL WHILE-LOOP An example WHILE total <= 25000 LOOP ... SELECT sal INTO salary FROM emp WHERE ... total := total + salary; END LOOP;

36. PL/SQL FOR-LOOP The number of iterations through a FOR loop is known before the loop is entered. FOR loops iterate over a specified range of integers. The syntax follows: FOR counter IN [REVERSE] lower_bound..higher_bound LOOP sequence_of_statements END LOOP;

37. Oracle PL/SQL for loop An example FOR i IN 1..3 LOOP -- assign the values 1,2,3 to i sequence_of_statements-- executes three times END LOOP; FOR i IN REVERSE 1..3 LOOP -- assign the values 3,2,1 to i sequence_of_statements-- executes three times END LOOP;

38. PL/SQ CURSOR FOR Loops The general structure is as follows:  • FOR <record> IN <cursor> LOOP<PL/SQL statements>END LOOP;

39. PL/SQ CURSOR FOR Loops Advantage Oracle automatically OPENs the cursor, the results are automatically FETCHed and when all rows have been returned, Oracle automatically CLOSEs the cursor for you.

40. PL/SQ CURSOR FOR Loops disadvantages If the query returns no data, then the body of the loop is not executed which causes an error. Processing is exactly the same for each row returned, which means you can't initialize variables.