cau1_2.sql

create procedure sp_Cau1_ThuThapNV_19127582(
    i_EMPNO in EMP_19127582.EMPNO%TYPE,
    o_ENAME out EMP_19127582.ENAME%TYPE,
    o_ANUAL_SAL out number
) as
begin
    select ename, (sal * 12) + NVL(comm, 0) into o_ENAME, o_ANUAL_SAL
    from EMP_19127582 where empno = i_EMPNO;
end;
/


create procedure SP_CAUTRUCBANGEMP_19127582 is
    -- data types
    cursor c_emp is
        select column_name, data_type from ALL_TAB_COLS where table_name = 'EMP_19127582';

    type ROW_TYPE is record(column_name ALL_TAB_COLS.column_name%type, data_type ALL_TAB_COLS.data_type%type);

    -- declare variables
    result ROW_TYPE;
begin
    dbms_output.put_line('Table: EMP_19127582');

    open c_emp;
    loop
        fetch c_emp into result;

        dbms_output.put_line(result.column_name || ' ' || result.data_type);

        exit when c_emp%notfound;
    end loop;
    close c_emp;

end;
/