How to use an aliase name in the SELECT clause in Oracle
However, instead of ename and job the column headings should be empname and jobrole. The query that will solve our purpose will be:
SELECT ENAME EMPNAME, JOB JOBROLE FROM EMP;
The fun comes, if you want a space in between. Like if you want JOBROLE should be displayed as JOB ROLE, then the query will become like
SELECT ENAME Empname, JOB “Job Role” FROM EMP;
Notice the usage of quodes.
NULL in Oracle means empty. You need really a special mechanism to handle NULL values in Oracle
Oracle supoorts the NVL function to handle NULL values.
NULL means empty, so it is neither zero nor spaces. NULL value takes place, when the user does not enter any data in the respective columns.
So, if you encounter a null value, ensure that you have taken enough precautions well in advance, failing which you can land up with invisible result as any operation with the NULL value results NULL only.
Like for numbers, you can handle NVL(comm,0) – which means, if the comm column is NULL, then convert it to zero for computation purpose
Ensure that the column attribute is not set to NOT NULL. A Primary Key, by default can NEVER accept any NULL Value.
This example will demonstrate the performance of different blocks. The golden rule is followed – first inner, then outer
<
DECLARE
X NUMBER;
BEGIN
X:=1;
WHILE X<5
LOOP
DBMS_OUTPUT.PUT_LINE (X);
<
DECLARE
X NUMBER;
BEGIN
X:=1;
WHILE X<3
LOOP
DBMS_OUTPUT.PUT_LINE(X||’ ‘||OUTER.X);
X:=X+1;
END LOOP;
END;
X:=X+1;
END LOOP;
END;
The example given below shows a While Loop. Here the value of the variable I is checked to ascertain whether it is less than 10 and as long as it is so the body of the loop will be executed.
DECLARE
I NUMBER (4) :=0;
BEGIN
WHILE (I<10)
LOOP
I:= I+1;
DBMS_OUTPUT.PUT_LINE (I);
END LOOP;
END;