Sfrgttk

Microchip documentation does not label CAN buss pins on micro controller pinout diagram

Why Shazam when there is already Superman?

Did the UK lift the requirement for registering SIM cards?

Why is so much work done on numerical verification of the Riemann Hypothesis?

Is there a nicer/politer/more positive alternative for "negates"?

Which Article Helped Get Rid of Technobabble in RPGs?

How could a planet have erratic days?

Why the "ls" command is showing the permissions of files in a FAT32 partition?

PTIJ: Why is Haman obsessed with Bose?

When were female captains banned from Starfleet?

Why is it that I can sometimes guess the next note?

How do I fix the group tension caused by my character stealing and possibly killing without provocation?

Doesn't the system of the Supreme Court oppose justice?

Shouldn’t conservatives embrace universal basic income?

Has any country ever had 2 former presidents in jail simultaneously?

What is the English pronunciation of "pain au chocolat"?

Has the laser at Magurele, Romania reached a tenth of the Sun's power?

Can I cause damage to electrical appliances by unplugging them when they are turned on?

awk assign to multiple variables at once

Is this part of the description of the Archfey warlock's Misty Escape feature redundant?

How does electrical safety system work on ISS?

Stack Interview Code methods made from class Node and Smart Pointers

Merge org tables

Creating two special characters

PLSQL using cursors or functions to query repeated data

stored procedure returns ERROR 1305 (42000): FUNCTION does not existWhy is this MySQL proc using cursors failing to retrieve results?How to correctly return a query result only if it isn't NULL?Find all potentially redundant records in a table using cursors and fuzzy string matchingStored procedure using cursorsError with Loop using Cursors - Invalid Object Name with Set = @Dynamic query with cursorsHow to commit transaction on an after update event trigger?Oracle 11 query functions default valueUsing PostgreSQL functions instead of joining?

2

1

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)?
Whats the difference?
What are the best practices?
What are the other considerations?
Or is there another better way?

1) using a package cursor

cursor getLicenseStatus_cur (in_license_no varchar2) is

    SELECT status, status_dt from tbl_license where licence_no=in_license_no;



--use:

OPEN getLicenseStatus_cur('123');

fetch getLicenseStatus_cur into l_status, l_status_dt;

EXIT WHEN sql%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);

close getLicenseStatus_cur('123');

2) or using a function and passing a rowtype

create or replace function getLicenseStatus(in_license_no varchar2(10))

RETURN tbl_license%ROWTYPE

    as

    output_rec tbl_license%ROWTYPE;

begin

    SELECT * into output_rec 

    from tbl_license where licence_no=in_license_no;

    return output_rec;

end;



--use

lic_rec users%ROWTYPE;

lic_rec := getLicenseStatus('123');

DBMS_OUTPUT.PUT_LINE('l_status= ' || lic_rec.status || ', l_status_dt= ' || lic_rec .status_dt);

3) or using a function and passing a sys refcursor

create or replace function getLicenseStatus(in_license_no varchar2(10)) 

return sys_refcursor as

    v_curs sys_refcursor;

begin

    open v_curs for SELECT status, status_dt from tbl_license where licence_no=in_license_no;

    return v_curs;

end;



--use:

v_rc := getLicenseStatus('123'); 

fetch v_rc into l_status , l_status_dt;

exit when sql%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);

close v_rc ;

query-performance plsql functions cursors query-optimization

share|improve this question

asked Nov 13 '14 at 11:23

eurekaeureka

111

bumped to the homepage by Community♦ 4 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

2

1

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)?
Whats the difference?
What are the best practices?
What are the other considerations?
Or is there another better way?

1) using a package cursor

cursor getLicenseStatus_cur (in_license_no varchar2) is

    SELECT status, status_dt from tbl_license where licence_no=in_license_no;



--use:

OPEN getLicenseStatus_cur('123');

fetch getLicenseStatus_cur into l_status, l_status_dt;

EXIT WHEN sql%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);

close getLicenseStatus_cur('123');

2) or using a function and passing a rowtype

create or replace function getLicenseStatus(in_license_no varchar2(10))

RETURN tbl_license%ROWTYPE

    as

    output_rec tbl_license%ROWTYPE;

begin

    SELECT * into output_rec 

    from tbl_license where licence_no=in_license_no;

    return output_rec;

end;



--use

lic_rec users%ROWTYPE;

lic_rec := getLicenseStatus('123');

DBMS_OUTPUT.PUT_LINE('l_status= ' || lic_rec.status || ', l_status_dt= ' || lic_rec .status_dt);

3) or using a function and passing a sys refcursor

create or replace function getLicenseStatus(in_license_no varchar2(10)) 

return sys_refcursor as

    v_curs sys_refcursor;

begin

    open v_curs for SELECT status, status_dt from tbl_license where licence_no=in_license_no;

    return v_curs;

end;



--use:

v_rc := getLicenseStatus('123'); 

fetch v_rc into l_status , l_status_dt;

exit when sql%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);

close v_rc ;

query-performance plsql functions cursors query-optimization

share|improve this question

asked Nov 13 '14 at 11:23

eurekaeureka

111

bumped to the homepage by Community♦ 4 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)?
Whats the difference?
What are the best practices?
What are the other considerations?
Or is there another better way?

1) using a package cursor

cursor getLicenseStatus_cur (in_license_no varchar2) is

    SELECT status, status_dt from tbl_license where licence_no=in_license_no;



--use:

OPEN getLicenseStatus_cur('123');

fetch getLicenseStatus_cur into l_status, l_status_dt;

EXIT WHEN sql%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);

close getLicenseStatus_cur('123');

