PL/SQL is a case-independent language, unless you specifically set up Oracle to be case-sensitive or explicitly define object names within the “” marks. In our careers, we have seen the use of camelBack, all_lower_case, crammedupvariablenames, and Mixed_Variations. For clarity, this reference will use the standards outlined in Oracle’s wiki—mainly these:
-
All keywords and Oracle supplied objects are UPPERCASE.
-
User-defined variables and objects are lowercase.
-
There will be no camelBackScrunchedUpVariablesThatAreDifficultToRead.
-
All words are separated with an underscore (_) symbol.
We also align all long-running code blocks vertically, as follows:
SQL> SELECT contact_id 2 , member_id 3 , first_name 4 , last_name 5 , last_updated_by 6 , last_update_date 7 FROM video_store.contact 8 WHERE member_id = 1001 9 AND contact_type = 1003 10 / Variable Data Types:
|
Data Type |
Prefix |
Example |
Comment |
|---|---|---|---|
|
Boolean |
B |
lb_employee_status |
Prefix common variables |
|
Date |
D |
ld_hire_date |
|
|
Number |
N |
gv_bonus_percentage |
|
|
Varchar2 |
V |
gv_department |
|
|
Cursor |
cur, c |
c_item |
Prefix cursors and records like this |
|
Record |
rec, r |
r_employee |
|
|
Type |
typ, t |
typ_monthly_sale |
|
|
Associative array |
aa |
aa_sales_rank |
Lexicon varies |
|
Varray |
va |
va_top_sales_item |
Lexicon varies |
|
Nested table |
Nt |
nt_customer_address |
Lexicon varies |
|
Exception |
ex, e |
ex_bonus_too_high |
|
|
Table type |
Tt |
tt_customer |
Lexicon varies |
Variables:
|
Scope |
Prefix |
Example |
Comment |
|---|---|---|---|
|
Global Private Variable |
G |
gd_hire_date |
d – date |
|
Local Variable |
L |
lv_first_name |
v – varchar |
|
Parameter |
i, o, io |
pi_employee_id |
i – in, o – out, io – in out |
