oracle数据库——知识点总结（加示例）

新入oracle数据库，把目前学到的知识点记录下来，可能都比较基础，但还是比较全的，里面的示例都是自己在PL/SQL中跑过的，如果有错误，还望各位大侠指出哈。

创建用户

1.创建用户（使用管理员身份创建，即用system进行登录）

create user rick identified by ric account unlock;
--创建一个用户名是rick的用户，密码是ric，用户状态未锁定

2.上诉创建后，如果直接用设置的用户名和密码进行登录，则会提示下面的错误


没有权限.png

3.可以理解为用户没有会话权限，无法进行数据库登录。则，接下来给予用户rick基本的connect权限

grant connect to rick; --将connect权限给予rick

4.这次rick可以作为normal用户登录（如果选择dba身份，则需要更高的权限）
5.重新使用system登录数据库，更改rick的信息

alter user rick account lock; --将rick的状态设置为锁定

6.再次以rick来登录，则会提示下面错误（一开始在创建用户时如果不设置状态为unlock，则系统会默认为lock状态）


locked.png

创建表及约束

create table infos
(
  stuid varchar2(7) not null,
  age number(2) not null
)
alter table infos add constraint infos_check --给infos表加一个约束，限制age在20到60之间
check(age>20 and age<60)

根据结果集创建表

create table infos2 as select * from infos;  --会把数据一起复制到新表中

create table infos3 as select * from infos where 1=2;
 --后面加了一个where条件，这个条件不成立，则只会复制表结构，不包括数据

插入数据时，如果表中字段是date类型，需要先使用to_date('2012-12-29','YYYY-MM-DD')将数据转换成date类型

当两个表的结构全部相同时，可以直接插入结果集

insert into infos3 select * from infos;

清空表中的数据（不可恢复）

turncate table infos;

字符串拼接

select (ename||' is a '||job) as "employee detail" from emp; 
--括号内的内容会在一列中显示出来

消除重复项

select distinct deptno from emp;

in操作查询指定列表的值

select ename,job from emp where job in ('salesman','president','analyst');
--只要job是salesman、president、analyst中任意一个即满足条件

between……and操作查询范围内的数据

select ename,job from emp where sal between 1000 and 2000; --包括边界

like模糊查询

  • % 表示零个或多个任意字符
  • _ 表示一个任意字符
  • 如果查询条件中有特殊字符，则使用[ ]括起来

集合运算

  • intersect （交集） 返回两个查询共有的记录
  • union all（并集） 返回各个查询的所有记录，包括重复记录
  • union（并集） 返回各个查询的所有记录，不包括重复记录
  • minus（补集） 返回第一个查询的记录减去第二个查询的记录后剩余的记录

使用union实现多条数据插入

insert into dept
select 50,'公关部','武汉' from dual
union
select 60,'研发部','重庆' from dual
union
select 70,'培训部','四川' from dual

其中dual是一个oracle自带的特殊的表，使用只是为了符合select的查询结构，它只会返回一行记录

连接查询

  • 内连接：join……on 两个表中只有满足条件的才会被查询出来
  • 左外连接：left join ……on 左边的表不受限制，右边的表需要满足条件
  • 右外连接：right join……on 右边的表不受限制，左边的表需要满足条件
  • 全外连接：full join……on 左右两张表都不受限制

ANY子查询（只要满足ANY中的一个即可）

select ename,job from emp where sal<any(select sal from emp where job='salesman'); 
--查询出比任意一个销售的工资低的员工姓名和工作

ALL子查询（需要满足all中的所有）

select ename,job from emp where sal>all(select sal from emp where job='salesman');
 --查询出比所有销售工资都高的员工的姓名和工作

伪列

  • ROWID：表中每一行在数据文件中都有一个物理地址，ROWID就是返回这个物理地址
  • ROWNUM：表中每行数据前面的序号
select t.* from (select ename,job sal from emp order by sal desc) t where rownum<5;
--查出工资排行前四的员工信息

select t.* from (select ename,job,rownum r from emp where rownum<10) t where r>5; 
--查询第6到第9条数据
数学函数.png

日期函数

  • ADD_MONTHS(d,n) 在日期d的基础上加上n个月，返回新日期
  • LAST_DAY(d) 返回指定日期当月的最后一天
  • ROUND(d,fmt) 返回一个以fmt为格式的四舍五入的日期值
  • EXTRACT(fmt FROM d) 从日期d中提取fmt部分（YEAR、MONTH……）
  • TO_CHAR(d,'YYYY-MM-DD HH24:MI:SS') 按照右边的格式转换日期成字符串，HH24表示是采用24小时制
  • TO_DATE(x, 'YYYY-MM-DD') 按照右边的格式把字符串x转换成对应的date类型

其他

  • NVL(x,value) 如果x为空，则返回value，否则正常返回x
  • NVL2(x,value1,value2) 如果x非空，返回value1，否则返回value2
  • SUM()统计某一列中的数据的和，COUNT()统计有多少条数据（即行）

同义词

create synonym myemp for scott.emp; --给scott用户的emp表创建别名myemp

