Let us examine the concept with the following listing.
Listing 1
CREATE OR REPLACE
PROCEDURE merge_employee_p
as
Employee_no emp.EMPLOYEE_ID%type := 0;
Employee_name emp.FIRST_NAME%type := '';
Employee_job emp.JOB_ID%type := '';
Employee_dept emp.DEPARTMENT_ID%type := 0;
Error_code varchar2(400);
Error_message varchar2(400);
counter number :=0;
begin
dbms_output.put_line('selection of records for processing started');
For process_record in
(
select EMPLOYEE_ID,FIRST_NAME,JOB_ID,DEPARTMENT_ID
from emp
)
Loop
Employee_name := 'MR.'||process_record.FIRST_NAME;
Employee_no := process_record.EMPLOYEE_ID;
Employee_job := process_record.JOB_ID;
Employee_dept := process_record.DEPARTMENT_ID;
dbms_output.put_line('loop'||counter);
MERGE INTO merge_employee a
USING (
SELECT
Employee_name Employee_name,/*Employee_name is the alias*/
Employee_no Employee_no,
Employee_job Employee_job,
Employee_dept Employee_dept
FROM dual
) p
ON (a.EMPLOYEE_ID = p.Employee_no)
WHEN MATCHED THEN UPDATE SET
a.FIRST_NAME = p.Employee_name,
a.JOB_ID = p.Employee_job
a.DEPARTMENT_ID = p.Employee_dept
WHEN NOT MATCHED THEN INSERT
(
a.EMPLOYEE_ID,
a.FIRST_NAME,
a.JOB_ID,
a.DEPARTMENT_ID
)
VALUES
(
p.Employee_no,
p.Employee_name,
p.Employee_job,
p.Employee_dept
);
counter:=counter+1;
dbms_output.put_line('successfully insert completed for record no :'||counter);
end loop;
EXCEPTION
WHEN OTHERS THEN
error_code := SQLCODE;
error_message := SQLERRM;
dbms_output.put_line('failed due to '|| SQLERRM||' '||SQLCODE);
end merge_employee_p;Explanation
Here the records are being picked from emp table. To make
the example easy, only four fields (EMPLOYEE_ID, FIRST_NAME, JOB_ID, DEPARTMENT_ID)
from emp table have been used as source data. Further, to make the scenario
lively, the "FIRST_NAME" field has been manipulated before being
merged into a test table "merge_employee."
This requires the data from the source to be picked record
wise one after the next. As the records are pulled into the local variables one
at a time, they are manipulated and merged into the destination table through a
loop, one insertation or updation per iteration.
But the crucial point here is that we are not peeking up
records from any table/view/sub_query, instead they are from the dual table
which picks the local variables found within the scope of current iteration. The
use of column alias in selecting the local variables from dual is the solution
to the problem raised in the introduction part.
The interesting thing is that any fail to declare the alias
in the select clause will not face any syntactical error as expected. But there
seems to be an inherent problem with the merge without alias. It throws a
runtime error as mentioned below.
Figure 1
Indicating that the scope of the variables is not powerful
enough demands the use of aliasing. The user can run the same piece of code
without the alias in order to find the difference.
Structures of source table
Figure 2
Execution of the procedure
Figure 3
.
.
Structures of destination table after merging is done (The
first names have been appended)
Figure 4
