Page History
As noted in 4.6. Enabling Versioned Views, database views allow useful queries to be saved and reused as though they were database tables. The SQL queries used in a view can be complex and can include data from other views such as those described in 4.8. Composite Views. However, the definition of each view is defined by a single SQL query.
Stored procedures and functions provide an another way to define reusable resources in a database. The key difference between these database views, stored procedures and functions are summarized in
Footnote Macro |
---|
The features of stored procedures and functions shown in the table are those that apply to MySQL. Some of these features may differ in other database environments. |
. From a practical perspective these differences enable stored procedures to facilitate some types of access to a SNOMED CT data that cannot be supported by using database views. The following subsections describe a few examples of stored procedures that are included in the SNOMED CT example database.
Feature | Database View | Stored Procedure | Stored Function | ||
Enable definition of reusable resources that facilitate commonly required processes that access data without | |||||
Defined by a single query | |||||
Produce output that can queried in the same way as a database table | |||||
Can be defined to output the results of a single SQL query | |||||
Can be defined to output the results of one or more SQL queries | |||||
Can be defined to add, delete or alter data in a table
| |||||
Can be defined to include transactional SQL statements
| |||||
Can create, alter or delete database tables, views, procedures or functions
| |||||
Can be defined with input parameters to be set when invoked with values that affect the results | |||||
Can be defined to set values the values of one or more output parameters | |||||
Can be defined to return a single value of a specified datatype |
...
Display Footnotes Macro |
---|