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.
Table 4.9.1-1: Features of Views, Stored Procedures and Functions (in MySQL)
| 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 | | | |
| Ref | Notes |
|---|---|
| 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 ] |
Feedback
Overview
Content Tools
Apps