PLSQL top syntax rules read before you start

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

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

Author: Srini

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