Procedure HQT
unknown
sql
2 years ago
2.2 kB
1
Indexable
Never
-- 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;