oracle可以为表、视图、序列、过程、函数、程序包等指定别名，比如上面的，当当前用户拥有权限时，访问emp表，可以直接访问myemp。而不需要使用scott.emp

创建视图

create view view_name as select ……;  --将查询到的结果保存成视图

create view empdetail
as
select empno,ename,emp.deptnp,dname
from emp join dept on emp.deptno=dept.deptno;

上图是将emp表与dept表联合查询后的结果保存成视图，视图相当于一张新的表（虚表），可以直接来用

创建索引

create index ename_index on emp(ename);  --为emp表的ename创建索引

创建表空间 (目前不是很懂表空间)

create tablespace myspace
datafile 'd:/a.ora' size 10M;
         'd:/b.ora' size 5M;
extent management local  --指出表空间类型是：本地管理表空间
uniform size 1M;  --指定每个分区的统一大小

扩充表空间

alter tablespace myspace
add datafile 'd:/c.ora' size 10M;

为表和索引指定表空间，只需要在其创建语句后面加上tablespace space_name即可，这种情况的表空间是不能修改的

PL/SQL块

declare
  --声明部分，如常量和变量
begin
  --执行部分，这部分不能少
exception
  --异常处理部分
end;

如下例子

declare
   sname varchar2(20) default 'jerry'; --声明变量sname并设置初始值
begin
   select ename into sname from emp where empno=9527; --将查询到的值赋给sname变量
   dbms_output.put_line(sname);  --输出sname
end;

还可以声明宿主变量，该变量属于全局变量，类似于web中的session，只要用户没退出，这个变量都可以使用

var emp_name varchar(30);
begin
select ename into :emp_name from emp where empno=9527;
end;

!! 注意在使用这种宿主变量时，需要在变量名前加：

属性数据类型

  • %ROWTYPE 引用表中的一行作为数据类型
declare
   myemp emp%rowtype; --创建一个以emp表中一行作为数据类型的变量
begin
   select * into myemp from emp where empno=9527; --赋值
   dbms_output.put_line(myemp.ename); --ename相当于一个对象,可以使用.来调用它里面的属性值
end;
  • %TYPE 引用表中的某列的属性作为数据类型
declare
   cursal emp.sal%TYPE; --引用emp表中的sal列的类型作为cursal的数据类型
   mysal number(4):=3000; --声明一个变量
   totalsal mysal%TYPE;  --引用变量mysal的数据类型作为当前变量的数据类型
begin
   select sal into cursal from emp where empno=9527;
   total:=cursal+mysal;
   dbms_output.put_line(totalsal);
end;

IF ELSE语句

declare
   newsal emp.sal%TYPE; --
begin
   select sal into newsal from emp where ename='JAMES';
   if newsal>2000 then
      update emp set comm=800 where ename='JAMES';
   else
      update emp set comm=500 where ename='JAMES';
   end if;
end;

IF THEN ……ELSIF THEN

declare
   newsal emp.sal%TYPE;
begin
   select sal into new sal from emp where ename='JAMES';
   if newsal>1500 then
      update emp set comm=1000 where ename='JAMES';
   elsif newsal>1000 then
      update emp set comm=800 where ename='JAMES';
   else
      update emp set comm=500 where ename='JAMES';
   end if;
end;

!!! 注意是elsif ， 不是elseif

CASE语句

declare
  v_grade char(1):=upper('&p_grade'); --&表示弹出输入框，接收用户的输入值
begin
  case v_grade
     when 'A' then
        dbms_out.put_line('Excellent');
     when 'B' then
        dbms_out.put_line('Very Good');
     when 'C' then
        dbms_out.put_line('Good');
     else
        dbms_out.put_line('no such grade');
     end case;
end;

还可以把case的值赋给变量，如下

declare
   v_grade char(1):=upper('&grade');
   p_grade varchar(20);
begin
   p_grade:=
   case v_grade
       when 'A' then
           'Excellent'
       when 'B' then
           'Very Good'
       when 'C' then
           'Good'
       else 
           'no such grade'
   end case;
   dbms_output.put_line(v_grade);
end;

LOOP循环

declare
   counter number(3):=0;
   sumResult number:=0;
begin
   loop
     counter:=counter+1;
     sumResult:=sumResult+counter;
     if counter>=100 then
        exit;  --退出loop循环
     end if;
   end loop;
end;

WHILE循环

declare
   counter number(3):=0;
   sunResult number:=0;
begin
   while counter<100 loop
      counter:=counter+1;
      sunResult:=sumResult+counter;
   end loop;
end;

FOR循环

declare 
   counter number(3):=0;
   sumResult number:=0;
begin
   for counter in 1..100 loop  -- 1..100表示1到100这个范围
       sumResult:=sumResult+counter;
   end loop;
end;

GOTO语句

declare
   sumsal emp.sal%TYPE;
begin
   select sum(sal) into sumsal from emp;
   if sumsal>20000 then
      goto first_label;  --前往第一个标签处
   else
      goto second_label;  --前往第二个标签处
   end if;
   <<first_label>> --<<>>是goto可识别的标志
   dbms_output.put_line(sumsal);
   <<second_label>>
   null;  --无操作,语句接着往下执行
end;

动态SQL

declare
   sql_stmt varchar2(200); --存放SQL语句
   emp_id number(4):=7566; --实参
   dept_id number(2):=90;  --实参
   dept_name varchar2(14):='PERSOLLNNEL'; --实参
   location varchar2(13):='DALLAS'; --实参
   emp_rec emp%ROWTYPE; --一行数据
begin
   sql_stmt:='insert into dept values(:1,:2,:3)'; --里面的:1,:2,:3相当于形参
   execute immediate sql_stmt using dept_id,dept_name,location; --using后面的相当于实参
   sql_stmt:='select * from emp where empno=:id'; --:id相当于形参
   execute immediate sql_stmt into emp_rec using emp_id; --把执行sql_stmt的结果赋给emp_rec,using后面的相当于实参
end;

异常处理

declare
   newsal emp.sal%TYPE;
begin
   select sal into newsal from emp;
exception
   when TOO_MANY_ROWS then  --TOO_MANY_ROWS是常用异常名
       dbms_output.put_line('返回的记录太多');
   when OTHERS then
       dbms_output.put_line('未知异常');
end;

自定义异常

declare
   cursal emp.sal%TYPE;
   myexp exception; --定义一个名为myexp的异常
begin
   select sal into cursal from emp where ename='JAMES';
   if cursal<5000 then
     raise myexp;  --raise用于引发myexp这个异常
   end if;
exception
   when NO_DATA_FOUND then
     dbms_output.put_line('no resource found');
   when MYEXP then
     dbms_output.put_line('sal is less 5000');
end;

创建存储过程

create procedure my_procedure(eno in number,salary out number)
 --in表示是传入的参数，out表示输出的参数，这里声明的参数不写范围
as
begin
  select sal into salary from emp where empno=eno;
end;

执行上述存储过程

declare
  my_salary number; --声明一个变量用于接收输出参数
  my_eno number:=7369; --作为传入的参数
begin 
  my_procedure(7369,my_salary); --执行存储过程
  dbms_output.put_line(my_salary);
end;

创建触发器

create trigger my_trigger 
after insert or update on dept for each row 
--after表示在操作之后触发，on dept表示这个触发器是绑定在dept表上的
declare  --不需要变量的话，可以不要declare
begin
  if inserting then  --如果执行的是插入操作，则执行下面语句
     dbms_output.put_line('已经插入');
  elsif updating then  --如果执行的是更新操作，则执行下面语句
     dbms_output.put_line('已经更新');
  end if;
end;

创建游标（游标可以对结果集进行一行行处理）

  • 隐式游标（不用创建，由系统自动控制开启和关闭）
begin
   update emp set sal=sal+100 where empno=7876; --增删改查时系统会自动打开隐式游标
   if sql%found then  --如果有游标存在，就说明之前的操作成功
      dbms_output.put_line('修改成功');
   else 
      dbms_output.put_line('修改失败');
   end if;
end;
  • 显示游标
declare
   cursor emp_cursor is select * from emp; 
--声明一个游标，这个游标时指向emp表的返回集(多行),可以理解为这个地方的游标是一个游标工厂
   my_cursor emp_cursor%rowtype;  --声明一个自己的游标，它指向的是一行数据
begin
   for my_cursor in emp_cursor loop --循环，只要在游标工厂中还有自己的位置就继续
      dbms_output.put_line('job is '||my_cursor.job||' and salary is '||salary);
   end loop;
ennd;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,242评论 5赞 459
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 81,769评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 141,484评论 0赞 319
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,133评论 1赞 263
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,007评论 4赞 355
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,080评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,496评论 3赞 381
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,190评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,464评论 1赞 290
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,549评论 2赞 309
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,330评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,205评论 3赞 312
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,567评论 3赞 298
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,889评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,160评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,475评论 2赞 341
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,650评论 2赞 335

推荐阅读更多精彩内容

  • Oracle数据库学习一
    1.简介 数据存储有哪些方式？电子表格，纸质文件，数据库。 那么究竟什么是关系型数据库？ 目前对数据库的分类主要是...
    乔震阅读 1,680评论 0赞 2
  • Oracle数据库学习二
    5.多表查询 多表查询 目的：从多张表获取数据 前提：进行连接的多张表中有共同的列 等连接 通过两个表具有相同意义...
    乔震阅读 1,171评论 0赞 0
  • Oracle学习第三天
    1.PLSQL入门 Oracle数据库对SQL进行了扩展,然后加入了一些编程语言的特点,可以对SQL的执行过程进行...
    随手点灯阅读 593评论 0赞 8
  • 为什么你不穿运动内衣就去健身！！！
    以下文章就是警醒你的…转载自知乎！ 运动的同时必须要对胸器做好十二分的爱好。 1.为什么要选专业的sport br...
    姚小白少校阅读 1,337评论 0赞 0
  • 妻子出轨之后
    晚饭的时候，他告诉她明天四点多要去西北出差。少则一星期，多则半个月。 她说：“你咋不早说，好让我准备准备。” “有...
    河洛梦话阅读 3,422评论 13赞 22