Oracle Learning Path (v)-----General Operations for Oracle data types

1. Some common operators

operator	meaning
=	Equals
<>,! =, ~=, ^=	Not equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
+	Plus
-	Minus sign
*	Multiplication sign
/	Division Sign
:=	Assignment number
=	Relationship Number
..	Range arithmetic
||	Character connector
Is NULL	is a null value
Between and	Somewhere between the two
Inch	In the middle of a column of values
and	Logic and
OR	Logical OR
Not	Return, if is isn't NULL, not in

2. Assigning values to variables

　　in PL/SQL Programming, variable assignment is a notable place, and its syntax is as follows:
　　
* Variable Assignment value

　variable  := expression ;

* Declarations are also assigned values for variables

　variable  variable_Type:= expression ;

3. Variable calculation

• Null value plus number is still null: null + < number > = NULL
• Null plus (connection) character, the result is character: NULL | | < string > = < string >
• Boolean values are only true, false, and NULL three values
• The database assignment is done by the SELECT statement, which is assigned once per execution of the SELECT statement, and generally requires that the assigned variable be mapped to the column name in select. Such as:

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

　　Tip: You cannot assign a column in a SELECT statement to a Boolean variable.

4. Conversion of data types

1. CHAR is converted to number:TO_NUMBER(‘100.0‘)

convert 2.NUMBER to char:TO_CHAR(‘123.45‘)

3. Character conversion to date: v_date := TO_DATE(‘2001.07.03‘,‘yyyy.mm.dd‘);
4. Convert dates to charactersv_to_day := TO_CHAR(SYSDATE, ‘yyyy.mm.dd hh24:mi:ss‘) ;

2015/6/12by LYH declare   strchar(10):=‘100.0‘;  int:=421;  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 there is no uniformity in the definition of a variable, some dangerous errors may be hidden, mainly due to the scope of the variable. The scope of a variable refers to the effective scope of the variable, similar to other high-level languages, and PL/SQL's variable scope features are:

1. The scope of a variable is within the program unit (block, subroutine, package) you are referencing. That is, the declaration variable starts at the end of the block.
2. A variable (identity) can only be visible within the block you are referencing.
3. When a variable is out of scope, the PL/SQL engine frees the space used to hold the variable (as it may not be needed).
   
   1. After redefining the variable in a child block, it functions only within that block.

An example

--Created on 2015/6/15 by LyhDeclare emess char (4): = ' John Doe '; begindeclare v1 number (ten);     beginSELECT fage  to v1  from T_employee where fname=' Tom ';     Dbms_output.put_line (v1); end;DECLARE v1 number (10); beginSELECT fage  to v1  from T_employee where Fnam e=' Jerry ';     Dbms_output.put_line (v1); end; end;

Oracle Learning Path (v)-----General Operations for Oracle data types