RelationalDBDesign 





PL/SQL Programming   «Prev 
Using DBMS_OUTPUT.PUT_LINE

Display values on the SQL*Plus screen

  1. To connect to the database, type a valid user name, password, and service. Type PETSTORE in the User Name box. Type GREATPETS in the Password box. Type MYDB in the Host String box. Click the OK button.
  2. To display the result of the PL/SQL block on the screen, set the server output on. Type the following command at the SQL > prompt:
    SET SERVEROUTPUT ON
    End your SET command by clicking ENTER. This tells SQL*Plus to execute the command.
  3. Accept the value from the user and store it in the av_price_of_pet variable. Type the following command at the SQL > prompt:
    ACCEPT av_price_of_pet PROMPT 'Please enter the price of the pet: '
    End your ACCEPT command by clicking ENTER. This tells SQL*Plus to execute the command.
  4. Next, begin the main block with the DECLARE statement. Type DECLARE at the SQL > prompt.
    End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  5. Next, declare a variable of type NUMBER and call it v_num_price_of_pet. Assign the accepted value from the user to this variable. Type v_price_of_pet NUMBER := &av_price_of_pet; at the 2 prompt.
    End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  6. Start the executable section of the block. Type BEGIN at the 3 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  7. Display the string value using the DBMS_OUTPUT.PUT_LINE package procedure.
    Type DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_price_of_pet)); at the 4 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  8. End the block. Type END; at the 5 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  9. Type / at the 6 prompt for SQL*Plus to compile your PL/SQL block. End your command by clicking ENTER.
  10. SQL*Plus now displays the result of the compilation of your block.

Invoking a Standalone Procedure from SQL*Plus

SQL> -- Invoke standalone procedure with CALL statement
SQL>
SQL> CALL award_bonus(179, 1000);
Call completed.
SQL>
SQL> -- Invoke standalone procedure from within block
SQL>
SQL> BEGIN
2 award_bonus(179, 10000);
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
Using the BEGIN-END block is recommended in several situations. For example, using the CALL statement can suppress an ORA-n error that was not handled in the PL/SQL subprogram.