Oracle 技巧

2016/01/01 Database

这里总结一些 Oracle 数据库的奇巧淫技。

信息

用户

-- 查看所有用户及表空间
select username from dba_users;
select tablespace_name from dba_tablespaces;
-- 忘记密码
conn / as sysdba
alter user 用户名(system identified by  新密码(m1234
-- 创建用户及其表
create user TESTCTL identified by TESTCTL;
grant connect, resource to TESTCTL;
COMMIT;
--------------------------------------------------------
-- DDL for Type NUM_LIST
--------------------------------------------------------
CREATE OR REPLACE TYPE "TESTCTL"."NUM_LIST"
AS TABLE OF NUMBER/* datatype */;
-- 当前用户下表
select * from user_tables
-- 特定用户的表
SELECT * FROM ALL_TABLES WHERE OWNER='USER_NAME' 
-- 删除用户及表空间
drop user ×× cascade
-- 说明: 删除了user,只是删除了该user下的schema objects,是不会删除相应的tablespace的。
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
-- 删除当前用户下的所有表
-- 加了cascade就可以把用户连带的数据全部删掉。删除后再创建该用户。
drop user user_name cascade;
-- 创建管理员用户
create user 用户名 identified by 密码 default tablespace space_data(表空间名称) temporary tablespace space_temp(临时表空间名称);
-- 授权
grant connect,dba to 用户名;
-- 修改限额
ALTER USER "用户名" QUOTA UNLIMITED ON SPACE_DATA(表空间名称);
-- 查看所有用户对象
select uo.object_name,uo.object_type from user_objects uo where uo.object_type<>'LOB' order by uo.object_type desc
-- 批量生成删表语句
select 'drop table '||table_name||';' from cat where table_type='TABLE'
-- 查看用户所有表及每个表的记录数
select table_name,num_rows from (select * from user_tables)
where table_name not like 'APEX$%' order by table_name asc;
-- 其值可能与count(1)的值不一致,需要先analyze.
analyze table table_name estimate statistics;

SET 命令

SQL>set colsep' ';     //-域输出分隔符
SQL>set echo off;     //显示start启动的脚本中的每个sql命令,缺省为on
SQL> set echo on               //设置运行命令是是否显示语句
SQL> set feedback on;       //设置显示“已选择XX行”
SQL>set feedback off;      //回显本次sql命令处理的记录条数,缺省为on
SQL>set heading off;     //输出域标题,缺省为on
SQL>set pagesize 0;      //输出每页行数,缺省为24,为了避免分页,可设定为0
SQL>set linesize 80;      //输出一行字符个数,缺省为80
SQL>set numwidth 12;     //输出number类型域长度,缺省为10
SQL>set termout off;     //显示脚本中的命令的执行结果,缺省为on
SQL>set trimout on;   //去除标准输出每行的拖尾空格,缺省为off
SQL>set trimspool on;  //去除重定向(spool)输出每行的拖尾空格,缺省为off
SQL>set serveroutput on;  //设置允许显示输出类似dbms_output
SQL> set timing on;          //设置显示“已用时间:XXXX
SQL> set autotrace on-;    //设置允许对执行的sql进行分析
set verify off                     //可以关闭和打开提示确认信息old 1new 1的显示.
SET SERVEROUTPUT ON SIZE UNLIMITED;

存储过程

创建

CREATE SEQUENCE seqTest
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXvalue -- 不设置最大值
NOCYCLE -- 一直累加,不循环
CACHE 10; --设置缓存cache个序列,如果系统down掉了或者其它情况将会导致序列不连续,也可以设置为---------NOCACHE

得到值

-- 当前值
SELECT Sequence名称.CurrVal FROM DUAL;
insert into 表名(id,name)values(seqtest.Nextval,'sequence 插入测试');
-- 第一次NEXTVAL返回的是初始值;随后的NEXTVAL会自动增加你定义的INCREMENT BY值,然后返回增加后的值。
-- CURRVAL 总是返回当前SEQUENCE的值,但是在第一次NEXTVAL初始化之后才能使用CURRVAL,否则会出错。
-- 一次NEXTVAL会增加一次 SEQUENCE的值,所以如果你在同一个语句里面使用多个NEXTVAL,其值就是不一样的。

拥有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有sequence参数.如果想要改变start值,必须 drop sequence 再 re-create。

alter sequence SEQTEST maxvalue 9999999;
DROP SEQUENCE seqTest; 

常用函数

字符串

-- 左填充 lpad, 右填充 rpad
-- lpad(string, padded_length, [ pad_string ] )
--  lpad(被填充的字符串, 填充之后的字符串长度, [填充字符串 默认空格])
select lpad(22,10,'0') from dual; 结果: 1	0000000022

-- TRIM 函数
-- TRIM([ { { LEADING | TRAILING | BOTH } [ trim_character ] | trim_character } FROM] trim_source)
-- 使用默认的参数,默认情况下TRIM会同时删除字符串前后出现的空格。
select trim (' DWEYE ') "TRIM e.g." from dual --删除左右
select trim (both from ' DWEYE ') "TRIM e.g." from dual; --删除左右
select trim (trailing from ' DWEYE ') "TRIM e.g." from dual; --删除尾部
select trim (leading from ' DWEYE ') "TRIM e.g." from dual; --删除头部
select trim ('x' from 'xxxxDWEYExxxx') "TRIM e.g." from dual; --删除特定字符 可配合上边三个参数使用,不支持多字符
select rtrim('xyxxDWEYExyyx','xy') "e.g." from dual; --xyxxDWEYE
select ltrim('xyxxDWEYExyyx','xy') "e.g." from dual; --DWEYExyyx xy这里不代表字符串,而代表两字符x和y

-- REPLACE 函数
-- 删除字符串头部指定字符串
select REPLACE(字段名,'指定字符','替换字符') From 表名

时间

时间格式转换:

-- 时间转字符串
SELECT CONVERT(VARCHAR(40),GETDATE(),25);
SELECT TO_CHAR(sysdate, 'dd/mm/yyyy hh:mi:ss AM') FROM DUAL;
SELECT TO_CHAR(systimestamp, 'dd/mm/yyyy hh:mi:ss AM') FROM DUAL;

-- 字符串转时间
SELECT CAST('2014-6-1' AS DATETIME);
SELECT TO_DATE(SUBSTR('20141011132010',0,14), 'yyyymmddhh24miss') FROM DUAL;
SELECT TO_TIMESTAMP('2014-Apr-5 0:0:0', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

TO_DATE() 参数

日期格式参数 含义说明
D 一周中的星期几
DAY 天的名字,使用空格填充到9个字符
DD 月中的第几天
DDD 年中的第几天
DY 天的简写名
IW ISO标准的年中的第几周
IYYY ISO标准的四位年份
YYYY 四位年份
YYY,YY,Y 年份的最后三位,两位,一位
HH 小时,按12小时计
HH24 小时,按24小时计
MI
SS
MM
Mon 月份的简写
Month 月份的全名
W 该月的第几个星期
WW 年中的第几个星期

特殊函数

-- 判断为空
isnull(); NVL();
-- 先分组在组内排序分配编号
row_number() over (partition by activity_id order by user_id desc) 
 -- 多行合并 必须用group by 
select a.activity_id, wmsys.wm_concat(a.user_id) users from aor_assgnmt_skillgrp_assn a where a.activity_id = '23f0a122-2a60-40c2-8495-ec0fd55c3d5a' group by a.activity_id; 
-- 选择函数 当字段或字段的运算的值等于值1时,该函数返回值2,否则返回值3,当然值1,值2,值3也可以是表达式。
DECODE (字段或字段的运算,值1,值2,值3

变量

声明变量,游标,输出,循环:

SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
v_last_month_date DATE:=TRUNC(ADD_MONTHS(SYSDATE, -1),'MM');
v_month_date DATE:=TRUNC(ADD_MONTHS(SYSDATE, -0),'MM');
 
CURSOR AID_CURSOR IS
SELECT * FROM TABLE_NAME;
AID AID_CURSOR%ROWTYPE;
 
BEGIN
OPEN AID_CURSOR;
LOOP
FETCH AID_CURSOR INTO AID;
EXIT WHEN AID_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(AID.column_name||AID.column_name2);
END LOOP;
CLOSE AID_CURSOR;
END;
/

Search

    Table of Contents