User-defined functions. SQL Stored Procedures: Creation and Use

1) List of standard functions

Function	Return result
Bit_Length()	Number of bits in
Cast(As)	, converted to the specified
Char_Length()	Character length
Convert(using)	, converted in accordance with the specified
Current_Date	The current date
Current_Time()	Current time from the specified
Current_TimeStamp()	Current date and time from the specified
Extract(from)	Specified (Day, Hour, etc.) from the date
Lower()	, converted to lowercase
Octet_Length()	Number of bytes in
Position(in)	Position from which enters
Substring(from for)	The part starting at the position and having the specified
Trim(Leading\|Trailing\|Both from)	, from which the leading ones have been removed \| end \| on both sides
Upper()	, converted to uppercase
User	Defines the user ID

2) Overview of MS SQL Server features
Thus, SQL Server provides many functions, divided into the following groups:
→ String;
→ Mathematical;
→ Transformations;
→ To work with data of Text and Image types;
→ For working with dates;
→ System;
→ Niladic (null-place - without parameters).
As well as a number of other functions.

3) Oracle Features Overview
→ To work with errors;
→ Numerical;
→ String;
→ Transformations;
→ Broadcasts, for working with dates;
→ Various purposes.

Declaring a Stored Function

CREATE FUNCTION ([ [()], …]) RETURNS [()] [[NOT] DETERMINISTIC] [CONTAINS SQL|READS SQL DATA|MODIFIES SQL DATA]
BEGIN

RETURN
END

Keywords
. DETERMINISTIC shows whether or not a function returns the same values given the same input values. For example, the CURRENT_TIME function is NOT DETERMINISTIC.
. CONTAINS SQL indicates that the function does not contain SQL statements that read and modify data. This value is set by default.
. READS SQL DATA indicates that the function contains SELECT or FETCH statements.
. MODIFIES SQL DATA indicates that the function contains INSERT, UPDATE, or DELETE statements.

Restrictions on non-deterministic functions

In Oracle:
. Nondeterministic functions cannot be used when checking constraints in a CHECK statement. Additionally, restrictions cannot include calls to user-defined functions.
. Non-deterministic functions cannot be used in function-based indexes.

In SQL Server, a user-defined function is considered deterministic if:
. The function is schema-bound, i.e. The function is created using the SCHEMABINDING option, which means that objects referenced by the function cannot be modified or deleted.
. Every function (whether built-in or user-defined) called from the body of that function is deterministic.
. The body of the function contains no references to database objects (for example, tables, views and other functions) that were outside the scope.
. The function does not call extended stored procedures (which can change the state of the database).

Removing and changing stored functions

To remove a function, use the operator:

To change a function, use the operator:

ALTER FUNCTION ([[{IN|OUT|INOUT}] [()],…])
BEGIN

RETURN
END

Stored functions in SQL Server

CREATE FUNCTION ([@ [AS] [()] [=] [READONLY],…])
RETURNS [()]
[AS]
BEGIN

RETURN
END

Example:

USE B1;
GO
CREATE FUNCTION Quarter(@Dat DateTime) RETURNS int
BEGIN
DECLARE @ISQuarter int;
IF ((Month(@Dat)>=1) And (Month(@Dat)=4) And (Month(@Dat)=8) And (Month(@Dat)=10) And (Month(@Dat)

Requirements for called functions

For a PL/SQL programmer-defined function to be called from SQL commands, it must meet the following requirements:

All function parameters must have the use mode IN . IN OUT and OUT modes in functions embedded in SQL code are not allowed.
The data types of function parameters and the return type must be recognized by the Oracle server. PL/SQL complements the core Oracle types that are not yet supported by the database. These types include BOOLEAN, BINARY_INTEGER, associative arrays, PL/SQL records, and programmer-defined subtypes.
The function must be stored in the database. A client-side defined function cannot be called in SQL commands because SQL will not be able to resolve the function reference.

By default, user-defined functions called in SQL operate on data of a single row rather than a column (like the aggregate functions SUM, MIN, and AVG). To create aggregate functions that are called in SQL, you must use the ODCIAggregate interface, which is part of the Oracle Extensibility Framework. For detailed information on this topic, please refer to the Oracle documentation.

Limitations on user-defined functions called in SQL

To protect against side effects and unpredictable behavior, Oracle does not allow stored procedures to do the following:

Stored functions cannot modify database tables and cannot issue DDL (CREATE TABLE, DROP INDEX, etc.), INSERT, DELETE, MERGE, or UPDATE commands. These restrictions are relaxed if the function is defined as an autonomous transaction. In this case, any changes it makes are carried out independently of the external transaction in which the request is executed.
Stored functions that are called remotely or in parallel cannot read or change the values of package variables. Oracle Server does not support side effects that extend beyond the user's session.
A stored function can change the values of package variables only if it is called in a select list or in a VALUES or SET clause. If a stored function is called in a WHERE or GROUP BY clause, it cannot change the values of package variables.
Before Oracle8, user-defined functions could not call the RAISE_APPLICATION_ERROR procedure.
A stored function cannot call another module (stored procedure or function) that does not meet these requirements.
A stored function cannot access a view that violates any of the preceding rules. A view is a stored SELECT command in which stored functions can be called.
Before Oracle11g, only positional notation could be used to pass parameters to functions. Starting with Oracle11g, passing parameters by name and mixed notation is allowed.

Read Consistency and Custom Functions

The read consistency model in the Oracle database is simple and straightforward: after execution, a query “sees” the data in the state in which it existed (was committed to the database) at the time the query was started, taking into account the results of changes made by the DML commands of the current transaction. So, if my query runs at 9:00 am and continues to run for an hour, even if another user makes changes to the data during that time, they will not be reflected in my query.

But unless you take special precautions with user-defined functions in your queries, you may find that your query violates (at least on the surface) the Oracle database read consistency model. To understand this aspect, consider the following function and the query that calls it:

FUNCTION total_sales (id_in IN account.account_id%TYPE) RETURN NUMBER IS CURSOR tot_cur IS SELECT SUM (sales) total FROM orders WHERE account_id = id_in AND TO_CHAR (ordered_on, "YYYY") = TO_CHAR (SYSDATE, "YYYY");

tot_rec tot_cur%ROWTYPE; BEGIN OPEN tot_cur;

FETCH tot_cur INTO tot_rec;

CLOSE tot_cur;

RETURN tot_rec.total; END; SELECT name, total_sales (account_id) FROM account WHERE status = "ACTIVE";

The account table has 5 million active rows, and the orders table has 20 million. I run the request at 10:00 and it takes about an hour to complete. At 10:45, someone with the necessary privileges comes in, deletes all rows from the orders table, and commits the transaction. According to the rules of Oracle's read consistency model, the session in which the query is executed should not consider these rows to be deleted until the query completes. But the next time the total_sales function is called from the query, it will not find any rows and will return NULL - and this will continue until the query completes.

When running queries from functions called in SQL code, you need to be careful about reading consistency. If these functions are called in long-running queries or transactions, you may want to run the following command to ensure read consistency between the current transaction's SQL commands: SET TRANSACTION READ ONLY

In this case, care must be taken to ensure that there is sufficient undo table space.

Defining PL/SQL Routines in SQL Commands (12.1 and higher)

This feature allows you to “extend” SQL with application-specific functionality and reuse algorithms (instead of copying them). The disadvantages of running user-defined functions in SQL include the need to switch context between the SQL and P L/SQL execution engines. Beginning with Oracle Database 12c, you can define PL/SQL functions and procedures in the WITH clause of a subquery, which you can then use like any built-in or user-defined function. This feature allows you to consolidate a function and a query into one command:

WITH FUNCTION betwnstr (string_in IN VARCHAR2, start_in IN PLS_INTEGER, end_in IN PLS_INTEGER) RETURN VARCHAR2 IS BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));

