Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 23 July 2017

Oracle PL/SQL Implicit Cursor Attributes

0 comments

Implicit Cursors

PL/SQL implicitly declares a cursor for all SQL data manipulation statements on a set of   rows, including queries that return only one row
For queries that return more than one row, you can explicitly declare a cursor to process the rows individually
We can refer to the most recent implicit cursor by the name SQL
Implicit Cursors - Attributes

%FOUND Attribute: used to check whether a DML Statement has affected one or many rows

Until the DML or SELECT statement is executed, it yields NULL
Yields TRUE if the DML affects one or more rows, or a SELECT INTO statement returns one or more rows.
Otherwise yields FALSE.

%ISOPEN Attribute: always FALSE for Implicit Cursors

Yields TRUE if the cursor is open else returns FALSE. 
Oracle closes the SQL cursor automatically after executing its associated SQL statement. As a result, %ISOPEN always yields FALSE.

%NOTFOUND Attribute: used to check whether a DML statement has failed to change rows


%NOTFOUND is the logical opposite of %FOUND
Yields TRUE if the DML statement affects no rows, or a SELECT INTO statement returns no rows
Otherwise it yields FALSE

%ROWCOUNT Attribute: used to find out how many rows are affected so far


%ROWCOUNT yields the number of rows affected by the DML statement, or returned by a SELECT INTO statement
Yields 0 if the DML statement affected no rows, or a SELECT INTO statement returned no rows
Implicit Cursor: Example 1
         Using SQL%ROWCOUNT 

 DECLARE
   v_tot_rows   NUMBER (3);
BEGIN
   DELETE FROM emp
         WHERE deptno = 10;
   /* PL/SQL will use an implicit cursor to process the above statement */
   v_tot_rows := SQL%ROWCOUNT;
   /* Implicit Attribute %ROWCOUNT is used to find the number of  rows affected by the DELETE command */
   DBMS_OUTPUT.put_line('Total records deleted : ' || v_tot_rows);
END;

Implicit Cursor: Example 2
       Using SQL%FOUND
DECLARE
   v_empno   NUMBER (4) := &eno;
BEGIN
   DELETE FROM emp
         WHERE empno = v_empno;
   IF SQL%FOUND
   THEN
      /* Making use of %FOUND to find out whether at least one row was affected by the DELETE statement */
      DBMS_OUTPUT.put_line('Delete successful');
   END IF;
END;
Continue reading >>

Thursday, 20 July 2017

SQL is NULL AND is NOT NULL

0 comments

What is Null values:


SQL NULL is an undefined and incomparable value.
It is not equal to zero or space.
It will not occupy any memory.
It is represented by NULL keyword and displayed as space.
It is a standard value supported by every RDBMS tool defined by EF code.
It provides unique treatment for all types of data any athematic operation with null values gives null.

Is null/is not null:


Used to compare null values
Supports with all types of data

The following statement List the employees who are not having commission.

SELECT ename, sal, comm
  FROM emp
 WHERE comm IS NULL;

The following statement List the employees who are having commission.

SELECT ename, sal, comm
  FROM emp
 WHERE comm IS NOT NULL;

The following statement Assign job as executive if job is null.

UPDATE emp
   SET job = 'EXECUTIVE'
 WHERE job IS NULL;

The following statement Assign dept as 10 if deptno is null.

UPDATE emp
   SET hiredate = '19-NOV-2011'
 WHERE hiredate IS NULL;

The following statement removing a column where comm having 5000

UPDATE emp
   SET comm = NULL
 WHERE comm = 5000; 

SQL is NULL or empty


The following statement returns values where commission having NULL or EMPTY

SELECT *
  FROM emp
 WHERE (comm IS NULL OR comm = '');


SQL is NOT NULL or empty


The following statement returns values where commission having NOT NULL or EMPTY

SELECT *
  FROM emp
 WHERE (comm IS NOT NULL OR comm = '');


SQL is NULL or 0(zero)


The following statement returns values where commission having NULL or 0(ZEO)

SELECT *
  FROM emp
 WHERE (comm IS NULL OR comm =0);



You May Like                                             
                            SQL IN Operator
                            SQL Like Operator
                            SQL Between Operator                       



Continue reading >>

