savepoint--保存点，
begin
insert into temp_hwj(id) values(2);
savepoint a;
insert into temp_hwj(id) values(2);
savepoint b;
insert into temp_hwj(id) values(2);
savepoint c;
insert into temp_hwj(id) values(2);
rollback b;--回滚后 c保存掉失效
commit;
end;
----------------------
conn sys/tiger 切换用户
一，游标(cursor)
DML/Select执行时的内存空间(context area)
cursor 是指向这个内存空间的指针
隐式游标/显示游标(自定义游标名称)
SQL%ROWCOUNT/mycur%ROWCOUNT
%ROWCOUNT/%FOUND/%NOTFOUND/%ISOPEN
显示游标的使用步骤:
1.定义游标
2.打开游标
3.操作游标(从游标中获取数据)
4.关闭游标
declare
v_ame%type;
v_sal emp.sal%type;
--定义游标
CURSOR emp_cursor IS
select ename,sal from emp;
begin
--打开游标
open emp_cursor;
--循环取出游标中的记录 每次取一条，移动游标
loop
fetch emp_cursor into v_name,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line(v_name||' ''s salary is'||v_sal);
end loop;
close emp_cursor;--游标关闭，游标就销毁了
end;
---通过游标传递参数------
declare
cursor emp_cursor(v_no number) is
select ename,sal from emp where deptno=v_no;
emp_record emp%rowtype; 
begin
open emp_cursor(20);--参数
loop
fetch emp_cursor into ame,emp_record.sal;
exit when emp_cursor%notfound;
dbms_output.put_line(ame||'''s salary' ||emp_record.sal);
end loop;
close emp_cursor;
end;
----
游标变量
declare
type my_cursor_type is ref  cursor return dept%rowtype;
dept_cursor my_cursor_type;
dept_record dept%rowtype;
begin
open dept_cursor for select * from dept;
loop
fetch dept_cursor into dept_record;
exit when dept_cursor%notfound;
dbms_output.put_line(dept_record.dname||','||dept_record.loc);
end loop;
close dept_cursor;
end; 
---------------------
type cursor_type is ref cursor;-弱类型定义游标---
---------------------
declare
type record_type is record(
id number;
description varchar2(100)
);
myrecord record_type;
type cursor_type is ref cursor;
mycursor cursor_type;
begin
end;
------while--------------
fetch mycursor into myrecord---只有fetch才能使指针下移---
while mycursor%found loop
//-----
fetch mycursor into myrecord;--需要再次移动指针----
end loop;
接收数据不同字符
使用同一个游标 记录不同结构的数据
----------------
针对游标的for循环的简化方式(不用open的游标才能用，只有在
声明游标的时候就绑定sql语句)
declare
CURSOR mycursor(v_no number)
is select ename,sal from emp where deptno=v_no;
begin
for myrecord in mycursor(10) loop
dbms_output.put_ame||','||myrecord.sal);
end loop;
end;
----
-------------------------
事务的起点DML操作，select语句不启动事务
SQL>select * from emp --不启动事务--
for update;---启动事务--
-------更新或删除游标行-------------------
declare
cursor mycursor is
select * from h_emp for update;
begin
for myrecord in mycursor loop
if myrecord.job='ANALYST' then
update h_emp set sal=sal*1.2
where current of mycursor; --游标的当前行进行更新
dbms_output.put_line(myrecord.sal||','||myrecord.sal*1.2); 
else
dbms_output.put_line(myrecord.sal||'no change');
end if;
end loop;
commit;
end;
-----------------------------------
二，异常(exception)
declare
v_sal emp.sal%type;
begin
select sal into v_sal
from emp wehre deptno=&no;
exception
when no_data_found then
--//
when too_many_rows then
--//
when orthers then
--//
end;
---非预订异常
declare
e_child_record exception;
PRAGMA exception_init(e_child_record,-2292);--2292是错误码-
begin
plsql配置oracle主目录delete h_dept where deptno=10;
exception
when e_child_record then
dbms_output.put_line('not option');
when others then
dbms_output.put_line('others');
end;
--注意：在创建表使用
create table h_dept as select * from dept;
--这样的语句是不能复制边的约束条件的
-
-给表添加表的约束添加
alter table h_dept add constraint d_pk primary key(deptno);
alter table h_emp add constraint d_fk foreign key(deptno)
references h_dept(deptno);
----用户自定义异常
declare
v_empno pno%type:=&no;
v_comm h_empm%type;
e_null exception;
--自定义异常名字和编码的关联
pragma exception_init(e_null,-20101);
begin
select comm into v_comm
from h_emp where empno=v_empno;
if v_comm is null then
raise e_null;--抛出异常
else
update h_emp set comm=comm+100 where empno=v_empno;
end if;
exception
when e_null then
dbms_output.put_line('奖金空了');
when others then
dbms_output.put_line('others');
end;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。