Sometimes when I create procedure, I separate it into some procedures based on the function. I’ll use and call it dynamically from the main procedure. Here is the way how I do it.
I have a procedure “p_get_fee” that has 2 in parameters and 1 out parameter.
CREATE OR REPLACE PROCEDURE P_GET_FEE ( in_kode_toko IN VARCHAR2, in_id_pel IN VARCHAR2, out_fee OUT NUMBER ) IS BEGIN out_fee := 0; SELECT fee INTO out_fee FROM FEE_PELANGGAN WHERE KODE_TOKO = in_kode_toko AND ID_PELANGGAN = in_id_pel; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- Consider logging the error and then re-raise RAISE; END P_GET_FEE;