oracle学习笔记

oracle学习笔记

1. 表相关

-- 查询以KJ为开头的所有表
SELECT table_name FROM user_tab_cols WHERE table_name LIKE 'KJ%';

-- 查询以KJ为开头的表在哪个模式中
SELECT * FROM dba_tables WHERE table_name = 'KJ00';

-- 利用user_objects表查询建表时间
select * from user_objects;
-- 表名  object_name
-- 创建时间  created 
-- 最后一个操作时间  last_ddl_time

-- 查询临时表
select * from user_objects 
where object_type = 'TABLE' 
and object_name like '%TEMP_CWH_%' 
order by created

-- 查看当前用户下的表
SELECT * FROM USER_TABLES;

-- 查看当前数据库中的所有表
SELECT * FROM DBA_TABLES;

-- 查看建表语句
select dbms_metadata.get_ddl('TABLE','table_name') from dual;

2. 函数

decode

oracle没有if else, 但是有case when和decode,可以达到类似效果,decode不像if函数那么直观, 但是语法比较简洁.

-- decode  
--使用方法 decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
-- eg: 如果该字段是1000 返回D ...
decode(column_name,1000,'D',2000,'C',3000,'B',4000,'A',’Other’)

字符串操作

大小写转换

lower(): 大写转为小写
upper(): 小写转大写
initcap(): 首字母大写

字符串拼接

concat(): 和MySQL一样, concat(‘str1’,‘str2’)
|| : ‘str1’ || ‘str2’

字符串截取

substr(str,start_position,length): str是需要截取的字符串, start_position是起始位置(从1开始),length是截取的字符串的长度, length是可选项, 不写的话默认到最后.

字符串查找

instr(str,substr,position,ocurrence): str是源字符串, substr是需要查找的字符串,position是开始查找的位置,occurence是原字符串中第几次出现子字符串,
instr()和length()可以配合substr()或replace(),replace_regexp()去截取字符串替代字符串

字符串替换

replace(): replace(str, str1, str2) 将str中的str1替换成str2
replace_regexp():共有 6个参数
第一个是输入的字符串
第二个是正则表达式
第三个是替换的字符
第四个是标识从第几个字符开始正则表达式匹配。（默认为1）
第五个是标识第几个匹配组。（默认为全部都替换掉）
第六个是是取值范围:
i：大小写不敏感；
c：大小写敏感；
n：点号 . 不匹配换行符号；
m：多行模式；
x：扩展模式，忽略正则表达式中的空白字符

3. 存储过程

存储过程中的参数与变量类似于Java中的参数和变量, 变量定义于函数内部, 参数用于接收函数间的传值
begin和end之间写存储过程的逻辑
oracle的sql中没有if 但是存储过程中有

CREATE OR REPLACE procedure "DROP_TABLE" (
-- parm 参数
   pram in varchar2
) is
-- variable  变量
    variable number(10);
begin
   select count(*)
   into v_count
   from user_objects
   where object_name = upper(p_table);
   if v_count > 0 then
      execute immediate 'drop table ' || p_table ||' cascade constraints';
   end if;
end;