PLSQL Operators

PL SQL Operators

Relational operators compare two expressions or values and return a Boolean result. PL/SQL supports operators like

=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN

Operator	Meaning
=	equal to
<>, !=, ~=, ^=	not equal to
<	less than
>	greater than
<=	less than or equal to
>=	greater than or equal to

Is NULL operator checks if
Example code:

declare
   a number ( 2 ) := 50 ;
   b number ( 2 ) := 15 ;
begin
   if ( a = b ) then
      dbms_output.put_line ( '1-a is equal to b' );
   else
      dbms_output.put_line ( '1-a is not equal to b' );
   end if;
   if ( a < b ) then
      dbms_output.put_line ( '2-a is less than b' );
   else
      dbms_output.put_line ( '2-a is not less than b' );
   end if ;
   if ( a > b ) then
      dbms_output.put_line ( '3-a is greater than b' );
   else
      dbms_output.put_line ( '3-a is not greater than b' );
   end if ;
   -- Lets change value  a and b
   a      := 40 ;
   b      := 90 ;
   if ( a <= b ) then
      dbms_output.put_line ( '4-a is either equal or less than b' );
   end if ;
   if ( b >= a ) then
      dbms_output.put_line ( '5-b is either equal or greater than a' );
   end if ;
   if ( a <> b ) then
      dbms_output.put_line ( '6-a is not equal to b' );
   else
      dbms_output.put_line ( '6-a is equal to b' );
   end if ;
end;

Output:



IS NULL Operator

The IS NULL operator returns the Boolean value TRUE if its operand is null or FALSE if it is not null. test for the state of being null IF variable IS NULL THEN


LIKE Operator

You use the LIKE operator to compare a character, string, or CLOB value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the patterns match or FALSE if they do not match.

The patterns matched by LIKE can include two special-purpose characters underscore _ matches one character and percent sign % matches zero or more characters
example:
select var_name from table_names where var_name LIKE 'JOHN%';

you will get all names starting with JOHN like JOHN, JOHNSON,JOHNATHON etc...

BETWEEN Operator

The BETWEEN operator tests whether a value lies in a specified range. It means "greater than or equal to low value and less than or equal to high value."
Example:
If you want to select some record between some input date and system date

Select * from SOME_TABLE where to_char(beginning_date,'yyyymmdd') between to_char(i_first_date,'yyyymmdd') and to_char(sysdate,'yyyymmdd')

IN Operator

The IN operator tests set membership. It means "equal to any member of
 select * from table names where var_name IN ('JOHN','ERIC');
You will get all records containing names of JOHN and ERIC...

Read More

PLSQL - Decoding time

 PL SQL Decode Function

Some of my friends told me to write something about famous decode :)
So here it goes ....

What is decode?

Decode is implemented function that has the functionality of an IF THEN ELSE statement.
But there is one major difference between implementing these two functionality. DECODE can only work in select statement.You need to have in mind that DECODE is supported only in SQL Context so you don't really working with PL code. More flexible than decode is case statement....

The syntax for the decode function is: decode( expression , search , result [, search , result]... [, default] )

expression is the value to compare.

search is the value that is compared against expression.

result is the value returned, if expression is equal to search.

default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).

So something to decode now....
 let's create some table

CREATE
   TABLE company
   (
      id            NUMBER(32) NOT NULL,
      company_id    NUMBER(8),
      owner_name    VARCHAR2(20 byte),
      street_adress VARCHAR2(20 byte)
   );

insert some data in it

   insert into company values (1,10001,'Nerad Kovacevic','Neradna BB');
   insert into company values (2,10003,'Vedran Rudjic','Shvalerska 22');
   insert into company values (3,10006,'Ljuban Draga','Vlak u snijegu bb');

select * from company;

you will get 

let's say 10001 is IBM company's id, 10003 is Microsoft and 10006 is TrainStation Co :)

and now we want to display some writings instead of company id's so we decode it
Pseudo-code; if i found id by the number of 10001 i will write 'IBM' if that number is 10003 i will write 'Microsoft' if that number is 10006 i will write 'TrainStation Co' if i don't find (ELSE) number that i have in my decode function i will write 'Nameless'

select decode(company_id,10001,'IBM',10003,'Microsoft',10006,'Trainstation Co','Nameless') as company_name , owner_name, street_adress from company;

after running statement we get;

Lets insert one more row...

insert into company values (3,10009,'Unknown','UNKNOWN bb'); 

and run our statement again and for every undefined id we will get Nameless in company name column...

select decode(company_id,10001,'IBM',10003,'Microsoft',10006,'Trainstation Co','Nameless') as company_name , owner_name, street_adress from company;

So that's about it...

Simple decode function....

Anyway you can find it useful in many occasions and it's use is quite simple and in many situations very grateful...

so one last example

 select decode((select id from company where id = 1 ),1,'IBM',2,'Microsoft',3,'Trainstation Co','Nameless') as company_name , owner_name, street_adress from company where rownum < 2;

Happy decoding :D

Read More

PLSQL numeric check

PL SQL numeric check

Assuming that you need some function to check input from the front-end and you want to assure that input is numerical data, you can do it by simple function that has one input parameter and is returning false or true...
So here it goes...

Function F_numeric_val (i_value IN VARCHAR2) return boolean is
l_num NUMBER;
BEGIN
         l_num := to_number(i_value);
        return(true);
exception
   when others then
   return(false);
end;

So basically what this function do?
You have some input on front end, let's say it is 123456A and you want to check it's number consistency.
So when front-end passes you that input you call Function F_numeric_val and check its content.
In this case function will return false value to input 123456A
example;

I have program that needs to check are there any letters from third place of input till it's end.
If there are any letters I don't want to continue my code.

declare
   l_input varchar2(32767) := 'BJ1234567890        '; -- setting inputs default value
   l_errmsg varchar2(32767) := null;
begin
/* substr command means that you're checking some range of chars from desired sign. let's say we have input from l_input parameter. We string it with substr('BJ1234567890       ',3,32767) and we get  '1234567890    ' values from third sign to end of string . If we add trim before input, it will crop spaces before and after string, but not inside the string. Substr(trim('BJ1234567890       '),3,32767) this expression would return 1234567890 value but we just want to see is there any alpha characters after third sign  and we don't mind if there are any spaces so we trim them */

if not f_numeric_val(substr(trim(l_input),3,32767)) then
      l_errmsg :=( 'You don''t have number in you''re data on right places');
      return;
   else
      -- do some code here :)
      l_errmsg := 'all ok';
   end if;
exception
    when others then
       raise_application_error(-20001,'Program stopped '||sqlcode);
end;

 This code will return all ok message....
 Simple :)

Read More