跳至主要內容

Oracle数据库

TAI数据库oracleSQLPLSQL游标存储过程触发器约 1717 字大约 6 分钟

哔哩哔哩Oracle视频课程

1.SQL语句


// 创建表空间
CREATE TABLESPACE hy DATAFILE 'hy.dbf' SIZE 10m;

// 创建用户
CREATE user hy identified by "123"  default tablespace hy temporary tablespace TEMP profile DEFAULT;

// 更改密码
alter user hy identified by "123456"

// 给用户加上DBA权限
grant dba to hy with admin option;

// 建表
CREATE TABLE Employee
(   ID VARCHAR(255) NOT NULL primary key,
　　 NAME VARCHAR(20),
　　 AGE Integer,
　　 SEX CHAR(1)
) 

// 插入数据
insert into Employee values('0001','Andy',20,'M');

// 查询数据
select * from Employee where age>22

// 更新数据
UPDATE Employee SET name = 'Felex' WHERE age = 25

// 删除数据
delete from Employee where age<21

Select语句基本结构
Select [Distinct] {column1,column2,…}
From tablename 
Where {conditions}
Group by {conditions}
Order by {expressions} [ASC/DESC]

//求行总和函数SUM
select sum(AMOUNT) from CHECKTABLE

//求平均值函数AVG
select  round(avg(AMOUNT)) from CHECKTABLE

//求最大值函数Max
select  max(AMOUNT) from CHECKTABLE

//求最小值函数Min
select  min(AMOUNT) from CHECKTABLE

//分组求和
Select PAYEE,sum(amount) from CHECKTABLE Group by payee

//使用Having子句进行分组过滤
select PAYEE, 
        avg(AMOUNT) 
 from CHECKTABLE 
 group by PAYEE 
 having avg(AMOUNT)>200

//左外连接
select n.id, n.name, t.name
from nation n, tank t
where n.id=t.nid(+);

//右外连接
select n.id, n.name, t.name
from nation n, tank t
where n.id(+)=t.nid;

//左连接
select *
from nation n left join tank t
on n.id = t.id;

//右连接
select *
from nation n right join tank t
on n.id = t.id;

//内连接
select *
from nation n inner join tank t
on n.id=t.nid

//使用union得到并集
 select NAME, SCORE from CALSS07
union
 select * from CALSS05
 
 //使用minus得到差集
  select NAME, SCORE from CALSS35 
minus
 select NAME, SCORE from CALSS07

//使用intersact得到交集
 select NAME, SCORE from CALSS35 
intersect
 select NAME, SCORE from CALSS07

//从一个表向另外的表中复制记录
insert into calss05(id,sc) select name,score from calss07

//找出学生‘牛顿’选择的所有科目
select
   t01.id,
   t01.name,
   subject.name 
from
   subject,
         (
         select 
             student.id,
             student.name,
             connector.subjectid as sid
         from 
             student,connector
         where 
             student.name='牛顿' and
             student.id=connector.studentid
         ) t01
where
    subject.id=t01.sid

//Case表达式
select name,score,
       case when score>90 then 'A'
            when score>80 then 'B'
            when score>70 then 'C'
            when score>60 then 'D'
       else 'E'
       end as grade
from singerscore

//对结果集进行转置
select sum(case when score>=60 then 1 else 0 end) as passed,
       sum(case when score<60 then 1 else 0 end) as failed
from singerscore

2.Mysql和Oracle区别


区别一：

mysql使用limit关键字实现分页
oracle使用rownum关键字使用分页

rownum伪列：在查询数据的时候会自动分配一个编号(1,2,3,4)
select name, rownum from t1;

rownum在项目中的具体应用：
select * from (select t1.*, rownum r from t1) t2 where r >= 1 and r <= 3;

区别二：

mysql数据库：并集
oracle数据库：并集、交集、差集

交集：intersect
select * from t1 where name in ('张三','李四')
intersect
select * from t1 where name in ('张三','王五')

差集：
select * from t1 where name in ('张三','李四')
minus
select * from t1 where name in ('张三','王五')

并集
select * from t1 where name in ('张三','李四')
union
select * from t1 where name in ('张三','王五')

select * from t1 where name in ('张三','李四')
union all
select * from t1 where name in ('张三','王五')

区别三：

左连接
select com.name, emp.name from com left join emp on com.id = emp.comid;
select com.name, emp.name from com, emp where com.id = emp.comid(+);

