Conditional Statements

Conditional Statements

The following conditional statement are there in Oracle.Which is used inside the PL/SQL Block While Checking the Condition or Validating the Business logic's in our own Way.

1.IF
2.IF ELSE
3.Multiple IF ELSE

4.LOOP
5.FOR LOOP
6.WHILE

7.GO TO
8.EXIT
9.CONTINUE
10.BREAK

Example For IF::

DECLARE
   v_a   NUMBER := 7;
BEGIN
   IF v_a < 10
   THEN
      DBMS_OUTPUT.put_Line (v_a || ' ' || 'Is Less than 10');
   END IF;
END;
/

7 Is Less than 10

ELSE::

DECLARE
   v_a   NUMBER := 11;
BEGIN
   IF v_a < 10
   THEN
      DBMS_OUTPUT.put_Line (v_a || ' ' || 'is Less than 10');
   ELSE
      DBMS_OUTPUT.put_Line (v_a || ' ' || 'is Greater than 10');
   END IF;
END;
/

11 is Greater than 10
PL/SQL procedure successfully completed.


ELSE IF::

Multiple If Statements:

   v_a   NUMBER := 2;
BEGIN
   IF v_a = 1
   THEN
      DBMS_OUTPUT.put_Line ('The Value of a is One');
   ELSE IF v_a = 2
   THEN
      DBMS_OUTPUT.put_Line ('The Value of a is Two');
   ELSE IF v_a = 2
   THEN
      DBMS_OUTPUT.put_Line ('The Value of a is Three');
   ELSE
      DBMS_OUTPUT.put_Line ('The Value of a is One,Two,Three');
   END IF;
END;
/

The Value of a is Two
PL/SQL procedure successfully completed.

LOOP::

In Oracle, the LOOP statement is used when you are not sure how many times
you want the loop body to execute at least once.

Example::

DECLARE
   v_a   NUMBER := 1;
BEGIN
   LOOP
      DBMS_OUTPUT.put_Line (v_a);
      v_a := v_a + 1;
      EXIT WHEN v_a > 10;
   END LOOP;
END;
/

1
2
3
4
5
6
7
8
9
10
PL/SQL procedure successfully completed.


FOR LOOP::

In Oracle, the FOR LOOP allows you to execute code repeatedly for a fixed
number of times.
Example::

DECLARE
   v_a   NUMBER := 1;
BEGIN
   FOR i IN 1 .. 8
   LOOP
      DBMS_OUTPUT.put_Line (i);
   END LOOP;
END;
/

1
2
3
4
5
6
7
8
PL/SQL procedure successfully completed.

REVERSE KEYWORD in FOR LOOP::

    The loop counter will count in reverse.
Example:

DECLARE
   v_a   NUMBER := 1;
BEGIN
   FOR i IN REVERSE 1 .. 8
   LOOP
      DBMS_OUTPUT.put_Line (i);
   END LOOP;
END;
/

8
7
6
5
4
3
2
1
PL/SQL procedure successfully completed.

CONTINUE Keyword in LOOP::

The Continue statement exits the current iteration of the loop unconditionally and then transfers the control to the next iteration of the current loop.

Example

BEGIN
   FOR i IN 1 .. 50
   LOOP
      IF MOD (i, 5) = 0
      THEN
         CONTINUE;
      END IF;

      DBMS_OUTPUT.put_Line (i);
   END LOOP;
END;
/

EXIT IN LOOP::

The Oracle PL/SQL EXIT statement is used to skip the current block. It can be used to terminate loops or stop processing based on a condition you specify. When encountered, an EXIT statement forces a loop to complete immediately and unconditionally. Control is then passed to the next statement.

BEGIN
   FOR i IN 1 .. 50
   LOOP
      IF i = 3
      THEN
         EXIT;
      END IF;

      DBMS_OUTPUT.put_Line (i);
   END LOOP;

   DBMS_OUTPUT.put_Line ('Control Came out From Loop');
END;
/

1
2
Control Came out From Loop
PL/SQL procedure successfully completed.

Example::

DECLARE
   v_grade   CHAR (1);
