Oracle PLSQL Online Training

1. PL/sql ONLINE TRAINING Email Id : trainings@cheyat.com Contact IND : +91-91009-98433/34(WAPP) USA : +1(224) 676-9882 www.cheyat.com

2. COURSE CONTENT Introduction to Oracle Database Introduction to Oracle Database Retrieve Data using the SQL SELECT Statement Learn to Restrict and Sort Data Usage of Single-Row Functions to Customize Output Invoke Conversion Functions and Conditional Expressions Aggregate Data Using the Group Functions Display Data From Multiple Tables Using Joins Use Sub-queries to Solve Queries The SET Operators Data Manipulation Statements Use of DDL Statements to Create and Manage Tables Control User Access Management of Schema Objects Retrieve Data Using Sub-queries OLAP Quieres Pseudo Columna Hierarchical Queries

3. COURSE CONTENT • Introduction to PL/SQL • PL/SQL Identifiers • Write Executable Statements • Interaction with the Oracle Server • Control Structures • Write Executable Statements • Composite Data Types • Explicit Cursors • Exception Handling • Stored Procedures and Functions • Create Stored Procedures • Create Stored Functions • Create Packages • Implement Oracle-Supplied Packages in Application Development • Dynamic SQL • Design Considerations for PL/SQL Code • Describe Triggers • Create Compound, DDL, and Event Database Triggers

4. WHAT IS PL/SQL • Stands for Procedural Languages extension to SQl • Is Oracle Corporations’s standard data access language for relational databases. • Seamlessly integrates Procedural constructs with QL

5. ABOUT PL/SQL • Provides a block structure for executable units of code. • Maintenance of code is made easier with such a well-defined structure • • Provides procedural constructs such as: • – Variables, constants, and types • – Control structures such as conditional statements and loops • – Reusable program units that are written once and executed many times

6. BENEFITS OF PL/SQL Benefits of PL/SQL – It is portable. – You can declare identifiers . – You can program with procedural language control structures. – It can handle errors.

7. PL/SQL Block Structure • DECLARE (optional) – Variables, cursors, user-defined exceptions • BEGIN (mandatory) – SQL statements – PL/SQL statements • EXCEPTION (optional) – Actions to perform when errors occur • END; (mandatory)

8. Identifiers Identifiers are used for: • Naming a variable • Providing conventions for variable names – Must start with a letter – Can include letters or numbers – Can include special characters (such as dollar sign, underscore, and pound sign) – Must limit the length to 30 characters – Must not be reserved words

9. Use of Variables Variables can be used for: • Temporary storage of data • Manipulation of stored values • Reusability

10. Types of Variables • PL/SQL variables: – Scalar – Composite – Reference – Large object (LOB) • Non-PL/SQL variables: Bind variables

11. Handling Variables in PL/SQL Variables are: • Declared and initialized in the declarative section • Used and assigned new values in the executable section • Passed as parameters to PL/SQL subprograms • Used to hold the output of a PL/SQL subprogram

12. Functions A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value

13. Function example • Create function funname • Return number is • a number(10); • Begin • Select avg(sal) into a from emp; • return a; • End; • / 

14. CREATETRIGGERS • A Trigger is a PL/SQL block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed. 

15. Insert Triggers: BEFORE INSERT Trigger AFTER INSERT Trigger • Update Triggers: BEFORE UPDATE Trigger AFTER UPDATE Trigger • Delete Triggers: BEFORE DELETE Trigger AFTER DELETE Trigger • Drop Triggers: Drop a Trigger • Disable/Enable Triggers: Disable a Trigger Disable all Triggers on a table Enable a Trigger Enable all Triggers on a table

16. CURSORS A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. • Cursors provide a way for your program to select multiple rows of data from the database and then to process each row individually. • There are two types of cursors in PL/SQL: • 1)IMPLICIT CURSORS • 2) Explicit cursors

17. Implicit Cursors Implicit Cursors • These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. • The set of rows returned by query is called active set. • Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The are as follows 1) %FOUND 2) %NOTFOUND 3) %ROWCOUNT 4) %ISOPEN. 61

18. FOR E.G. DECLARE • n number(5); • BEGIN • UPDATE emp SET salary = salary + 1000; • IF SQL%NOTFOUND THEN • dbms_output.put_line('No sal are updated'); • ELSIF SQL%FOUND THEN • n := SQL%ROWCOUNT; • dbms_output.put_line('Sal for ' ||n || 'employees are updated'); • END IF; • END; • /

19. EXPLANATION  • %FOUND->The return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT ….INTO statement return at least one row. • %NOTFOUND->same as above but if not found. • %ROWCOUNT ->Return the number of rows affected by the DML operations 63

20. Explicit Cursors • Explicit cursors are declared by and used by user to process multiple rows ,returned by select statement. • An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. We can provide a suitable name for the cursor.

21. Explicit cursor management • 1)Declare the cursor • 2)Open the cursor • 3)Fetch data from cursor • 4)Close the cursor

22. Declaring the Cursor •CURSOR cursor_name IS select_statement; • For e.g. • Cursor cursor_name is • Select name from emp where dept=‘maths’

23. Opening the Cursor • Open cursor_name • For e.g. • Open c_name • Where c_name is the name of cursor. • Open statement retrieves the records from db and places in the cursor(private sql area).

24. Fetching data from cursor • The fetch statement retrieves the rows from the active set one row at a time. The fetch statement is used usually used in conjunction with iterative process (looping statements) • Syntax: FETCH cursor-name INTO record-list • Ex: LOOP • ----------- • ------------ • FETCH c_name INTO name; • ----------- • END LOOP;

25. Closing cursor Closing a cursor: • Closing statement closes/deactivates/disables the previously opened cursor and makes the active set undefined. • Syntax : CLOSE cursor_name 69 • 70. Cursor can store multiple rows at a time but without loop cursor cannot fetch multiple rows but only print very first row from your result e.g. on next slide

26. PACKAGES • A Package is a container that may have many functions and procedures within it. • A PACKAGE is a group of programmatic constructs combined. • A package is a schema object that groups logically related PL/SQL types, items, and subprograms

27. THANK YOU Contact IND : +91-91009-98433/34 (WAPP) USA : +1(224) 676-9882 Email ID : trainings@cheyat.com Website : http://cheyat.com/ Skype ID: cheyatTraining