All variables used in a block must be declared in the
declarations section of the block.
(The only exceptions are that the loop variable of a FOR loop
iterating over a range of integer values is automatically declared as an
integer variable, and likewise the loop variable of a FOR loop
iterating over a cursor's result is automatically declared as a
record variable.)
pl/sql variables can have any SQL data type, such as
integer, varchar, and
char.
Here are some examples of variable declarations:
user_id integer; quantity numeric(5); url varchar(64); myrow tablename%ROWTYPE; myfield tablename.columnname%TYPE; arow RECORD;
The general syntax of a variable declaration is:
name[ CONSTANT ]type[ NOT NULL ] { DEFAULT | := }expression;
The DEFAULT clause, if given, specifies the initial value assigned
to the variable when the block is entered. If the DEFAULT clause
is not given then the variable is initialized to the
SQL null value.
The CONSTANT option prevents the variable from being
assigned to after initialization, so that its value will remain constant
for the duration of the block.
If NOT NULL
is specified, an assignment of a null value results in a run-time
error. All variables declared as NOT NULL
must have a nonnull default value specified.
Examples:
quantity integer DEFAULT 32; url varchar(64) := 'http://mysite.com'; user_id CONSTANT integer := 10;
The data type of the declared function parameter name can be either the SQL parameter type or the pl/sql parameter type. And you can use in and out to modify the parameters. For example:
CREATE FUNCTION sales_tax(subtotal in real) RETURN real IS
BEGIN
RETURN subtotal * 0.06;
END;
/
If the anonymous block requires a function return value, use the inout decorating parameter, and the return value must have a specific return value, otherwise it is a record. For example：
create table nested_tab(id int, name varchar2(100), job varchar2(100), age int);
insert into nested_tab values (2, 'sdfsd', 'cvxvx', 14);
create or replace function nested_func(id1 inout int, name1 inout varchar2(100), job1 inout varchar2(100), age1 inout int)
return int as
id2 int;
name2 varchar2(100);
job2 varchar2(100);
age2 int;
begin
select * into id2, name2, job1, age1 from nested_tab where age = 14;
id1 := id2;
name1 := name2;
job1 := job2;
age1 := age2;
begin
id1 := 45;
name1 := 'name';
job1 := 'job';
age1 := 45;
end;
return 0;
end;
/
create or replace function nested_func12(id1 inout int, name1 inout varchar2(100), job1 inout varchar2(100), age1 inout int)
return int as
retcode int;
begin
begin
retcode := nested_func(id1, name1, job1, age1);
end;
return 1;
end;
/
declare
id2 int;
name2 varchar2(100);
job2 varchar2(100);
age2 int;
retcode int;
begin
begin
retcode := nested_func12(id2, name2, job2, age2);
end;
dbms_output.put_line(id2 || name2 || job2 || age2);
end;
/
variable%TYPE
%TYPE provides the data type of a variable or
table column. You can use this to declare variables that will hold
database values. For example, let's say you have a column named
user_id in your users
table. To declare a variable with the same data type as
users.user_id you write:
user_id users.user_id%TYPE;
By using %TYPE you don't need to know the data
type of the structure you are referencing, and most importantly,
if the data type of the referenced item changes in the future (for
instance: you change the type of user_id
from integer to real), you might not need
to change your function definition.
%TYPE is particularly valuable in polymorphic
functions, since the data types needed for internal variables can
change from one call to the next. Appropriate variables can be
created by applying %TYPE to the function's
arguments or result placeholders.
%TYPE can also use to declare vaiables reference package variable.
To declare a variable with the same data type as package.variable you write:
var package.variable%TYPE;
nametable_name%ROWTYPE;
A variable of a composite type is called a row
variable (or row-type variable). Such a variable
can hold a whole row of a SELECT or FOR
query result, so long as that query's column set matches the
declared type of the variable.
The individual fields of the row value
are accessed using the usual dot notation, for example
rowvar.field.
A row variable can be declared to have the same type as the rows of
an existing table or view, by using the
table_name%ROWTYPE
notation; or it can be declared by giving a composite type's name.
(Since every table has an associated composite type of the same name,
it actually does not matter in LightDB whether you
write %ROWTYPE or not. But the form with
%ROWTYPE is more portable.)
name RECORD;
Record variables are similar to row-type variables, but they have no
predefined structure. They take on the actual row structure of the
row they are assigned during a SELECT or FOR command. The substructure
of a record variable can change each time it is assigned to.
A consequence of this is that until a record variable is first assigned
to, it has no substructure, and any attempt to access a
field in it will draw a run-time error.
Note that RECORD is not a true data type, only a placeholder.
One should also realize that when a pl/sql
function is declared to return type record, this is not quite the
same concept as a record variable, even though such a function might
use a record variable to hold its result. In both cases the actual row
structure is unknown when the function is written, but for a function
returning record the actual structure is determined when the
calling query is parsed, whereas a record variable can change its row
structure on-the-fly.
name EXCEPTION;
The EXCEPTION type is used to declare user-defined exception variables,
and after declaring the variables, use PRAGMA EXCEPTION_INIT(exception_variable, error code)
command to initialize the exception error code.
Please note that the value of EXCEPTION variable can be
either positive numbers 100 or any negative numbers greater than -100000 except -1403.