左连接
select com.name, emp.name from com right join emp on com.id = emp.comid;
select com.name, emp.name from com, emp where com.id(+) = emp.comid;

全连接
select com.name, emp.name from com full join emp on com.id = emp.comid;

3.PLSQL(分支和循环)


--PLSQL编程语言：Oracle数据库对SQL语句扩展。实现更复杂的功能或计算。

--(1)PLSQL基本语法
declare
a number(2);
begin
a := 3;
dbms_output.put_line(a);
end;

declare
a number(2) := 5;
begin
dbms_output.put_line(a);
end;


--(2)if分支语句
declare
a number(2) := 5;
b number(2) := 3;
begin
  if (a > b) then
     dbms_output.put_line(a);
  else
     dbms_output.put_line(b);
  end if;
end;


-- (3)case分支语句
declare
a number(2) := 9;
b number(2) := 5;
begin
  case when a>b then
    dbms_output.put_line(a);
  else
    dbms_output.put_line(b);
  end case;
end;


--(4)while循环
declare
i number(10) := 1;
begin
  while i <= 10 loop
    dbms_output.put_line(i);
    i := i + 1;
  end loop;
end;


--(5)for循环
declare
i number(10);
begin
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

declare
i number(10);
begin
  for i in reverse  1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

4.PLSQL(变量和游标)


--(6)基础变量

create table t(
id integer, 
name varchar2(10), 
age number(2)
);
insert into t values(1,'张三',30);
insert into t values(2,'李四',40);
select * from t;

--输出id=1的名字
declare
a varchar2(10);
begin
  select name into a from t where id = 1;
  dbms_output.put_line(a);
end;


--(7)自定义复合变量、记录类型、存储表中的记录
declare
type myrecord is record(
a t.name%type,
b t.age%type
);
x myrecord;
begin
  select name, age into x from t where id = 1;
  dbms_output.put_line(x.a);
  dbms_output.put_line(x.b);
end;

--(8)存储整条记录、行类型
declare
a t%rowtype;
begin
  select * into a from t where id = 1;
  dbms_output.put_line(a.id || a.name || a.age);
end;

--(9)游标、用来遍历表中的记录、理解为一个指针

->   1张三30
     2李四40
     
declare
a t%rowtype;
cursor mycur is select * from t;
begin
  open mycur;
  fetch mycur into a;
  while mycur%found loop
    dbms_output.put_line(a.name);
    fetch mycur into a;
  end loop;
  close mycur;
end;

5.PLSQL(存储过程)


我们可以把存储过程看成是一些 SQL 语句的集合，中间加了点逻辑控制语句。
存储过程在业务比较复杂的时候是非常实用的，比如很多时候我们完成一个操作可能需要写一大串 SQL 语句，这时候我们就可以写有一个存储过程，这样也方便了我们下一次的调用。存储过程一旦调试完成通过后就能稳定运行，另外，使用存储过程比单纯 SQL 语句执行要快，因为存储过程是预编译过的。
存储过程在互联网公司应用不多，因为存储过程难以调试和扩展，而且没有移植性，还会消耗数据库资源。阿里巴巴 Java 开发手册里要求禁止使用存储过程。

--(10)存储过程(没有返回结果的存储过程)

--实现添加功能的存储过程
--定义存储过程
create or replace procedure pro1(a in number, b in varchar2, c in number) as
begin
  insert into t values(a,b,c);
end pro1; 
--调用存储过程
begin
  pro1(3,'王五',50);
end;
--查看表
select * from t;

--(11)有返回结果的存储过程
--定义存储过程、传入id、返回名字
create or replace procedure pro2(a in number, b out varchar2) as 
begin
select name into b from t where id = a;
end pro2;
--调用有返回值的存储过程
declare
c t.name%type;
begin
pro2(2, c);
dbms_output.put_line(c);
end;

6.PLSQL(触发器)


--(12)触发器
存储过程：必须调用存储过程才会执行。
触发器：通过事件触发自动执行的。
--日志表案例

create table t_log(u varchar2(255), t varchar2(255), d varchar2(255));
drop table t_log;
select * from t_log;

create or replace trigger t_trigger
after delete or insert or update on t
declare
s varchar2(255);
begin
  if inserting then
    s:='添加操作';
  elsif updating then
    s := '修改操作';
  elsif deleting then
    s := '删除操作';
  end if;
  insert into t_log values(user, s, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end;

select * from t_log;
select * from t;
delete from t_log;

insert into t values(9,'你好',23);
update t set name='abc' where id = 1;
delete from t where id = 2;