The title might be a little confusing but I will try explaining here better.

What I want to achieve is:

1. Create a table with 3 columns (so far so good):

   CREATE TABLE StatisticCounter
   (
       statisticID NUMBER GENERATED ALWAYS as IDENTITY (START with 1000 INCREMENT by 1), 
       statistikname varchar(255),
       counter integer 
   );
   

2. Create a way to call (function, procedure, trigger or something. I will call it function for now) that will increment counter with 1 based on statistikname or statisticid.

The restriction here being: said statistic is an SQL script ran through a cockpit file with a little bit of a different syntax than regular sql (in: out: select, WHERE with variables). I want to put a function or something in this cockpit file, that will run each time the script is run. Which will auto increment the number of times the statistic has been used.

I have no idea what I need (function, procedure, trigger or anything else) and this is why I am writing it a bit vague.

EDIT: I tried with merge but I always get the WHEN NOT MATCHED result executed. Without CAST its the same.

merge into StatisticCounter stc using 
CAST((select 1000 id from dual)AS INTEGER) val on (stc.statisticid=val.id) 
when matched then 
UPDATE StatisticCounter SET counter = counter   1; 
when not matched then 
select * from dual;

CodePudding user response：

I found an answer on my own after going through a lot of functions and procedures.

1. Create table;
2. Create function;
3. Call function in file.

The important thing here is that the outside _cockpit file that executes the SQL goes through all OUT parameters for each line the statistic returns as result. This makes the counter go wild - no way to catch END of statistic or beginning. So I made a second _cockpit with only 1 result and attach it to the statistic I want to count.

CREATE TABLE UDX_Table_StatisticCounter(statisticID INTEGER, StatistikName varchar2(256 char), counter integer);

CREATE OR REPLACE FUNCTION UDX_FUNC_StatisticCounter(datainput IN VARCHAR2) RETURN VARCHAR2 IS PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN UPDATE UDX_Table_StatisticCounter SET counter = counter 1 WHERE statisticID=datainput; COMMIT; RETURN 'done'; END UDX_FUNC_StatisticCounter;