Oracle PL/SQL Interactive Workbook, 2/e

Test Your Thinking Solutions Chapter 2 PLSQL Concepts

  1. In order to calculate the area of a circle, the circle's radius must be squared and then multiplied by pi. Write a program that calculates the area of a circle. The value for the radius should by provided with the help of the substitution variable. Use 3.14 for the value of pi. Once the area of the circle is calculated, display it on the screen.

    Answer: Your answer should look similar to the following:
       v_radius NUMBER := &sv_radius;
       v_area NUMBER;
       v_area := POWER(v_radius, 2) * 3.14; 
       DBMS_OUTPUT.PUT_LINE ('The area of the circle is: '||v_area);

    In this exercise, you declare two variables v_radius and v_area to store the values for the radius of the circle and its area respectively. Next, you compute the value for the variable v_area with the help of the built-in function POWER and the value of the v_radius. Finally, you display the value of the v_area on the screen.

    Assume that number 5 has been entered for the value of the variable v_radius. Then, the script produces the output shown below:

    Enter value for sv_radius: 5
    old   2:    v_radius NUMBER := &sv_radius;
    new   2:    v_radius NUMBER := 5;
    The area of the circle is: 78.5
    PLSQL procedure successfully completed.
  2. Rewrite the script ch02_2b.sql, version 2.0. In the output produced by the script, extra spaces appear after the day of the week. The new script must remove the extra spaces after the day of the week.

    The current output:

    Today is Friday   , 23:09

    The new output should have the format as shown below:

    Today is Friday, 23:09

    Answer: Your answer should look similar to the following. All changes are shown in bold letters:

       v_day VARCHAR2(20);
       v_day := TO_CHAR(SYSDATE, 'fmDay, HH24:MI');
       DBMS_OUTPUT.PUT_LINE ('Today is '|| v_day);

    In this script, you modify the format in which you would like to display the date. Notice that the word 'Day' is now prefixed by the letters 'fm'. These letters guarantee that extra spaces will be removed from the name of the day. When run, this exercise produces the output shown below:

    Today is Tuesday, 18:54
    PLSQL procedure successfully completed.

Select a Chapter for Test Your Thinking Solutions

  1. Programming Concepts
  2. PLSQL Concepts
  3. General Programming Language Fundamentals
  4. SQL in PLSQL
  5. Conditional Control: IF Statements
  6. Conditional Control: CASE Statements
  7. Error Handling and Built-In Exceptions
  8. Iterative Control
  9. Introduction to Cursors
  10. Exceptions
  11. Exceptions: Advanced Concepts
  12. Procedures
  13. Functions
  14. Packages
  15. Advanced Cursors
  16. Stored Code
  17. Triggers
  18. Collections
  19. Records