– 数据库存储数据
– 市面上主流的数据库有哪些
– 甲骨文 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.esalary1.2,
‘组长’,e.esalary1.1,
esalary) 加薪后的工资
from t_employees e
– 等同于case…when
select e.ename,e.ejob,e.esalary,
case e.ejob when ‘科长’ then e.esalary1.2
when ‘组长’ then e.esalary1.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
---------
扫一扫
3845
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
