SQL Basic's - Tutorial

Tutorial Link - I

Tutorial Link - II

Oracle Syntax

1. DECODE
DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database
returns the corresponding result. If no match is found, then Oracle returns default.
If default is omitted, then Oracle returns null.

Examples

This example decodes the value warehouse_id. If warehouse_id is 1, then the function returns 'Southlake';
if warehouse_id is 2, then it returns 'San Francisco'; and so forth. If warehouse_id is not 1, 2, 3, or 4,
then the function returns 'Non domestic'.

Example:

SELECT state,

DECODE(state, 'NY', 'New York', 'NJ', 'New Jersey', 'RI', 'Phode Island', 'unknown') As state_name

SELECT product_id,
DECODE (warehouse_id, 1, 'Southlake',
2, 'San Francisco',
3, 'New Jersey',
4, 'Seattle',
'Non domestic')

client_open_pkg.get_organization_client_info:
Decode(pb.profile_exists_flg
, 1
, pb.profile_id
, NULL)

2. FOR Loop

FOR i IN i_w8_profile_detail_list.FIRST .. i_w8_profile_detail_list.LAST
LOOP
FETCH l_local_date_time_cur
INTO l_user_date;
EXIT WHEN l_local_date_time_cur%NOTFOUND;
END LOOP;


3. IF-ELSIF-END IF;

IF i_w8_profile_detail_list(i).ACTION_FLG = c_operation_insert THEN
INSERT INTO w8_file_profile_details(
profile_id
VALUES(
l_w8_file_profileId);
ELSIF i_w8_profile_detail_list(i).ACTION_FLG = c_operation_update THEN

UPDATE w8_file_profile_details
SET w8_type = i_w8_profile_detail_list(i).w8_type
WHERE profile_id = l_w8_file_profileId
AND w8_detail_id = i_w8_profile_detail_list(i).w8_detail_id;

END IF;

4. If table name is not know, then use dual

OPEN o_IBAN_req_cur FOR
SELECT l_IBAN_req_flg
FROM dual;


ao_common_pkg.get_iban_required_flg:

5.IN

ca.iso_country is nothing but country code like "GB".
Here l_firm_country_code will always be "GB" and i_recv_country_code can be any country code.
If both are "GB" then count will be 1, or else count will be 2.

SELECT COUNT(*)
INTO l_match_row_count
FROM country_association ca
WHERE ca.country_assoc = c_country_association
AND ca.iso_country IN (l_firm_country_code, i_recv_country_code);

IF l_match_row_count = 2 THEN
l_IBAN_req_flg:= 1;
ELSE
l_IBAN_req_flg:= 2;
END IF;

OPEN o_IBAN_req_cur FOR
SELECT l_IBAN_req_flg
FROM dual;

RETURN o_IBAN_req_cur;

client_open_pkg.get_organization_client_info

SELECT MAX(last_update_dt)
INTO l_last_updated_date
FROM profile_base
WHERE entity_id = i_entity_id
AND profile_type IN(
ao_constants.c_organization_details
, ao_constants.c_aml_client_details
, ao_constants.c_rebalance_instructions
, ao_constants.c_charity_information
, ao_constants.c_client_basic_profile
, ao_constants.c_organization_type);

6. CASE

CASE
WHEN condition1 THEN return-value1
WHEN condition2 THEN return-value2 . . .
[ ELSE return-value ]
END

, (CASE
WHEN srm.interm_bank_routing_number IS NOT NULL THEN
(SELECT ensu.institution_name
FROM external_num_sys_universe ensu
WHERE ensu.external_num_system = srm.interm_bank_routing_num_type
AND ensu.external_num = srm.interm_bank_routing_number)
WHEN srm.interm_bank_routing_number IS NULL THEN
NULL
END) interm_bank_name

CASE WHEN e.salary > 2000 THEN e.salary
ELSE 2000
END

7. Procedure declaration

PROCEDURE validate_swift_chaps_details(
i_user_id IN sei_user.user_id%TYPE
, i_entity_a_id IN entity_relationship.entity_a_id%TYPE
, i_iban_required_flg IN NUMBER
, o_relationship_id OUT entity_relationship.entity_relationship_id%TYPE
, o_validate_flag OUT NUMBER) AS

Cursor

Hi

From performance point of view isn't Scenario - II a better option, coz we aren't using LEFT OUTER JOIN.

Scenario - I

Oracle - 9i ==> Join is done using Keywords

OPEN o_r85_profile_cur FOR
SELECT DECODE(pb.profile_exists_flg
, 1
, pb.profile_id
, NULL) l_profile_id
, r85.active_flg
, r85.inactive_dt
, r85.r85_profile_ver
FROM profile_base pb
LEFT OUTER JOIN
r85_profile r85
ON pb.profile_id = r85.profile_id
WHERE pb.entity_id = i_entity_id
AND pb.profile_type = ao_constants.c_r85_profile;

Oracle - 8i ==> Join is done using + operator

OPEN o_r85_profile_cur FOR
SELECT DECODE(pb.profile_exists_flg
, 1
, pb.profile_id
, NULL) l_profile_id
, r85.active_flg
, r85.inactive_dt
, r85.r85_profile_ver
FROM profile_base pb
, r85_profile r85
WHERE pb.entity_id = i_entity_id
AND pb.profile_type = ao_constants.c_r85_profile
AND pb.profile_id = r85.profile_id(+);

Scenario - II

BEGIN

SELECT profile_id
INTO l_profile_id
FROM profile_base
WHERE entity_id = i_entity_id
AND profile_type = ao_constants.c_r85_profile
AND profile_exists_flg = ao_constants.c_profile_exists_yes;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_profile_id := 0;
END;

l_section := 'Open cur o_r85_profile_cur';

OPEN o_r85_profile_cur FOR
SELECT profile_id
, active_flg
, inactive_dt
, r85_profile_ver
FROM r85_profile
WHERE profile_id = l_profile_id;

Left Outer Join

Left Outer Join Tutorial

This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).

