游标
用来存储多条查询数据的一种数据结构(‘结果集’),
它有一个 ‘指针’,从上往下移动(‘fetch’),从而能够 ‘遍历每条记录’
declare
--定义游标
cursor c_job is
select empno,ename,job,sal from SCOTT.emp where job = 'MANAGER';
--定义一个变量,存放c_job的每行结果
c_row c_job%rowtype;
begin
-- for 遍历
for c_row in c_job loop
dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
end loop;
end;
declare
--定义游标
cursor c_job is
select empno,ename,job,sal from SCOTT.emp where job = 'MANAGER';
--定义一个变量,存放c_job的每行结果
c_row c_job%rowtype;
begin
open c_job; --打开游标
loop
--抓取游标中的每一行数据,赋值给c_row
fetch c_job into c_row;
exit when c_job%notfound;
dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
end loop;
close c_job;--关闭游标
end;
--执行任意一个update
declare
e_count number;
begin
update SCOTT.emp set ename='ybdemo' where empno = 123;
if sql%rowcount=1 then
dbms_output.put_line('更新一条记录');
else
dbms_output.put_line('没有找到要更新的记录');
end if;
end;
第一种方式:for 循环
declare
cursor csr_dept is
select dname from SCOTT.dept;
row_dept csr_dept%rowtype;
begin
for row_dept in csr_dept loop
dbms_output.put_line('部门名称:'||row_dept.dname);
end loop;
end;
第二种方式:fetch
declare
cursor csr_dept is
select dname from SCOTT.dept;
row_dept csr_dept%rowtype;
begin
open csr_dept;
--fetch 每次只取一行
fetch csr_dept into row_dept;
while csr_dept%found loop
dbms_output.put_line('部门名称:'||row_dept.dname);
--输出完之后,再取
fetch csr_dept into row_dept;
end loop;
close csr_dept;
end;
二者执行结果都是一样的
存储过程
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。
1、求累加和
-----存储过程(实现求和)
create or replace procedure testloop(num in number,sumVal out number)
as
begin
declare
orinum number:=0;
sumadd number:=0;
begin
loop
if(orinum>num) then
sumVal:=sumadd;
dbms_output.put_line('最终结果:'|| sumVal);
exit;
end if;
dbms_output.put_line('start orinum:'||orinum||'sumadd:'||sumadd);
sumadd:=sumadd+orinum;
orinum:=orinum+1;
dbms_output.put_line('end orinum:'||orinum||'sumadd:'||sumadd);
end loop;
end;
end;
存储过程调用
declare
num number;
sumVal number;
begin
num:=5;
sumVal:=0;
testloop(num,sumVal); --调用存储过程
dbms_output.put_line('result'||sumVal);
end;
2、向表中插入数据
--创建(实现向表中插入数据)
create or replace procedure addbook(bookName in varchar2,typeId in number) as
begin
declare
maxId number;
begin
select max(id) into maxId from t_book;
insert into t_book values(maxId+1,bookName,typeId);
end;
end;
--调用
declare
bookName varchar2(32);
bookTypeId number;
begin
bookName:='Python爬虫20例';
bookTypeId:='2';
addbook(bookName,bookTypeId);
end;
分区表
- 范围分区
- 列表分区
- 散列分区(hash分区)
- 复合分区(子分区)
1、范围表
--创建范围分区表
create table range_table(
id number,
done_date date,
data varchar2(32)
)
partition by range(done_date)(
partition part_1 values less than (to_date('20200712','yyyymmdd')),
partition part_2 values less than (to_date('20210712','yyyymmdd')),
partition part_3 values less than (maxvalue)
);
--向分区表中插入数据
insert into range_table values (1,to_date('20200712','yyyymmdd'),'java');
insert into range_table values (2,to_date('20210711','yyyymmdd'),'java');
insert into range_table values (3,to_date('20210712','yyyymmdd'),'java');
--查看分区表
select * from RANGE_TABLE;
--查看分区表中的某个分区内容
select * from RANGE_TABLE where done_date = to_date('20210711','yyyymmdd');
--查看分区表信息
select * from user_tab_partitions where table_name = 'RANGE_TABLE';
2、列表分区
--列表分区
create table list_table(
id number,
name varchar2(32),
data varchar2(32)
)
partition by list(id)
(
partition part_1 values(1,3,5,7),
partition part_2 values(2,4,6,8),
partition part_default values (default)
);
select * from user_tab_partitions where table_name='LIST_TABLE';
--插入数据
insert into list_table values(1,'Java','20210713');
insert into list_table values(2,'Python','20210713');
insert into list_table values(3,'Scala','20210713');
insert into list_table values(9,'Ruby','20210713');
select * from list_table where id = 1;
3、散列分区
--创建散列分区
create table hash_table(
id number,
done_date date,
data varchar2(32)
)
partition by hash(done_date)
(
partition part_1,
partition part_2
)
-- 查看散列表信息
select * from user_tab_partitions where table_name = 'HASH_TABLE';
4、复合分区
--复合分区(范围+散列)
create table range_hash_table(
id number,
done_date date,
data varchar2(32)
)
--先按日期进行范围分区
partition by range(done_date)
--再按id进行散列分区
subpartition by hash(id)
(
partition part_1 values less than (to_date('20200712','yyyymmdd'))
(
subpartition part_1_sub_1,
subpartition part_1_sub_2
),
partition part_2 values less than (to_date('20210712','yyyymmdd'))
(
subpartition part_2_sub_1,
subpartition part_2_sub_2
),
partition part_3 values less than ( maxvalue )
(
subpartition part_3_sub_1,
subpartition part_3_sub_2
)
);
select * from user_tab_partitions where table_name='RANGE_HASH_TABLE';
select * from user_tab_partitions where table_name='RANGE_LIST_TABLE';
--查看子分区信息
select * from user_tab_subpartitions where table_name='RANGE_LIST_TABLE';
--复合分区(范围分区+列表分区)
create table range_list_table(
id number,
done_date date,
data varchar2(32)
)
--先按日期进行范围分区
partition by range(done_date)
--再按id进行列表分区
subpartition by list(id)
(
partition part_1 values less than (to_date('20200712','yyyymmdd'))
(
subpartition part_1_sub_1 values(1,3,5),
subpartition part_1_sub_2 values(2,4,6),
subpartition part_1_sub_default values(default)
),
partition part_2 values less than (to_date('20210712','yyyymmdd'))
(
subpartition part_2_sub_1 values(1,3,5),
subpartition part_2_sub_2 values(2,4,6),
subpartition part_2_sub_default values(default)
),
partition part_3 values less than ( maxvalue )
(
subpartition part_3_sub_1 values(21,23,25),
subpartition part_3_sub_2 values(22,24,26),
subpartition part_3_sub_default values(default)
)
);