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 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

Related