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?

+3


source to share


3 answers


A statement is a piece of code that gets executed. This is a team. He's doing something. Example:

print "Hello World"



A function is a block of code that you call with parameters and returns a value (even if it's undefined). Example:

function (a) {return a * 2}

+1


source


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.

+1


source


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.

enter image description here


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;

      

0


source







All Articles