How do I assign a value to a variable in an expression for a PL / SQL block?
When I run this script it returns an error in this statement no1:=(no1+no2)-(no2:=no1);
declare
no1 number(3):=31;
no2 number(3):=34;
begin
dbms_output.put_line('Before swap');
dbms_output.put_line('No1 : '||no1||' No2 : '||no2 );
-- no1:=(no1+no2)-(no2:=no1); generate error
dbms_output.put_line('After swap');
dbms_output.put_line('No1 : '||no1||' No2 : '||no2 );
end;
source to share
Apart from using the xor trick in PL / SQL, you can simply use the SQL statement
DECLARE
a number := 17;
b number := 42;
BEGIN
SELECT a, b
INTO b, a
FROM dual;
dbms_output.put_line( 'a = ' || a );
dbms_output.put_line( 'b = ' || b );
END;
which replaces two variables without using a temporary variable
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 a number := 17;
3 b number := 42;
4 BEGIN
5 SELECT a, b
6 INTO b, a
7 FROM dual;
8 dbms_output.put_line( 'a = ' || a );
9 dbms_output.put_line( 'b = ' || b );
10* END;
SQL> /
a = 42
b = 17
PL/SQL procedure successfully completed.
source to share
Actually, you can also replace two numbers without a tempo number using the Swap XOR Algorithm (but you still have 3 commands):
declare
no1 number(3):=31;
no2 number(3):=34;
begin
dbms_output.put_line('Before swap');
dbms_output.put_line('No1 : '||no1||' No2 : '||no2 );
n1 := (n1 + n2) - bitand(n1,n2) * 2;
n2 := (n2 + n1) - bitand(n2,n1) * 2;
n1 := (n1 + n2) - bitand(n1,n2) * 2;
dbms_output.put_line('After swap');
dbms_output.put_line('No1 : '||no1||' No2 : '||no2 );
end;
As for how bitwise xor in plsql see here
IMHO one should avoid one-liners in real programs, they are interested in writing, but hell to maintain them ...
source to share
You cannot perform multiple assignments in the same expression so that they continue to generate errors. Instead, I suggest you define a temporary variable and use it for your swap operation, for example:
declare
no1 number(3):=31;
no2 number(3):=34;
temp number;
begin
dbms_output.put_line('Before swap');
dbms_output.put_line('No1 : '||no1||' No2 : '||no2 );
-- no1:=(no1+no2)-(no2:=no1); generate error
temp := no1;
no1 := no2;
no2 : temp;
dbms_output.put_line('After swap');
dbms_output.put_line('No1 : '||no1||' No2 : '||no2 );
end;
source to share