Statements in Oracle stored procedures

The solution is as follows: 1. The create global temporary table t temp statement is to create a temporary table, there are two kinds of temporary tables created in oracle, one is transaction-level and the other is session-level, when the creation of temporary table statement is followed by on commit delete rows, it means that the temporary table is transaction-level, and when on commit preserve rows it means that the temporary table is session-level.

2. Execute Immediate is the dynamic execution of SQL statements.

The preceding statement means that a DDL statement is dynamically executed to create a transaction-level temporary table.

Execute Immediate indicates dynamic execution and improved efficiency and performance.

A temporary table is created.

On commit delete rows indicates that the temporary table is a transaction, and Oracle will truncate the table (delete all rows) after each commit

First, the subject is different.

1. Function: When you need to analyze whether the values in the data list meet specific conditions, use the database worksheet function.

2. Stored procedure: It is a set of SQL statements in a large database system that is stored in the database in order to complete a specific function, and is permanently valid after a compilation.

Second, the characteristics are different.

1. Function: Only one variable can be returned, which can be embedded in SQL and used in stored procedures.

2. Stored procedure: The user executes by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters). A stored procedure is an important object in a database. In the case of a particularly large amount of data, the use of stored procedures can achieve double speed efficiency improvement.

Third, the functions are different.

1. Functions: Temporary tables cannot be used, only table variables can be used, and functions cannot perform a set of operations to modify the state of the global database. It can be called as part of a query statement, and since the function can return a table object, it is located after the from keyword in the query.

2. Stored procedure: The stored procedure needs to be executable for SQL query, and the stored procedure can return parameters, such as record sets. SQL statements cannot contain stored procedures.

1.The difference in return values is that a function has one return value, while a stored procedure is returned by parameters, which can have multiple or none.

2.The difference between invocation is that functions can be called directly in query statements, while stored procedures must be called separately.

Functions are generally used to compute and return a computation result in hailstorms, while stored procedures are generally used to perform specific data operations (such as modifying, inserting database tables, or executing certain DDL statements, etc.).

The return of the parameters is as follows:

If you return multiple parameter values, it is best to use a stored procedure, and if you have only one return value, you can use a function;

Judging by the call situation:

If it is called in an SQL statement (dml or select), it must be a stored function or a stored encapsulated function, not a stored procedure, but there are still many restrictions and the purity level of the function when calling the stored function.

If it's called in a procedural statement, it depends on what kind of functionality you're trying to implement. Functions are generally used to compute and return a calculation result, while stored procedures are generally used to complete specific data operations (such as modifying, inserting database tables, executing certain DDL statements, etc.), so although their syntax is similar, the functions that users need to accomplish when using them are mostly different.

The biggest difference between an oracle function and a stored procedure is that a function must have a return value followed by the type of return value, while a stored procedure can have no return value.

In addition, stored procedures and functions also support in, out, and in out parameters of the three types of codes or types, corresponding to the parameters of the input type, the parameters of the output type, and the parameters of the input and output types, respectively. That is to say, if we need to return multiple values, in addition to using stored procedures, we can also use functions to do so, by returning one of them and indicating that the other data is an out parameter.

In Oracle, both stored procedures and functions can return values, but functions must return a value, and generally only return one value, while stored procedures do not have this limitation. From the general point of view, if you don't need a return value or need multiple return values, use a stored procedure, if you only use a return value, use a function, from the general programmer's habit, this is more in line with thinking.

In addition, the naming of stored procedures should start with proc, the function should start with func, and the variable should start with v.

Tags: Oracle function query select application method for maximum execution by users.