Cursor Based Record vs Strong ref cursor
I'm new in Oracle databases and pl/sql language. My question is what's the differences between strong ref cursor and cursor based record - like e.g. "var_exmpl" variable which will store cursor values i.e. "cur_exmpl%rowtype"
oracle plsql database-cursor
add a comment |
I'm new in Oracle databases and pl/sql language. My question is what's the differences between strong ref cursor and cursor based record - like e.g. "var_exmpl" variable which will store cursor values i.e. "cur_exmpl%rowtype"
oracle plsql database-cursor
add a comment |
I'm new in Oracle databases and pl/sql language. My question is what's the differences between strong ref cursor and cursor based record - like e.g. "var_exmpl" variable which will store cursor values i.e. "cur_exmpl%rowtype"
oracle plsql database-cursor
I'm new in Oracle databases and pl/sql language. My question is what's the differences between strong ref cursor and cursor based record - like e.g. "var_exmpl" variable which will store cursor values i.e. "cur_exmpl%rowtype"
oracle plsql database-cursor
oracle plsql database-cursor
edited Dec 29 '18 at 16:32
Wernfried Domscheit
24.1k42858
24.1k42858
asked Dec 29 '18 at 15:57
J.DoeJ.Doe
112
112
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You use ref cursor types to declare cursor variables. You can then fetch from those cursor variables as you would an explicit cursor. In both cases, you can and usually will fetch into a record.
With an explicit cursor, you can declare the record based on the cursor. With a strong ref cursor type, I would declare a record based on the same type used to declare the ref cursor.
Here are some examples:
DECLARE
TYPE employee_rt IS RECORD
(
employee_id employees.employee_id%TYPE,
salary employees.salary%TYPE
);
TYPE strong_rc IS REF CURSOR
RETURN employee_rt;
strong_rec employee_rt;
CURSOR twocols_cur
IS
SELECT employee_id, salary FROM employees;
rec_from_explicit_cur twocols_cur%ROWTYPE;
BEGIN
OPEN strong_rc FOR SELECT employee_id, salary FROM employees;
FETCH strong_rc INTO strong_rec;
CLOSE strong_rc;
OPEN twocols_cur;
FETCH twocols_cur INTO rec_from_explicit_cur;
CLOSE twocols_cur;
END;
/
So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?
– J.Doe
Dec 30 '18 at 13:49
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53971055%2fcursor-based-record-vs-strong-ref-cursor%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You use ref cursor types to declare cursor variables. You can then fetch from those cursor variables as you would an explicit cursor. In both cases, you can and usually will fetch into a record.
With an explicit cursor, you can declare the record based on the cursor. With a strong ref cursor type, I would declare a record based on the same type used to declare the ref cursor.
Here are some examples:
DECLARE
TYPE employee_rt IS RECORD
(
employee_id employees.employee_id%TYPE,
salary employees.salary%TYPE
);
TYPE strong_rc IS REF CURSOR
RETURN employee_rt;
strong_rec employee_rt;
CURSOR twocols_cur
IS
SELECT employee_id, salary FROM employees;
rec_from_explicit_cur twocols_cur%ROWTYPE;
BEGIN
OPEN strong_rc FOR SELECT employee_id, salary FROM employees;
FETCH strong_rc INTO strong_rec;
CLOSE strong_rc;
OPEN twocols_cur;
FETCH twocols_cur INTO rec_from_explicit_cur;
CLOSE twocols_cur;
END;
/
So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?
– J.Doe
Dec 30 '18 at 13:49
add a comment |
You use ref cursor types to declare cursor variables. You can then fetch from those cursor variables as you would an explicit cursor. In both cases, you can and usually will fetch into a record.
With an explicit cursor, you can declare the record based on the cursor. With a strong ref cursor type, I would declare a record based on the same type used to declare the ref cursor.
Here are some examples:
DECLARE
TYPE employee_rt IS RECORD
(
employee_id employees.employee_id%TYPE,
salary employees.salary%TYPE
);
TYPE strong_rc IS REF CURSOR
RETURN employee_rt;
strong_rec employee_rt;
CURSOR twocols_cur
IS
SELECT employee_id, salary FROM employees;
rec_from_explicit_cur twocols_cur%ROWTYPE;
BEGIN
OPEN strong_rc FOR SELECT employee_id, salary FROM employees;
FETCH strong_rc INTO strong_rec;
CLOSE strong_rc;
OPEN twocols_cur;
FETCH twocols_cur INTO rec_from_explicit_cur;
CLOSE twocols_cur;
END;
/
So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?
– J.Doe
Dec 30 '18 at 13:49
add a comment |
You use ref cursor types to declare cursor variables. You can then fetch from those cursor variables as you would an explicit cursor. In both cases, you can and usually will fetch into a record.
With an explicit cursor, you can declare the record based on the cursor. With a strong ref cursor type, I would declare a record based on the same type used to declare the ref cursor.
Here are some examples:
DECLARE
TYPE employee_rt IS RECORD
(
employee_id employees.employee_id%TYPE,
salary employees.salary%TYPE
);
TYPE strong_rc IS REF CURSOR
RETURN employee_rt;
strong_rec employee_rt;
CURSOR twocols_cur
IS
SELECT employee_id, salary FROM employees;
rec_from_explicit_cur twocols_cur%ROWTYPE;
BEGIN
OPEN strong_rc FOR SELECT employee_id, salary FROM employees;
FETCH strong_rc INTO strong_rec;
CLOSE strong_rc;
OPEN twocols_cur;
FETCH twocols_cur INTO rec_from_explicit_cur;
CLOSE twocols_cur;
END;
/
You use ref cursor types to declare cursor variables. You can then fetch from those cursor variables as you would an explicit cursor. In both cases, you can and usually will fetch into a record.
With an explicit cursor, you can declare the record based on the cursor. With a strong ref cursor type, I would declare a record based on the same type used to declare the ref cursor.
Here are some examples:
DECLARE
TYPE employee_rt IS RECORD
(
employee_id employees.employee_id%TYPE,
salary employees.salary%TYPE
);
TYPE strong_rc IS REF CURSOR
RETURN employee_rt;
strong_rec employee_rt;
CURSOR twocols_cur
IS
SELECT employee_id, salary FROM employees;
rec_from_explicit_cur twocols_cur%ROWTYPE;
BEGIN
OPEN strong_rc FOR SELECT employee_id, salary FROM employees;
FETCH strong_rc INTO strong_rec;
CLOSE strong_rc;
OPEN twocols_cur;
FETCH twocols_cur INTO rec_from_explicit_cur;
CLOSE twocols_cur;
END;
/
answered Dec 29 '18 at 17:58
Steven FeuersteinSteven Feuerstein
1,20158
1,20158
So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?
– J.Doe
Dec 30 '18 at 13:49
add a comment |
So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?
– J.Doe
Dec 30 '18 at 13:49
So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?
– J.Doe
Dec 30 '18 at 13:49
So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?
– J.Doe
Dec 30 '18 at 13:49
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53971055%2fcursor-based-record-vs-strong-ref-cursor%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown