WORKING WITH EXCEPTIONS IN PL/SQL:
=================================
EXCEPTIONS:
a) It is an IDENTIFIER in PL/SQL, raised during the execution of a block that termination its main body of ACTIONS.
b) A block terminates when PL/SQL raises an EXCEPTION, By Specifying an Exception HANDLER we can perform final action.
METHODS TO RAISE AN EXCEPTION:
==============================
a) An ORACLE ERROR OCCURS and the associated exception is raised automatically.
b) Raise the EXCEPTION explicitly by issuing the RAISE statement within the Block.
c) The RAISED EXCEPTION can be either USER DEFINED or PREDEFINED.
EXCEPTION HANDLING:
==================
a) Exception Handling can happier in two ways
* Exception trapping
* Exception propagation
* EXCEPTION TRAPPING:
-------------------
-> If an Exception is raised in the Executable section of the Block then process the Exception to
the corresponding Exception Handling in the Exception Section of the same Block.
-> If the Exception is successfully handled then the exception should not propagate to the enclosing
block of environment.
-> If exception is trapped and Handled successfully then the PL/SQL Block terminated successfully.
* EXCEPTION PROPAGATION:
----------------------
-> If an EXCEPTION is raised in the executable section of the block and there is no corresponding
Exception Handler then the PL/SQL Block terminates with failure and the exception is
propagated to the calling encampment.
EXCEPTION TRAPPING EXCEPTION PROPAGATION
DECLARE DECLARE
BEGIN BEGIN
Exception Raised; Exception Raised;
EXCEPTION EXCEPTION
Exception Trapped; END
END
Exception Not Trapped
|
V
Exception Propagation to calling Environment.
TYPES OF EXCEPTIONS:
====================
a) As per PL/SQL Exception are considered to be of three types
* Predefined Oracle Server Errors
* Non-Predefined Oracle Server Errors
* User Defined Errors.
b) The PL/SQL programmer can program for exceptions to avoid disruption at Runtime.
SPECIFICATIONS OF EXCEPTIONS:
=============================
1) EXCEPTION TYPE : Predefined Oracle Server Errors
DESCRIPTION : These are one of appropriately 20 errors that occurs most often in PL/SQL code.
HANDLING TIP : Do not declare and allow the ORACLE SERVER to RAISE then Implicitly.
2) EXCEPTION TYPE : Non-Predefined Oracle Server Errors
DESCRIPTION : It is any other standard Oracle Server Error.
HANDLING TIP : Declare with in the Declarative Section and allow the Oracle Server to Raise them implicitly.
3) EXCEPTION TYPE : User Defined Errors
DESCRIPTION : It is any condition that the developer determines, which is abnormal.
HANDLING TIP : Declare with in the Declarative Section and Raise Explicitly.
TRAPPING EXCEPTIONS:
====================
a) Including a corresponding routine within the exception handling section of the PL/SQL block can trap any errors
raised while running a PL/SQL Block.
b) Each Exception Handler consists of WHEN clause which specifies an Exception.
c) Each Exception is followed by a sequence of statements to be executed when that exception is raised.
Syntax:
EXCEPTION
WHEN Exception 1[OR Exception 2.............]THEN
Statement 1;
Statement 2;
Statement 3;
.
.
.
.
WHEN OTHERS THEN
Statement 1;
Statement 2;
.
.
.
d) OTHERS is an optional EXCEPTION handling clause that traps unspecified Exceptions.
WHEN OTHERS EXCEPTION HANDLER:
==============================
a) The EXCEPTION Handling Section taps only those Exceptions that are specified any other Exceptions are not trapped
unless the OTHERS EXCEPTION handles is used.
b) The OTHERS should be the last exceptions handles in the defination.
TRAPPING EXCEPTIONS GUIDELINES:
===============================
a) Begin the exception handling section of the Block with the keyword EXCEPTION.
b) Define several Exception handlers, each with its own set of actions for the block.
c) When an Exception occurs PL/SQL processes only one handles before leaving the block.
d) Place the OTHERS clause after all other Exception Handling Clauses.
e) We can have at most one OTHERS clause.
f) EXCEPTIONS cannot appear in argument statements or SQL statements.
HANDLING PREDEFINED EXCEPTIONS:
===============================
a) A PREDEFINED Oracle Server Error Tapped by referencing its standard Name within the corresponding EXCEPTION
HANDLING Runtime.
b) All predefined Exceptions are declared by PL/SQL in the STANDARD Package.
SOME VERY COMMON PREDEFINED EXCEPTIONS:
EXCEPTION NAME NUMBER DESCRIPTION
ACCESS_INTO_NULL ORA_06530 Attempted to assign values to
the attributes of an instantiated object.
COLLECTION_IS_NULL ORA_06531 Attempt to apply collection methods other
than Exists to an uninitiated nested table
or VARY.
CURSOR WITH SUBQURIES:
a) A CURSOR can be constructed upon the result provided through a SUBQUERY.
b) The SUBQUERY can be an ordinary on of a correlated SUBQUERY.
c) The SUBQUERY when evaluated can provide a value on a set of values to the statement.
Example:
-------
Sql > DECLARE
V_DeptNo Dept.DeptNo%TYPE;
V_DName Dept.DName%TYPE;
V_Staff NUMBER(4);
CURSOR StraffCountCursor IS
SELECT T1.DeptNo,T1.Dname,T2.Staff FROM Dept T1,
(SELECT DeptNo,COUNT(*) Staff FROm Emp GROUP BY DeptNo)T2
WHERE T1.DeptNo=T2.DeptNo AND T2.Staff>=5;
BEGIN
OPEN StaffCountCursor;
LOOP
FETCH StaffCountCursor INTO V_DeptNo,V-Dname,V-Staff;
EXIT WHEN StaffCountCursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_DeptNo||''||V_Dname||''||V_Staff);
END LOOP;
CLOSE StaffCountCursor;
END;
DATA MANIPULATING THROUGH PL/SQL:
a) The data in the database is manipulated using the DML Commands.
b) The DML Commands INSERT, UPDATE and DELETE can be used in PL/SQL without any restrictions.
c) The Row Locks and Table Locks in the database are released using the COMMIT and ROLLBACK.
INSERTING DATA:
a) The SQL INSERT Statement is used as it exists, in the PL/SQL Block.
b) We can use SQL functions, like USER and SYSDATE as per requirements.
c) The PRIMARY KEY values can be generated using the database sequences.
d) Derive values within the PL/SQL Block as per necessity.
e) Add Column default values if any.
f) Any Identifiers in INSERT Clause must be an Database Column Name only, avoiding ambiguity.
Example:
SQL> BEGIN
INSERT INTO Emp(Empno, EName,Job,Deptno,MGR,Sal)
VALUES (Empsequence.NEXTVAL,'SATISH','MANAGER',10,7654,5000);
END;
UPDATING DATA:
a) The Data Updation is an similar to the UPDATE statement used in SQL.
b) A small amount of Ambiguity can arise in the SET clause of the UPDATE statement, as the identifier on the right side of the assignment operates can be a PL/SQL variable, or another database column.
c) In the SET clause, the identifier on the left is always a database column.
d) It is always better to implement a WHERE clause to that the required rows only are UPDATED.
e) If any rows are not updated, then no errors is returned.
f) A point to note this state is
* PL/SQL Variable Assignement use:=.
* SQL Column Assignement uses=.
Example:
Sql > DECLARE
V_EmpNo%TYPE:=&EmpNo;
V_Asterisk Emp.States%TYPE:=NULL;
CURSOR EmpCursor IS
SELECT EmpNo, Nvl(ROUND(Sal/100),0) Sal FROM EmpNo=V_EmpNo FOR UPDATE;
BEGIN
FOR EmpRecord IN EmpCursor
LOOP
FOR MyIndex IN 1...EmpRecord.Sal
LOOP
V_Asterisk:=V_Asterisk||'*';
END LOOP;
UPDATE Emp Set States= V_Asterisk
WHERE CURRENT OF EmpCursor;
V_Asterisk:=NULL;
END LOOP;
COMMIT;
END;
PL/SQL TABLES:
a) Objects of type TABLE are called PL/SQL tables.
b) They are modeled as database tables, but are not same.
c) PL/SQL tables use a Primary key to give array like access to Rows.
Things To Note:
--------------
i) It is similar to an Array.
ii) It shouls contains two components.
* A PRIMARY KEY of datatype BINARY_INTEGER, that Indexes the PL/SQL Table.
* A Column of a scalar or Record datatyoe which stores the PL/SQL TABLE elements.
iii) It can increase dynamically as it is unconstrained.
CREATING A PL/SQL TABLE:
=========================
a) There are two steps involved in creating a PL/SQL Table.
-> Declare a TABLE Datatype.
-> Declare a Variable of that Datatype.
Syntax:
TYPE TypeName IS TABLE OF
{ ColumnType | Variable%TYPE
| Table.Column%TYPE} [NOT NULL]
[INDEX BY BINARY_INTEGER];
Identifier TypeName;
Example:
TYPE Ename_Table_TYOE IS TABLE OF Emp.Ename%TYPE INDEX BY BINARY INTEGER;
EnameTable Ename_Table_Type;
PL/SQL TABLE STRUCTURE:
PRIMARY KEY COLUMN
1 SATISH
2 RAJ
3 KRISHNA
BINARY_INTEGER SCALAR
a) The number of rows in PL/SQL table can increase dynamically, Hence a PL/SQL table can grow as new rows are added.
b) PL/SQL tables can have one column and a PRIMARY KEY, neither of which can be named.
c) The column can belong to any scalar or record datatype, but the PRIMARY KEY must belong to Typr BINARY_INTEGER.
d) We cannot initialize a PL/SQL table in its declaration.
REFERENCING A PL/SQL TABLE:
Syntax:
PL/SQL_Table_Name(Primary_Key_Value);
a) Primary_Key_Value belongs to type BINARY_INTEGER.
b) The Primary Key value can be negative Indexing need not start with 1.
c) The Methods that make PL/SQL Tables easier to use are
* EXISTS(n) -> Returns TRUE if the nth element in a PL/SQL table exists.
* COUNT -> Returns the number of elements that PL/SQL TABLE currently contains.
* FIRST and
LAST -> Returns the FIRST and Last index numbers in PL/SQL Table. Returns NULL if the PL/SQL table is empty.
* Prior(n) -> Returns the Index number that preceeds index n in a PL/SQL Table.
* NEXT(n) -> Returns the Index number taht succeeds Index n in a PL/SQL Table.
* EXTEND(n.i) -> It increases the size of a PL/SQL Table.
i) It appends one NULL element to a PL/SQL Table.
ii) EXTEND(n) appends n NULL elements into a PL/SQL table.
iii) EXTEND(n.i) appends n copies of the ith element to a PL/SQL Table.
* TRIM -> It removes one element from the end of a PL/SQL Table.
* DELETE -> It removes all elements from a PL/SQL Table.
DELETE(n), DELET(m,n)
d) To Reference PL/SQL Table of Records we use
-> Table(index).Field
Example:
SQL > DECLARE
TYPE Dept_Table_Type IS TABLE OF Dept.DName%TYPE INDEX BY BINARY_INTEGER;
My_Dept_Table Dept_Table_Type;
V_Count NUMBER(2);
BEGIN
SELECT COUNT(*) INTO V_Count FROM DEPT;
FOR MyIndex IN 1...V_Count
LOOP
SELECT Dname INTO My_Dept_Table(MyIndex) FROM DEPT WHERE Deptno=MyIndex*10;
END LOOP;
FOR MyIndex IN 1....V_Count
LOOP
DBMS_OUTPUT_PUTLINE(My_Dept_Table(MyIndex));
ENDLOOP;
END;
SQL > DECLARE
TYPE Dept_Table_Type IS TABLE OF Dept%ROWTYPE INDEX BY BINARY_INTEGER;
My_Dept_Table Dept_Table_Type;
V_Count NUMBER(2);
BEGIN
SELECT COUNT(*) INTO V_Count FROM DEPT;
FOR MyIndex IN 1...V_Count
LOOP
SELECT *INTO My_Dept_Table(MyIndex) FROM DEPT WHERE Deptno=MyIndex*10;
END LOOP;
FOR MyIndex IN 1....V_Count
LOOP
DBMS_OUTPUT_PUTLINE('Department NUmber'||My_Dept_Table(MyIndex).DeptNO||'Department Name'||My_Dept_Table(MyIndex).DeptName ||'Department Location'||My_Dept_Table(MyIndex).LOC);
ENDLOOP;
END;
%ROWTYPE ATTRIBUTE:
a) To declare a record based on a collection of columns in a database table or view, we can use the %ROWTYPE attribute.
b) The fields in the Record take their Names and datatypes from the columns of the table or view.
c) The Record can also store an entre row of the data fetched from cursor or cursor variable.
d) Prefix %ROWTYPE with the database table.
Syntax:
VariableName TableName%ROWTYPE;
Example:
V_EmpRecord Emp%ROWTYPE;
ADVANTAGES:
a) The number and datatypes of the underlying database columns may not be known.
b) The number and datatypes of the underlying database column may change at runtime.
c) It is mostly when retrieving a row with the SELECT statement.
Example:
SQL > DECLARE
V_Empno Emp.Empno%ROWTYPE:=&EnterEmpno;
V_EmpRecord Emp%ROWTYPE;
BEGIN
SELECT * INTO V_EmpRecord FROM WHERE Empno=V_Empno;
DBMS_OUTPUT.PUT_LINE('Employee Name:' || EmpRecord.V_EmpRecord.EName);
DBMS_OUTPUT.PUT_LINE('Designation:'|| EmpRecord.Job);
END;
DEFINING and DECLARING A PL/SQL RECORD:
a) To create a Recoed, we define a RECORD type and then declare Records of that Type.
* TypeName -> is the name of the RECORD Type.
* FieldName -> it is the Name of the field within the Record.
* FieldType -> is the datatype of the Field.
* Expr -> it is the FieldType or an initial value.
b) The NOTNULL Constraint prevents the assigning of NULL's to those fields.
c) Field declaration are like variable declarations each field has a Unique Name aand a specific datatype.
d) The most important point to note is we must create the datatype first and then declare an identifier using the datatype.
Example:
SQL > DECLARE
TYPE EmpRecordType IS RECORD
(
EMpno NUMBER(4) NOT NULL:=500,
EName Emp.EName%TYPE;
Job Emp.Job%TYPE;
);
EmpRecord EmpRecordType;
e) Fields in a record are accessed by Name.
f) To reference of initialize an individual field, we must use dot notation.
RecordName.FieldName
Example:
SQL > DECLARE
V_Empno NUMBER(4):=&EnterEmpmno;
TYPE EmpRecordType IS RECORD
(
V_EName VARCHAR2(30),
V_Deptno NUMBER(2),
V_Job VARCHAR2(20),
);
EmpRecord EmpRecordType;
BEGIN
SELECT EName, Deptno, Job INTO
EmpRecord.V_Ename,
EmpRecord.V_Deptno,
EmpRecord.V_Job
FROM EMP WHERE Empno=V_Empno;
DBMS_OUTPUT.PUT_LINE('Employee Name' || EmpRecord.V_EName);
DBMS_OUTPUT.PUT_LINE('Employee Department' || EmpRecord.V_Deptno);
DBMS_OUTPUT.PUT_LINE('Employee Designation'|| EmpRecord.V_Job);
END;
APPLYING COMPOSITE DATATYPES IN PL/SQL:
a) The Composite datatypes in PL/SQL are also called as COLLECTIONS.
b) The types of composite datatypes in PL/SQL are
* RECORD *TABLE *NESTED TABLE *VARRAY
PL/SQL RECORDS:
a) A RECORD is a group of related data items stored in Fields, each with its own Name and Datatype.
b) When a RECORD TYPE of fields in declared then they can be manipulated as a Unit.
c) The Points to Note are:
-> Each record defined can have as many fields as necessarily.
-> Records can be assigned initial values and can be defined as NOT NULL.
-> Fields without initial values are initialized to NULL.
-> The DEFAULT keyword can be used when defining fields.
-> RECORD Types can be declared as user define records in the declaritive part of any block.
-> We can declare and reference nested records. A recoed can be the component of another record.
Syntax:
TYPE Type_Name IS RECORD
(Field_declaration[,Field_declaration]....);
Identifier Type_Name;
* WHERE Field_Declaration is:
Field_Name{Field_Type| Variable%TYPE | Table.Column%TYPE| Table%ROWTYPE}
[[ NOT NULL]
{ :=|DEFAULT}Expr]
USING SQL WITH PL/SQL:
Data Retrieval Standards:
a) Data Retrieval options range from basic SELECT statements to pattern matching with Regular Expressions.
Sql> SELECT Statement
Syntax:
SELECT SelectList
[INTO VariableList]
FROM TableList
[WHERE WhereClause]
[ORDER BY ColumnList]
b) The SelectList can be Columns, Strings, Built in Functions or * to retrieve data.
c) Arithmetic operations are allowed in theSelect List.
d) Variables can be declared as a single data type, or anchored datatype or entire record types.
e) The table list can be one or more tables, views or inline views.
f) The WHERE clause restricts the result set.
g) When selecting a value into a variable, be sure to return one and only one variable.
h) Only one record can be returned into a SELECT Statement in PL/SQL.
i) The Common Errors Encountered are:
-> ORA-01403-Nodata Found.
-> ORA-01422-Exact fetch returns more than requested number of rows.
Example:
Sql > DECLARE
V_Empno NUMBER:=&EnterEmpno;
V_EName VARCHAR2(30);
V_Job VARCHAR2(30);
V_Sal NUMBER(7,2);
BEGIN
SELECT Ename, Job, Sal INTO V_Ename, V_Job, V_Sal From EMP WHERE Empno=V_Empno;
DBMS_OUTPUT.PUT_LINE('The Name:' || V_Ename);
DBMS_OUTPUT.PUT_LINE('The Job: ' || V_Job);
DBMS_OUTPUT.PUT_LINE('The Sal: ' || V_Sal);
END;
j) SELECT INTO ASSOCIATED WIYH CONDITION CONSTRUCTS and SQL BUILT IN FUNCTION.
Sql > DECLARE
V_Empno NUMBER(4):=&EnterEmpno;
V_EName VARCHAR2(30);
V_Sal NUMBER(7,2);
v_comm NUMBER(7,2);
BEGIN
SELECT Ename,Sal, Comm INTO V_Ename, V_Sal, V_Comm From EMP WHERE Empno=V_Empno;
DBMS_OUTPUT.PUT_LINE('The Name:' || INITCAP(V_Ename));
DBMS_OUTPUT.PUT_LINE('The Salary: ' || TO_CHAR(V_Sal,'99,999.999'));
DBMS_OUTPUT.PUT_LINE('The Commission: ' || NVL(TO_CGAR(V_CHAR(V_Comm),'NO Commission'));
DBMS_OUTPUT.PUT_LINE('The Total sal is:'|| TO_CHAR(V_Sal+NVL(V_Comm,0)));
END;
% TYPE VARIABLE DECLARATION:
===========================
a) The % Type variable is used to anchor PL/SQL variable to the datatype Type Columns.
b) This methodology is more suitable where the variable that is declared in the program is mapping directly to a column in the database table.
Syntax:
VariableName TableName.ColumnName%Type;
c) This methos keeps the PL/SQL program to be unaffected even when the datatypes within the database are changing.
d) The metedology provides structural independency for the PL/SQL program.
Example:
SQL > DECLARE
V_Empno Emp.Empno%TYPE;
V_Empno Emp.Empno%TYPE;
V_Sal Emp.Sal%TYPE;
BEGIN
3) ITERATIONS IN PL/SQL:
a) LOOPs facilitates to repeat a statement or sequence of statements multiple times.
b) The different types of PL/SQL Loops are
i) Basic or SImple Loop
ii) For Loops
iii) WHILE Loops
i) BASIC OR SIMPLE LOOP:
--------------------
a) It is the simplest form of the LOOP construct in PL/SQL.
b) It encloses a sequence o statements between the keywords LOOP and END LOOP.
c) It allows execution of its statements at least once.
d) If we keep the LOOP in finite state the EXIT statement is used.
Syntax:
LOOP
Statement1;
:
:
EXIT[WHEN Condition];
END LOOP;
-> EXIT Statement:
i) EXIT statement is used to terminate a LOOP.
ii) Once the LOOP is terminated, the Control passes to the next statement after the END LOOP.
iii) EXIT can be issued either as an action within an IF statement or as stand alone statement with LOOP.
iv) The EXIT statement should always be placed inside a LOOP.
v) EXIT ca be associated with in a WHEN clause to allow conditional termination of the LOOP.
vi) A Basic Loop can contain multiple EXIT statements.
vii) The EXIT condition can be at the top of the LOOP or at the end of the LOOP as per Logical Convenience.
Example: \SimpleLoop\ SimpleExit1.sql
Sql> DECLARE
V_Num NUMBER:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('The Line'||V_Num||'Output is'||V_Num);
V_Num:=V_Num+1;
IF V_Num>5 THEN
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('The Total Lines of Outputs are'|| TO_CHAR(V_Num-1));
END;
Example: \SimpleLoop\ SimpleLoopExitWhen2.sql
Sql> DECLARE
V_Num NUMBER:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('The Line'||V_Num||'Output is'||V_Num);
V_Num:=V_Num+1;
EXIT WHEN V_Num>5;
END LOOP;
DBMS_OUTPUT.PUT_LINE('The Total Lines of Outputs are'|| TO_CHAR(V_Num-1));
END;
->NESTED LOOPS:
i) It is a situation where one Loop is embedded into the other.
ii) The Outer Loop and the Inner Loop get associated with one another and executed simultaniously.
iii) The overall Loop terminates in declared by the Outer Loop EXIT WHEN condition or EXIT cindition.
Example: \SimpleLoop\NestedLoops3.sql
NestedLoops4.sql
Sql> DECLARE
V_Num NUMBER:=1;
BEGIN
LOOP
LOOP
EXIT WHEN V-Num>5;
DBMS_OUTPUT.PUT_LINE('Inner Loop:'||V_Num);
V_Num:=V_Num+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Outer Loop:'||V_Num);
V_Num:=V_Num+1;
EXIT WHEN V_Num>10;
END LOOP;
END;
->WHILE LOOP:
i) It can be used to repeat a sequence of statements untill the controlling condition is no longer TRUE.
ii) The condition is evaluated at the start of each iteration.
iii) The Loop terminates when the condition is FALSE.
Syntax:
WHILE Condition LOOP
Statement1;
Statement2;
:
END LOOP;
iv) If the condition yields NULL, the LOOP is bypassed and control passed to the next statement.
-> NESTED LOOPS AND LABLES:
i) LOOPs can be nested to multiple levels.
ii) All the LOOPs can be Nested into one another.
iii) LOOPs can be Labeled as per the requirements.
iv) A Label is placed before the statement, eithe on the same line or on a separate line.
v) Label Loops by placing the label before the word Loop within the Label delimiters.
vi) When the LOOP is labled, the Label Name can be optionally included after the END LOOP statement for clarity.
Example:
SQL> DECLARE
V_Number NUMBER(2):=1;
V_Output VARCHAR2(100);
BEGIN
WHILE V_Number<=10
LOOP
V_Output||''||V_Number;
V_Number:=V-Number+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_Output);
END;
SQL> DECLARE
V_Number NUMBER;
V_Position NUMBER;
V_Result NUMBER:=0;
BEGIN
V_Num:=&NumbertoReverse;
WHILE V_Num>0
LOOP
V_Position:=MOD(V_Num,10);
V_Result:=(V_Result*10)+V_Position;
V_Num:=TRUNC(V_Num/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_Result);
END;
->FOR LOOP:
i) It has the same general structure as the basic loop.
ii) FOR LOOP contains a control statement at the front of the LOOP Keyword, to determine the number of iterations that PL/SQL performs.
Syntax:
FOR Counter IN[REVERSE]
LowerBound..UpperBound LOOP
Statement1;
Statement2;
:
:
END LOOP;
Counter:
It is an implicitly declared integer whose value automatically increased or decreased by 1 on each iteration of the LOOP untill the Upper Bound or Lower Bound is reached.
REVERSE:
It is a keyword, and causes the counter to decreament with each iteration from the UpperBound to the Lower Bound.
iii) The Counter need not be declared, as it implicitly declared as an Integer.
iv) The Lower Bound and the Upper Bound of the loop can be Literals, Variables or Expressions, but they should be evaluated to Integer.
v) The Lower and Upper bounds of a LOOP statement need not be numeric literals, they can be expressions that convert to numeric values.
Example:
Sql> DECLARE
V_StartRange NUMBER:=&StartRange;
V_EndRange NUMBER:=&EndRange;
V_Result VARCHAR2(500):=NULL;
BEGIN
DBMS_OUTPUT.PUT_LINE('Illustration of FOR LOOP......!');
FOR MyIndex IN V_StartRange.V-EndRange
LOOP
V_Result"=V_Result||''||V_StartRange;
V_StartRange:=V_StartRange+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_Result);
END;
Sql> DECLARE
V_NumFact NUMBER:=&GiveNumber;
V_Factorial NUMBER:=1;
BEGIN
FOR Index1 IN V_NumFact
LOOP
V_Factorial:=V-Factorial*Index1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_Factorial);
END;
Sql> DECLARE
V_NumFact NUMBER:=&GiveNumber;
V_Factorial NUMBER:=1;
BEGIN
FOR Index1 IN REVERSE 1..V_NumFact
LOOP
V_Factorial:=V-Factorial*Index1;
DBMS_OUTPUT.PUT_LINE(Index1);
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_Factorial);
END;
Sql> DECLARE
V_Name VARCHAR2(30);
V_Position VARCHAR2(200):=NULL;
BEGIN
V_Name:='&EnterName';
FOR 1 IN 1..LENGTH(V_Name)
LOOP
V_Position:=V_Position||''||SUBSTR(V_Nmae,1,1);
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_Position);
END;
Sql> DECLARE
V_MyChar Varchar2(20);
BEGIN
FOR MyIndex1 IN 1..15
LOOP
FOR MyIndex2 IN 1...MyIndex1
LOOP
V_MyChar:=V_MyChar||"*";
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_MyChar);
V_MyChar:=NULL;
END LOOP;
END;
Sql> DECLARE
V_OuterLoopRange NUMBER:=5;
V_InnerLoopRange NUMBER:=3;
BEGIN
<
FOR MyIndex IN 1..V_OuterLoopRange
LOOP
DBMS_OUTPUT.PUT_LINE('OuterLoop:'|| MyIndex);
<
FOR MyIndex2 IN 1..V_InnerLoopRange
LOOP
DBMS_OUTPUT.PUT_LINE('InnerLoop:'|| MyIndex2);
END LOOP;
END LOOP;
END;
AddThis
Posts
