Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
1999-12-22
2003-01-14
Coby, Frantz (Department: 2171)
Data processing: database and file management or data structures
Database design
Data structure types
C703S003000, C703S004000, C703S007000, C703S007000
Reexamination Certificate
active
06507834
ABSTRACT:
BACKGROUND OF THE INVENTION
1. Field of the Invention
The present invention relates to systems and methods for performing queries on data stored in a database, and in particular to a method and system for executing SQL from stored procedures.
2. Description of the Related Art
The ability to manage massive amounts of information has become a virtual necessity in business today. The information and data are often stored in related files. A set of related files is referred to as a database. A database management system DBMS) creates and manages one or more databases. Today, DBMSs can manage any form of data including text, images, sound and video. Further, large-scale integrated DBMS' provide an efficient, consistent, and secure means for storing and retrieving the vast amounts of data.
Certain computer languages have been developed and utilized to interact with and manipulate the data. For example, SQL (Structured Query Language) is a language used to interrogate and process data in a relational database (a database in which relationships are established between files and information stored in the database). Originally developed for mainframes, most database systems designed for client/sever environments support SQL. SQL commands can be used to interactively work with a database or can be embedded within a programming language to interface to a database. Thus, methods and functions may embed and utilize SQL commands.
Stored Procedures
Users/programmers often need to invoke the same set of commands (or the same set of commands with different parameters) at varying times and locations of a program. In such a situation, the query may be placed into a stored procedure. A stored procedure is a batch of SQL statements stored in a database/on a server, that may be partially or fully processed/compiled before it is stored (or upon its first invocation). Additionally, a stored procedure is a method or procedure written in any programming language that is partially or fully processed/compiled before it is stored (or upon its first invocation).
Stored procedures may be called directly from a client or from a database trigger and are often stored on the server. A database trigger is a user defined mechanism that causes a stored procedure to automatically initiate and execute upon the occurrence of the user specified events in the database (i.e., when the trigger “fires”). Thus, the trigger will not “fire” unless the event(s) specified by the user occurs. For example, a user may define a trigger to automatically “fire” whenever a user updates, deletes, or inserts data.
Since the stored procedure is stored on a server, the stored procedure is available to all clients and does not need to be replicated in each client. Further, by storing the stored procedure on the server, when the stored procedure is modified, all clients automatically get/have access to the new version. This saves programming effort especially when different client user interfaces and development systems are used. Further, this allows stored procedures to be an easy mechanism for sharing complex queries and functions between multiple applications. Additionally, SQL and stored procedures may call other stored procedures and may be written independently from (and without knowledge of) the underlying DBMS.
A stored procedure may be partially or completely processed/compiled before it is stored on the database. Consequently, the stored procedure does not have to be parsed and compiled each time it is invoked. Further, because a stored procedure is stored in a compiled format, it executes faster than if its constituent commands were executed individually.
Alternatively, a stored procedure may not be compiled prior to storage but may be automatically compiled the first time the procedure is invoked. As part of such a compilation, a query execution plan may be generated. The query execution plan describes the order in which tables are to be accessed and the indexes to be used. Further, the query execution plan is optimized for the stored procedure parameters and data in the database tables at the time the stored procedure is first executed.
A stored procedure may be invoked by its name. The caller can pass parameters to and receive results from the stored procedure. A user can create and name a stored procedure to execute specific database queries and perform other database tasks. For example, a user may create a stored procedure that returns the number of videos of a particular movie remaining in a video store for the video title that is specified at the time the stored procedure is called.
Stored procedures may also maintain the integrity of the database and prevent unauthorized users from modifying certain entries. For example, a user may be given the right to call a stored procedure that updates a table or set of tables but denied the right to update the tables directly.
Stored procedures may be created using a variety of mechanisms. The following format may be utilized to declare a stored procedure:
create proc {procedure name}
as
{statement of block of statements}
For example the following stored procedure called myproc will return the number of Casablanca videos left in a video store as well as other movie titles, the rental price of those movie titles, and the location of those videos when Humphrey Bogart is an actor in the movie:
create proc myproc
as
begin
select inv_videos
from video
where title_id = “Casablanca”
select title_id, rental_price, location
from video
where actor = “Humphrey Bogart”
end
As described above, stored procedures can also be passed parameters. Parameters may be defined as part of the stored procedure creation statement. The syntax of a “create proc” command with parameters is:
create proc {proc name}
( @{param_name} {param_type},
@{param_name} {param_type},
{. . .}
)
as
{statement}
For example, the following stored procedure may be passed the @mytitle and @myactor parameters for use in the select query:
create proc myproc
( @mytitle char,
@myactor char
)
as
begin
select inv_videos, title_id, rental_price, location
from video
where title_id = @mytitle
or actor = @myactor
end
Once a stored procedure has been created, a user can invoke the stored procedure using the following syntax:
exec [database.owner.] {procname} {opt params}
In the above syntax, “database” and “owner” will default to the current database and the current dbo (database owner). For example, the following command may invoke the stored procedure “myproc” defined above:
exec myproc “Casablanca”, “Humphrey Bogart”
In this example, the user would see the same results as if the following command were utilized:
select inv_videos, title_id, rental_price, location
from video
where title_id = “Casablanca”
or actor = “Humphrey Bogart”
Additionally, the “exec” portion of an invocation of a stored procedure is not necessary if the stored procedure call is the first line in a batch.
Stored procedures can also have a “return” status. A “return” statement returns from the stored procedure with an optional status parameter. The return status is zero for success, or negative otherwise. Negative values between −1 and −99 are reserved. For example, the following stored procedure returns a negative value (−999) if no rows/records are in the result set and a 0 if rows/records are in the result set:
create myproc ( @mytitle char, @myactor char)
as
begin
select title_id, rental_price, location
from video
where title_id = @mytitle
or actor = @myactor
if @@rowcount = 0
return −999
else
return 0
end
The following commands illustrate the invocation of the above stored procedure with a return status:
declare @mystatus int
exec @mystatus = myproc “Casablanca”, “Humphrey Bogart”
if @mystatus ! = 0
begin
{do error
Chen Jian-Jun
Kabra Navin
Nag Biswadeep
Patel Jignesh M.
Yu Jie-Bing
Coby Frantz
Gates & Cooper
NCR Corporation
LandOfFree
Method and apparatus for parallel execution of SQL from... does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Method and apparatus for parallel execution of SQL from..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Method and apparatus for parallel execution of SQL from... will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3061548