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

Oracle learning path (5) ----- General oracle data type operations, oracle ----- oracle
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 is still NULL: NULL + <number> = NULL
• NULL value plus (connected) character, result: NULL ||< string >=< string>
• Boolean values include TRUE, FALSE, and NULL.
• Database assignment is performed using the SELECT statement. Each execution of the SELECT statement assigns a value. Generally, the variables to be assigned must correspond to the column names 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('100.0')

2. Convert NUMBER to CHAR: TO_CHAR('123.45')

3. character conversion to date: v_date := TO_DATE('2001.07.03','yyyy.mm.dd');
4. convert a date to a character v_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:

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;