例子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 过程已成功完成。
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 过程已成功完成。
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 过程已成功完成。