Oracle数据库小知识点整理
-- 数据库存储数据
-- 市面上主流的数据库有哪些
-- 甲骨文 oracle mysql
-- ibm db2 金融
-- 微软 sqlserver
--这些是关系型数据库。
--nosql 不仅仅是sql,典型 mongodb.
----------------- ----------------------------------------
--数据库的语言有哪些分类
-- **************************************
/***
1.数据定义语言(ddl),create,alter,drop
2.数据操纵语言(dml),select,update,insert,delete
3.数据事务语言, commit,savepoint,rollback
事务特性:原子性，一致性，持久性，隔离型，
...........后面补充.............
在关系型数据库中数据是以table
(二维数据结构，行和列的形式)
来组织数据
table 就是表来组织数据，这个table的设计的规范是什么?
--三范式 *****************
--第一范式:数据库表中的字段应该最小单位，
是不能够分割(本地化)
--第二范式:数据库表中的一个字段
不能由另外一个字段通过计算得出。
age
birthday
工资
养老保险费用
***/
-------------------------------------------------------
-- 创建表 oracle 列名和列的类型来定义的
--1.数据类型：
--number 数值类型 number(长度),number(10)
--number(长度，小数点位数) number(7,2)
--2.char类型 char(长度)，定长 char(6) 固定长度为6位
--3.varchar2类型 varchar2(20)
--4.date类型 日期类型
--5.int类型，表示一个正整数
-- 大文本类型 blob ,clob
-- 路径
-- 首先我们来创建一张表 命名规范 t_
create table t_students
(
sn number(7) primary key ,--学号 -- 主键（唯一不能重复），主键约束
sname varchar2(20) , --学生姓名
spwd char(6), -- 学生密码
sbirthday date,--出生年月
smoney number(7,2), --账户金额
job varchar2(20), --学生职位
saddress varchar2(20),--学生籍贯
sphone char(11) --学生手机号码
)
--查询表
select * from t_students
-- 插入数据
insert into t_students values(1, '赵蓉','12345',
to_date('1992-12-03','yyyy-mm-dd'),500.65,'学生会委员',
'盐城','13913321089');
commit;
insert into t_students values(2, '秦日霞','123456',
to_date('1992-11-03','yyyy-mm-dd'),1500.65,'学生会委员',
'盐城','13913321085');
commit;
-- 修改表
-- 表增加一个字段
alter table t_students add ssex char(4);
--表字段类型修改
alter table t_students modify saddress varchar2(15)
--删除表的字段
alter table t_students drop column ssex ;
-- 删除整个表
drop table t_students;
select length(s.spwd)
from t_students s where s.sname='赵蓉'
-- 创建表
create table t_employees
(
eid number(6) primary key, -- 员工编号 主键
ename varchar2(20), --员工姓名
epwd char(5) , -- 员工密码
ebirthday date, --出生年月
esalary number(7,2), --工资
ejob varchar2(20), --职位
esex char(4) --性别
)
-- oracle序列机制，自增长机制 sequence
create sequence seq_employees
start with 100 --开始编号
increment by 2 --步长
nocache --没有缓存，立即生成
--查看表结构
desc t_employees
select * from t_employees for update
select * from t_employees where
--数据操作语言（select ,insert,update,delete）dml
--select 操作 ,不需要commit 70%
--insert,update,delete 都需要commit 30%
--查询
select * from t_employees
--插入
-- 插入数据
insert into t_employees values(seq_employees.nextval,
'胡瓜','1234',to_date('1992-12-30','yyyy-mm-dd'),10000.88,
'职工','男');
insert into t_employees values(seq_employees.nextval,
'李欣','11111',to_date('1992/08/30','yyyy/mm/dd'),12000.88,'组长','男');
commit;
--更新数据
update t_employees e set e.epwd='12345' ;
commit;
update t_employees e set e.epwd='00000' where e.ename='李欣';
commit;
--删除数据
delete from t_employees
delete from t_employees e where e.eid=100;
commit;
--约束
--1.主键约束，唯一不能重复，数据唯一性 primary key
--2.非空约束 这个字段的取值不能为空 not null
--3.默认约束 这个字段取值如果不给值，取默认值 default
--4.检查约束 这个字段取值是一定范围内 check(字段取值范围)
--5.外键约束 一个字段取值从属于另外一个的值
drop table t_stus
create table t_stus
(
sid number(3) primary key,
sname varchar2(20) not null
)
insert into t_stus values(seq_stus.nextval,null);
commit;
insert into t_stus values(seq_stus.nextval,'李欣',to_date('1976-12-30','yyyy-mm-dd'));
commit;
insert into t_stus values(seq_stus.nextval,'李欣1',default);
commit;
insert into t_stus values(seq_stus.nextval,'李欣1',default,'男');
commit;
select * from t_stus
--default 是默认，sysdate是oracle默认时间的关键字
alter table t_stus add birthday date default sysdate;
alter table t_stus add sex char(4)
check(sex in('男','女'));
-- 创建一张表，树状菜单，自身关联表 一对多
create table t_menu
(
fid number(4) primary key, --父id
fmenuname varchar2(20) not null, --父菜单名称
cmenuuname varchar2(20) not null,-- 子菜单名称
cid number(4) references t_menu(fid) --子id 外键约束
)
select * from t_menu
-- oracle序列机制 自增长机制
create sequence seq_stus
start with 1
increment by 1
nocache;
--查询序列的当前值 dual是个系统表
select seq_stus.currval from dual;
drop sequence seq_stus
insert into t_stus values(seq_stus.nextval,'李欣');
commit;
select * from t_stus
delete from t_stus
--mysql自增长 auto_increment 从起始编号为1，每次增加1.
--sqlserver自增长 identity(10,1)
-- oracle内置函数
--to_date(参数1，参数2)，把一个字符串类型的日期转换成date类型，
--参数2，按照什么日期格式
--length() 获取结果的长度
--to_char()转换成字符串类型
select to_char(sysdate,'yyyy')
-to_char(e.ebirthday,'yyyy') age
from t_employees e
-- 拼接字符串concat()
-- 输出一段描述"胡瓜的职位是什么"
select concat(concat(e.ename,'职位是:'),e.ejob) 描述
from t_employees e where e.ename='胡瓜';
-- ||连接
select e.ename||',职位是'||e.ejob from t_employees e
--nvl()函数,取值为null,给默认值
select * from t_employees e for update
--增加奖金字段
alter table t_employees add ecomm number(5,2)
-- 算出每个员工的收入
select e.ename,e.esalary+e.ecomm from t_employees e
select e.ename,e.esalary+nvl(e.ecomm,0) from t_employees e
--substr()截取
alter table t_employees add ephone char(11)
--号段
select substr(e.ephone,0,3) from
t_employees e where e.ename='胡瓜'
--to_number()转换成数值的函数
--聚合函数 count(),max(),min(),sum(),avg()
select count(*) from t_employees
select max(e.esalary) from t_employees e
select min(e.esalary) from t_employees e
select avg(e.esalary) from t_employees e
--事务？？？？？
--手动事务，自动事务，可以设置
-- 3查询技术 70%
-- 3.1 条件查询 where关键字
-- 查询姓名是李欣的所有信息
select * from t_employees e where e.ename='李欣'
--查询姓名是李欣，密码是12345的这个用户是不是合法用户
select count(*) from t_employees e where e.ename='李欣'
and e.epwd='1111'
select * from t_employees e where e.esex='男'
or e.ename='李欣'
--3.2 比较查询 >,<,>=,<=,!= <>
select * from t_employees e where e.esalary!=5000
select * from t_employees e where e.esalary<>5000
--3.3 模糊查询
--查询姓李的人员的信息
select * from t_employees e where e.ename like '李%'
--查询姓李的人员的，但是是两个字的人员信息
select * from t_employees e where e.ename like '李__'
--3.4 排序
--asc desc
select * from t_employees e order by e.esalary asc
select * from t_employees e order by e.esalary desc
--3.5 分组
--group by 统计就是和聚合函数在一起使用。
--统计这个单位的男女 性别数量 分组条件是明确
select e.esex,count(e.esex)
from t_employees e group by e.esex
--
select * from t_employees for update
-- 统计每个年龄员工的数量 select count(*), 分组条件是计算的
select count(*),
to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy') age
from t_employees e group by
to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy')
--统计每个职位的数量
select e.ejob,count(*)
from t_employees e group by e.ejob
--统计学历，统计籍贯
--统计每个年龄数量大于1 分组后带有限制条件
select count(*),
to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy') age
from t_employees e group by
to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy')
having count(*)>1
--统计每个年龄数量,数量按升序(先分组，后排序)分组带排序
select count(*),
to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy') age
from t_employees e group by
to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy')
order by count(*)
-- 子查询 一个查询的结果作为另外一个查询的条件
-- 找出工资最高的人的姓名
select * from t_employees
select * from t_employees e where e.esalary=
(select max(e.esalary) from t_employees e)
--3.6 in ,not in ,between and ...any all关键字
-- =只能等于一个值
select * from t_employees e where e.esalary=10000.88
-- in可以是多个值
select * from t_employees e where e.esalary
in(10000.88,5000.00)
select * from t_employees e where e.esalary
not in(10000.88,5000.00)
select * from t_employees e where e.esalary between 4000
and 20000;
select * from t_employees e where e.esalary >=4000
and e.esalary <=20000
--any >是比最小的大的信息，<是比最大的小信息
select e.esalary,e.ename from t_employees e where e.esalary<any
(select e.esalary from t_employees e where e.ejob='职工')
--all >是比最大的大的信息，<是比最小的小信息
select e.esalary,e.ename from t_employees e where e.esalary < all
(select e.esalary from t_employees e where e.ejob='职工')
select * from t_employees for update
-- 查询1991年到1999年出生的员工的信息
select * from t_employees
select * from t_employees e
where to_number(to_char(e.ebirthday,'yyyy'))
between 1991 and 1999
--****
insert into t_stus values(seq_stus.nextval,
'李二',default,'男')
select * from t_employees for update
--- oracle高级查询技术
-- 1.子查询
-- 逻辑，就是从一个条件出发去找关联的条件。
-- 一个查询的结果作为另一个查询的条件
-- 找出员工工资最高人的信息
select * from t_employees e where e.esalary in
(select max(e.esalary) from t_employees e)
--找出和李欣职位不同的员工的信息
select * from t_employees e where e.ejob!=
(select e.ejob from t_employees e where e.ename='李欣')
--找出比员工平均工资高的员工的信息
select * from t_employees e where e.esalary>
(select avg(e.esalary) from t_employees e)
--找出收入最高的员工的信息
select * from t_employees e where e.esalary+nvl(e.ecomm,0)=
(select max(e.esalary+nvl(e.ecomm,0)) from t_employees e )
--oracle分页查询 局限性 ，一条sql语句对应一张表，造成sql冗余
--oracle特殊的列，伪列 rownum ,它始终在第一行，不能移动
select rownum, e.* from t_employees e
--查询前三条的数据
select rownum, e.* from t_employees e where rownum<=3
select rownum, e.* from t_employees e where rownum<=2
-- 查询第2条到第4条之间的数据，查询出是空的
select rownum, e.* from t_employees e where rownum>=2
and rownum<=4;
select rownum, e.* from t_employees e where rownum
between 2 and 4
--rownum始终在第一行，导致你查询出来的是空的。
-- 查询第2条到第4条之间的数据
--通过临时结果集过渡查询
select * from
(select rownum rm,e.* from t_employees e where rownum<=4) tmp
where tmp.rm>=2
select rownum, e.* from t_employees e
--分页查询
-- 最大的编号:<=第几页*每页条数
--起始编号:>（第几页-1）*每页条数
select * from t_employees e
--每页的条数是2条
--第一页数据 第一页，2条 out:结果集
select * from
(select rownum rm,e.* from t_employees e where rownum<=1*2)
tmp where tmp.rm>(1-1)*2
--第二页数据
select * from
(select rownum rm,e.* from t_employees e where rownum<=2*2)
tmp where tmp.rm>(2-1)*2
--第三页数据
select * from
(select rownum rm,e.* from t_employees e where rownum<=3*2)
tmp where tmp.rm>(3-1)*2
-- 在实际的项目中的一个
--局限性 ，一条sql语句对应一张表，造成sql冗余 ，
--比如;100张表需要100个sql吗?
--2.集合查询
--集合查询
-- 多个结果集的查询
-- 工资大于3000的和职位是组长的两个查询结果的合并，过滤重复
-- 合并不过滤重复，查询的两个结果合并
select e.ename,e.esalary from
t_employees e where e.ejob='组长'
union all
select e.ename,e.esalary from t_employees e
where e.esalary>3000
--合并过滤重复
select e.ename,e.esalary from
t_employees e where e.ejob='组长'
union
select e.ename,e.esalary from t_employees e
where e.esalary>3000
--交集，两个查询结果集都有的
select e.ename,e.esalary from
t_employees e where e.ejob='组长'
intersect
select e.ename,e.esalary from t_employees e
where e.esalary>3000
--差集
select e.ename,e.esalary from
t_employees e where e.ejob='组长'
minus
select e.ename,e.esalary from t_employees e
where e.esalary>3000
--3.decode()函数查询 分支查询
select * from
t_employees
--科长工资加1.2倍，组长工资加1.1倍,其它职位保持原有，
--请输出加薪后的所有的员工的
--信息
select e.ename,e.ejob,e.esalary,
decode(e.ejob,'科长',e.esalary*1.2,
'组长',e.esalary*1.1,
esalary) 加薪后的工资
from t_employees e
-- 等同于case..when
select e.ename,e.ejob,e.esalary,
case e.ejob when '科长' then e.esalary*1.2
when '组长' then e.esalary*1.1
else e.esalary end
加薪后的工资
from t_employees e
--4.多表查询(2表查询，第三范式)
--之前讲的都是单表查询，进入到多表(>1)查询
--第三范式
-- 表中的列只能参照一个主键字段
-- 员工表 员工id，员工姓名，部门名称，部门id, 拆分形成两个实体表
--实体表之间的关系，主要有三种:
--1.一对一关系 员工表和角色表
--2.一对多关系 部门表和员工表 商品分类表和商品表
--3.多对多关系 学生和课程表（中间关系表，第三方表，成绩表）
--实体表之间的关系图，er图
--这个关系的定义是根据现实的业务来决定的。
select * from t_employees
--建立一个部门表 部门和员工表之间的关系是1对多
create table t_depts
(
did int primary key,
dname varchar2(20) not null,
daddress varchar2(50),
dphone char(11) not null
)
create sequence seq_depts
start with 10
increment by 2
nocache;
insert into t_depts
values(seq_depts.nextval,'质量部','南京江北新区','13913321089');
insert into t_depts
values(seq_depts.nextval,'技术部','南京江宁区','13913321086');
insert into t_depts
values(seq_depts.nextval,'人力资源部','南京鼓楼区','13913321085');
commit;
select * from t_depts for update
select * from t_employees for update
-- 员工和部门之间是有关系的
alter table t_employees add deptid int;
--外键约束？？？？？？？？？？？？
alter table t_employees add constraints fk_emp_depts
-- 表的设计，软件核心来自于数据
-- 查询这两张表的数据 笛卡尔积 表1*表2
select * from t_employees,t_depts
--等值连接
select * from t_employees e,t_depts d
where e.deptid=d.did
--内连接查询inner join
select * from t_employees e inner join t_depts d
on e.deptid=d.did
-- 查询员工李欣的部门名称
select d.dname,e.ename from t_employees e inner join
t_depts d
on e.deptid=d.did where e.ename='李欣'
--子查询 的效率比内连接查询要低
select d.dname from t_depts d where d.did=
(select e.deptid from t_employees e where e.ename='李欣')
--左连接
select d.dname,e.ename from t_employees e left join
t_depts d
on e.deptid=d.did
--右连接
select d.dname,e.ename from t_employees e right join
t_depts d
on e.deptid=d.did
--全连接
select d.dname,e.ename from t_employees e full join
t_depts d
on e.deptid=d.did
--
select * from t_depts
-- 统计技术部人员的工资的总和
select sum(e.esalary) 工资总和 from t_depts d inner join t_employees e on d.did
=e.deptid where d.dname='技术部'
-- 统计每个部门的人员的数量
select count(e.ename) 数量, d.dname from t_depts d
left join t_employees e on d.did=e.deptid
group by d.dname
=e.deptid
--统计每个部门的人员的数量大于1的信息 group by 限制条件 having
select count(e.ename) 数量, d.dname from t_depts d
left join t_employees e on d.did=e.deptid
group by d.dname
having count(e.ename)>1
--统计每个部门的人员的数量按降序排列
select count(e.ename) 数量, d.dname from t_depts d
left join t_employees e on d.did=e.deptid
group by d.dname order by count(e.ename) desc
------------------------------------------------------------------
---pl-sql块
--块:一组sql语句在一起运行，解决复杂的业务逻辑。
-- 是不能够被编程语言所调用 java,python,c#
--块的基本结构
/**
declare
---定义的变量
begin
-- 一组sql语句
end;
**/
/**
变量v_
变量赋值:=
**/
-- 计算两个数值类型的变量的和并输出
declare
v_num1 number:=100;
v_num2 number:=10;
v_sum number;
begin
v_sum:=v_num1+v_num2;
dbms_output.put_line('计算这两个数的和为'||v_sum);
end;
-- 异常处理 计算两个数值类型的变量的商并输出
declare
v_num1 number:=100;
v_num2 number:=0;
v_sum number;
begin
v_sum:=v_num1/v_num2;
dbms_output.put_line('计算这两个数的和为'||v_sum);
--异常处理块
exception --捕获异常
when others then
dbms_output.put_line('v_num2这个数作为除数不能为零');
end;
-- 流程控制语句
-- 一个数判断是奇数还是偶数,并输出信息
declare
v_num number:=22;
begin
-- oracle不支持%取模，取模函数mod()
if mod(v_num,2)=0 then
dbms_output.put_line('v_num这个数是偶数');
else
dbms_output.put_line('v_num这个数是奇数');
end if;
end;
--优化
declare
v_num number:=22;
v_str varchar2(100);
begin
-- oracle不支持%取模，取模函数mod()
if mod(v_num,2)=0 then
v_str:='v_num这个数是偶数';
else
v_str:='v_num这个数是奇数';
end if;
dbms_output.put_line(v_str);
end;
-- 查询李欣这个员工的性别，如果是男的，奖金加1000，
--如果是女的，奖金加500，
-- 输出他现在的收入输出
/**
1.赋值:= 直接给变量赋值
2.从sql语句查询的结果进行赋值。2.1查询出来的是一个值，into
2.2 如果是多个值，不能用into，要用游标遍历
**/
declare
v_esex t_employees.esex%type; --这个变量的类型参照表中的字段类型
v_usaraly t_employees.esalary%type;
v_sum number(10,2);
begin
--1.查询李欣这个员工的性别
select e.esex into v_esex
from t_employees e where e.ename='李欣';
--2.判断是男还是女
if v_esex='男' then
v_usaraly:=1000;
else
v_usaraly:=500;
end if;
--执行更新
update t_employees e set e.esalary=e.esalary+v_usaraly
where e.ename='李欣';
commit;
-- 查询出现在的收入
select e.esalary+nvl(e.ecomm,0) into v_sum from t_employees e where e.ename='李欣';
dbms_output.put_line('李欣这个员工的性别是:'||v_esex||',他现在的收入为:'||v_sum);
--select e.ename from t_depts d inner join t_employees e
-- on d.did=e.deptid where d.dname='质量部'
end;
-- 查询李欣的部门名称,如果是质量部，加1000，如果是技术部加2000，
--如果是人力资源部加500，其它部门加100
declare
v_dname t_depts.dname%type;
v_ecomm t_employees.ecomm%type;
begin
-- 李欣的部门名称
select d.dname into v_dname from t_employees e inner join t_depts d
on e.deptid=d.did where e.ename='李欣';
if v_dname='质量部' then
v_ecomm:=500;
elsif v_dname='技术部' then
v_ecomm:=600;
elsif v_dname='人力资源部' then
v_ecomm:=200;
else
v_ecomm:=100;
end if;
update t_employees e set e.ecomm=e.ecomm+v_ecomm
where e.ename='李欣';
commit;
end;
-- 循环结构
--1.100之和并判断是奇数还是偶数
-- for loop
declare
v_sum number:=0;
begin
for v_i in 0..100 loop
v_sum:=v_sum+v_i;
end loop;
dbms_output.put_line('和为'||v_sum);
end;
--while loop
declare
v_i number:=0;
v_sum number:=0;
begin
while v_i<=100 loop
v_sum:=v_sum+v_i;
v_i:=v_i+2; --改变循环变量的值
end loop;
dbms_output.put_line('和为'||v_sum);
end;
--循环结构使用
create table t_users
(
id int primary key,
tname varchar2(20),
tsex char(6)
)
create sequence seq_users
start with 1
increment by 1
nocache;
select * from t_users
--for循环批量数据
declare
begin
for v_i in 1..10000 loop
if mod(v_i,2)=0 then
insert into t_users values(seq_users.nextval,'李'||v_i,'男');
else
insert into t_users values(seq_users.nextval,'王'||v_i,'女');
end if;
end loop;
commit;
end;
--while循环批量数据
declare
v_i number:=1;
begin
while v_i<=1000 loop
insert into t_users values(seq_users.nextval,'李'||v_i,'男');
v_i:=v_i+1;
end loop;
commit;
end;
---into是只能附一个值
-- 如果是多个值，就是游标，就是结果集，分为;cursor(显示游标),隐式游标
-- 查询员工表的中的数据，并输出每个人的姓名和性别
-- while..loop
declare
--定义一个显示游标
cursor v_datas is select * from t_employees;
-- 定义一行
v_linedatas t_employees%rowtype;
begin
--打开这个游标
open v_datas;
--遍历游标
fetch v_datas into v_linedatas; --遍历第一行
while v_datas%found loop -- 如果有数据进入循环体
dbms_output.put_line(v_linedatas.ename||',性别是:'||v_linedatas.esex);
fetch v_datas into v_linedatas;--移动到下一行
end loop;
-- 关闭游标
close v_datas;
end;
-- for..loop
declare
--定义一个显示游标
cursor v_datas is select * from t_employees;
begin
for v_linedatas in v_datas loop
dbms_output.put_line(v_linedatas.ename||',性别是:'||v_linedatas.esex);
end loop;
-- 关闭游标
end;
-- 隐式游标
-- 查询质量部员工的性别，是男加1000，是女加5000
declare
v_sex t_employees.esex%type;
v_comm t_employees.ecomm%type;
begin
--质量部的员工的信息
for v_datas in ( select e.esex ,e.ename from t_employees e right join t_depts d
on e.deptid=d.did where d.dname='质量部') loop
dbms_output.put_line(v_datas.esex||v_datas.ename);
if v_datas.esex='男' then
v_comm:=200;
else
v_comm:=50;
end if;
--执行更新
update t_employees e set e.ecomm=e.ecomm+v_comm
where e.ename=v_datas.ename;
commit;
end loop;
end;
select * from t_employees
---存储过程
-- 存储(是以一个名字来存储)+过程(过程化的语句块)
-- pl-sql块是不能够被编程语言直接调用,只能运行在数据库端
-- 以一个名字命名，这个名字被编程语言call，这样形成交互
--预编译，编译一次，下次调用的话不需要再次编译，性能好，
--能够处理复杂的业务逻辑,
--可以有传入和输出参数,缺点:占用存储空间
--sql(dml语言，调用一次编译一次)，性能没有存储过程好
--基本结构
create or replace procedure 存储过程的名字
(
--传入和输出参数
)
as
begin
end;
---检查登录
create or replace procedure p_checklogin
(
v_uname in varchar2, --传入参数不需要长度
v_pwd in varchar2,
v_msg out varchar2
)
as
v_count int;
begin
--检查登录
select count(*) into v_count from t_employees e where e.ename=v_uname
and trim(e.epwd)=v_pwd;
--进行判断
if v_count>0 then
v_msg:='登录成功';
else
v_msg:='登录失败';
end if;
--记录
insert into t_userlog values(seq_userlog.nextval,v_uname,default,v_msg);
commit;
end;
-- 变更存储过程
create table t_userlog
(
ulid int primary key,
uname varchar2(20), -- 登录的人
logintime date default sysdate, --登录的时间
loginresult varchar2(20) --登录的结果
)
create sequence seq_userlog
start with 1
increment by 1
nocache;
select * from t_userlog
-- 直接返回一个结果集(不在存储过程内部遍历)
--**部门的员工的数量和员工的姓名
create or replace procedure p_queryempdatas
(
v_dname in varchar2, --部门名称
v_count out int, --部门员工数量
v_namedatas out sys_refcursor --部门员工姓名
)
as
begin
--1.通过部门名称得到部门的员工的数量
select count(e.ename) into v_count from t_employees e
right join t_depts d
on e.deptid=d.did where d.dname=v_dname;
--2.员工姓名(不在存储过程内部遍历)
open v_namedatas for select e.ename from t_employees e
right join t_depts d
on e.deptid=d.did where d.dname=v_dname;
end;
----**部门的员工的数量和属于这个部门的员工的性别，
--如果是男，工资加250；
--女加100；
create or replace procedure p_querydeptupdatesalary
(
v_dname in varchar2,
v_count out int
)
as
v_salary t_employees.esalary%type;
begin
--1.通过部门名称得到部门的员工的数量
--select count(e.ename) into v_count from t_employees e
--right join t_depts d
--on e.deptid=d.did where d.dname=v_dname;
--调用函数，减少冗余代码
v_count:=f_querydeptempnum(v_dname);
--2.这个部门的所有员工的信息
for v_linedatas in (select * from t_employees e
right join t_depts d
on e.deptid=d.did where d.dname=v_dname) loop
if v_linedatas.esex='男' then
v_salary:=250;
else
v_salary:=150;
end if;
--执行更新
update t_employees e set e.esalary=e.esalary+v_salary
where e.ename=v_linedatas.ename;
commit;
end loop;
end;
select * from t_employees
-- 自定义函数 特殊的存储过程
-- 自定义函数 1.关键字function 2.只能返回一个值
create or replace function f_querydeptempnum
(
v_dname in varchar2
)
return int --切记这个地方不能加;
as
v_count int;
begin
select count(e.ename) into v_count from t_employees e
right join t_depts d on e.deptid=d.did
where d.dname=v_dname;
return v_count;
end;
-- 查询一个表t_employees的条数 如果系统中的表很多，这样会造成冗余
create or replace function f_queryempcount
return int
as
v_count int;
begin
select count(*) into v_count from t_employees ;
return v_count;
end;
--动态sql
-- 一个值
create or replace function f_querytablecount
(
v_tablename in varchar2
)
return int
as
v_sql varchar2(1000);
v_count int;
begin
v_sql:='select count(*) from ' ||v_tablename;
-- 执行这个动态sql,是一个值
execute immediate v_sql into v_count;
return v_count;
end;
---多个值
create or replace function f_querytabledatas
(
v_tablename in varchar2
)
return sys_refcursor
as
v_sql varchar2(1000);
v_datas sys_refcursor;
begin
v_sql:='select * from ' ||v_tablename;
-- 执行这个动态sql,是一个结果集
open v_datas for v_sql;
return v_datas;
end;
--sql语句分页 固定2条
select * from t_employees
-- sql分页，系统大了，会造成sql冗余
select * from (select rownum rm,
e.* from t_employees e where rownum<=4) tmp
where tmp.rm>2
--存储过程分页 一个存储过程可以对系统中的表都可以分页
-- tablename in 表名
--pagenum in 每页几条
--currentnum in 第几页
--datas out 每页的结果集
--count out 总条数
--pagesize out 总页数
create or replace procedure p_pagemodel
(
v_tablename in varchar2,
v_pagenum in int,
v_currentnum in int,
v_datas out sys_refcursor,
v_count out int,
v_pagesize out int
)
as
v_endindex int:= v_currentnum*v_pagenum;
v_startindex int:=(v_currentnum-1)*v_pagenum;
v_sql varchar2(1000);
begin
--1.构建总的条数动态sql
v_sql:='select count(*) from '||v_tablename;
--2.执行动态sql
execute immediate v_sql into v_count;
--3.获取总页数
if mod(v_count,v_pagenum)=0 then
v_pagesize:=v_count/v_pagenum;
else
--除不尽
v_pagesize:=floor(v_count/v_pagenum)+1;
end if;
--获取分页结果集
v_sql:='select * from '||'(select rownum rm, e.* from '||v_tablename||
' e where rownum<='||v_endindex||') tmp' ||' where tmp.rm>'||v_startindex;
dbms_output.put_line(v_sql);
--执行动态sql，返回的是一个结果集
open v_datas for v_sql;
end;
-- 面试的时候，什么是事务
--jdbc 事务 自动事务
--hiernate事务
--mybatis事务
--spring事务
--python事务 手动事务
-- rollback回滚事务 commit提交事务 savepoint 设置事务保存点
--数据库事务
--事务就是一个工作单元，所谓的工作单元，就是不可分割的一个或多个sql
-- (insert ,update,delete)
--秦日霞借钱1000给李欣，并记录日志。
select * from t_employees
--秦日霞借钱1000给李欣
--记录日志
create or replace procedure p_operatorsalary
(
v_rname in varchar2,
v_tname in varchar2,
v_money in number,
v_msg out varchar2
)
as
begin
-- 1.先把借钱人的钱扣掉
update t_employees e set
e.esalary=e.esalary-v_money where e.ename=v_rname;
--2.加上给借钱人
update t_employees e set
e.esalary=e.esalary+v_money where e.ename=v_tname;
--设置一个事务保存点 给个名字
savepoint a;
--3.记录日志
insert into t_userlog
values(seq_userlog.nextval,v_tname,'1997-12-30','借钱');
commit;
exception
when others then
rollback to a; --回滚到事务保存点a，提交
commit;
end;
select * from t_userlog
-------------
--索引，job(定时任务)，视图，触发器
-- 索引
-- 怎么来提高查询的性能?
-- 1.索引 2. sql优化
-- 索引就是来提高查询的性能。
-- 打个比方来说:书的目录 聚集索引 ；
--书的页码:聚集索引
-- 70w条的数据以上我们才考虑建立索引
-- 建立一张表
create table t_stuinfo
(
suid number(7) primary key,
sname varchar2(100),
sbirthday date default sysdate,
ssex char(4)
)
--加载80w条数据
declare
begin
for v_i in 1..1200000 loop
if mod(v_i,2)=0 then
insert into t_stuinfo
values(v_i,'李'||v_i,default,'男');
else
insert into t_stuinfo
values(v_i,'王华'||v_i,default,'女');
end if;
end loop;
commit;
end;
delete from t_stuinfo
select count(*) from t_stuinfo
select * from t_stuinfo
-- 白盒测试 查询 王华67001 查这个人的信息
create or replace procedure p_queryinfo
(
v_name in varchar2
)
as
v_begintime varchar2(1000);
v_endtime varchar2(1000);
v_birthday t_stuinfo.sbirthday%type;
begin
v_begintime:=to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff');
select s.sbirthday into v_birthday
from t_stuinfo s where s.sname=v_name;
v_endtime:=to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff');
dbms_output.put_line('开始时间:'||v_begintime);
dbms_output.put_line('接受时间:'||v_endtime);
end;
--
/**
开始时间:2018-03-23 09:29:31.490000000
接受时间:2018-03-23 09:29:31.537000000 --47000000
开始时间:2018-03-23 09:32:05.235000000
接受时间:2018-03-23 09:32:05.250000000 --15000000
--
开始时间:2018-03-23 09:32:52.942000000
接受时间:2018-03-23 09:32:52.958000000 16000000
开始时间:2018-03-23 09:33:26.529000000
接受时间:2018-03-23 09:33:26.576000000 47
**/
-- 建立索引
create index snameindex on t_stuinfo(sname);
drop index snameindex;
call p_queryinfo('李966002');
-- job定时任务 数据库定时任务
--自动化定时任务 对于表的历史数据的一个定时清理
-- 1-10
-- t_stuinfo ，每隔1分钟自动插入一条数据
delete from t_stuinfo;
commit;
create sequence seq_stuinfo
start with 1
increment by 1
nocache;
--建立一个存储过程
create or replace procedure p_timetaskadddata
as
begin
insert into t_stuinfo
values(seq_stuinfo.nextval,'李欣',default,'男');
commit;
end;
--建立一个任务
variable job2018 number;
-- 命令行窗口 提交这个定时任务
sql> variable job2018 number;
sql> begin
2 dbms_job.submit(:job2018,'p_timetaskadddata;',
sysdate,'sysdate+1/1440');
3 end;
4 /
select * from t_stuinfo
sql> begin
2 dbms_job.remove(:job2018);
3 end;
4 /
**pl/sql procedure successfully completed
job2018
---------**
上一篇： Pandas进阶笔记 （一） Groupby 重难点总结
下一篇： 离太阳近