PLSQL stuff

I’m learning new stuff all the time. Here’s one of my new tricks in PLSQL.

When you’re dealing with huge volumes of data, often you need to manipulate this data and execute multiple steps one after another. It becomes cut-and-paste work of a huge amount of identifiers. For example, first fetch me all the employees that have following IDs, and then update the leaves entitlement with 20 days for the employee names retrieved from the previous query. The identifiers need to be between single-quotes.

There are basically two solutions to this data-intensive cut-and-paste work.

First of all, you could just paste all the data in a text editor: identifiers line by line. Then you would need to replace the selected data with \n with ','. This will return a single line with all identifiers separated by comma’s and single quotes. You could then copy this line and use it in your next query.

Or you can be more clever and just eliminate the above intermediate and redundant step. Just directly manipulate your data in the query itself. Use the below concatenate function in your SELECT statement itself:

concat(concat('''',i. service_id),''',')

Even more smart: just automate it altogether and loop!

-- Loop: Select employee name and country values for following employees.

BEGIN
FOR empl IN (
SELECT empl.name, empl.country
FROM tb_employees
WHERE empl.id IN('121','122','123','124','125','126'..'N')
ORDER BY empl.id
)

-- Update the data
LOOP
-- update leave entitlement to 20 with retrieved country from previous query

UPDATE tb_leaves SET
leave_employee_country = empl.country,
leave_employee_entitlement = '20'
WHERE leave_employee_name = empl.name
END LOOP;
END;


--commit (do not forget commit when you update !!!!! )
--end of file

Ha!

Leave a Reply

Your email address will not be published. Required fields are marked *