oracle笔记游标的使用

/*

PL/SQL 游标的使用

游标知识点

什么是游标，说的简单直白点，游标的作用就是用来取多条数据，遍
历数据(一句话概括，其实就是处理多行数据)有点像java集合中的迭代器一样 

游标的使用(游标类似于java中的Iterator)

*/

--使用游标
--打印出 80 部门的所有的员工的工资:salary: xxx

DECLARE
v_salary employees.salary%TYPE;
--定义游标
CURSOR emp_salary_cursor IS SELECT salary FROM employees WHERE department_id = 80;
BEGIN
  --打开游标
  OPEN emp_salary_cursor;
  --提取游标
  FETCH emp_salary_cursor INTO v_salary;
  
  --循环
  WHILE emp_salary_cursor%FOUND LOOP
    dbms_output.put_line('salary = ' || v_salary);
    FETCH emp_salary_cursor INTO v_salary; --迭代条件 --这句话不能少，少了这句话会造成死循环
  END LOOP;
  --关闭游标
  CLOSE emp_salary_cursor;
END;

--
DECLARE
v_salary employees.salary%TYPE;
v_empid employees.employee_id%TYPE;
v_lastname employees.last_name%TYPE;
--定义游标
CURSOR emp_salary_cursor IS SELECT salary, employee_id, last_name FROM employees WHERE department_id = 80;
BEGIN
  --打开游标
  OPEN emp_salary_cursor;
  --提取游标
  FETCH emp_salary_cursor INTO v_salary, v_empid, v_lastname;
  
  --循环
  WHILE emp_salary_cursor%FOUND LOOP
    dbms_output.put_line('employee_id = ' || v_empid || ' last_name = ' || v_lastname || ' salary = ' || v_salary);
    FETCH emp_salary_cursor INTO v_salary, v_empid, v_lastname; --迭代条件 --这句话不能少，少了这句话会造成死循环
  END LOOP;
  --关闭游标
  CLOSE emp_salary_cursor;
END;

--
DECLARE
  --定义一个记录类型
  TYPE EMP_RECORD IS RECORD(
    V_SALARY   EMPLOYEES.SALARY%TYPE,
    V_EMPID    EMPLOYEES.EMPLOYEE_ID%TYPE,
    V_LASTNAME EMPLOYEES.LAST_NAME%TYPE,
    V_HIREDATE EMPLOYEES.HIRE_DATE%TYPE);
  --声明一个记录类型的变量
  V_EMP_RECORD EMP_RECORD;
  --定义游标
  CURSOR EMP_SALARY_CURSOR IS
    SELECT SALARY, EMPLOYEE_ID, LAST_NAME, HIRE_DATE
      FROM EMPLOYEES
     WHERE DEPARTMENT_ID = 80;
BEGIN
  DBMS_OUTPUT.ENABLE(buffer_size => null); --解决ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes这个问题
  --打开游标
  OPEN EMP_SALARY_CURSOR;
  --提取游标
  FETCH EMP_SALARY_CURSOR
    INTO V_EMP_RECORD;

  --循环
  WHILE EMP_SALARY_CURSOR%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('employee_id = ' || V_EMP_RECORD.V_EMPID ||
                         ' last_name = ' || V_EMP_RECORD.V_LASTNAME ||
                         ' salary = ' || V_EMP_RECORD.V_SALARY ||
                         ' hire_date = ' || V_EMP_RECORD.V_HIREDATE
                         );
    FETCH EMP_SALARY_CURSOR
      INTO V_EMP_RECORD; --迭代条件 --这句话不能少，少了这句话会造成死循环
  END LOOP;
  --关闭游标
  CLOSE EMP_SALARY_CURSOR;
END;


--
DECLARE
  --定义一个记录类型
v_emp_record employees%ROWTYPE; --%ROWTYPE表示和employees表中的所有列的结构一样
  --定义游标
  CURSOR EMP_SALARY_CURSOR IS
    SELECT *
      FROM EMPLOYEES
     WHERE DEPARTMENT_ID = 80;
BEGIN
  DBMS_OUTPUT.ENABLE(buffer_size => null); --解决ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes这个问题
  --打开游标
  OPEN EMP_SALARY_CURSOR;
  --提取游标
  FETCH EMP_SALARY_CURSOR
    INTO v_emp_record;

  --循环
  WHILE EMP_SALARY_CURSOR%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('employee_id = ' || v_emp_record.employee_id ||
                         ' last_name = ' || v_emp_record.last_name ||
                         ' salary = ' || v_emp_record.salary ||
                         ' hire_date = ' || v_emp_record.hire_date
                         );
    FETCH EMP_SALARY_CURSOR
      INTO v_emp_record; --迭代条件 --这句话不能少，少了这句话会造成死循环
  END LOOP;
  --关闭游标
  CLOSE EMP_SALARY_CURSOR;
