首页 > 代码库 > 函数练习小结

函数练习小结

NULL值与任何值进行四则运算后,其结果均为NULL
如果列别名包括空格或特殊字符,或者它是区分大小写的,那么需要双引号


把列与列,列与字符连接起来用||表示,用来合成列


输出显示关键字或者操作符的内容q‘[]‘表示
 [ ] , ! ! , : : , { } , ( ) ,  < > , . . 等都是可以,但切记,要成对出现


 distinct去重的结果是不排序的

 字符和日期要包含在单引号中
 字符大小写敏感,日期格式敏感
 默认的日期格式是DD-MON-RR

 sql语句中的日期类型要与当前会话中的nls_date_format的格式相同



 使用escape标识符
 select employee_id,last_name,job_id from employees where job_id like ‘%SA\_%‘ escape ‘\‘;
如果字符串中有两个连续的"_",那么是否需要两次转译?
SQL1:select * from t_like where y like ‘a\__d%‘ escape ‘\‘;
SQL2:select * from t_like where y like ‘a\_\_d%‘ escape ‘\‘;
经实验转译一次两次都可以


检索null时,无法用=或<>来进行判断,只允许用is null和is not null来进行判断。

运算优先级
1.算数运算符
2.连接符
3.比较符
4.IS [NOT] NULL, LIKE, [NOT] IN
5.[NOT] BETWEEN
6.不等于
7.NOT
8.AND
9.OR

排序结果中有null值,升序排序后,null值在最下面(反之在最上面),因为null值是未知的,也就是无穷大

如果需求是,非空值升序,空值显示在结果的最上面
select distinct department_id from employees order by 1 asc nulls first;
如果需求是,非空值降序,空值在显示结果的最下面
select distinct department_id from employees order by desc nulls last;



使用&和&&临时存储值,替代变量
使用替代变量,可以替代一下内容
where条件
order by子句
列表达式
表名
整个select语句
SQL> select employee_id,last_name,salary,department_id
  2  from employees
  3  where employee_id=&v_num;
Enter value for v_num: 101
old   3: where employee_id=&v_num
new   3: where employee_id=101

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        101 Kochhar                        17000            90
SQL> select last_name,department_id,salary*12
  2  from employees
  3  where job_id=‘&job_title‘;
Enter value for job_title: HR_REP
old   3: where job_id=‘&job_title‘
new   3: where job_id=‘HR_REP‘

LAST_NAME                 DEPARTMENT_ID  SALARY*12
------------------------- ------------- ----------
Mavris                               40      78000

SQL> select last_name,department_id,salary from employees where hire_date=to_date(‘&hd‘,‘yyyy-mm-dd‘);
Enter value for hd: 2008/1/13
old   1: select last_name,department_id,salary from employees where hire_date=to_date(‘&hd‘,‘yyyy-mm-dd‘)
new   1: select last_name,department_id,salary from employees where hire_date=to_date(‘2008/1/13‘,‘yyyy-mm-dd‘)

LAST_NAME                 DEPARTMENT_ID     SALARY
------------------------- ------------- ----------
Grant                                50       2600
hire_date=to_date(‘2008-01-13‘,‘yyyy-mm-dd‘)是在告诉数据库我输入的格式是怎么样的
hire_date=‘2008-01-13‘是直接输入,但是这种情况输入的格式必须跟nls_date_format相同

SQL> select employee_id,last_name,job_id,&column_name from employees
  2  where &condition
  3  order by &order_column;
Enter value for column_name: email
old   1: select employee_id,last_name,job_id,&column_name from employees
new   1: select employee_id,last_name,job_id,email from employees
Enter value for condition: manager_id=101
old   2: where &condition
new   2: where manager_id=101
Enter value for order_column: 1
old   3: order by &order_column
new   3: order by 1

EMPLOYEE_ID LAST_NAME                 JOB_ID     EMAIL
----------- ------------------------- ---------- -------------------------
        108 Greenberg                 FI_MGR     NGREENBE
        200 Whalen                    AD_ASST    JWHALEN
        203 Mavris                    HR_REP     SMAVRIS
        204 Baer                      PR_REP     HBAER
        205 Higgins                   AC_MGR     SHIGGINS

&和&&都表示需要输入参数,区别是&表示一次性参数 &&表示可多次引用


使用define命令创建和分配一个变量的值,使用undefine命令来删除一个变量
define employee_num=200
SQL> define employee_num=200
SQL> select employee_id,last_name,salary,department_id from employees
  2  where employee_id=&employee_num;
old   2: where employee_id=&employee_num
new   2: where employee_id=200

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        200 Whalen                          4400            10

SQL> undefine employee_num

使用 VERIFY 命令来显示替换变量之前和之后的值,默认值为ON,即显示替换变量之前之后的值

set verify off
show verify


SQL> select ascii(‘A‘) from dual;

ASCII(‘A‘)
----------
        65

SQL> select ascii(‘‘‘‘) from dual;

ASCII(‘‘‘‘)
-----------
         39
求单引号的ASCII码写法很特殊 两个单引带表一个单引

