PL/SQL之访问Oracle

----检索单行数据
----1.使用标题变量接收数据

declare
  v_ename  emp.ename%type;
  v_sal    emp.sal%type;
begin
  select ename, sal
    into v_ename,v_sal
    from emp
   where empno = &no;
  dbms_output.put_line('雇员名:' || v_ename);
  dbms_output.put_line('雇员薪水:' || v_sal);
end;

----2.使用记录变量接收数据

declare
  type emp_record_type is record(
    ename  emp.ename%type,
    sal emp.sal%type);
  emp_record emp_record_type;
begin
  select ename, sal into emp_record from emp where empno = &no;
  dbms_output.put_line('雇员名:' || emp_record.ename);
  dbms_output.put_line('雇员薪水:' || emp_record.sal);
end;

-----操纵数据

----1.插入数据

create table tmp_table (t_no number(4),t_name varchar2(20))
select * from tmp_table

declare
v_t_no tmp_table.t_no%type;
v_t_name tmp_table.t_name%type;
begin
v_t_no:=&no;
v_t_name:='&name';
insert into tmp_table (t_no,t_name) values (v_t_no,v_t_name);
end;

insert into tmp_table values (001,'小王');

----2.子查询插入相当于复制

create table tmp_table_1 as select * from tmp_table where rownum<3

declare
  v_t1_no tmp_table_1.t_no%type := &no;
begin
  insert into tmp_table
    select * from tmp_table_1 where t_no = v_t1_no;
end;

-----更新数据

----1.表达式更新
declare
v_nsrdzdah dj_nsrxx.nsrdzdah%type:=&no;
v_nsrmc  dj_nsrxx.nsrmc%type:='&name';
begin
update dj_nsrxx set nsrmc=v_nsrmc
where nsrdzdah=v_nsrdzdah;
end;

----2.子查询更新
declare
v_ename emp.ename%type:='&name';
begin
update emp set (sal,comm)=(select sal,comm from emp where ename=v_ename)
where jo=(select job from emp where ename=v_ename);
end;

-----删除数据
----1.使用变量删除数据
declare
v_deptno dept.deptno%type:=&no;
begin
delete from dept where deptno=v_deptno;
end;

----2.使用子查询删除
declare
v_ename emp.ename%type:='&name';
begin
delete from emp where deptno=(select deptno from emp where ename=v_ename);
end;


------SQL游标

----1.SQL%ISOPEN
----2.SQL%FOUND
declare
v_deptno emp.deptno%type:=&no;
begin
update emp set sal=sal*1.1
where deptno=v_deptno;
if sql%found then
dbms_output.put_line('语句执行成功');
else
dbms_output.put_line('不存在');
end if ;
end ;
----3.SQL%NOTFOUND(跟上面刚好相反)
----4.SQL%ROWCOUNT
declare
v_deptno emp.deptno%type:=&no;
begin
update emp set sal=sal*1.1;
where deptno=v_deptno;
dbms_output.put_line('修改了'|| SQL%ROWCOUNT ||'行');
end;


-----事务控制语句

----commit/rollback
declare
  s_sal     emp.sal%type := &salary;
  v_ename   emp.ename%type := '&ename';
  commit   ;
  exception when others then rollback;
end;

----rollback/savepoint
begin
insert into temp values(1);
savepoint a1;
insert into temp values(2);
savepoint a2;
insert into temp values(3);
savepoint a3;
rollback to a2;
commit;
end;