新手 ORACLE游标的一些使用

 

 

游标的主要作用是把select的数据，存到游标里，然后用的时候在读出来。

一般的步骤是：

1.定义游标：如 CURSOR c_student IS SELECT sname FROM student ORDER BY sname; 意思是建立了一个叫c_student的游标，它里面装的是SELECT sname FROM student的一条条记录，是根据ORDER BY sname来排列的

tips：cursor c_student is select sname || age from student 在fetch c_studnet into nameandage 这样读取的是sname和age合在一起的信息。当然这一步也完全可以在游标读取到内容再进行拼凑。只是说也它的可行性

2.打开游标：用open c_student来打开游标。%isopen c_student 来判断是否已经打开游标了

3.提取游标里的参数：用fetch来提取游标里值。先在declare里面写好一个参数，用来装cursor c_student.然后就用 fetch c_student into rec_stu装进去了。接着你用输出函数 DBMS_OUTPUT.PUT_LINE(REC_STU);就可以输出第一条信息了。当然你多加几句

fetch c_student into rec_stu;

DBMS_OUTPUT.PUT_LINE(REC_STU);这块代码，游标就一条一条帮你继续写出来。

4.然后用完了把游标 关掉吧 是个好习惯啊！

 

	create or replace procedure c_su is
	CURSOR c_student IS SELECT sname FROM student ORDER BY sname; 
	rec_stu student.sname%TYPE
	begin
		open c_student;
		fetch c_student into rec_stu;
		DBMS_OUTPUT.PUT_LINE(REC_STU);
		close c_student;
	end c_su;

 二 使用循环输出游标。

 

意思差不多，就是在读游标的时候，使用了一个循环。

首先介绍一下循环：

这次不用fetch来装游标里的值了，咱用for来装：for s_name in c_student，装好之后就用loop循环吧.

 

for s_name in c_student
		loop 
			DBMS_OUTPUT.put_line(s_name.sname);
        end loop;
		当然也可以这样来遍历：
		loop
			fetch c_studnet into rec_stu;
				DBMS_OUTPUT.put_line(rec_stu);
		end loop;

 两者的区别就是：

 

假如你定义的游标是 cursor1 is select * from student。

for s_value in cursor1 那么可以用s_name.xxx的形式，取出任何一个列的值。

而如果用fetch 。。into。。读取出多列，那么只能是这样写fetch cursor1 into col1，col2，col3。。

 

 

tip：注意一下顺序for s_name in c_student <> fetch c_student into rec_stu;别下手太快搞错了。

 

 

三 游标属性：游标属性 

 

%isopen     布尔型，如果游标打开,则为true 

 

%notfound   布尔型，如果没有返回行,则为true   

 

%found      布尔型，如果有返回行,则为true 

 

%rowcount   数值型，当前为止从工作返回的总行数 

根据这些属性我们可以给cursor再遍历的时候，加点判断，以求完美

 

loop
			fetch c_studnet into rec_stu;
				flag := c_student%isopen;
				
				DBMS_OUTPUT.put_line(rec_stu);
				countrow := c_studnet%rowcount;
				DBMS_OUTPUT.put_line(countrow);--每次执行打印一次所执行的行数
				
				exit when c_student%notfound;--如果没有返回行，退出
		end loop;
		DBMS_OUTPUT.put_line(flag);--判断游标打开否，true果，false未果
		DBMS_OUTPUT.put_line(countrow);--执行打印所有执行的行数

 四 隐式游标：

 

所有隐式的游标都叫SQL，是oracle自带的游标。简单的说你拿来用就是了。用的时候只要根据属性来判断，判断之后写你要操作的statement就可以了。写个例子：

 

	create or replace procedure testsqlcursor(
	v_id in testemp.empno%TYPE
	) is
	begin
		delete from testemp where empno = v_id;
			if sql%notfound then
				delete  from salary where ID = v_id;
			END IF;
	   DBMS_OUTPUT.put_line(sql%rowcount);
	end testsqlcursor;

 五 游标变量。我觉得游标变量要比直接定义一个显示游标要灵活，

 

