DOC-20240916-WA0021(1).pdf

Advanced database Management System Unit III PL-SQL Introduction concept Pl/SQL stands for "Procedural Language extension of SQL" used in Oracle. PL/SQL is i...

Advanced database Management System Unit III PL-SQL Introduction concept Pl/SQL stands for "Procedural Language extension of SQL" used in Oracle. PL/SQL is integrated with Oracle database (since version 7). It is a block structure language. The programs of PL/SQL are logical blocks that can contain any number of nested sub- blocks. The functionalities of PL/SQL are usually extended after each release of Oracle database. Although PL/SQL is closely integrated with SQL language, it adds some programming constraints that are not available in SQL. PL/SQL includes procedural language elements like conditions and loops. It allows declaration of the constants and variables, procedures and functions, triggers etc. Advantages of PL/SQL PL/SQL is development tool that supports SQL data manipulation and conditional checking, branching and looping. It provides facility to deal with errors, as required and displays user-friendly messages when error occurs. Allows declaration and use of variables in blocks of code. These variables are used to store intermediate results of a query for later processing either in SQL or PL/SQL. Using PL/SQL, all sorts of calculations can be done quickly and efficiently without using oracle engine which improves transaction performance. Applications written in PL/SQL are portable to any computer hardware and operating system, where Oracle is operational.For example: PL/SQL code blocks written for a DOS version of Oracle will run on its Linux/ Unix version without any modifications. Disadvantages of SQL SQL does not provide the programming techniques of condition checking, looping and branching which is very important for data testing before its permanent storage. Pl/ SQL does this.  Advanced database Management System SQL statements are passed to the Oracle Engine one at a time. While executing SQL statement, each time a call is made to the engine's resources. This increase traffic in the network that decreases the speed of data processing especially in a multi-user environment. SQL has no facility of error handling. What is PL/SQL block? In PL/SQL, the code is not executed in single line format, but it is always executed by grouping the code into a single element called Blocks. In this tutorial, you are going to learn about these blocks. Blocks contain both PL/SQL as well as SQL instruction. All these instruction will be executed as a whole rather than executing a single instruction at a time. Block Structure PL/SQL blocks have a pre-defined structure in which the code is to be grouped. Below are different sections of PL/SQL blocks. Declaration section Execution section Exception-Handling section The below picture illustrates the different PL/SQL block and their section order.  Advanced database Management System Declaration Section This is the first section of the PL/SQL blocks. This section is an optional part. This is the section in which the declaration of variables, cursors, exceptions, subprograms, pragma instructions and collections that are needed in the block will be declared. Below are few more characteristics of this part. This particular section is optional and can be skipped if no declarations are needed. This should be the first section in a PL/SQL block, if present. This section starts with the keyword ‘DECLARE’ for triggers and anonymous block. For other subprograms, this keyword will not be present. Instead, the part after the subprogram name definition marks the declaration section. This section should always be followed by execution section. Execution Section Execution part is the main and mandatory part which actually executes the code that is written inside it. Since the PL/SQL expects the executable statements from this block this cannot be an empty block, i.e., it should have at least one valid executable code line in it. Below are few more characteristics of this part. This can contain both PL/SQL code and SQL code.  Advanced database Management System This can contain one or many blocks inside it as a nested block. This section starts with the keyword ‘BEGIN’. This section should be followed either by ‘END’ or Exception-Handling section (if present) Exception-Handling Section The exception is unavoidable in the program which occurs at run-time and to handle this Oracle has provided an Exception-handling section in blocks. This section can also contain PL/SQL statements. This is an optional section of the PL/SQL blocks. This is the section where the exception raised in the execution block is handled. This section is the last part of the PL/SQL block. Control from this section can never return to the execution block. This section starts with the keyword ‘EXCEPTION’. This section should always be followed by the keyword ‘END’. The Keyword ‘END’ marks the end of PL/SQL block. PL/SQL Block Syntax Below is the syntax of the PL/SQL block structure. DECLARE --optional BEGIN --mandatory EXCEPTION --optional  Advanced database Management System END; --mandatory / Variables in PL/SQL A variable is a meaningful name that provides facility for programmer to store data temporary during execution of code. It helps to manipulate data in PL/SQL. Each variable in the PL/SQL has a specific data type that defines the size and layout of the variable's memory. A variable should not be more than 30 characters and optionally followed by more letters like dollar signs, underscore etc. Syntax: variable_name[CONSTANT] datatype [NOT NULL] [:=default initial _value] Initializing Variables in PL/SQL On declaration PL/SQL defines a default value as NULL. If a user wishes to initialize a variable with a value other than NULL, he can do so by using one of the following methods. 1. DEFAULT keyword Example: Initialize variables using DEFAULT keyword. counterbinary_integer := 0; 2. Assignment operator  Advanced database Management System Example: Initialize variables using DEFAULT keyword. DECLARE Note: The backward slash '/' in the above program indicates to execute the above PL/SQL Block.Output: Value of c: 50 Scope of variable in PL/SQL PL/SQL allows nesting of blocks.There are two types of variable scope. 1. Local variable: Local variables are the inner variable and not accessible to outer block.2. Global variable: Global variable are declared in outermost box. DECLARE Output: Outer Variable num1: 45 Constants in PL/SQL A constant is a value used in a PL/SQL block which remains unchanged throughout the program. It can be declared and used instead of actual values.For example: Assume if programmer has to write a program which will increase salary of the employee upto 10%. So programmer can declare a constant and use it throughout the program. Syntax: Constant_name CONSTANT datatype := value;Lets take an example to understand the declaration and execution of constants. Example  Advanced database Management System DECLARE Circumference: ' || circumference); Output: Radius: 3.5 PL/SQL Control statements PL/SQL supports the conditional statements and iterative statements like other programming languages such as C++, java etc. PL/SQL IF Statements 1. IF-THEN Statement Syntax IF condition This syntax is used when user needs to execute statements when condition is true. 2. IF-THEN-ELSE Statement Syntax IF condition This syntax is used to execute one set of statements when condition is TRUE or different set of statements when condition is FALSE. 3. IF-THEN-ELSIF statement Syntax IF Condition1 This syntax is used to execute one set of statements when condition1 is TRUE or a different set of statements when condition is FALSE.  Advanced database Management System 4. IF-THEN-ELS-IF-ELSE Statement Syntax IF condition1 This syntax is used to execute one set of statements if condition1 is TRUE, a different set of statements when condition2 is TRUE or a third set of statements when both condition1 and condition2 are false.Lets take an example to understand the IF-THEN- ELSE statement. Example DECLARE Output: a is not less than 10 PL/SQL Case statement The PL/SQL CASE Statement provides facility to execute a sequence of statements based on a selector. A selector may be variable, function or an expression. Syntax CASE [expression] Example DECLARE OR grade char(1); grade: =&grade; BEGIN  Advanced database Management System Output: Second class PL/SQL Loop Loops are iterative control statements. They are used to repeat execution of one or more statements for defined number of times. Syntax LOOP The four types of loops are: 1. PL/SQL Exit Loop It is used a set of statements is executed at least once before termination of loop. There should be an EXIT condition in the loop, otherwise the loop will get into an infinite number of iterations. Syntax LOOP Lets take an example to understand Exit loop. Example DECLARE Output: 1 2. PL/SQL WHILE Loop It is used when a set of statements should be executed as long as condition is  Advanced database Management System true. The condition is decided at the beginning of each iteration and continues until condition becomes false. Syntax WHILE [condition] Example DECLARE Output: 1 3. PL/SQL FOR Loop It is used to execute a set of statements for a fixed number of times. It is iterated between the start and end integer values. Syntax FOR counter IN initial_value..final_value LOOP Example BEGIN Output: 1 4. PL/SQL GOTO Statement In PL/SQL, GOTO statement makes you able to get an unconditional jump from the GOTO to a specific executable statement label in the same subprogram of the PL/SQL block.  Advanced database Management System Syntax I) GOTO label_name; In the above syntax, the label declaration contains the label_name is encapsulated within the symbol and it should be followed by at least one statement to execute.Lets take an example to understand how to use GOTO statement. Example DECLARE Output: value of a: 50 What is PL/SQL Subprograms? Subprograms are named PL/SQL blocks that can be called with a set of parameters. PL/ SQL has two types of subprograms, procedures, and functions. Subprograms have: A declarative part, with declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These items are local and cease to exist when the subprogram ends. Advantages of PL/SQL Subprograms Subprograms let you extend the PL/SQL language. Procedures act like new  Advanced database Management System statements. Functions act like new expressions and operators. Subprograms break a program down into manageable, well-defined modules. Subprograms promote reusability. Subprograms promote maintainability. Dummy subprograms (stubs) let you defer the definition of procedures and functions until after testing the main program. PL/SQL Procedures A procedure is a subprogram that performs a specific action. You specify the name of the procedure, its parameters, its local variables, and the BEGIN-END block that contains its code and handles any exceptions. PL/SQL Procedure PL/SQL Create Procedure A stored procedure is nothing but a named PL/SQL code block that is compiled and stored in one of the Oracle engine's system tables. A procedure is a logically grouped set of SQL and PL/SQL statements, which performs a specific task. Syntax for creating store procedure:  Advanced database Management System CREATE OR REPLACE PROCEDURE procedure_name Example: Creating a stored procedure.1. Create table of person create table Person(id number(10) primary key, name varchar2(100));2. Create Procedure Create or replace procedure "INSERTPERSON" User will get the following output message: Procedure created.The three Important parts of procedure are: 1. Declarative part The declarative part may contain the declarations of constants, variables, exceptions, subprograms and cursors. These objects are local to the procedure and become invalid once the procedure or function exists. 2. Executable part The executable part is a PL/SQL block consisting of SQL and PL/SQL statements that assigns the values, control execution and manipulate data. In this part the code is written to perform an expected action to execute the procedure. 3. Exception Handling Part In this part the code deals with exceptions that may be raised during the execution of code in the executable part. Oracle exception handler can be redirected to the exception handling section of the procedure, where the procedure or function that determines the actual action should be carried out by Oracle exception handler. Following are the three ways used to pass parameters to procedure. 1. IN parameter It indicates that the parameter will accept a value from the user. 2. Out parameter It indicates that the parameter will return a value to the user. 3. IN OUT It indicates that the parameter will either accept a value from the user or return a value to the user.Example:  Advanced database Management System Stored procedure using In and Out mode. DECLARE Output: Square of (2): 4 PL/SQL Drop Procedure Syntax: DROP PROCEDURE procedure_nameFor example: DROP PROCEDURE Employee_salary Functions in PL/SQL PL/SQL CREATE Function The PL/SQL functions are same as PL/SQL procedure. The main difference between a procedure and a function is, a function must always return a value and the procedure may or may not return value. The function should contain a return statement. Syntax CREATE [OR REPLACE] FUNCTION function_name [parameters] Where, function_name specifies the name of the function. [OR REPLACE] option allows to modify an existing function. Lets take an example to declare, define and invoke a simple PL/SQL function that will compute and return the maximum two values.  Advanced database Management System Example DECLARE Output: Maximum of (20,30): 30 Example: Create a function using table. Consider the following table titled 'Employee'. Id Name Department salary 1 Jay IT 45000 2 Albert HR 30000 3 Anna HR 28000 4 Bob IT 35000 5 Alice IT 55000 CREATE OR REPLACE FUNCTION totalEmployee The user will get the following message after creating the function. Function created.Then call a function To call a function user needs to pass the required parameters along with function name and if function returns a value then store the returned value. DECLARE The user will get following result after executing the above code. Total no. of Employee: 4 PL/SQL DROP Function Syntax: DROP FUNCTION function_name  Advanced database Management System Cursor in PL/SQL Oracle Engine uses a work area for its internal processing to execute SQL statements. This work area is private to SQL's operations and is called a context area And the pointer which is pointed to context area is called cursor. The data stored in the cursor is called the active data set. Cursor contains information on a SELECT statement and the rows of data accessed by it. It can hold more than one row, but can process only one row at a time. Cursor is used to access the result-set present in the memory. This result set contains the records returned on execution of a query. Attributes of Cursors Attributes Description %ISOPEN Returns TRUE if cursor is open, else FALSE. %FOUND Returns TRUE if record was fetch successfully, else FALSE. %NOTFOUND Returns TRUE if records was not fetched successfully, else FALSE. %ROWCOUNT Returns number of records processed from the cursor. Types of Cursors The two types of cursors are: 1. Implicit cursors Implicit cursors are automatically generated by the Oracle engine. If the Oracle Engine opens a cursor for its internal processing, it is known as Implicit cursor. Implicit cursors are created by default to process the statements when DML statements(INSERT, UPDATE, DELETE) are executed. Example: Update the information of employees using implicit cursor.  Advanced database Management System Id Name Designation Salary 1 Albert Programmer 50000 2 Anna HR 25000 3 Mark Analyst 55000 4 Jason Content writer 21000 5 Andrew Programmer 90000 Write a procedure to update the salary of employees using implicit cursor.//Sql implicit cursor DECLARE sql%notfound THEN sql%found THEN %rowcount; The result is shown 5 Employee updated 2. Explicit Cursor If a cursor is opened for processing data through a PL/SQL block as per requirement like user defined cursor, is known as an Explicit cursor. Explicit cursor is created while executing a SELECT statement that returns more than one row. These cursor should be defined in the declaration section of the PL/SQL block and created on a SELECT statement which returns more than one row. Syntax: Cursor cursor_name IS select_statement;Following are the steps to work with an explicit cursor: 1. Declare Syntax: CURSOR Cursor_name IS2.Open Syntax:  Advanced database Management System OPEN Cursor_name ;3. Fetch This statement is used to access one row at a time. Syntax: FETCH cursor_name INTO variable_list; 4. Close Syntax: Close Cursor_name; Example: Write a PL/SQL code to retrieve the employee name and designation using explicit cursor. Id Name Designation Salary 1 Albert Programmer 51000 2 Anna HR 26000 3 Mark Analyst 56000 4 Jason Content writer 21000 5 Andrew Programmer 90000 Write a Pl/SQL code to retrieve the employee id, employee name and designation using explicit cursor. DECLARE c_id employee.id%type; c_name employee.name%type; CURSOR c_employee is SELECT id, name, designation FROM employee; //explicit cursor declaration OPEN c_employee; // cursor executed(select statement will be executed)FETCH c_employee into c_id, c_name, c_desig ; CLOSE c_employee ; Output:  Advanced database Management System 1 Albert Programmer Exception in PL/SQL(User define ,Predefine) An error occurred during the execution of program is called exception in PL/SQL. PL/SQL provides the facility to catch errors by declaring conditions in exception block in the program and necessary action to be taken to rectify the error. Exception can be user defined (these are logical error defined by user) or internally defined.For example: The division by zero error. Internal exceptions are raised automatically by the runtime system. User defined exception should be raised explicitly by RAISE statements. Syntax DECLARE BEGIN EXCEPTION Advantages of PL/SQL Exceptions It is possible to handle potential errors from many statements by using a single exception handler. If user needs to check for error at every point, it is solved by adding an exception handler to PL/SQL block in the program. To check error at a specific spot is possible by enclosing a single statement or a group of statements inside its own exception handler. Isolating error handling method makes the rest of the program easier to read and understand. Pre-defined Exceptions PL/SQL provides many pre-defined exceptions, which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. The following table lists few of the important pre-defined exceptions −  Advanced database Management System Oracl SQLCOD Exception e Description E Error 0653 It is raised when a null object is ACCESS_INTO_NULL -6530 0 automatically assigned a value. It is raised when none of the choices 0659 in the WHEN clause of a CASE CASE_NOT_FOUND -6592 2 statement is selected, and there is no ELSE clause. It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized COLLECTION_IS_NU 0653 -6531 nested table or varray, or the program LL 1 attempts to assign values to the elements of an uninitialized nested table or varray. It is raised when duplicate values are 0000 DUP_VAL_ON_INDEX -1 attempted to be stored in a column 1 with unique index. It is raised when attempts are made 0100 to make a cursor operation that is not INVALID_CURSOR -1001 1 allowed, such as closing an unopened cursor. It is raised when the conversion of a 0172 character string into a number fails INVALID_NUMBER -1722 2 because the string does not represent a valid number. It is raised when a program attempts 0101 LOGIN_DENIED -1017 to log on to the database with an 7 invalid username or password. 0140 It is raised when a SELECT INTO NO_DATA_FOUND +100 3 statement returns no rows. It is raised when a database call is 0101 NOT_LOGGED_ON -1012 issued without being connected to the 2 database. 0650 It is raised when PL/SQL has an PROGRAM_ERROR -6501 1 internal problem.  Advanced database Management System It is raised when a cursor fetches ROWTYPE_MISMAT 0650 -6504 value in a variable having CH 4 incompatible data type. It is raised when a member method is 3062 SELF_IS_NULL -30625 invoked, but the instance of the object 5 type was not initialized. 0650 It is raised when PL/SQL ran out of STORAGE_ERROR -6500 0 memory or memory was corrupted. 0142 It is raised when a SELECT INTO TOO_MANY_ROWS -1422 2 statement returns more than one row. It is raised when an arithmetic, 0650 VALUE_ERROR -6502 conversion, truncation, or 2 sizeconstraint error occurs. 0147 It is raised when an attempt is made ZERO_DIVIDE 1476 6 to divide a number by zero. Example: Illustration of exception handling. Consider the following table titled 'Employee'. Id Name Designation Salary 1 Albert Programmer 50000 2 Anna HR 25000 3 Mark Analyst 55000 4 Jason Content writer 20000 5 Andrew Programmer 90000 Write a PL/SQL program to give message that no such a employee is present in a table. DECLARE desi employee.designation%type; SELECT name, designation INTO c_name, c_desi  Advanced database Management System FROM Employees WHER E id = c_id; c_desi ); EXCEPTION Output: No such Employee exist!Note: In the above example, the given input value for employee id= 9, which is not present in the database. If we provide input value for id=3, the output will be: Raising Exceptions The Oracle engine provides a procedure RAISE_APPLICATION_ERROR that allows user to issue user-defined error message. When an error makes it undesirable or impossible to finish processing, the PL/ SQL block and subprogram raise an error. User can write raise statements for a given exception anywhere within the scope of that exception. Syntax: RAISE_APPLICATION_ERROR(,);Where, ErrorNumber is a negative integer in the range -2000 to -20999 & Message is a character string upto 2048 bytes in length. Example: Raising exception by using RAISE statement. DECLARE Output: Encountered out-of-stock error. Triggers in PL/SQL Triggers are Stored in database and executed by Oracle engine whenever some event occurs.  Advanced database Management System When a trigger is fired, SQL statement inside the trigger's PL/SQL code block can also fire the same or some other trigger. This is called cascading triggers. Triggers are written to execute in response events like DML Statements (DELETE, INSERT, or UPDATE), DDL statements (CREATE, ALTER) and database operation (SERVERERROR, LOGON, LOGOFF, STARTUP or SHUTDOWN) Difference between Trigger and Stored Procedure Trigger Stored Procedure Trigger is an act which is performed Stored procedure is a set of functionality automatically before or after an event has which is executed when it is explicitly occurred. invoked. It cannot accept parameters. It can accept parameters. A trigger cannot return any value. A stored procedure can return a value. It is executed automatically on some It needs to be explicitly called. event. Triggers are used for insertion, update Stored procedures are often used and deletion. independently in the database. Uses of Triggers Triggers are used to: 1. Generate virtual column values automatically. DML Triggers DML triggers are created on table or view, and their triggering event that is composed of the DML statements DELETE, INSERT, UPDATE. Syntax for creating Trigger CREATE [OR REPLACE ] TRIGGER trigger_name Example: Illustration of creating trigger in PL/SQL.  Advanced database Management System Consider the following table titled 'Employee'. Id Name Designation Salary 1 Albert Programmer 50000 2 Anna HR 25000 3 Mark Analyst 55000 4 Jason Content writer 20000 5 Andrew Programmer 90000 Lets take a program to create a row level trigger for the table 'Employee' that will fire for INSERT, or UPDATE or DELETE operations performed on the table 'Employee'. This trigger will display the difference between the old salary and new salary. CREATE OR REPLACE TRIGGER print_salary_changes