Difference between function and expression
In the class, we were asked to name several PL / SQL functions and indicate their use. Someone mentioned CREATE FUNCTION and I noticed that Oracle designated this as a statement, not a function.
I cannot give a very good answer about the difference between the two. I think the assertion is more of a declarative tool that indicates what the program should execute, whereas the function seems to be more procedural, we define a procedure for the new function in the CREATE FUNCTION statement.
The distinction seems clearer for something like a SELECT statement, can someone please help with a clear distinction between the two?
source to share
CREATE FUNCTION
- this statement. The function itself is FUNCTION
. Running the operator creating this function allows you to access this function.
In general, if you create anything in the database, you use an instruction (specifically, a Data Definition Language (DDL) instruction). The element you create, be it a table, function, package, etc., can then be referenced in future operations.
source to share
The word "statement" has two different meanings in SQL and PL / SQL.
In the narrow technical sense, it CREATE FUNCTION
is a type of SQL statement and CREATE FUNCTION
contains PL / SQL statements.
More generally, an SQL statement is declarative code that tells the database "what is true" or "what to do" without detailed instructions on "how to do it." A function or any PL / SQL block is required code that tells the database exactly "what to do". Declarative SQL is usually simpler and faster than imperative PL / SQL, so it's usually best to do most of the work in SQL and just glue it together with PL / SQL.
SQL has about 170 commands (as defined V$SQLCOMMAND
), organized into about 31 types of statements, organized in below 6 categories , Command CREATE FUNCTION
is the type of statement CREATE
in a category DDL
. This classification scheme is not 100% clear from the database and manuals, but I used it to carefully classify all the SQL statements .
Data Definition Language (DDL) Statements
Data Manipulation Language (DML) Statements
Transaction Control Statements
Session Control Statements
System Control Statement
Embedded SQL Statements
In PL / SQL, most blocks are sequences of statements. These operations can be assignment, loop, fetch, block, etc.
To make things more confusing, in 12c the SQL statement can contain PL / SQL statements:
with function f return number is begin return 1; end; select f from dual;
source to share