I have to do a procedure that add plus 1 to the previous value every time its is called in PL/SQL language. But I don't know how to do that.
I mean, if the procedure is call "plus1":
First execution:
exec plus1
will return value 1.
Second execution:
exec plus1
will return value 2.
And go on
The best way is to create a sequence, as noticed in comments:
create sequence my_seq;
To call the sequence in PL/SQL:
my_var := my_seq.nextval;
To call in SQL:
select t.*, my_seq.nextval from table t;
In the SQL query, a new value will be generated for each line.
If you don't need a sequence, and you don't need to store the value between sessions, create a package:
create or replace package my_package as
function get_next_value return number;
end my_package;
/
create or replace package body my_package as
current_num number := 0;
function get_next_value return number is
begin
current_num := current_num + 1;
return current_num;
end;
end my_package;
/
And then call my_package.get_next_value.
It is not entirely clear what you need. Here is one approach, assuming you need a session variable, initialized to zero at the start of the session, which you can call as needed, and is increased only when a procedure is executed. This is different from a function that increments the variable and returns it at the same time.
If you need to access the variable in SQL (rather than just in PL/SQL), you need to write a wrapper function that returns the value; I included the wrapper function in the code below.
create or replace package silly_p as
v number := 0;
function show_v return number;
procedure increment_v;
end;
/
create or replace package body silly_p as
function show_v return number is
begin
return v;
end show_v;
procedure increment_v is
begin
v := v+1;
end increment_v;
end silly_p;
/
Here is a SQL*Session demonstrating the compilation of this package and then its use - I access the variable both through SQL SELECT and from PL/SQL (with DBMS_OUTPUT) to demonstrate both access methods. Notice how the value is unchanged between calls to the procedure, and increases by one every time the procedure is executed.
SQL> create or replace package silly_p as
2 v number := 0;
3 function show_v return number;
4 procedure increment_v;
5 end;
6 /
Package created.
Elapsed: 00:00:00.03
SQL>
SQL> create or replace package body silly_p as
2 function show_v return number is
3 begin
4 return v;
5 end show_v;
6 procedure increment_v is
7 begin
8 v := v+1;
9 end increment_v;
10 end silly_p;
11 /
Package body created.
Elapsed: 00:00:00.00
SQL> select silly_p.show_v from dual;
SHOW_V
----------
0
1 row selected.
Elapsed: 00:00:00.00
SQL> exec dbms_output.put_line(silly_p.v)
0
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> exec silly_p.increment_v
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
SQL> select silly_p.show_v from dual;
SHOW_V
----------
1
1 row selected.
Elapsed: 00:00:00.14
SQL> exec silly_p.increment_v
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
SQL> exec dbms_output.put_line(silly_p.v)
2
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
SQL>
I've answered a similar question recently (have a look here); basically, you need to store current value somewhere (a table might be a good choice) and create a function (or, in your case, a procedure) that returns the next number.
How to convert the function I wrote to a procedure? Use it as a wrapper.
Here's the whole example:
SQL> CREATE TABLE broj (redni_br NUMBER NOT NULL);
Table created.
SQL>
SQL> CREATE OR REPLACE FUNCTION f_get_broj
2 RETURN NUMBER
3 IS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 l_redni_br broj.redni_br%TYPE;
6 BEGIN
7 SELECT b.redni_br + 1
8 INTO l_redni_br
9 FROM broj b
10 FOR UPDATE OF b.redni_br;
11
12 UPDATE broj b
13 SET b.redni_br = l_redni_br;
14
15 COMMIT;
16 RETURN (l_redni_br);
17 EXCEPTION
18 WHEN NO_DATA_FOUND
19 THEN
20 LOCK TABLE broj IN EXCLUSIVE MODE;
21
22 INSERT INTO broj (redni_br)
23 VALUES (1);
24
25 COMMIT;
26 RETURN (1);
27 END f_get_broj;
28 /
Function created.
SQL>
SQL> CREATE PROCEDURE p_get_Broj
2 AS
3 BEGIN
4 DBMS_OUTPUT.put_line (f_get_broj);
5 END;
6 /
Procedure created.
SQL>
SQL> EXEC p_get_broj;
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> EXEC p_get_broj;
2
PL/SQL procedure successfully completed.
SQL> EXEC p_get_broj;
3
PL/SQL procedure successfully completed.
SQL> EXEC p_get_broj;
4
PL/SQL procedure successfully completed.
I believe you need a session a variable like mathguy's assumption above. You can try below code and understand how it works. Note that each DB session could have different value to the NUM_VAR variable in var_pkg package depending on how many times the procedure below was executed for each session.
CREATE OR REPLACE PACKAGE var_pkg
IS
num_var NUMBER := 0;
PROCEDURE set_num_var(p_number NUMBER);
FUNCTION get_num_var RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY var_pkg
IS
PROCEDURE set_num_var(p_number NUMBER)
IS
BEGIN
num_var := p_number;
END;
FUNCTION get_num_var RETURN NUMBER
IS
BEGIN
RETURN num_var;
END;
END;
/
CREATE PROCEDURE plus1
IS
v_num NUMBER;
BEGIN
v_num := var_pkg.get_num_var + 1;
var_pkg.set_num_var(v_num);
DBMS_OUTPUT.PUT_LINE(v_num);
END;
/
To run the procedure,
exec plus1;
or
BEGIN
plus1;
END;
/
And in case you want to know the current value of the variable, you can query it using below code,
SELECT var_pkg.get_num_var
FROM dual;