END; SELECT betwnstr (last_name, 3, 5) FROM employees

The main advantage of this solution is increased performance, since with this definition of functions, the cost of context switching from the SQL engine to the PL/SQL engine is significantly reduced. On the other hand, it comes at the cost of being able to reuse logic in other parts of the application.

However, there are other reasons for defining functions in the WITH clause. In SQL, you can call a batch function, but you cannot reference a constant declared in a batch (unless the SQL command is executed inside a PL/SQL block), as shown in the following example:

SQL> CREATE OR REPLACE PACKAGE pkg 2 IS 3 year_number CONSTANT INTEGER:= 2013;

4 END;

5/Package created. SQL> SELECT pkg.year_number FROM employees 2 WHERE employee_id = 138 3 / SELECT pkg.year_number FROM employees * ERROR at line 1: ORA-06553: PLS-221: "YEAR_NUMBER" is not a procedure or is undefined

WITH FUNCTION year_number RETURN INTEGER IS BEGIN RETURN pkg.year_number;

END; SELECT year_number FROM employees WHERE employee_id = 138

PL/SQL functions defined in SQL are also useful when working with contained read-only databases. Although you can't create PL/SQL "helper" functions in such databases, you can define them directly in queries.

WITH FUNCTION has been an extremely useful enhancement to the SQL language. However, every time you plan to use it, one question worth asking yourself is: “Will this functionality be needed in multiple places in the application?”

If the answer is yes, you need to decide whether the performance gains from using WITH FUNCTION offset the potential losses from copying and pasting this logic across multiple SQL commands.

Please note that in version 12.1 it is not possible to execute a static select command with a with function clause in PL/SQL blocks. Of course, this looks very strange, and I'm sure that this feature will appear in 12.2, but for now, if you try to run the following code, you will get an error:

SQL> BEGIN 2 WITH FUNCTION full_name (fname_in IN VARCHAR2, lname_in IN VARCHAR2) 3 RETURN VARCHAR2 4 IS 5 BEGIN 6 RETURN fname_in || " " || lname_in;

7 END;

8 9 SELECT LENGTH (full_name (first_name, last_name)) 10 INTO c 11 FROM employees;

Many people believe that they are similar to various procedures (respectively, except MS SQL). Perhaps this is true. They have similar parameters and can produce similar values. Moreover, in some cases they touch. For example, they are combined with DDL and DML databases, as well as user functions (codenamed UDF).

In reality, SQL stored procedures have a wide range of advantages that set them apart from similar processes. Security, programming flexibility, productivity - all this attracts more and more users working with databases. The peak popularity of procedures occurred in 2005-2010, when a program from Microsoft called “SQL Server Management Studio” was released. With its help, working with databases has become much easier, more practical and convenient. From year to year, this one gained popularity among programmers. Today it is an absolutely familiar program, which for users who “communicate” with databases is on a par with Excel.

When a procedure is called, it is instantly processed by the server itself without unnecessary processes or user intervention. After this, you can carry out any deletion, execution, or modification. The DDL operator is responsible for all this, who alone performs the most complex actions to process objects. Moreover, all this happens very quickly, and the server is not actually loaded. This speed and performance allows you to very quickly transfer large amounts of information from the user to the server and vice versa.

To implement this technology for working with information, there are several programming languages. These include, for example, PL/SQL from Oracle, PSQL in the InterBase and Firebird systems, as well as the classic Microsoft Transact-SQL. All of them are designed for creating and executing stored procedures, which allows large database processors to use their own algorithms. This is also necessary so that those who manage such information can protect all objects from unauthorized access by third parties and, accordingly, the creation, modification or deletion of certain data.

Productivity

These database objects can be programmed in a variety of ways. This allows users to choose the type of method used that is most suitable, saving effort and time. In addition, the procedure is processed itself, which avoids the huge time spent on communication between the server and the user. Also, the module can be reprogrammed and changed in the desired direction at absolutely any time. It is especially worth noting the speed with which the SQL stored procedure is launched: this process occurs faster than others similar to it, which makes it convenient and universal.

