Assignment operators explained
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.
