mysql database question Create a stored procedure PDYNTABLE

mysql database question

Create a stored procedure ( P_DYN_TABLE) that creates a table DYNAMICALLY by having 2 input parameters, 1 which is the SOURCE table for that new table, and the second input is the name of the new table. You will need to define two statements, 1 for the structure of the table, AND 1 statement to populate it (Hint for the first statement you will need to build the CREATE statement by concatenating the NEW table name and the SOURCE table. For the second statement you will need to use the PREPARE and EXECUTE commands. ).

Solution

CREATE TABLE department_new (id NUMBER, emps t_createemplist) NESTED TABLE emps STORE AS emp_table; INSERT INTO department_new VALUES ( 10, t_createemplist( t_emp(1, \'JOHN\'), t_emp(2, \'ROGER\'))); DECLARE deptarmentid NUMBER; ename VARCHAR2(20); BEGIN EXECUTE IMMEDIATE \'SELECT z.id, e.name FROM department_new z, TABLE(z.emps) p WHERE p.id = 1\' INTO deptartmentid, ename; END;