Safety

This type of information processing differs from similar processes in that it guarantees increased security. This is ensured by the fact that access to procedures by other users can be completely excluded. This will allow the administrator to carry out operations with them independently, without fear of interception of information or unauthorized access to the database.

Data transfer

The relationship between the SQL stored procedure and the client application is the use of parameters and return values. The latter does not have to pass the data to the stored procedure, but this information (mainly at the user's request) is processed for SQL. After the stored procedure has completed its work, it sends data packets back (but again optionally) to the application that called it, using various methods that can be used to either call the SQL stored procedure or return, for example:

Transferring data using an Output type parameter;

Passing data using the return operator;

Passing data using the select operator.

Now let’s figure out what this process looks like from the inside.

1. Create an EXEC stored procedure in SQL

You can create a procedure in MS SQL (Managment Studio). After the procedure is created, it will be listed in the programmable node of the database, in which the creation procedure is executed by the operator. To execute, SQL stored procedures use an EXEC process that contains the name of the object itself.

When you create a procedure, its name appears first, followed by one or more parameters assigned to it. Parameters may be optional. After the parameter(s), that is, the body of the procedure, have been written, some necessary operations need to be performed.

The point is that a body can have local variables located in it, and these variables are also local in relation to procedures. In other words, they can only be viewed within the body of a Microsoft SQL Server procedure. Stored procedures in this case are considered local.

So, to create a procedure, we need the name of the procedure and at least one parameter as the body of the procedure. Note that a great option in this case is to create and execute a procedure with the schema name in the classifier.

The body of the procedure can be of any kind, such as creating a table, inserting one or more rows of a table, establishing the type and nature of the database, and so on. However, the procedure body restricts certain operations from being performed within it. Some of the important restrictions are listed below:

The body should not create any other stored procedure;

The body should not create a false impression of the object;

The body should not create any triggers.

2. Setting a variable in the body of the procedure

You can make variables local to the body of the procedure, and then they will reside exclusively within the body of the procedure. It is good practice to create variables at the beginning of the stored procedure body. But you can also set variables anywhere in the body of a given object.

Sometimes you may notice that several variables are set on one line, and each variable parameter is separated by a comma. Also note that the variable is prefixed with @. In the body of the procedure you can set the variable to wherever you want. For example, the @NAME1 variable may be declared near the end of the procedure body. To assign a value to a declared variable, a set of personal data is used. Unlike the situation where more than one variable is declared on the same line, in this situation only one set of personal data is used.

Users often ask the question: “How to assign multiple values in one statement in the body of a procedure?” Well. It's an interesting question, but it's much easier to do than you think. Answer: Using pairs such as "Select Var = value". You can use these pairs by separating them with a comma.

A variety of examples show people creating a simple stored procedure and executing it. However, a procedure can accept parameters such that the process calling it will have values close to it (but not always). If they coincide, then corresponding processes begin inside the body. For example, if you create a procedure that will accept a city and region from the caller and return data about how many authors belong to the corresponding city and region. The procedure will query the database's author tables, such as Pubs, to perform this author count. To get these databases, for example, Google downloads the SQL script from the SQL2005 page.

In the previous example, the procedure takes two parameters, which in English will be conventionally called @State and @City. The data type matches the type defined in the application. The body of the procedure has internal variables @TotalAuthors, and this variable is used to display the number of authors. Next comes the query selection section, which calculates everything. Finally, the calculated value is printed in the output window using the print statement.

How to execute a stored procedure in SQL

There are two ways to perform the procedure. The first way shows, by passing parameters, how a comma-separated list is executed after the procedure name. Let's say we have two values (as in the previous example). These values are collected using the @State and @City procedure parameter variables. In this method of passing parameters, order is important. This method is called ordinal argument passing. In the second method, the parameters are already directly assigned, and in this case the order is not important. This second method is known as passing named arguments.

The procedure may deviate slightly from the typical one. Everything is the same as in the previous example, but only here the parameters are shifted. That is, the @City parameter is stored first, and @State is stored next to the default value. The default parameter is usually highlighted separately. SQL stored procedures are passed as just parameters. In this case, provided, the parameter "UT" replaces the default value "CA". In the second execution, only one argument value is passed for the @City parameter, and the @State parameter takes on the default value of "CA". Experienced programmers advise that all variables should be located towards the end of the parameter list by default. Otherwise execution is not possible, and then you have to work with passing named arguments, which is longer and more complex.

4. SQL Server Stored Procedures: Return Methods

There are three important ways to send data in a called stored procedure. They are listed below:

Return the value of a stored procedure;

Stored procedure parameter output;

Selecting one of the stored procedures.

4.1 Returning values from SQL stored procedures

In this technique, a procedure assigns a value to a local variable and returns it. A procedure can also directly return a constant value. In the following example, we created a procedure that returns the total number of authors. If you compare this procedure with the previous ones, you can see that the print value is reversed.

Now let's see how to execute a procedure and print its return value. Executing the procedure requires setting a variable and printing, which is carried out after this entire process. Note that instead of a print statement, you can use a Select statement, such as Select @RetValue as well as OutputValue.

4.2 SQL Stored Procedure Parameter Output

The response value can be used to return a single variable, which is what we saw in the previous example. Using the Output parameter allows a procedure to send one or more variable values to the caller. The output parameter is designated precisely by this keyword “Output” when creating a procedure. If a parameter is given as an output parameter, then the procedure object must assign a value to it. SQL stored procedures, examples of which can be seen below, in this case return with summary information.

In our example there will be two output names: @TotalAuthors and @TotalNoContract. They are indicated in the list of parameters. These variables assign values within the body of the procedure. When we use output parameters, the caller can see the value set inside the body of the procedure.

Also, in the previous scenario, two variables are declared to see the values that the MS SQL Server stored procedures set in the output parameter. Then the procedure is performed by supplying the normal value of the “CA” parameter. The following parameters are output parameters and hence the declared variables are passed in the specified order. Note that when passing variables, the output keyword is also set here. After the procedure is completed successfully, the values returned by the output parameters are displayed in the message box.

4.3 Selecting one of the SQL stored procedures

This technique is used to return a set of values as a data table (RecordSet) to the calling stored procedure. In this example, the SQL stored procedure with @AuthID parameters queries the Authors table by filtering the records returned using that @AuthId parameter. The Select statement decides what should be returned to the caller of the stored procedure. When the stored procedure is executed, the AuthId is passed back. This procedure here always returns only one record or none at all. But a stored procedure does not have any restrictions on returning more than one record. It is not uncommon to see examples where data is returned using selected parameters involving calculated variables by providing multiple totals.

Finally

A stored procedure is a fairly serious program module that returns or passes, and also sets the necessary variables thanks to the client application. Because the stored procedure runs itself on the server, huge amounts of data exchange between the server and the client application (for some calculations) can be avoided. This allows you to reduce the load on SQL servers, which, of course, benefits their owners. One of the subtypes is T SQL stored procedures, but their study is necessary for those who create impressive databases. There are also a large, even huge number of nuances that can be useful when studying stored procedures, but this is needed more for those who plan to get involved in programming, including professionally.

Stored procedure is a special type of Transact-SQL statement package created using the SQL language and procedural extensions. The main difference between a package and a stored procedure is that the latter is stored as a database object. In other words, stored procedures are stored on the server side to improve performance and consistency of repeatable tasks.

The Database Engine supports stored procedures and system procedures. Stored procedures are created in the same way as all other database objects, i.e. using DDL language. System procedures are provided by the Database Engine and can be used to access and modify information in the system catalog.

When you create a stored procedure, you can define an optional list of parameters. This way, the procedure will accept the appropriate arguments each time it is called. Stored procedures can return a value containing user-defined information or, in the event of an error, an appropriate error message.

The stored procedure is precompiled before it is stored as an object in the database. The precompiled form of the procedure is stored in the database and used each time it is called. This property of stored procedures provides the important benefit of eliminating (in almost all cases) repeated procedure compilations and achieving corresponding performance improvements. This property of stored procedures also has a positive effect on the amount of data exchanged between the database system and applications. In particular, calling a stored procedure that is several thousand bytes in size may require less than 50 bytes. When multiple users perform repetitive tasks using stored procedures, the cumulative effect of these savings can be quite significant.

Stored procedures can also be used for the following purposes:

to create a log of actions with database tables.

Using stored procedures provides a level of security control that goes well beyond the security provided by using GRANT and REVOKE statements, which grant different access privileges to users. This is possible because the authorization to execute a stored procedure is independent of the authorization to modify the objects contained in the stored procedure, as described in the next section.

Stored procedures that create logs of table write and/or read operations provide an additional option for database security. Using such procedures, the database administrator can monitor modifications made to the database by users or application programs.

Creating and Executing Stored Procedures

Stored procedures are created using a statement CREATE PROCEDURE, which has the following syntax:

CREATE PROC proc_name [((@param1) type1 [ VARYING] [= default1] )] (, ...) AS batch | EXTERNAL NAME method_name Syntax conventions

The schema_name parameter specifies the name of the schema that is assigned by the owner of the created stored procedure. The proc_name parameter specifies the name of the stored procedure. The @param1 parameter is a procedure parameter (formal argument) whose data type is determined by the type1 parameter. Procedure parameters are local within the procedure, just as local variables are local within the package. Procedure parameters are values that are passed by the caller to the procedure for use in it. The default1 parameter specifies the default value for the corresponding procedure parameter. (The default value can also be NULL.)

OUTPUT option indicates that a procedure parameter is a return parameter and can be used to return a value from a stored procedure to the calling procedure or system.

As mentioned earlier, the precompiled form of a procedure is stored in the database and used every time it is called. If for some reason the stored procedure needs to be compiled each time it is called, when declaring the procedure, use WITH RECOMPILE option. Using the WITH RECOMPILE option negates one of the most important benefits of stored procedures: the performance improvement due to a single compilation. Therefore, the WITH RECOMPILE option should only be used when the database objects used by the stored procedure are frequently modified.

EXECUTE AS clause defines the security context in which the stored procedure should execute after it is called. By setting this context, the Database Engine can control the selection of user accounts to verify access permissions to the objects referenced by the stored procedure.

By default, only members of the sysadmin fixed server role and the db_owner or db_ddladmin fixed database roles can use the CREATE PROCEDURE statement. But members of these roles can assign this right to other users using the statement GRANT CREATE PROCEDURE.

The example below shows how to create a simple stored procedure to work with the Project table:

USE SampleDb; GO CREATE PROCEDURE IncreaseBudget (@percent INT=5) AS UPDATE Project SET Budget = Budget + Budget * @percent/100;

As mentioned earlier, to separate two packets, use GO instructions. The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in the same batch. The IncreaseBudget stored procedure increases budgets for all projects by a certain percentage, determined by the @percent parameter. The procedure also defines a default percentage value (5) that is used if this argument is not present when the procedure runs.

Stored procedures can access tables that do not exist. This property allows you to debug procedure code without first creating the appropriate tables or even connecting to the destination server.

Unlike primary stored procedures, which are always stored in the current database, it is possible to create temporary stored procedures that are always stored in the temporary system database tempdb. One reason to create temporary stored procedures may be to avoid repeatedly executing a specific group of statements when connecting to a database. You can create local or global temporary procedures. To do this, the name of the local procedure is specified with a single # character (#proc_name), and the name of the global procedure is specified with a double character (##proc_name).

A local temporary stored procedure can only be executed by the user who created it, and only while connected to the database in which it was created. A global temporary procedure can be executed by all users, but only until the last connection on which it is executed (usually the connection of the procedure's creator) terminates.

The life cycle of a stored procedure consists of two stages: its creation and its execution. Each procedure is created once and executed many times. The stored procedure is executed using EXECUTE statements a user who is the owner of a procedure or has EXECUTE privilege to access that procedure. The EXECUTE statement has the following syntax:

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] value | [@parameter1=] @variable ] | DEFAULT).. Syntax conventions

