Oracle基础

DDL 语句（数据定义语言）Create、Alter、 Drop、Truncate

DML 语句（数据操作语言）Insert、Update、 Delete、Merge

DCL 语句（数据控制语言）Grant、Revoke

事务控制语句 Commit 、Rollback、Savepoint

 

一、DDL(改变表结构)

1、创建表

Create table student(
Sid number(10),
Sname varchar2(10)
) tablespace tt;

以上 tablespace 不是必须的。默认不写，则创建在登录的用户所在的表空间上

2、使用子查询创建表

create table myemp as select * from emp;
create table myemp as select * from emp where deptno=10;
create table myemp as select * from emp 1=2;

3、添加字段

Alter table student add age number(5);

4、修改字段

Alter table student modify age number(10);

 

5、删除字段

Alter table student drop column age;

6、清空表中数据

Truncate table student;

正常情况下删除数据，如果发现删除错了，则可以通过 rollback 回滚。如果使用了截断表，

则表示所有的数据不可恢复了.所以速度很快（更详细的说明可查看 Oracle 体系结构）

7、删除表

Drop table student;

8、重命名表

alter table table2 rename column result to result2;

 

二、DML(改变数据结构）

1、insert 语句

直接插入数据　　insert into  表名（对应的字段名） values（，，...）;

表间数据拷贝   insert into dept1(id, name) select deptno, dname from dept

2、update 语句

将编号为 7779 用户的工作换成编号为 7566 的雇员的工作和所属上级。
UPDATE myemp SET(job,mgr) = (SELECT job,mgr FROM myemp WHERE
empno=7566) WHERE empno=7779 ;
如果子查询中返回的是空，则目标字段也更新成 NULL.

3、delete 语句

Delete from emp;

.4、merge 语句

create table test1(eid number(10), name varchar2(20),birth date,salary number(8,2));
insert into test1 values (1001, '张三', '20-5 月-70', 2300);
insert into test1 values (1002, '李四', '16-4 月-73', 6600);
select * from test1;
create table test2(eid number(10), name varchar2(20),birth date,salary number(8,2));
select * from test2;

merge into test2
using test1
on(test1.eid = test2.eid )
when matched then
update set name = test1.name, birth = test1.birth, salary = test1.salary
when not matched then
insert (eid, name, birth, salary) values(test1.eid, test1.name, test1.birth, test1.salary);

select * from test2;

 

三、约束

PRIMARY KEY：主键约束

· 不能重复，不能为空
· 例如：身份证号不能为空。
现在假设 pid 字段不能为空，且不能重复。
CREATE TABLE person
(
pid NUMBER PRIMARY KEY not null,
name VARCHAR(30)NOT NULL
) ;
-- 插入数据
INSERT INTO person(pid,name) VALUES (11,'张三');
-- 主键重复了
INSERT INTO person(pid,name) VALUES (11,'李四');

 

Foreign Key：外键

例如：有以下一种情况：
· 一个人有很多本书：
|- Person 表
|- Book 表：而且 book 中的每一条记录表示一本书的信息，一本书的信息属
于一个人
CREATE TABLE book
(
bid NUMBER PRIMARY KEY NOT NULL ,
name VARCHAR(50) ,
-- 书应该属于一个人
pid NUMBER
) ;

如果使用了以上的表直接创建，则插入下面的记录有效：

INSERT INTO book(bid,name,pid) VALUES(1001,'JAVA',12) ;

以上的代码没有任何错误，但是没有任何意义，因为一本书应该属于一个人，

所以在此处的 pid 的取值应该与 person 表中的 pid 一致。

此时就需要外键的支持。修改 book 的表结构

DROP TABLE book ;
CREATE TABLE book
(
bid NUMBER PRIMARY KEY NOT NULL ,
name VARCHAR(50) ,
-- 书应该属于一个人
pid NUMBER REFERENCES person(pid) ON DELETE CASCADE
-- 建立约束：book_pid_fk，与 person 中的 pid 为主-外键关系
--CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
) ;

INSERT INTO book(bid,name,pid) VALUES(1001,'JAVA',12) ;

 

CHECK：条件约束，插入的数据必须满足某些条件

例如：人员有年龄，年龄的取值只能是 0~150 岁之间

CREATE TABLE person
(
pid NUMBER PRIMARY KEY NOT NULL ,
name VARCHAR(30)NOT NULL ,
tel VARCHAR(50)NOT NULL UNIQUE ,
age NUMBER CHECK(age BETWEEN 0 AND 150)
) ;
-- 插入数据
INSERT INTO person(pid,name,tel,age) VALUES (11,'张三','1234567',30);
-- 年龄的输入错误
INSERT INTO person(pid,name,tel,age) VALUES (12,'李四','2345678',-100);

 

ON DELETE CASCADE ：级联删除

　　如果假设一个人的人员信息没有了，那么此人所拥有的书还应该存在吗？

最好，如果 person 中的一条数据没了，则对应在 book 中的数据也应该同时

消失。

　　在之前的结构上执行 delete 语句，删除 person 表中的一条记录：

DELETE FROM person WHERE pid=11;

　　提示不能删除的错误：因为 book 中存在了此项的关联，如果 person 表中的

一条数据删除了，则肯定会直接影响到 book 表中数据的完整性，所以不让删

除。

　　如果非要删除，则应该先删除 book 表中的对应数据，之后再删除 person 表

中的对应数据。

　　此时如果想完成删除 person 表的数据同时自动删除掉 book 表的数据操作，

则必须使用级联删除。

在建立外键的时候必须指定级联删除（ON DELETE CASCADE）。

CREATE TABLE book
(
bid NUMBER PRIMARY KEY NOT NULL ,
name VARCHAR(50) ,
-- 书应该属于一个人
pid NUMBER ,
-- 建立约束：book_pid_fk，与 person 中的 pid 为主-外键关系
CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE 
CASCADE
) ;

 

如果表已经建成，我们后期想要对其添加一些约束，可通过 alter 命令添加：

1、为两个表添加主键：person 表 pid 为主键；book 表 bid 为主键：

ALTER TABLE person ADD CONSTRAINT person_pid_pk PRIMARY KEY(pid) ;

ALTER TABLE book ADD CONSTRAINT book_bid_pk PRIMARY KEY(bid) ;

 

2、为 person 表中的 tel 添加唯一约束：

ALTER TABLE person ADD CONSTRAINT person_tel_uk UNIQUE(tel) ;

 

3、为 person 表中的 age 添加检查约束：

ALTER TABLE person ADD CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND
150) ;

 

