-- 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;