oracle常用函数

这篇具有很好参考价值的文章主要介绍了oracle常用函数。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

查看版本

select * from v$version;

查看数据库中所有表

select * from all_tables WHERE OWNER='DATAWARE'

查看表中所有字段

SELECT * FROM user_col_comments where table_name='DW_FRONT_APPLY'

oracle通过字段名查找表名

select table_name from user_tab_columns t where t.COLUMN_NAME = '字段名';

建表语句

create table DW_T_ORDER_COUPON_PAY_INFO
(
   N_ID NUMBER(11),
   C_APP_ID VARCHAR2(24),
   C_FUND_ID VARCHAR2(24),
   N_APPLY_AMOUNT NUMBER(13,2),
   N_COUPON_AMOUNT NUMBER(13,2),
   N_REDUCE_AMOUNT NUMBER(13,2),
   C_BASE_PID VARCHAR2(32),
   C_NEW_PID VARCHAR2(32),
   N_BASE_POINT NUMBER(10),
   N_NEW_POINT NUMBER(10),
   C_PAY_ORDER_NO VARCHAR2(36),
   C_STATUS VARCHAR2(1),
   C_IN_USE int,
   D_CREATE_TIME DATE,
   D_UPDATE_TIME DATE
)
/
comment on table DW_T_ORDER_COUPON_PAY_INFO is '贴息券支付记录表'
/
comment on column DW_T_ORDER_COUPON_PAY_INFO.C_APP_ID is '订单编号'
/
comment on column DW_T_ORDER_COUPON_PAY_INFO.C_FUND_ID is '资方ID'
/
comment on column DW_T_ORDER_COUPON_PAY_INFO.N_APPLY_AMOUNT is '申请金额'
/
comment on column DW_T_ORDER_COUPON_PAY_INFO.N_COUPON_AMOUNT is '免息券金额'
...

给数据库表授权:给用户开通部分表的查询权限

https://www.cnblogs.com/bellwether/p/10404956.html

GRANT SELECT ON DW_CXD_PRE_ORDER TO DATAMART;
GRANT 查询权限 ON 具体表 TO 用户;

oracle存储过程

https://blog.csdn.net/qq_39443053/article/details/104044530

https://blog.csdn.net/qq_39443053/article/details/104044530

其中call oracle函数() 为调用存储过程

一 .创建存储过程语法

二.输出案例

三.调用存储过程

ORACLE中的rowid用法

https://www.cnblogs.com/xzdblogs/p/6495755.html

日期函数

https://blog.csdn.net/Doris2016/article/details/118362336

--获取上个月最后一天
SELECT TO_DATE(TO_CHAR(last_day(ADD_MONTHS(trunc(TO_DATE(TO_CHAR(TRUNC(SYSDATE, 'mm'), 'yyyy-mm-dd'),'yyyy-mm-dd')),-1)),'yyyy-mm-dd'),'yyyy-mm-dd')  FROM dual;  输出 31-01-20

--获取上个月第一天
SELECT TO_DATE(TO_CHAR(ADD_MONTHS(trunc(TO_DATE(TO_CHAR(TRUNC(SYSDATE, 'mm'), 'yyyy-mm-dd'),'yyyy-mm-dd')),-1),'yyyy-mm-dd'),'yyyy-mm-dd') FROM dual; 

重复值处理

https://blog.csdn.net/loveLifeLoveCoding/article/details/85156946

查询结果只显示重复的字段

1. 查询重复的单个字段(group by)

select 重复字段A, count(*)
  from 表
 group by 重复字段A
having count(*) > 1

2.查询重复的多个字段(group by)
select 重复字段A, 重复字段B, count(*)
  from 表
 group by 重复字段A, 重复字段B
having count(*) > 1

查询结果显示重复数据的所有字段

1.查询重复的单个字段( rowid)
排序是为了更直观的比较多个重复数据
-- table a,table b  代表同一张表 
select a.*
   from table a, table b
  where a.重复字段 = b.重复字段
    and a.rowid != b.rowid
  order by a.重复字段
2.查询重复的多个字段( rowid)
--  table a, table b 代表同一张表
select a.*
   from table a, table b
  where a.重复字段A = b.重复字段A
    and a.重复字段B = b.重复字段B
    and a.rowid != b.rowid
  order by a.重复字段A

删除重复数据

1.删除重复数据中rowid 最大的数据
-- 先查出来看一眼  table a,table b 属于同一张表  rowid 最大的数据
select *
  from table a
 where rowid != (select max(rowid)
                   from table b
                  where a.重复字段 = b.重复字段)
 
