DBT-2_Theory PDF - Oracle Database Architecture

. Unit 1: Overview of Oracle Architecture Oracle Database is a relational database management system (RDBMS) that Oracle Corporation created and marketed. It is one of the most popular RDBMSs on the market and is used to store and retrieve data for a wide range of applications. Oracle Database is we...

. Unit 1: Overview of Oracle Architecture Oracle Database is a relational database management system (RDBMS) that Oracle Corporation created and marketed. It is one of the most popular RDBMSs on the market and is used to store and retrieve data for a wide range of applications. Oracle Database is well-known for its dependability, scalability, and performance, and it is compatible with a wide range of programming languages and development frameworks. It includes data warehousing, online transaction processing, and advanced analytics, as well as high availability, disaster recovery, and security. The oracle database architecture consists of: 1. Memory structure(Instances) 2. Database system 3. Processes Oracle Instances The instance is a collection of two things: SGA: It stands for System Global Area. It is a shared memory area. Whenever a database instance starts, some memory gets allocated and that memory is termed SGA. Along with memory allocation, one or more background processes will. SGA is used to store data as well as control information about one database instance through its various subcomponents, Where each component is dedicated to a specific purpose. Various Components are: Database buffer cache: The Buffer Cache is a portion of the SGA that stores copies of data blocks read from datafiles. It is used to cache frequently accessed data blocks, reducing the number of disks I/Os required to access the data. This can improve performance by reducing disk I/O time and increasing the speed of data retrieval. Redo log Buffer: The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure. Java pool: The Java Pool is an optional portion of the SGA that is used by Java Virtual Machine (JVM) and related components. It is used to store Java-related data structures, such as Java classes and objects. This pool is used when the Oracle Database is configured to run Java applications or when using Oracle JVM. Large Pool: The Large Pool is an optional portion of the SGA that can be used for large memory allocation, such as backup and restore operations and I/O server processes. It is typically used to improve the performance of these operations by reducing the amount of disk I/O required. Shared pool: The Shared Pool is a portion of the SGA that contains shared memory structures, such as shared SQL and PL/SQL areas. It is used to store the parsed representation of SQL statements, execution plans, and PL/SQL program units. This allows for the efficient reuse of frequently executed statements, reducing the need for reparsing and improving performance. Background processes: Oracle has a collection of processes that are called background processes. These processes are responsible for managing memory, performing I/O operations, and other maintenance activities. Following are some important background processes that are required: System Monitor Process (SMON): These processes are responsible for performing system- level recovery and maintenance activities. Process Monitor Process (PMON): The task of these processes is to monitor other background processes.  Database Writer Process (DBWR): This process performs the task of writing data blocks from the Database Buffer Cache (present in SGA) to physical data files(Present in the Database system). Log Writer Process (LGWR): This process writes the Redo blocks from Redo Log Buffer (present in SGA) to Redo Log Files(present in the Database system). CheckPoint (CKPT): This process maintains data files and control files with the most recent checkpoint information. Database System The database system is suited to the storage system of a computer. The Database system is simply the storage of files. There are three categories of files that are situated in the database system and those are:- Data files: These files hold the actual data in the database. Redo log files: These files are used to hold the changes made in the database. Redo log files can be utilized during the database recovery process to retrieve the original information. Control files: It is a binary file that holds database status-related information like Database Name, Data File, and Redo Log file Names, File Locations, and Log Sequence Number. There are other categories of files that contribute to database management. Parameter file: This file contains the parameters which define the way the database is expected to start up. Password file: This file holds the user passwords and thus maintains the security of databases. Processes There are two types of processes: User process: It is also known as the client process The user actually connects to the instance with the help of user processes. the user process is stabilized when the user sends a connection request to the oracle server. Server process: The server Process connects the user to the database and performs the activities on the client’s behalf as executing SQL statements or retrieving data from the database. Explanation select statement when select statement is issues by the user first the hashed value is generated through parse.it verify the SQLArea is already present or not if it is not present it will check the syntax, semantic(whether the particular column and table present in the DB or not) and privilege(whether the user has access right or not to the column or table) generates the execution plan and execute the query and finally fetches the data to the buffer cache. if the same query is again executed by the user the hash value is generated and this time the SQLArea is present the means execution plan need not to be generated and the query is executed directly. DML statement process update emp set sal=5000 where empid=500 1. when this query issues the query is parsed and the query is executed and the record or block bring to the buffer cache. before the updations is happened the old and new value is store into the log buffer (eg:101, 500, 5000) and the old value kept in undo block until the transaction is committed 2. and the value is updated as 5000 in the buffer cache but the transaction is neither committed nor rollback. I. if the transaction is rollback the old value is restored in the buffer cache ii. when the user commit the transaction a system change number(SCN) is generated along with the transaction id, the entire content of the log buffer is put into the read log file through LGWR. 3. when the DBWR is initited the 5000 value which is kept in the buffer cache is put into the datafile What is PL/SQL PL/SQL is a block structured language. The programs of PL/SQL are logical blocks that can contain any number of nested sub-blocks. Pl/SQL stands for "Procedural Language extension of SQL" that is used in Oracle. PL/SQL is integrated with Oracle database (since version 7). The functionalities of PL/SQL usually extended after each release of Oracle database. Although PL/SQL is closely integrated with SQL language, yet it adds some programming constraints that are not available in SQ PL/SQL Functionalities PL/SQL includes procedural language elements like conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variable of those types and triggers. It can support Array and handle exceptions (runtime errors). After the implementation of version 8 of Oracle database have included features associated with object orientation. You can create PL/SQL units like procedures, functions, packages, types and triggers, etc. which are stored in the database for reuse by applications. PL/SQL is not case sensitive so you are free to use lower case letters or upper case letters except within string and character literals. A line of PL/SQL text contains groups of characters known as lexical units. It can be classified as follows: o Delimeters o Identifiers o Literals o Comment Delimiters o A delimiter is a character, or character combination, that has a special meaning in PL/SQL. Identifiers Identifiers name PL/SQL elements, which include: Constants Cursors Exceptions Keywords Labels Packages Reserved words Subprograms Types Variables Every character in an identifier, alphabetic or not, is significant. For example, the identifiers lastname and last_name are different. Reserved Words and Keywords Reserved words and keywords are identifiers that have special meaning in PL/SQL. Ordinary User-Defined Identifiers Ordinary User-Defined Identifiers An ordinary user-defined identifier: Begins with a letter Can include letters, digits, and these symbols: Dollar sign ($) Number sign (#) Underscore (_) Examples of acceptable ordinary user-defined identifiers: X t2 phone# credit_limit LastName oracle$number money$$$tree SN## try_again_ Examples of unacceptable ordinary user-defined identifiers: mine&yours debit-amount on/off user id Quoted User-Defined Identifiers A quoted user-defined identifier is enclosed in double quotation marks. Between the double quotation marks, any characters from the database character set are allowed except double quotation marks, new line characters, and null characters. For example, these identifiers are acceptable: "X+Y" "last name" "on/off switch" "employee(s)" "*** header info ***" Literals A literal is a value that is neither represented by an identifier nor calculated from other values. For example, 123 is an integer literal and 'abc' is a character literal, but 1+2 is not a literal. When using character literals in PL/SQL, remember: Character literals are case-sensitive. For example, 'Z' and 'z' are different. Whitespace characters are significant. For example, these literals are different: 'abc' ' abc' 'abc ' ' abc ' 'a b c' Comments The PL/SQL compiler ignores comments. Their purpose is to help other application developers understand your source text. Single-Line Comments A single-line comment begins with -- and extends to the end of the line. -- Begin processing SELECT COUNT(*) INTO howmany FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'; -- Check number of tables num_tables := howmany; -- Compute another value Multiline Comments A multiline comment begins with , and can span multiple lines. END IF; */ PL/SQL Variables A variable is a meaningful name which facilitates a programmer to store data temporarily during the execution of code. It helps you to manipulate data in PL/SQL programs. It is nothing except a name given to a storage area. Each variable in the PL/SQL has a specific data type which defines the size and layout of the variable's memory Syntax for declaring variable: variable_name [CONSTANT] datatype [NOT NULL] [{ := | DEFAULT } initial_value]; Constrained declarations A programmer may constrain certain datatypes at declaration so that variables have a size, scale, or precision that is less than the maximum allowed. Some examples: Example total_sales NUMBER(15,2); -- Constrained emp_id VARCHAR2(9); -- Constrained company_number NUMBER; -- Unconstrained book_title VARCHAR2; -- Not valid Naming rules for PL/SQL variables The variable in PL/SQL must follow some naming rules like other programming languages. o The variable_name should not exceed 30 characters. o Variable name should not be the same as the table table's column of that block. o The name of the variable must begin with ASCII letter. The PL/SQL is not case sensitive so it could be either lowercase or uppercase. For example: v_data and V_DATA refer to the same variables. o You should make your variable easy to read and understand, after the first character, it may be any number, underscore (_) or dollar sign ($). o NOT NULL is an optional specification on the variable. initializing Variables in PL/SQL Evertime you declare a variable, PL/SQL defines a default value NULL to it. If you want to initialize a variable with other value than NULL value, you can do so during the declaration, by using any one of the following methods. o The DEFAULT keyword o The assignment operator Example: counter binary_integer := 0; greetings varchar2(20) DEFAULT 'Hello SIES'; You can also specify NOT NULL constraint to avoid NULL value. If you specify the NOT NULL constraint, you must assign an initial value for that variable. Example of initializing variable Let's take a simple example to explain it well: DECLARE a integer := 30; b integer := 40; c integer; f real; BEGIN c := a + b; dbms_output.put_line('Value of c: ' || c); f := 100.0/3.0; dbms_output.put_line('Value of f: ' || f); END; / Variable Scope in PL/SQL: PL/SQL allows nesting of blocks. A program block can contain another inner block. If you declare a variable within an inner block, it is not accessible to an outer block. There are two types of variable scope: o Local Variable: Local variables are the inner block variables which are not accessible to outer blocks. o Global Variable: Global variables are declared in outermost block. Example of Local and Global variables Let's take an example to show the usage of Local and Global variables in its simple form: DECLARE --Global variables num1 number := 95; num2 number := 85; BEGIN dbms_output.put_line('Outer Variable num1: ' || num1); dbms_output.put_line('Outer Variable num2: ' || num2); DECLARE --Local variables num1 number := 195; num2 number := 185; BEGIN dbms_output.put_line('Inner Variable num1: ' || num1); dbms_output.put_line('Inner Variable num2: ' || num2); END; END; / PL/SQL Constants A constant is a value used in a PL/SQL block that remains unchanged throughout the program. It is a user-defined literal value. Syntax to declare a constant: constant_name CONSTANT datatype := VALUE; o Constant_name:it is the name of constant just like variable name. The constant word is a reserved word and its value does not change. o VALUE: it is a value which is assigned to a constant when it is declared. It can not be assigned later Example: pi constant number := 3.141592654; Operators PL/SQL language is rich in built-in operators and provides the following types of operators − Arithmetic operators Relational operators Comparison operators Logical operators String operators Example: BEGIN dbms_output.put_line( 10 + 5); dbms_output.put_line( 10 - 5); dbms_output.put_line( 10 * 5); dbms_output.put_line( 10 / 5); dbms_output.put_line( 10 ** 5); END; 1. Relational Operators Relational operators compare two expressions or values and return a Boolean result. Following table shows all the relational operators supported by PL/SQL. Let us assume variable A holds 10 and variable B holds 20, then − DECLARE a number (2) := 21; b number (2) := 10; BEGIN IF (a = b) then dbms_output.put_line('Line 1 - a is equal to b'); ELSE dbms_output.put_line('Line 1 - a is not equal to b'); END IF; IF (a < b) then dbms_output.put_line('Line 2 - a is less than b'); ELSE dbms_output.put_line('Line 2 - a is not less than b');  END IF; IF ( a > b ) THEN dbms_output.put_line('Line 3 - a is greater than b'); ELSE dbms_output.put_line('Line 3 - a is not greater than b'); END IF; -- Lets change value of a and b a := 5; b := 20; IF ( a = a ) THEN dbms_output.put_line('Line 5 - b is either equal or greater than a'); END IF; IF ( a b ) THEN dbms_output.put_line('Line 6 - a is not equal to b'); ELSE dbms_output.put_line('Line 6 - a is equal to b'); END IF; END; / Output: 2. Logical Operators Following table shows the Logical operators supported by PL/SQL. All these operators work on Boolean operands and produce Boolean results. Let us assume variable A holds true and variable B holds false, then − DECLARE a boolean := true; b boolean := false; BEGIN  IF (a AND b) THEN dbms_output.put_line('Line 1 - Condition is true'); END IF; IF (a OR b) THEN dbms_output.put_line('Line 2 - Condition is true'); END IF; IF (NOT a) THEN dbms_output.put_line('Line 3 - a is not true'); ELSE dbms_output.put_line('Line 3 - a is true'); END IF; IF (NOT b) THEN dbms_output.put_line('Line 4 - b is not true'); ELSE dbms_output.put_line('Line 4 - b is true'); END IF; END; / Output: Line 2 - Condition is true Line 3 - a is true Line 4 - b is not true Control Structure Decision-making structures require that the programmer specify one or more conditions to be evaluated or tested by the program, along with a statement or statements to be executed if the condition is determined to be true, and optionally, other statements to be executed if the condition is determined to be false. It is the simplest form of the IF control statement, frequently used in decision-making and changing the control flow of the program execution. The IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. If the condition is TRUE, the statements get executed, and if the condition is FALSE or NULL, then the IF statement does nothing. 1. If statement IF condition THEN S; END IF; Where condition is a Boolean or relational condition and S is a simple or compound statement. Following is an example of the IF-THEN statement – IF (a 50 THEN exit; END IF; END LOOP; -- after exit, control resumes here dbms_output.put_line('After Exit x is: ' || x); END; / Output: 10 20 30 40 50 After Exit x is: 60 PL/SQL procedure successfully completed. You can use the EXIT WHEN statement instead of the EXIT statement – Example: DECLARE x number := 10; BEGIN LOOP dbms_output.put_line(x); x := x + 10; exit WHEN x > 50; END LOOP; -- after exit, control resumes here dbms_output.put_line('After Exit x is: ' || x); END; / 2. PL/SQL WHILE LOOP Repeats a statement or group of statements while a given condition is true. It tests the condition before executing the loop body. Syntax: WHILE condition LOOP sequence_of_statements END LOOP; Example: DECLARE a number(2) := 10; BEGIN WHILE a < 20 LOOP dbms_output.put_line('value of a: ' || a); a := a + 1; END LOOP; END; / Output: value of a: 10 value of a: 11 value of a: 12 value of a: 13 value of a: 14 value of a: 15 value of a: 16 value of a: 17 value of a: 18 value of a: 19 PL/SQL procedure successfully completed. 3. PL/SQL FOR LOOP Execute a sequence of statements multiple times and abbreviates the code that manages the loop variable. Syntax: FOR counter IN initial_value.. final_value LOOP sequence_of_statements; END LOOP; Example: DECLARE a number(2); BEGIN FOR a in 10.. 20 LOOP dbms_output.put_line('value of a: ' || a); END LOOP; END; / Output: value of a: 10 value of a: 11 value of a: 12 value of a: 13 value of a: 14 value of a: 15 value of a: 16 value of a: 17 value of a: 18 value of a: 19 value of a: 20 PL/SQL procedure successfully completed. 3. Reverse FOR LOOP Statement By default, iteration proceeds from the initial value to the final value, generally upward from the lower bound to the higher bound. You can reverse this order by using the REVERSE keyword. In such case, iteration proceeds the other way. After each iteration, the loop counter is decremented. DECLARE a number(2) ; BEGIN FOR a IN REVERSE 10.. 20 LOOP dbms_output.put_line('value of a: ' || a); END LOOP; END; / Output: value of a: 20 value of a: 19 value of a: 18 value of a: 17 value of a: 16 value of a: 15 value of a: 14 value of a: 13 value of a: 12 value of a: 11 value of a: 10 PL/SQL procedure successfully completed 5.Nested loops in PL/SQL You can use one or more loop inside any another basic loop, while, or for loop Syntax: LOOP Sequence of statements1 LOOP Sequence of statements2 END LOOP; END LOOP; The syntax for a nested FOR LOOP statement in PL/SQL is as follows − FOR counter1 IN initial_value1.. final_value1 LOOP sequence_of_statements1 FOR counter2 IN initial_value2.. final_value2 LOOP sequence_of_statements2 END LOOP; END LOOP; The syntax for a nested WHILE LOOP statement in Pascal is as follows – WHILE condition1 LOOP sequence_of_statements1 WHILE condition2 LOOP sequence_of_statements2 END LOOP; END LOOP; Example: DECLARE i number(3); j number(3); BEGIN i := 2; LOOP j:= 2; LOOP exit WHEN ((mod(i, j) = 0) or (j = i)); j := j +1; END LOOP; IF (j = i ) THEN dbms_output.put_line(i || ' is prime'); END IF; i := i + 1; exit WHEN i = 50; END LOOP; END; / Output: 2 is prime 3 is prime 5 is prime 7 is prime 11 is prime 13 is prime 17 is prime 19 is prime 23 is prime 29 is prime 31 is prime 37 is prime 41 is prime 43 is prime 47 is prime PL/SQL procedure successfully completed. The Loop Control Statements EXIT statement The Exit statement completes the loop and control passes to the statement immediately after the END LOOP. Example: DECLARE a number(2) := 10; BEGIN -- while loop execution WHILE a < 20 LOOP dbms_output.put_line ('value of a: ' || a); a := a + 1; IF a > 15 THEN -- terminate the loop using the exit statement EXIT; END IF; END LOOP; END; / Output: value of a: 10 value of a: 11 value of a: 12 value of a: 13 value of a: 14 value of a: 15 PL/SQL procedure successfully completed. The EXIT WHEN Statement The EXIT-WHEN statement allows the condition in the WHEN clause to be evaluated. If the condition is true, the loop completes and control passes to the statement immediately after the END LOOP. Syntax: EXIT WHEN condition; Example: DECLARE a number(2) := 10; BEGIN -- while loop execution WHILE a < 20 LOOP dbms_output.put_line ('value of a: ' || a); a := a + 1; -- terminate the loop using the exit when statement EXIT WHEN a > 15; END LOOP; END; / Output: value of a: 10 value of a: 11 value of a: 12 value of a: 13 value of a: 14 value of a: 15 6.CONTINUE statement Causes the loop to skip the remainder of its body and immediately retest its condition prior to reiterating. Syntax: CONTINUE; Example: DECLARE a number(2) := 10; BEGIN -- while loop execution WHILE a < 20 LOOP dbms_output.put_line ('value of a: ' || a); a := a + 1; IF a = 15 THEN -- skip the loop using the CONTINUE statement a := a + 1; CONTINUE; END IF; END LOOP; END; / Example: value of a: 10 value of a: 11 value of a: 12 value of a: 13 value of a: 14 value of a: 16 value of a: 17 value of a: 18 value of a: 19 PL/SQL procedure successfully completed. 7.GOTO statement Transfers control to the labeled statement. Though it is not advised to use the GOTO statement in your program. Syntax: GOTO label;.... > statement; example: DECLARE a number(2) := 10; BEGIN -- while loop execution WHILE a < 20 LOOP dbms_output.put_line ('value of a: ' || a); a := a + 1; IF a = 15 THEN a := a + 1; GOTO loopstart; END IF; END LOOP; END; Example: value of a: 10 value of a: 11 value of a: 12 value of a: 13 value of a: 14 value of a: 16 value of a: 17 value of a: 18 value of a: 19 PL/SQL procedure successfully completed. PL/SQL Cursor When an SQL statement is processed, Oracle creates a memory area known as context area. A cursor is a pointer to this context area. It contains all information needed for processing the statement. In PL/SQL, the context area is controlled by Cursor. A cursor contains information on a select statement and the rows of data accessed by it. A cursor is used to referred to a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors: o Implicit Cursors o Explicit Cursors 1) PL/SQL Implicit Cursors  The implicit cursors are automatically generated by Oracle while an SQL statement is executed, if you don't use an explicit cursor for the statement. These are created by default to process the statements when DML statements like INSERT, UPDATE, DELETE etc. are executed. Orcale provides some attributes known as Implicit cursor's attributes to check the status of DML operations. Some of them are: %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN. For example: When you execute the SQL statements like INSERT, UPDATE, DELETE then the cursor attributes tell whether any rows are affected and how many have been affected. If you run a SELECT INTO statement in PL/SQL block, the implicit cursor attribute can be used to find out whether any row has been returned by the SELECT statement. It will return an error if there no data is selected. The following table soecifies the status of the cursor with each of its attribute. PL/SQL Implicit Cursor Example Create customers table and have records: Let's execute the following program to update the table and increase salary of each customer by 5000. Here, SQL%ROWCOUNT attribute is used to determine the number of rows affected: DECLARE total_rows number(2); BEGIN UPDATE customers SET salary = salary + 5000; IF sql%notfound THEN dbms_output.put_line('no customers updated');  ELSIF sql%found THEN total_rows := sql%rowcount; dbms_output.put_line( total_rows || ' customers updated '); END IF; END; / Output: 6 customers updated PL/SQL procedure successfully completed. 2) PL/SQL Explicit Cursors The Explicit cursors are defined by the programmers to gain more control over the context area. These cursors should be defined in the declaration section of the PL/SQL block. It is created on a SELECT statement which returns more than one row. Syntax of explicit cursor CURSOR cursor_name IS select_statement;; Steps: You must follow these steps while working with an explicit cursor. 1. Declare the cursor to initialize in the memory. 2. Open the cursor to allocate memory. 3. Fetch the cursor to retrieve data. 4. Close the cursor to release allocated memory. 1) Declare the cursor: It defines the cursor with a name and the associated SELECT statement Syntax for explicit cursor decleration CURSOR name IS SELECT statement; 2) Open the cursor: It is used to allocate memory for the cursor and make it easy to fetch the rows returned by the SQL statements into it. Syntax for cursor open: OPEN cursor_name; Fetch the cursor: It is used to access one row at a time. You can fetch rows from the above-opened cursor as follows: Syntax for cursor fetch: FETCH cursor_name INTO variable_list; Close the cursor: It is used to release the allocated memory. The following syntax is used to close the above-opened cursors. Syntax: Close cursor_name; Example: 1. 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; / Cursor for loop A nice feature of the cursor FOR LOOP statement is that it allows you to fetch every row from a cursor without manually managing the execution cycle i.e., OPEN, FETCH, and CLOSE. The cursor FOR LOOP implicitly creates its loop index as a record variable with the row type in which the cursor returns and then opens the cursor. In each loop iteration, the cursor FOR LOOP statement fetches a row from the result set into its loop index. If there is no row to fetch, the cursor FOR LOOP closes the cursor. The cursor is also clos Syntax: FOR record IN cursor_name LOOP process_record_statements; END LOOP; 1) record The record is the name of the index that the cursor FOR LOOP statement declares implicitly as a %ROWTYPE record variable of the type of the cursor. The record variable is local to the cursor FOR LOOP statement. It means that you can only reference it inside the loop, not outside. After the cursor FOR LOOP statement execution ends, the record variable becomes undefined. 2) cursor_name The cursor_name is the name of an explicit cursor that is not opened when the loop starts. Note that besides the cursor name, you can use a SELECT statement as shown below: Syntax: FOR record IN (select_statement) LOOP process_record_statements; END LOOP; In this case, the cursor FOR LOOP declares, opens, fetches from, and closes an implicit cursor. However, the implicit cursor is internal; therefore, you cannot reference it. PL/SQL cursor FOR LOOP example The following example declares an explicit cursor and uses it in the cursor FOR LOOP statement. Example: DECLARE CURSOR c_product IS SELECT product_name, list_price FROM products ORDER BY list_price DESC; BEGIN FOR r_product IN c_product LOOP dbms_output.put_line( r_product.product_name || ': $' || r_product.list_price ); END LOOP; END; In this example, the SELECT statement of the cursor retrieves data from the products table. The FOR LOOP statement opened, fetched each row in the result set, displayed the product information, and closed the cursor. Introduction to PL/SQL cursor variables A cursor variable is a variable that references a cursor. Unlike implicit and explicit cursors, a cursor variable is not tied to any specific query. This means that a cursor variable can be opened for any query. To declare a cursor variable, you use the REF CURSOR is the data type. PL/SQL has two forms of REF CURSOR typeS: strong typed and weak typed REF CURSOR. The following shows an example of a strong REF CURSOR. DECLARE TYPE customer_t IS REF CURSOR RETURN customers%ROWTYPE; c_customer customer_t; example: DECLARE TYPE CUST_CO IS REF CURSOR RETURN CUSTOMERS%ROWTYPE; CUST_CO_VAR CUST_CO; CUST_REC CUSTOMERS%ROWTYPE; BEGIN OPEN CUST_CO_VAR FOR SELECT * FROM CUSTOMERS where CUSTOMER_ID END procedure_name; 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; / Output: Minimum of (23, 45) : 23 PL/SQL procedure successfully completed. Example 2: Table creation: create table user(id number(10) primary key,name varchar2(100)); Procedure Code: create or replace procedure "INSERTUSER" (id IN NUMBER, name IN VARCHAR2) is begin insert into user values(id,name); end; / PL/SQL program to call procedure BEGIN insertuser(101,'Rahul'); dbms_output.put_line('record inserted successfully'); END; / PL/SQL Drop Procedure Syntax for drop procedure DROP PROCEDURE procedure_name; Example of drop procedure DROP PROCEDURE pro1; Parameters in Procedures In PL/SQL, parameters are used to pass values into procedures. There are three types of parameters used in procedures: 1. IN parameters Used to pass values into the procedure Read-only inside the procedure Can be a variable, literal value, or expression in the calling statement. 2. OUT parameters Used to return values from the procedure to the calling program Read-write inside the procedure Must be a variable in the calling statement to hold the returned value 3. IN OUT parameters  Used for both passing values into and returning values from the procedure Read-write inside the procedure Must be a variable in the calling statement PL/SQL Function The PL/SQL Function is very similar to PL/SQL Procedure. The main difference between procedure and a function is, a function must always return a value, and on the other hand a procedure may or may not return a value. Except this, all the other things of PL/SQL procedure are true for PL/SQL function too. Functions − These subprograms return a single value; mainly used to compute and return a value. Procedures − These subprograms do not return a value directly; mainly used to perform an action. Syntax: CREATE [OR REPLACE] FUNCTION function_name [parameters] [(parameter_name [IN | OUT | IN OUT] type [,...])] RETURN return_datatype {IS | AS} BEGIN < function_body > END [function_name]; Here: o Function_name: specifies the name of the function. o [OR REPLACE] option allows modifying an existing function. o The optional parameter list contains name, mode and types of the parameters. o IN represents that value will be passed from outside and OUT represents that this parameter will be used to return a value outside of the procedure. o The AS keyword is used instead of the IS keyword for creating a standalone function. Example: create or replace function adder(n1 in number, n2 in number) return number is n3 number(8); begin n3 :=n1+n2; return n3; end; / Now write another program to call the function. DECLARE n3 number(2); BEGIN n3 := adder(11,22); dbms_output.put_line('Addition is: ' || n3); END; / Output: Addition is: 33 Statement processed.  0.05 seconds PL/SQL function example using table CREATE OR REPLACE FUNCTION totalCustomers RETURN number IS total number(2) := 0; BEGIN SELECT count(*) into total FROM customers; RETURN total; END; / Calling PL/SQL Function: DECLARE c number(2); BEGIN c := totalCustomers(); dbms_output.put_line('Total no. of Customers: ' || c); END; / Output: Total no. of Customers: 4 PL/SQL procedure successfully completed. PL/SQL Drop Function DROP FUNCTION function_name; Procedure Function It is a subprogram or set of SQL statements. Function is a set of SQL statements and it is used to get computed values or to perform computation. It can change the database environment Function will not make any permanent changes to the database environment. Select, insert, update and delete statement are In function only select statement is allowed, allowed, DML commands are allowed insert, delete, update are not allowed. Stored procedures cannot be executed from It can be called from select, where or stored select, where or functions. procedures. Stored procedures can have single or multiple Mostly function returns scalar values/single outputs. values. We can use functions inside the stored Stored procedures cannot be used inside the procedure. functions. Error handling is possible using try, catch in Error handling is not possible using try catch in stored procedure. functions. Triggers Trigger is a predefined program that automatically gets executed in response to specific events occurring in a database. These events could be associated with tables, views, schemas, or the entire database. A trigger is stored in the database and gets invoked repeatedly when specified conditions are met. They are programmed to respond to different events, including database manipulation (DML) statements, database definition (DDL) statements, and various database operations like LOGON, LOGOFF, STARTUP, or SHUTDOWN. In this session, we will learn in-depth about triggers. Creating Triggers CREATE [OR REPLACE] TRIGGER name_of_trigger {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) BEGIN EXCEPTION END; CREATE [ OR REPLACE ] TRIGGER trigger_name is used to create a trigger or replace the existing trigger.| BEFORE | AFTER | INSTEAD OF specifies trigger timing. INSERT | UPDATE | DELETE are the DML operations performed on table or views. OF column_name specifies the column that would be updated. ON table_name species the table for the operation. FOR EACH ROW specify that trigger is executed on each row. Types of Triggers in PL/SQL END IF; END; Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which will create a new record in the table – Create table and have records: This trigger will display the salary difference between the old values and new values: 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; / Check the salary difference by procedure: DECLARE total_rows number(2); BEGIN UPDATE customers SET salary = salary + 5000; IF sql%notfound THEN dbms_output.put_line('no customers updated'); ELSIF sql%found THEN total_rows := sql%rowcount; dbms_output.put_line( total_rows || ' customers updated '); END IF; END; / Output: Note: As many times you executed this code, the old and new both salary is incremented by 5000 and hence the salary difference is always 5000. PERFORM SOME DDL COMMANDS DELETE trigger Syntax DROP TRIGGER trigger_name; Exception Handling: What is an Exception? Any abnormal condition or say event that interrupts the normal flow of your program’s instructions at run time is an exception. Or in simple words you can say an exception is a run time error. Types of exceptions There are two types of PL/SQL exceptions in Oracle database. 1. System-defined exceptions and 2. User-defined exceptions System-Defined Exceptions System-defined exceptions are defined and maintained implicitly by the Oracle server. These exceptions are mainly defined in the Oracle STANDARD package. Whenever an exception occurs inside the program. The Oracle server matches and identifies the appropriate exception from the available set of exceptions. System defined exceptions majorly have a negative error code and error message. These errors have a short name which is used with the exception handler. User-Define Exceptions Unlike System-Define Exception, User-Define Exceptions are raised explicitly in the body of the PL/SQL block (more specifically inside the BEGIN-END section) using the RAISE Statement. 1. By declaring a variable of EXCEPTION type in declaration section. You can declare a user defined exception by declaring a variable of EXCEPTION datatype in your code and raise it explicitly in your program using RAISE statement and handle them in the Exception Section. 2. RAISE_APPLICATION_ERROR method. Using this method you can declare a user defined exception with your own customized error number and message. It is used to display user-defined error messages with error number whose range is in between -20000 and -20999. When RAISE_APPLICATION_ERROR executes it returns error message and error code which looks same as Oracle built-in error. 3. Declare user-defined exception using PRAGMA EXCEPTION_INIT function. Using PRAGMA EXCEPTION_INIT function you can map a non-predefined error number with the variable of EXCEPTION datatype. Means using the same function you can associate a variable of EXCEPTION datatype with a standard error. Declaring a user-define exception using Exception variable is a three step process. These three steps are –  1. Declare a variable of exception datatype – This variable is going to take the entire burden on its shoulders. 2. Raise the Exception – This is the part where you tell the compiler about the condition which will trigger the exception. 3. Handle the exception – This is the last section where you specify what will happen when the error which you raised will trigger. Step 1: Declare a variable of Exception datatype DECLARE var_dividend NUMBER := 24; var_divisor NUMBER := 0; var_result NUMBER; ex_DivZero EXCEPTION; Step 2: Raise the Exception BEGIN IF var_divisor = 0 THEN RAISE ex_DivZero; END IF; var_result := var_dividend/var_divisor; DBMS_OUTPUT.PUT_LINE('Result = ' ||var_result); Step 3: Handle the exception EXCEPTION WHEN ex_DivZero THEN DBMS_OUTPUT.PUT_LINE('Error Error - Your Divisor is Zero'); END; / What is RAISE_APPLICATION_ERROR method? RAISE APPLICATION ERROR is a stored procedure which comes in-built with Oracle software. Using this procedure you can associate an error number with the custom error message. Combining both the error number as well as the custom error message you can compose an error string which looks similar to those default error strings which are displayed by Oracle engine when an error occurs. How many errors can we generate using RAISE_APPLICATION_ERROR procedure? RAISE_APPLICATION_ERROR procedure allows us to number our errors from -20,000 to -20,999 thus we can say that using RAISE_APPLICATION_ERROR procedure we can generate 1000 errors. Syntax of Raise_Application_Error raise_application_error (error_number, message [, {TRUE | FALSE}]); ACCEPT var_age NUMBER PROMPT 'What is yor age'; DECLARE age NUMBER := &var_age; BEGIN IF age < 18 THEN RAISE_APPLICATION_ERROR (-20008, 'you should be 18 or above for the DRINK!'); END IF; DBMS_OUTPUT.PUT_LINE ('Sure, What would you like to have?'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLERRM); END; / In this exception handling section I have called a SQLERRM function using DBMS OUTPUT statement. This is a utility function provided by Oracle which retrieves the error message for the last occurred exception. What is PRAGMA EXCEPTION_INIT? Pragma Exception_Init is a two part statement where first part is made up of keyword PRAGMA and second part is the Exception_Init call. PRAGMA Keyword A pragma is a compiler directive which indicates that the Statements followed by keyword PRAGMA is a compiler directive statement this means that the statement will be processed at the compile time & not at the runtime. PRAGMA Exception_Init Exception_init helps you in associating an exception name with an Oracle error number. In other words we can say that using Exception_Init you can name the exception. Why name the exception? Yes, there is a way of declaring user-define exception without the name and that is by using Raise_Exception_Error procedure. This indeed is a simple and easy way but as we learnt in the last tutorial that to handle exceptions without name we use OTHERS exception handler. Now think that in your project you have multiple exceptions and that too without name. In order to handle all those exceptions you have a single exception handler with name OTHERS. In this case on the occurrence of an exception condition the compiler will display the error stack produced by the OTHER handler. Can you imagine how difficult it will be to trace that part of your project which is causing the error. In order to trace that part you need to go through each & every line of your code. This will be mere waste of time. You can save all those time wasting efforts just by naming the exception, that way you can design an exception handler specific to the name of your exception which will be easily traceable. This is the advantage of naming the exception. Syntax of Pragma Exception_Init. PRAGMA EXCEPTION_INIT (exception_name, error_number); DECLARE ex_age EXCEPTION; age NUMBER := 17; PRAGMA EXCEPTION_INIT(ex_age, -20008); BEGIN IF age SELECT S2.NEXTVAL FROM DUAL; Output: NEXTVAL ---------- 1 SQL> SELECT S2.NEXTVAL FROM DUAL; output: NEXTVAL ---------- 2 SQL> SELECT S2.CURRVAL FROM DUAL; Output: CURRVAL ---------- 2 3. ROWNUM: Oracle engine maintains the number of each record inserted by users in table. By the help of ROWNUM clause we can access the data according to the record inserted. 1. SQL> SELECT first_name, salary,rownum FROM Employees WHERE Department_id= 30;  2. Select rownum A * from employee Where rownum SELECT first_name, salary,rowid FROM Employees WHERE Department_id= 30; PL/SQL Transation PL/SQL transactions are vital components in database management, providing a means to maintain data integrity, consistency, and reliability within a relational database. A transaction in PL/SQL is defined as a series of SQL operations treated as a single unit of work. In this article, We will learn about PL/SQL Transactions in detail and so on. PL/SQL Transactions In PL/SQL, a transaction is a sequence of one or more SQL operations executed as a unit of work. A transaction starts when an SQL operation (like INSERT, UPDATE, or DELETE) is executed and ends when the changes are either committed or rolled back. Transactions are fundamental in ensuring data integrity and consistency within a database, especially in cases of concurrent access or failure. A transaction begins whenever the first SQL statement (particularily DML commands INSERT, UPDATE, DELETE, SELECT) is encountered and ends when a COMMIT or ROLLBACK command is executed. Using COMMIT Commit command is executed after every DML command as they are not auto saved or commited like DDL commands. This way, the commit command permanently changes the data in the database. Following is the syntax: Commit; NOTE: By default, automatic commit for DML commands is off. The automatic commit for DML commands can be set by using the following command: set autocommit on; -- and to turn it off set autocommit off; Using ROLLBACK Rollback means undo. Whenever rollback command is executed, it ends the transaction and undoes all the changes made during the transaction. Rollback can be applied to those transactions which are not committed. The rollback command will have no affect if it is executed after the commit command because in that case the commit command will make the changes done in the transaction permanent. Following is the syntax: Rollback [to savepoint ]; where, savepoint is an optional parameter and is used to rollback a transaction partly upto a certain specified point. savepointname is the name given to the savepoint created during the transaction and is user-defined. Using SAVEPOINT For longer transactions, savepoint is quite useful as it divides longer transactions into smaller parts and marks certain points of a transaction as checkpoints. It is useful when we want to rollback a particular part of a transaction instead of applying rollback to unwanted parts of a transaction or the complete transaction. For example, if a complete transaction has 8 DML statements, and we create a savepoint after 4 statements, then if, for some reason after the execution of 6th statement we want to rollback uptil the 4th statement, then we can easily do that and the transaction can again be executed starting from the 4th statement. For example, INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 ); COMMIT; Savepoints INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Rajnish', 27, 'HP', 9500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (8, 'Riddhi', 21, 'WB', 4500.00 ); SAVEPOINT sav1; UPDATE CUSTOMERS SET SALARY = SALARY + 1000; ROLLBACK TO sav1; UPDATE CUSTOMERS SET SALARY = SALARY + 1000 WHERE ID = 7; UPDATE CUSTOMERS SET SALARY = SALARY + 1000 WHERE ID = 8; COMMIT; ROLLBACK TO sav1 − This statement rolls back all the changes up to the point, where you had marked savepoint sav1. Unit 3 Locks Locks in PL/SQL is a way to maintain data integrity of the database. As oracle is a multi-user platform where tables used in a database acts as a global resource being shared by multiple users at the same time. There is a possibility that the data may become inconsistent due to concurrent processing of data by multiple user at the same time. Therefore, locks play an important role to maintain concurrency control ensuring data integrity of stored data in the database. Types of Lock Following are the two different types of locking in Oracle: 1. Implicit Locking 2. Explicit Locking Implicit Locking It is an automatic locking of the database done by the oracle engine on the basis of following two factors: 1. Type of lock to be applied. 2. Level of lock to be applied. Let's see both of these factors one by one: 1. Type of lock to be applied Depending upon the type of operation (read or write) to be performed on a database, there are two types of locks. 2. Level of lock to be applied: Locking can be done on basis of three levels:  1. Row Level: It is used when a condition is applied in a query on a single row(or record) using WHERE clause. 2. Page Level: It is used when the condition is applied in a query on a certain set of data(certain records) using WHERE clause. 3. Table Level: It is used when the condition is applied in a query on the entire table of data(certain records) using WHERE clause. Explicit Locking This is a user-defined locking done on a database by the user according to their granted table privileges. Such type of locking overrides the implicit locking. Explicit Locking can be done by using one of the following ways: 1. Select...For Update statement 2. Lock Table statement 1. Select…For Update statement: Following is the syntax for this statement: Syntax: SELECT * FROM tablename WHERE condition For Update Nowait; Let's take a few example and see, Suppose two clients, client X and client Y are performing the transactions on the same database table flight_detail Following is the query run by Client X: SELECT * from flight_detail where city='DELHI' For Update; When the above SELECT statement is executed, the oracle engine automatically locks the record with city as Delhi and this lock can be released only when Client X will execute COMMIT or ROLLBACK command. Now let's see the query run by Client Y: SELECT * from flight_detail where city='DELHI' For Update; Now the client Y executes the SELECT statement for the same record that has been already locked by the oracle engine for Client X. So in this case the client Y has to wait till the client X release the lock by executing the COMMIT statement. Therefore, to overcome the unnecessary waiting time NOWAIT option can be used to inform the oracle engine that the record has already been locked and it can terminate the statement. So if the Client Y executed the query with NOWAIT option then SELECT * from flight_detail where city='DELHI' For Update Nowait; With the execution of the above statement, the oracle engine displays the message in the output Resource is Busy. NOTE: Select…For update statement cannot be used with the group functions, set operators ,group by and distinct clause. Database security All systems have ASSETS and security is about protecting assets. The first thing, then, is to know your assets and their value. The second thing to know is what THREATs are putting your assets at risk. These include things such as power failure and employee fraud. An outlined development mechanism is: 1. Document assets (what they are, what their value is). 2. Identify treats (what they are, how likely they are, what the impact is if they occur). 3. Associate threats with each asset. 4. Design mechanisms to protect each asset appropriate to its value and the cost of its protection, to detect a security breach against each asset, to minimise the losses incurred and to recover normal operation. Threats to the database You will build your security skills from two directions. One is from the appre ciation and awareness of changing threats, and the other from the technical remedies to them. Threats include: Unauthorised modification: Changing data values for reasons of sabotage, crime or ignorance which may be enabled by inadequate security mecha nisms, or sharing of passwords or password guessing, for example. Unauthorised disclosure: When information that should not have been disclosed has been disclosed. A general issue of crucial importance, which can be accidental or deliberate. Loss of availability: Sometimes called denial of service. When the database is not available it incurs a loss (otherwise life is better without the sys tem!). So any threat that gives rise to time offline, even to check whether something has occurred, is to be avoided. Commercial sensitivity: Most financial losses through fraud arise from employees. Access controls provide both protection against criminal acts and evidence of attempts (successful or otherwise) to carry out acts detrimental to the organisation, whether fraud, extraction of sensitive data or loss of availability. Computer misuse: There is also generally legislation on the misuse of computers. Misuse includes the violation of access controls and attempts to cause damage by changing the database state or introducing worms and viruses to interfere with proper operation. These offences are often extraditable. Audit requirements: These are operational constraints built around the need to know who did what, who tried to do what, and where and when everything happened. They involve the detection of events (including CONNECT and GRANT transactions), providing evidence for detection, assurance as well as either defence or prosecution. Principles of database security To structure thoughts on security, you need a model of security. These come in various forms that depend on roles, degree of detail and purpose. The major categories are areas of interest (threats, impact and loss) as well as the actions involved in dealing with them. Security risks are to be seen in terms of the loss of assets. These assets include: Hardware Software Data Data quality Credibility  Availability Business benefit You need to accept that security can never be perfect. There always remains an element of risk, so arrangements must be made to deal with the worst eventuality- which means steps to minimise impact and recover effectively from loss or damage to assets. Security models