You can either declare an explicit cursor first and then define it later in the same block, subprogram, or package, or declare and define it at the same time.
An explicit cursor declaration, which only declares a cursor, has this syntax:
TYPE type_name IS TABLE OF {column_type | variable%TYPE | table.column%TYPE} [NOT NULL] | table.%ROWTYPE [INDEX BY BINARY_INTEGER]; identifier type_name;
实践
实践1-书写一个最简单的块，运行并查看结果
先设定 SQLPLUS 的环境变量，如果不指定默认值为不输出，设定后用 show 来验证。
set serveroutput on show serveroutput
该实验的目的是掌握简单的 pl/sql 语法，执行一个最简单的匿名块。
书写一个最简单的块，将字符串输出到屏幕。使用的是 sqlplus 输出 Hello world。
begin dbms_output.put_line('-----------------Begin------------------'); dbms_output.put_line('hello world'); dbms_output.put_line('-----------------End------------------'); end; /
DECLARE v_sal NUMBER (9,2); g_monthly_sal v_sal%TYPE := 10; BEGIN /* Compute the annual salary based on the monthly salary input from the user */ v_sal := g_monthly_sal * 12; dbms_output.put_line(v_sal); END; -- This is the end of the block /
declare v1 emp.ename%type; v2 emp.sal%type; begin select ename,sal into v1,v2 from emp where empno=7900; dbms_output.put_line(v1); dbms_output.put_line(v2); end; /
删除表中的数据并打印删除的行数
DML 语句和 SQL 相同，使用隐式游标的属性来控制 DML，有四种隐式的游标：
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN
declare v1 emp.deptno%type :=20; v2 number; begin delete emp where deptno=v1; v2:=sql%rowcount; dbms_output.put_line('delete rows :'); dbms_output.put_line(v2); rollback; end; /
执行结果
SCOTT@testdb>select * from emp where deptno=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20
DECLARE v1 DATE := to_date('12-11-1990','mm-dd-yyyy'); v2 BOOLEAN; BEGIN IF MONTHS_BETWEEN(SYSDATE,v1) > 5 THEN v2 := TRUE; dbms_output.put_line('True'); ELSE v2 := FALSE; dbms_output.put_line('False'); END IF; end; /
结果
True
PL/SQL procedure successfully completed.
实践6-在块中的分支操作CASE语句
该实验的目的是掌握在 pl/sql 块中使用 CASE 语句进行分支操作。
DECLARE v1 CHAR(1) := UPPER('&v1'); v2 VARCHAR2(20); BEGIN v2 :=CASE v1 WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' ELSE 'No such grade' END; DBMS_OUTPUT.PUT_LINE (v1 || ' is ' || v2); END; /
Null 的逻辑运算真值表
True and null 结果为 null
Flase and null 结果为flase
实践7-在块中使用三种循环for.while.loop
该实验的目的是掌握 pl/sql 块中使用 三种 循环的语法。
打印乘法口诀。
-- for declare s varchar2(2000) := ''; begin for i in 1..9 loop s := ''; for j in 1..i loop s := s || j || ' * ' || i || ' = ' || i*j || ' '; end loop; dbms_output.put_line(s); end loop; end; /
-- while declare s varchar2(2000) := ''; i number :=1; j number :=1; begin while i <= 9 loop s := ''; j := 1; while j <= i loop s := s || j || ' * ' || i || ' = ' || i*j || ' '; j := j + 1; end loop; dbms_output.put_line(s); i := i + 1; end loop; end; /
-- loop declare s varchar2(2000) := ''; i number :=1; j number :=1; begin loop s := ''; j := 1; loop s := s || j || ' * ' || i || ' = ' || i*j || ' '; j := j + 1; exit when j > i; end loop; dbms_output.put_line(s); i := i + 1; exit when i > 9; end loop; end; /
DECLARE TYPE DeptRecTyp IS RECORD ( dept_id NUMBER(4) NOT NULL := 10, dept_name VARCHAR2(30) NOT NULL := 'Administration', mgr_id NUMBER(6) := 200, loc_id NUMBER(4) );
DEPTNO: 10 DNAME: Administration LOC: New York DEPTNO: 10 DNAME: ACCOUNTING LOC: NEW YORK
实践11-在块中自定义COLLECTIONS的类型
DECLARE TYPE t1 IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; TYPE t2 IS TABLE OF DATE INDEX BY BINARY_INTEGER; v1 t1; v2 t2; BEGIN V1(0) := 'AA'; v1(1) := 'CAMERON'; v2(8) := SYSDATE + 7; select ename,hiredate into v1(7900),v2(7900) from emp where empno=7900; dbms_output.put_line(v1(1)||' '||v1(7900)); dbms_output.put_line(v2(8)||' '||v2(7900)); dbms_output.put_line(v1(0)); END; /
DECLARE TYPE t1 IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; v1 t1; BEGIN V1(0) := 'AA'; v1(1) := 'CAMERON'; dbms_output.put_line(v1(1)); END; /
DECLARE TYPE t1 IS TABLE OF NUMBER; v1 t1 := t1(1,2,3); BEGIN dbms_output.put_line(v1(1)); END; /
实践12-使用集合的属性来操作集合的数据
方法
类型
描述
DELETE
程序
从集合中删除元素。
TRIM
程序
从varray或嵌套表的末尾删除元素。
EXTEND
程序
将元素添加到varray或嵌套表的末尾。
EXISTS
功能
TRUE当且仅当存在varray或嵌套表的指定元素时才返回。
FIRST
功能
返回集合中的第一个索引。
LAST
功能
返回集合中的最后一个索引。
COUNT
功能
返回集合中元素的数量。
LIMIT
功能
返回集合可以具有的最大元素数。
PRIOR
功能
返回指定索引之前的索引。
NEXT
功能
返回在指定索引之后的索引。
DECLARE type nt_type is table of number; nt nt_type := nt_type(11, 22, 33, 44, 55, 66); PROCEDURE print_nt(nt nt_type) IS i number; begin i := nt.FIRST; IF i IS NULL THEN DBMS_OUTPUT.PUT_LINE('nt is empty'); ELSE WHILE i IS NOT NULL LOOP DBMS_OUTPUT.PUT('nt.(' || i || ') = '); DBMS_OUTPUT.PUT(nt(i)); DBMS_OUTPUT.PUT_line(''); i := nt.NEXT(i); END LOOP; END IF; DBMS_OUTPUT.PUT_line('=========================='); END; BEGIN print_nt(nt);
nt.DELETE(2); -- Delete second element print_nt(nt);
nt(2) := 2222; -- Restore second element print_nt(nt);
nt.DELETE(2, 4); -- Delete range of elements print_nt(nt);
nt(3) := 3333; -- Restore third element print_nt(nt);
nt.DELETE; -- Delete all elements print_nt(nt); END; /
DECLARE TYPE t1 IS TABLE OF emp%rowtype INDEX BY BINARY_INTEGER; TYPE t2 IS TABLE OF dept%rowtype INDEX BY BINARY_INTEGER; v1 t1; v2 t2; BEGIN select * into v1(7900) from emp where empno=7900; select * into v2(10) from dept where deptno=10; dbms_output.put_line(v1(7900).empno||v1(7900).ename); dbms_output.put_line(v2(10).dname); END; /
实践14-在块中使用自定义游标
DECLARE CURSOR c1 is select ename,sal from emp order by sal desc; v1 c1%rowtype; BEGIN open c1; fetch c1 into v1; dbms_output.put_line(v1.ename || ': ' ||v1.sal); fetch c1 into v1; dbms_output.put_line(v1.ename || ': ' ||v1.sal); close c1; END; /
实践15—在块中使用自定义游标和游标的属性
游标的属性:
前缀为游标的名称
%isopen，测试该游标是否打开，返回真或假
%rowcount，游标已经操作了多少行， 返回数值
%found，游标是否找到记录，返回真或假
%notfound，游标是否找到记录，返回真或假
DECLARE CURSOR c1 is select ename,sal from emp order by sal desc; v1 c1%rowtype; n1 number(2); BEGIN if not c1%isopen then open c1; end if; fetch c1 into v1; n1:=c1%rowcount; dbms_output.put_line(v1.ename||' '||v1.sal||' '||n1); close c1; END; /
实践16-在块中使用自定义游标和循环控制
DECLARE CURSOR c1 is select ename,sal from emp order by sal desc; v1 c1%rowtype; n1 number(2); BEGIN open c1; loop fetch c1 into v1; exit when c1%notfound; dbms_output.put_line(v1.ename||' '||v1.sal); n1:=c1%rowcount; end loop; close c1; dbms_output.put_line(n1); END; /
-- For 循环 DECLARE CURSOR c1 is select ename,sal from emp order by sal desc; n1 number(2); BEGIN for v1 in c1 loop dbms_output.put_line(v1.ename||' '||v1.sal); n1:=c1%rowcount; end loop; dbms_output.put_line(n1); END; /
v1 的数据类型为 c1%rowtype
c1 自动 open,自动fetch,自动 close
for 循环和游标的结合可以很方便的 处理游标内的每一行。
实践18-在块中使用自定义游标之带变量的游标
带变量的游标，每次打开游标的时候需要给定变量。
根据变量的不同，游标的内容将不同。
一般用于多层循环中内层循环的游标控制。
DECLARE CURSOR c1(n1 number) is select ename,sal from emp where empno=n1; v1 c1%rowtype; BEGIN open c1(7900); fetch c1 into v1; dbms_output.put_line(v1.ename||' '||v1.sal); close c1; END; /
ENAME DEPTNO DNAME ---------- ---------- ------------------ test1 50 test 50 SMITH 20 ALLEN 30 WARD 30 JONES 20 MARTIN 30 BLAKE 30 CLARK 10 SCOTT 20 KING 10 TURNER 30 ADAMS 20 JAMES 30 FORD 20 MILLER 10 booboo 50
17rows selected.
答案：
DECLARE CURSOR c1 is select * from t1 for update; v1 dept.dname%type;
BEGIN for n1 in c1 loop select dname into v1 from dept where deptno=n1.deptno; update t1 set dname=v1 WHERE CURRENT OF C1; end loop; END; /
检查
SCOTT@testdb>select*from t1;
ENAME DEPTNO DNAME ---------- ---------- ------------------ test1 50 test test 50 test SMITH 20 RESEARCH ALLEN 30 SALES WARD 30 SALES JONES 20 RESEARCH MARTIN 30 SALES BLAKE 30 SALES CLARK 10 ACCOUNTING SCOTT 20 RESEARCH KING 10 ACCOUNTING TURNER 30 SALES ADAMS 20 RESEARCH JAMES 30 SALES FORD 20 RESEARCH MILLER 10 ACCOUNTING booboo 50 test