Procedure HQT

-- Procedure 
-- Nhan input dau vao la mot chuoi ID array, cach nhau bang dau phay, và 'choice' la loai ID (nhanvien, loaihang, khachhang,...)
-- Output la chuoi thong tin tuong ung voi chuoi ID dau vao
CREATE OR REPLACE PROCEDURE Proc_Print_List(id_array varchar2, choice varchar2)
as
    id_array_local VARCHAR2(1000);
    start_pos SMALLINT;
    comma_pos SMALLINT;
    current_id VARCHAR(1000);
    end_loop INT;
    c1 SYS_REFCURSOR;
BEGIN
    id_array_local := id_array;
    start_pos := 1;
    comma_pos := INSTR(id_array_local, ',');
    LOOP
        EXIT WHEN end_loop = 1;
        -- Tach ID string dau vao thanh cac ID rieng le
        IF comma_pos > 0 THEN
            current_id := substr(id_array_local, start_pos, comma_pos - start_pos);
            end_loop := 0;
        ELSE
            current_id := substr(id_array_local, start_pos);
            end_loop := 1;
        END IF;     
        -- Su dung current id    
        IF end_loop = 0 THEN
            id_array_local := substr(id_array_local, comma_pos + 1);
            -- comma_pos := LOCATE(',', id_array_local);
            comma_pos := INSTR(id_array_local, ',');
        END IF;
        dbms_output.put_line('- ID hien tai = '||current_id);
        -- Chia truong hop, neu choice = nhanvien thì select tu bang NHANVIEN
        IF LOWER(choice) = 'nhanvien' THEN
            open c1 for
            SELECT * FROM NHANVIEN WHERE MANV=UPPER(current_id);
        -- Neu choice=Hoadon thi lay thong tin tu HOADON tuong ung voi id
        ELSIF LOWER(choice) = 'hoadon' THEN
            open c1 for
            SELECT * FROM HOADON WHERE MAHD=UPPER(current_id);
        ELSIF LOWER(choice) = 'khachhang' THEN
            open c1 for
            SELECT * FROM KHACHHANG WHERE MAKH=UPPER(current_id);
        ELSIF LOWER(choice) = 'loaihang' THEN
            open c1 for
            SELECT * FROM LOAIHANG WHERE MALOAI=UPPER(current_id);
        END IF;
        DBMS_SQL.RETURN_RESULT(c1);
    END LOOP;
END;

DECLARE
    id_array varchar2(1000);
    choice varchar2(50);
BEGIN
    id_array:=&id_array;
    choice:=&choice;
    Proc_Print_List(id_array, choice);
END;