4、为 book 表中的 pid 添加与 person 的主-外键约束，要求带级联删除

ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY (pid)
REFERENCES person(pid) ON DELETE CASCADE ;

 

删除约束：
ALTER TABLE book DROP CONSTRAINT person_book_pid_fk ;
alter table student drop unique(tel);
启用约束
ALTER TABLE book enable CONSTRAINT person_book_pid_fk ;
禁用约束
ALTER TABLE book disable CONSTRAINT person_book_pid_fk ;

 

四、视图 view

可理解为：就是将SQL语句查询的结果集生成一张可视化的表，之后可以直接用

　　视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。

 

创建视图：
CREATE OR REPLACE VIEW 视图名字(字段) AS 子查询；

# 建立一个只包含 20 部门雇员信息的视图（雇员的编号、姓名、工资）；

　CREATE OR REPLACE VIEW empv20 (empno,ename,sal) AS SELECT empno,ename,sal FROM emp

WHERE deptno=20 ;

　查看视图：

　select * from 视图名；

这样创建出来的视图，如果原表数据发生变动，而视图中恰好包含这些数据，那么在次查询该视图时，视图中的数据也会随着更新

 

 序列 sequence

作用是生成一系列数字。序列常用于为某张表的主键字段提供值使用。

CREATE SEQUENCE [schema.]sequence_name
[ START WITH i ] [ INCREMENT BY j ]
[ MAXVALUE m | NOMAXVALUE ]
[ MINVALUE n | NOMINVALUE ]
[ CYCLE | NOCYCLE ][ CACHE p | NOCACHE ]

其中：
sequence_name是序列名，将创建在schema方案下
序列的第一个序列值是i，步进是j
如果j是正数，表示递增，如果是负数，表示递减
序列可生成的最大值是m，最小值是n
如果没有设置任何可选参数，序列的第一个值是1，步进是1
CYCLE表示在递增至最大值或递减至最小值之后是否继续生成序列号，若是递减并有最大值，从最大值开始。
若是递增有最小值，从最小值开始。若没有，从START WITH 指定的值开始。默认是NOCYCLE
CACHE用来指定先预取p个数据在缓存中，以提高序列值的生成效率，默认是20

 

创建一个myseq序列，从1开始，每次递增1
Create sequence myseq
Start with 1 
Increment by 1 
Order
cache 20
Nocycle;

 

 五、游标 cursor

　　游标是一种 PL/SQL 控制结构；可以对 SQL 语句的处理进行显示控制，便于对表的行数据逐条进行处理。游标并不是一个数据库对象，只是存留在内存中

 　　

操作步骤：

• 声明游标

• 打开游标

• 取出结果，此时的结果取出的是一行数据

• 关闭游标

到底那种类型可以把一行的数据都装进来

• 此时使用 ROWTYPE 类型，此类型表示可以把一行的数据都装进来。

 游标分为静态游标和动态游标

 1、静态游标

declare
    cursor  stus  is  select * from student; -- 定义一个静态游标
    stu student%rowtype; -- 定义一个变量，是行类型，用来接收返回值
