Oracle本篇文章皆为本人笔记，欢迎一起讨论。
CREATE table cla(--创建班级表
claid integer primary key,--班级编号
claname VARCHAR2(20) not null --班级名称
?
)
create table studen(--创建表
stuno integer primary key,--学生编号
stuname VARCHAR(20) not null,--学生姓名
studate date not null,--入学时间
claid integer,--班级
constraint fk_cla_studen foreign key(claid) references cla(claid)
)
--添加班级表
insert into cla values(1,'20网络三班')
insert into cla values(2,'20网络四班')
insert into cla values(3,'20网络五班')
--添加学生表
insert all
into studen VALUES(1,'张三',to_date('2020-09-08','YYYY-MM-DD'),1)
into studen VALUES(2,'李四',to_date('2020-09-07','YYYY-MM-DD'),3)
into studen VALUES(3,'王五',to_date('2020-09-06','YYYY-MM-DD'),2)
SELECT * from dual;
--查询学生姓名，学号和所在班级名称
SELECT stuno,stuname,claname from studen join cla on cla.CLAID=studen.CLAID;
-- 创建序列，使用序列完成数据的添加
-- 给班级表添加序列
create sequence sequ_cla
start with 20
increment by 2;
select sequ_cla.nextval from dual;
insert into cla values(sequ_cla.nextval,'20网络八班');
select * from cla;
create sequence seq_studen --给哪个表添加序列
start with 100
increment by 2;
--sequence两个属性：currval:序列的当前值 ?nextval:序列的下一个值
SELECT seq_studen.nextval from dual;
insert into studen values(seq_studen.nextval,'图图',to_date('2020-09-08','YYYY-MM-DD'),1)
SELECT * from studen;
-- 第三章
DECLARE
?type table_dept is table of dept%rowtype index by binary_integer;
?v_deptno integer;
??
begin
insert into dept VALUES(110,'管理部','北京');
end;
declare
? type emp_table_type is table of emp%rowtype;
? v_emp_table emp_table_type;
? v_max_sal emp.sal%type;
begin?
? for dept_row in(select * from dept) loop
? select max(sal) into v_max_sal
? from emp ?
? where emp.deptno=dept_row.deptno;
? select * ?bulk collect ?into v_emp_table
? from emp
? where sal=v_max_sal;
? dbms_output.put_line('部门编号:' || dept_row.deptno);
? dbms_output.put_line('----------------------------');
? if v_emp_table.count>0 then
? ? for i in v_emp_table.first..v_emp_table.last loop
? ? dbms_output.put_line(v_emp_table(i).ename || ' ' || v_emp_table(i).sal);
? end loop;
? else
? dbms_output.put_line('该部门暂无员工'); ??
? end if;
? dbms_output.put_line('----------------------------');
? end loop;
? end;
?
?
select *from dept;
declare
type table_emp is table of emp%rowtype;
v_emp_table table_emp;
v_sal emp.sal%type;
begin
for dept in (select * from dept) loop select max(sal) into v_sal from emp
where emp.DEPTNO=dept.DEPTNO;
select * bulk collect into v_emp_table from emp where sal=v_sal;
dbms_output.put_line('部门编号：'||DEPT.DEPTNO);
if v_emp_table.count>0 then for i in v_emp_table.first..v_emp_table.last loop
dbms_output.put_line('员工姓名：'||v_emp_table(i).ENAME||'员工工资：'||v_emp_table(i).SAL);
end loop;
else dbms_output.put_line('该部门没有员工！');
end if;
end loop;
end;
declare?
?v_dept_row DEPT%rowtype;
begin
? v_dept_row.DEPTNO:=50;
? v_dept_row.DNAME:='ADMINISTRATOR';
? v_dept_row.AREA:='BEIJING';
? insert into dept values v_dept_row;
end;
select t.* from DEPT t
--- ? ?
DECLARE
v_dept_row dept%rowtype;
type dept_table is table of dept%rowtype index by binary_integer;
v_dept_table dept_table;
begin
v_dept_table(0).deptno:=70;
v_dept_table(0).dname:='人事部';
v_dept_table(0).area:='北京';
v_dept_table(1).deptno:=80;
v_dept_table(1).dname:='行政部';
v_dept_table(1).area:='北京';
v_dept_table(2).deptno:=90;
v_dept_table(2).dname:='研发部';
v_dept_table(2).area:='北京';
-- for循环
for i in 0..v_dept_table.count-1 loop
INSERT into dept VALUES(v_dept_table(i).deptno,v_dept_table(i).dname,v_dept_table(i).AREA);
end loop;
end;
-- 要求查询出各部门工资最高的雇员
-- 1.知道有哪些部门
-- 2.每个部门的最高工资
-- 3.查询出工资最高的员工信息
DECLARE
type emp_table is table of emp%rowtype index by binary_integer;
v_empMaxSal_table emp_table;-- 工资最高的员工信息
v_max_sal emp.sal%type;--记录最高工资
begin-- 知道有哪些部门
for ?dept_row in (select * from dept) loop-- dbms_output.put_line(dept_row.empno);
--查询部门工资最高是多少
select max(sal) into v_max_sal from emp where deptno=dept_row.DEPTNO;
--3.查询出工资最高员工信息
--bulk collect 将查询出来的数据一次性添加到collECTion
SELECT * bulk collect into v_empMaxSal_table from emp where sal=v_max_sal;
dbms_output.put_line('部门编号：'||dept_row.DEPTNO);
if v_empMaxSal_table.count>0 then --说明这个部门是有人的
-- ?for i in 1..v_empMaxSal_table.count loop
for i in v_empMaxSal_table.first..v_empMaxSal_table.last loop
dbms_output.put_line('员工编号:'||v_empMaxSal_table(i).empno||'员工姓名:'||v_empMaxSal_table(i).ENAME||'员工工资'||v_empMaxSal_table(i).sal);
end loop;
else-- 这个部门没人
dbms_output.put_line('该部门没有员工！！');
end if;
dbms_output.put_line('====================');
end loop;
end;
insert into dept VALUES(60,'人事部','北京');
SELECT * from dept;
SELECT * from emp;
----------- 第五章
--向部门表dept中插入两条部门信息，要求创建存储过程proc _ insert _ dept实现
create or REPLACE PROCEDURE proc_insert_dept
is
begin
INSERT all?
into dept VALUES(50,'后勤部','北京')
into dept VALUES(60,'总部','北京')
SELECT * from dual;
end;
call proc_insert_dept();
---创建函数名“fun_getEmpInfo”,该函数包含一个输入参数雇员名称param_ename、一个输出参数部门名称param_dname和一个返回值返回岗位信息
create or replace function fun_scj(param_ename emp.ename%type,param_dname out dept.dname%type)
return VARCHAR2
as
param_job emp.job%type;
begin
select e.job,d.dname into param_job,param_dname from emp e,dept d where e.DEPTNO=d.DEPTNO and e.ename=param_ename;
---upper(e.ENAME)=upper(param_ename);
return param_job;
end;
declare?
param_ename VARCHAR2(20):='天天';
param_dname VARCHAR2(20);
param_job VARCHAR2(20);
begin
param_job:=fun_scj(param_ename,param_dname);
dbms_output.put_line('员工名称'||param_ename);
dbms_output.put_line('部门名称'||param_dname);
dbms_output.put_line('员工岗位'||param_job);
end;
------编写过程，命名为“proc_storage”,向该存储过程传入需要出库的商品编号和出库数量，执行出库操作并返回完成状态、商品名称、原库存和现有库存。其中“0”表示出库成功，“1”表示找不到该商品，“2”表示库存不足。商品表（商品编号，商品库存，商品名称）
CREATE table stor(
sid INTEGER primary key,
sal number(10,0) not null,
gname VARCHAR2(20) not null
);
CREATE or REPLACE procedure ?proc_storage
(v_SID stor.SID%type,--商品编号
v_sal stor.SAL%type,-- 出库数量
v_gname out stor.GNAME%type,-- 商品名称
v_flag out integer,--出库状态
v_oldstor out stor.sal%type,-- 原库存
v_newstro out stor.sal%type) -- 现库存
is
begin
-- 根据商品编号查询出商品名称和商品原库存记录到 v_oldstor,v_gname
select sal,gname into v_oldstor,v_gname from stor where sid=v_sid;
if v_sal>v_oldstor then-- 库存不足
v_flag:=2;
else-- 库存不足
v_newstro:=v_oldstor-v_sal;-- 现库存数量
UPDATE stor set sal=v_newstro where sid=v_sid;-- 更新数据库中商品数量
v_flag:=0;
end if;
EXCEPTION
when ?no_data_found then
v_flag:=1;
dbms_output.put_line('输入的商品不存在');
end;
-- 需要定义变量接收输出参数 所以需要plsql块
DECLARE
v_gname ?stor.GNAME%type;-- 商品名称
v_flag integer;--出库状态
v_oldstor ?stor.sal%type;-- 原库存
v_newstro ?stor.sal%type;-- 现库存
begin
proc_storage(1,15,v_gname,v_flag,v_oldstor,v_newstro);
dbms_output.put_line(v_flag);
end;
insert into stor VALUES(1,10,'商品a');
insert into stor VALUES(2,100,'商品b');
---创建函数名“fun_getEmpInfo”,该函数包含一个输入参数雇员名称param_ename、一个输出参数部门名称param_dname和一个返回值返回岗位信息
CREATE or REPLACE function fun_getEmpInfo(v_ename emp.ename%type,v_dname out dept.dname%type)
return VARCHAR2
is
v_job emp.job%type;
begin
select e.job, d.dname into v_job,v_dname from emp e join dept d on e.deptno=d.deptno where ename=v_ename;
return v_job;
end;
DECLARE
v_job emp.job%type;
v_dname dept.dname%type;
begin
v_job:=fun_getEmpInfo('天天',v_dname);
dbms_output.put_line(v_job||'=='||v_dname);
end;
SELECT * from emp;
select * from dept;
--- 第六章
--1.
create table score(
id integer primary key,--主键
sid integer not null unique,-- 学号
chinese number(3,0),
math number(3,0),
english number(3,0),
rank number(4,0) --总分
);
create table scj(
sid integer primary key,
sname VARCHAR2(20) not null,
sal varchar(20) not null
)
insert into score(id,sid,chinese,math,english) values(1,1001,85,99,99);
insert into score(id,sid,chinese,math,english) values(2,1002,95,89,90);
insert into score(id,sid,chinese,math,english) values(3,1003,95,79,90);
insert into score(id,sid,chinese,math,english) values(4,1004,90,90,99);
select * from score;
declare
cursor cur_score is select * from score;
v_sum number;
v_rank number;
begin
-- 使用for循环简化游标
for score_row in cur_score loop -- 循环获取每一行数据
-- 把当前学生的成绩相加
v_sum:= score_row.chinese+score_row.math+score_row.english;
--看表中其他成绩之和有几个比v_sum大
-- 0-1
--1-2
--2-3
select count(*) into v_rank from score where chinese+math+english>v_sum;
v_rank:=v_rank+1;--当前这个学生的排名
update score set rank=v_rank where id=score_row.id;
end loop;
end;
declare?
cursor cur_score is select * from score;
v_sum number;
v_rank number;
begin
for score_row in cur_score loop
v_sum:=score_row.CHINESE+score_row.math+score_row.english;
select count(*) into v_rank from score where CHINESE+math+ENGLISH>v_sum;
update score set rank=v_rank where id=score_row.id;
end loop;
end;
---使用游标完成，输出每位员工的工作年限（年，月）
declare
cursor cur_work
is
? select ENAME,HIREDATE,trunc(months_between(sysdate,HIREDATE) / 12) as spandyears,
?? ?trunc(mod(months_between(sysdate,HIREDATE),12)) as months
from emp;
r_work cur_work%rowtype;
begin
for r_work in cur_work loop
dbms_output.put_line(r_work.ename||'已经工作了'||r_work.spandyears||'年零'||r_work.months||'月');
end loop;
end;
--使用触发器完成数据表的主键自增
create table sjb(
scjid VARCHAR2(4) primary key,
sname VARCHAR2(20)
)
create sequence scj_seq START with 1 INCREMENT by 1;
create or replace trigger sjb_trigger
before insert on sjb
for each row
begin
select scj_seq.nextval into :new.scjid from dual;
end;
insert into sjb(sname) VALUES('程程');
insert into sjb(sname) VALUES('称称');
commit;
select * from sjb;
--输出员工工作年限
declare
-- 定义
-- ? 年 首先知道两个日期相差多少个月,除以12，取整 ?
cursor cur_emp is?
select HIREDATE,ename,floor(months_between(sysdate,HIREDATE) /12) as sysyear,
floor(mod(months_between(sysdate,HIREDATE) ,12)) as year
from emp;
begin
for v_row in cur_emp loop
dbms_output.put_line(v_row.ename||'=='||v_row.sysyear||'年'||v_row.year||'月');
end loop;
end;
create trigger tri_primary_auto
before insert on dept
for each row
?begin
?-- 将序列中的值into需要添加到这一行的deptno中
?select seq_dept.nextval into :new.deptno from dual;
?end;
insert into dept(dname,area) VALUES('text','test');
select * from emp;
select * from dept;
?