Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

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

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks