Oracle游标和存储过程

这里写目录标题

• • 游标
  • • 显式游标
    • • for循环游标
      • fetch 游标 loop循环
      • fetch 游标while循环
      • for循环游标加if判断
    • 隐式游标
    • 游标传值
    • 动态游标
  • 存储过程
  • • 有返回值
    • • 创建
      • 调用
    • 无返回值
    • • 创建
      • 调用

游标

显式游标

显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；

for循环游标

declare
	-- 定义游标
	cursor c_job is  --cursor 声明游标
	select empno, ename, job, sal from SCOTT.EMP WHERE job='MANAGER';
	-- 定义游标变量，用来接收c_job中每一行的数据
	c_row c_job%rowtype;
BEGIN
	for c_row in c_job loop
dbms_output.put_line(c_row.empno||'--'||c_row.ename||'--'||c_row.job||'--'||c_row.sal);
end loop;
end;
           

fetch 游标 loop循环

declare
	-- 定义游标
	cursor c_job is 
	select empno, ename, job, sal from SCOTT.EMP WHERE job='MANAGER';
	-- 定义游标变量，用来接收c_job中每一行的数据
	c_row c_job%rowtype;
BEGIN
	open c_job; -- 打开游标
		loop
			--抓取游标中一行数据，赋值给c_row
			fetch c_job into c_row;
			exit when c_job%notfound; --c_job%notfound
			dbms_output.put_line(c_row.empno||'--'||c_row.ename||'--'||c_row.job||'--'||c_row.sal);--控制打印
		end loop;
	close c_job; -- 关闭游标
end;
           

fetch 游标while循环

declare
	cursor csr_dept is select dname from SCOTT.DEPT;
	row_dept csr_dept%rowtype;
begin
	open csr_dept;
		fetch csr_dept into row_dept;
			while csr_dept%found loop
				dbms_output.put_line('部门名称：'||row_dept.dname);
				fetch csr_dept into row_dept;
			end loop;
	close csr_dept;
end;
           

for循环游标加if判断

DECLARE
	cursor csr_update is select * from SCOTT.EMP1 FOR UPDATE OF SAL;
	empInfo csr_update%rowtype; --定义一个游标变量，该类型为游标csr_update中的一行数据
	salInfo SCOTT.EMP1.SAL%type;--salInfo 为EMP1表中的SAL列的类型
BEGIN
	FOR empInfo in csr_update loop
		if empInfo.sal<1500 then
			salInfo:=empInfo.sal*1.2;
		else if empInfo.sal<2000 THEN
			salInfo:=empInfo.sal*1.5;
		else if empInfo.sal<3000 THEN
			salInfo:=empInfo.sal*2;
		end if;
		end if;
		end if;
		update SCOTT.EMP1 SET SAL=salInfo WHERE CURRENT OF csr_update;
		--WHERE CURRENT OF是用来更新被锁住的记录的，执行游标遍历时的当前行就好像for(int i=0;i++;i<10){} where current of与“i”的功能相似。
	end loop;
end;
           

隐式游标

对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，隐式游标的名字为SQL，这是由ORACLE 系统定义的。

**格式：**SQL%

begin
	if sql%isopen then
		dbms_output.put_line('sql游标已经打开');
	else
		dbms_output.put_line('sql游标未打开');
	end if;
end;
declare 
	e_count number;
begin
	select count(*) into e_count from SCOTT.EMP; 
	dbms_output.put_line('游标记录数：'||sql%rowcount);
end;
           

游标传值

declare
	cursor c_dept(p_deptno NUMBER) is select empno, ename, job, sal from SCOTT.EMP WHERE deptno=p_deptno;
	row_emp c_dept%rowtype;
begin
	for row_emp in c_dept(20) loop
		dbms_output.put_line(row_emp.empno||'--'||row_emp.ename||'--'||row_emp.job||'--'||row_emp.sal);
	end loop;
end;

           

动态游标

游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可。

DECLARE
   --定义一个游标数据类型
   TYPE emp_cursor_type IS REF CURSOR;
   --声明一个游标变量
   c1 EMP_CURSOR_TYPE;
   --声明两个记录变量
   v_emp_record employees%ROWTYPE;
   v_reg_record regions%ROWTYPE;
 
BEGIN
   OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20;
   LOOP
      FETCH c1 INTO v_emp_record;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇佣日期是'
                            ||v_emp_record.hire_date);
   END LOOP;
   --将同一个游标变量对应到另一个SELECT语句
   OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）;
   LOOP
      FETCH c1 INTO v_reg_record;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示'
                            ||v_reg_record.region_name);
   END LOOP;
   CLOSE c1;
END;
           

存储过程

有返回值

创建

-- 输入值num 5 , 输出 0+1+2+3+4+5=15
create or replace procedure testloop(num in NUMBER,sumValue out number) as
BEGIN
	declare
		orinum NUMBER:=0;
		sumadd NUMBER:=0;
	begin
		loop
			IF(orinum>num) THEN
				sumValue:=sumadd;
				dbms_output.put_line('存储过程内计算的最后结果：'||sumValue);
				exit;
			end if;
			dbms_output.put_line('start orinum:'||orinum||'  sumadd:'||sumadd);
			sumadd:=sumadd+orinum;
			orinum:=orinum+1;
			dbms_output.put_line('end orinum:'||orinum||'  sumadd:'||sumadd);
		end loop;
	end;
END;
           

调用

DECLARE
	num Number;
	sumValue NUMBER;
BEGIN
	num:=5;
	sumValue:=0;
	TESTLOOP(num, sumValue); -- 调用存储过程
	dbms_output.put_line('result:'||sumValue);
END;
           

无返回值

创建

create or replace procedure addbook(bookName in VARCHAR2, typeId in NUMBER) AS
BEGIN
	declare 
		maxId NUMBER;
	BEGIN
		select MAX(ID) into maxId from T_BOOK ;
		insert into t_book values(maxId+1, bookName, typeId);
	END;
END;
           

调用

--VARCHAR2(32)  VARCHAR(32) 两者区别在于varchar2是可变的，varchar是固定大小
DECLARE
	bookName VARCHAR2(32);
	bookTypeId NUMBER;
BEGIN
	bookName:='武人琴音';
	bookTypeId:=1;
	ADDBOOK(bookName,bookTypeId);  -- 调用存储过程
END;