Search



Page tree

  

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)

FeatureDatabase ViewStored ProcedureStored Function
Enable definition of reusable resources that facilitate commonly required processes that access data without(tick)(tick)(tick)
Defined by a single query(tick)(error)(error)
Produce output that can queried in the same way as a database table(tick)(error)(error)
Can be defined to output the results of a single SQL query(tick)(tick)(error)
Can be defined to output the results of one or more SQL queries(error)(tick)(error)

Can be defined to add, delete or alter data in a table2

(error)(tick)(tick)

Can be defined to include transactional SQL statements2

(error)(tick)(error)

Can create, alter or delete database tables, views, procedures or functions2

(error)(tick)(tick)
Can be defined with input parameters to be set when invoked with values that affect the results(error)(tick)(tick)
Can be defined to set values the values of one or more output parameters(error)(tick)(error)
Can be defined to return a single value of a specified datatype(error)(error)(tick)



Footnotes
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
  • No labels