1.set verify on
打开绑定变量显示
SQL> select first_name,job_id,&&colnum1 from employees where &&colnum1='&num'order by &&colnum1;
old 1: select first_name,job_id,&&colnum1 from employees where &&colnum1='&num'order by &&colnum1
new 1: select first_name,job_id,DEPARTMENT_ID from employees where DEPARTMENT_ID='100'order by DEPARTMENT_ID
2.DEFINE employee_num = 200
使用DEFINE命令给变量创建和指派数值
使用UNDEFINE 命令删除变量
3.& 与&&的区别符号替代变量
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num ;
SELECT employee_id, last_name, job_id, &&column_name
FROM employees
ORDER BY &column_name ;
&每个变量都要输入,而&&只要输入一次就会记录,后面重复相同的变量自动赋值。
4.LOWER变小写函数
SQL> select LOWER('SQL Course') from dual;
LOWER('SQL
----------
sql course
5.UPPER变大写函数
SQL> select UPPER('SQL Course') from dual;
UPPER('SQL
----------
SQL COURSE
6.首字母大写函数
SQL> select INITCAP('SQL Course') from dual;
INITCAP('S
----------
Sql Course
7.CONCAT连接字符串
SQL> select CONCAT('Hello', 'World') from dual;
CONCAT('HE
----------
HelloWorld
8.substr截取字符串
SQL> select SUBSTR('HelloWorld',1,5) from dual;
SUBST
-----
Hello
9.字符串长度length
SQL> select LENGTH('HelloWorld') from dual;
LENGTH('HELLOWORLD')
--------------------
10
10.查看字符或字符串在什么位置instr
SQL> select INSTR('HelloWorld', 'Wo') from dual;
INSTR('HELLOWORLD','W')
-----------------------
6
11.左边填充LPAD
SQL> select LPAD(salary,10,'*') from employees;
12.右边填充RPAD
SQL> select RPAD(salary,10,'*') from employees;
13.REPLACE替换字符串
SQL> select REPLACE ('JACK and JUE','J','BL') from dual;
REPLACE('JACKA
--------------
BLACK and BLUE
14.去除字符TRIM
select TRIM('H' FROM 'HelloWorld') from dual;
15.ROUND: 对一个特定的十进制数进行四舍五入
SQL> select ROUND(45.926, 2) from dual;
ROUND(45.926,2)
---------------
45.93
16.TRUNC:对一个特定的十进制数进行截断
SQL> select trunc(45.926, 2) from dual;
TRUNC(45.926,2)
---------------
45.92
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923),
2 TRUNC(45.923,-1)
3 FROM DUAL;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)
--------------- ------------- ----------------
45.92 45 40
17. MOD:除法的返回余数
SQL> select MOD(1600, 300) from dual;
MOD(1600,300)
-------------
100
18.round的用法
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),
2 ROUND(45.923,-1)
3 FROM DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
45.92 46 50
19.MONTHS_BETWEEN 月份差函数
SQL> select MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') from dual;
MONTHS_BETWEEN('01-SEP-95','11-JAN-94')
---------------------------------------
19.6774194
20.ADD_MONTHS N个月之后
SQL> select ADD_MONTHS ('31-JAN-96',1) from dual;
ADD_MONTHS('
------------
29-FEB-96
21.NEXT_DAY(下个星期五是哪天)
SQL> select NEXT_DAY('01-SEP-95','FRIDAY') from dual;
21.本月的最后一天LAST_DAY
select LAST_DAY(sysdate) from dual;
22.ROUND和TRUNC 对于日期的应用
QL> select ROUND(SYSDATE,'MONTH') from dual
2 ;
ROUND(SYSDAT
------------
01-MAR-11
SQL> select ROUND(SYSDATE,'DAY') from dual
2 ;
ROUND(SYSDAT
------------
27-FEB-11
SQL> select ROUND(SYSDATE,'YEAR') from dual
2 ;
ROUND(SYSDAT
------------
01-JAN-11
SQL> select TRUNC(SYSDATE,'YEAR') from dual
2 ;
TRUNC(SYSDAT
------------
01-JAN-11
SQL> select TRUNC(SYSDATE,'MONTH') from dual
2 ;
TRUNC(SYSDAT
------------
01-FEB-11
SQL> select TRUNC(SYSDATE,'DAY') from dual
2 ;
TRUNC(SYSDAT
------------
20-FEB-11
SQL> select sysdate from dual
2 ;
SYSDATE
------------
25-FEB-11
这里我们注意一下红色的实现,按照我们的想法应该返回是2月26日才对,但是却是27日,看一下SQL Reference就会知道是怎么回事了,可以看看