With the exception of the return_status parameter, all parameters of the EXECUTE statement have the same logical meaning as the same parameters of the CREATE PROCEDURE statement. The return_status parameter specifies an integer variable that stores the return status of the procedure. A value can be assigned to a parameter using either a constant (value) or a local variable (@variable). The order of the values of named parameters is not important, but the values of unnamed parameters must be provided in the order in which they are defined in the CREATE PROCEDURE statement.

DEFAULT clause provides the default value for a procedure parameter that was specified in the procedure definition. When a procedure expects a value for a parameter for which no default value has been defined and the parameter is missing or the DEFAULT keyword is specified, an error occurs.

When the EXECUTE statement is the first statement of a batch, the EXECUTE keyword can be omitted. However, it is safer to include this word in every packet. The use of the EXECUTE statement is shown in the example below:

USE SampleDb; EXECUTE IncreaseBudget 10;

The EXECUTE statement in this example executes the IncreaseBudget stored procedure, which increases the budget of all projects by 10%.

The example below shows how to create a stored procedure to process data in the Employee and Works_on tables:

The ModifyEmpId example procedure illustrates the use of stored procedures as part of the process of maintaining referential integrity (in this case between the Employee and Works_on tables). A similar stored procedure can be used inside a trigger definition, which actually provides referential integrity.

