PL/SQL fundamentals –Before you start coding

You need to learn certain basic things about PL/SQL before you begin creating code blocks in PL/SQL. Here are some PL/SQL fundamentals for you to remember.

PL/SQL is NOT Case-Sensitive

You can code in any case (uppercase or lowercase) you want but it is always preferred to write keywords in uppercase for better readability. But, when you write strings and characters enclosed in single quotes, case matters.

It means ‘Monday’, ‘MONDAY’, and ‘Monday’ are different strings. If you compare them, they will not be equal!!  So, while using strings, be careful.

When using variable names and keywords, case doesn’t matter. Variable names NUM, Num and num all are same. Keywords SELECT can be used as select, Select or any way you want. Same is the case with all other keywords, table names, view names and other names you use in your codes.

Print using DBMS_OUTPUT.PUT_LINE

When you need to print some value in your PL/SQL code you can use DBMS_OUTPUT.PUT_LINE function. Whatever is written in the parenthesis () is printed on the screen. The value can be numeric, string, character, date or any combination of these data types.

DBMS_OUTPUT.PUT_LINE is inbuilt function that allows you to concatenate strings. You can also combine strings and other data types to display as output of a PL/SQL block.

Examples

DBMS_OUTPUT.PUT_LINE(‘Hello! World’);
DBMS_OUTPUT.PUT_LINE(‘The sum of two numbers id’+result);
DBMS_OUTPUT.PUT_LINE(dob+ ’ is your Birth Date  and you are ’+age+’ years old’);

Read Value during Execution

You call the PL/SQL blocks from your application programs that will pass on the values to functions or procedures. You can read values from users in PL/SQL while testing the code blocks.  It is done by using & with a variable name immediately after it is declared.

Example

Num1 NUMBER:=& Num1;

Here Num1 is the name of the variable and NUMBER is an Oracle data-type for numeric value. To initialize it with a value entered by user use ‘:=’ assignment operators followed by & and name of the variable.

Add Comments in PL/SQL blocks

Comments are important in coding to explain logic to others or recall after a while. Adding comments in your code is a good programming practice. It also improves readability of code and helps others in understanding while maintenance of software programs.  When PL/SQL compiler encounters comments in PL/SQL block it ignores them.

PL/SQL provided two methods of adding comments – Single line comments and Multi-line comments.

Single-line comment

It is added in the code by beginning the sentence with double hyphens. This comment works only in the code line it is given. As the line ends the comment also ens

Example

--this is a single line code in PL/SQL

Multiline comment

You can enclose a paragraph describing the code between /* and */. Compiler starts to ignore the line beginning with /* till it gets the matching */. After this the rest of the statements are complied. Due to this matching set of slash-asterix , nesting of comments is not possible.

Example

/* This is a paragraph
added as a comment in
a PL/SQL Block spanned 
across multiple lines*/

Delimiters

In PL/SQL coding, delimiter is any symbol or combination of symbols that carry a special purpose.  Common delimiters are +,*,/,-   for arithmetic operations. You can learn about delimiters here . A few other PL/SQL delimiters are mentioned in the image.

Operators

PL/SQL Operators

Operator Precedence

Operator Precedence

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *