search
HomeDatabaseMysql TutorialOracle常用知识小结
Oracle常用知识小结Jun 07, 2016 pm 03:03 PM
oraclePrefaceCommonly usedKnowledgeProject Development

前言: 前一段时间项目开发数据库环境为Oracle,作为一个SQLer,表示各种不适应。所以刚开始的时候走了一些弯路,浪费了一席时间。因此就想把这些常用的东西给总结一下,算是对自己学习的总结,也希望能给初次使用Oracle的朋友提供一些帮助。 1. 创建自增主

前言:

前一段时间项目开发数据库环境为Oracle,作为一个SQLer,表示各种不适应。所以刚开始的时候走了一些弯路,浪费了一席时间。因此就想把这些常用的东西给总结一下,算是对自己学习的总结,也希望能给初次使用Oracle的朋友提供一些帮助。

1. 创建自增主键

对于习惯了SQL SERVER的图形化界面操作的SQLer,很长一段时间不用oracle,创建一个带自增字段的主键的表都觉得很费事,但是习惯了之后也还好,其实也可以用EA设计好表结构之后,直接生成SQL,这里只是为了演示如何通过SQL语句创建表。

示例:

1)创建表

Create Table Test ( ID int, Name varchar2(200), Primary Key(ID) )

2)创建自增序列

Create Sequence Test_Sequence

Increment by 1

Start with 1

Nomaxvalue

Nocycle

3)创建触发器

Create Or Replace Trigger Test_Trigger

Before Insert On Test

For Each Row

Begin

select Test_Sequence.Nextval Into :new.id From dual;

End;

以上三步就完成了带有主键的自增序列的表的创建。

2. 修改表名、字段名、字段类型、长度

在实际的开发,会有遇到需要修改表名、字段的一些情况,下面就这种修改一一说明。

1)修改表名: Alter Table Test Rename To Test1

2)修改字段名 Alter Table Test Rename Column Name To Name1

3)新增字段 Alter Table Test Add Age int

4)删除字段 Alter Table Test drop column Age

5) 修改字段类型、长度 Alter Table Test Modify Age number(8,2)

3. 时间、日期相关

在oracle数据库中,没有专门的date数据类型,有时候我们只需要存储日期,不需要时间,而我们在数据库存储时都会自动加上日期,针对于这样的情况,我们可以通过以下方式处理:

未使用trunc时,查询结果如下: select sysdate from dual 2013-12-22 12:06:16

使用trunc之后,结果如下: select trunc(sysdate) from dual 2013-12-22

这样我们可以通过update语句加上trunc将日期+时间(2013-12-22 12:03:03)的存储改成日期(2012-12-22)形式,事实上数据库内部存储的也还是“2013-12-22 00:00:00”只不过在查询显示的时候,展示的是日期形式。

下面讲一下,oracle中如何获取当前年、月、日、季度、星期、星期几。

获取当前年: select to_char(sysdate,‘yy’) from dual

获取当前月: select to_char(sysdate,‘MM’) from dual

获取当前天: select to_char(sysdate,‘dd’) from dual

获取当前季度: select to_char(sysdate,‘Q’) from dual

获取当前第几个星期: select to_char(sysdate,‘iw’) from dual

获取当前是星期几: select to_char(sysdate,‘day’) from dual

4. Top N

在oracle中,不能使用top,但是也有相应的替代的解决办法,就是使用rownum。比如我们想取某些信息集合的top5,具体演示如下:

with as sub ( select *,rownow as PX from Test)

select * from PX where PX

这样就解决了top问题,在使用这样方式查询top N时,最好先按照某些字段对数据源进行排序。

5. 多表关联更新

在实际的开发中,有时会需要将某一个表的字段内容更新为另一个表的字段内容,这个时候就会用到多表关联更新。在SQL Server中,我们可以这样做:

update t set id=t1.id from Test t inner join Test1 t1 on t.no=t1.no

但是,在Oracle中却不支持这样的用法,当然也有替代的方法可用,我们可用通过使用子查询的方式来实现,如下:

update Test t set t.id=(select t1.id from Test1 t1 where t1.no=t.no) where exists(select * from Test1 t1 where t1.no=t.no)

需要注意的是子查询的值,只能是一个唯一值,不能是多值。后边的where exists也是很重要的,如果没有的话,所得到的结果可能会有误。

还有如果是在数据库里做更新、删除、新增的操作,一定要记得commit提交,要不然在程序里是查询不到已修改的变化。

6. 存储过程、函数

关于存储过程和函数在数据库中都是经常用到的,在sql server和oracle中语法稍有不太一样,这里就只做两个简单的示例即可。

存储过程:

一般的语法:

Create Or Replace Procedure 存储过程名 (parm1 in type,parm2 out type,....)

as

变量 类型 范围;--(例:name varchar2(200);)

变量 类型 范围;--(例:age number(8);)

begin

select * from Test where Name=name; --有判断的存储过程

