PLSQL Procedures – Top Syntax Rules

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.

  • All keywords and Oracle supplied objects are UPPERCASE.
  • User-defined variables and objects are lowercase.
  • All words are separated with an underscore (_) symbol

Audio Post

You can 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  /

Data types

Data TypePrefixExampleComment
BooleanBlb_employee_statusPrefix common variables
DateDld_hire_date 
NumberNgv_bonus_percentage 
Varchar2Vgv_department 
Cursorcur, cc_itemPrefix cursors and records like this
Recordrec, rr_employee 
Typetyp, ttyp_monthly_sale 
Associative arrayaaaa_sales_rankLexicon varies
Varrayvava_top_sales_itemLexicon varies
Nested tableNtnt_customer_addressLexicon varies
Exceptionex, eex_bonus_too_high 
Table typeTttt_customerLexicon 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

Related

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.