 |
|
ss
Oracle Tips by Burleson |
Use Consistent PL/SQL Variable
Naming
Lastly, we need to talk about the importance of
using consistent naming conventions. You should define the
programming style you will use in defining variables and all members
of the programming staff should follow it. In the example above,
varchars start with “v_”, constants “c_”, dates “d_” and numbers as
“n_”. As the programming procedures and functions become larger and
more complicated, the ability to identify what a variable is becomes
more important. This naming example is rather simplistic and will be
used throughout this book.
The following script accepts two numbers. Because
they are stored in SQL*Plus variables, they are actually character strings. The
to_number function
is used to convert them from
strings to numbers.
SQL> accept
v_numb1 prompt "Enter the First Number: "
Enter the First Number: 5
SQL> accept v_numb2 prompt "Enter the Second Number: "
Enter the Second Number: 3
SQL> set serveroutput on
SQL> set serveroutput on
SQL> set verify off
SQL> declare
2 v_out number := 0;
3 begin
4 v_out := to_number('&v_numb1') *
to_number('&v_numb2');
5 dbms_output.put_line ('Multiplication:
'||to_char(v_out));
6 v_out := to_number('&v_numb1') /
to_number('&v_numb2');
7 dbms_output.put_line ('Division:
'||to_char(v_out));
8 v_out := to_number('&v_numb1') +
to_number('&v_numb2');
9 dbms_output.put_line ('Addition:
'||to_char(v_out));
10 v_out := to_number('&v_numb1') -
to_number('&v_numb2');
11 dbms_output.put_line ('Subtraction:
'||to_char(v_out));
12 end;
13 /
Multiplication: 15
Division: 1.66666666666666666666666666666666666667
Addition: 8
Subtraction: 2
The built-in function to_number
is actually a PL/SQL
function provided by Oracle and it is the same single row function
used in a SQL statement. It is passed a character variable and
returns a number. Internally, Oracle will automatically convert
between number and characters without you having to implicitly use a
function to convert. The previous example could be re-written without
the to_number function, like this.
SQL> declare
2 v_out number := 0;
3 begin
4 v_out := &v_numb1 * '&v_numb2';
5 dbms_output.put_line ('Multiplication:
'||to_char(v_out));
6 v_out := '&v_numb1' / &v_numb2;
7 dbms_output.put_line ('Division:
'||to_char(v_out));
8 v_out := &v_numb1 + '&v_numb2';
9 dbms_output.put_line ('Addition:
'||to_char(v_out));
10 v_out := '&v_numb1' - &v_numb2;
11 dbms_output.put_line ('Subtraction:
'||to_char(v_out));
12 end;
13 /
Multiplication: 15
Division: 1.66666666666666666666666666666666666667
Addition: 8
Subtraction: 2
PL/SQL procedure successfully completed.
If the database can determine that a conversion is
necessary, it will automatically execute it. Sometimes, for
readability, you may want to implicitly convert variables so that
others reading the code will know that a conversion it taking place.
When a PL/SQL
block contains a SQL statement,
that statement can include any function or
conversion that a stand alone SQL statement can use to include
single-row functions, multi-row functions, grouping, sorting and the
special decode function.
All the single-row functions can also be used in
the PL/SQL block, outside of a SQL statement.
v_name :=
lower(&v_string);
v_str_long := ‘Hello Student’||CHR(10)||’Bye’;
The first example uses the lower function to
change the SQL*Plus variable to lowercase as it assigned it to the
v_name variable. The second example uses the concatenate function
(represented by two double-bars like ||) to format the output by
adding a carriage return between the text fields. We also have PL/SQL
functions like the dbms_output.put_line
procedure that will trim off white space.
dbms_output.put_line (‘ This has spaces. ’);
The leading and training spaces will be removed
when placed in the buffer.
Just like in SQL
, dates are actually stored in
variables as a type of number. This does not mean that a date can be
treated as a number, but it does allows you to perform date math and
use the date functions in PL\SQL just like a SQL statement. It is
common to encounter dates as strings which will require the to_date
function to convert the string to an actual date variable.
d_date1 date
:= ’25-Jan-2005’; -- fails
d_date2 date := to_date(’Jan-25-2005’,’MON-DD-YYYY’);
v_date varchar2(30) := to_char(sysdate); -- date to string
This book does not cover the Oracle-provided
built-in functions of SQL
, but we will be looking at some
PL/SQL functions. If you need help with SQL functions please refer to
Chapter 2 of the companion book: Easy Oracle SQL.
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
|