2) or using a function and passing a rowtype

create or replace function getLicenseStatus(in_license_no varchar2(10))

RETURN tbl_license%ROWTYPE

    as

    output_rec tbl_license%ROWTYPE;

begin

    SELECT * into output_rec 

    from tbl_license where licence_no=in_license_no;

    return output_rec;

end;



--use

lic_rec users%ROWTYPE;

lic_rec := getLicenseStatus('123');

DBMS_OUTPUT.PUT_LINE('l_status= ' || lic_rec.status || ', l_status_dt= ' || lic_rec .status_dt);

3) or using a function and passing a sys refcursor

create or replace function getLicenseStatus(in_license_no varchar2(10)) 

return sys_refcursor as

    v_curs sys_refcursor;

begin

    open v_curs for SELECT status, status_dt from tbl_license where licence_no=in_license_no;

    return v_curs;

end;



--use:

v_rc := getLicenseStatus('123'); 

fetch v_rc into l_status , l_status_dt;

exit when sql%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);

close v_rc ;

query-performance plsql functions cursors query-optimization

share|improve this question

asked Nov 13 '14 at 11:23

eurekaeureka

111

cursor getLicenseStatus_cur (in_license_no varchar2) is

    SELECT status, status_dt from tbl_license where licence_no=in_license_no;



--use:

OPEN getLicenseStatus_cur('123');

fetch getLicenseStatus_cur into l_status, l_status_dt;

EXIT WHEN sql%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);

close getLicenseStatus_cur('123');

create or replace function getLicenseStatus(in_license_no varchar2(10))

RETURN tbl_license%ROWTYPE

    as

    output_rec tbl_license%ROWTYPE;

begin

    SELECT * into output_rec 

    from tbl_license where licence_no=in_license_no;

    return output_rec;

end;



--use

lic_rec users%ROWTYPE;

lic_rec := getLicenseStatus('123');

DBMS_OUTPUT.PUT_LINE('l_status= ' || lic_rec.status || ', l_status_dt= ' || lic_rec .status_dt);

create or replace function getLicenseStatus(in_license_no varchar2(10)) 

return sys_refcursor as

    v_curs sys_refcursor;

begin

    open v_curs for SELECT status, status_dt from tbl_license where licence_no=in_license_no;

    return v_curs;

end;



--use:

v_rc := getLicenseStatus('123'); 

fetch v_rc into l_status , l_status_dt;

exit when sql%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);

close v_rc ;

share|improve this question

asked Nov 13 '14 at 11:23

eurekaeureka

111

share|improve this question

asked Nov 13 '14 at 11:23

eurekaeureka

111

asked Nov 13 '14 at 11:23

eurekaeureka

111

asked Nov 13 '14 at 11:23

eurekaeureka

111

1 Answer
1

active

oldest

votes

0

This is a 4th option, using out parameters?

create or replace function getLicenseStatus

    (in_license_no varchar2, l_status out varchar2, l_status_dt out varchar2)

    as

begin

    SELECT status, status_dt 

       into into l_status, l_status_dt 

    from tbl_license 

    where licence_no=in_license_no;

end;

share|improve this answer

edited Sep 10 '15 at 1:00

Paul White♦

53.3k14284457

answered Sep 9 '15 at 23:35

Dan SpkDan Spk

1

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});


}
});

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f82566%2fplsql-using-cursors-or-functions-to-query-repeated-data%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

This is a 4th option, using out parameters?

create or replace function getLicenseStatus

    (in_license_no varchar2, l_status out varchar2, l_status_dt out varchar2)

    as

begin

    SELECT status, status_dt 

       into into l_status, l_status_dt 

    from tbl_license 

    where licence_no=in_license_no;

end;

share|improve this answer

edited Sep 10 '15 at 1:00

Paul White♦

53.3k14284457

answered Sep 9 '15 at 23:35

Dan SpkDan Spk

1

add a comment | 

0

This is a 4th option, using out parameters?

create or replace function getLicenseStatus

    (in_license_no varchar2, l_status out varchar2, l_status_dt out varchar2)

    as

begin

    SELECT status, status_dt 

       into into l_status, l_status_dt 

    from tbl_license 

    where licence_no=in_license_no;

end;

share|improve this answer

edited Sep 10 '15 at 1:00

Paul White♦

53.3k14284457

answered Sep 9 '15 at 23:35

Dan SpkDan Spk

1

add a comment | 

This is a 4th option, using out parameters?

create or replace function getLicenseStatus

    (in_license_no varchar2, l_status out varchar2, l_status_dt out varchar2)

    as

begin

    SELECT status, status_dt 

       into into l_status, l_status_dt 

    from tbl_license 

    where licence_no=in_license_no;

end;

share|improve this answer

edited Sep 10 '15 at 1:00

Paul White♦

53.3k14284457

answered Sep 9 '15 at 23:35

Dan SpkDan Spk

1

create or replace function getLicenseStatus

    (in_license_no varchar2, l_status out varchar2, l_status_dt out varchar2)

    as

begin

    SELECT status, status_dt 

       into into l_status, l_status_dt 

    from tbl_license 

    where licence_no=in_license_no;

end;

share|improve this answer

edited Sep 10 '15 at 1:00

Paul White♦

53.3k14284457

answered Sep 9 '15 at 23:35

Dan SpkDan Spk

1

edited Sep 10 '15 at 1:00

Paul White♦

53.3k14284457

edited Sep 10 '15 at 1:00

Paul White♦

53.3k14284457

answered Sep 9 '15 at 23:35

Dan SpkDan Spk

1

answered Sep 9 '15 at 23:35

Dan SpkDan Spk

1