if(age

elsif (age>10 and age

else raise NO_DATA_FOUND;

end if;

Exception

when others then Rollback;

end;

示例:

Create Or Replace TestAge(age in int,agegroup out varchar2(200))

as

age_group varchar2(200);

begion

if(age

age_group:="幼年";

elsif(age>10 and age

age_group:="青少年";

else age_group:="成年";

end if;

agegroup:=age_group;

end;

函数:

一般语法:

Create Or Replace Function 函数名 ( 参数名 in/out 参数类型, 参数名 in/out 参数类型, ..... )

return 类型

as 参数名 类型;--(例:name varchar2(200);) ....

begin

......

......

retrun expression;

end 函数名;

示例:

Create Or Replace Function GetAgeGroup (age in int)

retrun varchar2

as agegroup varchar2(200);

begin if(age

elsif(age>=10 and age

else agegroup="成年";

end if; retrun agegroup;

end GetAgeGroup;

这一块的内容没有什么别的意义,只是Oracle的函数和存储过程跟sql的有些不同,仅供参考,区分使用。

7. 索引

关于索引,即使在sql server中一般也很少使用,因为数据量还没有那么大,这次项目中因为牵扯的数据量比较大,即使在oracle,查询、检索速度还是不行,所以需要创建索引。但是索引创建之后,跟没创建没有什么差别,后来经过分析才得知,oracle 中的索引的创建还是有很多的规矩的,下面就简要是列一下需要注意的事项。

1)索引应该在SQL语句中“Where”或者“and”部分涉及的表的列创建。

2)Order by中用索引。需要注意的是Order by中所有的列都包含在相同的索引中并保持在索引中的排列顺序,且order by中所有的列必须定义为非空,否则索引无效。

3)避免改变索引列的类型。

例如:select * from EMP where EMP_TYPE='123'

事实上oracle在执行的时候,会执行如下语句:

select * from EMP where EMP_TYPE=to_number('123')

这样类型转化并没有发生在索引列上,所以索引仍旧有效,但是,如果写成这样,索引就会失效的。

select * from EMP where to_number(EMP_TYPE)=123

因为索引列的类型发生了变化,所以索引失效了。

4)在Where子句中需要特别注意的是,不是所有的索引列都会有效。

例如:

a)在where 子句中使用了 “!=”这样索引也是无效的,

b)除此之外还有使用“||"字符拼接,也会导致索引失效。

c) 还有相同索引列也不能用来比较,也会导致索引失效。

d) IS NULL 和 IS NOT NULL也会导致索引失效。

e) 函数的使用,也会导致索引失效

5)在经常用在连接上的列上创建索引,可以加快连接的速度,这些列主要是一些外键。

6)在主键列上创建索引,可以强制该列的唯一性并且组织表中的数据的排列结构。

7)对于列值经常修改变动的列,不适合创建索引,因为增加索引会降低修改性能,同样,增加修改性能会降低索引性能。

8)对于只有很少数据值的列也不应该增加索引,因为结果集的数据行占据了表中数据行的很大比例,增加索引并不能增加检索速度。

好了,今天就总结到这里了,也都是一些很常用的知识,希望对于入手oracle的朋友们有些帮助。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
什么是oracle asm什么是oracle asmApr 18, 2022 pm 04:16 PM

oracle asm指的是“自动存储管理”,是一种卷管理器,可自动管理磁盘组并提供有效的数据冗余功能;它是做为单独的Oracle实例实施和部署。asm的优势:1、配置简单、可最大化推动数据库合并的存储资源利用;2、支持BIGFILE文件等。

oracle怎么查询所有索引oracle怎么查询所有索引May 13, 2022 pm 05:23 PM

方法:1、利用“select*from user_indexes where table_name=表名”语句查询表中索引;2、利用“select*from all_indexes where table_name=表名”语句查询所有索引。

Oracle怎么查询端口号Oracle怎么查询端口号May 13, 2022 am 10:10 AM

在Oracle中,可利用lsnrctl命令查询端口号,该命令是Oracle的监听命令;在启动、关闭或重启oracle监听器之前可使用该命令检查oracle监听器的状态,语法为“lsnrctl status”,结果PORT后的内容就是端口号。

oracle全角怎么转半角oracle全角怎么转半角May 13, 2022 pm 03:21 PM

在oracle中,可以利用“TO_SINGLE_BYTE(String)”将全角转换为半角;“TO_SINGLE_BYTE”函数可以将参数中所有多字节字符都替换为等价的单字节字符,只有当数据库字符集同时包含多字节和单字节字符的时候有效。

oracle怎么删除sequenceoracle怎么删除sequenceMay 13, 2022 pm 03:35 PM

在oracle中,可以利用“drop sequence sequence名”来删除sequence;sequence是自动增加数字序列的意思,也就是序列号,序列号自动增加不能重置,因此需要利用drop sequence语句来删除序列。

oracle怎么查询数据类型oracle怎么查询数据类型May 13, 2022 pm 04:19 PM

在oracle中,可以利用“select ... From all_tab_columns where table_name=upper('表名') AND owner=upper('数据库登录用户名');”语句查询数据库表的数据类型。

oracle查询怎么不区分大小写oracle查询怎么不区分大小写May 10, 2022 pm 05:45 PM

方法:1、利用“LOWER(字段值)”将字段转为小写,或者利用“UPPER(字段值)”将字段转为大写;2、利用“REGEXP_LIKE(字符串,正则表达式,'i')”,当参数设置为“i”时,说明进行匹配不区分大小写。

Oracle怎么修改sessionOracle怎么修改sessionMay 13, 2022 pm 05:06 PM

方法:1、利用“alter system set sessions=修改后的数值 scope=spfile”语句修改session参数;2、修改参数之后利用“shutdown immediate – startup”语句重启服务器即可生效。

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment