Constants and Literals and Operators in PL-SQL

Last updated on May 28 2022
Nitin Bajabalkar

Table of Contents

Constants and Literals and Operators in PL-SQL

In this blog, we’ll discuss constants and literals in PL/SQL. a continuing holds a worth that when declared, doesn’t change within the program. a continuing declaration specifies its name, data type, and value, and allocates storage for it. The declaration also can impose the NOT NULL constraint.
Declaring a continuing
A constant is said using the CONSTANT keyword. It requires an initial value and doesn’t allow that value to be changed. for instance −

PI CONSTANT NUMBER := 3.141592654; 
DECLARE 
-- constant declaration 
pi constant number := 3.141592654; 
-- other declarations 
radius number(5,2); 
dia number(5,2); 
circumference number(7, 2); 
area number (10, 2); 
BEGIN 
-- processing 
radius := 9.5; 
dia := radius * 2; 
circumference := 2.0 * pi * radius; 
area := pi * radius * radius; 
-- output 
dbms_output.put_line('Radius: ' || radius); 
dbms_output.put_line('Diameter: ' || dia); 
dbms_output.put_line('Circumference: ' || circumference); 
dbms_output.put_line('Area: ' || area); 
END; 
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
Radius: 9.5
Diameter: 19
Circumference: 59.69
Area: 283.53

Pl/SQL procedure successfully completed.
The PL/SQL Literals
A literal is a particular numeric, character, string, or Boolean value not represented by an identifier. for instance , TRUE, 786, NULL, ‘tecklearn’ are all literals of type Boolean, number, or string. PL/SQL, literals are case-sensitive. PL/SQL supports the subsequent sorts of literals −
• Numeric Literals
• Character Literals
• String Literals
• BOOLEAN Literals
• Date and Time Literals
The following table provides examples from of these categories of literal values.

S.No Literal Type & Example
1 Numeric Literals

050 78 -14 0 +32767

6.6667 0.0 -12.0 3.14159 +7800.00

6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3

2 Character Literals

‘A’ ‘%’ ‘9’ ‘ ‘ ‘z’ ‘(‘

3 String Literals

‘Hello, world!’

‘Tecklearn’

’19-NOV-12′

4 BOOLEAN Literals

TRUE, FALSE, and NULL.

5 Date and Time Literals

DATE ‘1978-12-25’;

TIMESTAMP ‘2012-10-29 12:01:01’;

To embed single quotes within a string literal, place two single quotes next to every other as shown within the following program −

DECLARE 
message varchar2(30):= 'That''s tecklearn.com!'; 
BEGIN 
dbms_output.put_line(message); 
END; 
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
That’s tecklearn.com!

PL/SQL procedure successfully completed.

PL/SQL – Operators

In this blog, we’ll discuss operators in PL/SQL. An operator may be a symbol that tells the compiler to perform specific mathematical or logical manipulation. PL/SQL language is rich in built-in operators and provides the subsequent sorts of operators −
• Arithmetic operators
• Relational operators
• Comparison operators
• Logical operators
• String operators
Here, we’ll understand the arithmetic, relational, comparison and logical operators one by one. The String operators are going to be discussed during a later chapter − PL/SQL – Strings.
Arithmetic Operators
Following table shows all the arithmetic operators supported by PL/SQL. allow us to assume variable A holds 10 and variable B holds 5, then

Operator Description Example
+ Adds two operands A + B will give 15
Subtracts second operand from the first A – B will give 5
* Multiplies both operands A * B will give 50
/ Divides numerator by de-numerator A / B will give 2
** Exponentiation operator, raises one operand to the facility of other A ** B will give 100000

Relational Operators
Relational operators compare two expressions or values and return a Boolean result. Following table shows all the relational operators supported by PL/SQL. allow us to assume variable A holds 10 and variable B holds 20, then –

Operator Description Example
= Checks if the values of two operands are equal or not, if yes then condition becomes true. (A = B) isn’t true.
!=

<>

~=

Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. (A != B) is true.
> Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. (A > B) isn’t true.
< Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. (A < B) is true.
>= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. (A >= B) isn’t true.
<= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. (A <= B) is true

Comparison Operators

Comparison operators are used for comparing one expression to a different . The result’s always either TRUE, FALSE or NULL.

Operator Description Example
LIKE  

The LIKE operator compares a personality , string, or CLOB value to a pattern and returns TRUE if the worth matches the pattern and FALSE if it doesn’t

If ‘Zara Ali’ like ‘Z% A_i’ returns a Boolean true, whereas, ‘Nuha Ali’ like ‘Z% A_i’ returns a Boolean false.
BETWEEN The BETWEEN operator tests whether a worth lies in a specified range. x BETWEEN a AND b means that x >= a and x <= b. If x = 10 then, x between 5 and 20 returns true, x between 5 and 10 returns true, but x between 11 and 20 returns false.
IN The IN operator tests set membership. x IN (set) means that x is equal to any member of set. If x = ‘m’ then, x in (‘a’, ‘b’, ‘c’) returns Boolean false but x in (‘m’, ‘n’, ‘o’) returns Boolean true.
IS NULL The IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL or FALSE if it’s not NULL. Comparisons involving NULL values always yield NULL. If x = ‘m’, then ‘x is null’ returns Boolean false.

Logical Operators
Following table shows the Logical operators supported by PL/SQL. of these operators work on Boolean operands and produce Boolean results. allow us to assume variable A holds true and variable B holds false, then –

Operator Description Examples
and Called the logical AND operator. If both the operands are true then condition becomes true. (A and B) is fake.
or Called the logical OR Operator. If any of the two operands is true then condition becomes true. (A or B) is true.
not Called the logical NOT Operator. wont to reverse the logical state of its operand. If a condition is true then Logical NOT operator will make it false. not (A and B) is true.

PL/SQL Operator Precedence
Operator precedence determines the grouping of terms in an expression. This affects how an expression is evaluated. Certain operators have higher precedence than others; for instance, the multiplication operator has higher precedence than the addition operator.
For example, x = 7 + 3 * 2; here, x is assigned 13, not 20 because operator * has higher precedence than +, so it first gets multiplied with 3*2 then adds into 7.
Here, operators with the very best precedence appear at the highest of the table, those with rock bottom appear at rock bottom. Within an expression, higher precedence operators are going to be evaluated first.
The precedence of operators goes as follows: =, , =, , !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN.

Operator Operation
** exponentiation
+, – identity, negation
*, / multiplication, division
+, -, || addition, subtraction, concatenation
comparison
NOT logical negation
AND conjunction
OR inclusion

So, this brings us to the end of blog. This Tecklearn ‘Constants and Literals and operators in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

 

0 responses on "Constants and Literals and Operators in PL-SQL"

Leave a Message

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