When developing a database program, you often need to record some test data in the database table object. If you create an entry-entered stored procedure for each table object, it seems trouble. Here, an example PL / SQL code is given, and the caller gives the table object name to be inserted into the test data. After the number of rows to be inserted, the process can generate random data, and entered into the table object.
/ * ----------- Insert the actual row into a given table object. --------------------------- * / Procedure INSERT_ROWS (P_TABLENAME VARCHAR, P_NUMROWS NUMBER) AS - Defines the table object of the DBA_TAB_COLUMNS view type. Definition information of the field stored for the given table TYPE TABLE_DEFINE IS TABLE OF DBA_TAB_COLUMNS% ROWTYPE INDEX by binary_integer; Td Table_Define; I binary_integer
- Define the Dynamic SQL query cursor l_cursor number: = dbms_sql.open_cursor; l_ignone number; - Dynamic SQL statement character variable sqlstr varchar2 (1000); begin - Definition information for all fields of the table I: = 0; for REC in (SELECT * FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = Upper (p_tablename)) LOOP i: = i 1; TD (i): = Rec; dbms_output.put_line (TD (i) .table_name); end loop; - Organize Insert SQL Field string SQLSTR: = 'INSERT INTO' || TD (1) .table_name || '('; for var in 1..i loop sqlstr: = sqlstr || td (var) .COLUMN_NAME; if (Var <> i) THEN SQLSTR: = SQLSTR || ','; end if; end loop; - Organize the parameter string of the INSERT SQL statement SQLSTR: = SQLSTR || ')' || 'VALUES ('; for var in 1..i loop sqlstr: = SQLSTR || ':' || TD (var) .COLUMN_NAME; IF (var <> i) Then SQLSTR: = SQLSTR || ','; end if; end loop; sqlstr: = SQLSTR || ')';
DBMS_OUTPUT.PUT_LINE (SQLSTR);