The following example shows the use of an OUTPUT clause in a stored procedure:

This stored procedure can be executed using the following instructions:

DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT; PRINT N"Deleted employees: " + convert(nvarchar(30), @quantityDeleteEmployee);

This procedure counts the number of projects that the employee with personnel number @empId is working on and assigns the resulting value to the ©counter parameter. After all rows for a given personnel number are deleted from the Employee and Works_on tables, the calculated value is assigned to the @quantityDeleteEmployee variable.

The parameter value is returned to the calling procedure only if the OUTPUT option is specified. In the example above, the DeleteEmployee procedure passes the @counter parameter to the calling procedure, hence the stored procedure returns a value to the system. Therefore, the @counter parameter must be specified both in the OUTPUT option when declaring a procedure and in the EXECUTE statement when calling it.

WITH RESULTS SETS clause of EXECUTE statement

In SQL Server 2012, for the EXECUTE statement, you enter WITH RESULTS SETS clause, through which, when certain conditions are met, you can change the form of the result set of a stored procedure.

The following two examples will help explain this sentence. The first example is an introductory example that shows what the result might look like when the WITH RESULTS SETS clause is omitted:

The EmployeesInDept procedure is a simple procedure that displays the personnel numbers and last names of all employees working in a specific department. The department number is a procedure parameter and must be specified when calling it. Executing this procedure produces a table with two columns whose headings match the names of the corresponding columns in the database table, i.e. Id and LastName. To change the headers of result columns (as well as their data type), SQL Server 2012 uses the new WITH RESULTS SETS clause. The application of this sentence is shown in the example below:

USE SampleDb; EXEC EmployeesInDept "d1" WITH RESULT SETS (( INT NOT NULL, [LastName] CHAR(20) NOT NULL));

The result of executing a stored procedure called in this way will be as follows:

As you can see, running a stored procedure using the WITH RESULT SETS clause in the EXECUTE statement allows you to change the names and data types of the columns in the result set produced by the procedure. Thus, this new functionality provides greater flexibility in executing stored procedures and placing their results in a new table.

Changing the Structure of Stored Procedures

The Database Engine also supports the instruction ALTER PROCEDURE to modify the structure of stored procedures. The ALTER PROCEDURE statement is typically used to change Transact-SQL statements within a procedure. All parameters of the ALTER PROCEDURE statement have the same meaning as the same parameters of the CREATE PROCEDURE statement. The main purpose of using this statement is to avoid overriding existing stored procedure rights.

The Database Engine supports CURSOR data type. This data type is used to declare cursors in stored procedures. Cursor is a programming construct used to store the results of a query (usually a set of rows) and allow users to display that result row by row.

To delete one or a group of stored procedures, use DROP PROCEDURE instruction. Only the owner or members of the db_owner and sysadmin fixed roles can delete a stored procedure.

Stored procedures and the common language runtime

SQL Server supports the Common Language Runtime (CLR), which allows you to develop various database objects (stored procedures, user-defined functions, triggers, user-defined aggregations, and custom data types) using C# and Visual Basic. The CLR also allows you to execute these objects using the common runtime system.

The common language runtime is enabled and disabled using the option clr_enabled system procedure sp_configure, which is launched for execution by instruction RECONFIGURE. The following example shows how you can use the sp_configure system procedure to enable the CLR:

USE SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE

To create, compile, and save a procedure using the CLR, you must complete the following sequence of steps in the order shown:

Create a stored procedure in C# or Visual Basic, and then compile it using the appropriate compiler.

Using instructions CREATE ASSEMBLY, create the corresponding executable file.

Execute the procedure using the EXECUTE statement.

The figure below shows a graphical diagram of the previously outlined steps. The following is a more detailed description of this process.

First, create the required program in a development environment such as Visual Studio. Compile the finished program into object code using a C# or Visual Basic compiler. This code is stored in a dynamic-link library (.dll) file, which serves as the source for the CREATE ASSEMBLY statement, which creates the intermediate executable code. Next, issue a CREATE PROCEDURE statement to save the executing code as a database object. Finally, run the procedure using the familiar EXECUTE statement.

The example below shows the source code for a stored procedure in C#:

