Re: The case preference of writing SQL, PL/SQL, SQL*PLUS comands/scripts

From: Spencer <spencerp_at_swbell.net>
Date: Sat, 2 Jun 2001 23:20:33 -0500
Message-ID: <FUiS6.185$Z42.10548@nnrp1.sbc.net>

"Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message news:0dfhht44o0un1gea4ri1bglifq6i59dbb0_at_4ax.com...
> Dear all,
>
> In Oracle PL/SQL training merterial, upper cases are used for key
> words, while lower cases for variable names (ex.1); but in many books
> about Oracle the reverse are used (ex.2).
>
> (ex.1)
> CREATE OR REPLACE FUNCTION sf_age
> (i_date IN DATE)
> RETURN NUMBER
> IS
> v_year1 NUMBER;
> v_year2 NUMBER;
> BEGIN
> v_year1 := TO_NUMBER(TO_CHAR(SYSDATE,'yyyy'));
> v_year2 := TO_NUMBER(TO_CHAR(i_date,'yyyy'));
> RETURN v_year1 - v_year2;
> END;
> /
>
> (ex.2)
> create or replace function SF_AGE
> (I_DATE in date)
> return number
> is
> V_YEAR1 number;
> V_YEAR2 number;
> begin
> V_YEAR1 := to_number(to_char(sysdate,'YYYY'));
> V_YEAR2 := to_number(to_char(I_DATE,'YYYY'));
> return V_YEAR1 - V_YEAR2;
> end;
> /
>
> Which one is the proper usage? Thanks for your comments.
>
> Dino
>
>

ex1. looks valid, and is proper.
ex2. looks valid, but is NOT proper.

(i'm prepared to be toasted for that answer...)

i apologize now for the length of this post, but i believe this is a topic worthy of discussion. thanks for asking Dino. others are welcomed to add their thoughts...

in looking at the two examples, it appears that oracle will interpret these both the same way, there will be no difference (? someone correct me if i'm wrong about this) in the compiled function. (the source text that is stored will be different, of course)

the following examples are also valid syntax, but also not proper.

(ex.3)
CREATE or Replace function sf_AGe
(I_DATE in Date)
Return NUMBER
iS

    V_Year1 number;
    v_yeAr2 NUMBER;
Begin

    V_yEar1 := to_number(TO_CHAR(sysdate,'YYYY'));     v_YeAR2 := TO_NUMBER(to_char(I_DATE,'YYYY'));     return V_year1 - v_YeaR2;
end SF_Age;
/

ex4.(all upper case)

ex5.(all lower case)

i've seen it done all five ways, and every which way in between. and some of the PL/SQL source that gets compiled is valid but is definitely NOT proper.

some important rules: avoid using double quotes around object names. that is, do NOT create case sensitive object names.

i've tried coding and maintaining SQL and PL/SQL several different ways. IM(nsh)O, what works best is the convention used in your ex1: use UPPERCASE for oracle reserved words and object names (like built in functions, table names, column names, etc. basically, any of the things that oracle would normally show you in uppercase from the dictionary. if the dictionary shows mixed case, then the name is case sensitive, and you'll be putting double quotes around it every time you reference it. MiXeDcAsE object names are a BiGnUiSaNcE.

use lower case ONLY for things you have control over within the scope... variable names, comment text, etc. e.g. in a SELECT statement, code all of the oracle keywords, oracle functions, tablenames, column names, in UPPERCASE. use lowercase only for the things that you "name" within the query... like table aliases. that is, things that don't exist outside the scope of the query. (i'm talking here about SQL statements that are developed for use in an application... not a one off adhoc query).

this same coding convention works exceptionally well for PL/SQL.

rule... if it is a reserved word or database object that is defined somewhere else, something which i do not (or cannot) "rename" within the scope of the package, i use UPPERCASE.

for names of arguments, variables, functions, procedures etc. that i define within the scope of the package, i use lowercase. (of course, oracle translates the package names and function names to UPPERCASE for the rest of the database to see once the package is compiled... here is an example package body... (yes, there would be package specification too, which i've omitted here)

CREATE OR REPLACE PACKAGE BODY acp
IS
FUNCTION accum_one
(ls_key IN MYTABLE.COL1%TYPE
,ld_begin IN DATE
)RETURN NUMBER
IS
  li_rc PLS_INTEGER;
BEGIN
  li_rc := 0;
  RETURN li_rc;
END accum_one;
FUNCTION accum_all
(ad_begin_dt IN DATE
)RETURN NUMBER
IS
  CURSOR lcsr_query1 IS

  SELECT t.ROWID
       , t.COL1
       , t.COL2

    FROM MYTABLE t
   ORDER BY 1;
  lrec_prev lcsr_query1%ROWTYPE;
BEGIN
  FOR lrec_curr IN lcsr_query1 LOOP
    accum_one(lrec.COL1, ad_begin_dt);
  END LOOP;
END accum_all;
...
END acp;

note that when the function ACCUM_ONE, defined in package ACP, is called from within the ACP package, it is referenced in lowercase. but when the package function ACP.ACCUM_ALL called from outside the ACP package, it is referred to in UPPERCASE:

DECLARE
  rc NUMBER;
  ld_begin DATE;
BEGIN

• set begin date value ld_begin := TO_DATE('06/01/2001 06:00','MM/DD/YYYY HH24:MI')
• get return code from call to acp.accum_all function rc := ACP.ACCUM_ALL(ld_begin); DBMS_OUTPUT.PUT_LINE('acp.accum_all complete, return code='||rc); END;

in this PL/SQL block, rc and ld_begin are defined only within the scope of the block. therefore, they are defined and referred to in lower case, and always in the same case.

in general, i avoid using MixedCase for variable names. my exception to the "no mixed case" rule is for variables that hold column values or constant strings. to hold column values, i typically use a record (lower case) with columns named in upper case e.g. lrec_curr.COL1) in the cases where i use a scalar variable to hold column valyes, i will some times use a mixed case name something like this ls_prev_COL1. the UPPERCASE portion of the name is always at the end, and is the same as the name of another object. the prefix is in lower case, to indicate that this is something my code has control over. sometimes, for global constant values, i use names like gcs_OK, gci_ERROR, or gci_SUCCESS. the java developers like having a string values (VARCHAR2) returned (e.g. "OK"), the C developers prefer integer values... (0 = success) but i don't always like hardcoding values... 'OK' or -1, and i will use constants gcs_OK CONSTANT ... := 'OK'.

you may have noticed that i also have a convention for naming variables. a lot of developers and authors use v_ for variable, but i don't find that very meaningful. (the real benefit of using v_ as a prefix for all of your variables is that you significantly reduce the risk of name resolution problems that can be encountered when a variable name matches the name of a column in one of your queries... yikes ! i like the idea of prefixing variables with, but i use prefixes that carry a little more meaning:

i use the first character to identify the "scope" of the variable

a = argument
l = local
p = package
g = global

in some packages, i omit the "g" for global when i define a type, and simply use a first character of
t = TYPE

the next character is a 'c' when the variable is a CONSTANT

the next character indicates the datatype of the variable

b = BOOLEAN
d = DATE
i = integer (BINARY_INTEGER, PLS_INTEGER)
n = NUMBER or FLOAT
s = string (CHAR, VARCHAR2)
t = table (pl/sql array) (or type)
r = record or raw

local cursors names typically start with lcsr_ local record names typically start with lrec_

so, variable names starting with:

as_    are "argument string"
li_     are "local integer"
ld_    are "local date"

gcs_ are "global constant string"
at_ are "argument table" (pl/sql table) lcsr_ are local cursors
lrec_ are local records

there are occasions when i will add another character to the prefix part of the name to further indicate special datatype... when dealing with RAWTOHEX and HEXTORAW conversions, for example, lsx_ = "local string hex"

with these conventions, i have to carefully review the column names of views or tables used in cursors, to make sure that the variable names do not overlap with any column names.

(someday, there will be an option to turn on the generation of warning messages when the name of a PL/SQL variable within a package matches the name of a column used in a query, and identify the usage of those column names in the query... someday soon i hope)

anyway, i've inherited thse coding conventions from other coding wizards, and have adopted them. these conventions have been refined and tested over many years. i find these conventions to be an invaluable aid in maintaining SQL and PL/SQL code. (and the importance of consistent indentation and formatting can not be overemphasized.)

i've tried "prettier" ways of doing things, but most of my attempts usually got complicated, and botched things up.

a few simple rules is all you need to improve readability and maintainability.

other posters will undoubtedly have other preferences.

as for the authors that reverse the convention of LOWERCASE and uppercase, i have no idea what's up with that. i suppose it's some coding convention they learned somewhere, maybe they are rebels that overturn convention, or maybe there's some other benefit i'm not aware of... i eye that code a little suspiciously... maybe one of those authors has an explanation.... but i dunno.

btw.. most of the oracle docs and the oracle press books follow the convention shown in ex1. Received on Sat Jun 02 2001 - 23:20:33 CDT