Oracle 之游标学习总结

最新推荐文章于 2021-04-13 16:34:28 发布

Rhys-Oracle

最新推荐文章于 2021-04-13 16:34:28 发布

阅读量1.2k

点赞数

分类专栏： ORACLE 之 PL /SQL

本文链接：https://blog.csdn.net/rhys_oracle/article/details/8806696

版权

ORACLE 之 PL /SQL 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

Oracle 之游标学习总结

一）记录

SYS@orcl#r
  1  declare
  2  type emp_record_type is record(
  3  v_empno emp.empno%type,
  4  v_ename emp.ename%type,
  5  v_sal number(7,2)
  6  );
  7  emp_record emp_record_type;
  8  begin
  9  select empno,ename,sal into emp_record from emp where empno=7844;
 10  dbms_output.put_line('empno:'||emp_record.v_empno);
 11  dbms_output.put_line('ename:'||emp_record.v_ename);
 12  dbms_output.put_line('sal:'||emp_record.v_sal);
 13* end;
empno:7844
ename:TURNER
sal:1500

PL/SQL 过程已成功完成。

SYS@orcl#declare

SYS@orcl#r
  1  declare
  2  emp_record emp%rowtype;
  3  begin
  4  select * into emp_record from emp where empno=7844;
  5  dbms_output.put_line('empno:'||emp_record.empno);
  6  dbms_output.put_line('ename:'||emp_record.ename);
  7  dbms_output.put_line('sal:'||emp_record.sal);
  8* end;
empno:7844
ename:TURNER
sal:1500

PL/SQL 过程已成功完成。

SYS@orcl#

二）索引表

SYS@orcl#r
  1  declare
  2  type type_emp is table of emp%rowtype index by pls_integer;
  3  v_type_emp type_emp;
  4  begin
  5  select * into v_type_emp(1) from emp where empno=7844;
  6  dbms_output.put_line('v_type_emp(1)='||v_type_emp(1).ename);
  7* end;
v_type_emp(1)=TURNER

PL/SQL 过程已成功完成。

SYS@orcl#

  1  declare
  2  type emp_type_record is table of emp%rowtype index by pls_integer;
  3  type one_type is table of varchar2(20) index by pls_integer;
  4  emp_type emp_type_record;
  5  one one_type;
  6  begin
  7  select * into emp_type(1) from emp where empno=7844;
  8  one(1):='xiaohai';
  9  dbms_output.put_line('one(1)='||one(1));
 10  dbms_output.put_line('emp_type='||emp_type(1).ename);
 11* end;
SYS@orcl#/
one(1)=xiaohai
emp_type=TURNER

PL/SQL 过程已成功完成。

SYS@orcl#

SQL> r
  1  declare
  2  type emp_index_table is table of emp%rowtype index by pls_integer;
  3  type test_index_table is table of varchar2(20) index by binary_integer;
  4  type test2_index_table is table of number(7,2) index by varchar2(20);
  5  emp_index emp_index_table;
  6  test_index test_index_table;
  7  test2_index test2_index_table;
  8  begin
  9  select * into emp_index(1) from emp where empno=7788;
 10  dbms_output.put_line('ename='||emp_index(1).ename||'######'||'sal='||emp_index(1).sal);
 11  test_index(1):='hello world';
 12  test_index(2):='hello oracle';
 13  test_index(-1):='hello Rhys';
 14  dbms_output.put_line(test_index(1));
 15  dbms_output.put_line(test_index(2));
 16  dbms_output.put_line(test_index(-1));
 17  test2_index('hello'):=210;
 18  test2_index('oracle'):=10000;
 19  dbms_output.put_line(test2_index('hello'));
 20  dbms_output.put_line(test2_index('oracle'));
 21  dbms_output.put_line(test2_index.first);
 22  dbms_output.put_line(test2_index.last);
 23* end;
ename=SCOTT######sal=8000
hello world
hello oracle
hello Rhys
210
10000
hello
oracle

PL/SQL procedure successfully completed.

SQL>

注意：可以使用变量名称.first或是变量名称.last格式得到第一个或是最后一个键值，如果是两个键值中有一个是大写，那么先显示大写，后显示另一个键值，如果两个都是大写那么还是按照字符顺序进行排序。

eg：

SQL> declare
  2  type emp_index_table is table of emp%rowtype index by pls_integer;
  3  type test_index_table is table of varchar2(20) index by binary_integer;
  4  type test2_index_table is table of number(7,2) index by varchar2(20);
  5  emp_index emp_index_table;
  6  test_index test_index_table;
  7  test2_index test2_index_table;
  8  begin
  9  select * into emp_index(1) from emp where empno=7788;
 10  dbms_output.put_line('ename='||emp_index(1).ename||'######'||'sal='||emp_index(1).sal);
 11  test_index(1):='hello world';
 12  test_index(2):='hello oracle';
 13  test_index(-1):='hello Rhys';
 14  dbms_output.put_line(test_index(1));
 15  dbms_output.put_line(test_index(2));
 16  dbms_output.put_line(test_index(-1));
 17  test2_index('hello'):=210;
 18  test2_index('Oracle'):=10000;
 19  dbms_output.put_line(test2_index('hello'));
 20  dbms_output.put_line(test2_index('Oracle'));
 21  dbms_output.put_line(test2_index.first);
 22  dbms_output.put_line(test2_index.last);
 23  end;
 24  /
ename=SCOTT######sal=8000
hello world
hello oracle
hello Rhys
210
10000
Oracle
hello

PL/SQL procedure successfully completed.

SQL>