Using System.Data.SqlClient; using Microsoft.SqlServer.Server; public partial class StoredProcedures ( public static int CountEmployees() ( int rows; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) as "Number of employees" " + "from Employee"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; ) )

This procedure implements a query to count the number of rows in the Employee table. Using directives at the beginning of a program specify the namespaces required to execute the program. Using these directives allows you to specify class names in source code without explicitly specifying the corresponding namespaces. Next, the StoredProcedures class is defined, for which it is applied SqlProcedure attribute, which informs the compiler that this class is a stored procedure. The CountEmployees() method is defined inside the class code. A connection to the database system is established through an instance of the class SqlConnection. To open a connection, the Open() method of this instance is used. A CreateCommand() method allows you to access an instance of a class SqlCommnd, to which the required SQL command is passed.

In the following code snippet:

Cmd.CommandText = "select count(*) as "Number of employees" " + "from Employee";

uses a SELECT statement to count the number of rows in the Employee table and display the result. The command text is specified by setting the CommandText property of the cmd variable to the instance returned by the CreateCommand() method. Next it is called ExecuteScalar() method SqlCommand instance. This method returns a scalar value that is converted to an integer data type and assigned to the rows variable.

You can now compile this code using Visual Studio. I added this class to a project called CLRStoredProcedures, so Visual Studio will compile an assembly of the same name with a *.dll extension. The example below shows the next step in creating a stored procedure: creating the executable code. Before you run the code in this example, you need to know the location of the compiled dll file (usually located in the Debug folder of the project).

USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" WITH PERMISSION_SET = SAFE

The CREATE ASSEMBLY statement takes managed code as input and creates a corresponding object on which you can create CLR stored procedures, user-defined functions, and triggers. This instruction has the following syntax:

CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM (dll_file) Syntax conventions

The assembly_name parameter specifies the name of the assembly. The optional AUTHORIZATION clause specifies the role name as the owner of this assembly. The FROM clause specifies the path where the assembly to load is located.

WITH PERMISSION_SET clause is a very important clause of the CREATE ASSEMBLY statement and must always be specified. It defines the set of permissions granted to the assembly code. The SAFE permission set is the most restrictive. Assembly code that has these rights cannot access external system resources such as files. The EXTERNAL_ACCESS rights set allows assembly code to access certain external system resources, while the UNSAFE rights set allows unrestricted access to resources both inside and outside the database system.

To save assembly code information, the user must be able to issue a CREATE ASSEMBLY statement. The owner of the assembly is the user (or role) executing the instruction. You can make another user the owner of the assembly by using the AUTHORIZATION clause of the CREATE SCHEMA statement.

The Database Engine also supports ALTER ASSEMBLY and DROP ASSEMBLY statements. ALTER ASSEMBLY statement used to update the assembly to the latest version. This instruction also adds or removes files associated with the corresponding assembly. DROP ASSEMBLY instruction Removes the specified assembly and all its associated files from the current database.

The example below shows how to create a stored procedure based on the managed code you implemented earlier:

USE SampleDb; GO CREATE PROCEDURE CountEmployees AS EXTERNAL NAME CLRStoredProcedures.StoredProcedures.CountEmployees

The CREATE PROCEDURE instruction in the example differs from the same instruction in the previous examples in that it contains EXTERNAL NAME parameter. This option specifies that the code is generated by the common language runtime. The name in this sentence consists of three parts:

assembly_name.class_name.method_name

assembly_name - indicates the name of the assembly;

class_name - indicates the name of the general class;

method_name - optional part, specifies the name of the method that is defined inside the class.

The execution of the CountEmployees procedure is shown in the example below:

USE SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Return 7

The PRINT statement returns the current number of rows in the Employee table.

CREATE FUNCTION - create a function

Syntax

Description

The CREATE FUNCTION command defines a new function. CREATE OR REPLACE FUNCTION creates a new function or replaces the definition of an existing one. To define a function, you must have USAGE rights for the appropriate language.

If a schema name is specified, the function is created in the specified schema, otherwise - in the current one. The name of the new function must be different from the names of existing functions with the same argument types in this schema. However, functions with arguments of different types can have the same name (this is called overload).

To replace the current definition of an existing function, use the CREATE OR REPLACE FUNCTION command. But note that it does not allow you to change the name or arguments of the function (if you try to do this, it will actually create a new, independent function). Additionally, CREATE OR REPLACE FUNCTION will not allow you to change the result type of an existing function. To do this, you will have to delete the function and create it again. (This means that if a function has output parameters (OUT), you can only change the OUT parameter types by deleting the function.)

When a CREATE OR REPLACE FUNCTION command replaces an existing function, the ownership and permissions of that function do not change. All other function properties receive values that are set explicitly or by default by the command. To replace a function, you must be its owner (or a member of the owning role).

If you delete and then recreate a function, the new function becomes a different entity from the old one; you will also need to remove existing rules, views, triggers, etc. that reference the old function. Therefore, to change the definition of a function while preserving the objects that reference it, you should use CREATE OR REPLACE FUNCTION . In addition, many additional properties of an existing function can be changed using ALTER FUNCTION.

The user who created it becomes the owner of the function.

To create a function, you must have USAGE rights on the types of its arguments and return type.

Options

Name

The name of the function to create (possibly supplemented with a schema). argument_mode

Argument mode: IN (input), OUT (output), INOUT (input and output) or VARIADIC (variable). The default is IN . A single VARIADIC argument can only be followed by OUT arguments. Additionally, the OUT and INOUT arguments cannot be used with the RETURNS TABLE clause. argument_name

Argument name. Some languages (including SQL and PL/pgSQL) allow this name to be used in the body of a function. For other languages, this name simply serves as an additional description for the function itself; however, you can specify argument names when calling a function to improve readability (see Section 4.3). The name of the output argument matters anyway because it specifies the name of the column in the result type. (If you omit the name of the output argument, the system will choose a default name for it.) argument_type

The data type of the function argument (possibly extended with a schema), if there are any arguments. The argument type can be base, compound, or domain, or it can be a table column reference.

Depending on the implementation language, it may also be possible to specify "pseudotypes" eg cstring. Pseudotypes indicate that the actual type of the argument is either not fully defined or exists outside the set of normal SQL types.

A reference to a column type is written as table_name.column_name%TYPE. Sometimes this specification is useful because it allows you to create a function that is independent of changes to the table definition. default_expression

The expression used to calculate the default value if the parameter is not specified explicitly. The result of the expression must be reduced to the type of the corresponding parameter. Only input parameters (including INOUT) can have default values. All input parameters following a parameter with a defined default value must also have a default value defined. result_type

The return data type (possibly extended with a schema). This can be a base, compound, or domain type, or a reference to a table column type. Depending on the implementation language, this may also allow " pseudotypes », for example cstring. If the function should not return a value, the result type is void .

If there are OUT or INOUT parameters, the RETURNS clause can be omitted. If present, it must be consistent with the type of the result inferred from the output parameters: the return type is RECORD if there are multiple output parameters, or the type of a single output parameter.

Specifying SETOF indicates that the function returns a set rather than a single element.

The name of the output column in the RETURNS TABLE entry. This is essentially another way to declare a named output parameter (OUT), but RETURNS TABLE also implies RETURNS SETOF. column_type

The data type of the output column in the RETURNS TABLE record. language_name

The name of the language in which the function is implemented. This can be sql , c , internal , or the name of a user-defined procedural language such as plpgsql . The style of writing this name in apostrophes is considered obsolete and requires an exact match of case. TRANSFORM ( FOR TYPE type_name } [, ... ] }

