Candidate Key In Pl/Sql What Is The Assignment Operator

PL/SQL - Operators


Previous Page

Next Page  


In this chapter, we will discuss operators in PL/SQL. An operator is 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 following types of operators −

  • Arithmetic operators
  • Relational operators
  • Comparison operators
  • Logical operators
  • String operators

Here, we will understand the arithmetic, relational, comparison and logical operators one by one. The String operators will be discussed in a later chapter − PL/SQL - Strings.

Arithmetic Operators

Following table shows all the arithmetic operators supported by PL/SQL. Let us assume variable A holds 10 and variable B holds 5, then −

Show Examples

OperatorDescriptionExample
+Adds two operandsA + B will give 15
-Subtracts second operand from the firstA - B will give 5
*Multiplies both operandsA * B will give 50
/Divides numerator by de-numeratorA / B will give 2
**Exponentiation operator, raises one operand to the power of otherA ** 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. Let us assume variable A holds 10 and variable B holds 20, then −

Show Examples

OperatorDescriptionExample
=Checks if the values of two operands are equal or not, if yes then condition becomes true.(A = B) is not 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) is not 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) is not 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 another. The result is always either TRUE, FALSE or NULL.

Show Examples

OperatorDescriptionExample
LIKEThe LIKE operator compares a character, string, or CLOB value to a pattern and returns TRUE if the value matches the pattern and FALSE if it does not.If 'Zara Ali' like 'Z% A_i' returns a Boolean true, whereas, 'Nuha Ali' like 'Z% A_i' returns a Boolean false.
BETWEENThe BETWEEN operator tests whether a value 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.
INThe 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 NULLThe IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL or FALSE if it is 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. All these operators work on Boolean operands and produce Boolean results. Let us assume variable A holds true and variable B holds false, then −

Show Examples

OperatorDescriptionExamples
andCalled the logical AND operator. If both the operands are true then condition becomes true.(A and B) is false.
orCalled the logical OR Operator. If any of the two operands is true then condition becomes true.(A or B) is true.
notCalled the logical NOT Operator. Used 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 example, 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 and then adds into 7.

Here, operators with the highest precedence appear at the top of the table, those with the lowest appear at the bottom. Within an expression, higher precedence operators will be evaluated first.

The precedence of operators goes as follows: =, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN.

Show Examples

OperatorOperation
**exponentiation
+, -identity, negation
*, /multiplication, division
+, -, ||addition, subtraction, concatenation
comparison
NOTlogical negation
ANDconjunction
ORinclusion

Previous Page

Print

Next Page  


Would you please explain in details with examples 'assignment operators' like (:= ,:=:,=:).These altogether seem...

to be a little bit confusing in terms of where to use what and how to use them.

The assignment operator is simply the way PL/SQL sets the value of one variable to a given value. There is only one assignment operator, := . I'm not sure where you saw the others listed or used, but they are invalid.

Assignment operators are different from a regular equal sign, =, in that they are used to assign a specified value to a PL/SQL variable. For instance, if I wanted to give a variable named V_TEMPERATURE an initial value of 98.6, I'd use the following assignment statement:

set serveroutput on DECLARE v_temperature number := 98.6 ; BEGIN dbms_output.put_line('The temperature is ' || v_temperature) ; END ; / I could also change the value of v_temperature in the body of my code as well: set serveroutput on DECLARE v_temperature number := 98.6 ; BEGIN dbms_output.put_line('The initial temperature is ' || v_temperature) ; v_temperature := 100.6 ; dbms_output.put_line('The next temperature is ' || v_temperature) ; v_temperature := v_temperature - 4 ; dbms_output.put_line('The last temperature is ' || v_temperature) ; END ; / Assigning a value is different from just using the equal sign. Whenever you see an = in PL/SQL, it is typically used for comparison purposes: set serveroutput on DECLARE v_temperature number := 98.6 ; BEGIN IF v_temperature = 99 THEN dbms_output.put_line('You have a slight fever.') END IF ; END ; / Notice how the = is used to compare the value of v_temperature (which is 98.6) to the value 99.

That's the key way to differentiate. If you want to compare a value use an equal sign, but if you want to assign a value to a variable, use the := assignment operator.

For More Information

  • Dozens more answers to tough Oracle questions from Karen Morton are available.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Comments

Leave a Reply

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