Display values on SQL*Plus screen
- 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.
- 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.
- 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.
-
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.
- 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.
- 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.
- 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.
- 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.
- Type
/
at the 6 prompt for SQL*Plus to compile your PL/SQL block. End your command by clicking ENTER.
- SQL*Plus now displays the result of the compilation of your block.
Oracle PL/SQL Programming
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.