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 Conversion1. 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 VariablesIn 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;