An assignment of a value to a pl/sql variable is written as:

variable := expression;

As explained previously, the expression in such a statement is evaluated by means of an SQL SELECT command sent to the main database engine. The expression must yield a single value (possibly a row value, if the variable is a row or record variable). The target variable can be a simple variable (optionally qualified with a block name), a field of a row or record variable, or an element of an array that is a simple variable or field.

Examples:

tax := subtotal * 0.06;
my_record.user_id := 20;

The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding an INTO clause. For example,

SELECT select_expressions INTO target FROM ...;
INSERT ... RETURNING expressions INTO target;
UPDATE ... RETURNING expressions INTO target;
DELETE ... RETURNING expressions INTO target;

where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields. pl/sql variables will be substituted into the rest of the query, and the plan is cached, just as described above for commands that do not return rows. This works for SELECT, INSERT/UPDATE/DELETE with RETURNING, and utility commands that return row-set results (such as EXPLAIN). Except for the INTO clause, the SQL command is the same as it would be written outside pl/sql.

If a row or a variable list is used as target, the query's result columns must exactly match the structure of the target as to number and data types, or else a run-time error occurs. When a record variable is the target, it automatically configures itself to the row type of the query result columns.

The INTO clause can appear almost anywhere in the SQL command. Customarily it is written either just before or just after the list of select_expressions in a SELECT command, or at the end of the command for other command types. It is recommended that you follow this convention in case the pl/sql parser becomes stricter in future versions.