begin
    for stu in  stus loop  -- for循环的开启游标方式
        dbms_output.put_line(stu.sno||':'||stu.sname);
    end loop;
end;

以上其实游标就是student整个表，而有时我们无法确定一个游标，需要传递参数进来，此时就不能用静态游标，而需要自定义一个动态游标

2、动态游标

步骤：

　　1.定义游标类型

　　2.用你定义好的类型去定义一个游标变量

例如：查询某年龄范围内的学生的学号和姓名，年龄范围可输入不同参数进来

　　两种方法：

　　方法一：用整行去接收

declare
    type my_type is ref cursor;-- 定义一个游标类型
    stus my_type; -- 定义一个游标变量
    str varchar(200);
    myrow student%rowtype;-- 用整行去接收返回值
begin
    str:='select * from student where sage between :x and :y';
    　　　　　　　　　　　　　　　　　　　　　　  -- :x 和:y 在这里是占位符　　　　　　　
    open stus for str using 23,28;-- 开启游标并传入参数
    fetch stus into myrow; -- 将获取到的每一行返回到定义好的myrow中
    while(stus%found)loop -- 循环判断游标是否取到
        dbms_output.put_line(myrow.sno||':'||myrow.sname);
        fetch stus into myrow;
    end loop;
    close stus;-- 关闭游标
end;

　　方法二：定义变量去接收

declare
    type mytype is ref cursor;
    stus mytype;
    str varchar(200);
    myno varcahr(20);-- 定义接收sno的变量
    myname varchar(20);-- 定义接收sname的变量
begin
    str:='select sno,sname from student where sage between :x and :y';
    
    open stus for str using 23,28;
    fetch stus into myno,myname;-- 将返回值传入定义好的变量中
    while(stus%found)loop
        dbms_output.put_line(myno||':'||myname);
        fetch stus into myno,myname;
  end loop;
  close stus;
end;

可见：for循环中的游标无需手动开启关闭，而while循环需要

　　这样一来，每次我们都需要去自定义游标类型，再用该类型去定义一个游标，太繁琐，在此引入一个系统游标，相当于是一个系统自定义好的游标，直接拿来用即可。拿来吧你！

declare
  stus sys_refcursor;-- 系统游标
  str varchar(200);-- 存放SQL字符串
  myrow student%rowtype;-- 一行一行的去接收
   
begin
  str:='select * from student where sage between :x and :y';

  -- 将using后的值传给str语句中的占位符，并执行语句，并把执行结果放入前面的游标中
  open stus for str using 23,25; 
  fetch stus into myrow;
  while (stus%found) loop
        dbms_output.put_line(myrow.sno||':'||myrow.sname);
        fetch stus into myrow;
  end loop;
  close stus;
end;

 

通过一个题目细分下用法：使用游标 和 循环来显示所有部门的的地理位置

方法一：while循环，静态游标
declare
  cursor dept2 is select * from dept;
  myrow dept%rowtype;
begin
  open dept2;
  fetch dept2 into myrow;
  while(dept2%found)loop
      dbms_output.put_line(myrow.deptno||':'||myrow.loc);
      fetch dept2 into myrow;
  end loop;
　close dept2;
end;

方法二：while循环，动态系统游标
declare
   dept1 sys_refcursor;
   myrow dept%rowtype;
   str varchar(200);
begin
  str:='select * from dept';
  open dept1 for str;
  fetch dept1 into myrow;
  while(dept1%found)loop
        dbms_output.put_line(myrow.deptno||':'||myrow.loc);
        fetch dept1 into myrow;
  end loop;
end;

方法三：for循环，静态游标
declare
  cursor dept2 is select * from dept;
  myrow dept%rowtype;
begin
  for myrow in dept2 loop
      dbms_output.put_line(myrow.deptno||':'||myrow.loc);
  end loop;
end;

 

六、自定义函数 function

 自定义函数像内置函数一样返回标量值，也可以将结果集用表格变量返回。
sql函数必须有返回值。

自定义函数可分为标量函数和表格值函数。

标量函数：返回一个标量值。
表值函数{内联表值函数、多表值函数}：返回行集（即返回多个值）

标量函数和表格值函数的区别在于 返回是标量值(单个数字或者单个数据)，还是表格值(多个数据)

创建步骤：

create or replace Function 函数名字（参数  参数类型）return  返回值类型
as
    定义变量来接收返回值
begin
    执行语句
end；

例如：

-- 定义一个函数：传参（雇员的编号）返回（查询到的雇员的年薪）
CREATE OR REPLACE FUNCTION myfunc(eno emp.empno%TYPE) RETURN NUMBER
AS
　　rsal NUMBER ;
BEGIN
　　SELECT (sal+nvl(comm,0))*12 INTO rsal FROM emp WHERE empno=eno ;
　　RETURN rsal ;
END ;

可直接调用该函数

SELECT myfun(7369) FROM dual ;