Decode VS Case in Oracle

Decode: In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

select decode(col1, 1,one,2,two) from table_name


if col1 value 1 then it will print as 'one' in result
if col1 value 2 then it will print as 'two' in result

decode will give performance issue. so now CASE came with same funtionality with better performance.

Case:

select main_acnt_code as code, main_acnt_name as name
from fm_main_account
where MAIN_FRZ_FLAG = 'N'
and case in_Code
when 'CAPEX' THEN main_class_code_05 = 'Y';
when 'MAIN_ACC2' THEN main_class_code_02 = 'Y';
end case;

select statements

Below two select statements will give same results

select *from employee where 10=emplid
select *from employee where emplid=10

Decode in Oracle

In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

For example:

You could use the decode function in an SQL statement as follows:

SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN
result := 'IBM';

ELSIF supplier_id = 10001 THEN
result := 'Microsoft';

ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';

ELSE
result := 'Gateway';

END IF;


Question: I would like to know if it's possible to use decode for ranges of numbers, ie 1-10 = 'category 1', 11-20 = 'category 2', rather than having to individually decode each number.

Answer: Unfortunately, you can not use the decode for ranges of numbers. However, you can try to create a formula that will evaluate to one number for a given range, and another number for the next range, and so on.

For example:

SELECT supplier_id,
decode(trunc ((supplier_id - 1) / 10), 0, 'category 1',
1, 'category 2',
2, 'category 3',
'unknown') result
FROM suppliers;
In this example, based on the formula:

trunc ((supplier_id - 1) / 10

The formula will evaluate to 0, if the supplier_id is between 1 and 10.
The formula will evaluate to 1, if the supplier_id is between 11 and 20.
The formula will evaluate to 2, if the supplier_id is between 21 and 30.

Incremental Update

Using Merge Join:

SQL> MERGE
INTO target_table tgt
USING source_table src
ON ( src.object_id = tgt.object_id )
WHEN MATCHED
THEN
UPDATE
SET tgt.object_name = src.object_name
, tgt.object_type = src.object_type
WHEN NOT MATCHED
THEN
INSERT ( tgt.object_id
, tgt.object_name
, tgt.object_type )
VALUES ( src.object_id
, src.object_name
, src.object_type );



Using SQL%ROWCOUNT (will give last DML updated row count):


SQL> DECLARE
i PLS_INTEGER := 0;
u PLS_INTEGER := 0;
BEGIN
FOR r IN ( SELECT * FROM source_table )
LOOP

UPDATE target_table tgt
SET tgt.object_name = r.object_name
, tgt.object_type = r.object_type
WHERE tgt.object_id = r.object_id;

u := u + SQL%ROWCOUNT;

IF SQL%ROWCOUNT = 0 THEN
INSERT INTO target_table
( object_id, object_name, object_type )
VALUES
( r.object_id, r.object_name, r.object_type );
i := i + 1;
END IF;

END LOOP;
DBMS_OUTPUT.PUT_LINE( u || ' rows updated.' );
DBMS_OUTPUT.PUT_LINE( i || ' rows inserted.' );
END;

Type of Cursors

Two types:

1. Implicit cursor
2. Explicit cursor

Implicit cursor: Are used by oracle.
eg1:
select name into var_name from emp;
eg2:
FOR r IN ( SELECT * FROM source_table )
LOOP

UPDATE target_table tgt
SET tgt.object_name = r.object_name
, tgt.object_type = r.object_type
WHERE tgt.object_id = r.object_id;

u := u + SQL%ROWCOUNT;

IF SQL%ROWCOUNT = 0 THEN
INSERT INTO target_table
( object_id, object_name, object_type )
VALUES
( r.object_id, r.object_name, r.object_type );
i := i + 1;
END IF;

END LOOP;

Explicit Cursors :For explicit cursors developer should do the following things

1. Declare the cursor
CURSOR company_cur IS
SELECT company_id FROM company;

2. Open the cursor (if not already open)
OPEN company_cur ;
3. Fetch one or more rows from the cursor
LOOP
FETCH company_cur INTO var_emplid;

EXIT WHEN company_cur %NOTFOUND;
-- do some data process here

END LOOP;

4. Close the cursor
CLOSE company_cur;

how many form-bean objects will be created by Web Container?

That depends on scope attribute in action element(struts-config.xml).

if scope= request then WC creates form bean object for each request if object not exist in ServletRequest object otherwise it uses existing object.
if scope= session then WC creates form bean object for each session if object not exist in HttpSession object otherwise it uses existing object.