PL/SQL

1. PL/SQL PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages like procedures, loops, variables, objects etc. Resulting a structural language that is more powerful than SQL.

2. Why PL/SQL? In case of SQL to send 3 queries we will need three network trips between client and server. In PL-SQL we bundle any number of queries in a block and in single network trip task is done. Server Server SQL Query1 SQL Query2 SQL Query3 SQL Query1 SQL Query2 SQL Query3 PL-SQL Block Client Client

3. PL/SQL ADVANTAGES Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.  Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops). Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic. Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

4. PL/SQL BLOCK STRUCTURE • PL/SQL blocks contain three sections • Declare section • Executable section and • Exception-handling section. • The executable section is the only mandatory section of the block. • Both the declaration and exception-handling sections are optional.

5. PL/SQL BLOCK STRUCTURE PL/SQL block has the following structure: DECLARE Declaration statements BEGIN Executable statements EXCEPTION Exception-handling statements END ;

6. PL/SQL Variables • Variables are local to the code block • Names can be up to 30 characters long and must begin with a character • Declaration is like that in a table • Name then data type then semi-colon • Can be initialized using := operator in the declaration • Can be changed with := in the begin section • Can use constraints • Variables can be composite or collection types • Multiple values of different or same type

7. PL/SQL Variables The syntax for declaring a variable is: variable_namedatatype [NOT NULL := value ]; e.g. sales number(10, 2); name varchar2(25); address varchar2(100); Variable Scope in PL/SQL Local variables - variables declared in an inner block and not accessible to outer blocks. Global variables - variables declared in the outermost block or a package.

8. Common PL/SQL Data Types • CHAR ( max_length ) • VARCHAR2 ( max_length ) • NUMBER ( precision, scale ) • BINARY_INTEGER – more efficient than number • RAW ( max_length ) • DATE • BOOLEAN (true, false, null) • Also LONG, LONG RAW and LOB types but the capacity is usually less in PL/SQL than SQL

9. PL/SQL Control Structures • PL/SQL has a number of control structures which includes: • Conditional controls. •  Iterative or loop controls. • Exception or error controls • It is these controls, used singly or together, that allow the PL/SQL developer to direct the flow of execution through the program

10. PL/SQL Control Structures IF-THEN Statement IF condition THEN sequence_of_statements END IF; Example IF x > y THEN high := x; END IF;

11. IF-THEN-ELSE Statement IF condition THEN sequence_of_statements1 ELSE sequence_of_statements2 END IF; Example IF trans_type = 'CR‘ THEN UPDATE accounts SET balance = balance + credit WHERE ... ELSE UPDATE accounts SET balance = balance – debit WHERE ... END IF

12. IF-THEN-ELSIF Statement IF condition1 THEN sequence_of_statements1 ELSIF condition2 THEN sequence_of_statements2 ELSE sequence_of_statements3 END IF; Example 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;

13. Control Structures: Loops Loop statements executes a sequence of statement multiple times. Example: Declare credit_rating number := 0; Begin LOOP credit_rating := credit_rating +1; IF credit_rating < 3 THEN . EXIT; -- exit loop immediately END IF; END LOOP; -- control resumes here IF credit_rating >3 THEN . return; END IF; END LOOP; End; /

14. WHILE-LOOP WHILE condition LOOP sequence_of_statements END LOOP; Example WHILE total <= 25000 LOOP ... SELECT sal INTO salary FROM emp WHERE ... total := total + salary; END LOOP;

15. FOR LOOP FOR counter IN [REVERSE] lower_bound..higher_bound LOOP sequence_of_statements END LOOP; Example cnt_employee NUMBER; BEGIN SELECT COUNT(*) INTO cnt_employee FROM employee; FOR v_counter IN 1..cnt_employee LOOP DBMS_OUTPUT.PUT_LINE('v_counter = ' || v_counter); END LOOP; END; /

16. PL-SQL Transactions • Oracle transactions provide a data integrity. PL/SQL transaction is a series of SQL data manipulation statements that are work logical unit. Transaction is an atomic unit all changes either committed or rollback. • At the end of the transaction that makes database changes, Oracle makes all the changes permanent save or may be undone. If your program fails in the middle of a transaction, Oracle detect the error and rollback the transaction and restoring the database. • You can use the COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION command to control the transaction.

