AAA

/** INSERT - THAM SO TRUYEN VAO**/
-- 1 gen len insert. Su dung danh sach bien cho ham cha goi
DECLARE
v_sql VARCHAR2(4000); -- export query
v_columns VARCHAR2(4000); -- columns name
v_values VARCHAR2(4000); -- values name
v_table_name VARCHAR2(100):= 'BH_HH';
v_owner VARCHAR2(100):= 'TCDN_MOI_ADAN';
v_variable_label varchar(10):='b_';
BEGIN
v_sql := 'INSERT INTO '|| v_table_name || ' (';

-- list column table
SELECT DISTINCT LISTAGG(lower(column_name), ', ') WITHIN GROUP (ORDER BY column_id)
INTO v_columns
FROM all_tab_columns
WHERE table_name = UPPER(v_table_name) and owner=v_owner ORDER BY column_id; 

-- Kiem tra so luong cot
IF v_columns IS NOT NULL THEN
    v_sql := v_sql || v_columns || ') VALUES (';

    -- b_ + ten cot
    FOR col IN (SELECT column_name FROM all_tab_columns WHERE table_name = UPPER(v_table_name) and owner=v_owner  ORDER BY column_id) LOOP
        v_values := v_values || v_variable_label || lower(col.column_name) || ', ';
    END LOOP;

    -- remove last ,
    v_values := RTRIM(v_values, ', ');

    v_sql := v_sql || v_values || ');';
ELSE
    DBMS_OUTPUT.PUT_LINE('error gen ' || v_table_name);
END IF;

-- export query
DBMS_OUTPUT.PUT_LINE(v_sql);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
-- 2 gen tham so truyen vao cho ham NH_NH (example b_so_hd varchar2,b_ngay_ht number)

SELECT LISTAGG('b_' ||LOWER(COLUMN_NAME) || ' ' || DATA_TYPE , ', ') WITHIN GROUP (ORDER BY COLUMN_ID) AS COLUMN_INFO
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'BH_HH';

-- 3 gen insert in loop
DECLARE
v_sql VARCHAR2(4000); -- export query
v_columns VARCHAR2(4000); -- columns name
v_values VARCHAR2(4000); -- values name
v_table_name VARCHAR2(100):= 'bh_hh_ds';
v_owner VARCHAR2(100):= 'TCDN_MOI_ADAN';
v_variable_label varchar(10):='ds_';
v_loop_label varchar(10):='b_lp';
BEGIN
v_sql := 'INSERT INTO '|| v_table_name || ' (';

-- list column table
SELECT DISTINCT LISTAGG(lower(column_name), ', ') WITHIN GROUP (ORDER BY column_id)
INTO v_columns
FROM all_tab_columns
WHERE table_name = UPPER(v_table_name) and owner=v_owner ORDER BY column_id; 

-- Kiem tra so luong cot
IF v_columns IS NOT NULL THEN
    v_sql := v_sql || v_columns || ') VALUES (';

    -- b_ + ten cot
    FOR col IN (SELECT column_name FROM all_tab_columns WHERE table_name = UPPER(v_table_name) and owner=v_owner  ORDER BY column_id) LOOP
        v_values := v_values || v_variable_label || lower(col.column_name) || '('||v_loop_label||'), ';
    END LOOP;

    -- remove last ,
    v_values := RTRIM(v_values, ', ');

    v_sql := v_sql || v_values || ');';
ELSE
    DBMS_OUTPUT.PUT_LINE('error gen ' || v_table_name);
END IF;

-- export query
DBMS_OUTPUT.PUT_LINE(v_sql);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
-- 4 gen bien insert in loop
SELECT LISTAGG('ds_' ||
LOWER(COLUMN_NAME) || ' ' ||
CASE
WHEN DATA_TYPE = 'VARCHAR2' THEN 'pht_type.a_var'
ELSE 'pht_type.a_num'
END, ', ')
WITHIN GROUP (ORDER BY column_name) AS COLUMN_INFO
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'BH_HH_DS';
-- 5 khai bao bien ho so co do dai - HAM NH
SELECT LISTAGG('b_' ||LOWER(COLUMN_NAME) || ' ' || DATA_TYPE ||
CASE
WHEN DATA_TYPE IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR') THEN '(' || DATA_LENGTH || ')'
WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL THEN '(' || DATA_PRECISION || ',' || DATA_SCALE || ')'
ELSE ''
END, '; ') WITHIN GROUP (ORDER BY COLUMN_ID) AS COLUMN_INFO
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'BH_HH';
-- 6 tao tham so truyen vao cua loop
SELECT LISTAGG('ds_' ||
LOWER(COLUMN_NAME), ', ')
WITHIN GROUP (ORDER BY column_name) AS COLUMN_INFO
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'BH_HH_DS';
-- 7 tao mang -- tu thay ky tu dau
SELECT LISTAGG('b_' ||
LOWER(COLUMN_NAME) || ' out ' ||
CASE
WHEN DATA_TYPE = 'VARCHAR2' THEN 'pht_type.a_var'
ELSE 'pht_type.a_num'
END, ', ')
WITHIN GROUP (ORDER BY column_id) AS COLUMN_INFO
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'BH_HH';
-- 8 DANH SACH COT THEO BANG - SU DUNG O BIEN LENH
SELECT LOWER(LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_ID)) AS COLUMNS
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'BH_HH'; bh_hh_ds