How to create a PL/SQL stored procedure with input parameter to return a dataset
I am new to PL/SQL and trying to create a stored procedure with some input parameters that we will be using to generate our reports.
The aim is to be able to generate 100s or rows of data based on the input parameter that the user will input.
I can do it in SQL Server but I am not sure how to go about it in Oracle.
For example:
Create Procedure usp_employees
(@Deptid int not null,
@MaritalStatus varchar(10) null)
As
Begin
Select Firstname, LastName, HireDate, DepartmentName, Marital_Status
From EmployeeTable
Where DeparatmentID = @Deptid
and Marital_Status = @MaritalStatus
End;
And then I execute it like this:
Execute usp_employees 1,'F';
Can someone please point me on how to recreate and execute this using PL/SQL?
Thanks.
sql oracle plsql
edited Jan 20 at 8:01
marc_s
576k12811111258
asked Jan 19 at 23:39
BdkBdk
32
add a comment |
I am new to PL/SQL and trying to create a stored procedure with some input parameters that we will be using to generate our reports.
The aim is to be able to generate 100s or rows of data based on the input parameter that the user will input.
I can do it in SQL Server but I am not sure how to go about it in Oracle.
For example:
Create Procedure usp_employees
(@Deptid int not null,
@MaritalStatus varchar(10) null)
As
Begin
Select Firstname, LastName, HireDate, DepartmentName, Marital_Status
From EmployeeTable
Where DeparatmentID = @Deptid
and Marital_Status = @MaritalStatus
End;
And then I execute it like this:
Execute usp_employees 1,'F';
Can someone please point me on how to recreate and execute this using PL/SQL?
Thanks.
sql oracle plsql
edited Jan 20 at 8:01
marc_s
576k12811111258
asked Jan 19 at 23:39
BdkBdk
32
Which Oracle version are you using?
– a_horse_with_no_name
Jan 20 at 8:56
I believe it is 11g.
– Bdk
Jan 21 at 4:46
With a more up-to-date version you could use an implicit result set: stackoverflow.com/a/40360471
– a_horse_with_no_name
Jan 21 at 8:02
add a comment |
I am new to PL/SQL and trying to create a stored procedure with some input parameters that we will be using to generate our reports.
The aim is to be able to generate 100s or rows of data based on the input parameter that the user will input.
I can do it in SQL Server but I am not sure how to go about it in Oracle.
For example:
Create Procedure usp_employees
(@Deptid int not null,
@MaritalStatus varchar(10) null)
As
Begin
Select Firstname, LastName, HireDate, DepartmentName, Marital_Status
From EmployeeTable
Where DeparatmentID = @Deptid
and Marital_Status = @MaritalStatus
End;
And then I execute it like this:
Execute usp_employees 1,'F';
Can someone please point me on how to recreate and execute this using PL/SQL?
Thanks.
sql oracle plsql
edited Jan 20 at 8:01
marc_s
576k12811111258
asked Jan 19 at 23:39
BdkBdk
32
I am new to PL/SQL and trying to create a stored procedure with some input parameters that we will be using to generate our reports.
The aim is to be able to generate 100s or rows of data based on the input parameter that the user will input.
I can do it in SQL Server but I am not sure how to go about it in Oracle.
For example:
Create Procedure usp_employees
(@Deptid int not null,
@MaritalStatus varchar(10) null)
As
Begin
Select Firstname, LastName, HireDate, DepartmentName, Marital_Status
From EmployeeTable
Where DeparatmentID = @Deptid
and Marital_Status = @MaritalStatus
End;
And then I execute it like this:
Execute usp_employees 1,'F';
Can someone please point me on how to recreate and execute this using PL/SQL?
Thanks.
sql oracle plsql
sql oracle plsql
edited Jan 20 at 8:01
marc_s
576k12811111258
asked Jan 19 at 23:39
BdkBdk
32
edited Jan 20 at 8:01
marc_s
576k12811111258
asked Jan 19 at 23:39
BdkBdk
32
edited Jan 20 at 8:01
marc_s
576k12811111258
edited Jan 20 at 8:01
marc_s
576k12811111258
edited Jan 20 at 8:01
marc_s
576k12811111258
576k12811111258
asked Jan 19 at 23:39
BdkBdk
32
asked Jan 19 at 23:39
BdkBdk
32
asked Jan 19 at 23:39
BdkBdk
32
32
Which Oracle version are you using?
– a_horse_with_no_name
Jan 20 at 8:56
I believe it is 11g.
– Bdk
Jan 21 at 4:46
With a more up-to-date version you could use an implicit result set: stackoverflow.com/a/40360471
– a_horse_with_no_name
Jan 21 at 8:02
add a comment |
Which Oracle version are you using?
– a_horse_with_no_name
Jan 20 at 8:56
I believe it is 11g.
– Bdk
Jan 21 at 4:46
With a more up-to-date version you could use an implicit result set: stackoverflow.com/a/40360471
– a_horse_with_no_name
Jan 21 at 8:02
Which Oracle version are you using?
– a_horse_with_no_name
Jan 20 at 8:56
Which Oracle version are you using?
– a_horse_with_no_name
Jan 20 at 8:56
I believe it is 11g.
– Bdk
Jan 21 at 4:46
I believe it is 11g.
– Bdk
Jan 21 at 4:46
With a more up-to-date version you could use an implicit result set: stackoverflow.com/a/40360471
– a_horse_with_no_name
Jan 21 at 8:02
With a more up-to-date version you could use an implicit result set: stackoverflow.com/a/40360471
– a_horse_with_no_name
Jan 21 at 8:02
add a comment |
1 Answer
1
active
oldest
votes
You can do this:
CREATE OR REPLACE PROCEDURE usp_employees
(p_dept_id IN int,
p_MaritalStatus IN VARCHAR2(10),
p_cursor OUT SYS_REFCURSOR)
IS
BEGIN
Open p_cursor for
select Firstname, LastName, HireDate,
DepartmentName, Marital_Status
from EmployeeTable
where DeparatmentID=p_dept_id
and Marital_Status=p_maritalstatus;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
/
To run, through the GUI, right click on stored procedure, then click Test. Provide the input parameters and click on execute. You can either Step into the code or Over. Once complete, select “...” next to the ref cursor to view resultset.
Through the query,
SQL> var rc refcursor
SQL> execute myproc(10, “Single”, :test)
SQL> print rc
answered Jan 19 at 23:51
GauravsaGauravsa
3,0691817
Thanks Gauravsa. What is the best way to execute it to view the result?
– Bdk
Jan 20 at 0:17
Usually I right click on stored procedure and click Test. Then provide input in the test window and click on “...” to view the resultset
– Gauravsa
Jan 20 at 1:37
It worked! Thank you Gauravsa. You are awesome!
– Bdk
Jan 20 at 1:46
If you have a procedure with one output parameter then I would rather create a functionCREATE OR REPLACE FUNCTION usp_employees (p_dept_id IN integer, p_MaritalStatus IN VARCHAR2) RETURN SYS_REFCURSOR IS ...
– Wernfried Domscheit
Jan 20 at 12:17
Wernfried Domscheit, what are some of the pros of creating a function rather than a stored proc in this case?
– Bdk
Jan 21 at 4:55
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%2f54272311%2fhow-to-create-a-pl-sql-stored-procedure-with-input-parameter-to-return-a-dataset%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 can do this:
CREATE OR REPLACE PROCEDURE usp_employees
(p_dept_id IN int,
p_MaritalStatus IN VARCHAR2(10),
p_cursor OUT SYS_REFCURSOR)
IS
BEGIN
Open p_cursor for
select Firstname, LastName, HireDate,
DepartmentName, Marital_Status
from EmployeeTable
where DeparatmentID=p_dept_id
and Marital_Status=p_maritalstatus;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
/
To run, through the GUI, right click on stored procedure, then click Test. Provide the input parameters and click on execute. You can either Step into the code or Over. Once complete, select “...” next to the ref cursor to view resultset.
Through the query,
SQL> var rc refcursor
SQL> execute myproc(10, “Single”, :test)
SQL> print rc
answered Jan 19 at 23:51
GauravsaGauravsa
3,0691817
Thanks Gauravsa. What is the best way to execute it to view the result?
– Bdk
Jan 20 at 0:17
Usually I right click on stored procedure and click Test. Then provide input in the test window and click on “...” to view the resultset
– Gauravsa
Jan 20 at 1:37
It worked! Thank you Gauravsa. You are awesome!
– Bdk
Jan 20 at 1:46
If you have a procedure with one output parameter then I would rather create a functionCREATE OR REPLACE FUNCTION usp_employees (p_dept_id IN integer, p_MaritalStatus IN VARCHAR2) RETURN SYS_REFCURSOR IS ...
– Wernfried Domscheit
Jan 20 at 12:17
Wernfried Domscheit, what are some of the pros of creating a function rather than a stored proc in this case?
– Bdk
Jan 21 at 4:55
add a comment |
You can do this:
CREATE OR REPLACE PROCEDURE usp_employees
(p_dept_id IN int,
p_MaritalStatus IN VARCHAR2(10),
p_cursor OUT SYS_REFCURSOR)
IS
BEGIN
Open p_cursor for
select Firstname, LastName, HireDate,
DepartmentName, Marital_Status
from EmployeeTable
where DeparatmentID=p_dept_id
and Marital_Status=p_maritalstatus;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
/
To run, through the GUI, right click on stored procedure, then click Test. Provide the input parameters and click on execute. You can either Step into the code or Over. Once complete, select “...” next to the ref cursor to view resultset.
Through the query,
SQL> var rc refcursor
SQL> execute myproc(10, “Single”, :test)
SQL> print rc
answered Jan 19 at 23:51
GauravsaGauravsa
3,0691817
Thanks Gauravsa. What is the best way to execute it to view the result?
– Bdk
Jan 20 at 0:17
Usually I right click on stored procedure and click Test. Then provide input in the test window and click on “...” to view the resultset
– Gauravsa
Jan 20 at 1:37
It worked! Thank you Gauravsa. You are awesome!
– Bdk
Jan 20 at 1:46
If you have a procedure with one output parameter then I would rather create a functionCREATE OR REPLACE FUNCTION usp_employees (p_dept_id IN integer, p_MaritalStatus IN VARCHAR2) RETURN SYS_REFCURSOR IS ...
– Wernfried Domscheit
Jan 20 at 12:17
Wernfried Domscheit, what are some of the pros of creating a function rather than a stored proc in this case?
– Bdk
Jan 21 at 4:55
add a comment |
You can do this:
CREATE OR REPLACE PROCEDURE usp_employees
(p_dept_id IN int,
p_MaritalStatus IN VARCHAR2(10),
p_cursor OUT SYS_REFCURSOR)
IS
BEGIN
Open p_cursor for
select Firstname, LastName, HireDate,
DepartmentName, Marital_Status
from EmployeeTable
where DeparatmentID=p_dept_id
and Marital_Status=p_maritalstatus;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
/
To run, through the GUI, right click on stored procedure, then click Test. Provide the input parameters and click on execute. You can either Step into the code or Over. Once complete, select “...” next to the ref cursor to view resultset.
Through the query,
SQL> var rc refcursor
SQL> execute myproc(10, “Single”, :test)
SQL> print rc
answered Jan 19 at 23:51
GauravsaGauravsa
3,0691817
You can do this:
CREATE OR REPLACE PROCEDURE usp_employees
(p_dept_id IN int,
p_MaritalStatus IN VARCHAR2(10),
p_cursor OUT SYS_REFCURSOR)
IS
BEGIN
Open p_cursor for
select Firstname, LastName, HireDate,
DepartmentName, Marital_Status
from EmployeeTable
where DeparatmentID=p_dept_id
and Marital_Status=p_maritalstatus;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
/
To run, through the GUI, right click on stored procedure, then click Test. Provide the input parameters and click on execute. You can either Step into the code or Over. Once complete, select “...” next to the ref cursor to view resultset.
Through the query,
SQL> var rc refcursor
SQL> execute myproc(10, “Single”, :test)
SQL> print rc
answered Jan 19 at 23:51
GauravsaGauravsa
3,0691817
edited Jan 21 at 5:22
answered Jan 19 at 23:51
GauravsaGauravsa
3,0691817
answered Jan 19 at 23:51
GauravsaGauravsa
3,0691817
answered Jan 19 at 23:51
GauravsaGauravsa
3,0691817
3,0691817
Thanks Gauravsa. What is the best way to execute it to view the result?
– Bdk
Jan 20 at 0:17
Usually I right click on stored procedure and click Test. Then provide input in the test window and click on “...” to view the resultset
– Gauravsa
Jan 20 at 1:37
It worked! Thank you Gauravsa. You are awesome!
– Bdk
Jan 20 at 1:46
If you have a procedure with one output parameter then I would rather create a functionCREATE OR REPLACE FUNCTION usp_employees (p_dept_id IN integer, p_MaritalStatus IN VARCHAR2) RETURN SYS_REFCURSOR IS ...
– Wernfried Domscheit
Jan 20 at 12:17
Wernfried Domscheit, what are some of the pros of creating a function rather than a stored proc in this case?
– Bdk
Jan 21 at 4:55
add a comment |
Thanks Gauravsa. What is the best way to execute it to view the result?
– Bdk
Jan 20 at 0:17
Usually I right click on stored procedure and click Test. Then provide input in the test window and click on “...” to view the resultset
– Gauravsa
Jan 20 at 1:37
It worked! Thank you Gauravsa. You are awesome!
– Bdk
Jan 20 at 1:46
If you have a procedure with one output parameter then I would rather create a functionCREATE OR REPLACE FUNCTION usp_employees (p_dept_id IN integer, p_MaritalStatus IN VARCHAR2) RETURN SYS_REFCURSOR IS ...
– Wernfried Domscheit
Jan 20 at 12:17
Wernfried Domscheit, what are some of the pros of creating a function rather than a stored proc in this case?
– Bdk
Jan 21 at 4:55
Thanks Gauravsa. What is the best way to execute it to view the result?
– Bdk
Jan 20 at 0:17
Thanks Gauravsa. What is the best way to execute it to view the result?
– Bdk
Jan 20 at 0:17
Usually I right click on stored procedure and click Test. Then provide input in the test window and click on “...” to view the resultset
– Gauravsa
Jan 20 at 1:37
Usually I right click on stored procedure and click Test. Then provide input in the test window and click on “...” to view the resultset
– Gauravsa
Jan 20 at 1:37
It worked! Thank you Gauravsa. You are awesome!
– Bdk
Jan 20 at 1:46
It worked! Thank you Gauravsa. You are awesome!
– Bdk
Jan 20 at 1:46
If you have a procedure with one output parameter then I would rather create a function
CREATE OR REPLACE FUNCTION usp_employees (p_dept_id IN integer, p_MaritalStatus IN VARCHAR2) RETURN SYS_REFCURSOR IS ...– Wernfried Domscheit
Jan 20 at 12:17
If you have a procedure with one output parameter then I would rather create a function
CREATE OR REPLACE FUNCTION usp_employees (p_dept_id IN integer, p_MaritalStatus IN VARCHAR2) RETURN SYS_REFCURSOR IS ...– Wernfried Domscheit
Jan 20 at 12:17
Wernfried Domscheit, what are some of the pros of creating a function rather than a stored proc in this case?
– Bdk
Jan 21 at 4:55
Wernfried Domscheit, what are some of the pros of creating a function rather than a stored proc in this case?
– Bdk
Jan 21 at 4:55
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%2f54272311%2fhow-to-create-a-pl-sql-stored-procedure-with-input-parameter-to-return-a-dataset%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
Which Oracle version are you using?
– a_horse_with_no_name
Jan 20 at 8:56
I believe it is 11g.
– Bdk
Jan 21 at 4:46
With a more up-to-date version you could use an implicit result set: stackoverflow.com/a/40360471
– a_horse_with_no_name
Jan 21 at 8:02