END;

--
--在for循环中使用游标
DECLARE
  --定义游标
  CURSOR EMP_SALARY_CURSOR IS
    SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 80;
BEGIN
  /*
  注意：for循环中会自动的把打开游标、提取游标、关闭游标这3部分完成，不需要我们手
  动的去操作打开游标、提取游标、关闭游标这3部分
  */
  FOR OBJ IN EMP_SALARY_CURSOR LOOP
    DBMS_OUTPUT.PUT_LINE('salary = ' || OBJ.SALARY || ' last_name = ' ||
                         OBJ.LAST_NAME || ' hire_date = ' || OBJ.HIRE_DATE);
  END LOOP;
END;

--
/*

利用游标, 调整公司中员工的工资: 
    工资范围       调整基数
    0 - 5000       5%
    5000 - 10000   3%
    10000 - 15000  2%
    15000 -        1%

*/

--使用pl/sql的方式
DECLARE
--定义游标
CURSOR EMP_SALARY_CURSOR
IS 
SELECT employee_id, salary FROM EMPLOYEES;
--用于记录调整基数
v_adjustment_base NUMBER(5, 3);
v_employee_id employees.employee_id%TYPE;
v_salary employees.salary%TYPE;
v_new_salary employees.salary%TYPE;
BEGIN 
 
OPEN EMP_SALARY_CURSOR;

FETCH EMP_SALARY_CURSOR INTO v_employee_id, v_salary;

WHILE EMP_SALARY_CURSOR%FOUND LOOP
  IF v_salary < 5000 THEN v_adjustment_base := 0.05;
   ELSIF v_salary < 10000 THEN v_adjustment_base := 0.03;
   ELSIF v_salary < 15000 THEN v_adjustment_base := 0.02;
  ELSE v_adjustment_base := 0.01;
  END IF;
  dbms_output.put_line('调整前的薪资 ' || '员工号=' || v_employee_id || ' 薪水=' || v_salary);
  UPDATE employees 
  SET salary = salary * (1 + v_adjustment_base)
  WHERE employee_id = v_employee_id;
  SELECT salary INTO v_new_salary FROM employees WHERE employee_id = v_employee_id;
  dbms_output.put_line('调整后的薪资 ' || '员工号=' || v_employee_id || ' 薪水=' || v_new_salary);
  dbms_output.put_line('---------------------------------------------------------');
  FETCH EMP_SALARY_CURSOR INTO v_employee_id, v_salary;
END LOOP;
CLOSE EMP_SALARY_CURSOR;

END;

--使用sql中的DECODE函数
UPDATE employees SET salary = salary * (1 + (DECODE( TRUNC(salary / 5000), 0, 0.05,
1, 0.03,
2, 0.02,
0.01
)
)) 

--改成for循环的方式(显然，使用for循环来处理游标比较简单一些)
DECLARE
--定义游标
CURSOR EMP_SALARY_CURSOR
IS 
SELECT employee_id, salary FROM EMPLOYEES;
--用于记录调整基数
v_adjustment_base NUMBER(5, 3);
v_new_salary employees.salary%TYPE;
BEGIN 

FOR rowObj IN EMP_SALARY_CURSOR LOOP
  IF rowObj.salary < 5000 THEN v_adjustment_base := 0.05;
   ELSIF rowObj.salary < 10000 THEN v_adjustment_base := 0.03;
   ELSIF rowObj.salary < 15000 THEN v_adjustment_base := 0.02;
  ELSE v_adjustment_base := 0.01;
  END IF;
  DBMS_OUTPUT.ENABLE(buffer_size => null); --解决ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes这个问题
  dbms_output.put_line('调整前的薪资 ' || '员工号=' || rowObj.employee_id || ' 薪水=' || rowObj.salary);
 --更新薪资
 UPDATE employees SET salary = salary * (1 + v_adjustment_base) WHERE employee_id = rowObj.employee_id;
 
