MOCKSTACKS
EN
Questions And Answers

More Tutorials









Oracle Assignments model and language

Assignments model in PL/SQL


All programming languages allow us to assign values to variables. Usually, a value is assigned to variable, standing
on left side. The prototype of the overall assignment operations in any contemporary programming language looks like this:

left_operand assignment_operand right_operand instructions_of_stop

This will assign right operand to the left operand. In PL/SQL this operation looks like this:

left_operand := right_operand;

Left operand must be always a variable. Right operand can be value, variable or function:

SET serveroutput ON
DECLARE
 v_hello1 VARCHAR2(32767);
 v_hello2 VARCHAR2(32767);
 v_hello3 VARCHAR2(32767);
 FUNCTION hello RETURN VARCHAR2 IS BEGIN RETURN 'Hello from a function!'; END;
BEGIN
 -- from a value (string literal)
 v_hello1 := 'Hello from a value!';
 -- from variable
 v_hello2 := v_hello1;
 -- from function
 v_hello3 := hello;
 DBMS_OUTPUT.put_line(v_hello1);
 DBMS_OUTPUT.put_line(v_hello2);
 DBMS_OUTPUT.put_line(v_hello3);
END;
/

When the code block is executed in SQL*Plus the following output is printed in console:

Hello from a value!
Hello from a value!
Hello from a function!

There is a feature in PL/SQL that allow us to assign "from right to the left". It's possible to do in SELECT INTO statement. Prototype of this instrunction you will find below:

SELECT [ literal | column_value ]
INTO local_variable
FROM [ table_name | aliastable_name ]
WHERE comparison_instructions;

This code will assign character literal to a local variable:

SET serveroutput ON
DECLARE
 v_hello VARCHAR2(32767);
BEGIN
 SELECT 'Hello world!'
 INTO v_hello
 FROM dual;
 DBMS_OUTPUT.put_line(v_hello);
END;
/

When the code block is executed in SQL*Plus the following output is printed in console:

Hello world!

Asignment "from right to the left" is not a standard, but it's valuable feature for programmers and users. Generally it's used when programmer is using cursors in PL/SQL - this technique is used, when we want to return a single scalar value or set of columns in the one line of cursor from SQL cursor.


Conclusion

In this page (written and validated by ) you learned about Oracle Assignments model and language . What's Next? If you are interested in completing Oracle tutorial, your next topic will be learning about: Oracle Triggers.



Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.


Share On:


Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.