PL/SQL 第6章 访问Oracle

第6章 访问Oracle

记录2012-5-22 20:00

6.1检索单行数据

注意：当在PL/SQL块中直接使用SELECT INTO语句时，该语句必须返回一条数据，并且只能返回一条数据。

1.使用标量变量接收数据

在PL/SQL中代码
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;
输出结果
雇员名：SCOTT
雇员薪水：2000

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;
输出结果
雇员名：SCOTT
雇员薪水：2000

3.where子句使用注意事项
  在where子句中使用变量时注意，变量名不能与列名相同，否则会触发TOO_MANY_ROWS例外
declare
empno number(6):=7788;
v_ename varchar2(10);
begin
  select ename into v_ename from emp where empno=empno;
  end;

6.2操纵数据

   在PL/SQL块中不仅可以嵌入SELECT语句，也可以嵌入DML.另外，通过使用SQL游标属性，还可以取得DML语句所作用的行数。

6.2.1插入数据

    插入数据用insert语句，即可以使用values子句，也可以使用子查询。

示例一：在PL/SQL块中使用values子句插入数据

begin
  v_deptno:=&no;
  v_dname:='&name';
  insert into dept(deptno,dname)
  values (v_deptno,v_dname);
  end;
 
select * from dept where deptno=50;

6.2.2 更新数据
6.2.3 删除数据
6.2.4 SQL游标
      当执行SELECT,INSERT,UPDATE以及DELETE语句时，Oracle Server会为这些SQL语句分配相应的上下文区(Context Area).并且Oracle使用

上下文区解析并执行相应的SQL语句，而游标是指向上下文区的指针。在Oracle数据库中，游标包含隐含游标和显示游标两种类型。其中隐含游

标又被称为SQL游标，它专门用于处理SELECT INTO ,INSERT,UPDATE以及DELETE语句；而显示游标则用于处理多行的SELECT语句。当在PL/SQL块

中执行INSERT、UPDATE以及DELETE语句时，为了取得DML语句作用的结果，必须要使用SQL游标属性，SQL游标属性，SQL游标包括SQL%

found,SQL%NOTFOUND,SQL%ROWCOUNT,SQL%ISOPEN等四种属性。

1.SQL%ISOPEN
  游标属性SQL%ISOPEN用于确定SQL游标是否已经打开。当在PL/SQL块中执行SELECT INTO,INSERT,UPDATE以及DELETE语句时，Oracle会隐含地

打开游标，并且在语句执行完成之后会隐含地关闭游标。

2.SQL%FOUND
  游标属性SQL%FOUND用于确定SQL语句执行是否成功。注意，SQL语句执行是否成功是否有作用行来判断，当SQL语句有作用行时，其属性值为

TRUE;否则为FALSE

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
    游标属性SQL%NOTFOUND用于确定SQL语句执行是否成功。注意，SQL语句执行是否成功是否有作用行来判断，当SQL语句有作用行时，其属性

值为false;否则为true

declare
v_deptno emp.deptno%type:=&no;
begin
  update emp set sal=sal*2.1 where deptno=v_deptno;
  if SQL%notFOUND THEN
      dbms_output.put_line('该部门不存在编号');
    else
      dbms_output.put_line('语句执行成功');
      end if;
end;

4.SQL%ROWCOUNT
  游标属性SQL%ROWCOUNT用于返回SQL语句所作用的总计行数

declare
v_deptno emp.deptno%type:=&no;
begin
  update emp set sal=sal*2.1 where deptno=v_deptno;
   dbms_output.put_line('修改了'|| SQL%rowcount ||'行数');
end;

6.3事务控制语句

   当编写PL/SQL程序时，不仅可以直接嵌入SELECT和DML语句，也可以直接嵌入事务控制语句。在Oracle数据库中，事务控制语句
包括COMMIT,ROLLBACK以及SAVEPOINT等三种语句。

   示例一：在PL/SQL块中使用COMMIT和ROLLBACK语句

   COMMIT语句用于提交事务，从而确认事务变化；ROLLBACK语句用于回退事务，从而取消事务变化。
     
declare
v_sal emp.sal%type:=&salary;
v_ename emp.ename%type:='&name';
begin
  update emp set sal=v_sal where ename=v_ename;
  commit;
 exception
   when others then
     rollback;
 end;

在执行了以上PL/SQL块之后，会根据输入的雇员名和雇员工资更新特定员工的工资，并且在更新了工资之后提交事务。而如果执行该PL/SQL块

出现例外，则会取消事务变化。

示例二：在PL/SQL块中使用ROLLBACK和SAVEPOINT语句

SAVEPOINT语句用于设置保存点，通过与ROLLBACK语句结合使用取消部分事务。

begin
  insert into new_emp (id) values(1);
  savepoint a1;
  insert into new_emp (id) values(2);
  savepoint a2;
  insert into new_emp (id) values(3);
  savepoint a3;
  rollback to a2;
  commit;
  end;
当执行以上PL/SQL块之后，应该为new_emp表实际插入了几条数据呢？ 2条