Oracle PL SQL Concept Book in PDF Form

Oracle (PL/SQL)

Oracle (PL/SQL) Md. Iqbal Iqbal Ahmed, M.Sc. Retired Lecturer Lecturer from from Nagarjuna Government College,

Nalgonda (Telangana State) INDIA

F A S T

T R A C K

Mobile: 9 4 4 0 1 0 2 0 5 6 Email: [email protected] Neha Ne ha Comp Co mput uter ers, s, Nal N algo gond ndaa - Telan Tel anga gana na - INDI IN DIAA-Mo Mobi bile le:: 944 9 4401 0102 0205 0566

PDF created with pdfFactory Pro trial version www.pdffactory.com

1

Oracle (PL/SQL)

Preface This book is useful to learn Oracle (PL/SQL) 10g (Grid Computing) on Fast Track for B.Sc., J.K.C., M.C.A., M.Sc., B.Tech., B.E., students preparing for multi-national companies. In this book more concentration is given on concept and practic prac tical al side rather rat her than theory theo ry.. I req request uest the readers to notify any suggestions to improve this book to great extent through email. This book is revised as per the needs and suggestions through email on 15th, February, 2015. I humbly appeal to the readers to encourage me to write more books on different topics; please send your minimum contribution to my bank account if possible. State Bank of Hyderabad (INDIA) S.B. A/C No.: 52084247503 IFSC Code: SBHY0020952 Name: MD IQBAL AHMED

Address: Md. Iqbal Ahemd, M.Sc., Retired Lecturer, Lecturer, H.No. 6-2-916/4, Meerbagh Colony, Nalgonda - (T.S.) (T.S.) - 508001 I N D I A.

Author Md. Iqbal Ahemd, M.Sc., Retired Lecturer from Nagarjuna Government College Nalgonda - (T.S.) (T.S.) I N D I A. Email: [email protected]

Neha Ne ha Comp Co mput uter ers, s, Nal N algo gond ndaa - Telan Tel anga gana na - INDI IN DIAA-Mo Mobi bile le:: 944 9 4401 0102 0205 0566


2

Oracle (PL/SQL)

Preface This book is useful to learn Oracle (PL/SQL) 10g (Grid Computing) on Fast Track for B.Sc., J.K.C., M.C.A., M.Sc., B.Tech., B.E., students preparing for multi-national companies. In this book more concentration is given on concept and practic prac tical al side rather rat her than theory theo ry.. I req request uest the readers to notify any suggestions to improve this book to great extent through email. This book is revised as per the needs and suggestions through email on 15th, February, 2015. I humbly appeal to the readers to encourage me to write more books on different topics; please send your minimum contribution to my bank account if possible. State Bank of Hyderabad (INDIA) S.B. A/C No.: 52084247503 IFSC Code: SBHY0020952 Name: MD IQBAL AHMED

Address: Md. Iqbal Ahemd, M.Sc., Retired Lecturer, Lecturer, H.No. 6-2-916/4, Meerbagh Colony, Nalgonda - (T.S.) (T.S.) - 508001 I N D I A.

Author Md. Iqbal Ahemd, M.Sc., Retired Lecturer from Nagarjuna Government College Nalgonda - (T.S.) (T.S.) I N D I A. Email: [email protected]



2

Oracle (PL/SQL)

Oracle (PL/SQL) Contents 1

Introduction to PL/SQL ..................................4

2

Structure of PL/SQL Block ............................5

3

Data Types and Variables ...............................8

4

Control Structure .................................... ..........................................15 ......15

5

Cursors ..................................... .........................................................33 ....................33

6

Exception ..................................... .....................................................59 ................59

7

Collections ..................................................67

8

Transaction Control .................................. .....................................79 ...79

9

Procedures, Packages and Triggers Triggers ..............84 ***



3

Oracle (PL/SQL)

Oracle (PL/SQL) Introduction to PL/SQL Chapter 1

Introduction : PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the late 1980’s to enhance the capabilities of SQL. It is an object oriented language. With the help of SQL language, we can create or modify tables, and extract required data from the tables. But the relational database server where the tables are stored executes them one at a time in serial order. It causes overhead to the relational database server. But using PL/SQL we can send a bulk of queries which are kept in a block to execute. It can handle conditional branching statements, loops, and runtime errors. It has the power of fourth-generation programming languages. It can interact with the user and receives data from the keyboard. It is capable to display messages to the user wherever necessary. It brings added functionality to the Forms and Reports.

Features of PL/SQL: • • • • • • • •

PL/SQL is a completely portable, high-performance transaction-processing language. It provides a built-in interpreted and Operating System independent programming environment. It can also directly be called from the command-line SQL *Plus interface. It’s general syntax is based on that of ADA and Pascal programming language. It offers extensive error checking. It offers numerous data types. It supports structured programming through functions and procedures. It supports developing web applications and server pages.

***

Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


4

Oracle (PL/SQL)

Structure of PL/SQL Block Chapter 2 PL/SQL is a block-structured language, meaning that its programs are divided and written in logical blocks or sections. Within a block, statements are executed for data manipulation or queries. The blocks may be Anonymous Blocks (Unknown blocks) or Stored Program Units (Procedures, Functions, and Packages) or Triggers.

Anonymous Blocks: An Anonymous Block is a PL/SQL program unit that has no name. It contains three sections as shown below: 1. 2. 3.

Declarative Section Executable Section Exception Handling Section

Optional Mandatory (Required) Optional

Declarative Section:

This section starts with the keyword DECLARE. It is used to declare variables, constants, cursors etc., if needed. Executable Section:

This section is enclosed between the keyword BEGIN and END and it is a mandatory section. It contains one or more executable PL/SQL statements of the program. Exception Handling Section:

This section starts with the keyword EXCEPTION. This section is again optional and contains exception(s) that handle errors in the run-time. Note: PL/SQL blocks can be nested within other PL/SQL blocks using BEGIN and

END.



5

Oracle (PL/SQL) We use BEGIN, DECLARE, EXCEPTION and END keywords in the sections. All the SQL statements and END keyword are followed by semicolon. But BEGIN, DECLARE, and EXCEPTION are not followed by semicolon. Use a slash (/) to run the anonymous PL/SQL block in the SQL*Plus buffer. When the block is executed successfully, without unhandled errors or compile errors, the message output should be as follows: PL/SQL procedure successfully completed. The structure of an anonymous block is shown as below: DECLARE

BEGIN

EXCEPTION

END; / Sample Program: To display Hello World! on the screen.

Create the following simple program to display a message on the screen. Neha Program 1:

-- Program to display a message on the screen. -- File Name: 1.sql SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.PUT_LINE('Hello World!'); END; / Note: A PL/SQL block is terminated by a slash (/) on a line by itself. Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


6

Oracle (PL/SQL) Note: DBMS_OUTPUT is an Oracle-supplied package and PUT_LINE is a procedure

within that package. To execute this package, we must write the command SET SERVEROUTPUT ON. Executing a PL/SQL Block:

PL/SQL statements are created using a text editor and are executed. A PL/SQL block is started by using the START command, abbreviated as STA or @. To run the program:

START 1or STA 1or @1 OUTPUT:

Hello World! Analysis:

In the above program, declarative and Exception sections are absent and DBMS_OUTPUT.PUT_LINE() method is used to display a message on the screen. ***



7

Oracle (PL/SQL)

Data Types and Variables Chapter 3 Data Types in PL/SQL: PL/SQL variables, constants and parameters must have a valid data type, which specifies a storage format, constraints, and valid range of values. 1.

CHAR

Alphanumeric values that represent single character or a group of characters. This type of data is used to store fixed-length character strings. Syntax:

2.

CHAR(max_length)

VARCHAR2

This type of data is used to store variable-length character string. Syntax:

VARCHAR2(max_length)

3.

INTEGER or INT

It is used to store integers.

4.

BINARY_INTEGER

Signed integer in range - 2,147,483,648 through 2,147,483,647, represented in 32 bits.

5.

NUMBER

Syntax:

Subtype of integer is number. This type of data is used to store any type of number. Arithmetic operations are done on the number. NUMBER(max_length) or number(precision, scale)

6.

REAL

Floating-point type approximately 18 decimal digits.

7.

DECIMAL(precision, scale)

Example: DECIMAL(18,2)

8.

DATE

This type of data is used to store a date.



8

Oracle (PL/SQL) 9.

BOOLEAN

This type of data is used to store true, false. This type of data is used for logical operations.

10.

LONG

This type of data is used to store variable-length character strings like remarks.

11.

BLOB

This type of data is used to store photographs.

12.

BFILE

This type of data is used to store a movie.

13.

RAW

This type of data is used to store data that is in a binaryformat, such as graphics or photographs.

14.

LONGRAW

This type of data is used to store more data that is in a binary format.

NULLs in PL/SQL Null values represent missing or undefined or unknown data. It is not equal to zero value. It should not be used in computation.

Identifier : Name of a variable or column name, table name, function or procedure name is called Identifier. The identifiers consist of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters. Identifier is not case sensitive. You cannot use a reserved keyword as an identifier.

The PL/SQL Delimiters A delimiter is a symbol with a special meaning. Following is the list of delimiters in PL/SQL: Delimiter

Description

+, -, *, / Addition, subtraction, multiplication, division % Attribute indicator ' Character string delimiter Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


9

Oracle (PL/SQL) . (,) : , " = @ ; := => || ** <<, >> /*, */ -.. <, >, <=, >= <>, '=, ~=, ^=

Component selector Expression or list delimiter Host variable indicator Item separator Quoted identifier delimiter Relational operator Remote access indicator Statement terminator Assignment operator Association operator Concatenation operator Exponentiation operator Label delimiter (begin and end) Multi-line comment delimiter (begin and end) Single-line comment indicator Range operator Relational operators Different versions of NOT EQUAL

Comment Line: Comments are used for better understanding. The PL/SQL compiler ignores the comment lines. There are two types of comments are available namely single line comment and multiple-line comment. Syntax of Single Line Comment

-- This is single line comment. where -- is single-line comment delimiter Syntax of Multi-line comment

/* This a Multi-line comment */ where /* and */ is multi-line comment delimiter.



10

Oracle (PL/SQL) Variables A variable is nothing but a name given to a storage area that our programs can manipulate. Each variable has a specific data type, which determines the size and memory. The name of a variable consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters. By default, variable names are not case-sensitive. You cannot use a reserved keyword as a variable name.

Importance of a variable: Variable is used to store a value temporarily during the program. It can be maniplated also if required.

Types of variables: There are two types of variables namely Host ( Blind or Global ) Variable and Local Variable. The scope and longevity of Host variable is large, but local variable works within a limited section. Example for Host Variable:

g_monthly_sal NUMBER(9,2) DEFAULT 5000.00 It is declared before begin keyword. To reference host variables, you must prefix the reference with a colon (:) to distinguish them from Local variables. :g_monthly_sal := v_sal/12; To print the Host Variable, we use Print command as shown below: PRINT g_monthly_sal Instead of PRINT command, you can use an DBMS_OUTPUT.PUT_LINE(g_monthly_sal);

option,

Example for Local Variable:

v_orderdata DATE:= SYSDATE +7; Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


11

Oracle (PL/SQL) There are four types of variables - scalar, composite, reference, and LOB (Large Object) depending upon usage. Scalar

