一、序列的概念
1. 序列是用于生成唯一、连续整数的对象,创建序列时可以指定是升序还是降序的
2. 序列是一种数据库对象,可以用于生成唯一的整数
3. 序列常用来生成自增的主键
二、创建序列的语法
create sequence 序列名
start with 开始数字
increment by 间隔
maxvalue 最大值
minvalue 最小值
cycle|nocycle
cache 缓冲区大小|nocache
三、序列的两个伪列
nextval:序列的下一个整数
currval:序列的当前值
注意:第一次不能使用currval伪列
回滚操作不会使序列回滚
四、序列的更改
alter sequence 序列名
五、查看当前用户下有哪些序列
select * fromuser_sequences;
六、删除序列
drop sequence 序列名;
七、游标的概念
1. 为了处理SQL语句,oracle将在内存中分配一个区域,这个就是上下文区cursor
2. 上下文区域包含了该处理所需的信息,比如已经处理完的行数,指向被分析语句的指针,查询语句返回的数据行集
3. 游标的类型:隐式游标、显示游标和REF游标
八、隐式游标
1. 隐式游标是oracle自动定义的,用于DML语句和select...into..语句
2. 隐式游标的名称SQL
3. 通过隐式游标的属性,可以获得DML语句的结果
4. 当执行DML语句后,结果会保存在游标的属性中:
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN
九、隐式游标的使用例子
1.sql%count的使用
begin
delete from emp2;
dbms_output.put_line('删除了'||SQL%ROWCOUNT||'条数据');
end;
2.sql%found的使用
begin
update emp set sal=sal-500 where empno=7369;
if sql%found then
dbms_output.put_line('更新成功');
else
dbms_output.put_line('没有更新任何数据');
end if;
end;
十、显示游标
1. 显示游标是由程序员定义和命名的游标,用于多行查询语句
2. 上面所讲的隐式游标属性显示游标也有,不同之处在于游标属性的前缀是游标名而不是SQL
十一、 显示游标和隐式游标的区别
隐式游标 | 显式游标 |
PL/SQL维护,当执行查询时自动打开和关闭 | 在程序中显式定义、打开、关闭,游标有一个名字 |
游标属性前缀是SQL | 游标属性的前缀是游标名 |
属性%ISOPEN总是为FALSE | %ISOPEN根据游标的状态确定值 |
SELECT语句带有INTO子句,只有一行数据被处理 | 可以处理多行数据,在程序中设置循环,取出每一行数据。 |
十二、 显示游标的使用步骤
1. 声明游标
cursor 游标名 is 查询语句
2. 打开游标
在使用之前应该打开游标
open 游标名;
3. 从游标提取数据
从游标得到一行数据使用fetch命令。每一次提取数据后,游标都指向结果集的下一行
fetch 游标名 into 变量1,变量2
如果有多行数据,可以使用循环来提取数据
loop
fetch 游标名 into 变量1,变量2;
exit when 游标%notfound;
end loop;
4. 关闭游标
close 游标名;
十三、 演示游标的使用,查询员工的编号,姓名和薪水
declare
cursor mycur is select empno,ename,sal fromemp;
myempno emp.empno%type;
myename emp.ename%type;
mysal emp.sal%type;
begin
open mycur;
loop
fetch mycur into myempno,myename,mysal;
exit when mycur%notfound;---注意退出的位置
dbms_output.put_line('编号:'||myempno||'姓名:'||myename||'薪水'||mysal);
end loop;
close mycur;
end;
上面的代码使用了myempno.myename,mysal三个变量来接受游标的返回值,也可以这么做
定义游标类型的变量来接收数据
declare
cursor mycur is select empno,ename,sal fromemp;
my mycur%rowtype;-----注意这里的变化
begin
open mycur;
loop
fetch mycur into my;
exit when mycur%notfound;
dbms_output.put_line('编号:'||my.empno||'姓名:'||my.ename||'薪水'||my.sal);
end loop;
close mycur;
end;
十四、 循环游标
1. 这种使用游标的方式比较简单。
2. 用于游标for循环的游标按照正常的声明方式声明,但在使用时不需要显示的打开,关闭,取数据,测试数据的存在,定义存放数据的变量
3. 语法格式
for 变量 in 游标或者查询语句
loop
语句
end loop;
十五、 使用循环游标来简化前面的代码
declare
cursor mycur is select empno,ename,sal fromemp order by sal desc;
begin
for my in mycur loop
dbms_output.put_line('编号:'||my.empno||'姓名:'||my.ename||'薪水:'||my.sal);
end loop;
end;
上面的代码已经够简洁了,但是还有更简洁的,可以把声明部分去掉
begin
for my in (select empno,ename,sal from emporder by sal) loop
dbms_output.put_line('编号:'||my.empno||'姓名:'||my.ename||'薪水:'||my.sal);
end loop;
end;
十六、 使用游标生成部门员工报表
begin
for myd in (select * from dept) loop
dbms_output.put_line(myd.deptno||' '||myd.dname||' '||myd.loc);
dbms_output.put_line('------------------------------------------');
for mye in (select empno,ename,sal from emp where deptno = myd.deptno)loop
dbms_output.put_line(mye.empno||' '||mye.ename||' '||mye.sal);
end loop;
endloop;
end;
得到的结果如下面所示:
10 ACCOUNTING NEW YORK
------------------------------------------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
20 RESEARCH DALLAS
------------------------------------------
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000
30 SALES CHICAGO
------------------------------------------
7499 ALLEN 1600
7521 WARD 1250
7654 MARTIN 1250
7698 BLAKE 2850
7844 TURNER 1500
7900 JAMES 950
40 OPERATIONS BOSTON
------------------------------------------
十七、 带参数的游标
1. 游标只能接受传递的值,而不能返回值
2. 参数只定义类型,不定义大小
十八、 使用带参数的游标实现上面的功能
declare
cursor mye(mydeptno dept.deptno%type) isselect empno,ename,sal from emp where deptno = mydeptno;
begin
for myd in (select * from dept) loop
dbms_output.put_line(myd.deptno||' '||myd.dname||' '||myd.loc);
dbms_output.put_line('------------------------------------------');
for varmye in mye(myd.deptno) loop
dbms_output.put_line(varmye.empno||' '||varmye.ename||' '||varmye.sal);
end loop;
end loop;
end;
得到的结果还是上面的表格
十九、 使用游标来更新数据
DECLARE
CURSOR c_emp IS
SELECTempno,ename,sal FROM emp ORDER BY sal DESC FOR UPDATE;
v_increase NUMBER := 0;
v_new_sal NUMBER;
BEGIN
FOR r_emp IN c_emp LOOP
v_new_sal := r_emp.sal + v_increase;
UPDATE emp SET sal = v_new_sal WHERE CURRENT OF c_emp;
DBMS_OUTPUT.PUT_LINE(r_emp.empno||' '||r_emp.ename||'old salary='||r_emp.sal||' new salary= '||v_new_sal);
v_increase:=v_increase+100;
END LOOP;
END;
二十、 REF游标
在用户运行时动态的决定执行何种查询