Stored Procedure, Triggers, Cursor

Stored Procedure

Stored procedure is a database object that is a set of SQL statements stored on the server. Stored procedures are very similar to ordinary procedures in high-level languages, they can have input and output parameters and local variables, they can perform numeric calculations and operations on symbolic data, the results of which can be assigned to variables and parameters. Stored procedures can perform standard database operations (both DDL and DML). In addition, loops and branches are possible in stored procedures, that is, they can use instructions to control the execution process.

Stored procedures improve performance, enhance programmability, and support data security features. In most DBMSs, the first time a stored procedure is run, it is compiled (parsed and a data access plan is generated) and further processing it faster.

Trigger

Trigger is a stored procedure of a special type, which the user does not call directly, but whose execution is conditioned by the action of modifying the data: adding, deleting or changing data in a given table of the relational database. Triggers are used to ensure data integrity and to implement complex business logic. The trigger is automatically started by the server and any modifications it makes to the data are treated as being performed in the transaction in which the action that caused the trigger to fire was performed. Accordingly, if an error or data integrity violation is detected, this transaction can be rolled back.

When a trigger is fired, it is specified using the keywords BEFORE (the trigger is fired before the associated event is executed) or AFTER (after the event). In case the trigger is called before the event, it can make changes to the event-modified record. In addition, triggers can be bound not to a table, but to a VIEW. In this case, they are used to implement the "updatable view" mechanism. In this case, the BEFORE and AFTER keywords only affect the sequence of trigger calls, since the actual event (delete, insert, or update) does not occur.

Cursor

Cursor is a database object that allows applications to work with records one at a time, rather than working with multiple records at once, as is done in regular SQL commands.

The procedure for working with the cursor is as follows:

  • Declare cursor (DECLARE)
  • Open cursor (OPEN)
  • Fetch record from cursor (FETCH)
  • Process entry...
  • Close cursor (CLOSE)
  • Delete cursor reference (DEALLOCATE). When the last reference of the cursor is deleted, SQL frees the data structures that make up the cursor.

Difference between DATETIME and TIMESTAMP data types

DATETIME is for storing an integer: YYYYMMDDHHMMSS. And this time does not depend on the time zone configured on the server. Size: 8 bytes

TIMESTAMP stores a value equal to the number of seconds elapsed since midnight, January 1, 1970, Greenwich Mean Time. When received from the database, it is displayed taking into account the time zone. Size: 4 bytes

For which numeric types is it illegal to use addition/subtraction operations?

The numeric type BIT cannot be used as operands for addition and subtraction operations.

PIVOT and UNPIVOT statements in Transact-SQL

PIVOT and UNPIVOT are non-standard relational operators that are supported by Transact-SQL.

The PIVOT operator expands a table-valued expression by converting the unique values of one column of the expression to multiple output columns, and optionally concatenates the remaining duplicate column values and displays them in the output. The UNPIVOT operator performs the opposite of PIVOT, converting the columns of a table-valued expression to column values.

Basic functions of ranking in Transact-SQL

Ranking functions are functions that return a value for each group record in the result dataset. In practice they can be used, for example, for simple list numbering, ranking or page navigation.

ROW_NUMBER is a Transact-SQL numbering function that simply returns the record number.

RANK returns the rank of each record. In this case, in contrast to ROW_NUMBER, the analysis of the values is already underway and, if the same is found, it returns the same rank with the next one skipping.

DENSE_RANK also returns the rank of each record, but unlike RANK, if the same values are found, it returns the rank without skipping the next one.

NTILE is a Transact-SQL function that divides a result set into groups based on a specific column.

INTERSECT, EXCEPT operators in Transact-SQL

The EXCEPT operator returns unique records from the left input query that are not displayed by the right input query.

The INTERSECT operator returns the unique records displayed by the left and right input queries.

Read also:


Comments

Post a Comment

Popular posts from this blog

Methods for reading XML in Java

Image
DOM (Document Object Model) - object - reads XML, recreating it in memory in the form of an object structure, while the XML document is represented as a set of tags - nodes. Each node can have an unlimited number of child nodes. Each child can also contain several levels of descendants or none at all. Thus, the result is a kind of tree. Pluses: Easy to program. If there are many objects in XML with cross-references to each other, it is enough to go through the document twice: first time create objects without references and fill in the name-object dictionary, the second time - restore links. If an error occurs in XML, a half-created XML structure remains in memory, which will be automatically destroyed. Suitable for both reading and writing. Minuses: Low speed. Wastes a lot of memory. SAX (Simple API for XML) - event-driven - reads an XML document, responding to emerging events (opening or closing tag, string, attribute) by calling the event handlers provided by the appl...
Read more

XML, well-formed XML and valid XML

Image
XML, the eXtensible Markup Language, is a language with a simple formal syntax, well suited for creating and manipulating documents with programs, and at the same time easy to read and create documents by humans. XML is extensible, it does not fix the markup used in documents, and the developer is free to create markup in accordance with the needs of a particular area, being limited only by the syntax rules of the language. DTD A DTD, Document Type Definition, is a predefined set of rules that defines the relationships between elements and attributes. For example, the DTD for HTML says that the DIV tag must be inside the BODY tag and can appear multiple times, TITLE - in HEAD and only once, and SCRIPT - both there and there as many times as you want. A DTD is usually described directly in the document as a formulation line starting with <!DOCTYPE ... > or in a separate file. Difference between well-formed XML and valid XML Depending on the level of compliance, the document...
Read more

ArrayList and LinkedList in Java, memory usage and speed

Image
How much extra memory is needed when calling ArrayList.add()? If there is enough space in the array to accommodate a new element, then no additional memory is required. Otherwise, a new array is created, 1.5 times larger than the existing one (this is true for JDK above 1.7, in earlier versions the size of the increase is different). How much extra memory is allocated when you call LinkedList.add()? One new instance of the nested Node class is created. How much memory to store one byte primitive in LinkedList? Each LinkedList element stores a link to the previous element, the next element, and a data link. private static class Node<E> { E item; Node<E> next; Node<E> prev; //... } For 32-bit systems, each link is 32 bits (4 bytes). The object (header) of the nested Node class itself takes 8 bytes. 4 + 4 + 4 + 8 = 20 bytes, and since the size of each object in Java is a multiple of 8, so we get 24 bytes. A byte primitive occupies 1 by...
Read more