Using SQL in PL/SQL.

The only SQL statements allowed directly in PL/SQL programs are DML (Data Manipulation Language: select, insert, update, delete) and transaction control statements (commit, rollback, etc). For example, the following hypothetical block is illegal: 
begin
  create table TestTable (
    id   number(7),
    name varchar2(24)
  );
  insert into TestTable values (123, 'testing');
end;
It's illegal because of the DDL (Data Definition Language) statement create table, but we can explain it ourselves the following way. In order to compile this, the TestTable needs to be bound. This process will check to see if the table exists. However, the table won't exist until the block is run. But because the block can't even compile, there is no way it can be run.

Using dynamic SQL

There is, however, a technique that allows all valid SQL statements, including DDL, to be issued from PL/SQL: dynamic SQL: 
begin
  execute immediate 'create table TestTable (
    id   number(7),
    name varchar2(24)
  )';
  execute immediate 'insert into TestTable values (123, ''testing'')';
end;

The select statement

PL/SQL version of the select statement has practically the same syntax: 
select * | list_of_items
  [into record | variable | list_of_variables ]
  from table_reference
 [where conditions]
 [group by list_of_items]
[having conditions] 
 [order by order_clause];
The form of the select statement described here (the one which includes into clause) should return no more than one row. The where clause will be compared against each row in the table. If it matches more than one row, PL/SQL returns this error message: 
ORA-1427: Single-row query returns more than one row
The example from Oracle 9i: PL/SQL Programming book illustrates two different select statements.

The returning clause

It is often desirable to know information about rows modified by a DML statement once the statement has been issued - for example, the rowid of a newly inserted row. One way of accomplishing this is to issue a select statement. However, this involves a second SQL statement. Oracle provides a shortcut: the returning clause. This clause is valid at the end of any DML statement, and is used to get information about the row just processed. The syntax is described below. 
DML_statement
returning list_of_expressions into list_of_variables;
For example, the following code prints the rowid of just inserted row: 
 declare
   newid rowid;
 begin
   insert into pl_test
     values ('thesis', 234)
     returning rowid into newid;
   dbms_output.put_line('ROWID of the inserted row is ' || newid); 
 end;
This more sophisticated example shows how to use the returning clause with all DML statements. Please notice that the returning clause has the same constraint as the into clause in the select statement: the query should process only one row.