将整行或行的一部分赋值给记录变量
(1)SELECT INTO子句将行赋值给记录变量
select 查询列表 into 记录变量名 from 表或视图名 |
对于选择列表中的每列,记录变量必须有对应的、类型兼容的域。选择列表中的列必须和记录中的域具有相同的顺序。
DECLARE TYPE RecordTyp IS RECORD ( last employees.last_name%TYPE, id employees.employee_id%TYPE ); rec1 RecordTyp; BEGIN SELECT last_name, employee_id INTO rec1 FROM employees WHERE job_id = 'AD_PRES';
DBMS_OUTPUT.PUT_LINE ('Employee #' || rec1.id || ' = ' || rec1.last); END;
|
(2)FETCH语句将行赋值给记录变量
FETCH 光标变量 INTO 记录变量名; |
--FETCH给函数返回的记录变量赋值 DECLARE TYPE EmpRecTyp IS RECORD ( emp_id employees.employee_id%TYPE, salary employees.salary%TYPE ); CURSOR desc_salary RETURN EmpRecTyp IS SELECT employee_id, salary FROM employees ORDER BY salary DESC; highest_paid_emp EmpRecTyp; next_highest_paid_emp EmpRecTyp; FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS emp_rec EmpRecTyp; BEGIN OPEN desc_salary; FOR i IN 1..n LOOP FETCH desc_salary INTO emp_rec; --赋值 END LOOP; CLOSE desc_salary; RETURN emp_rec; END nth_highest_salary; BEGIN highest_paid_emp := nth_highest_salary(1); next_highest_paid_emp := nth_highest_salary(2); DBMS_OUTPUT.PUT_LINE( '最高收入: #' || highest_paid_emp.emp_id || ', $' || highest_paid_emp.salary ); DBMS_OUTPUT.PUT_LINE( '第二高收入: #' || next_highest_paid_emp.emp_id || ', $' || next_highest_paid_emp.salary ); END;
|
(3)SQL语句返回行到PL/SQL记录变量中
SQL语句INSERT、UPDATE、DELETE有个可选的RETURNING INTO 子句,可以返回受影响的行到一个PL/SQL记录变量中。
DECLARE TYPE EmpRec IS RECORD ( last_name employees.last_name%TYPE, salary employees.salary%TYPE ); emp_info EmpRec; old_salary employees.salary%TYPE; BEGIN SELECT salary INTO old_salary FROM employees WHERE employee_id = 100;
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 100 RETURNING last_name, salary INTO emp_info; --Returning into子句的使用
DBMS_OUTPUT.PUT_LINE ( emp_info.last_name || ' 工资 ' || ' 从 ' || old_salary || ' 涨到 ' || emp_info.salary); END;
|
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1118608/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1118608/