Oracle learning path (5) ----- General oracle data type operations

Oracle learning path (5) ----- General oracle data type operations
1. Some common Operators

Operator	Meaning
=	Equal
<> ,! = ,~ =, ^ =	Not equal
<	Less
>	Greater
<=	Less than or equal
> =	Greater than or equal
+	Plus sign
-	Minus sign
*	Multiplication number
/	Division Number
: =	Value assignment number
=>	Link No.
..	Range operation
|	Character Connector
IS NULL	Is null
BETWEEN AND	Between the two
IN	In the middle of a column Value
AND	Logic and
OR	Logic or
NOT	Return, such as is not null, NOT IN

2. Variable assignment

　　In PL/SQL programming, variable assignment is worth noting. Its syntax is as follows:
　　
* Variable assignment

　variable  := expression ;

* Assign values to variables simultaneously

　variable variable_Type:= expression ;

3. variable calculation NULL value plus number still NULL value: NULL + <number> = NULL value plus (connection) character, the result is a character: NULL | <string >=< string> boolean values are only TRUE, FALSE, and NULL. Database assignments are assigned using the SELECT statement. Values are assigned each time a SELECT statement is executed, generally, the variables to be assigned must correspond one to one with the columns in the SELECT statement. For example:

 DECLARE  emp_id    emp.empno%TYPE :=7788;  emp_name  emp.ename%TYPE;  wages     emp.sal%TYPE;BEGIN  SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages  FROM emp WHERE empno = emp_id;  DBMS_OUTPUT.PUT_LINE(emp_name||'----'||to_char(wages));END;

　　Tip: columns in SELECT statements cannot be assigned to boolean variables.

4. Data Type Conversion

1. Convert CHAR to NUMBER:TO_NUMBER ('1970. 0 ')

2. Convert NUMBER to CHAR:TO_CHAR ('2017. 45 ')

3. character conversion to date:V_date: = TO_DATE ('192. 123', 'yyyy. mm. dd ');
4. convert a date to a characterV_to_day: = TO_CHAR (SYSDATE, 'yyyy. mm. dd hh24: mi: ss ');

 -- Created on 2015/6/12 by LYH declare   str char(10):='100.0';  i int:=421;  total int;begin  total:=TO_NUMBER(str)+i;  Dbms_Output.put_line(TO_CHAR(total));end;

5. Scope and visibility of Variables

In PL/SQL programming, if the definition of variables is not uniform, some dangerous errors may be hidden, mainly because of the scope of the variables. The scope of variables refers to the effective scope of variables. Similar to other advanced languages, PL/SQL variables have the following features:

Variables are used in the program units you reference (blocks, subprograms, packages. That is, from the beginning of the declared variable to the end of the block. A variable (identifier) can only be visible within the block you reference. When a variable is out of scope, the PL/SQL engine releases the space used to store the variable (because it may not be used ).
After the variable is redefined in the sub-block, it is only used in the block.

Example

-- Created on 2015/6/15 by LYH declare Emess char (4): = 'lily'; begin declare v1 number (10); begin SELECT fage INTO v1 from t_employee where fname = 'Tom '; dbms_Output.put_line (v1); end; declare v1 number (10); begin SELECT fage INTO v1 from t_employee where fname = 'Jerry '; Dbms_Output.put_line (v1); end;