创建表
CREATE TABLE "SCOTT"."DEPT" (
"DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14 BYTE),
"LOC" VARCHAR2(13 BYTE),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "USERS" ;
三种方式的游标展现:
SET serveroutput ON ;
DECLARE
v_dname dept.dname%type;
v_deptno dept.deptno%type;
v_log dept.loc%type;
CURSOR c_cursor(dno number)
IS
SELECT d.deptno, d.dname,d.loc FROM dept d where d.deptno = dno;
type t_cursor IS ref CURSOR;
v_cursor t_cursor;
BEGIN
dbms_output.put_line('使用游标一');
BEGIN
OPEN v_cursor FOR SELECT d.deptno,
d.dname,d.loc FROM dept d ;
LOOP
FETCH v_cursor INTO v_deptno,v_log,v_dname;
EXIT
WHEN v_cursor%notfound ;
dbms_output.put_line(v_deptno||'的名称是'||v_dname||v_log);
END LOOP;
CLOSE v_cursor;
END ;
dbms_output.put_line('使用游标二');
BEGIN
OPEN c_cursor(10);
LOOP
FETCH c_cursor INTO v_deptno,v_dname,v_log ;
EXIT
WHEN c_cursor%notfound;
dbms_output.put_line(v_deptno||':'||v_dname);
END LOOP ;
CLOSE c_cursor;
END ;
dbms_output.put_line('使用游标三');
BEGIN
FOR cur IN c_cursor(10)
LOOP
dbms_output.put_line(cur.deptno || cur.dname);
END LOOP;
END;
END ;