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