This data type holds a single value. For example the data which is in a column of a table, NUMBER, DATE, or BOOLEAN.

Composite Data items that have internal components that can be accessed

individually. For example, collections and records (group of fields of a table).

Reference This data type holds values called pointers, that shows addresses of

other program items. LOB

This data type holds values, called locators, that sjpecify the locations of large objects (text, graphic images, video clips, and sound waveforms) that are stored.

Declaring variables You need to declare all variables in the declaration section before it is used. The syntax for declaring a variable is given below: variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] Where, variable_name is a valid identifier, datatype must be a valid data type constant

It constraints the variable so that its value cannot change and it must be initialized.

datatype

It refers the data type such as char, number, so on. (NOT NULL variables must be initialized.) It is any expression that can be involving operators and functions.

expr



12

Oracle (PL/SQL) Declaring and initialization of a Variable The initialization of variables is shown below :

fiscal_year NUMBER(2) :=97; hiredate DATE := '22-NOV-2014'; name:= 'DHANA LAXMI'; When you provide a size, scale or precision limit with the data type, it is called a constrained declaration. Constrained declarations require less memory than unconstrained declarations. Note: You can use the symbol := to initialize, or assign an initial value, to a variable.

You must initialize a variable that is defined as NOT NULL. Date and Character type data must be enclosed in single quotation marks. In constant declarations, the keyword CONSTANT must precede the type specifier. Example:

v_sal CONSTANT REAL := 50000.00;

Note: REAL is subtype of number data type.

Column variables may be prefixed with v for variable and g for global variable. Example:

v_hiredate date; g_deptno number (2) NOT NULL := 10; Another way to assign value to a variable is shown below: select sal*0.10 into v_bonus from emp where empno = 7369; Then you can use the variable v_bonus in another computation.



13

Oracle (PL/SQL) Different type of assigning values to variables: PL/SQL does not have input/output capability of its own. You must rely on the environment in which PL/SQL is executing for passing values into and out of a PL/SQL block. Example:

Program to illustrate input operation of PL/SQL block: Neha Program 2:

-- To illustrate input operation in PL/SQL block. -- File Name: 2.sql SET SERVEROUTPUT ON SET VERIFY OFF DECLARE v_sal NUMBER(9,2) := &p_annual_sal; BEGIN v_sal := v_sal/12; DBMS_OUTPUT.PUT_LINE('The monthly salary is ' || v_sal ); END; / SET VERIFY ON INPUT: 48000 OUTPUT:

Enter value for p_annual_sal: 48000 The monthly salary is 4000 PL/SQL procedure successfully completed.

***



14

Oracle (PL/SQL)

Control Structure Chapter 4 In addition to SQL commands, PL/SQL can also process data using flow of control statements. The flow of control statements can be classified under the following categories: • • •

Conditional control Iterative control Sequential control

Conditional Statement Sequence of statements can be executed based on some condition using the IF statement. There are three forms of IF statements, namely, IF then statement, IF THEN ELSE and IF THEN ELSIF. The simplest form of an IF statement is the IF THEN statement. First form of if statement:



15

Oracle (PL/SQL) Syntax IF condition THEN

sequence of statements; END IF;

The sequence of statements is executed only if the condition evaluates to true. If it is false, then, the control passes to the statement after ‘END IF’. Note: This type of form is useful when only one option is available. Example:

Neha Program 3:

-- Program to find whether a person is major. -- File Name: 3.sql SET SERVEROUTPUT ON DECLARE i NUMBER:= 25; BEGIN IF i >= 18 THEN DBMS_OUTPUT.PUT_LINE('The person is major.'); END IF; END; / OUTPUT:

The person is major. PL/SQL procedure successfully completed.



16

Oracle (PL/SQL) Second form of IF statement: Syntax IF condition THEN

statement1; ELSE

statement2; END IF; An ‘ELSE’ clause in the ‘IF THEN’ statement defines what is to be done if the condition is false. Note: This type of form is useful when two options are available.

To illustrate if else statement: Neha Program 4:

-- Program to find whether a student is passed or failed. -- File Name: 4.sql SET SERVEROUTPUT ON DECLARE marks NUMBER:= 65; BEGIN IF marks >= 35 THEN DBMS_OUTPUT.PUT_LINE('Pass'); ELSE DBMS_OUTPUT.PUT_LINE('Fail'); END IF; END; / INPUT: marks = 65 OUTPUT:

Pass PL/SQL procedure successfully completed. INPUT: marks = 15 OUTPUT:

Fail PL/SQL procedure successfully completed. Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


17

Oracle (PL/SQL) To illustrate IF ELSE statement: Step 1: Create table accounts using following statement:

CREATE TABLE accounts (account_id NUMBER(4), bal NUMBER(4)); Table created. To display the structure of accounts table:

DESCRIBE accounts; Name Null? Type ----------------------------------------- -------- ---------------------------ACCOUNT_ID NUMBER(4) BAL NUMBER(4) To insert data into accounts table:

INSERT INTO accounts VALUES(1,6000); Similarly enter the following data: 2 800 3 6500 4 400 5 5000 Display the records of accounts table:

SELECT * FROM accounts; ACCOUNT_ID BAL ---------- -------------------- 1 6000 2 800 3 6500 4 400 5 5000 Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


18

Oracle (PL/SQL) Step 2:

Create table tempaccounts as shown below:

CREATE TABLE tempaccounts (account_id NUMBER(4), bal NUMBER(4), remarks VARCHAR2(20)); Table created. DESC tempaccounts; Name Null? Type ----------------------------------------- -------- ------------ACCOUNT_ID NUMBER(4) BAL NUMBER(4) REMARKS VARCHAR2(20) Step 3: To write a program to illustrate IF ELSE statement: Neha Program 5:

SET SERVEROUTPUT ON -- Program to illustrate if else statements. -- Name of the file: 5.sql DECLARE acct_balance NUMBER(4); acct CONSTANT NUMBER(4) := 3; debit_amt CONSTANT NUMBER(4) := 500; BEGIN SELECT bal INTO acct_balance FROM accounts WHERE account_id = acct FOR UPDATE OF bal;



19

Oracle (PL/SQL) IF acct_balance >= debit_amt THEN UPDATE accounts SET bal = bal - debit_amt WHERE account_id = acct; ELSE INSERT INTO tempaccounts VALUES(acct, acct_balance, 'Insufficient funds'); -- It inserts account, current balance, and message END IF; COMMIT; END; / Step 4:

Before execution of file the records of accounts:

SELECT * FROM accounts; ACCOUNT_ID BAL ---------- --------------------- 1 6000 2 800

3

6500

4 5

400 5000

Execute the file 5. Display the records after execution:

SELECT * FROM accounts; ACCOUNT_ID BAL ---------- -------------------- 1 6000 2 800 3

6000

4 400 5 5000 Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


20


Modify the statement of program as shown below:

acct CONSTANT NUMBER(4) := 4; Before execution of the program the records of accounts and tempaccounts tables:

SELECT * FROM accounts; ACCOUNT_ID BAL ---------- -------------------- 1 6000 2 800 3 6000

4

400

5

5000

SELECT * FROM tempaccounts; no rows selected After the execution of the file the records of accounts and tempaccounts tables:

SELECT * FROM accounts; ACCOUNT_ID BAL ---------- -------------------- 1 6000 2 800 3 6000

4

400

5

5000

SELECT * FROM tempaccounts; ACCOUNT_ID BAL REMARKS ---------- ---------- -------------------------4 400 Insufficient funds Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


21

Oracle (PL/SQL) Third form of IF statement: Syntax

IF condition1 THEN statement1; ELSIF condition2 THEN statement2; ELSE statement3; END IF; An ‘IF THEN ELSIF’ statement can be used to select one of several mutually exclusive alternatives. Note: This type of form is useful when more than two options are available. Example:

Neha Program 6 :

-- Program to illustrate IF - THEN - ELSEIF statement. -- File Name: 6.sql SET SERVEROUTPUT ON SET VERIFY OFF DECLARE sales NUMBER :=&sales; bonus NUMBER; BEGIN IF sales > 50000 THEN bonus := 1500;



22

Oracle (PL/SQL) ELSIF sales > 35000 THEN bonus := 500; ELSE bonus := 100; END IF; sales:=sales+bonus; DBMS_OUTPUT.PUT_LINE('Gross pay = '|| sales); END; / SET VERIFY ON INPUT:

60000

OUTPUT:

Enter value for sales: 60000 Gross pay = 61500 INPUT:

45000

OUTPUT:

Enter value for sales: 45000 Gross pay = 45500 INPUT:

10000

Enter value for sales: 10000 Gross pay = 10100



23

Oracle (PL/SQL) Iterative Control A sequence of statements can be executed any number of times using loop constructs. Loop can be broadly classified as: •

Simple or Basic loop

•

While loop

•

For loop

Simple or Basic loop:

Basic loop is used to provide repetitive actions without overall condition. The Exit statement is used to terminate the loop. The keyword LOOP should be placed before the first statement in the sequence and the keyword END LOOP after the last statement in the sequence. The following example illustrates this concept and the sequence of statements are repeated, until a = 250. Syntax

LOOP sequence of statements; Exit [WHEN condition]; END LOOP;

-- delimiter -- statements -- EXIT statement -- delimiter

Example:

Neha Program 7: -- Program to illustrate simple or basic loop.

-- File Name: 7.sql SET SERVEROUTPUT ON DECLARE a NUMBER := 100; Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


24

Oracle (PL/SQL) BEGIN LOOP a := a + 25; EXIT WHEN a = 250; END LOOP; DBMS_OUTPUT.PUT_LINE(a); END; / OUTPUT: 250 PL/SQL procedure successfully completed. The exit when statement allows us to complete a loop if further processing is undesirable or impossible. Note:

While loop:

The while loop statement includes a condition associated with a sequence of statement. If the condition evaluates to true, then the sequence of statements will be executed, and again control resumes at the top of the loop. If the condition evaluates to false, then the loop is bypassed and the control passes to the next statement. Note: While loop is used to provide iterative control of actions based on a condition.

Syntax LOOP sequence_of_statements;

WHILE

END LOOP; Example:

Neha Program 8:

-- Program to illustrate while loop and to generate even numbers from 2 to 10. -- File Name: 8.sql SET SERVEROUTPUT ON



25

Oracle (PL/SQL) DECLARE i NUMBER:=2; BEGIN WHILE i <= 10 LOOP DBMS_OUTPUT.PUT_LINE(i); i:=i+2; END LOOP; END; / OUTPUT: 2 4 6 8 10 PL/SQL procedure successfully completed. For loop:

The number of iterations for a WHILE loop is unknown until the loop terminates, whereas the number of iterations in a FOR loop is known before the loop gets executed. The FOR loop statement specifies a range of integers, to execute the sequence of statements once for each integer. Note: For loop provides iterative control of actions based on a count.

Syntax FOR counter in [Reverse] lowerbound .. upperbound LOOP

sequence_of_statements; END LOOP;

By default, iteration proceeds from lowerbound to upperbound. If we use the optional keyword Reverse, then, iteration proceeds downwards from upperbound to lowerbound. Note: In a FOR loop counter is implicitly declared.



26

Oracle (PL/SQL) Neha Program 9:

-- Program to print 1 to 5 in ascending order using for loop -- File Name: 9.sql SET SERVEROUTPUT ON BEGIN FOR i in 1 .. 5 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP; END; / OUTPUT:

