oracle基本语句
查看当前用户的所有表
select * from tab;
连接操作符 ||
查询批量删除表的语句
select 'drop table '||tname||';' from tab;
转义关键字escape
select * from emp where ename like '_/_%' escape '/';
sqlplus
设置行宽
set line(size) 100;
设置每页行数
set pagesize 100;
设置列宽
col name format a10;
col id format 9999;
查询表的字段数
select table_name,Count(*) As 列数 from user_tab_columns Group By table_name;
查看数据库语言
select userenv('language') from dual;
select * from v$nls_parameters;
单行函数：
lower() : 将字符串中的字符全都转换成小写；
upper() : 将字符串中的字符全都转换成大写；
initcap() : 将字符串中的首字母转换成大写，其它字符转换成小写；
concat('Hello','World') HelloWorld
substr('HelloWorld',1,5) Hello
length('HelloWorld') 10
instr('HelloWorld','W') 6
lpad(salary,10,'*') *****24000
rpad(salary,10,'*') 24000*****
trim('H' from 'HelloWorld') elloWorld
replace('abcd','b','m') amcd
round:四舍五入 round(45.926,2) 45.93
trunc:截断 trunc(45.926,2) 45.92
mod:求余 mod(1600,300) 100
months_between:两个日期相差的月数
add_months:向指定日期中加上若干月数
next_day:指定日期的下一个日期
last_day:本月的最后一天
round:日期四舍五入
trunc:日期截断
例：sysdate='25-JUL-95'
round(sysdate,'MONTH') 01-AUG-95
round(sysdate,'YEAR') 01-JAN-96
trunc(sysdate,'MONTH') 01-JUL-95
trunc(sysdate,'YEAR') 01-JAN-95
隐式数据类型转换：
varchar2 or char number
varchar2 or char date
number varchar2
date varchar2
显式数据类型转换：
character to_number number
number to_char character
character to_date date
date to_char character
to_char对日期的转换：
to_char(date,'format_model')
格式：
必须包含在单引号中而且大小写敏感
可以包含任意的有效的日期格式
日期之间用逗号隔开
格式 说明 举例
YYYY Full year in numbers 2011
YEAR Year spelled out(年的英文全称) twenty eleven
MM Two-digit value of month 月份(两位数字) 04
MONTH Full name of the month(月的全称) 4月
DY Three-letter abbreviation of the day of the week(星期几)星期一
DAY Full name of the day of the week 星期一
DD Numeric day of the month 02
时间格式：
HH24:MI:SS AM 15:45:32 PM
使用双引号向日期中添加字符
DD "of" MONTH 12 of OCTOBER
to_char对数字的转换：
to_char(number,'format_model')
9 数字
0 零
$ 美元符
L 本地货币符号
. 小数点
, 千位符
to_number将字符转成数字
to_number(char[,'format_model'])
to_date将字符转成日期
to_date(char[,'format_model'])
通用函数：
nvl(expr1,expr2)
nvl2(expr1,expr2,expr3)
nullif(expr1,expr2)
coalesce(expr1,expr2,...,exprn)
case表达式：sql99的语法，类似Basic,比较繁琐
decode函数：Oracle自己的语法，类似Java，比较简洁
case expr when comparison_expr1 then return_expr1
[when comparison_expr2 then return_expr2
else else_expr]
end
decode(col|expression, search1, result1[,search2,result2,...,][,default])
order by 空值问题nulls first, nulls last
替代变量：&变量名
--查询表的主键字段
select * from user_cons_columns
where constraint_name = (select constraint_name from user_constraints
where table_name = 'EC_CUST_PRODUCT' and constraint_type ='P');
创建数据库链接
-- Drop existing database link
drop public database link dblink_name;
-- Create database link
create public database link dblink_name connect to SYSTEM using '192.168.1.73:1521/oracle';
create database link dblink_fxqtest_zkrprpall connect to zkrprpall IDENTIFIED by "sinosoft" using '10.16.8.23:1521/fxqtest';
创建数据库同义词
SELECT 'create or replace SYNONYM ' || c.TABLE_NAME || ' for ' ||
c.TABLE_NAME ||
decode(SUBSTR(c.TABLE_NAME, 1, 2),
'GU',
'@DBLINK_FXQTEST_ZKRPRPALL',
'@DBLINK_FXQTEST_ZKRCLAIM')||';'
from cat c
where c.TABLE_TYPE = 'SYNONYM';
--闪回sql
select * from ggriskconfigvalue as of timestamp to_timestamp('2016-10-18 16:00:00', 'yyyy-mm-dd hh24:mi:ss');
alter table ggriskconfigvalue enable row movement;
flashback TABLE ggriskconfigvalue to timestamp to_timestamp('2016-10-18 16:00:00', 'yyyy-mm-dd hh24:mi:ss');
