显式调用cursor的一个例子

declare
    l_salary employees.salary%type := 10000;
    l_employee_id EMPLOYEES.EMPLOYEE_ID%type;
    l_cursor integer;
    l_retval integer;
begin

    -- open cursor 只是分配内存结构
    l_cursor := DBMS_SQL.OPEN_CURSOR;

    -- parse 将SQL语句关联到cursor
    DBMS_SQL.PARSE(l_cursor, 'select employee_id from employees where salary > :salary', 1);

    -- 为返回结果设置接受变量
    DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_employee_id);

    -- 邦定变量
    dbms_sql.bind_variable(l_cursor, ':salary', l_salary);

    -- 执行cursor
    l_retval := dbms_sql.execute(l_cursor);

    -- 获取cursor的返回结果
    while DBMS_SQL.FETCH_ROWS(l_cursor) > 0 loop
        DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_employee_id);
        dbms_output.put_line('employee id: ' || l_employee_id);
    end loop;
    DBMS_SQL.CLOSE_CURSOR(l_cursor);
end;

 

另一个例子：

declare
    l_salary employees.salary%type := 12000;
    l_employee_id EMPLOYEES.EMPLOYEE_ID%type;
    l_first_name EMPLOYEES.first_name%type;
    l_return_salary EMPLOYEES.salary%type;
    l_cursor integer;
    l_retval integer;
begin
    l_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(l_cursor, 'select employee_id, first_name, salary from employees where salary > :salary', 3);
    DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_employee_id);

    -- 定义字符串类型时，注意要加上长度参数
    DBMS_SQL.DEFINE_COLUMN(l_cursor, 2, l_first_name, 20);
    DBMS_SQL.DEFINE_COLUMN(l_cursor, 3, l_return_salary);
    dbms_sql.bind_variable(l_cursor, ':salary', l_salary);
    l_retval := dbms_sql.execute(l_cursor);
    while DBMS_SQL.FETCH_ROWS(l_cursor) > 0 loop
        DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_employee_id);
        DBMS_SQL.COLUMN_VALUE(l_cursor, 2, l_first_name);
        DBMS_SQL.COLUMN_VALUE(l_cursor, 3, l_return_salary);
        dbms_output.put_line('employee id: ' || l_employee_id || '  first name: ' || l_first_name || '  salary: ' || l_return_salary);
    end loop;
    DBMS_SQL.CLOSE_CURSOR(l_cursor);
end;