大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说oracle数据库基础知识总结_oracle数据库常用sql语句,希望您对编程的造诣更进一步.
— 数据库存储数据
— 市面上主流的数据库有哪些
— 甲骨文 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
———**
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/11008.html
