A sample PL/SQL program

Before we plunge into code analysis techniques, let us write down a standard PL/SQL program for demonstrating code analysis, profiling, and tracing. The following PL/SQL procedure calculates the score of a user in an exam (Note that negative scoring is applicable). The procedure P_CALC_USER_POINTS declares a local function and a procedure to calculate the points:

/*Create a PL/SQL procedure*/
CREATE OR REPLACE PROCEDURE p_calc_user_points
(p_user VARCHAR2 DEFAULT USER, p_correct NUMBER, p_wrong NUMBER)
IS
 l_num NUMBER;

 /*A local function F_CALC_POINTS */
 FUNCTION f_calc_points (p_ques NUMBER, p_factor NUMBER)
 RETURN NUMBER
 IS
 BEGIN
  RETURN (p_ques*p_factor);
 END;

 /*A local procedure */
 PROCEDURE P_NET_CALC (p_net_points OUT NUMBER) IS
 BEGIN
  p_net_points := f_calc_points (p_correct,4) + f_calc_points (p_wrong,-2);
 END;

/*Main procedure body */
BEGIN
 p_net_calc (l_num);
 DBMS_OUTPUT.PUT_LINE (USER||' earned '||TO_CHAR (l_num)||' points');
END;
/

We will...