-- 删除这部分数据  rowid 最大的数据
 
delete from table a
 where rowid != (select max(rowid)
                   from table b
                  where a.重复字段 = b.重复字段)
2.删除重复数据中rowid 最小的数据
-- 先查出来看一眼  table a,table b 属于同一张表  rowid 最小的数据
select *
  from table a
 where rowid != (select min(rowid)
                   from table b
                  where a.重复字段 = b.重复字段)
 
-- 删除这部分数据  rowid 最小的数据
 
delete from table a
 where rowid != (select min(rowid)
                   from table b
                  where a.重复字段 = b.重复字段)
3.删除所有重复的数据
-- 慎重考虑后执行,后悔记得及时回滚。
 
delete from table group by 重复字段 having count(重复字段) > 1

查看表大小


select Segment_Name "表名",sum(bytes)/1024/1024 "表大小(M)" 
From User_Extents 
Group By Segment_Name 
having Segment_Name='你要查询的表名'; 

in()参数超过1000处理方式

select * from table01 t
where t.ext_id in () or t.ext_id in()...;

MySQL与Oracle数据库常见数据类型对应关系

编号 ORACLE MYSQL 注释
1 NUMBER int / DECIMAL DECIMAL就是NUMBER(10,2)这样的结构INT就是是NUMBER(10),表示整型;
MYSQL有很多类int型,tinyint mediumint bigint等,不同的int宽度不一样
2 Varchar2(n) varchar(n)
3 Date DATATIME 日期字段的处理
MYSQL日期字段分DATE和TIME两种,ORACLE日期字段只有DATE,包含年月日时分秒信息,用当前数据库的系统时间为 SYSDATE, 精确到秒,或者用字符串转换成日期型函数TO_DATE(‘2001-08-01','YYYY-MM-DD')年-月-日 24小时:分钟:秒的格式YYYY-MM-DD HH24:MI:SS TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC.日期型字段转换成字符串函数TO_CHAR(‘2001-08-01','YYYY-MM-DD HH24:MI:SS')

日期字段的数学运算公式有很大的不同。MYSQL找到离当前时间7天用 DATE_FIELD_NAME > SUBDATE(NOW(),INTERVAL 7 DAY)ORACLE找到离当前时间7天用 DATE_FIELD_NAME >SYSDATE - 7;