有些书上说是 动态游标，显示和隐式的叫静态游标。

1.游标声明：

声明成有返回值的叫强类型定义，没有返回值的叫弱类型定义( 大概这意思)

游标变量的声明分成两步：

1.1 游标的声明 TYPE cursor1_type is ref cursor;

1.2 游标的定义 cur1 cursor1_type;

例子：声明一个强的，一个弱的

 

create or replace procedure TEST_REF_CURSOR
	is
		type return_type is record(
		v_name testemp.empno%type,
		v_job testemp.job%type
		);
		type cur1_type is ref cursor return return_type;
		type cur3_type is ref cursor return testemp%rowtype;
		type cur2_type is ref cursor;
		cur1 cur1_type;--用cur1_type定义了一个cursor
		cur2 cur2_type;--同上
		cur3 cur3_type;--同上
	begin
		  commit;
	end TEST_REF_CURSOR;

 

 

 

2.打开游标 OPEN emp_refcur FOR

    SELECT employees.employee_id, employees.first_name||employees.last_name, employees.job_id 

  3.遍历游标

 

还是老方法 fetch cursor_name into  variable_name

然后就loop吧loop完别忘了end loop就行，建议写完loop就直接写end loop。

4.关闭游标

养成物归原主的好习惯，用完了，你得关了它。

5.例子

还是写个例子吧

 

create or replace procedure TEST_REF_CURSOR is

	  type return_type is record(
		v_name testemp.empno%type,
		v_job  testemp.job%type);
	  type cur3_type is ref cursor return testemp%rowtype;
	  cur3 cur3_type;
	  rec  testemp%rowtype;

	begin
	  open cur3 for
		select * from testemp order by testemp.empno;
		loop
		  fetch cur3 into rec;
		  exit when cur3%notfound;
		  dbms_output.put_line(rec.ename||rec.job);
		end loop;
	  close cur3;		--如果你要用游标对其他表进行处理的话，你只要再次打开游标就是了，当然你定义得是弱类型的游标，顺便把记录变量定义--一下就可以使用了。
	end TEST_REF_CURSOR;

 

给个例子弱类型游标变量：

 

create or replace procedure TEST_REF_CURSOR is

		  type return_type is record(
			v_name testemp.empno%type,
			v_job  testemp.job%type);
		  type cur3_type is ref cursor return testemp%rowtype;
		  type cur2_type is ref cursor;
		  cur2 cur2_type;
		  cur3 cur3_type;
		  rec  testemp%rowtype;
		  rec2 salary%rowtype;

		begin
		  open cur3 for
			select * from testemp order by testemp.empno;
		  loop
			fetch cur3
			  into rec;
			exit when cur3%notfound;
			-- dbms_output.put_line(rec.ename||rec.job);
		  end loop;
		  close cur3;
		  open cur2 for
			select * from testemp order by testemp.empno;
		  loop
			fetch cur2
			  into rec;
			exit when cur2%notfound;
			dbms_output.put_line(rec.deptno || rec.empno || rec.ename || rec.job);
		  end loop;
		  close cur2;
		  open cur2 for
			select * from salary where salary.id = 3 order by salary.id;
		  loop
			fetch cur2
			  into rec2;
			exit when cur2%notfound;
			dbms_output.put_line(rec2.sal);
		  end loop;
		  close cur2；

		end TEST_REF_CURSOR;

 

一些补充：

 

	TYPE MYTYPE (
		MYTYPE1 NUMBER,
		MYTYPE2 VARCHAR2,
		MYTYPE3 VARCHAR
	);

 这样就算是定义了一个类型

然后声明一个为之对应的类型比如

mytype_1 MYTYPE;

那MYTYPE_1就可以用这个MYTYPE类型了

 

TABLENAME.COLNAME%TYPE
	declare
	myparameter test.name%type; --是说明你用test表里name列的类型
TABLENAME%ROWTYPE
	DECLARE
	REC TEST%ROWTYP;--说明rec的类型和test表里各个列的类型是一样的。