declare
type emp_index_table is table of emp%rowtype index by pls_integer;
type test_index_table is table of varchar2(20) index by binary_integer;
type test2_index_table is table of number(7,2) index by varchar2(20);
emp_index emp_index_table;
test_index test_index_table;
test2_index test2_index_table;
begin
select * into emp_index(1) from emp where empno=7788;
dbms_output.put_line('ename='||emp_index(1).ename||'######'||'sal='||emp_index(1).sal);
test_index(1):='hello world';
test_index(2):='hello oracle';
test_index(-1):='hello Rhys';
dbms_output.put_line(test_index(1));
dbms_output.put_line(test_index(2));
dbms_output.put_line(test_index(-1));
test2_index('Hello'):=210;
test2_index('Oracle'):=10000;
dbms_output.put_line(test2_index('Hello'));
dbms_output.put_line(test2_index('Oracle'));
dbms_output.put_line(test2_index.first);
dbms_output.put_line(test2_index.last);
end;
/

ename=SCOTT######sal=8000
hello world
hello oracle
hello Rhys
210
10000
Hello
Oracle

PL/SQL procedure successfully completed.

SQL>

三）varray变长数组类型：

SQL> declare
  2  type emp_varray is varray(20) of varchar2(20);
  3  v_varray emp_varray:=emp_varray('1','2','3');
  4  begin
  5  v_varray(1):='Rhys';
  6  v_varray(2):='learn';
  7  v_varray(3):='oracle';
  8  dbms_output.put_line(v_varray(1)||'$$$'||v_varray(2)||'$$$'||v_varray(3));
  9  end;
 10  /
Rhys$$$learn$$$oracle

PL/SQL procedure successfully completed.

SQL>

四）游标

oracle 中有显示游标和隐式游标

显示游标有如下属性：

%isopen；判断是否游标打开，打开返回true，否则返回false

%found ：判断游标是否获得了数据记录，如果是返回true否则返回false

%nofount：判断游标是否没有获得记录，如果没有获得记录那么返回true否则返回false

%rowcount：计数器，判断fetch获得的次数

SCOTT@orcl#declare
  2  cursor emp_cursor is select * from emp order by empno;
  3  v_emp emp%rowtype;
  4  begin
  5   if emp_cursor%isopen then
  6      dbms_output.put_line('the cursor is opened by oracle');
  7   else
  8      dbms_output.put_line('the cursor is not open,it is must open first!');
  9      open emp_cursor;
 10      dbms_output.put_line('the cursor is opened by oracle');
 11        loop
 12        fetch emp_cursor into v_emp;
 13             if emp_cursor%found then
 14           dbms_output.put_line('empno='||v_emp.empno);
 15           dbms_output.put_line('ename='||v_emp.ename);
 16                dbms_output.put_line('job='||v_emp.job);
 17                dbms_output.put_line('sal='||v_emp.sal);
 18              else
 19             exit;
 20              end if;
 21         end loop;
 22    end if;
 23  close emp_cursor;
 24  end;
 25  /
the cursor is not open,it is must open first!
the cursor is opened by oracle
empno=7000
ename=MILLER
job=hello
sal=2000
empno=7369
ename=SMITH
job=CLERK
sal=2800
empno=7499
ename=ALLEN
job=SALESMAN
sal=3600
empno=7521
ename=WARD
job=SALESMAN
sal=3250
empno=7566
ename=JONES
job=MANAGER
sal=2975
empno=7654
ename=MARTIN
job=SALESMAN
sal=3250
empno=7698
ename=BLAKE
job=MANAGER
sal=2850
empno=7782
ename=CLARK
job=MANAGER
sal=2450
empno=7788
ename=SCOTT
job=ANALYST
sal=4000
empno=7839
ename=KING
job=PRESIDENT
sal=5000
empno=7844
ename=TURNER
job=hello
sal=3500
empno=7876
ename=ADAMS
job=hello
sal=3100

PL/SQL 过程已成功完成。

SCOTT@orcl#

隐士游标有如下属性和显示游标不同

%isopen，oracle自己管理永远返回false

eg：

SCOTT@orcl#declare
  2  v_emp emp%rowtype;
  3  begin
  4  select * into v_emp from emp where empno=7788;
  5  if sql%isopen then 
  6   dbms_output.put_line('no');
  7  else 
  8   dbms_output.put_line('yes');
  9  end if;
 10  end;
 11  /
yes

PL/SQL 过程已成功完成。

SCOTT@orcl#

%found：判断dml和selectinto对数据时否由影响，如果dml对数据有影响那么返回true，如果select into对数据有影响那么返回true

eg：

SCOTT@orcl#declare
  2  v_emp emp%rowtype;
  3  begin
  4  select * into v_emp from emp where empno=7788;
  5  if sql%found then
  6  dbms_output.put_line('effect');
  7  end if;
  8  update emp  set sal=sal+20 where empno=7788;
  9  if sql%found then
 10  dbms_output.put_line(v_emp.ename||'###'||v_emp.sal);
 11  end if;
 12  end;
 13  /
effect
SCOTT###4000

PL/SQL 过程已成功完成。

SCOTT@orcl#

%notfound：dml操作没有影响数据或select into对数据没有影响那么为true

%rowcount：可以反应dml对数据操作影响的数量：

eg：

SCOTT@orcl#declare
  2  v_emp emp%rowtype;
  3  begin
  4  select * into v_emp from emp where empno=7788;
  5  if sql%found then
  6  dbms_output.put_line('effect');
  7  dbms_output.put_line(sql%rowcount);
  8  end if;
  9  update emp  set sal=sal+20;
 10  if sql%found then
 11  dbms_output.put_line(v_emp.ename||'###'||v_emp.sal);
 12  dbms_output.put_line(sql%rowcount);
 13  end if;
 14  end;
 15  / 
effect
1
SCOTT###4020
12

PL/SQL 过程已成功完成。

SCOTT@orcl#