1.DECLARE
v_locid NUMBER := 1700;
CURSOR c_emp_cursor IS
SELECT employee_id,last_name FROM employees
WHERE department_id=v_locid;
v_empno emplouees.employee_id%TYPE;
v_lname employees.last_name%TYPE;
// v_emp_record c_emp_cursor%ROWTYPE; 使用记录
BEGIN
OPEN c_emp_cursor;
LOOP
FETCH c_emp_cursor INTO v_empno,v_lname;
// FETCH c_emp_cursor INTO v_emp_record;
EXIT WHEN c_emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vemp_record.employee_id || ' ' || v_emp_record.last_name);
END LOOP;
CLOSE c_emp_cursor;
END;
2.游标FOR循环
FOR record_name IN cursor_name
LOOP
statement1;
statement2;
...
END LOOP;
在游标FOR循环中,游标可以被隐式的打开,提取,退出和关闭,且记录也可以被隐式的声明。
例:DECLARE
CURSOR c_emp_cursor IS
SELECT employee_id,last_name,ROM employees
WHERE department_id=30;
BEGIN
FOR emp_record IN c_emp_cursor
LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.employee_id || ' ' || emp_record.last_name);
END LOOP;
END;
/
在FOR 循环中游标的定义也可以省略,上述代码可以简化为:
BEGIN
FOR emp_record IN (SELECT employee_id,last_name FROM employees WHERE department_id=30)
LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.employee_id ||' '||emp_record.last_name);
END LOOP;
END;
/
3. 游标的属性
%ISOPEN:判断游标是不是被打开;
IF NOT c_emp_cursor%ISOPEN THEN
OPEN c_emp_cursor;
END IF
LOOP
FETCH c_emp_cursor
%NOTFOUND:如果最近一次抓取记录的操作没有返回值,为TRUE;
%FOUND:如果最近一次抓取记录的操作有返回值,为TRUE;
%ROWCOUNT:每FETCH一次,次数加一。
EXIT WHEN c_emp_cursor%ROWCOUNT > 10 OR c_emp_cursor%NOTFOUND;
4.游标的高级用法--带参数的游标:
DECLARE
CURSOR c_emp_cursor(deptno NUMBER) IS
SELECT employee_id,last_name
FROM employees
WHERE department_id = deptno;
...
BEGIN
OPEN c_emp_cursor(10);
...
CLOSE c_emp_cursor;
OPEN c_emp_cursor(20);
...
5.FOR UPDATE子句用来锁定查询出来的记录用于以后修改。
SELECT ...
FROM
FOR UPDATE [OF column_reference][NOWAIT | WAIT n];
6.WHERE CURRENT OF子句用来修改游标的内容=修改表的内容。
UPDATE employees
SET salary = ...
WHERE CURRENT OF c_emp_cursor;
7.批量导入 BULK COLLECT
例:不使用游标
DECLARE
TYPE emp_type IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
l_emp emp_type;
l_row PLS_INTEGER;
BEGIN
SELECT * BULK COLLECT INTO l_emp FROM employees;
DBMS_OUTPUT.PUT_LINE('The count is:' || l_emp.COUNT);
l_row := l_emp.FIRST;
WHILE(l_row IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LIN(l_row||':'||l_emp(l_row).employee_id||'-->'||l_emp(l_row).first_name);
l_row := l_emp.NEXT(l_row);
END LOOP;
END;
/
使用游标的情况:
DECLARE
CURSOR e IS SELECT * FROM employees;
TYPE emp_type IS TABLE OF e%ROWTYPE INDEX BY PLS_INTEGER;
l_emp emp_type;
l_row PLS_INTEGER;
BEGIN
OPEN e;
FETCH e BULK COLLECT INTO l_emp;
CLOSE E;
DBMS_OUTPUT.PUT_LINE('The count is:' || l_emp.COUNT);
l_row := l_emp.FIRST;
WHILE(l_row IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LIN(l_row||':'||l_emp(l_row).employee_id||'-->'||l_emp(l_row).first_name);
l_row := l_emp.NEXT(l_row);
END LOOP;
END;
/
v_locid NUMBER := 1700;
CURSOR c_emp_cursor IS
SELECT employee_id,last_name FROM employees
WHERE department_id=v_locid;
v_empno emplouees.employee_id%TYPE;
v_lname employees.last_name%TYPE;
// v_emp_record c_emp_cursor%ROWTYPE; 使用记录
BEGIN
OPEN c_emp_cursor;
LOOP
FETCH c_emp_cursor INTO v_empno,v_lname;
// FETCH c_emp_cursor INTO v_emp_record;
EXIT WHEN c_emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vemp_record.employee_id || ' ' || v_emp_record.last_name);
END LOOP;
CLOSE c_emp_cursor;
END;
2.游标FOR循环
FOR record_name IN cursor_name
LOOP
statement1;
statement2;
...
END LOOP;
在游标FOR循环中,游标可以被隐式的打开,提取,退出和关闭,且记录也可以被隐式的声明。
例:DECLARE
CURSOR c_emp_cursor IS
SELECT employee_id,last_name,ROM employees
WHERE department_id=30;
BEGIN
FOR emp_record IN c_emp_cursor
LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.employee_id || ' ' || emp_record.last_name);
END LOOP;
END;
/
在FOR 循环中游标的定义也可以省略,上述代码可以简化为:
BEGIN
FOR emp_record IN (SELECT employee_id,last_name FROM employees WHERE department_id=30)
LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.employee_id ||' '||emp_record.last_name);
END LOOP;
END;
/
3. 游标的属性
%ISOPEN:判断游标是不是被打开;
IF NOT c_emp_cursor%ISOPEN THEN
OPEN c_emp_cursor;
END IF
LOOP
FETCH c_emp_cursor
%NOTFOUND:如果最近一次抓取记录的操作没有返回值,为TRUE;
%FOUND:如果最近一次抓取记录的操作有返回值,为TRUE;
%ROWCOUNT:每FETCH一次,次数加一。
EXIT WHEN c_emp_cursor%ROWCOUNT > 10 OR c_emp_cursor%NOTFOUND;
4.游标的高级用法--带参数的游标:
DECLARE
CURSOR c_emp_cursor(deptno NUMBER) IS
SELECT employee_id,last_name
FROM employees
WHERE department_id = deptno;
...
BEGIN
OPEN c_emp_cursor(10);
...
CLOSE c_emp_cursor;
OPEN c_emp_cursor(20);
...
5.FOR UPDATE子句用来锁定查询出来的记录用于以后修改。
SELECT ...
FROM
FOR UPDATE [OF column_reference][NOWAIT | WAIT n];
6.WHERE CURRENT OF子句用来修改游标的内容=修改表的内容。
UPDATE employees
SET salary = ...
WHERE CURRENT OF c_emp_cursor;
7.批量导入 BULK COLLECT
例:不使用游标
DECLARE
TYPE emp_type IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
l_emp emp_type;
l_row PLS_INTEGER;
BEGIN
SELECT * BULK COLLECT INTO l_emp FROM employees;
DBMS_OUTPUT.PUT_LINE('The count is:' || l_emp.COUNT);
l_row := l_emp.FIRST;
WHILE(l_row IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LIN(l_row||':'||l_emp(l_row).employee_id||'-->'||l_emp(l_row).first_name);
l_row := l_emp.NEXT(l_row);
END LOOP;
END;
/
使用游标的情况:
DECLARE
CURSOR e IS SELECT * FROM employees;
TYPE emp_type IS TABLE OF e%ROWTYPE INDEX BY PLS_INTEGER;
l_emp emp_type;
l_row PLS_INTEGER;
BEGIN
OPEN e;
FETCH e BULK COLLECT INTO l_emp;
CLOSE E;
DBMS_OUTPUT.PUT_LINE('The count is:' || l_emp.COUNT);
l_row := l_emp.FIRST;
WHILE(l_row IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LIN(l_row||':'||l_emp(l_row).employee_id||'-->'||l_emp(l_row).first_name);
l_row := l_emp.NEXT(l_row);
END LOOP;
END;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22012035/viewspace-694166/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22012035/viewspace-694166/