SELECT salary INTO v_new_salary FROM employees WHERE employee_id = rowObj.employee_id;
  dbms_output.put_line('调整后的薪资 ' || '员工号=' || rowObj.employee_id || ' 薪水=' || v_new_salary);
  dbms_output.put_line('---------------------------------------------------------');
END LOOP;
END;


--带参数的游标
DECLARE
--定义带参数的游标
CURSOR EMP_SALARY_CURSOR(dept_id NUMBER, sal NUMBER) -- 带参数的游标
IS 
SELECT employee_id, salary FROM EMPLOYEES
WHERE department_id = dept_id AND salary > sal;
BEGIN
  FOR rowObj IN EMP_SALARY_CURSOR(80, 15000) LOOP --给游标的参数赋值(可以这样赋值)
    dbms_output.put_line('员工号=' || rowObj.employee_id || ' 薪水=' || rowObj.salary);
  END LOOP;
  
END;

--带参数的游标
DECLARE
--定义带参数的游标
CURSOR EMP_SALARY_CURSOR(dept_id NUMBER, sal NUMBER) -- 带参数的游标
IS 
SELECT employee_id, salary, department_id FROM EMPLOYEES
WHERE department_id = dept_id AND salary > sal;
BEGIN
  FOR rowObj IN EMP_SALARY_CURSOR(sal => 15000, dept_id => 80) LOOP --给游标的参数赋值(也可以这样赋值)
    dbms_output.put_line('员工部门号' || rowObj.department_id || '员工号=' || rowObj.employee_id || ' 薪水=' || rowObj.salary);
  END LOOP;
  
END;

--隐式游标: 更新指定员工 salary(涨工资 10)，如果该员工没有找到，则打印”查无此人” 信息
BEGIN
  UPDATE employees 
  SET salary = salary + 10
  WHERE employee_id = 888;
  --这里有隐式游标
  IF SQL%NOTFOUND THEN dbms_output.put_line('查无此人');
   END IF;
END;


/*

--以下语句会报错
--使用begin  end 运行select 时，不能单单地使用select，必须使用select  into 。声明一个变量，将查询到的结果 into 赋给一个变量
BEGIN 
  SELECT employee_id, salary FROM employees;
END;

*/


--查询某张表中的字段名，字段类型，是否为空，字段长度等信息
SELECT COLUMN_ID, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME = 'EMPLOYEES'
 ORDER BY COLUMN_ID;
 
--查询某张表中的字段名，字段类型，是否为空，字段长度等信息
SELECT * FROM  ALL_TAB_COLUMNS WHERE TABLE_NAME = 'EMPLOYEES'


--
SELECT * FROM employees WHERE 6 = 6

--
SELECT * FROM employees WHERE '江西省赣州市于都县' = '江西省赣州市于都县'

--salary是数字类型
--正确
SELECT * FROM employees WHERE salary = 8000
--正确
SELECT * FROM employees WHERE salary = '8000'
--报错
SELECT * FROM employees WHERE salary = '8000ABC'
--正确
SELECT * FROM employees WHERE salary = 8000 OR 1 = 1
--报错
SELECT * FROM employees WHERE salary = '8000 OR 1 = 1'

/*

突然想起来sql注入的问题和一些疑问、疑惑(以后有时间再回过头来想这些sql注入的具体问题，现在暂时先放一放)
--'ddd\' OR \'1\' = '1'
--'ddd\' OR \'1\'=\'1'
SELECT * FROM t_user WHERE PASSWORD = 'ddd' OR '1' = '1';

*/

--使用exit关键字 
DECLARE
v_number NUMBER(5) := 100; --赋值
BEGIN
  FOR mynumber IN 1.. v_number LOOP
    IF mynumber = 50 THEN EXIT; --使用exit关键字 
    END IF;
    dbms_output.put_line('自然数' || mynumber);
  END LOOP;
  dbms_output.put_line('打印结束');
END;

/*

回顾以前的知识点，trunc函数

*/
--
SELECT TRUNC(10000 / 5000),  10000 / 5000 FROM dual;

--
SELECT TRUNC(9000 / 5000), 9000 / 5000 FROM dual;

--
SELECT TRUNC(14999 / 5000), 14999 / 5000 FROM dual;

--
SELECT TRUNC(15000 / 5000) "use TRUNC Function", 15000 / 5000 myRESULT FROM dual;

--
SELECT TRUNC(9.991 / 3.33) AS "使用TRUNC函数", 9.991 / 3.33 AS 结果 FROM dual;

--
SELECT salary, salary + 10 sal FROM employees;