Define function in SQL Navigator query

Question

I would like to extract a function from a piece of SQL code that is used several times in a single query. I am looking for functionality similar to the syntax of the following (invented by me):

with f(x) as (return x+1) select f(thing1), f(thing2), f(thing3) from things 

thing1, thing2, thing3 - whole columns in the table "things" in the example. Also, imagine f is more complicated than the add-one function.

How to define a function inside a request?

Answer 1

Declaring a function in a WITH clause is not possible, but according to the information provided in OOW, it will be in version 12c. Therefore, at the moment you need to create a function as a schematic object, regardless of whether it is an autonomous function or part of a package. For instance:

 create or replace function F(p_p in number) return number is begin return p_p + 1; end; 

And then call it in the query, ensuring that the data type of the column that you pass to the function as a parameter has the same data type as the function parameter:

 select f(col1) , f(col2) , ... , f(coln) from your_table 

Answer 2

You are trying to build a function for dynamic columns of a table and table, I would do some code like this? And you cannot declare a function in a WITH clause of a query.

  SELECT f ( tablename,columnname1 ), f ( tablename,columnname2 ), ........ FROM tablename; Create or replace function f (tableName varchar2,ColumnName varchar2) Return somethingHere Is varTableName varchar2(200); varColumnName varchar2(200); varValue integer; t_cid INTEGER; t_command VARCHAR2(200); Begin --Get tableName varTableName := tableName ; --Get columnName varColumnName := ColumnName ; t_command := 'SELECT ' || varColumnName ||' FROM ' || varTableName; --Here execute dynamic sql statement DBMS_SQL.PARSE DBMS_SQL.DEFINE_COLUMN DBMS_SQL.EXECUTE --fatch row values into varValue DBMS_SQL.COLUMN_VALUE (..,..,varValue); --then do your x+1 magic here varValue := varValue+1 --then output your value. End;