BEGIN
   v_grade := 'B';

   CASE v_grade
      WHEN 'A'
      THEN
         DBMS_OUTPUT.Put_Line ('Excellent');
      WHEN 'B'
      THEN
         DBMS_OUTPUT.Put_Line ('Very Good');
      WHEN 'C'
      THEN
         DBMS_OUTPUT.Put_Line ('Good');
      WHEN 'D'
      THEN
         DBMS_OUTPUT.Put_Line ('Fair');
      WHEN 'E'
      THEN
         DBMS_OUTPUT.Put_Line ('Poor');
      ELSE
         DBMS_OUTPUT.Put_Line ('No Grade');
   END CASE;
END;
/

Very Good

PL/SQL procedure successfully completed.

--Conditional Statements End--

Read More

Oracle PL/SQL Introduction

Oracle PL/SQL Introduction

*Procedural Language extension with SQL with design features of Programming language.
*Data Manipulation & query Statement of SQL are included with procedural Unit of
Code.
*It mainly used for reduce the network traffic.
*It group the set of logical statement Into single block & send the block
to the server for execution.

CONCEPTS IN PL/SQL::

1.Anonymous Block
2.Conditional Statements
3.Cursors
4.Exception
5.Stored Procedure
6.Stored Functions
7.Packages
8.Triggers

1.Unnamed block or Anonymous Block Structure::

DECLARE
           variable;
           cursor;
           Exception;
           SubProgram(Like Procedure);
BEGIN

           SQL Statements (*Mandatory)
           Business logics
EXCEPTION (*Optional)
Error Handling;
END;
/


EXAMPLE FOR ANONYMOUS BLOCK::
SQL> SET SERVEROUTPUT ON;
SQL> BEGIN
2 dbms_Output.put_Line('This is My First Program');
3 END;
4 /

This is My First Program
PL/SQL procedure successfully completed.

VARIABLES::

*Temporary Storage of Data
*Manipulation of Stored Value
*Reusability
PL/SQL VARIABLES::
------------------
1.Scalar
2.Composite
3.Reference
4.Large Object

NON PL/SQL VARIABLES::

1.Bind Variables

Standard Rules to be follow to Declare PL/SQL Variable::

*Follow Naming rule
(v_Name...(Start with v_)
1-30 Character size
*Initialize NOT NULL/Constant/Default
*Declare One identified Per line
*Initialize identifiers by using assignment Operator(:=)


EXAMPLE-1

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 v_a NUMBER(3):=4;
3 BEGIN
4 dbms_Output.put_Line('The Value Of a Is :' || v_a);
5 END;
6 /
The Value Of a Is :4
PL/SQL procedure successfully completed.
------------------------------------------------------------------

Example-2

DECLARE
v_a NUMBER:=&a;
v_b NUMBER:=&b;
v_c NUMBER;
BEGIN
v_c := v_a + v_b;
dbms_output.put_Line('The Values Of a+b Is :' || v_c);
END;
/
Enter value for a: 4
Enter value for b: 5
The Values Of a+b Is :9
-----------------------------------------------------------------

Example-3


DECLARE
   v_a             NUMBER := 100;
   v_name          VARCHAR2 (100) := INITCAP ('Arul Xavier');
   v_dt            DATE := SYSDATE;
   v_dt_dif_fmt    VARCHAR2 (30) := TO_CHAR (SYSDATE, 'DD Mon, YYYY');
   v_dept          VARCHAR2 (10) NOT NULL := 'CSE';
   v_depid         NUMBER DEFAULT 10;
   v_pi   CONSTANT NUMBER := 3.14;
BEGIN
   v_a := 200;
   DBMS_OUTPUT.put_line ('Number : ' || v_a);
   DBMS_OUTPUT.put_line ('Name : ' || v_name);
   DBMS_OUTPUT.put_line ('Date : ' || v_dt);
   DBMS_OUTPUT.put_line ('Char Chk : ' || v_dt_dif_fmt);
   DBMS_OUTPUT.put_line ('Dname : ' || v_dept);
   DBMS_OUTPUT.put_line ('did : ' || v_depid);
   DBMS_OUTPUT.put_line ('PI : ' || v_pi);
END;
/
------------------------------------------------------------------

NESTED BLOCK or SCOPE OF VARIABLE::


<<Outer>>
DECLARE
   v_a   NUMBER := 20;
   v_c   NUMBER := 100;
   v_d   NUMBER;
   v_e   BOOLEAN := FALSE;
   v_f   NUMBER (200) := 39000;
BEGIN
   DBMS_OUTPUT.put_Line ('-------->' || v_e);
   DBMS_OUTPUT.put_Line ('-------->' || v_f);

   IF v_e
   THEN
      DBMS_OUTPUT.put_Line ('Boolean Check :' || 1 || '<------------');
   ELSE
      DBMS_OUTPUT.put_Line ('Boolean Check :' || 0 || '<------------');
   END IF;

  <<inner>>
   DECLARE
      v_a   NUMBER := 30;
   BEGIN
      DBMS_OUTPUT.put_Line ('Value Of a is:' || Outer.v_a);
      DBMS_OUTPUT.put_Line ('Value Of a is:' || v_c);
   END;

   DBMS_OUTPUT.put_Line ('Value Of a is:' || v_a);
END;
/
--------------------------------------------------------------------
DROP TABLE TEST_INSERT
/
CREATE TABLE TEST_INSERT(id NUMBER)
/
BEGIN
INSERT INTO TEST_INSERT VALUES(1000);
INSERT INTO TEST_INSERT VALUES(1001);
INSERT INTO TEST_INSERT VALUES(1002);
INSERT INTO TEST_INSERT VALUES(1003);
INSERT INTO TEST_INSERT VALUES(1004);
INSERT INTO TEST_INSERT VALUES(1005);
COMMIT;
END;
/
-->--
DECLARE
   v_salary   NUMBER (8);
BEGIN
   SELECT MAX (salary) INTO v_salary FROM employees;

   DBMS_OUTPUT.put_line ('Maximum Salary : ' || v_salary);
END;
/
-------------------------------------------------------------------------------
DECLARE
   v_empid    employees.employee_id%TYPE := 100;
   v_fname    employees.first_name%TYPE;
   v_salary   employees.salary%TYPE;
   v_hdate    employees.hire_date%TYPE;
BEGIN
   SELECT first_name, salary, hire_date
     INTO v_fname, v_salary, v_hdate
     FROM employees
    WHERE employee_id = v_empid;

   DBMS_OUTPUT.put_line (' First Name : ' || v_fname);
   DBMS_OUTPUT.put_line (' Salary : ' || v_salary);
   DBMS_OUTPUT.put_line (' Hire Date : ' || v_hdate);
END;
/
-------------------------------------------------------------------------------
DECLARE
   v_empid     employees.employee_id%TYPE := 100;
   v_alldate   employees%ROWTYPE;
BEGIN
   SELECT *
     INTO v_alldate
     FROM employees
    WHERE employee_id = v_empid;

   DBMS_OUTPUT.put_line (' First Name : ' || v_alldate.first_name);
   DBMS_OUTPUT.put_line (' Salary : ' || v_alldate.salary);
   DBMS_OUTPUT.put_line (' Hire Date : ' || v_alldate.hire_date);
END;
/
----------------------------------------------------------------------------------
DECLARE
   v_department_id   NUMBER := 80;
BEGIN
   DELETE FROM employees
         WHERE department_id = v_department_id;
END;
/


BIND VARIABLES::

By using bind variables, you can write a SQL statement that accepts inputs or parameters at run time.

SQL> VARIABLE a NUMBER;
SQL>
SQL>
SQL> BEGIN
2 SELECT MAX(salary) INTO :a FROM employees;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> PRINT a;
A
----------
24000
SQL> SELECT :a FROM dual;
:A
----------
24000

                                               --END PL/SQL INTRO--

Read More

INDEX & MERGE

Index In Oracle

1.Its a Schema Object
2.Its One of the Performance tuning method.
3.It improves the performance of the query.
4.Index created by user and maintained by Database.

Types Of Index::

1.Unique Index
2.Non Unique Index

1.Unique Index::

It's crated by automatically by oracle,When you create Primary Key or Unique Key.

2.Non Unique Index::

It's Created by the programmer to speed up the retrieval of the query.

2.Types of Non Unique Index::

1.B-Tree Index
2.Bitmap Index
3.Functional Based Index
4.Reverse Index.

--------------------------------------------------------------------------------------------
Data Dictionary Table ---For Index Is ---->(USER_INDEX)
---------------------------------------------------------------------------------------------

WHEN TO CREATE INDEX::

1.When a table size is Large.
2.When a column is most often used as a condition in a query.
3.When column Contain wide range of Values.
4.When most of your queries are expected to retrieve less than 4% of the row from the table.

Example::

SELECT * from Employees Where First_Name='Steven';

In this table the Column "First_Name" is mostly used  So We can create Index for the Column.

WHEN NOT TO CREATE INDEX::

1.When the table size is small.
2.When a column is not used as a condition in a query.
3.When a column contains large number of duplicate values.
4.When your query are expect to retrieve more than 10% of the ROW from the table.
5.When a table is frequently Updated.

Example::

CREATE INDEX Dept_Id ON Employees(Department_Id);
[This index in known as Normal index or B-Tree Index]

Functional Index::

When performing a function on an indexed column in the where clause of a query is Known as Functional Index.

Example::

SELECT * FROM Employees WHERE First_Name='neena';
In this the First name is 'Neena' in the table,So its not return the output in this case,we can create Fun Index.

CREATE INDEX First_Nm ON Employees(LOWER(First_Name);

BITMAP INDEX::

When our Column having more duplicate records then we prefer the BITMAP index.

Example::

GENDER Column
CREATE BITMAP INDEX Gender_Ind ON Employees(Gender);

REVERSE INDEX::
 
A reverse key Index can be useful if it e.g. is the primary key (or other Index ) of an column that is filled by a sequence.

When generating new records in the base table, because of the used sequence you'll get a high contention on the same Index branches and thus database blocks.
It can have a high impact on Insert on that table.

SYNTAX::

CREATE INDEX <<INDEX_NAME>>ON <<TABLE_NAME(Col_Name)>> REVERSE;

Example::

CREATE INDEX Rev_Indx ON Bank_Master(Acc_num) REVERSE;

WHEN INDEX IS NOT PERFORM::
If we perform any action with the Index column then the Index is not perform.

Example::

If The Salary having Index,

Select Salary*12 As "Yearly Salary" From Employees;

In this it's not Working.

MERGE

MERGE is nothing but it's combined INSERT + UPDATE
When the Target table match with the Source table then the "UPDATE" will happen
When the target table is unmatched with source table then "INSERT" will happen.

EXAMPLE::

Consider Target_Table & Source Table,Merge [Insert + Update] data's from Source to Target Table

MERGE INTO table_Trg t
USING table_src s
ON(s.Department_Id=t.Department_Id)
WHEN NOT MATCHED THEN
INSERT(t.Department_Id,t.Department_Name,t.Manager_Id,t.location_Id)
VALUES(s.Department_Id,s.Department_Name,s.Manager_Id,s.location_Id)
WHEN MATCHED THEN
UPDATE SET t.Department_Name=s.Department_Name
  t.Manager_Id=s.Manager_Id
  t.Location_Id=s.Location_Id

--------END--------

Read More

SET OPERATORS

SET OPERATORS

Set operators are used to join the results of two (or more) SELECT statements.

TYPES::

1.UNION
2.UNION ALL
3.INTERSECT
4.MINUS

Condition to Use SET Operators::

1.Number of Columns Should be same
2.Order By Should be Place at Last of the Query
3.The data types Should Be same
4.First Query Alias Name will take for the Entier Result SET.

1.UNION::

When multiple SELECT queries are joined using UNION operator, Oracle displays the combined result from all the compounded SELECT queries,after removing all duplicates and in sorted order (ascending by default), without ignoring the NULL values.

Example::

SQL> SELECT 1 UNION_RESULT FROM Dual
2  UNION
3  SELECT 2 FROM DUAL
4  UNION
5  SELECT 3 FROM Dual
6  UNION
7  SELECT 3 FROM Dual;

UNION_RESULT
------------
1
2 [Removed Duplicate Records]
3

2.UNION ALL::

UNION and UNION ALL are similar in their functioning with a slight difference. But UNION ALL gives the result set without removing duplication and sorting the data.

Example::

SQL> SELECT 1 UNION_ALL FROM Dual
2  UNION ALL
3  SELECT 1 FROM DUAL
4  UNION ALL
5  SELECT 2 FROM Dual;

UNION_ALL
----------
1
1 [With out Remove Duplicates]
2

3.INTERSECT::

Using INTERSECT operator, Oracle displays the common rows from both the SELECT statements, with no duplicates.

Example::

SQL> SELECT ID,NAME FROM TEST
2  INTERSECT
3  SELECT ID,NAME FROM TESTING;

ID NAME
---------- -------------
1 Vino
2 Mathi
3 Xavi

4.MINUS::

     MINUS takes the first result set, and removes any that exist in the second result set; it also removes any duplicates.

Example::

SQL> SELECT * from Test;

ID NAME
---------- -------------------
1 Vino
2 Mathi
3 Xavi

SQL> SELECT * from Testing;

ID NAME
---------- -------------------
4 xx
5 YY
6 ZZ

SQL> SELECT ID from TEST
2  MINUS
3  SELECT ID FROM TESTING;

ID
----------
1
2
3

SQL> SELECT ID,NAME from TEST
2  MINUS
3  SELECT ID,NAME FROM TESTING;

ID NAME
---------- ----------------------
1 Vino
2 Mathi
3 Xavi

-------END-------

Read More

View In Oracle

View In Oracle

Logical representation of subset of data is known as "View".

Types Of View::

1.Simple View         --->DML (INSERT,UPDATE,DELETE Possible)
2.Complex View --->DML (INSERT,UPDATE,DELETE Not Possible)

Some Important Points about View::

        1.The Simple view contain only one table.
2.The Complex View Contains More than one table.
3.If we insert anything to view then the Values are inserted into the base table also.
4.Primary & Not null Values can not Insert
5.Using Group Function,Group By,DISTINCT,Expression we can't Perform the DML Operations in view.

Advantages of View::

1.Restrict the data Access
2.Make Complex Query Easy
3.To Provide data independancy

1.Simple View::

Contains Only one table.

Syntax::

CREATE OR REPLACE VIEW view_name AS
  SELECT columns
  FROM tables
  [WHERE conditions];

Example::

SQL> CREATE OR REPLACE VIEW Test_vw AS
  2  SELECT Name
  3  FROM TEST_LDR
  4  WHERE Name='xxx';

View created.

SQL> Select * from Test_vw;

NAME
-------------
xxx

Updating Simple View::

SQL> select * from TEST_LDR;

NAME                               SALARY
------------------------------ ----------
Test                                 1000
xxx                                  2000
yyy                                  3000

SQL> UPDATE Test_vw SET Name='xxxx' WHERE Name='xxx';

1 row updated.

SQL> select * from TEST_LDR;

NAME                               SALARY
------------------------------ ----------
Test                                 1000
xxxx                                 2000
yyy                                  3000

2.Complex View::

It Contains Multiple tables.

Syntax::

CREATE OR REPLACE VIEW view_name AS
  SELECT t1.column,t2.Column
  FROM tables t1,table t2
  [WHERE conditions];

Example::

SQL>CREATE OR REPLACE VIEW Test_Comp_vw AS
   SELECT e.Employee_Id,e.First_Name,d.Department_Name
   FROM Employees e,Departments d
   WHERE e.Department_Id=d.Department_Id AND d.Department_Id='10';

View created.

SQL> SELECT * FROM TEST_Comp_vw;

EMPLOYEE_ID FIRST_NAME           DEPARTMENT_NAME
----------- -------------------- ------------------------------
200     Jennifer             Administration

With Read Only  & With Check Options::

Read Only in Simple View Example::

*We cant do any dml operations inside the View while using WITH READ ONLY option.

SQL> CREATE OR REPLACE VIEW Test_vw AS
  2  SELECT Name
  3  FROM Test_LDR
  4  WHERE Name='xxx'
WITH READ ONLY;

SQL> UPDATE Test_read_vw SET Name='xxx' WHERE Name='xxxx';
UPDATE Test_read_vw SET Name='xxx' WHERE Name='xxxx'
                        *
ERROR at line 1:
ORA-01733: virtual column not allowed here

With Check Option::

*While Using WITH CHECK OPTION we cant violate the Where Condition in View.

CREATE OR REPLACE VIEW Tst_Check_vw AS
SELECT Name
FROM TEST
WHERE Name='Vino'
WITH CHECK OPTION;

SQL> select * from Test;

ID                NAME
---------- ------------------------------
1                 Vino
2                 Mathi
3                 Xavi

SQL> CREATE OR REPLACE VIEW Tst_Check_vw AS
2  SELECT Name
3  FROM TEST
ORA-01402: view WITH CHECK OPTION where-clause violation
4  WHERE Name='Vino'
5  WITH CHECK OPTION;

View created.

SQL> UPDATE Tst_Check_vw SET Name='Kumar' Where Name='Vino';
UPDATE Tst_Check_vw SET Name='Kumar' Where Name='Vino'
*
ERROR at line 1:

DROP VIEW::

SQL>DROP VIEW VIEW_NAME;
View Dropped

Read More

SQL*Loader

SQL*Loader In Oracle

*Loading data's to table from the external File(Like CSV).
*SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. 

STEPS::

==>Login into your SQLPLUS Tool
==>CREATE The Table Or Check the Table already existing in the Database
==>Create the Controler File(.ctl Format)
==>Get ready the External file with full of data's(External like Excel(.csv) File)
==>The table Should be Empty While Insert(load data) or other wise the Error Message Will thrown
  [like --> SQL*Loader-601: For INSERT option, table must be empty.]

Commands::


Controller file Syntax::

load data
infile 'External File Path'
  into table Table_Name
  fields terminated by "," optionally enclosed by '"'
  (Col..1,Col...2....etc)

Example Coding::

load data
  infile 'E:\XAVIER WORK\SQL ORACLE\UTL_Load\Testing.csv'
into table TEST_LDR
fields terminated by "," optionally enclosed by '"'
(Name,salary)

========>>Queries to be Follow in SQLPLUS TOOL<<=========

----------------------------------------------------------------------
C:\Users\JESUS>SQLPLUS HR/open
----------------------------------------------------------------------


SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jun 11 11:50:22 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
-------------------------------------------------------------------------
SQL> $SQLLDR HR@XE/open

control = E:\ORACLE\SQL ORACLE\UTL_Load\Control.ctl
-------------------------------------------------------------------------

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Jun 11 11:17:59 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 3

-----------------------------------------
SQL> SELECT * from TEST_LDR;

NAME                               SALARY
------------------------------ ----------
Test                                    1000
xxx                                      2000
yyy                                      3000


========>>If already Some data's in the table then What happens??<<=========

SQL> SELECT * FROM SQL_LRD;

NAME                               SALARY
------------------------------ ----------
Test                                 1000
xxx                                  2000
yyy                                  3000

SQL> $SQLLDR HR@XE/OPEN

control = E:\ORACLE\SQL ORACLE\UTL_Load\Control.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Jun 11 12:26:46 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL*Loader-601: For INSERT option, table must be empty.  Error on table SQL_LDR

LOAD Data's If the Table Having Some data's::

Use APPEND Key word to load data's into table While have some data's in the table.

load data
  infile 'E:\XAVIER WORK\SQL ORACLE\UTL_Load\Testing.csv'
        APPEND
 into table TEST_LDR
 fields terminated by "," optionally enclosed by '"'
 (Name,salary)
----------------------------------------------------------------------------------------------
Use Truncate Keyword to Truncate the data's from table and Insert New records:

load data
  infile 'E:\XAVIER WORK\SQL ORACLE\UTL_Load\Testing.csv'
        TRUNCATE
 into table TEST_LDR
 fields terminated by "," optionally enclosed by '"'
 (Name,salary)

Draft File or Log file in SQL*Loader::

   When we load the data's using sql*loader in any case,if there is any error will happen ,the remaining data's are stored in one log file that is known as "Draft file" or "Log File".

-----SQL*Loader END-----

Read More