This link explains outer join better.


We have 2 tables Employee and Address.

Employee
1. EmployeeID
2. EmployeeName
3. Division
4. Salary

Address
1. EmployeeID
2. Phone
3. Address

create table Employee values(EmployeeID number(5), EmployeeName varchar2(20), Division varchar2(20), Salary number(5));

create table Address values(EmployeeID number(5), Phone number(5), Address varchar2(30));

insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (1, 'abc', 'computer', 10000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (2, 'def', 'sales', 20000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (3, 'ghi', 'front desk', 30000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (4, 'jkl', 'production', 40000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (5, 'mno', 'marketing', 50000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (6, 'pqr', 'research', 60000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (7, 'stu', 'marketing', 70000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (8, 'vwx', 'hr', 80000);

insert into Address(EmployeeID, Address) values (1, 4444, '1 street, Bangalore');
insert into Address(EmployeeID, Address) values (3, 6666, '3 street, Bangalore');
insert into Address(EmployeeID, Address) values (5, 3333, '5 street, Bangalore');
insert into Address(EmployeeID, Address) values (7, 7777, '7 street, Bangalore');

select emp.EmployeeID, emp.Division, add.Phone from Employee emp, Address add where emp.EmployeeID = add.EmployeeID(+)

select emp.EmployeeID, emp.Division, add.Phone from Employee emp left outer join Address add on emp.EmployeeID = add.EmployeeID

SQL> select emp.EmployeeID, emp.Division, aa.Phone from Employee emp, Address aa where emp.EmployeeI D = aa.EmployeeID(+);

EMPLOYEEID DIVISION PHONE
---------- -------------------- ----------
1 computer 4444
3 front desk 6666
5 marketing 3333
7 marketing 7777
8 hr
2 sales
6 research
4 production

8 rows selected.

SQL> select emp.EmployeeID, emp.Division, aa.Phone from Employee emp left outer join Address aa on e mp.EmployeeID = aa.EmployeeID 2 ;

EMPLOYEEID DIVISION PHONE
---------- -------------------- ----------
1 computer 4444
3 front desk 6666
5 marketing 3333
7 marketing 7777
8 hr
2 sales
6 research
4 production

8 rows selected.

ORDER BY

Order By - keyword tutorial

This is the order in which Oracle orders the values when ORDER BY is used

Ascending Order:

1
2
A
B
a
b

This is the order in which Oracle orders the values when DESC keyword is used along with ORDER BY

Descending Order:

b
a
B
A
2
1

NVL2

Tutorial on nvl2