例子1:返回一行、多列记录。
SQL> set serveroutput on
SQL> declare
2 v_emp_hiredate employees.hire_date%type;
3 v_emp_salary employees.salary%type;
4 begin
5 select hire_date,salary into v_emp_hiredate,v_emp_salary
6 from employees where employee_id = 100;
7 dbms_output.put_line('Hire date is :' || v_emp_hiredate);
8 dbms_output.put_line('Salary is :' || v_emp_salary);
9 end;
10 /
Hire date is :17-6月 -03
Salary is :24000
PL/SQL 过程已成功完成。
例子2:返回一行、多列记录。
SQL> set serveroutput on
SQL> declare
2 type employees_record_type is record(v_first_name employees.first_name%type,v_salary employees.salary%type);
3 employees_record employees_record_type;
4 begin
5 select first_name,salary into employees_record from employees where employee_id = 100;
6 dbms_output.put_line(employees_record.v_first_name || employees_record.v_salary);
7 end;
8 /
Steven24000
PL/SQL 过程已成功完成。
游标的四个属性:
游标名%isopen --如果当前游标打开则返回true,否则返回false.
游标名%found --如果当前游标读取到数据则返回true,否则返回false.
游标名%notfound --如果当前游标未读取到数据则返回true,否则返回false.
游标名%rowcount --返回截止到目前为止当前游标读取到的数据的行数.
例子3:返回多行、多列记录。
SQL> set serveroutput on
SQL> declare
2 cursor v_cursor is select first_name,salary from employees;
3 v_first_name employees.first_name%type;
4 v_salary employees.salary%type;
5 begin
6 open v_cursor;
7 loop fetch v_cursor into v_first_name,v_salary;
8 exit when v_cursor%notfound;
9 dbms_output.put_line(v_first_name || v_salary);
10 end loop;
11 close v_cursor;
12 end;
13 /
Donald2600
Douglas2600
Jennifer4400
... ...
PL/SQL 过程已成功完成。