Hey.

Question on sql . I don’t understand the structure I’m dealing with. If you take PHP or JavaScript , they have a "bunch of names" and a "bunch of values." These heaps can be both local (the code inside the function) and global (global code). A bunch of names in PHP really looks like a heap and looks like (all user variables and functions are in the root, and there are server arrays in the root), the heap of names in JavaScript has a tree structure (a BOM tree with DOM trees and CSSOM ). I have a script program and a "bunch of names" associated with it, from which the interpreter ( PHP or JavaScript ) takes the names of variables and functions (in the interpreter, the interpreter names the variables and functions before the script runs into the context execution object of the code). What is the "bunch of names" for sql ? Is it possible to declare user functions in sql? Do cycles?

  • Custom functions in sql - this is the sql procedure - Alexander
  • This is called namespace. As for PHP, you are wrong. Functions and classes have their own scope. Global space is like a zero level, and additional levels can be entered using the namespace. - artoodetoo
  • 2
    If you learn SQL, then until the moment when you are fluent in queries (with large nesting of subqueries, etc.), forget about functions and loops. SQL does not apply to imperative programming languages. He does not have the notion of "current instruction", therefore there is no need for any cycles that would allow this "instruction" to be repeatedly executed. After you understand the queries well, then you can start exploring various extensions (which are no longer sql) of specific DBMS and which already have instructions and cycles and functions. Cycles, etc. really need less than 1% of tasks - Mike

1 answer 1

SQL is a query language. Those. the heap of names as such (tin term) is not here, but there is a scheme. The schema is stored in the database itself, in service tables.

Different database vendors have "add-ons" over standard SQL, such as TSQL in MSSQL, PL / SQL in Oracle, etc., which allow writing stored procedures and, to some extent, operating with pseudo-variables in the queries themselves. It is important to understand here that all these "variables" exist only within a single SQL transaction, inside the database engine.

Perhaps you will be more aware of this analogy: You have a POST request in which there is a certain set of transmitted data key-value and a certain amount of service information. This request itself does not know anything about what is inside it and how it should be processed - it simply serves as a wrapper for communicating the client-server. Here with SQL - the same story.

As for the namespace. In the context of the base under this term can be considered 2 different entities:

1) Names of tables, columns, built-in procedures, indexes, keys, etc. This is what is called the "database schema". Information on them is stored in the service areas (tables) of the database. Each database vendor has its own. As a rule, this information can be retrieved by simple SQL queries to these tables.

2) Variables used in stored procedures and queries (in dialects, where possible). These variables exist only within the framework of a transaction and their scope is always local. Those. variables used in the stored procedure are available only within this procedure. The "pseudo-variables" used in the query are visible only at the level of this query and are not visible, for example, within the procedure called by this query (if, of course, they were not passed to them by parameter by value ).

Once again I want to note that the second type - the variables in the stored and queries - this is all the "extension" of the SQL syntax, strongly dependent on the database used. They are not part of the SQL specification.

  • under a bunch of names, I mean the namespace - Dimon
  • @Dimon The namespace term is not applicable. In the query, only those fields of the tables specified in the query are visible. Although, on the other hand, there is some space, for example, in the correlated subquery, “names” are available from both the subquery tables and the external query. And in most DBMS this rule is valid only 1 level deep. Those. we are no longer available the names of the outermost level from a subquery that is in another subquery. - Mike
  • @Dimon updated the answer - rjhdby