17. PL-SQL Transactions • COMMIT: COMMIT command to make changes permanent save to a database during the current transaction. • ROLLBACK: ROLLBACK command execute at the end of current transaction and undo/undone any changes made since the begin transaction. • SAVEPOINT : SAVEPOINT command save the current point with the unique name in the processing of a transaction. • Set AUTOCOMMIT: Set AUTOCOMMIT ON to execute COMMIT Statement automatically. • SET TRANSACTION : PL/SQL SET TRANSACTION command set the transaction properties such as read-write/read only access.

18. SAVEPOINT • SQLSAVEPOINT SP1; • Savepoint created. • SQL> DELETE FROM CUSTOMERS WHERE ID=1; • 1 row deleted. • SQL> SAVEPOINT SP2; • Save>point created. • SQL> DELETE FROM CUSTOMERS WHERE ID=2; • 1 row deleted. • SQL> SAVEPOINT SP3; • Savepoint created. • SQL> DELETE FROM CUSTOMERS WHERE ID=3; • 1 row deleted.

19. SAVEPOINT SQL> ROLLBACK TO SP2; Rollback complete. SQL> SELECT * FROM CUSTOMERS; RELEASE SAVEPOINT Command: The release savepoint command is used to remove a savepoint that you have created. The syntax for release savepoint is as follows: RELEASE SAVEPOINT SAVEPOINT_NAME;

20. Cursors • Oracle creates a memory area, known as context area, for processing an SQL statement, which contains all information needed for processing the statement, for example, number of rows processed, etc. • A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set. • There are two types of cursors: • Implicit cursors • Explicit cursors

21. IMPLISIT CURSOR ATTRIBUTE

22. IMPLICIT CURSOR EXAMPLE DECLARE total_rows number(2); BEGIN UPDATE customers SET salary = salary + 500; IF sql%notfound THEN dbms_output.put_line('no customers selected'); ELSIF sql%found THEN total_rows := sql%rowcount; dbms_output.put_line( total_rows || ' customers selected '); END IF; END; /

23. Explicit Cursors The advantages of declaring an explicit cursor over the indirect implicit cursor are that the explicit cursor gives more programmatic control to the programmer. Implicit cursors are less efficient than explicit cursors and thus it is harder to trap data errors. The process of working with an explicit cursor consists of the following steps: 1. DECLARING the cursor. This initializes the cursor into memory. 2. OPENING the cursor. The previously declared cursor can now be opened; memory is allotted. 3. FETCHING the cursor. The previously declared and opened cursor can now retrieve data; this is the process of fetching the cursor. 4. CLOSING the cursor. The previously declared, opened, and fetched cursor must now be closed to release memory allocation.

24. EXPLICIT CURSOR EXAMPLE DECLARE c_id customers.id%type; c_name customers.name%type; c_addr customers.address%type; CURSOR c_customers IS SELECT id, name, address FROM customers; BEGIN OPEN c_customers; LOOP FETCH c_customers into c_id, c_name, c_addr; EXIT WHEN c_customers%notfound; dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); END LOOP; CLOSE c_customers; END; /

25. PROCEDURE • A stored procedure or in simple a procedure is a named PL/SQL block which performs one or more specific task. This is similar to a procedure in other programming languages. • A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. • The body consists of declaration section, execution section and exception section similar to a general PL/SQL Block. • We can pass parameters to procedures in three ways.1) IN-parameters2) OUT-parameters3) IN OUT-parameters • A procedure may or may not return any value.

26. PROCEDURES A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows: CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS} BEGIN < procedure_body > END procedure_name;

27. PROCEDURE EXAMPLE DECLARE a number; b number; c number; PROCEDURE findMin(x IN number, y IN number, z OUT number) IS BEGIN IF x < y THEN z:= x; ELSE z:= y; END IF; END; BEGIN a:= 23; b:= 45; findMin(a, b, c); dbms_output.put_line(' Minimum of (23, 45) : ' || c); END; /

28. FUNCTIONS • PL/SQL function block create using CREATE FUNCTION statement. The major difference between PL/SQL function and procedure, function return always value where as procedure may or may not return value. • When you create a function or procedure, you have to define IN/OUT/INOUT parameters. • IN : IN parameter referring to the procedure or function and allow to overwritten the value of parameter. • OUT : OUT parameter referring to the procedure or function and allow to overwritten the value of parameter. • IN OUT : Both IN OUT parameter referring to the procedure or function to pass both IN OUT parameter, modify/update by the function or procedure and also get returned. • IN/OUT/INOUT parameters you define in function argument list.

29. FUNCTION EXAMPLE DECLARE a number; b number; c number; FUNCTION findMax(x IN number, y IN number) RETURN number IS z number; BEGIN IF x > y THEN z:= x; ELSE z:= y; END IF; RETURN z; END; BEGIN a:= 23; b:= 45; c := findMax(a, b); dbms_output.put_line(' Maximum of (23,45): ' || c); END; /

30. PACKAGES • PL/SQL Packages is schema object and collection of related data type (variables, constants), cursors, procedures, functions are defining within a single context. • Package are devided into two part, • Package Specification • Package Body

31. Package Specification • It just DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. In other words, it contains all information about the content of the package, but excludes the code for the subprograms. CREATE PACKAGE cust_sal AS PROCEDURE find_sal(c_idcustomers.id%type); END cust_sal; /

32. Package Body • The package body has the codes for various methods declared in the package specification and other private declarations, which are hidden from code outside the package. CREATE OR REPLACE PACKAGE BODY cust_sal AS PROCEDURE find_sal(c_idcustomers.id%TYPE) IS c_salcustomers.salary%TYPE; BEGIN SELECT salary INTO c_sal FROM customers WHERE id = c_id; dbms_output.put_line('Salary: '|| c_sal); END find_sal; END cust_sal; /

33. PACKAGE ADVANTAGES • You can create package to store all related functions and procedures are grouped together into single unit called packages. • Package are reliable to granting a privileges. • All function and procedure within a package can share variable among them. • Package are support overloading to overload functions and procedures. • Package are improve the performance to loading the multiple object into memory at once, therefore, subsequent calls to related program doesn't required to calling physically I/O. • Package are reduce the traffic because all block execute all at once.

34. Triggers • Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events: • A database manipulation (DML) statement (DELETE, INSERT, or UPDATE). • A database definition (DDL) statement (CREATE, ALTER, or DROP). • A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN). • Triggers could be defined on the table, view, schema, or database with which the event is associated.

35. Syntax for creating Trigger CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END;

36. Trigger: Example CREATE OR REPLACE TRIGGER display_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON customers FOR EACH ROW WHEN (NEW.ID > 0) DECLARE sal_diff number; BEGIN sal_diff := :NEW.salary - :OLD.salary; dbms_output.put_line('Old salary: ' || :OLD.salary); dbms_output.put_line('New salary: ' || :NEW.salary); dbms_output.put_line('Salary difference: ' || sal_diff); END; /

37. Embedded SQL • Combine the power of both SQL & a general purpose programming language. • Use (embedded) SQL to perform data retrieval and updates. • Use the general purpose programming language (host PL) to perform more complex data processing and to provide a friendly user interface.

38. Embedded SQL • Most SQL statements can be embedded in a general-purpose host programming language such as COBOL, C, Java • An embedded SQL statement is distinguished from the host language statements by enclosing it between EXEC SQL or EXEC SQL BEGIN and a matching END-EXEC or EXEC SQL END (or semicolon) • Syntax may vary with language • Shared variables (used in both languages) usually prefixed with a colon (:) in SQL

39. Dynamic SQL • Objective: • Composing and executing new (not previously compiled) SQL statements at run-time • a program accepts SQL statements from the keyboard at run-time • a point-and-click operation translates to certain SQL query • Dynamic update is relatively simple; dynamic query can be complex • because the type and number of retrieved attributes are unknown at compile time

40. ODBC • The Open Database Connectivity (ODBC) standard provides a DBMS-independent means for processing relational database data. • The goal is to allow a developer to create a single application that can access databases supported by different DBMS products without needing to be changed or recompiled.

41. ODBC Architecture

42. ODBC Components • OBDC consists of a data source, an application program, a driver manager, and a DBMS driver. • A data source is the database and its associated DBMS, operating system, and network platform. • An ODBC data source can be a relational database, a file server, or a spreadsheet. • An applications program issues requests to create a connection with a data source.

43. ODBC Components • A driver manager determines the type of DBMS for a given ODBC data source and loads that driver in memory. • A DBMS driver processes ODBC requests and submits specific SQL statements to a given type of data source. • A single-tier driver processes both ODBC calls and SQL statements. • A multiple-tier driver processes ODBC calls, but passes the SQL requests to the database server.