 |
|
ss
Oracle Tips by Burleson |
Displaying PL/SQL Output
Another change with PL/SQL from SQL is
that the database does not return the output. PL/SQL code normally
will change data, insert values, and so forth, inside the database.
It will not normally display results back to the user. To do this we
use a procedure called dbms_output.put_line
to place the results in a buffer that SQL*Plus will
retrieve and display. SQL*Plus must be told to retrieve data from
this buffer in order to display the results. The SQL*Plus command “set
serveroutput on” causes SQL*Plus to
retrieve and display the buffer.
SQL> declare
2 v_line varchar2(40);
3 begin
4 v_line := 'Hello World';
5 dbms_output.put_line (v_line);
6 end;
7 /
PL/SQL
procedure successfully completed.
SQL> set
serveroutput on
SQL> declare
2 v_line varchar2(40);
3 begin
4 v_line := 'Hello World';
5 dbms_output.put_line (v_line);
6 end;
7 /
Hello World
PL/SQL
procedure successfully completed.
The first time the script is run, the result was
just a notice that the script completed successfully. Once we set
serverouput on and rerun the script, the results are shown.
As discussed earlier, this is an anonymous block
of PL/SQL code. It is sent to the database, compiled and executed,
then SQL*Plus
retrieves the results. The script is stored in the SQL*Plus buffer
and can be rerun by executing the forward slash.
SQL> /
Hello World
PL/SQL procedure successfully completed.
The script is not stored in the database as a
stored or named procedure
is. It must be resent to the
database and compiled each time it is executed.
As with SQL
statements, SQL*Plus
variables can be used to make the PL/SQL script dynamic. Just as
with a SQL statement, the variables are local to SQL*Plus and are
substituted before the code is sent to the database.
SQL> declare
2 v_line varchar2(40);
3 begin
4 v_line := 'Hello &name';
5 dbms_output.put_line (v_line);
6 end;
7 /
Enter value for name: John
old 4: v_line := 'Hello &name';
new 4: v_line := 'Hello John';
Hello John
PL/SQL
procedure successfully completed
The SQL
*Plus accept command is a
more flexible method of embedding dynamic data in the script.
SQL> accept
v_string prompt "Enter Your First Name: "
Enter Your
First Name: Thomas
SQL> declare
2 v_line varchar2(40):= '&v_string';
3 begin
4 v_line := 'Hello '||v_line;
5 dbms_output.put_line (v_line);
6 end;
7 /
old 2:
v_line varchar2(40):= '&v_string';
new 2: v_line varchar2(40):= 'Thomas';
Hello Thomas
PL/SQL
procedure successfully completed.
Let’s look at this script a little closer. The
first line is the SQL*Plus accept command to get the SQL*Plus variable
v_string. This line must be executed alone, not part of the
PL/SQL block. At the prompt the name Thomas was entered. Now the
script is run but it is slightly modified from previous examples.
SQL> declare
2 v_line varchar2(40):= '&v_string';
The variable v_line is declared as a
varchar2(40) and is given a default value that equals v_string.
The PL/SQL assignment operator (:=) is used to assign the value. So
v_line is a bucket that gets assigned the string ‘Thomas’. A
developer would read an assignment statement in English as “v_line
gets v_string” to indicate the assignment. Let’s examine a more
complex assignment statement.
4 v_line
:= 'Hello '||v_line;
Line 4 uses the concatenate operator to append
‘Hello ‘ to the front of v_line and then assigns it back to the
variable v_line. The variable v_line now contains the
string ‘Hello Thomas’. Line 5 places the value of v_line in
the buffer to be retrieved by SQL
*Plus.
old 2:
v_line varchar2(40):= '&v_string';
new 2: v_line varchar2(40):= 'Thomas';
These two lines demonstrate SQL*Plus’s verify
function showing us what is substituted before the code is sent to
the database for execution. This information can be switched on/off
with the verify command
SQL> set
verify on
SQL> set verify off
The above book excerpt is from:
Easy Oracle PL/SQL Programming
Get Started
Fast with Working PL/SQL Code Examples
ISBN 0-9759135-7-3
John Garmany
http://www.rampant-books.com/book_2005_1_easy_plsql.htm
|