1 2 3 4 5 PL/SQL procedure successfully completed. Note: In the for loop i variable need not be declared. Neha Program 10:

-- Program to print 1 to 5 in reverse order using for loop -- File Name: 10.sql SET SERVEROUTPUT ON BEGIN FOR i IN REVERSE 1 .. 5 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP; END; / OUTPUT:

5 4 3 2 1 PL/SQL procedure successfully completed. Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


27

Oracle (PL/SQL) Neha Program 11:

SET SERVEROUTPUT ON -- Program to print multiple table of given number using for loop -- File Name: 11.sql SET SERVEROUTPUT ON SET VERIFY OFF DECLARE x NUMBER:= &x; k NUMBER; BEGIN FOR i IN 1 .. 10 LOOP k:= i*x; DBMS_OUTPUT.PUT_LINE(i||' x '||x||' = '||k); END LOOP; END; / SET VERIFY ON INPUT:

8 OUTPUT:

Enter value for x: 8 1x8=8 2 x 8 = 16 3 x 8 = 24 4 x 8 = 32 5 x 8 = 40 6 x 8 = 48 7 x 8 = 56 8 x 8 = 64 9 x 8 = 72 10 x 8 = 80



28

Oracle (PL/SQL) Sequential Control:

The GOTO statement is used to transfer control to a labeled statement or block. Before GOTO statement if statement must be used. This type of control is called Sequential Control. The following program illustrates the usage of GOTO statement. Neha Program 12:

-- To illustrate GOTO statement. -- Name of the file: 12.sql SET SERVEROUTPUT ON SET VERIFY OFF DECLARE marks NUMBER(3):= &marks; BEGIN IF marks >= 35 THEN GOTO result; ELSE DBMS_OUTPUT.PUT_LINE('Fail'); RETURN; END IF; <> DBMS_OUTPUT.PUT_LINE('Pass'); RETURN; END; / SET VERIFY ON Note: If return keyword is not specified it will execute both label one by one. INPUT & OUTPUT:

Enter value for marks: 75 Pass PL/SQL procedure successfully completed. INPUT & OUTPUT: Enter value for marks: 12 Fail PL/SQL procedure successfully completed. Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


29

Oracle (PL/SQL) Case Construct:

The Case Construct is used to choose among several values or types of action. The CASE expression evaluates a condition and returns a value from each case. The syntax for CASE statement is given below :

CASE selector WHEN 'value1' THEN statement 1; WHEN 'value2' THEN statement 2; WHEN 'value3' THEN statement 3; ... ELSE statementn; -- default case END CASE; Flow Diagram:



30

Oracle (PL/SQL) The following program will illustrate the usage of case statement : Example: Neha Program 13:

-- To illustrate case statement. -- File Name: 13.sql -- Input the values for empno, 7369, 7900, 1311 and observe the output. SET VERIFY OFF SET SERVEROUTPUT ON DECLARE v_empno emp.empno%TYPE:=&v_empno; emp.ename%TYPE; v_ename emp.sal%TYPE; v_sal BEGIN CASE WHEN v_empno=7369 THEN BEGIN SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE('EMPNO '||'EMPNAME '||'SAL'); DBMS_OUTPUT.PUT_LINE(v_empno||' '||v_ename||' '||v_sal); END; WHEN v_empno=7900 THEN BEGIN SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE('EMPNO '||'EMPNAME '||'SAL'); DBMS_OUTPUT.PUT_LINE(v_empno||' '||v_ename||' '||v_sal); END; ELSE DBMS_OUTPUT.PUT_LINE('No such empno exists.'); END CASE; END; / SET VERIFY ON Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


31

Oracle (PL/SQL) START 13 INPUT & OUTPUT:

Enter value for v_empno: 7369 EMPNO EMPNAME SAL 7369 SMITH 800 PL/SQL procedure successfully completed. START 13 INPUT & OUTPUT:

Enter value for v_empno: 7900 EMPNO EMPNAME SAL 7900 JAMES 950 PL/SQL procedure successfully completed. START 13 INPUT & OUTPUT:

Enter value for v_empno: 1311 No such empno exists. PL/SQL procedure successfully completed. ***



32

Oracle (PL/SQL)

Cursors Chapter 5 The %TYPE Attribute: %TYPE is a variable attribute which is used to declare a variable which is same as the type of a column of a table. Syntax:

variable_name

table_name.column_name%TYPE;

Example 1:

id_num

emp.empno%TYPE;

To illustrate %TYPE attribute : Step 1: Create item table:

CREATE TABLE item (ordid NUMBER (3), itemid NUMBER (2)); Step 2:

Display the structure of item table:

DESCRIBE item Name Null? Type ----------------------------------------- -------- ---------ORDID NUMBER(3) ITEMID NUMBER(2)



33

Oracle (PL/SQL) Step 3: Write the below program and execute it.

Neha Program 14:

-- Program to illustrate %TYPE using simple or basic loop. -- File Name: 14.sql DECLARE v_ordid item.ordid%TYPE := 786; v_counter NUMBER(2) := 1; BEGIN LOOP INSERT INTO item(ordid, itemid) values(v_ordid, v_counter); v_counter := v_counter +1; EXIT WHEN v_counter > 10; END LOOP; END; / Step 4: Display the result.

SELECT * FROM item; OUTPUT:

ORDID ITEMID ---------- ---------786 1 786 2 786 3 786 4 786 5 786 6 786 7 786 8 786 9 786 10 10 rows selected. Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


34

Oracle (PL/SQL) Example:

Neha Program 15:

-- To illustrate while loop. -- File Name : 15.sql SET VERIFY OFF -- The above command is not display details on the screen ACCEPT v_ordid PROMPT 'Enter the order number: ' ACCEPT items PROMPT 'Enter the number of items in this order: ' DECLARE v_count NUMBER (2) := 1; BEGIN WHILE v_count <= &items LOOP INSERT INTO item (ordid, itemid) VALUES(&v_ordid, v_count); v_count := v_count + 1; END LOOP; COMMIT; END; / SET VERIFY ON INPUT:

Enter the order number: 586 Enter the number of items in this order: 5 PL/SQL procedure successfully completed. OUTPUT: SELECT * FROM item; ORDID ITEMID ---------- ---------586 1 586 2 586 3 586 4 586 5 Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


35

Oracle (PL/SQL) Example:

Neha Program 16 :

-- To illustrate %TYPE variable -- File Name: 16.sql SET SERVEROUTPUT ON DECLARE tsal

emp.sal%TYPE;

BEGIN SELECT sal INTO tsal FROM emp WHERE empno=7788; DBMS_OUTPUT.PUT_LINE('Salary of empno 7788 = '||tsal); END; / OUTPUT:

Salary of empno 7788 = 3000 PL/SQL procedure successfully completed.

To illustrate the use of %TYPE attribute. Step 1:

Create pay table using following command :

CREATE TABLE pay (code NUMBER(3), name VARCHAR2(15), basic NUMBER(4), da NUMBER(4), gross NUMBER(5), ded NUMBER(4), net NUMBER(4));



36

Oracle (PL/SQL) DESCRIBE pay; Name Null? Type ----------------------------------------- -------- ------------CODE NUMBER(3) NAME VARCHAR2(15) BASIC NUMBER(4) DA NUMBER(4) GROSS NUMBER(5) DED NUMBER(4) NET NUMBER(4) Step 2:

Insert the below data into the pay table:

INSERT INTO pay (code, name, basic) VALUES(101, 'PRIYA', 9000); Similarly insert the following data into the table. 102 103 104 105

SONIA SHILPA RAMBHA RANI

6000 2000 8000 4000

SELECT * FROM pay; set linesize 2000 set pagesize 2000 SELECT code, name, basic FROM pay;

CODE NAME BASIC ---------- --------------- ---------------101 PRIYA 9000 102 SONIA 6000 103 SHILPA 2000 104 RAMBHA 8000 105 RANI 4000



37


Create the following PL/SQL file as shown below: Neha Program 17 :

-- To update the table pay and update da with 10% and deduction 100 and display them. -- File Name: 17.sql SET SERVEROUTPUT ON DECLARE sno

pay.code%TYPE:=101;

BEGIN WHILE sno<= 105 LOOP UPDATE pay SET da = basic*(10/100); UPDATE pay SET gross = (basic+da); UPDATE pay SET ded =100; UPDATE pay SET net = (gross - ded); sno:=sno+1; END LOOP; COMMIT; END; /



38


set linesize 2000 set pagesize 2000 SELECT * FROM pay; CODE NAME BASIC DA GROSS DED NET ---------- --------------- ---------- ---------- ---------- ---------- ------------101 PRIYA 9000 900 9900 100 9800 102 SONIA 6000 600 6600 100 6500 103 SHILPA 2000 200 2200 100 2100 104 RAMBHA 8000 800 8800 100 8700 105 RANI 4000 400 4400 100 4300 5 rows selected. Example 2

name ename

VARCHAR2(15); name%TYPE:= 'IqBal AhMed';

Neha Program 18:

-- To illustrate %TYPE variable. -- File Name: 18.sql SET SERVEROUTPUT ON DECLARE name VARCHAR2(15); ename name%TYPE:= 'IqBal AhMed'; BEGIN DBMS_OUTPUT.PUT_LINE('Name in UPPER CASE is '||upper(ename)); DBMS_OUTPUT.PUT_LINE('Name in lower case is '||lower(ename)); DBMS_OUTPUT.PUT_LINE('Name in Title Case is '||initcap(ename)); END; / Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


39

Oracle (PL/SQL) OUTPUT:

Name in UPPER CASE is IQBAL AHMED Name in lower case is iqbal ahmed Name in Title Case is Iqbal Ahmed PL/SQL procedure successfully completed. Note: If we increase the length of name, the other variables become longer too. Composite Datatypes: Creating a PL/SQL Record: Syntax:

TYPE type_name IS RECOR (field_declaration1, field_declaraton2, ..); Example 1:

