Assign Variables thru INTO keyword and Concat "||" Operator

for better understanding please go to the YouTube channel.

https://www.youtube.com/oracleshooter

--====================================================

How to Assign value by using INTO keyword

and how to use concat "||" operator

--====================================================

--========

Example:-1 -- ( Simple Assignment )

--========

DECLARE

    V_NAME VARCHAR(30) := 'I am from DECLARE Section';

BEGIN

    dbms_output.put_line ('Stage-1 :- '||V_NAME);

       

     V_NAME := 'I am from BEGIN Section';

   

    dbms_output.put_line ('Stage-2 :- '||V_NAME);

    

    V_NAME := ' FOOOOOOOOOOOOOOOOOOOOOOOOOOR Exception ';

EXCEPTION when others THEN

V_NAME := 'I am from EXCEPTION Section';

    dbms_output.put_line ('Stage-3 :- '||V_NAME);

   

END;

--========

Example:-2 -- ( How to Assign value by using INTO keword )

NOTE:- we can no do this in declare section

--========

select * from XXEMP_TL

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
7369	Smith	CLERK	7902	17-Dec-80	800		20	1234543256	M
7499	Allen	SALESMAN	7698	20-Feb-81	1600	300	30	2123212541	M
7521	Ward	SALESMAN	7698	22-Feb-81	1250	500	30	9898989898	M
7566	Jones	MANAGER	7839	02-Apr-81	2975		20	8989898989	M
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30	7878787878	M
7698	BLAKE	MANAGER	7839	01-May-81	2850		30	6767676767	M
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10	5656565656	M
7788	Scott	ANALYST	7566	09-Dec-82	3000		20	4545454545	M
7839	KING	PRESIDENT		17-Nov-81	5000		10	3434343434	M
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30	2323232323	M
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20	1212121212	M
7900	James	CLERK	7698	03-Dec-81	950		30	9090909090	M
7902	FORD	ANALYST	7566	03-Dec-81	3000		20	101010101	M
7934	MILLER	CLERK	7782	23-Jan-82	1300		10	202020202	M
7999	Rani	SALESMAN	7698	23-Sep-88	1900	10	10	303030303	F

--========

-- use INTO keyword for Single Assignment 

--========

DECLARE

    V_NAME VARCHAR(10) ;

BEGIN

   

    SELECT ENAME INTO V_NAME FROM XXEMP_TL WHERE EMPNO =  :V_EMPNO;

    

    dbms_output.put_line ('Employee Name : -  '||V_NAME);

EXCEPTION when others THEN

    dbms_output.put_line (SQLCODE||' - ERROR MSG :- '||SQLERRM);

END;

--========

-- use INTO keyword for Multi Assignment 

--========

DECLARE

    V_NAME VARCHAR2(100);

    V_SAL NUMBER;

BEGIN

    SELECT ENAME, SAL INTO V_NAME, V_SAL  FROM XXEMP_TL WHERE EMPNO =  :V_EMPNO;

   

    dbms_output.put_line ('Employee Name : -  '||V_NAME || '           and Salary is :- '|| V_SAL);

EXCEPTION when others THEN

    dbms_output.put_line (SQLCODE||' - ERROR MSG :- '||SQLERRM);

END;

OUTPUT:-

Employee Name : -  Jones

Employee Name : -  Rani

Employee Name : -  Rani           and Salary is :- 1900