Sets a list of transformations that should be applied when calling a function. Transformations perform conversions between SQL types and language-specific data types; see CREATE TRANSFORM. Built-in type conversions are usually hard-coded in procedural language implementations, so they don't need to be specified here. If a procedural language implementation cannot handle a type and there is no conversion available for it, a default type conversion will be performed, but this is implementation dependent.

WINDOW The WINDOW indication shows that it is not a simple one that is being created, but window function
. Currently, this only makes sense for functions written in C. The WINDOW attribute cannot be changed by subsequently modifying the function definition.
IMMUTABLE

STABLE

The IMMUTABLE (constant) characteristic shows that the function cannot modify the database and always returns the same result for certain argument values; that is, it does not access the database or use information not explicitly passed to it in the argument list. If a function has this characteristic, any call to it with constant arguments can be immediately replaced by the value of the function.

The STABLE characteristic indicates that the function cannot modify the database and within a single table scan it always returns the same result for certain argument values, but this result may differ in different SQL statements. This is an appropriate choice for functions whose results depend on the contents of the database and configurable settings (such as the current time zone). (But this option is not suitable for AFTER triggers that want to read rows modified by the current command.) Also note that the current_timestamp family of functions are also considered stable because their results do not change within a transaction.

The VOLATILE (volatile) characteristic indicates that the result of a function can change even within a single table scan, so its calls cannot be optimized. Relatively few database functions are mutable in this sense, for example: random(), currval() and timeofday(). But note that any function with side effects must be classified as volatile, even if its result is quite predictable, so that its calls are not optimized; an example of such a function: setval() .

For additional details, see Section 35.6.

The LEAKPROOF (leak-proof) characteristic indicates that the function has no side effects. It does not reveal information about its arguments other than by returning the result. For example, a function that produces an error message with some but not all argument values, or that outputs argument values in an error message, is not airtight. This affects how the system executes queries against views created with a security barrier (specifying security_barrier) or against tables with row security enabled. To prevent uncontrolled data leakage, the system will check conditions from security policies and view definitions with security barriers before any conditions that the user specifies in the request itself that involve leaky functions. Functions and operators marked as sealed are considered trusted and can be executed before conditions from security policies and views with security barriers. However, functions that take no arguments or are not passed any arguments from a security barrier view or table do not need to be marked as sealed in order for them to execute before security-related conditions. See CREATE VIEW and Section 38.5. This property can only be set by the superuser.
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT

STRICT

CALLED ON NULL INPUT (default) indicates that the function will be called as usual if there are NULL values among its arguments. In this case, it is the responsibility of the function developer to check for NULL values and handle them accordingly. Specifying RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns NULL if it receives NULL in one of its arguments. Such a function will not be called with NULL arguments, but will instead automatically assume a NULL result.
[[

EXTERNAL ] SECURITY INVOKER

EXTERNAL ] SECURITY DEFINER

The PARALLEL UNSAFE statement means that the function cannot be executed in parallel and the presence of the function in the SQL statement causes the sequential execution plan to be selected. This is the default feature feature. PARALLEL RESTRICTED means that the function can be executed in parallel, but only in the leading process of the group. PARALLEL SAFE indicates that the function is safe to run in parallel without restrictions.