TYPE emp_record_type IS RECOR (ename VARCHAR2 (10), job VARCHAR2 (9), sal NUMBER (7,2); emp_record

emp_record_type;

Example 2:

TYPE emp_record_type IS RECOR (empno NUMBER (4) NOT NULL := 100, emp.ename%TYPE, ename job emp.job%TYPE); emp_record

emp_record_type;



40

Oracle (PL/SQL) The %ROWTYPE Attribute

This type of attribute is used to declare a row which is same as a row of a table. Syntax 1: rowtype_variable table_name%ROWTYPE; Example emp_rec emp%ROWTYPE; -- It declares a record variable of emp table type.

Cursor A cursor is another type of variable that points a row of data or multiple-rows of data. It is similar to view. Oracle creates a memory area, known as context area, for processing an SQL statement, which contains all information needed for processing the statement. 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. You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors: Implicit Cursor: Whenever you issue a SQL statement, the Oracle Server • opens an area of memory in which the command is executed. This area is called an implicit cursor.

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it. Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected. Explicit Cursor: Explicit Cursors are explicitly declared by the programmer. • Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


41

Oracle (PL/SQL) SQL Cursor Attributes: SQL%ROWCOUNT : Number of rows affected by the most recent SQL statement

(an integer value) SQL%FOUND:

Boolean attribute that evaluates to TRUE if the most recent SQL statement affects one or more rows.

SQL%NOTFOUND:

Boolean attribute that evaluates to TRUE if the most recent SQL statement does not affect any rows.

SQL%ISOPEN:

Always evaluates to FALSE because PL/SQL closes implicit cursors immediately after they are executed.

Cursor Declaration: Syntax:

CURSOR IS SELECT statement; Example:

CURSOR emp_cursor IS SELECT * FROM emp; ROWTYPE variable Declaration: Syntax :

rowtype_variable cursor_name%ROWTYPE; Example

emp_record

emp_cursor%ROWTYPE;



42

Oracle (PL/SQL) The %ROWCOUNT Attribute:

This attribute is used to count number of records in a cursor. Syntax:

CURSOR IS SELECT * FROM ; variable := %ROWCOUNT; Example:

CURSOR emp_cursor IS SELECT * FROM emp; records_processed:= emp_cursor%ROWCOUNT; Cursor Control Commands:

To access the defined cursor we use the following basic cursor control commands: DECLARE OPEN FETCH CLOSE DECLARE

In this section a cursor is declared.

OPEN

This command is used to open a cursor.

FETCH

This command is used to assign values to variables after extracting the values from the cursor.

CLOSE

This command is used to close the cursor at the end of task.



43

Oracle (PL/SQL) Method of inserting data from emp_cursor into emp_record: Syntax:

LOOP FETCH INTO ; EXIT WHEN %NOTFOUND; END LOOP; Al the data of emp_cursor is inserted into emp_record. Syntax to use cursors

DECLARE CURSOR emp_cursor IS SELECT empno, ename FROM emp; emp_record

emp_cursor%ROWTYPE;

BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor ` INTO emp_record; EXIT WHEN emp_cursor%NOTFOUND; ... END LOOP; CLOSE emp_cursor; END; / Note: PL/SQL uses semicolons to terminate each statement in a block. The forward

slash (/) is used to terminate the block.



44

Oracle (PL/SQL) Cursor Demo:

Neha Program 19:

-- To illustrate the usage of cursor. -- File Name: 19.sql SET SERVEROUTPUT ON DECLARE v_empno v_ename v_job v_sal v_deptno

emp.empno%TYPE; emp.ename%TYPE; emp.job%TYPE; emp.sal%TYPE; emp.deptno%TYPE:=20;

CURSOR c1 IS SELECT empno, ename, job, sal, deptno FROM emp WHERE deptno=v_deptno; BEGIN DBMS_OUTPUT.PUT_LINE('EMPNO'||' '||'ENAME'||' '||'JOB'||' '||'SAL'||' '||'DEPTNO'); DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------'); OPEN c1; LOOP FETCH c1 INTO v_empno, v_ename, v_job, v_sal, v_deptno; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno||' '||v_deptno); END LOOP; END; /

'||v_ename||'

'||v_job||'

'||v_sal||'



45

Oracle (PL/SQL) START 19 EMPNO ENAME JOB SAL DEPTNO ---------------------------------------------------------------------------7369 SMITH CLERK 800 20 7566 JONES MANAGER 2975 20 7788 SCOTT ANALYST 3000 20 7876 ADAMS CLERK 1100 20 7902 FORD ANALYST 3000 20 PL/SQL procedure successfully completed. To illustrate Cursor Application: Step 1:

Create nehacompany table as shown below:

CREATE TABLE nehacompany (empno NUMBER(4), ename VARCHAR2 (10), sal NUMBER(7,2)); DESCRIBE nehacompany Name Null? Type ----------------------------------------- -------- ------------EMPNO NUMBER(4) ENAME VARCHAR2(10) SAL NUMBER(7,2) Enter the below data into the table nehacompany.

EMPNO ENAME SAL ---------- ---------- -----------------1 SONI 3000 2 MONI 1500 3 JANI 4000 4 VANI 1000 Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


46

Oracle (PL/SQL) Create tempnehacompany table as shown below:

CREATE TABLE tempnehacompany (empno NUMBER(4), ename VARCHAR2(10), sal NUMBER(7,2), bonus NUMBER(7,2), net NUMBER(7,2)); DESCRIBE tempnehacompany Name Null? Type ----------------------------------------- -------- ------------EMPNO NUMBER(4) ENAME VARCHAR2(10) SAL NUMBER(7,2) BONUS NUMBER(7,2) NET NUMBER(7,2) STEP 2:

To create the following file: Neha Program 20:

-- To illustrate cursor application. -- File Name: 20.sql SET SERVEROUTPUT ON DECLARE CURSOR bonus_cursor IS SELECT empno, ename, sal FROM nehacompany; bonus_record v_bonus v_net

bonus_cursor%ROWTYPE; nehacompany.sal%TYPE; nehacompany.sal%TYPE;



47

Oracle (PL/SQL) BEGIN OPEN bonus_cursor; LOOP FETCH bonus_cursor INTO bonus_record; EXIT WHEN bonus_cursor%NOTFOUND; IF bonus_record.sal <= 2500 THEN v_bonus := bonus_record.sal*10/100; v_net := bonus_record.sal + v_bonus; INSERT INTO tempnehacompany(empno, ename, sal, bonus, net) VALUES(bonus_record.empno, bonus_record.ename, bonus_record.sal, v_bonus, v_net); END IF; END LOOP; CLOSE bonus_cursor; END; / Step 3:

Execute the file 20. Step 4:

SELECT * FROM tempnehacompany; EMPNO ENAME SAL BONUS NET ---------- ---------- ---------- ---------- ---------- ----------------2 MONI 1500 150 1650 4 VANI 1000 100 1100



48

Oracle (PL/SQL) To write a program to display names and salaries of first five employees who are getting high salaries from emp table and total salary using simple loop. Neha Program 21:

-- Program to display five top most employees’ salaries and total of them. -- File Name: 21.sql SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor IS SELECT empno, ename, sal FROM emp ORDER BY sal DESC; record_emp emp_cursor%rowtype; emp.sal%type:=0; totalsal BEGIN DBMS_OUTPUT.PUT_LINE ('EMPNO '||'ENAME '||'SAL '); OPEN emp_cursor; LOOP FETCH emp_cursor INTO record_emp; EXIT WHEN emp_cursor%ROWCOUNT >5 OR emp_cursor%NOTFOUND; totalsal:=totalsal+record_emp.sal; DBMS_OUTPUT.PUT_LINE (record_emp.empno || ' '|| record_emp.ename||' '|| record_emp.sal); END LOOP; DBMS_OUTPUT.PUT_LINE ('Total salary of five top employees '||totalsal); CLOSE emp_cursor; END; / OUTPUT:

EMPNO ENAME SAL 7839 KING 5000 7902 FORD 3000 7788 SCOTT 3000 7566 JONES 2975 7698 BLAKE 2850 Total salary of five top employees 16825 Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


49

Oracle (PL/SQL) Write a program to extract data from a table imposing a condition and insert into a table. Step 1:

To create payment_table:

CREATE TABLE payment_table (cust_id NUMBER(4) NOT NULL, name VARCHAR2(15), payment NUMBER(4), total_due NUMBER(4)); `DESCRIBE payment_table; Name Null? Type ----------------------------------------- -------- --------------CUST_ID NOT NULL NUMBER(4) NAME VARCHAR2(15) PAYMENT NUMBER(4) TOTAL_DUE NUMBER(4) Step 2:

To insert the data into the payment_table:

INSERT INTO payment_table VALUES(781, 'KAVITA', 500, 1500); Similarly enter the following data into payment_table. 782 783 784 785 786

SAVITA 8000 VANITA 7000 SUJATA 100 PUJITA 2000 NAVANITA 400

9000 7000 600 2000 1600



50

Oracle (PL/SQL) SELECT * FROM payment_table; CUST_ID NAME PAYMENT TOTAL_DUE ---------- --------------- ---------- ------------------------------------------ 781 KAVITA 500 1500 782 SAVITA 8000 9000 783 VANITA 7000 7000 784 SUJATA 100 600 785 PUJITA 2000 2000 786 NAVAVITA 400 1600 Step 3: Create the following table.

CREATE TABLE underpay_table (cust_id NUMBER (4), name VARCHAR2 (15), payment NUMBER (4), total_due NUMBER (4), remarks VARCHAR2 (10)); Display the structure of underpay_table.

DESC underpay_table Name Null? Type ----------------------------------------- -------- ------------CUST_ID NUMBER(4) NAME VARCHAR2(15) PAYMENT NUMBER(4) TOTAL_DUE NUMBER(4) REMARKS VARCHAR2(10)



51


Write the following file : Neha Program 22:

-- To find the outstanding balance of the customers. -- File Name: 22.sql SET SERVEROUTPUT ON DECLARE CURSOR payment_cursor IS SELECT * FROM payment_table; payment_record payment_cursor%ROWTYPE; BEGIN OPEN payment_cursor; LOOP FETCH payment_cursor INTO payment_record; EXIT WHEN payment_cursor%NOTFOUND; IF payment_record.payment < payment_record.total_due THEN INSERT INTO underpay_table(cust_id, name, payment, total_due, remarks) VALUES (payment_record.cust_id, payment_record.name, payment_record.payment, payment_record.total_due, 'STILL OWES'); END IF; END LOOP; COMMIT; CLOSE payment_cursor; END; /



52


START 22 Step 5:

SELECT * FROM underpay_table; CUST_ID NAME PAYMENT TOTAL_DUE REMARKS --------- --------------- ---------- ---------- -----------------------------------------781 KAVITA 500 1500 STILL OWES 782 SAVITA 8000 9000 STILL OWES 784 SUJATA 100 600 STILL OWES 786 NAVANITA 400 1600 STILL OWES Advantage of FOR LOOP:

• • •

The cursor FOR loop is a shortcut to process explicit cursors. The record is implicitly declared. The cursor is opened, row is fetched once for each iteration in the loop, and the cursor is closed automatically when all rows have been processed.

Note: The cursor must be declared explicitly. Syntax of FOR LOOP:

FOR record_name IN cursor_name LOOP statement1; statement2; ... END LOOP; Note: In the syntax, record_name is the implicitly declared record and cursor_name

is the previously explicitly declared cursor by the programmer.



53

Oracle (PL/SQL) To print on the screen a list of employees of deptno 30 having details about empno, ename, job, sal, deptno from emp table using for loop. Step 1:

Create a the following file to execute. Neha Program 23:

-- To display a list of employees of deptno 30 from emp table. -- Name of the file: 23.sql SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor IS SELECT empno, ename, job, sal, deptno FROM emp; BEGIN dbms_output.put_line('Employee No. '|| 'Name '|| 'Job ' || 'Salary '|| 'Department No.'); For emp_record IN emp_cursor LOOP -- implicit open and implicit fetch occurs IF emp_record.deptno = 30 THEN dbms_output.put_line(emp_record.empno||' '||emp_record.ename||' '||emp_record.job||' '||emp_record.sal||' '||emp_record.deptno); END IF; END LOOP; -- implicit close occurs END; /



54

Oracle (PL/SQL) OUTPUT:

Employee No. 7499 7521 7654 7698 7844 7900

Name ALLEN WARD MARTIN BLAKE TURNER JAMES

Job SALESMAN SALESMAN SALESMAN MANAGER SALESMAN CLERK

Salary 1600 1250 1250 2850 1500 950

Department No. 30 30 30 30 30 30

Cursor FOR Loop using Subqueries: To write a program in which cursor is not declared for FOR LOOP. Neha Program 24:

-- To display the names of employees of Department No. 20 -- File Name: 24.sql SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.PUT_LINE('Emplyee Name '|| 'Department No.'); For emp_record IN (select ename, deptno from emp) LOOP IF emp_record.deptno = 20 THEN DBMS_OUTPUT.PUT_LINE(emp_record.ename||' END IF; END LOOP;

'||emp_record.deptno);

END; / OUTPUT:

Emplyee Name Department No. SMITH 20 JONES 20 SCOTT 20 ADAMS 20 FORD 20 Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


55

Oracle (PL/SQL) Cursors with Parameters: Syntax:

CURSOR cursor_name[(parameter_name datatype, ...)] IS select_statement; Pass parameter values to a cursor when the cursor is opened and the query is executed. Note: Open an explicit cursor several times with a different active set each time. To write a program to increase 10% salaries of a department employees using a cursor with a parameter. Step 1:

CREATE TABLE empincre (empno NUMBER(4), name VARCHAR2(10), sal NUMBER(7,2), deptno NUMBER(2)); DESCRIBE empincre Name Null? Type ----------------------------------------- -------- -------------EMPNO NUMBER(4) NAME VARCHAR2(10) SAL NUMBER(7,2) DEPTNO NUMBER(2) Step 2: Enter the following data into empincre table:

empno 725 726 727 728 729 730

name Nishatha Shalini Salman Goutham Navya Pruthvi

sal 2500 4000 2000 7000 6500 8000

deptno 10 20 30 10 20 30



56


Create the following program, save it, and execute it. Neha Program 25:

-- To increase the salaries of employees of a department using cursor with a parameter. -- File Name: 25.sql. SET VERIFY OFF SET SERVEROUTPUT ON ACCEPT v_deptno PROMPT 'Enter the Department Number to increase the salary: ' DECLARE v_deptno empincre.deptno%TYPE; CURSOR empincre_cursor(v_deptno number) IS SELECT empno, name, deptno, sal FROM empincre WHERE deptno= &v_deptno FOR UPDATE OF sal NOWAIT; -- Only datatype of v_deptno is defined but not its length. empincre_record empincre_cursor%ROWTYPE; BEGIN OPEN empincre_cursor(v_deptno); LOOP FETCH empincre_cursor INTO empincre_record; EXIT WHEN empincre_cursor%NOTFOUND; UPDATE empincre SET sal = empincre_record.sal*1.10 WHERE CURRENT OF empincre_cursor; END LOOP; COMMIT; CLOSE empincre_cursor; COMMIT; END; / SET VERIFY ON Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


57

Oracle (PL/SQL) INPUT:

START 25 Enter the Department Number to increase the salary: 10 PL/SQL procedure successfully completed. OUTPUT:

SELECT * FROM empincre; EMPNO NAME SAL DEPTNO ---------- ---------- ---------- ---------- ------------2750 10 725 Nishatha 726 Shalini 4000 20 727 Salman 2000 30 7700 10 728 Goutham 729 Navya 6500 20 730 Prudhvi 8000 30 ***



58

Oracle (PL/SQL)

Exception Chapter 6 The Exception Section: The Exception section is an optional part of any PL/SQL block. The run time error is called Exception. If it is not handled properly, the block will be terminated. These Exceptions may be predefined or user-defined. Predefined exception:

An exception is raised implicitly when a PL/SQL program violates Oracle rule. The following are the predefined exceptions supported by PL/SQL. Predefined Exception

Explanation

no_data_found

This exception is raised when select statement returns no rows.

cursor_already_open

This exception is raised when we try to open a cursor which is already opened.

dup_val_on_index

This exception is raised when we insert duplicate values in a column, which is defined as unique index.

storage_error

This exception is raised if PL/SQL runs out of memory or if the memory is corrupted.

program_error

This exception is raised if PL/SQL has an internal problem.

zero_divide

This exception is raised when we try to divide a number by zero.

invalid_cursor

This exception is raised when we violate cursor operation. For example when we try to close a cursor which is not opened.



59

Oracle (PL/SQL) login_denied

This exception is raised when we try to enter Oracle using invalid username/password.

invalid_number

This exception is raised if the conversion of a character string to a number fails because the string does not represent a valid number. For example, inserting ‘john’ for a column of type number will raise this exception.

too_many_rows

Raised when the select into statement returns more than one row.

Raising Exceptions: Exception is raised in a block by using the command RAISE. Exception can be raised explicitly by the programmer or implicitly by the database server. Syntax

BEGIN DECLARE exception_name EXCEPTION; BEGIN IF condition THEN RAISE exception_name; END IF; EXCEPTION WHEN exception_name THEN statement; END; END; /



60

Oracle (PL/SQL) Syntax for predefined exception is as follows:

BEGIN sequence_of_statements; EXCEPION WHEN THEN sequence_of_statements; END; Example for predefined NO_DATA_FOUND exception: Step 1:

Create the program file as shown below: Neha Program 26 :

-- To illustrate Predefined Exception no_data_found. -- File Name: 26.sql SET SERVEROUTPUT ON DECLARE emp.sal%TYPE; v_sal BEGIN SELECT sal INTO v_sal FROM emp WHERE empno= 7370; DBMS_OUTPUT.PUT_LINE('Salary of the person = '||v_sal); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Record is not found'); END; / Step 2: INPUT:

Take empno as 7369 which is present in the database. OUTPUT:

START 26 Salary of the person = 800 PL/SQL procedure successfully completed. Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


61

Oracle (PL/SQL) Step 3: INPUT:

Take empno as 7370 which is not present in the database. OUTPUT:

START 26 Record is not found PL/SQL procedure successfully completed. Note: Predefined Exception is not declared in the declaration section. Example for predefined ZERO_DIVIDE exception: Step 1:

Create the following program as shown below: Neha Program 27 :

-- To illustrate Predefined zero_divide Exception. -- Name of the file: 27.sql SET SERVEROUTPUT ON DECLARE a NUMBER:=100; b NUMBER:=0; c NUMBER; BEGIN c:=a/b; DBMS_OUTPUT.PUT_LINE('The value of c ='||c); EXCEPTION WHEN zero_divide THEN DBMS_OUTPUT.PUT_LINE('Divided by zero is error.'); END; / Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


62


Run the program to get following output. OUTPUT:

START 27 Divided by zero is error. PL/SQL procedure successfully completed. Step 3:

Change the value of b as 25. Save the file and run it again. OUTPUT:

START 27 The value of c =4 PL/SQL procedure successfully completed.

User-defined exception: A user_defined exception should be declared in declarative section and raised explicitly by a ‘raise’ statement. The syntax of exception declaration:

EXCEPTION; The syntax for a ‘raise’ statement follows:

RAISE ; The following example is used to illustrate explicit exception and to find out whether commission is zero or null. If it is so then it raises an exception named . Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


63


Create the following program file, save and execute it. Neha Program 28:

-- To illustrate User-defined or Explicit Exception. -- File Name: 28.sql SET SERVEROUTPUT ON DECLARE zero_comm EXCEPTION; v_comm NUMBER(7,2); BEGIN SELECT comm INTO v_comm FROM emp WHERE empno = 7369; IF v_comm = 0 OR v_comm is NULL THEN RAISE zero_comm; END IF; DBMS_OUTPUT.PUT_LINE('The commission is '||v_comm); EXCEPTION WHEN zero_comm THEN DBMS_OUTPUT.PUT_LINE('Raised zero_bonus or null exception.'); END; / OUTPUT:

START 28 Raised zero_bonus or null exception. Step 2:

Change empno as 7499, save it, and execute it. OUTPUT:

START 28 The commission is 300 Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


64

Oracle (PL/SQL) Exercise for User-Designed Exception: Neha Program 29:

-- To illustrate User-Designed Exception. -- File Name: 29.sql -- Note that range of empno starts from 7369 and ends with 7935. SET VERIFY OFF SET SERVEROUTPUT ON DECLARE v_empno emp.empno%TYPE:=&v_empno; emp.ename%TYPE; v_ename emp.sal%TYPE; v_sal Empno_out_of_range EXCEPTION; BEGIN IF v_empno < 7369 OR v_empno > 7935 THEN RAISE Empno_out_of_range; ELSE SELECT empno, ename, sal INTO v_empno, v_ename, v_sal FROM emp WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE('Employee Number is '||v_empno); DBMS_OUTPUT.PUT_LINE('Employee Name is '||v_ename); DBMS_OUTPUT.PUT_LINE('Employee Salary is '||v_sal); END IF; EXCEPTION WHEN Empno_out_of_range THEN DBMS_OUTPUT.PUT_LINE('Employee Number ' || v_empno || ' is out of range.'); END; /



65

Oracle (PL/SQL) INPUT:

START 29 Enter value for v_empno: 7368 OUTPUT:

Employee Number 7368 is out of range. PL/SQL procedure successfully completed. INPUT:


Employee Number is 7499 Employee Name is ALLEN Employee Salary is 1600 PL/SQL procedure successfully completed. INPUT:


Employee Number 7936 is out of range. PL/SQL procedure successfully completed.

Handling Multiple Exceptions: Some times we may have to handle many Exceptions in a block as shown below: Syntax:

EXCEPTION WHEN exception1 THEN statement1; WHEN exception2 THEN statement2; WHEN OTHERS THEN statement3; *** Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


66

Oracle (PL/SQL)

Collections Chapter 7 Introduction to Collections: A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection.

PL/SQL provides three collections types:

•

Index-by table or Associative array

•

Nested table

•

Variable-size array or Varray

Index-By Table or Associative array: It may contain unbounded (unlimited) elements. The subscript may be string or integer. It may be either spare or dense. It is created only in PL/SQL block. It cannot be object type attribute. An index-by table is a set of key-value pairs. Each key is unique and is used to locate the corresponding value. The key can be either an integer or a string. An index-by table is created using the following syntax. Here, we are creating an index-by table named table_name whose keys will be of subscript_type and associated values will be of element_type. TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;

table_name

type_name;



67

Oracle (PL/SQL) Example1:

Following example shows how to create a table to store integer values along with names and later it prints the same list of names. Neha Program 30:

-- To illustrate Index-By Table. -- File Name: 30.sql SET SERVEROUTPUT ON DECLARE TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); salary_list salary; name VARCHAR2(20); BEGIN -- adding elements to the table salary_list('Geetha') := 6200; salary_list('Keerthi') := 7500; salary_list('Sudha') := 1000; salary_list('Ashok') := 7800; -- printing the table name := salary_list.FIRST; WHILE name IS NOT NULL LOOP

DBMS_OUTPUT.PUT_LINE ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name))); name := salary_list.NEXT(name); END LOOP;

END; / Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


68

Oracle (PL/SQL) Save the above program and run it to get the following output: OUTPUT:

@30 Salary of Ashok is 7800 Salary of Geetha is 6200 Salary of Keerthi is 7500 Salary of Sudha is 1000 PL/SQL procedure successfully completed. Example2:

Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the emp table stored in our database as: Neha Program 31:

-- To illustrate index-by table using %ROWTYPE or %TYPE -- File Name: 31.sql SET SERVEROUTPUT ON DECLARE CURSOR ci_emp IS SELECT ename FROM emp; TYPE ci_list IS TABLE of emp.ename%TYPE INDEX BY binary_integer; name_list

ci_list;

counter

INTEGER:=0;



69

Oracle (PL/SQL) BEGIN FOR n IN ci_emp LOOP counter := counter +1; name_list(counter) := n.ename; DBMS_OUTPUT.PUT_LINE('Employee('||counter|| '):'||name_list(counter)); END LOOP; END; / Save the above program and execute it to get the following output: START 31 Employee(1):SMITH Employee(2):ALLEN Employee(3):WARD Employee(4):JONES Employee(5):MARTIN Employee(6):BLAKE Employee(7):CLARK Employee(8):SCOTT Employee(9):KING Employee(10):TURNER Employee(11):ADAMS Employee(12):JAMES Employee(13):FORD Employee(14):MILLER PL/SQL procedure successfully completed. Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


