runstoredprocedure
Call stored procedure with and without input and output arguments
Syntax
Description
This function calls a stored procedure that has no input arguments, no output arguments, or any combination of input and output arguments. Define and instantiate this stored procedure in your database.
You can use this function if you connect to your database using a JDBC driver. For details,
see Connect to Database. If you are using
the native ODBC interface to connect to your database, use execute
to call the stored procedure.
Examples
Call a Stored Procedure Without Input and Output Arguments
Define a stored procedure named create_table
that
creates a table named test_table
by executing this
code. This procedure has no input or output arguments. This code assumes
you are using a Microsoft®
SQL Server® database.
CREATE PROCEDURE create_table AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; CREATE TABLE test_table ( CATEGORY_ID INTEGER IDENTITY PRIMARY KEY, CATEGORY_DESC CHAR(50) NOT NULL ); END GO
Create a Microsoft
SQL Server database
connection conn
using the JDBC driver. For details,
see Connect to Database. Then, call the stored procedure create_table
using
the database connection conn
.
results = runstoredprocedure(conn,'create_table')
results = 0
results
returns 0 because calling create_table
does
not return a data set.
Check your database for a new table named test_table
.
Close the database connection conn
.
close(conn)
Call a Stored Procedure with Input Arguments
Define a stored procedure named insert_data
that
inserts a category description into a table named test_create
by
executing this code. This procedure has one input argument data
.
This code assumes you are using a Microsoft
SQL Server database.
CREATE PROCEDURE insert_data @data varchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO test_create (CATEGORY_DESC) VALUES (@data) END GO
Create a Microsoft
SQL Server database
connection conn
using the JDBC driver. For details,
see Connect to Database. Then, call the stored procedure insert_data
using
the database connection conn
with the category
description Apples
as the input argument.
inputarg = {'Apples'}; results = runstoredprocedure(conn,'insert_data',inputarg)
results = 0
results
returns 0 because calling insert_data
does
not return a data set.
The table test_create
adds a row where the
column CATEGORY_ID
equals 1
and
the column CATEGORY_DESCRIPTION
equals Apples
.
CATEGORY_ID
is the primary key of the table test_create
.
This primary key increments automatically. CATEGORY_ID
equals 1
when
calling insert_data
for the first time.
Close the database connection conn
.
close(conn)
Call a Stored Procedure with Output Arguments
Define a stored procedure named maxDecVolume
that
selects the maximum sales volume in December by executing this code.
This procedure has one output argument data
and
no input arguments. This code assumes you are using a Microsoft
SQL Server database.
CREATE PROCEDURE maxDecVolume @data int OUTPUT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT @data = max(December) FROM salesVolume END GO
Create a Microsoft
SQL Server database
connection conn
using the JDBC driver. For details,
see Connect to Database. Then, call the stored procedure
using:
Database connection
conn
Stored procedure
maxDecVolume
Empty brackets to denote no input arguments
Numeric Java® data type
outputtype
outputtype = {java.sql.Types.NUMERIC};
results = runstoredprocedure(conn,'maxDecVolume',[],outputtype)
results = [1x1 java.math.BigDecimal]
results
returns a cell array that contains
the maximum sales volume as a Java decimal data type.
Display the value in results
.
results{1}
ans = 35000
The maximum sales volume in December is 35,000.
Close the database connection conn
.
close(conn)
Call a Stored Procedure with Input and Output Arguments
Define a stored procedure named getSuppCount
that
counts the number of suppliers for a specified city by executing this
code. This procedure has one input argument cityName
and
one output argument suppCount
. This code assumes
you are using a Microsoft
SQL Server database.
CREATE PROCEDURE getSuppCount (@cityName varchar(20), @suppCount int OUTPUT) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT @suppCount = count(supplierNumber) FROM suppliers WHERE City = @cityName; END GO
Create a Microsoft
SQL Server database
connection conn
using the JDBC driver. For details,
see Connect to Database. Then, call the stored procedure getSuppCount
using
the database connection conn
. The input argument inputarg
is
a cell array containing the character vector 'New York'
.
The output Java data type outputtype
is numeric.
inputarg = {'New York'}; outputtype = {java.sql.Types.NUMERIC}; results = runstoredprocedure(conn,'getSuppCount',inputarg,outputtype)
results = [1x1 java.math.BigDecimal]
results
is a cell array that contains the
supplier count as a Java decimal data type.
Display the value in results
.
results{1}
ans = 6.0000
There are six suppliers in New York.
Close the database connection conn
.
close(conn)
Call a Stored Procedure with Multiple Input and Output Arguments
Define a stored procedure named productsWithinUnitCost
that
returns the product number and description for products that have
a unit cost in a specified range by executing this code. This procedure
has two input arguments minUnitCost
and maxUnitCost
.
This procedure has two output arguments productno
and productdesc
.
This code assumes you are using a Microsoft
SQL Server database.
CREATE PROCEDURE productsWithinUnitCost (@minUnitCost INT, @maxUnitCost INT, @productno INT OUTPUT, @productdesc VARCHAR(50) OUTPUT) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT @productno = productNumber, @productdesc = productDescription FROM productTable WHERE unitCost > @minUnitCost AND unitCost < @maxUnitCost END GO
Create a Microsoft
SQL Server database
connection conn
using the JDBC driver. For details,
see Connect to Database. Then, call the stored procedure
using:
Database connection
conn
Stored procedure
productsWithinUnitCost
Input arguments
inputargs
to specify a unit cost between 19 and 21Output Java data types
outputtypes
to specify numeric and string data types for product number and description
inputargs = {19,21}; outputtypes = {java.sql.Types.NUMERIC,java.sql.Types.VARCHAR}; results = runstoredprocedure(conn,'productsWithinUnitCost',... inputargs,outputtypes)
results = [1x1 java.math.BigDecimal] 'Snacks'
results
returns a cell array that contains
the product number as a Java decimal data type and the product
description as a string.
Display the product number in results
.
results{1}
ans = 15
The product with product number 15 has a unit cost between 19 and 21.
Display the product description in results
.
results{2}
ans = Snacks
The product with product number 15 has the product description Snacks
.
Here, the narrow unit cost range returns only one product. If
the unit cost range is wider, then more than one product might satisfy
this condition. To return a data set with numerous products, use exec
and fetch
to
call this stored procedure. Otherwise, runstoredprocedure
returns
only the last row in the data set.
Close the database connection conn
.
close(conn)
Input Arguments
conn
— Database connection
connection
object
Database connection, specified as an ODBC connection
object or JDBC connection
object created using the
database
function.
spname
— Stored procedure name
character vector
Stored procedure name, specified as a character vector that contains the name of the stored procedure that is defined and instantiated in your database.
Data Types: char
inputargs
— Input arguments
cell array
Input arguments, specified as a cell array of one or more values for each input argument of the stored procedure. Input arguments can be only basic data types such as double, character vector, logical, and so on.
Data Types: cell
outputtypes
— Output types
cell array
Output types, specified as a cell array of one or more Java data
types for the output arguments of the stored procedure. Some JDBC
drivers do not support all java.sql.Types
. Consult
your JDBC driver documentation to find the supported types. Match
them to the data types found in your stored procedure.
Example: {java.sql.Types.NUMERIC}
Data Types: cell
Output Arguments
results
— Stored procedure results
logical | cell array
Stored procedure results, returned as a logical or cell array.
runstoredprocedure
returns a logical 1
when calling
the stored procedure returns a data set. Otherwise,
runstoredprocedure
returns a logical
0
. If the stored procedure returns a data set, use
exec
and fetch
to call the stored
procedure and retrieve the data set.
runstoredprocedure
returns a cell array
when you specify one or more output Java data types for the output
arguments of the stored procedure. Use cell array indexing to retrieve
the output argument values.
Version History
Introduced in R2006b
MATLAB 명령
다음 MATLAB 명령에 해당하는 링크를 클릭했습니다.
명령을 실행하려면 MATLAB 명령 창에 입력하십시오. 웹 브라우저는 MATLAB 명령을 지원하지 않습니다.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)