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 ;
/

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 ;
/

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 ;
/

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.