Let's say I have two tables, payments_received and payments_processed. I want to declare a variable in PL/SQL to copy the data from one table to the other. Both tables have a field that holds the same value, say payor_name. Which table do I use to define the %TYPE, the "from" or the "to"?
PROCEDURE some_proc AS
-- value coming FROM payments_received
-- value going TO payments_processed
v_payor_name payments_received.payor_name%TYPE;
-- OR
v_payor_name payments_processed.payor_name%TYPE;
BEGIN
SELECT payor_name INTO v_payor_name
FROM payments_received
WHERE payment_id = some_payment_id;
UPDATE payments_processed
SET payor_name = v_payor_name
WHERE processed_id = some_processed_id;
END some_proc;
I looked around for some best practice guidance, but didn't find anything that really seemed to cover this aspect of %TYPE/%ROWTYPE usage.
It generally doesn't matter. Presumably, the payor_name column is declared identically in the two tables.
Personally, I'd probably use the source table because that will be consistent if you need to select more than one column (or the entire row) now or in the future. But I wouldn't object if someone wanted to use the destination table as the anchor type.
If there is some reason that it would be better to error out at the SELECT statement or at the UPDATE statement if there is some difference in the declarations, that might be lead you to prefer one over the other. That's pretty uncommon, though, so it is generally a matter of preference and consistency.