70

Oracle (PL/SQL) Nested Tables: A nested table is like a one-dimensional array with an arbitrary (without restriction) number of elements. However, a nested table differs from an array in the following aspects: •

An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.

•

An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become spare when elements are deleted from it.

A nested table is created using the following syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL]; table_name

type_name;

This declaration is similar to declaration of an index-by table, but there is no INDEX BY clause. A nested table can be stored in a database column and so it could be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database. Example1:

The following example illustrate the use of nested table: Neha Program 32:

-- To illustrate nested table -- File Name: 32.sql SET SERVEROUTPUT ON DECLARE TYPE names_table IS TABLE OF VARCHAR2(10); TYPE grades IS TABLE OF INTEGER;



71

Oracle (PL/SQL) names

names_table;

marks

grades;

total

INTEGER;

BEGIN names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); marks:= grades(98, 97, 78, 87, 92); total := names.count; DBMS_OUTPUT.PUT_LINE('Total '|| total || ' Students'); FOR i IN 1 .. total LOOP DBMS_OUTPUT.PUT_LINE('Student:'||names(i)||', Marks:' || marks(i)); END LOOP; END; / Save the above program and execute it to get the following output: START 32 Total 5 Students Student:Kavita, Marks:98 Student:Pritam, Marks:97 Student:Ayan, Marks:78 Student:Rishav, Marks:87 Student:Aziz, Marks:92

PL/SQL procedure successfully completed.



72


Elements of a nested table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the emp table stored in our database as: Neha Program 33:

-- To illustrate nestedtable using %ROWTYPE or %TYPE -- File Name: 33.sql SET SERVEROUTPUT ON DECLARE CURSOR cn_emp IS SELECT ename FROM emp;

TYPE cn_list IS TABLE of emp.ename%TYPE; name_list cn_list := cn_list(); counter

INTEGER :=0;

BEGIN FOR n IN cn_emp LOOP counter := counter +1; name_list.extend; name_list(counter) := n.ename; DBMS_OUTPUT.PUT_LINE('Employee('||counter||'):'||name_list(counter)); END LOOP; END; / Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


73

Oracle (PL/SQL) Save the above program and run it to get the following output: START 33 Employee(1):SMITH Employee(2):ALLEN Employee(3):WARD Employee(4):JONES Employee(5):MARTIN Employee(6):BLAKE Employee(7):CLARK Employee(8):SCOTT Employee(9):KING Employee(10):TURNER Employee(11):ADAMS Employee(12):JAMES Employee(13):FORD Employee(14):MILLER




74

Oracle (PL/SQL) Variable Size Array or Varray PL/SQL programming language provides a data structure called the VARRAY, which can store a fixed-size sequential collection of elements of the same type. All varrays consists of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element.

Each element in a varray has an index associated with it. It also has a maximum size that can be changed dynamically. Creating a Varray Type:

A varray type is created with the CREATE TYPE statement. You must specify the maximum size and the type of elements stored in the varray. The basic syntax for creating a VARRAY type within a PL/SQL block is:

TYPE varray_type_name IS VARRAY(n) OF Where, varray_type_name is a valid attribute name, n is the number of elements (maximum) in the varray, element_type is the data type of the elements of the array.

Maximum size of a varray can be changed using the ALTER TYPE statement. For example: TYPE namearray IS VARRAY(5) OF VARCHAR2(10); TYPE grades IS VARRAY(5) OF INTEGER; Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


75


The following program illustrates using varrays: Neha Program 34:

-- To illustrate Varray -- File Name: 34.sql SET SERVEROUTPUT ON DECLARE TYPE namesarray IS VARRAY(5) OF VARCHAR2(10); TYPE grades IS VARRAY(5) OF INTEGER; names namesarray; marks grades; total INTEGER; BEGIN names:= namesarray('Dimpy', 'Rahul', 'Roja', 'Ajaz', 'Iqbal'); marks:= grades(98, 97, 78, 87, 92); total:= names.count; DBMS_OUTPUT.PUT_LINE('Total '|| total || ' Students'); FOR i in 1 .. total LOOP DBMS_OUTPUT.PUT_LINE('Student No. '||i||': ' || names(i) || ' Ma rks: marks(i)); END LOOP; END; / Save the above program and run as shown below: START 34 Total 5 Students Student No. 1: Dimpy Marks: 98 Student No. 2: Rahul Marks: 97 Student No. 3: Roja Marks: 78 Student No. 4: Ajaz Marks: 87 Student No. 5: Iqbal Marks: 92

'

||


76



Oracle (PL/SQL) Example 2:

Elements of a varray could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept: Neha Program 35:

-- To illustrate varray using cusors. -- File Name: 35.sql SET SERVEROUTPUT ON DECLARE CURSOR c_emp IS SELECT ename FROM emp; TYPE c_list IS varray (14) OF emp.ename%TYPE; name_list c_list:=c_list(); counter INTEGER:=0; BEGIN FOR n IN c_emp LOOP counter:= counter + 1; name_list.extend; name_list(counter):= n.ename; DBMS_OUTPUT.PUT_LINE('Employee('||counter ||'):'||name_list(counter)); END LOOP; END; / Save the above program and run it to get below output: START 35 Employee(1):SMITH Employee(2):ALLEN Employee(3):WARD Employee(4):JONES Employee(5):MARTIN Employee(6):BLAKE Employee(7):CLARK Employee(8):SCOTT Employee(9):KING Employee(10):TURNER Employee(11):ADAMS Employee(12):JAMES Employee(13):FORD Employee(14):MILLER PL/SQL procedure successfully completed. Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


77

Oracle (PL/SQL) Collection Methods:

PL/SQL provides the built-in collection methods that make collections easier to use. The following table lists the important methods and their purpose: 1.

COUNT:

It returns the number of elements that a collection currently contains. 2.

FIRST:

It returns the first (smallest) index numbers in a collection that user integer subscripts. 3.

LAST:

It returns the last (largest) index numbers in a collection that uses integer subscripts. 4.

NEXT(n):

It returns the index number that succeeds index n. 5.

EXTEND:

Appends one null element to a collection. ***



78

Oracle (PL/SQL)

Transaction Control Chapter 8 Introduction to Transactions:

A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit. The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database). A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly. In an;y multiple user environment trasaction, commit, savepoint, rollback statements are used for data consistency. The COMMIT statement ends the current transaction, making any changes made during that transaction permanent, and visible to other users. The ROLLBACK statement ends the current transaction and undoes any changes made during that transaction. If you make a mistake, such as deleting the wrong row from a table, a rollback restores the original data. If you cannot finish a transaction because an exception is raised or a SQL statement fails, a rollback lets you take corrective action. SAVEPOINT names and marks the current point in the processing of a

transaction. Savepoints let you roll back part of a transaction instead of the whole transaction.



79

Oracle (PL/SQL) Transaction Control in PL/SQL: Controlling Transactions are done using commands COMMIT, ROLLBACK and SAVEPOINT. Syntax

DECLARE ... BEGIN statement; IF condition THEN COMMIT; ELSE ROLLBACK; END IF; END; / Example for Transactions: Setp 1:

To create a database as shown below:

CREATE TABLE account (accno NUMBER, name VARCHAR2(25), balance NUMBER(8,2)); DESC account Name Null? Type ----------------------------------------- -------- ------------ACCNO NUMBER NAME VARCHAR2(25) BALANCE NUMBER(8,2) Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


80


Insert the following data into the account table:

ACCNO 1 2

NAME JANI VANI

BALANCE 5000 1000

SELECT * FROM account ; ACCNO NAME BALANCE ---------- ------------------------- ---------------1 JANI 5000 2 VANI 1000 Step 3:

Write the following program, save, and execute it. Neha Program 36 :

-- To illustrate commit and rollback. -- File name: 36.sql SET VERIFY OFF SET SERVEROUTPUT ON ACCEPT v_accno PROMPT 'Enter the Account Number: ' ACCEPT v_debit PROMPT 'Enter the Amount to be drawn: ' DECLARE v_accno v_minibal v_debit v_balance

account .accno%TYPE:=&v_accno; account .balance%TYPE:=1000; account .balance%TYPE:=&v_debit; account .balance%TYPE;



81

Oracle (PL/SQL) BEGIN SELECT balance INTO v_balance FROM account WHERE accno= v_accno; UPDATE account SET balance= balance - v_debit WHERE accno = v_accno; IF (v_balance-v_minibal > v_debit) OR (v_balance-v_minibal = v_debit) THEN DBMS_OUTPUT.PUT_LINE('Amount Rs. '||v_debit||' is debited from your accout.'); COMMIT; ELSE DBMS_OUTPUT.PUT_LINE('Sorry! Insufficent Balance.'); ROLLBACK; END IF; END; / SET VERIFY ON Step 4:

Execute the program: INPUT:

Account No.=1 and Amount to be drawn = 3000

OUTPUT:

START 36 Enter the Account Number: 1 Enter the Amount to be drawn: 3000 Amount Rs. 3000 is debited from your accout. PL/SQL procedure successfully completed.



82

Oracle (PL/SQL) SELECT * FROM account ; ACCNO NAME BALANCE ---------- ------------------------- ---------1 JANI 2000 2 VANI 1000 Analysis: Here, minimum balance is more than 1000. So, amount 3000 is drawn. Step 5:

Execute the program: INPUT:

Account No. 2 and Amount to be drawn = 100

OUTPUT:

START commitrollback Enter the Account Number: 2 Enter the Amount to be drawn: 100 Sorry! Insufficent Balance. PL/SQL procedure successfully completed. SELECT * FROM account ; ACCNO NAME BALANCE ---------- ------------------------- ---------1 JANI 2000 2 VANI 1000 Analysis: Here, minimum balance is 1000. So, amount 100 is not drawn.

***



83

Oracle (PL/SQL)

Procedures, Packages and Triggers Chapter 9 Introduction to Procedure, Package and a nd Trigger Trigger: An application can be broken into small modules or subprograms which can run independently. independently. A subprogram subprogram is used to perform a particular task. It can be invoked by other calling program. program. A subprogram subprogram can be created created at schema schema (an outline of of a plan) level, inside a package or inside a PL/SQL block. A schema level subprogram is a standalone subprogram. It is created with the CREATE CREATE PROCEDURE or CREATE CREATE FUNCTION statement. It is stored in the database and can be deleted with the DROP PROCEDURE P ROCEDURE or DROP FUNCTION statement. A subprogram created inside a package is a packaged subprogram. It is stored in the database and can be deleted only when the the package is deleted with the DROP PACKAGE statement. PL/SQL subprograms are named PL/SQL blocks. It provides two kinds of subprograms called Procedure and Function. They can be called by many many users. Procedure: This type type of subprogram does not return a value directly, directly, mainly used to

