oracle的游标定义与使用

oracle的游标定义与使用

/*
 * 游标本质上就是一个result结果集,用于临时存储从DB中提取出来的数据块;
 */

/*
 *游标的分类:
 */
/*    1  显示游标;
由用户定义，需要的操作：定义游标、打开游标、提取数据、关闭游标，主要用于对查询语句的处理。
属性：%FOUND %NOTFOUND %ISOPEN %ROWCOUNT
*/
-- 创建用户
create USER study_kay identified by "lk0313";

-- 创建表空间
CREATE tablespace LK_STUDY
datafile 'F:\MYSOFT\ORACLE\ORACLE_FILES\LK_STUDY.DBF' SIZE 50M
autoextend ON NEXT 10M;

-- 授予用户使用表空间的权利
ALTER USER study_kay quota unlimited ON LK_STUDY;

-- 授予用户的基本权限
grant create session TO STUDY_KAY;
grant create table to  STUDY_KAY;
grant create tablespace to  STUDY_KAY;
grant create view to  STUDY_KAY;
grant connect,resource to STUDY_KAY;
grant create any sequence to STUDY_KAY;
grant create any table to STUDY_KAY;
grant delete any table to STUDY_KAY;
grant insert any table to STUDY_KAY;
grant select any table to STUDY_KAY;
grant unlimited tablespace to STUDY_KAY;
grant execute any procedure to STUDY_KAY;
grant update any table to STUDY_KAY;
grant create any view to STUDY_KAY;

--  创建表
CREATE TABLE STUDY_KAY.DEPT (
	DEPTNO NUMBER(22,127),
	DNAME VARCHAR2(100),
	LOCATION VARCHAR2(100)
);
COMMENT ON COLUMN STUDY_KAY.DEPT.DEPTNO IS '部门编号';
COMMENT ON COLUMN STUDY_KAY.DEPT.DNAME IS '部门名称';
COMMENT ON COLUMN STUDY_KAY.DEPT.LOCATION IS '部门地址';


CREATE TABLE STUDY_KAY.EMP (
	ID NUMBER(22,127),
	EMP_NO VARCHAR2(100),
	EMP_NAME VARCHAR2(100),
	JOB VARCHAR2(100),
	MGR VARCHAR2(100),
	BIRTHDAY TIMESTAMP,
	SAL NUMBER(22,127),
	COMMON VARCHAR2(100),
	DEPNO VARCHAR2(100),
);
--  注意,查询具体的字段需要使用%TYPE来明确查询字段的类型
--  下面的工作能是查询打印出与员工信息

DECLARE CURSOR cur_temp IS SELECT * FROM EMP;
v_emp emp%ROWTYE;
-- v_EMP_NO EMP.EMP_NO%TYPE
-- v_EMP_NAME EMP.EMP_NAME%TYPE
-- v_SAL EMP.SAL%TYPE
BEGIN
	OPEN CUR_TEMP;
		LOOP
			FETCH CUR_TEMP INTO v_emp;
			EXIT WHEN CUR_TEMP%NOTFOUND; -- 当查询结束时推出循环
			dbms_output.put_line(v_emp.EMP_NO || v_emp.EMP_NAME || v_emp.SAL);
		END LOOP;
	CLOSE CUR_TEMP;
END;