重温Oracle(8)

第二十章 游标、函数

 

游标

游标是一种PL/SQL控制结构，可以对SQL语句的处理进行显示控制，便于对表的行数据逐条进行处理。

游标并不是一个数据库对象，只是存留在内存中。

 

操作步骤：

声明游标

打开游标

取出结果，此时的结果取出的是一行数据

关闭游标

到底那种类型可以把一行的数据都装起来？ROWTYPE类型，此类型表示可以把一行的数据都装起来。

 

使用for循环操作游标（比较常用）

Declare

  Cursor mycur is select * from emp where empno=1; --声明游标

  empInfo emp%ROWTYPE;

  cou number;

begin

  for empInfo in mycur LOOP

    cou:= mycur%ROWCOUNT; --获取游标的所有记录数

    dbms_output.put_line(cou||'雇员编号：'||empInfo.empno);

    dbms_output.put_line(cou||'雇员姓名：'||empInfo.ename);

  end LOOP;

end;

/

 

使用游标输出所有信息

Declare

  Cursor mycur is select * from emp; --创建游标

  empInfo emp%ROWTYPE;

begin

OPEN mycur; --游标操作使用循环，但是在操作之前必须先将游标打开

FETCH mycur INTO empInfo; --使游标向下移一行，即游标指向第一行并赋值给empInfo

While(mycur%FOUND) LOOP

    dbms_output.put_line('雇员编号：'||empInfo.empno);

dbms_output.put_line('雇员姓名：'||empInfo.ename);

FETCH mycur INTO empInfo; --修改游标，继续向下

End LOOP;

End;

/

 

LOOP…END LOOP循环游标

Declare

  Cursor mycur is select * from emp;

  empInfo emp%ROWTYPE;

begin

if mycur%ISOPEN then  --打开游标要先判断游标是否已经打开

  null;

else

  open mycur;

end if;

LOOP

  FETCH mycur INTO empInfo;

  EXIT WHEN mycur%NOTFOUND;

  dbms_output.put_line('雇员编号：'||empInfo.empno);

dbms_output.put_line('雇员姓名：'||empInfo.ename);

End LOOP;

End;

/

 

注意：

<!--[if !supportLists]-->1，  <!--[endif]-->在打开游标之前最好先判断游标是否已经打开。

<!--[if !supportLists]-->2，  <!--[endif]-->可以使用ROWCOUNT对游标所操作的行数进行记录。

 

函数

函数就是一个有返回值的过程。

定义一个函数：此函数可以根据雇员的编号查询出雇员的年薪

Create or replace function myfun(eno emp.empno%TYPE) return number

As

rsal number;

Begin

  Select (sal+nvl(comm,0))*12 info rsal from emp where empno=eno;

  Return rsal;

End;

/

调用：select myfun(7369) from dual;

 

第二十一章 存储过程

 

存储过程

与过程项目，存储过程是存在数据库中的一个对象

如果编译错误。可以用show errors or show errors procedure myproc

 

基本语法：

Create or replace procedure myproc

As

 I number;

Begin

 I:=100;

 Dbms_output.put_line(‘i=’||i);

End;

/

 

执行：exec myproc

 

带出入参数的语法：

Create or replace procedure myproc(dno in out dept.deptno%TYPE,name dept.dname%TYPE,dl dept.loc%TYPE) –传入参数，IN 带入参数，OUT 带出参数

As

 Cou number;

Begin

 Select count(deptno) into cou from dept where deptno=dno;

If cou=0 then –判断传入部门ID是否存在，不存在则插入

  Insert into dept(deptno,dname,loc) values(dno,name,dl);

  Dbms_output.put_line(‘部门插入成功’);

  Dno:=1;

Else

  Dbms_output.put_line(‘部门已存在，无法插入！’);

  Dno:=-1;

End if;

End;

/

 

说明：

IN：带入参数，默认的

IN OUT：带入带出参数

OUT：带出参数，不允许传入值

 

删除存储过程

Drop procedure myproc;