MYSQL中插入当前时间的几个函数是:NOW()函数以`'YYYY-MM-DD HH:MM:SS'返回当前的日期时间,可以直接存到DATETIME字段中。CURDATE()以'YYYY-MM-DD'的格式返回今天的日期,可以直接存到DATE字段中。CURTIME()以'HH:MM:SS'的格式返回当前的时间,可以直接存到TIME字段中。例:insert into tablename (fieldname) values (now())

而oracle中当前时间是sysdate
4 INTEGER int / INTEGER Mysql中INTEGER等价于int
5 EXCEPTION SQLEXCEPTION 详见<<2009001-eService-O2MG.doc>>中2.5 Mysql异常处理
6 CONSTANT VARCHAR2(1) mysql中没有CONSTANT关键字 从ORACLE迁移到MYSQL,所有CONSTANT常量只能定义成变量
7 TYPE g_grp_cur IS REF CURSOR; 光标 : mysql中有替代方案 详见<<2009001-eService-O2MG.doc>>中2.2 光标处理
8 TYPE unpacklist_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; 数组: mysql中借助临时表处理
或者直接写逻辑到相应的代码中,
直接对集合中每个值进行相应的处理
详见<<2009001-eService-O2MG.doc>>中2.4 数组处理
9 自动增长的序列 自动增长的数据类型 MYSQL有自动增长的数据类型,插入记录时不用操作此字段,会自动获得数据值。ORACLE没有自动增长的数据类型,需要建立一个自动增长的序列号,插入记录时要把序列号的下一个值赋于此字段。
10 NULL NULL 空字符的处理
MYSQL的非空字段也有空的内容,ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断,如果为NULL或空字符,需要把它改成一个空格的字符串。

varchar和number类型自动转化

oracle中纯数字的varchar2类型和number类型自动转换:
比如:
如果varchar里面有'sale_99' 就不会自动转换 
解决方案:case when 字段='sale_99' then '99' 
两个表的字段定义了不同的类型。一个字段是varchar2类型,另一个字段是number类型,内容如下:'00187'和187。在使用中发现会自动将varchar2类型转换为number,即187和187。
测试的sql:
select
case when '0110' = 110 then
'true'
else
'false'
end
from dual;
------------
select
case when to_number('0110') = to_number(110) then
'true'

Oracle单表备份三种方案

备份方案一:

1. 备份

create table [备份名] as select * from [表名];

2. 恢复

truncate table org_group;

insert into org_group select * from [备份名] ;

3. 说明

此种情况适用于,同一个数据库,需要备份某张表。

 

备份方案二:

1. 备份

oracle用户终端执行:

exp [用户名]/[密码] tables=[表一],[表二] file=/home/oracle/table.dmp

2. 恢复

oracle用户终端执行:

imp [用户名]/[密码] grants=y commit=y full=y ignore=y file=/home/oracle/table.dmp

 

备份方案三:

1. 备份

登录sqlplus:

sqlplus / as sysdba

创建directory:

create directory dpdata as '/home/oracle/';

目录创建以后,就可以把读写权限授予特定用户:

grant read, write on directory dpdata to [用户名];

oracle用户终端执行:

expdp [用户名]/[密码] tables=[表一],[表二] directory=dpdata dumpfile=table.dmp job_name=cases_export

2. 恢复

登录sqlplus :

sqlplus / as sysdba

创建directory:

create directory dpdata as '/home/oracle/';

目录创建以后,就可以把读写权限授予特定用户:

grant read, write on directory dpdata to [用户名];

oracle用户终端执行:

impdp [用户名]/[密码] directory=dpdata dumpfile=table.dmp job_name=cases_export

3. 说明

查询dba_directories查看所有directory:

select * from dba_directories;

删除directory:

drop directory dpdata;

删除

Oracle 删除数据的几种方法

https://blog.csdn.net/songylwq/article/details/8505391

DELETE FROM表名 WHERE 条件; 需要commit;

删除表(记录和结构)的语名delete   ————   truncate      ———— drop
  DELETE (删除数据表里记录的语句)

  DELETE FROM 表名 WHERE 条件;
  
    delete from  表名 ;也可以不加条件



  注意:删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused.

  如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间

  TRUNCATE TABLE 表名;

  此操作不可回退.

  相同点

  truncate和不带where子句的delete, 以及drop都会删除表内的数据

  注意:

1.这里说的delete是指不带where子句的delete语句

2.在存储过程中默认是不允许执行truncate table tablename操作的,所以要使用

execute   immediate 'truncate table tablename';
例如:
CREATE OR REPLACE PROCEDURE proc_delete_all_data
IS
BEGIN
   execute immediate 'truncate table T_FLOW_ACCOUNT';
   execute immediate 'truncate table T_FLOW_MERCHANT';
END proc_delete_all_data;

不同点:

  1. truncate和 delete只删除数据不删除表的结构(定义)

  drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.

  2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.

  truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

  3.delete语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动

  显然drop语句将表所占用的空间全部释放

  truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage;   truncate会将高水线复位(回到最开始).

  4.速度,一般来说: drop>; truncate >; delete

  5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及

  使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.

  想删除表,当然用drop

  想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete.

  如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据

  oracle中删除表中的一列语句

  alter table 表名 drop colum 列名

Commit提交

oracle在update、delete、和inset操作后,要commit操作

在oracle中使用sql语句执行了delete和update和insert命令,在本地数据库中已执行成功查看并没有删除成功或更新数据库,很是尴尬,为什么会出现这种不一致的情况呢?

    原来是因为我执行命令后没有commit提交,那么更新的内容只是被保存到内存中,而不是提交到数据库中,将不会被其他Session(对话)看到,其他对话看到的是更新前的数据。当用户退出对话时,Oracle才会自动commit。

SQL语言分为五大类:
DDL(数据定义语言) - Create、Alter、Drop 这些语句自动提交,无需用Commit提交。

DQL(数据查询语言)- Select查询语句不存在提交问题。

DML(数据操纵语言) - Insert、Update、Delete 这些语句需要Commit才能提交。

DTL(事务控制语言) - Commit、Rollback 事务提交与回滚语句。

DCL(数据控制语言) - Grant、Revoke 授予权限与回收权限语句。

执行完DML语句,若没有commit再执行DDL语句,也会自动commit未被commit的数据。

回滚

https://blog.csdn.net/qq_36513313/article/details/103256504

单表记录回滚到指定时间,在进行数据回滚前,也可以通过命令查看回滚时间点前的数据情况,语句如下:

select * from 表名  as of timestamp to_timestamp('2019-11-26 15:02:00', 'yyyy-mm-dd hh24:mi:ss');

接下来就是进行数据回滚操作了。 (1)开启闪回,如果不开启无法进行闪回

alter table 表名 enable row movement;

(2)闪回表数据到某个时间点

flashback table 表名 to timestamp TO_TIMESTAMP('2019-11-26 15:02:00', 'yyyy-mm-dd hh24:mi:ss');

(3)关闭闪回文章来源地址https://www.toymoban.com/news/detail-622115.html

alter table 表名 disable row movement;

到了这里,关于oracle常用函数的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Oracle数据库面试题 精选 Oracle 面试题

    1.解释冷备份和热备份的不同点以及各自的优点 冷备份 发生在数据库已经正常关闭的情况下,将关键性文件拷贝到另外位置的一种说法。适用于所有模式的数据库。 优点 1. 是非常快速的备份方法(只需拷贝文件) 2. 容易归档(简单拷贝即可) 3. 容易恢复到某个时间点上(只

    2024年02月05日
    浏览(104)
  • 【Oracle】使用 SQL Developer 连接 Oracle 数据库

    SQL Developer 是 Oracle 官方推出的一款免费的数据库开发工具,它提供了丰富的数据库开发功能,其中包括连接 Oracle 数据库的功能。 在本文中,我们将从多个方面详细阐述如何使用 SQL Developer 连接 Oracle 数据库。 在连接 Oracle 数据库前,需要需要做一些准备工作,包括安装 SQ

    2024年02月06日
    浏览(70)
  • Oracle数据库

    ①层次型数据库 ②网状型数据库 ③关系型数据库(主要介绍) E-R图:属性(椭圆形),实体(矩形),联系(菱形-一对一、一对多、多对多) 注:有的联系也有属性 关系型数据库的设计范式: 第一范式(1NF):属性不可再分,字段保证原子性 第二范式(2NF):在满足1

    2024年02月08日
    浏览(59)
  • Oracle 开发篇+Java通过HiKariCP访问Oracle数据库

    标签:HikariCP、数据库连接池、JDBC连接池、 释义:HikariCP 是一个高性能的 JDBC 连接池组件,号称性能最好的后起之秀,是一个基于BoneCP做了不少的改进和优化的高性能JDBC连接池。 ★ Java代码 ※ 如果您觉得文章写的还不错, 别忘了在文末给作者点个赞哦 ~

    2024年02月13日
    浏览(59)
  • Oracle数据库概念简介

    一般意义上的数据库包含两个部分 库:就是一个存储一堆文件的文件目录 数据库管理系统:管理库的系统 数据库管理系统(Database Management System),是一种操纵和管理数据库的大型软件,用于建立、使用和维护数据库,简称 DBMS ; 它对数据库进行统一的管理和控制,以保证数

    2024年02月09日
    浏览(52)
  • 快速监控 Oracle 数据库

    Oracle 数据库在行业内应用广泛,通常存放的非常重要的数据,监控是必不可少的,本文使用 Cprobe 采集 Oracle 监控数据,极致简单,分享给大家。 安装配置 Oracle 简单起见,我使用 Docker 启动 Oracle,命令如下: 如上命令启动之后,Oracle 的监听端口是 1521,用户名/密码是 syst

    2024年01月20日
    浏览(52)
  • oracle数据库巡检脚本

    用于Oracle数据库巡检的示例脚本: 

    2024年02月14日
    浏览(63)
  • 数据库(二) Oracle篇

    概述 SQL函数有单行函数和多行函数,其区别为: 单行:输入一行,返回一行,如字符、数字、转换、通用函数等 多行:输入多行,返回一行,也称为分组函数、组函数、聚合函数, 且多行函数会自动滤空 字符函数 CONCAT( X,Y): 连接字符串X和Y INSTR(X,STR) : 后面STR在前面字符串X第一次

    2024年02月10日
    浏览(56)
  • Oracle数据库完整卸载

    进入计算机管理,在服务中,找到oracle开头的所有服务,右击选择停止。 快捷键:ctrl+shift+esc打开任务管理器 点击开始菜单找到Oracle,然后点击Oracle安装产品,再点击Universal Installer。 点击之后稍等一会然后会进入进入下图界面,点击卸载产品。 单击“卸载产品”,选中除“Or

    2024年01月16日
    浏览(66)
  • Oracle大型数据库技术

    sqlplus 记录实验过程的方法: 开启相关服务 oracleserviceORCL 控制面板–管理–服务–找到后开启 命令行方法: cmd–net start|stop oracleserviceorcl 常用操作 显示当前用户名 常用的用户名和口令 :dba用户,默认口令:change_on_install,Oraclell,必须以sysdba或者sysoper身份登录 system: 普通管

    2024年02月19日
    浏览(51)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包