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 Table 4.9.1-11 . 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 table2
Can be defined to include transactional SQL statements2
Can create, alter or delete database tables, views, procedures or functions2
|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|
|1||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.|
|2||Access to features that make changes to data or database resources may be limited by database security settings. [ a b c ]|