Tuesday, June 14, 2016

Developing and Refining my Understanding of some more Functions and Operations

Some of the lessons this week involved processing and formatting text using various functions, such as concatenation, which has a rather strange | | syntax, upper/lower, substring, to_char, year, month and other date/time related functions.  Additionally, we've explored creating sequences for automatically generated, consecutive/numerical (datatype is object) identity attributes, which comes in handy with table entities that lack good, natural candidate keys.  The create view operation is also handy for generating a storable, virtual tabular report based on a more lengthy select query that can be re-run based, with the select operator, on its given variable name alone.

I've also learned that SQL does not support conditional or looping operations like other programming languages; however, persistent stored modules (PSMs), blocks of code containing standard statements, can be saved and run.  MS SQL uses Procedural Language SQL (PL/SQL) to store and run or anonymously run such procedural code traditionally used in programming within the database.  The blocks can use DECLARE or BEGIN/END at each end.  The former can be saved with a variable name, which is convenient for business operations.  Similarly, stored procedures and triggers can automatically invoke such procedural code blocks.  With respect to triggers, the text gives one example of the updating of a product inventory number upon a sale and the flagging of inventory levels when quantity falls below a certain number, so items can be re-ordered.  (These are not things one should have to remember to periodically do.)  The syntax involves that mentioned just above plus timing and event indications.  Otherwise, as throughout, I've tried to focus, when in situations where multiple solutions exist, to use the most efficient or precise command or function to achieve the desired result.

No comments:

Post a Comment