If STRICT is not specified in the INTO clause, then target will be set to the first row returned by the query, or to nulls if the query returned no rows. (Note that “the first row” is not well-defined unless you've used ORDER BY.) Any result rows after the first row are discarded. You can check the special SQL%FOUND variable to determine whether a row was returned:

DROP TABLE t1;
DROP SEQUENCE t1_seq;

CREATE TABLE t1 (
  id NUMBER(10),
  description VARCHAR2(50),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

CREATE SEQUENCE t1_seq;

INSERT INTO t1 VALUES (t1_seq.nextval, 'ONE');
INSERT INTO t1 VALUES (t1_seq.nextval, 'TWO');
INSERT INTO t1 VALUES (t1_seq.nextval, 'THREE');

The RETURNING INTO clause allows us to return column values for rows affected by DML statements.
The returned data could be a single column. For example, When we insert data using a sequence to
generate our primary key value, we can return the primary key value as follows.

DECLARE
  l_id t1.id%TYPE;
BEGIN
  INSERT INTO t1 VALUES (t1_seq.nextval, 'FOUR')
  RETURNING id INTO l_id;
  COMMIT;

  DBMS_OUTPUT.put_line('ID=' || l_id);
END;
/

For example, The syntax is also available for update and delete statements.
DECLARE
  l_id t1.id%TYPE;
BEGIN
  UPDATE t1
  SET    description = description
  WHERE  description = 'FOUR'
  RETURNING id INTO l_id;

  DBMS_OUTPUT.put_line('UPDATE ID=' || l_id);

  DELETE FROM t1
  WHERE  description = 'FOUR'
  RETURNING id INTO l_id;

  DBMS_OUTPUT.put_line('DELETE ID=' || l_id);

  COMMIT;
END;
/

pl/sql supports common table expressions (cte), which are used in anonymous blocks, stored procedures, functions, and packages. The cte can be followed by the INSERT, UPDATE, and DELETE clauses. If it does followed by the SELECT statement, which must contain into_clause, otherwise an error will be reported. DML operations are not allowed in functions. with_clasue can also be used as a subquery, but recursion is not supported. Common Table Expression. For example:

CREATE TABLE company(
   ID INT      NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
INSERT INTO company VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO company VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO company VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO company VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO company VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO company VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO company VALUES (7, 'James', 24, 'Houston', 10000.00 );

with_clause used in anonymous blocks, For example:

declare
v_count varchar(100);
begin
with tmp as (select * from company where id=2) select * into v_count from tmp;
DBMS_OUTPUT.PUT_LINE(v_count);
end;
/

with_clause used in function, For example:

create or replace function hs_with_f
return int 
as
v_count INTEGER;
begin
with tmp as (select * from company where id=2) select id into v_count from tmp;
return v_count;
end;
/

select hs_with_f();

with_clause are used in stored procedures, For example:

create or replace procedure hs_with_p as
v_count varchar(100);
begin
with tmp as (select * from company where id=2) select id into v_count from tmp;
DBMS_OUTPUT.PUT_LINE(v_count);
end;
/

call hs_with_p();

with_clause used in the package, For example:

create or replace package hs_with_pkg
 is
 v_count varchar(100);
 function hs_with_f return int;
 procedure hs_with_p;
end;
/

create or replace package body hs_with_pkg is
 function hs_with_f return int as
 begin
  with tmp as (select * from company where id=2) select id into v_count from tmp;
  return v_count;
 end;
 
 procedure hs_with_p as
 begin
  with tmp as (select * from company where id=3) select id into v_count from tmp;
  DBMS_OUTPUT.PUT_LINE(v_count);
 end;
 
 begin
  with tmp as (select * from company where id=4) select id into v_count from tmp;
  DBMS_OUTPUT.PUT_LINE(v_count);
end;
/

select hs_with_pkg.hs_with_f;
call hs_with_pkg.hs_with_p;

pl/sql supports DML, which are used in anonymous blocks, stored procedures, functions, and packages. DML includes INSERT, UPDATE, and DELETE clauses. If it has a SELECT statement, which must contain into_clause, otherwise an error will be reported. For example:

create table dml_table(id int, name varchar2(100), job varchar2(100), age int);
insert into dml_table values (1, 'asda', 'gfdgd', 12);
insert into dml_table values (2, 'sdfsd', 'cvxvx', 14);
insert into dml_table values (3, 'uyiy', 'mmbv', 16);

create or replace function support_dml return int as
begin
update dml_table set id = 4 where name = 'uyiy';
return 12;
end;
/
select support_dml() from dual;

declare
retcode int := 1;
begin
retcode := support_dml();
dbms_output.put_line(retcode);
end;
/

Oftentimes you will want to generate dynamic commands inside your pl/sql functions, that is, commands that will involve different tables or different data types each time they are executed. pl/sql's normal attempts to cache plans for commands will not work in such scenarios. To handle this sort of problem, the EXECUTE IMMEDIATE statement is provided:

EXECUTE IMMEDIATE command-string [ INTO target ] [ USING expression [, ... ] ];

where command-string is an expression yielding a string (of type varchar) containing the command to be executed. The optional target is a record variable, a row variable, or a comma-separated list of simple variables and record/row fields, into which the results of the command will be stored. The optional USING expressions supply values to be inserted into the command.

No substitution of pl/sql variables is done on the computed command string. Any required variable values must be inserted in the command string as it is constructed; or you can use parameters as described below.

Also, there is no plan caching for commands executed via EXECUTE IMMEDIATE . Instead, the command is always planned each time the statement is run. Thus the command string can be dynamically created within the function to perform actions on different tables and columns.

The INTO clause specifies where the results of a SQL command returning rows should be assigned. If a row or variable list is provided, it must exactly match the structure of the query's results (when a record variable is used, it will configure itself to match the result structure automatically). If multiple rows are returned, only the first will be assigned to the INTO variable. If no rows are returned, NULL is assigned to the INTO variable(s). If no INTO clause is specified, the query results are discarded.

The BULK COLLECT INTO clause specifies where the results of a SQL command returning rows should be assigned. If multiple rows are returned, all will be assigned to the BULK COLLECT INTO variable. If no rows are returned, NULL is assigned to the BULK COLLECT INTO variable(s). If no BULK COLLECT INTO clause is specified, the query results are discarded. An example is:

declare	
  type array_number is table of number;
  type array_varchar is table of varchar2(255);
 
  a_employee_id    array_number := array_number();
  a_first_name     array_varchar := array_varchar();
 	
BEGIN
  
  dbms_output.put_line('----------multiple nested table---------');
  execute IMMEDIATE 'WITH employeetemp AS (
      SELECT employee_id AS eid, first_name AS fname FROM employees
    )
    SELECT eid, fname FROM employeetemp' bulk collect into a_employee_id, a_first_name ;
 
  for i in a_employee_id.first .. a_employee_id.last loop
 		dbms_output.put_line(a_employee_id(i)); 	
  end loop;
  
  for i in a_first_name.first .. a_first_name.last loop
 		dbms_output.put_line(a_first_name(i)); 	
  end loop; 
  
end;
/

If the STRICT option is given, an error is reported unless the query produces exactly one row.

The command string can use parameter values, which are referenced in the command as :inserted_by, :inserted, etc. These symbols refer to values supplied in the USING clause. This method is often preferable to inserting data values into the command string as varchar: it avoids run-time overhead of converting the values to varchar and back, and it is much less prone to SQL-injection attacks since there is no need for quoting or escaping. An example is:

EXECUTE IMMEDIATE 'SELECT count(*) FROM mytable WHERE inserted_by = :inserted_by AND inserted <= :inserted'
   INTO c
   USING checked_user, checked_date;

An EXECUTE IMMEDIATE with a simple constant command string and some USING parameters, as in the first example above, is functionally equivalent to just writing the command directly in pl/sql and allowing replacement of pl/sql variables to happen automatically. The important difference is that EXECUTE IMMEDIATE will re-plan the command on each execution, generating a plan that is specific to the current parameter values; whereas pl/sql may otherwise create a generic plan and cache it for re-use. In situations where the best plan depends strongly on the parameter values, it can be helpful to use EXECUTE IMMEDIATE to positively ensure that a generic plan is not selected.

SELECT INTO is not currently supported within EXECUTE IMMEDIATE; instead, execute a plain SELECT command and specify INTO as part of the EXECUTE IMMEDIATE itself.

The using clause uses the variables used can be added with variable types in and out. in type variables indicate that the input variable cannot be changed and out type variable indicates that the input variable can be changed. An example is:

CREATE OR REPLACE PROCEDURE TargetProcedure(  
    p_input1 IN VARCHAR2,  
    p_input2 IN NUMBER,  
    p_output1 OUT VARCHAR2,  
    p_output2 OUT NUMBER  
) AS  
BEGIN   
    DBMS_Output.PUT_LINE('TargetProcedure begin-> p_input1 '||' is: '||p_input1);
    DBMS_Output.PUT_LINE('TargetProcedure begin-> p_input2 '||' is: '||p_input2);
    DBMS_Output.PUT_LINE('TargetProcedure begin-> p_output1 '||' is: '||p_output1);
    DBMS_Output.PUT_LINE('TargetProcedure begin-> p_output2 '||' is: '||p_output2);
    p_output1 := 'Response from TargetProcedure: ' || p_input1;  
    p_output2 := p_input2 * 10;  
    DBMS_Output.PUT_LINE('TargetProcedure end-> p_input1 '||' is: '||p_input1);
    DBMS_Output.PUT_LINE('TargetProcedure end-> p_input2 '||' is: '||p_input2);
    DBMS_Output.PUT_LINE('TargetProcedure end-> p_output1 '||' is: '||p_output1);
    DBMS_Output.PUT_LINE('TargetProcedure end-> p_output2 '||' is: '||p_output2);
END TargetProcedure;  
/
CREATE OR REPLACE PROCEDURE MainProcedure(  
    main_input1 IN VARCHAR2,  
    main_input2 IN NUMBER,  
    main_output1 OUT VARCHAR2,  
    main_output2 OUT NUMBER  
) AS  
BEGIN  
    main_output2 := 0;
    main_output1  := 'Operation successful';
    -- Use EXECUTE IMMEDIATE to execute dynamic SQL and pass parameters  
    DBMS_Output.PUT_LINE('MainProcedure begin-> main_input1 '||' is: '||main_input1);
    DBMS_Output.PUT_LINE('MainProcedure begin-> main_input2 '||' is: '||main_input2);
    DBMS_Output.PUT_LINE('MainProcedure begin-> main_output1 '||' is: '||main_output1);
    DBMS_Output.PUT_LINE('MainProcedure begin-> main_output2 '||' is: '||main_output2);
    EXECUTE IMMEDIATE 'BEGIN TargetProcedure(:p_input1, :p_input2, :p_output1, :p_output2); END;'  
    USING IN main_input1, IN main_input2, OUT main_output1, OUT main_output2;  
    DBMS_Output.PUT_LINE('MainProcedure end-> main_input1 '||' is: '||main_input1);
    DBMS_Output.PUT_LINE('MainProcedure end-> main_input2 '||' is: '||main_input2);
    DBMS_Output.PUT_LINE('MainProcedure end-> main_output1 '||' is: '||main_output1);
    DBMS_Output.PUT_LINE('MainProcedure end-> main_output2 '||' is: '||main_output2);
END MainProcedure;  
/
call MainProcedure('xiaoming',123,'xiaohong',234);

Sometimes a placeholder statement that does nothing is useful. For example, it can indicate that one arm of an if/then/else chain is deliberately empty. For this purpose, use the NULL statement:

NULL;

For example, the following two fragments of code are equivalent:

declare
y int := 10;
x int := 10;
BEGIN
  NULL;
END;
/