Create stored procedure from set of SQL – Oracle

I have a set of SQL like and I would like to convert it to function or a stored procedure in an automated way:

create table test as
select * from test1;

DELETE FROM test
WHERE
    e_id LIKE '%00-01';



MERGE INTO test tgt USING (
  SELECT 
    LISTAGG(e_val, ':') WITHIN GROUP(
      ORDER BY 
        e_id
    ) OVER(PARTITION BY unique_id, line) e_val, 
    CASE WHEN e_id IN ('BHT03-02', 'BHT03-03') THEN 'Y' ELSE 'N' END del 
  FROM 
    test 
  WHERE 
    e_id IN (
      'ABC03-01'
    )
) src ON (
  tgt.unique_id = src.unique_id 
  AND tgt.line = src.line 
  AND tgt.e_id = src.e_id
)
WHEN MATCHED THEN 
UPDATE 
SET 
  tgt.e_value = src.e_value DELETE 
WHERE 
  src.del = 'Y';

Is there any easy way of creating a stored procedure or function using this?

Answer

You can just enclose this code between procedure definition

example

create or replace procedure do_some_work
as

begin
  --paste your code here;
end;

you can run this procedure by calling it :

EXEC do_some_work;

Advertisement

Answer