Functions should be marked as unsafe for concurrency if they modify database state, make changes to transactions such as using subtransactions, access sequences, or attempt to store parameters (such as using setval). Restricted parallel should mark functions that access temporary tables, client connection state, cursors, prepared statements, or miscellaneous server process state that the system cannot synchronize in parallel (for example, setseed can only be executed by the group's leading process because changes made by another process are not transmitted to the master). In general, if a function is marked as safe when it is restricted or unsafe, or if it is marked as limited safe when it is not safe, it may produce errors or incorrect results when you try to call it in a parallel query. C functions, when incorrectly marked, can theoretically exhibit completely undefined behavior, since the system has no way of protecting against arbitrary C code, but more often than not they will all behave no worse than any other function. When in doubt, a function should be marked as unsafe (UNSAFE), which is the default. execution_cost

A positive number specifying the approximate cost of executing the function, in cpu_operator_cost units. If the function returns a set, this number specifies the cost for one row. If the cost is not specified, it is assumed to be 1 unit for C and internal functions and 100 units for functions in all other languages. For large values, the scheduler will try not to call this function more often than necessary. rows_in_result

A positive number that specifies the approximate number of rows that the scheduler will expect as output from this function. This indication is only valid if the function is declared to return a set. The expected default value is 1000 rows. config_parameter
meaning

The SET clause specifies that when a function is called, the specified configuration parameter must take the specified value and then restore its previous value when the function completes. The SET FROM CURRENT clause stores as the value that will be applied upon function entry the value in effect at the time CREATE FUNCTION is executed.

If SET is added to a function definition, then the effect of the SET LOCAL command executed inside the function for the same parameter is limited to the body of the function: the previous value of the parameter will also be restored when the function exits. However, a regular SET command (without LOCAL) overrides the SET clause, just like the previous SET LOCAL command: the effect of such a command will continue after the function exits, unless the transaction is rolled back.

For details on parameter names and values, see SET and Chapter 18. definition

A string constant defining the implementation of the function; its meaning depends on the language. This could be the name of an internal function, the path to an object file, an SQL command, or code for a function in a procedural language.

It is often useful to enclose the definition of a function in dollars (see Subsection 4.1.2.4) rather than in the traditional apostrophes. If you don't use dollars, all apostrophes and backslashes in the function definition will have to be escaped by duplicating them. object_file, object_symbol

This form of the AS clause is used for dynamically loaded C functions when the function name in the C code is not the same as the function name in SQL. Line object_file specifies the name of the file containing the dynamically loaded object, and object_symbol is the linked function symbol, that is, the name of the function in the C source code. If the object symbol is omitted, it is assumed to match the name of the SQL function being defined. In C, all function names must be different, so overloaded functions implemented in C must be given different names (for example, include argument type designations in the C names).

If repeated calls to CREATE FUNCTION reference the same object file, it is loaded only once within the session. To upload and download this file again (for example, during development), start a new session. attribute

Historically a way to pass additional information about a function. This entry may contain the following attributes:

IsStrict

Equivalent to STRICT or RETURNS NULL ON NULL INPUT .

isCachable

Attribute names are case insensitive.

For more information on feature development, see Section 35.3.

Overload

PostgreSQL allows overload functions; that is, it allows the same name to be used for several different functions if they have different types of input arguments. Whether you use this feature or not, it requires caution when calling functions in databases where some users do not trust others; see Section 10.3.

Two functions are considered the same if they have the same name and type input arguments, OUT parameters are ignored. So for example these declarations would cause a conflict:

CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, out text) ...

Functions that have different argument types will not be considered to conflict at the time of creation, but the default values provided for them may cause a conflict at the time of use. For example, consider the following definitions:

CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, int default 42) ...

The call to foo(10) will fail due to ambiguity in the choice of function to call.

Notes

Full SQL type declaration syntax is allowed in the declaration of function arguments and return value. However, type modifiers in parentheses (for example, the precision field for type numeric) are not taken into account by the CREATE FUNCTION command. So, for example, CREATE FUNCTION foo (varchar(10)) ... will create the same function as CREATE FUNCTION foo (varchar) ... .

When replacing an existing function using CREATE OR REPLACE FUNCTION, there are restrictions on changes to parameter names. In particular, you cannot change a name already assigned to any input parameter (although you can add names to previously unnamed parameters). Also, if a function has more than one output parameter, you cannot change the names of the output parameters, as this will change the names of the columns of the anonymous composite type that describes the function's result. These restrictions help ensure that existing calls to a function will not stop working after it is replaced.

If a function is declared STRICT with a VARIADIC argument, strictness evaluation checks that the entire variable array generally not NULL. If this array contains NULL elements, the function will be called.

Examples

Below are some simple introductory examples. For more information and examples, see Section 35.3.

CREATE FUNCTION add(integer, integer) RETURNS integer AS "select $1 + $2;"

LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;

Function to increment an integer by 1 using a named argument, in PL/pgSQL:

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$ BEGIN RETURN i + 1;

END; $$ LANGUAGE plpgsql;

A function that returns a record with multiple output parameters:

CREATE FUNCTION dup(in int, out f1 int, out f2 text) AS $$ SELECT $1, CAST($1 AS text) || " is text" $$ LANGUAGE SQL; SELECT * FROM dup(42);

The same thing can be done in more detail by explicitly declaring a composite type:

CREATE TYPE dup_result AS (f1 int, f2 text); CREATE FUNCTION dup(int) RETURNS dup_result AS $$ SELECT $1, CAST($1 AS text) || " is text" $$ LANGUAGE SQL; SELECT * FROM dup(42);

Another way to return multiple columns is to use the TABLE function: CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text) AS $$ SELECT $1, CAST($1 AS text) || " is text" $$ LANGUAGE SQL; SELECT * FROM dup(42); However, the TABLE example is different from the previous ones, since in it the function actually returns not one, but

kit

records.

Development of secure SECURITY DEFINER functions

This function should access the admin.pwds table, but without a SET clause or with a SET clause that only includes admin , it can be tricked by creating a temporary table pwds .

Before PostgreSQL 8.3, there was no SET clause, so older functions can contain quite complex logic for saving, modifying, and restoring the search_path variable. The current SET offering makes this much easier.

Also be aware that the default execution permission for created functions is PUBLIC (see GRANT for details). However, it is often necessary to restrict access to functions that operate in the context of the definer to only some users. To do this, you need to revoke the standard PUBLIC rights and then grant execution rights individually. To avoid creating a window in which the new function will not be available at all, create it and assign rights in one transaction. For example, like this:

BEGIN; CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER; REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC; GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins; COMMIT;

Compatibility

The CREATE FUNCTION command is defined in SQL:1999 and later standards. The version implemented in PostgreSQL is close to the standardized one, but does not fully comply with it. In particular, attributes are not portable, as are different implementation languages.

For compatibility with other DBMS argument_mode can be written after argument_name or before it, but only the first option meets the standard.

To define default values for parameters, the SQL standard only supports syntax with the DEFAULT keyword. The = syntax is used in T-SQL and Firebird.