cursor的使用

admin

DECLARE
  CURSOR c_emp IS SELECT employee_id, first_name, last_name FROM employees;
  v_emp_id NUMBER;
  v_first_name VARCHAR2(50);
  v_last_name VARCHAR2(50);
BEGIN
  -- 打开游标
  OPEN c_emp;
  
  -- 循环读取游标中的每一行数据并进行处理
  LOOP
    FETCH c_emp INTO v_emp_id, v_first_name, v_last_name;
   
    EXIT WHEN c_emp%NOTFOUND; -- 当游标读取到最后一行时退出循环
   
    -- 对于每一行数据进行处理
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Name: ' || v_first_name || ' ' || v_last_name);
  END LOOP;
  
  -- 关闭游标
  CLOSE c_emp;
END;
/

admin

1. DECLARE
   
2.   CURSOR c_user IS SELECT username, account_status FROM dba_users;
   
3.   v_username  dba_users.username%TYPE;
   
4.   v_account_status  dba_users.account_status%TYPE;
   
5. BEGIN
   
6.   OPEN c_user;
   
7.   LOOP
   
8.     FETCH c_user INTO v_username, v_account_status;
   
9.     EXIT WHEN c_user%NOTFOUND;
   
10.     DBMS_OUTPUT.PUT_LINE('DB USERNAME: ' || v_username || ', ACCOUNT STATUS: ' || v_account_status);
    
11.   END LOOP;
    
12.   CLOSE c_user;
    
13. END;
    
14. /

复制代码