ascii只针对一个字符进行转换


SQL> select chr(39) from dual;

C
-

空格:chr(32)
回车:chr(13)
换行:chr(10)


instr在一个字符串中搜索指定的字符,返回发现指定的字符的位置
instr(c1,c2,I,J)
c1:被搜索的字符串
c2:希望搜索的字符串
I:搜索开始的位置,默认为1
J:出现的位置,默认为1

vsize(expr)  vsize返回expr的内部表示的字节数,如果expr是空的,那么这个函数返回null,此功能不支持直接clob数据


soundex(char)返回一个与给定的字符串读音几乎相同的字符串

select trim(‘a‘ from ‘aaaaaaabbbbbbbbbbbaaaaaa‘) from dual;

abs(n)返回指定值n的绝对值



to_multi_byte(char)半角转全角
to_single_byte(char)全角转半角


语法:ceil(n)
取大于等于数值n的最小整数
SCOTT@ORA11GR2> select ceil(9.9),ceil(9),ceil(9.1) from dual;

 CEIL(9.9)     CEIL(9)  CEIL(9.1)
---------- ---------- ----------
         10            9           10

语法:floor(n)
取小于等于数值n的最大整数
SCOTT@ORA11GR2> select floor(9.9),floor(9),floor(9.1) from dual;

FLOOR(9.9)    FLOOR(9) FLOOR(9.1)
---------- ---------- ----------
          9            9             9

 show parameter nls_date_format
 alter system set nls_date_format=‘yyyy-mm-dd hh24:mi:ss‘ scope=spfile
 nls_date_format 为静态参数,修改时必须加上scope=spfile子句,重启数据库参数生效

 带有时区及毫秒的日期格式
 select systimestamp from dual;

 返回数据库的时区
 select dbtimezone from dual;

scott用户如果不存在,利用脚本创建
 select username from dba_users where username=‘SCOTT‘;
 @?/rdbms/admin/utlsampl.sql


show linesize
show pagesize

清空sqlplus缓存区的内容
SQL>cl buff清空缓存区的内容

登录时读取的文件
 vi /u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/glogin.sql

power(x,y)返回x的y次幂。
SQL> select power(2,3) from dual;

POWER(2,3)
----------
         8
缺陷:y为小数会报错


sign(n)符号函数
当n>0时,sign(n)=1
当n=0时,sign(n)=0
当n<0时,sign(n)=-1



返回数据库的时区
select dbtimezone from dual;

 alter database set time_zone=‘+08:00‘;
时区分东西,东时区都带+号标识,西时区用-号标识。
在一个特定的时间点,时区号越大(考虑正负号)的地方,时间越晚。


返回当前会话的时区
select sessiontimezone from dual;

SQL> select dbtimezone,sessiontimezone,current_date from dual;

DBTIME SESSIONTIMEZONE                                                             CURRENT_DATE
------ --------------------------------------------------------------------------- -------------------
+00:00 +08:00                                                                      2016-11-21 14:53:58
发现current_date时间为会话所在时区的时间




将当前会话改为西六区
 alter session set time_zone=‘-6:00‘;

dbtimezone:是数据库服务器所在的时区
sessiontimezone:是你的会话的时区
比如数据库服务器是放在英国(+00:00时区),而你在中国(+08:00)访问数据库,则sessiontimezone就是+08:00,dbtimezone就是+00:00

返回为当前会话所在时区的timestamp with time zone时间
SYS@ORA11GR2> select dbtimezone,sessiontimezone,current_timestamp from dual;

DBTIME SESSIONTIMEZONE CURRENT_TIMESTAMP
------ --------------- ----------------------------------------
+08:00 -06:00            06-JUN-12 04.14.37.777012 AM -06:00



QL> select current_timestamp(3) from dual;

CURRENT_TIMESTAMP(3)
---------------------------------------------------------------------------
21-NOV-16 01.04.14.905 AM -06:00

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
21-NOV-16 01.09.44.191830 AM -06:00



巧妙显示为标准日期型
SQL> select current_timestamp,current_timestamp+0 from dual;

CURRENT_TIMESTAMP                                                           CURRENT_TIMESTAMP+0
--------------------------------------------------------------------------- -------------------
21-NOV-16 01.10.58.179676 AM -06:00                                         2016-11-21 01:10:58



scc或cc显示世纪

SQL> select to_char(sysdate,‘scc yyyy-mm-dd‘) from dual;

TO_CHAR(SYSDAT
--------------
 21 2016-11-21


 q表示当前时间为一年的第几季度
 
SQL> select to_char(sysdate,‘q‘) from dual;

T
-
4

ww:一年中的第几周,w一个月中的第几周

SQL> select to_char(sysdate,‘ww‘),to_char(sysdate,‘w‘) from dual;

TO T
-- -
47 3



ddd:一年中的第几天,dd一个月中的第几天  d一周中的第几天
SQL> select to_char(sysdate,‘ddd‘),to_char(sysdate,‘dd‘),to_char(sysdate,‘d‘) from dual;

TO_ TO T
--- -- -
326 21 2


函数练习小结