perform an action. Functions: This type of subprogram is invoked whenever it is called. It takes

parameters and after computation, computation, it returns a single value. Parts of a PL/SQL Subprogram Subprogram:

Each PL/SQL subprogram has a name, and may have a parameter list. It will have the following three parts: Declarative Part: It is an optional part. However, the declarative part for a subprogram

does not start with the DECLARE keyword. It contains declarations of types, cursors, constants, variables, variables, exceptions, and nested subprograms. subprograms. These items are local to the subprograms and cease to exist when the subprogram completes execution. execut ion. Neha Ne ha Comp Co mput uter ers, s, Nal N algo gond ndaa - Telan Tel anga gana na - INDI IN DIAA-Mo Mobi bile le:: 944 9 4401 0102 0205 0566


84

Oracle (PL/SQL) Executable Part: This is a mandatory part and contains statements that perform the

designated action. Exception-handling: This is again an optional part. It contains the code that handles

run-time errors. Creating a Procedure Procedure:

A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The syntax for the the procedure statement statement is as follows: CREATE CREATE [OR REPLACE] PROCEDURE procedure_name {(parameter_name {(parameter_ name [IN | OUT | IN OUT] type[, ...])} {IS | AS} BEGIN END procedure name; / Where, procedure_name procedure_name specifies the name of the procedure. procedure. [OR REPLACE] option allows modifying an existing procedure. The optional parameter paramete r list contains name, mode and types of the parameters. IN represents that value will be passed from the outside and OUT represents that this parameter will be used to return a value outside of the procedure. Procedure_body contains the executable part. The AS keyword keyword is used instead of the IS keyword for creating a standalone procedure.



85

Oracle (PL/SQL) Example to illustrate simple procedure:

The following example creates a simple procedure that displays the string 'Hello World!' World!' on the screen when executed. Step 1:

Neha Program 37 :

-- To To illustrate illustra te simple procedure proced ure -- File Name: 37.sql SET SERVEROUTPUT ON CREATE CREATE OR REPLACE PROCEDURE PROCEDUR E greetings AS BEGIN DBMS_OUTPUT.PUT_LINE('Hello World!'); END; / Save the file. Step 2:

START 37 The following message will be displayed. Procedure created. Step 3:

EXECUTE greetings; Hello World! PL/SQL procedure successfully completed.



86


Neha Program 38:

-- To execute a procedure from other file. -- File Name: 38.sql BEGIN greetings; END; / Save the file and run it. START 38 Hello World! PL/SQL procedure successfully completed.

Function: A PL/SQL function is same as a procedure except that it returns a value. Syntax to create a Function:

CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | INOUT] type [, ...])] RETURN return_datatype {IS | AS} BEGIN END [function_name]; /



87

Oracle (PL/SQL) Where, function_name specifies the name of the function.

•

[OR REPLACE] option allows modifying an existing function.

•

The optional parameter list contains name, mode and types of the parameters.

•

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.

•

The function must contain a return statement.

•

RETURN clause specifies that data type you are going to return from the functon.

•

function_body contains the executable part.

•

The AS keyword is used instead of the IS keyword for creating a standalone function.

Example 1:

The following example illustrates creating and calling a standalone function. This function returns the total number of records of emp table. Step 1:

Neha Program 39:

-- To illustrate creating and calling a standalone function -- File Name: 39.sql CREATE OR REPLACE FUNCTION totalRecords RETURN NUMBER IS total NUMBER(2) := 0;



88

Oracle (PL/SQL) BEGIN SELECT count(*) INTO total FROM emp; RETURN total; END; / Save the above program and exectute as follows: START 39 Then SQL prompt, it will produce the following result: Function created. Step 2: Calling a Function:

While creating a function, you give a definition of what the function has to do. To use a function, you will have to call that function to perform the defined task. When a program calles a function, program control is transferred to the called function. A called function performs defiend task and when its return statement is executed or when its last end statement is reached, it returns program control back to the main program. To call a function you simply need to pass the required parameters along with function name.



89

Oracle (PL/SQL) Following program totalRecords from an anonymous block: Neha Program 40:

-- To illustrate the use of function reuse -- File Name: 40.sql SET SERVEROUTPUT ON DECLARE c

NUMBER(2);

BEGIN c := totalRecords(); DBMS_OUTPUT.PUT_LINE('Total no. of Records of emp table: ' || c); END; / Save the above program and exectute as follows: START 40 Then SQL prompt, it will produce the following result: Total no. of Records of emp table: 14 PL/SQL procedure successfully completed.



90

Oracle (PL/SQL) To create a function to convert digits or numbers in words for Indian Rupees:

For this task we have to write two programs to create two functions. And one program to use the functions. Step 1: To create first function which converts a number to words. It is a sub

program. Neha Program 41:

-- To convert a number to word -- File Name: 41.sql CREATE OR REPLACE FUNCTION number_to_word (pnum IN NUMBER) RETURN VARCHAR2 IS rvalue VARCHAR2(50); BEGIN IF pnum = 1 THEN rvalue := 'One'; ELSIF pnum = 2 THEN rvalue := 'Two'; ELSIF pnum = 3 THEN rvalue := 'Three'; ELSIF pnum = 4 THEN rvalue := 'Four'; ELSIF pnum = 5 THEN rvalue := 'Five'; Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


91

Oracle (PL/SQL) ELSIF pnum = 6 THEN rvalue := 'Six'; ELSIF pnum = 7 THEN rvalue := 'Seven'; ELSIF pnum = 8 THEN rvalue := 'Eight'; ELSIF pnum = 9 THEN rvalue := 'Nine'; ELSIF pnum = 10 THEN rvalue := 'Ten'; ELSIF pnum = 11 THEN rvalue := 'Eleven'; ELSIF pnum = 12 THEN Rvalue := 'Twelve'; ELSIF pnum = 13 THEN rvalue := 'Thirteen'; ELSIF pnum = 14 THEN rvalue := 'Fourteen'; ELSIF pnum = 15 THEN rvalue := 'Fifteen'; ELSIF pnum = 16 THEN rvalue := 'Sixteen';



92

Oracle (PL/SQL) ELSIF pnum = 17 THEN rvalue := 'Seventeen'; ELSIF pnum = 18 THEN rvalue := 'Eighteen'; ELSIF pnum = 19 THEN rvalue := 'Nineteen'; ELSIF pnum = 20 THEN rvalue := 'Twenty'; ELSIF pnum = 30 THEN rvalue := 'Thirty'; ELSIF pnum = 40 THEN rvalue := 'Forty'; ELSIF pnum = 50 THEN rvalue := 'Fifty'; ELSIF pnum = 60 THEN rvalue := 'Sixty'; ELSIF pnum = 70 THEN rvalue := 'Seventy'; ELSIF pnum = 80 THEN rvalue := 'Eighty'; ELSIF pnum = 90 THEN rvalue := 'Ninety';



93

Oracle (PL/SQL) ELSE rvalue := ''; END IF; RETURN(rvalue); END; / Save the above program and execute it as shown below: START 41 It displays the following message: Function created. Step 2: To create second (main) funcion which can analyze the number.

Neha Program 42:

-- To split given number -- File Name: 42.sql CREATE OR REPLACE FUNCTION Rupees (pn IN NUMBER) RETURN VARCHAR2 IS vt

VARCHAR2(200);

ntv VARCHAR2(10); vlen NUMBER(2);



94

Oracle (PL/SQL) BEGIN vt := 'Rupees '; ntv := RTRIM (LTRIM (TO_CHAR (pn))); vlen := LENGTH (ntv); IF vlen > 0 AND vlen < 2 THEN

-- single digit

vt := vt || number_to_word (pn); ELSIF vlen > 1 AND vlen < 3 THEN

-- two digit

IF pn < 21 THEN vt := vt || number_to_word (pn); ELSE vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 1, 1) || '0')); vt := vt || ' '; vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 2, 1))); END IF; -- two digit handled.



95

Oracle (PL/SQL) ELSIF vlen > 2 AND vlen < 4 THEN

-- three digit

vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 1, 1))); vt := vt || ' Hundred ';

IF TO_NUMBER (SUBSTR (ntv, 2, 2)) > 0 THEN vt := vt || 'and '; END IF;

IF TO_NUMBER (SUBSTR (ntv, 2, 2)) > 20 THEN vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 2, 1) || '0')); IF TO_NUMBER (SUBSTR (ntv, 2, 1)) > 0 THEN vt := vt || ' '; END IF; vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 3, 1))); ELSE vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 2, 2)));



96

Oracle (PL/SQL) IF TO_NUMBER (SUBSTR (ntv, 2, 1)) > 0 THEN vt := vt || ' '; END IF; END IF; -- three digit handled ELSIF vlen > 3 AND vlen < 5 THEN

-- thousand

vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 1, 1))); vt := vt || ' Thousand ';

IF TO_NUMBER (SUBSTR (ntv, 2, 3)) > 0 THEN IF TO_NUMBER (SUBSTR (ntv, 2, 1)) > 0 THEN vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 2, 1))); vt := vt || ' Hundred '; END IF;

IF TO_NUMBER (SUBSTR (ntv, 3, 2)) > 0 THEN vt := vt || 'and '; END IF;



97

Oracle (PL/SQL) IF TO_NUMBER (SUBSTR (ntv, 3, 2)) > 20 THEN

-- 9999

vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 3, 1) || '0')); IF TO_NUMBER (SUBSTR (ntv, 3, 1)) > 0 THEN vt := vt || ' '; END IF; vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 4, 1))); ELSE vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 3, 2)));

IF TO_NUMBER (SUBSTR (ntv, 3, 1)) > 0 THEN vt := vt || ' '; END IF; END IF; END IF;

-- thousand handled now 99 thousand.

ELSIF vlen > 4 AND vlen < 6 THEN

-- thousand

IF TO_NUMBER(SUBSTR(ntv,1,2)) <= 20 THEN vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 1, 2))); vt := vt || ' Thousand ';



98

Oracle (PL/SQL) ELSE vt := vt || number_to_word(to_number(substr(ntv,1,1)||'0')); vt := vt || ' '|| number_to_word(to_number(substr(ntv,2,1))); vt := vt || ' Thousand '; END IF;

IF TO_NUMBER (SUBSTR (ntv, 3, 3)) > 0 THEN

IF TO_NUMBER (SUBSTR (ntv, 3, 1)) > 0 THEN vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 3, 1))); vt := vt || ' Hundred '; END IF;


vt := vt || 'and ';

END IF;


-- 99999

vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 4, 1) || '0'));



99

Oracle (PL/SQL) IF TO_NUMBER (SUBSTR (ntv, 4, 1)) > 0 THEN vt := vt || ' '; END IF; vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 5, 1))); ELSE vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 4, 2))); IF TO_NUMBER (SUBSTR (ntv, 4, 1)) > 0 THEN vt := vt || ' '; END IF; END IF; END IF; END IF; IF LENGTH (vt) > 7 THEN vt := RTRIM (vt) || ' only.'; ELSE

vt := '';

