- 1、本文档共10页,可阅读全部内容。
- 2、有哪些信誉好的足球投注网站(book118)网站文档一经付费(服务费),不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
- 3、本站所有内容均由合作方或网友上传,本站不对文档的完整性、权威性及其观点立场正确性做任何保证或承诺!文档内容仅供研究参考,付费前请自行鉴别。如您付费,意味着您自己接受本站规则且自行承担风险,本站不退款、不进行额外附加服务;查看《如何避免下载的几个坑》。如果您已付费下载过本站文档,您可以点击 这里二次下载。
- 4、如文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“版权申诉”(推荐),也可以打举报电话:400-050-0827(电话支持时间:9:00-18:30)。
查看更多
REF Cursors
Cursor Variables
Cursor variables are like C or Pascal pointers, which hold the memory location (address) of an item instead of the item itself.
In PL/SQL, a pointer is declared as REF X, where REF is short for REFERENCE and X stands for a class of objects.
A cursor variable has the data type REF CURSOR.
A cursor is static, but a cursor variable is dynamic.
Cursor variables give you more flexibility.
Using Cursor Variables
You can use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients.
PL/SQL can share a pointer to the query work area in which the result set is stored.
You can pass the value of a cursor variable freely from one scope to another.
You can reduce network traffic by having a PL/SQL block open (or close) several host cursor variables in a single round-trip.
Defining REF CURSOR Types
Define a REF CURSOR type:
Declare a cursor variable of that type:
Example:
Define a REF CURSOR type
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
ref_cv ref_type_name;
DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;
dept_cv DeptCurTyp;
Using the OPEN-FOR, FETCH, and CLOSE Statements
The OPEN-FOR statement associates a cursor variable with a multirow query, executes the query, identifies the result set, and positions the cursor to point to the first row of the result set.
The FETCH statement returns a row from the result set of a multirow query, assigns the values of select-list items to corresponding variables or fields in the INTO clause, increments the count kept by %ROWCOUNT, and advances the cursor to the next row.
The CLOSE statement disables a cursor variable.
Example of Fetching
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec employees%ROWTYPE;
sql_stmt VARCHAR2(200);
my_job VARCHAR2(10) := ST_CLERK;
BEGIN
sql_stmt := SELECT * FROM employees
WHERE job_id = :j;
OPEN emp_cv FOR sql_stmt USING my_job;
文档评论(0)