![]() SELECT level, id, position, operation, options, Retrieve information from PLAN_TABLE into cursor EXPLAIN_ROWS. * to procedure, to uniquely identify statement. The example EXPLAIN_OUT procedure retrieves the information from this table and formats the output in a nested manner that more closely depicts the order of steps undergone in processing the SQL statement. The user has also assigned a statement ID to this statement. In this example, the user has used the EXPLAIN PLAN command to retrieve information about the execution plan for a statement and has stored it in PLAN_TABLE. The user would then see the following information displayed in the output pane: Loop number = 1 Wages = 2000 ![]() ' Wages = '|| TO_CHAR(total_wages)) /* Debug line */Ĭounter := counter + 1 /* Increment debug counter */Īssume the EMP table contains the following rows:Īssume the user executes the following statements in SQL*Plus: SET SERVEROUTPUT ON ![]() Total_wages := total_wages + emp_record.salĭBMS_OUTPUT.PUT_LINE('Loop number = ' || counter || SELECT sal, comm FROM emp WHERE deptno = dnum Įmp_m := NVL(emp_m, 0) The function includes several calls to the PUT_LINE procedure:ĬREATE FUNCTION dept_salary (dnum NUMBER) RETURN NUMBER IS This function queries the employee table and returns the total salary for a specified department. This package can also be used to enable you to retrieve information about an object and format this output, as shown in " Example 3: Retrieving Information About an Object". The DBMS_OUTPUT package is commonly used to debug stored procedures and triggers.
0 Comments
Leave a Reply. |