END IF; RETURN (vt); END; / Save the above program and execute it as follows: Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


100

Oracle (PL/SQL) START 42 It displays the following message: Function created. Step 3: To use the main rupees function:

Neha Program 43:

-- To call Rupees function -- File Name: 43.sql SET VERIFY OFF SET SERVEROUTPUT ON ACCEPT num PROMPT 'Enter a number from 1 to 99999 only: ' DECLARE vstring VARCHAR2(200); num NUMBER:=# BEGIN vstring := Rupees(num); DBMS_OUTPUT.PUT_LINE(vstring); END; / SET VERIFY ON Save the above program and execute it as follows: START 43 Enter a number from 1 to 99999 only: 1311 Rupees One Thousand Three Hundred and Eleven only. PL/SQL procedure successfully completed. Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


101

Oracle (PL/SQL) Package: Package is like a container in which related procedures are stored. Before creating a Package, a related table must be created in the database as follows: CREATE TABLE customers (id NUMBER, name VARCHAR2(10), salary NUMBER); Table created. DESC customers Name Null? Type ----------------------------------------- -------- -----------ID NUMBER NAME VARCHAR2(10) SALARY NUMBER INSERT INTO customers (id, name, salary) VALUES(1,'Upen',5000); 1 row created. Similarly insert another 7 records. SELECT * FROM customers; ID NAME SALARY ---------- ---------- ------------1 Upen 5000 2 Ali 6000 3 Preetam 7000 4 Goutham 8000 5 Rahul 9000 6 Sukeerthi 10000 7 Salman 11000 8 Ajaj 12000 8 rows selected. Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


102

Oracle (PL/SQL) A package will have two mandatory parts:

•

Package specification

•

Package body or definition

Package Specification:

The specification is the interface to the package. 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. The following simple code shows a package specification having a single procedure. You can have many global variables defined and multiple procedures or functions inside a package. Syntax to create a package specification:

CREATE PACKAGE package_name AS PROCEDURE procedure_name(new_variable table_name.variable_name%TYPE); END package_name; / To create a package specification: Neha Program 44:

-- To create a package specification -- File Name: 44.sql CREATE PACKAGE cust_sal AS PROCEDURE find_sal(c_id customers.id%TYPE); END cust_sal; /



103

Oracle (PL/SQL) START 44 When the above code is executed at SQL prompt, it produces the following result: Package created. 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. The CREATE PACKAGE BODY Statement is used for creating the package body. The following simple code shows the package body declaration for the cust_sal package created above. To create the package body: Neha Program 45:

-- To write the package body -- File name: 45.sql CREATE OR REPLACE PACKAGE BODY cust_sal AS PROCEDURE find_sal(c_id customers.id%TYPE) IS c_sal customers.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; / Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


104

Oracle (PL/SQL) Save the above file packagebody.sql and execute it as follows: START 45 Package body created. Using the Package Elements:

The package elements (variables, procedures or functions) are accessed with the following syntax: package_name.element_name; Consider, we have already created above package in our database schema; the following program uses the find_sal method of the cust_sal package: To extract package elements: Neha Program 46 :

-- To extract package elements -- File Name: 46.sql SET SERVEROUTPUT ON SET VERIFY OFF DECLARE code customers.id%TYPE := &cc_id; BEGIN cust_sal.find_sal(code); END; / SET VERIFY ON Save the above file findsalary.sql and execute it as follows: Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


105

Oracle (PL/SQL) START 46 Enter value for cc_id: 1 Salary: 5000 PL/SQL procedure successfully completed. Purpose: To illustrate a more complete package. Note: We will use the CUSTOMERS table stored in our database with the following

records: SELECT * FROM customers; ID NAME SALARY ---------- ---------- ------------1 Upen 5000 2 Ali 6000 3 Preetam 7000 4 Goutham 8000 5 Rahul 9000 6 Sukeerthi 10000 7 Salman 11000 8 Ajaj 12000 8 rows selected.



106

Oracle (PL/SQL) THE PACKAGE SPECIFICATION:

Create the following file. Neha Program 47 :

-- To create a package specification File Name: 47.sql CREATE OR REPLACE PACKAGE c_package AS -- Adds a customer PROCEDURE addCustomer(c_id customers.id%TYPE, c_name customers.name%TYPE, c_sal customers.salary%TYPE); -- Removes a customer PROCEDURE delCustomer(c_id customers.id%TYPE); --Lists all customers PROCEDURE listCustomer; END c_package; / Save the above file addremovelistpack.sql and execute it. START 47 Package created.



107

Oracle (PL/SQL) CREATING THE PACKAGE BODY :

Create the following file. Neha Program 48:

-- To create the package body -- File Name: 48.sql SET SERVEROUTPUT ON SET VERIFY OFF CREATE OR REPLACE PACKAGE BODY c_package AS PROCEDURE addCustomer(c_id customers.id%TYPE, c_name customers.name%TYPE, c_sal customers.salary%TYPE) IS BEGIN INSERT INTO customers(id,name,salary) VALUES(c_id, c_name, c_sal); END addCustomer; PROCEDURE delCustomer(c_id customers.id%TYPE) IS BEGIN DELETE FROM customers WHERE id = c_id; END delCustomer; PROCEDURE listCustomer IS CURSOR c_ customers IS SELECT name FROM customers; TYPE c_list IS TABLE OF customers.name%TYPE; name_list c_list := c_list(); counter INTEGER :=0; BEGIN FOR n IN c_customers LOOP



108

Oracle (PL/SQL) counter := counter +1; name_list.extend; name_list(counter) := n.name; DBMS_OUTPUT.PUT_LINE('Customer(' ||counter|| ')'||name_list(counter)); END LOOP; END listCustomer; END c_package; / SET VERIFY ON Save the above file and execute it as follows: START 48 Package body created. USING THE PACKAGE:

The following program uses the methods declared and defined in the package c_package. Neha Program 49:

-- To use the package -- File Name: 49.sql SET SERVEROUTPUT ON SET VERIFY OFF DECLARE code customers.id%TYPE:= 10; BEGIN c_package.addcustomer(9, 'Rajnish', 3500); c_package.addcustomer(10, 'Subham', 7500); c_package.listcustomer; c_package.delcustomer(code); c_package.listcustomer; END; / SET VERIFY ON Save the above file and execute it as follows:



109

Oracle (PL/SQL) START 49 Customer(1)Upen Customer(2)Ali Customer(3)Preetam Customer(4)Goutham Customer(5)Rahul Customer(6)Sukeerthi Customer(7)Salman Customer(8)Ajaj Customer(9)Rajnish Customer(10)Subham Customer(1)Upen Customer(2)Ali Customer(3)Preetam Customer(4)Goutham Customer(5)Rahul Customer(6)Sukeerthi Customer(7)Salman Customer(8)Ajaj Customer(9)Rajnish PL/SQL procedure successfully completed.

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.



110

Oracle (PL/SQL) Benefits of Triggers: Triggers can be written for the following purposes :

•

Generating some derived column values automatically

•

Enforcing referential integrity

•

Event logging and storing information on table access

•

Auditing

•

Synchronous replication (duplication) of tables

•

Imposing security authorizations

•

Preventing invalid transactions

Creating Triggers:

The syntax for creating a trigger is :

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 Neha Computers, Nalgonda - Telangana - INDIA-Mobile: 9440102056


111

Oracle (PL/SQL) EXCEPTION Exception-handling-statements END; /

Where, CREATE [OR REPLACE] TRIGGER trigger_name: Creates or replaces an existing trigger with the trigger_name. {BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be executed. The INSTEAD OF clause is used for creating trigger on a view. {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation. [OF col_name]: This specifies the column name that would be updated. [ON table_name]: This specifies the name of the table associated with the trigger. [REFERENCING OLD AS o NEW AS n]: This allows you to refer new and old values for various DML statements, like INSERT, UPDATE, and DELETE. [FOR EACH ROW]: This specifies a row level trigger, i.e., the trigger would be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger. WHEN (condition): This provides a condition for rows for which the trigger would fire. This clause is valid only for row level triggers.



112

Oracle (PL/SQL) Example: Step 1: Create dealer table using following command :

CREATE TABLE dealer (ID NUMBER, NAME VARCHAR2(10), AGE NUMBER(3), ADDRESS VARCHAR2(15), SALARY NUMBER); Table created. DESC dealer ; Name

Null? Type

----------------------------------------- -------- ------------ ID NAME

NUMBER

VARCHAR2(10)

AGE

NUMBER(3)

ADDRESS

VARCHAR2(15)

SALARY

NUMBER



113

Oracle (PL/SQL) Step 2: Insert the following two records before creating Trigger on the table.

INSERT INTO DEALER(ID,NAME,AGE,ADDRESS,SALARY) VALUES(1,'Dimpy', ALUES(1 ,'Dimpy', 26, 'Telan 'Telangana', gana', 6000); 600 0); 1 row created.

SELECT * FROM dealer ; ID NAME

AGE

ADDR ADDRE ESS

SALA ALARY

---------- ---------- ---------- --------------- -------------------------1 Dimpy

26

Telangana

6000

INSERT INTO DEALER(ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Karishma', 'Karis hma', 31, 'Mumbai', 'Mumbai' , 7500 ); 1 row created. SELECT * FROM dealer ; ID NAME

AGE

ADDR ADDRE ESS

SALA ALARY

---------- ---------- ---------- --------------- ---------------------1 Dimpy 2 Karishma

26 31

Telangana

6000

Mumbai

7500



114

Oracle (PL/SQL) Step 3: Now, Now, create Trigger on dealer table t able as shown below: bel ow:

Neha Program 50:

-- To To create trigger trigge r on dealer table -- File Name: 50.sql SET SERVEROUTPUT ON CREATE CREATE OR REPLACE TRIGGER display_salary_changes BEFORE DELETE DELE TE OR INSER INS ERT T OR UPDATE UPDATE ON dealer FOR EACH ROW WHEN (NEW.ID > 0) DECLARE sal_diff

NUMBER;

BEGIN sal_diff := :NEW.salary :NEW.salary - :OLD.salary; DBMS_OUTPUT.PUT_LINE('Old salary: ' || :OLD.salary); DBMS_OUTPUT.PUT_LINE('New salary: ' || :NEW.salary); :NEW.salary); DBMS_OUTPUT.PUT_LINE('Salary DBMS_OUTPUT.PUT_LINE('Salary difference: difference: ' || sal_diff); END; /



115

Oracle (PL/SQL) Step 4: Execute the file triggerdemo.sql triggerdemo.sql as shown shown below:

START 50; Trigger created. Triggering theTrigger theTrigger: Step 5: Now, Now, insert a new record as shown below after creating trigger on the table:

INSERT INTO DEALER(ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Sambhavana', 'Sambhava na', 31, 'Delhi', 'Delhi' , 8700 ); Old salary: New salary: 8700 Salary difference: 1 row created. SELECT * FROM dealer ; ID NAME

AGE ADDRESS

SALARY

---------- ---------- ---------- --------------- -----------------------1 Dimpy

26

Telangana

6000

2 Karishma

31

Mumbai

7500

3 Sambhavana

31

Delhi

8700

Step 6:

Update ID No. 2 after creating the trigger as shown below :

UPDATE dealer SET salary = salary + 500 WHERE id = 2;



116

Oracle PL SQL Concept Book in PDF Form

Recommend Documents