Invoke a function from a procedure
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I am trying to create a PL/SQL procedure that will invoke a function I called GET_HIGHORDER_FUNC which is already working:
create or replace FUNCTION GET_HIGHORDER_FUNC
return number
AS
c_hiorder number;
BEGIN
select max(sum(product.product_standardprice * orderline.ordered_quantity)) into c_hiorder
from customer, product, orderline, orders
where customer.customer_id = orders.customer_id
and orders.order_id = orderline.order_id
and orderline.product_id = product.product_id
group by customer_name;
RETURN c_hiorder;
END GET_HIGHORDER_FUNC;
create or replace procedure PRINT_CUST_PROC(
p_hiordername in number)
as
begin
/* This procedure should show the name of the customer who have the highest
amount of order which will be available upon invoking the function above */
end;
sql oracle stored-procedures
edited Nov 23 '18 at 13:57
jarlh
29.9k52138
asked Nov 23 '18 at 13:44
ShoSho
196
add a comment |
0
I am trying to create a PL/SQL procedure that will invoke a function I called GET_HIGHORDER_FUNC which is already working:
create or replace FUNCTION GET_HIGHORDER_FUNC
return number
AS
c_hiorder number;
BEGIN
select max(sum(product.product_standardprice * orderline.ordered_quantity)) into c_hiorder
from customer, product, orderline, orders
where customer.customer_id = orders.customer_id
and orders.order_id = orderline.order_id
and orderline.product_id = product.product_id
group by customer_name;
RETURN c_hiorder;
END GET_HIGHORDER_FUNC;
create or replace procedure PRINT_CUST_PROC(
p_hiordername in number)
as
begin
/* This procedure should show the name of the customer who have the highest
amount of order which will be available upon invoking the function above */
end;
sql oracle stored-procedures
edited Nov 23 '18 at 13:57
jarlh
29.9k52138
asked Nov 23 '18 at 13:44
ShoSho
196
2
(Unrelated) Tip of today: Use the modern, explicitJOINsyntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.
– jarlh
Nov 23 '18 at 13:58
add a comment |
0
0
0
I am trying to create a PL/SQL procedure that will invoke a function I called GET_HIGHORDER_FUNC which is already working:
create or replace FUNCTION GET_HIGHORDER_FUNC
return number
AS
c_hiorder number;
BEGIN
select max(sum(product.product_standardprice * orderline.ordered_quantity)) into c_hiorder
from customer, product, orderline, orders
where customer.customer_id = orders.customer_id
and orders.order_id = orderline.order_id
and orderline.product_id = product.product_id
group by customer_name;
RETURN c_hiorder;
END GET_HIGHORDER_FUNC;
create or replace procedure PRINT_CUST_PROC(
p_hiordername in number)
as
begin
/* This procedure should show the name of the customer who have the highest
amount of order which will be available upon invoking the function above */
end;
sql oracle stored-procedures
edited Nov 23 '18 at 13:57
jarlh
29.9k52138
asked Nov 23 '18 at 13:44
ShoSho
196
I am trying to create a PL/SQL procedure that will invoke a function I called GET_HIGHORDER_FUNC which is already working:
create or replace FUNCTION GET_HIGHORDER_FUNC
return number
AS
c_hiorder number;
BEGIN
select max(sum(product.product_standardprice * orderline.ordered_quantity)) into c_hiorder
from customer, product, orderline, orders
where customer.customer_id = orders.customer_id
and orders.order_id = orderline.order_id
and orderline.product_id = product.product_id
group by customer_name;
RETURN c_hiorder;
END GET_HIGHORDER_FUNC;
create or replace procedure PRINT_CUST_PROC(
p_hiordername in number)
as
begin
/* This procedure should show the name of the customer who have the highest
amount of order which will be available upon invoking the function above */
end;
sql oracle stored-procedures
sql oracle stored-procedures
edited Nov 23 '18 at 13:57
jarlh
29.9k52138
asked Nov 23 '18 at 13:44
ShoSho
196
edited Nov 23 '18 at 13:57
jarlh
29.9k52138
asked Nov 23 '18 at 13:44
ShoSho
196
edited Nov 23 '18 at 13:57
jarlh
29.9k52138
edited Nov 23 '18 at 13:57
jarlh
29.9k52138
edited Nov 23 '18 at 13:57
jarlh
29.9k52138
29.9k52138
asked Nov 23 '18 at 13:44
ShoSho
196
asked Nov 23 '18 at 13:44
ShoSho
196
asked Nov 23 '18 at 13:44
ShoSho
196
196
2
(Unrelated) Tip of today: Use the modern, explicitJOINsyntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.
– jarlh
Nov 23 '18 at 13:58
add a comment |
2
(Unrelated) Tip of today: Use the modern, explicitJOINsyntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.
– jarlh
Nov 23 '18 at 13:58
2
2
(Unrelated) Tip of today: Use the modern, explicit
JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.– jarlh
Nov 23 '18 at 13:58
(Unrelated) Tip of today: Use the modern, explicit
JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.– jarlh
Nov 23 '18 at 13:58
add a comment |
1 Answer
1
active
oldest
votes
0
Just invoke it as you would with any function?
This is how i would try based on your latest comments
create or replace FUNCTION GET_HIGHORDER_FUNC(c_customer_name out varchar2,c_hiorder out int)
return number
AS
c_hiorder number;
BEGIN
select y.customer_name
,y.summed_price
into customer_name
,c_hiorder
from (
select x.customer_name
,x.summed_price
,row_number() over(order by x.summed_price desc) as rnk
from (
select customer_name
,sum(product.product_standardprice * orderline.ordered_quantity) as summed_price
from customer, product, orderline, orders
where customer.customer_id = orders.customer_id
and orders.order_id = orderline.order_id
and orderline.product_id = product.product_id
group by customer_name
)x
)y
where y.rnk=1;
RETURN 1;
END GET_HIGHORDER_FUNC;
create or replace procedure PRINT_CUST_PROC(
p_hiordername in number)
as
l_return int;
l_customer_name varchar2(1000);
l_hiorder int;
begin
/* This procedure should show the name of the customer who have the highest
amount of order which will be available upon invoking the function above
*/
l_return : = GET_HIGHORDER_FUNC(l_customer_name,l_hiorder);
dbms_output.put_line(l_customer_name);
dbms_output.put_line(l_hiorder);
end;
answered Nov 23 '18 at 14:03
George JosephGeorge Joseph
1,590510
Actually here's the problem I'm dealing with. I'm very new to this things. sorry. Create a procedure that displays the name of the customer with the highest amount of orders among all customers. Name the procedure PRINT_CUST_PROC. The procedure will also invoke a function called GET_HIGHORDER_FUNC that retrieve s the name and total amount ordered of customer with the highest amount of order . Invoke the procedure and view the results.
– Sho
Nov 23 '18 at 14:22
I Tested the function and I got the output which I think is corret.
– Sho
Nov 23 '18 at 14:26
check and share your feedback
– George Joseph
Nov 23 '18 at 17:08
It worked, just few editing and boom! Awesome. Thank you so much George!
– Sho
Nov 25 '18 at 12:29
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53447859%2finvoke-a-function-from-a-procedure%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
0
Just invoke it as you would with any function?
This is how i would try based on your latest comments
create or replace FUNCTION GET_HIGHORDER_FUNC(c_customer_name out varchar2,c_hiorder out int)
return number
AS
c_hiorder number;
BEGIN
select y.customer_name
,y.summed_price
into customer_name
,c_hiorder
from (
select x.customer_name
,x.summed_price
,row_number() over(order by x.summed_price desc) as rnk
from (
select customer_name
,sum(product.product_standardprice * orderline.ordered_quantity) as summed_price
from customer, product, orderline, orders
where customer.customer_id = orders.customer_id
and orders.order_id = orderline.order_id
and orderline.product_id = product.product_id
group by customer_name
)x
)y
where y.rnk=1;
RETURN 1;
END GET_HIGHORDER_FUNC;
create or replace procedure PRINT_CUST_PROC(
p_hiordername in number)
as
l_return int;
l_customer_name varchar2(1000);
l_hiorder int;
begin
/* This procedure should show the name of the customer who have the highest
amount of order which will be available upon invoking the function above
*/
l_return : = GET_HIGHORDER_FUNC(l_customer_name,l_hiorder);
dbms_output.put_line(l_customer_name);
dbms_output.put_line(l_hiorder);
end;
answered Nov 23 '18 at 14:03
George JosephGeorge Joseph
1,590510
Actually here's the problem I'm dealing with. I'm very new to this things. sorry. Create a procedure that displays the name of the customer with the highest amount of orders among all customers. Name the procedure PRINT_CUST_PROC. The procedure will also invoke a function called GET_HIGHORDER_FUNC that retrieve s the name and total amount ordered of customer with the highest amount of order . Invoke the procedure and view the results.
– Sho
Nov 23 '18 at 14:22
I Tested the function and I got the output which I think is corret.
– Sho
Nov 23 '18 at 14:26
check and share your feedback
– George Joseph
Nov 23 '18 at 17:08
It worked, just few editing and boom! Awesome. Thank you so much George!
– Sho
Nov 25 '18 at 12:29
add a comment |
0
Just invoke it as you would with any function?
This is how i would try based on your latest comments
create or replace FUNCTION GET_HIGHORDER_FUNC(c_customer_name out varchar2,c_hiorder out int)
return number
AS
c_hiorder number;
BEGIN
select y.customer_name
,y.summed_price
into customer_name
,c_hiorder
from (
select x.customer_name
,x.summed_price
,row_number() over(order by x.summed_price desc) as rnk
from (
select customer_name
,sum(product.product_standardprice * orderline.ordered_quantity) as summed_price
from customer, product, orderline, orders
where customer.customer_id = orders.customer_id
and orders.order_id = orderline.order_id
and orderline.product_id = product.product_id
group by customer_name
)x
)y
where y.rnk=1;
RETURN 1;
END GET_HIGHORDER_FUNC;
create or replace procedure PRINT_CUST_PROC(
p_hiordername in number)
as
l_return int;
l_customer_name varchar2(1000);
l_hiorder int;
begin
/* This procedure should show the name of the customer who have the highest
amount of order which will be available upon invoking the function above
*/
l_return : = GET_HIGHORDER_FUNC(l_customer_name,l_hiorder);
dbms_output.put_line(l_customer_name);
dbms_output.put_line(l_hiorder);
end;
answered Nov 23 '18 at 14:03
George JosephGeorge Joseph
1,590510
Actually here's the problem I'm dealing with. I'm very new to this things. sorry. Create a procedure that displays the name of the customer with the highest amount of orders among all customers. Name the procedure PRINT_CUST_PROC. The procedure will also invoke a function called GET_HIGHORDER_FUNC that retrieve s the name and total amount ordered of customer with the highest amount of order . Invoke the procedure and view the results.
– Sho
Nov 23 '18 at 14:22
I Tested the function and I got the output which I think is corret.
– Sho
Nov 23 '18 at 14:26
check and share your feedback
– George Joseph
Nov 23 '18 at 17:08
It worked, just few editing and boom! Awesome. Thank you so much George!
– Sho
Nov 25 '18 at 12:29
add a comment |
0
0
0
Just invoke it as you would with any function?
This is how i would try based on your latest comments
create or replace FUNCTION GET_HIGHORDER_FUNC(c_customer_name out varchar2,c_hiorder out int)
return number
AS
c_hiorder number;
BEGIN
select y.customer_name
,y.summed_price
into customer_name
,c_hiorder
from (
select x.customer_name
,x.summed_price
,row_number() over(order by x.summed_price desc) as rnk
from (
select customer_name
,sum(product.product_standardprice * orderline.ordered_quantity) as summed_price
from customer, product, orderline, orders
where customer.customer_id = orders.customer_id
and orders.order_id = orderline.order_id
and orderline.product_id = product.product_id
group by customer_name
)x
)y
where y.rnk=1;
RETURN 1;
END GET_HIGHORDER_FUNC;
create or replace procedure PRINT_CUST_PROC(
p_hiordername in number)
as
l_return int;
l_customer_name varchar2(1000);
l_hiorder int;
begin
/* This procedure should show the name of the customer who have the highest
amount of order which will be available upon invoking the function above
*/
l_return : = GET_HIGHORDER_FUNC(l_customer_name,l_hiorder);
dbms_output.put_line(l_customer_name);
dbms_output.put_line(l_hiorder);
end;
answered Nov 23 '18 at 14:03
George JosephGeorge Joseph
1,590510
Just invoke it as you would with any function?
This is how i would try based on your latest comments
create or replace FUNCTION GET_HIGHORDER_FUNC(c_customer_name out varchar2,c_hiorder out int)
return number
AS
c_hiorder number;
BEGIN
select y.customer_name
,y.summed_price
into customer_name
,c_hiorder
from (
select x.customer_name
,x.summed_price
,row_number() over(order by x.summed_price desc) as rnk
from (
select customer_name
,sum(product.product_standardprice * orderline.ordered_quantity) as summed_price
from customer, product, orderline, orders
where customer.customer_id = orders.customer_id
and orders.order_id = orderline.order_id
and orderline.product_id = product.product_id
group by customer_name
)x
)y
where y.rnk=1;
RETURN 1;
END GET_HIGHORDER_FUNC;
create or replace procedure PRINT_CUST_PROC(
p_hiordername in number)
as
l_return int;
l_customer_name varchar2(1000);
l_hiorder int;
begin
/* This procedure should show the name of the customer who have the highest
amount of order which will be available upon invoking the function above
*/
l_return : = GET_HIGHORDER_FUNC(l_customer_name,l_hiorder);
dbms_output.put_line(l_customer_name);
dbms_output.put_line(l_hiorder);
end;
answered Nov 23 '18 at 14:03
George JosephGeorge Joseph
1,590510
edited Nov 23 '18 at 14:45
answered Nov 23 '18 at 14:03
George JosephGeorge Joseph
1,590510
answered Nov 23 '18 at 14:03
George JosephGeorge Joseph
1,590510
answered Nov 23 '18 at 14:03
George JosephGeorge Joseph
1,590510
1,590510
Actually here's the problem I'm dealing with. I'm very new to this things. sorry. Create a procedure that displays the name of the customer with the highest amount of orders among all customers. Name the procedure PRINT_CUST_PROC. The procedure will also invoke a function called GET_HIGHORDER_FUNC that retrieve s the name and total amount ordered of customer with the highest amount of order . Invoke the procedure and view the results.
– Sho
Nov 23 '18 at 14:22
I Tested the function and I got the output which I think is corret.
– Sho
Nov 23 '18 at 14:26
check and share your feedback
– George Joseph
Nov 23 '18 at 17:08
It worked, just few editing and boom! Awesome. Thank you so much George!
– Sho
Nov 25 '18 at 12:29
add a comment |
Actually here's the problem I'm dealing with. I'm very new to this things. sorry. Create a procedure that displays the name of the customer with the highest amount of orders among all customers. Name the procedure PRINT_CUST_PROC. The procedure will also invoke a function called GET_HIGHORDER_FUNC that retrieve s the name and total amount ordered of customer with the highest amount of order . Invoke the procedure and view the results.
– Sho
Nov 23 '18 at 14:22
I Tested the function and I got the output which I think is corret.
– Sho
Nov 23 '18 at 14:26
check and share your feedback
– George Joseph
Nov 23 '18 at 17:08
It worked, just few editing and boom! Awesome. Thank you so much George!
– Sho
Nov 25 '18 at 12:29
Actually here's the problem I'm dealing with. I'm very new to this things. sorry. Create a procedure that displays the name of the customer with the highest amount of orders among all customers. Name the procedure PRINT_CUST_PROC. The procedure will also invoke a function called GET_HIGHORDER_FUNC that retrieve s the name and total amount ordered of customer with the highest amount of order . Invoke the procedure and view the results.
– Sho
Nov 23 '18 at 14:22
Actually here's the problem I'm dealing with. I'm very new to this things. sorry. Create a procedure that displays the name of the customer with the highest amount of orders among all customers. Name the procedure PRINT_CUST_PROC. The procedure will also invoke a function called GET_HIGHORDER_FUNC that retrieve s the name and total amount ordered of customer with the highest amount of order . Invoke the procedure and view the results.
– Sho
Nov 23 '18 at 14:22
I Tested the function and I got the output which I think is corret.
– Sho
Nov 23 '18 at 14:26
I Tested the function and I got the output which I think is corret.
– Sho
Nov 23 '18 at 14:26
check and share your feedback
– George Joseph
Nov 23 '18 at 17:08
check and share your feedback
– George Joseph
Nov 23 '18 at 17:08
It worked, just few editing and boom! Awesome. Thank you so much George!
– Sho
Nov 25 '18 at 12:29
It worked, just few editing and boom! Awesome. Thank you so much George!
– Sho
Nov 25 '18 at 12:29
add a comment |
draft saved
draft discarded
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.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53447859%2finvoke-a-function-from-a-procedure%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
2
(Unrelated) Tip of today: Use the modern, explicit
JOINsyntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.– jarlh
Nov 23 '18 at 13:58