Wednesday, 19 July 2017

SQL Between Operator

0 comments

Between Operator use to pick the values with the specified range.
It will Supports with numbers and data values.
Between includes range limits in output.
Not between excludes range limits from output.


SQL Between Syntax


SELECT column1,column2...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;


Example

The following statement returns employee salary who having 10000 and 20000

SELECT *
  FROM emp
 WHERE sal BETWEEN 10000 AND 20000;


Example


The following statement update employee salary 35% who joined between
'1 -JAN-10' and '31-DEC-10'

UPDATE emp
SET Sal = sal + sal * .35
WHERE hiredate BETWEEN '1 -JAN-10' and '31-DEC-10';



SQL Not Between



SQL NOT Between Syntax




SELECT column1,column2...
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;



Example

The following statement returns employee salary who not having 10000 and 20000

SELECT *
  FROM emp
 WHERE sal NOT BETWEEN 10000 AND 20000;



Example


The following statement update employee salary 15% who not joined between
'1 -JAN-10' and '31-DEC-10'

UPDATE emp
SET Sal = sal + sal * .15
WHERE hiredate NOT BETWEEN '1 -JAN-10' and '31-DEC-10';



You May Like                                             
                            SQL IN Operator
                            SQL Like Operator                         
                            SQL is NULL AND is NOT NULL



Continue reading >>

Monday, 17 July 2017

SQL Like Operator

0 comments

SQL like used to search for a pattern in given input supports with character data only.

It uses 2 special character (Meta characters).
(i)                % --->represents zero or more characters.

(ii)             – (underscore) represents one character.



Syntax for SQL Like



SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;


Examples

The following statement List the employees whose name begins with ‘s’


SELECT ename, sal, job
  FROM emp
 WHERE ename LIKE 's%';
             

The following statement List the employees whose name ends with ’s’


SELECT ename, sal, job
  FROM emp
 WHERE ename LIKE '%s';

The following statement List the employees whose name ends with ’S’(Capital),’s’(Small)


SELECT ename, sal, job
  FROM emp
 WHERE ename LIKE '%s' OR ename LIKE '%S';

The following statement List the employees whose name begins and ends with ‘s’


SELECT ename, sal, job
  FROM emp
WHERE ename LIKE 's%s';


The following statement List the employees whose name got 5 letters


SELECT ename, sal, job
  FROM emp
WHERE ename LIKE '-----';


The following statement List the employees whose name got 2nd letter as ‘i’


SELECT ename, sal, job
  FROM emp
WHERE ename LIKE '_i%';

The following statement List the employees whose name got ‘LL’


SELECT ename, sal, job
  FROM emp
 WHERE ename LIKE '%LL%';


Searching for Meta characters

Using escape option we can search for Meta characters in given input.

List the employees whose name got ‘_‘(underscore)


SELECT ename
  FROM emp
WHERE ename LIKE '%\_%' ESCAPE '\';

Output

ANIL_KUMAR
KIRAN_KUMAR
SANDEEP_REDDY

List the employees whose name got ‘%’ symbol


SELECT ename, sal, job
  FROM emp
WHERE ename LIKE '%\%%' escape '\';

Output

ANIL%KUMAR
KIRAN%KUMAR
SANDEEP%REDDY

SQL NOT Like Operator





Syntax for SQL NOT Like



SELECT column1, column2, ...
FROM table_name
WHERE columnN NOT LIKE pattern;


Examples



The following statement List the employees whose name not begins with ‘s’


SELECT ename, sal, job
  FROM emp
 WHERE ename NOT LIKE 's%';
             

The following statement List the employees whose name not ends with ’s’


SELECT ename, sal, job
  FROM emp
 WHERE ename NOT LIKE '%s';

The following statement List the employees whose name not ends with ’S’(Capital),’s’(Small)


SELECT ename, sal, job
  FROM emp
 WHERE ename NOT LIKE '%s' OR ename NOT LIKE '%S';

The following statement List the employees whose name not begins and not ends with ‘s’


SELECT ename, sal, job
  FROM emp
WHERE ename  NOT LIKE 's%s';



You May Like                                             
                            SQL IN Operator                           
                            SQL Between Operator
                            SQL is NULL AND is NOT NULL    

Continue reading >>