Tài liệu interating with oracle ppt

Interacting with Oracle 22Ć5
Overview
When you need to extract information from or apply changes to the database, you
must use SQL. PL/SQL supports full data manipulation language and transaction
control commands within SQL. You can use SELECT statements to populate
variables with values queried from a row in a table. Your DML commands can
process multiple rows.
Comparing SQL and PL/SQL Statement Types
D A PL/SQL block is not a transaction unit. Commits, savepoints, and rollbacks are
independent of blocks, but you can issue these commands within a block.
D PL/SQL does not support data definition language (DDL), such as CREATE
TABLE, ALTER TABLE, or DROP TABLE.
D PL/SQL does not support data control language (DCL), such as GRANT or
REVOKE.
D DBMS_SQL package allows you to issue DDL and DCL statements.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder22Ć6
Interacting with Oracle 22Ć7
Retrieving Data Using PL/SQL
Use the SELECT statement to retrieve data from the database. The SELECT
statement contains an additional mandatory clause: the INTO clause. In the INTO
clause, list the output variables for receiving the data. The SELECT statement must
return exactly one row or an error will occur.
Abridged Syntax
SELECT select_list
INTO variable_name | record_name
FROM table
WHERE condition;
where: select_list is a list of at least one column, and can include
SQL expressions, row functions, or group
functions.
variable_name is the scalar variable to hold the retrieved value.
record_name is the PL/SQL RECORD to hold the retrieved
values.
table specifies the database table name.
condition is composed of column names, expressions,
constants, and comparison operators, including
PL/SQL variables and constants.
Take advantage of the full range of Oracle7 Server syntax for the SELECT statement.
Guidelines
D Terminate each SQL statement with a semicolon (;).
D Assign values into PL/SQL tables in a loop by declaring an explicit cursor.
D The INTO clause is required for the SELECT statement when it is embedded
within PL/SQL.
D The WHERE clause is optional, and can be used to specify input variables,
constants, literals, or PL/SQL expressions.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder22Ć8
Interacting with Oracle 22Ć9
Retrieving Data Using PL/SQL continued
Guidelines
D Specify the same number of output variables in the INTO clause as database
columns in the SELECT clause. Be sure that they correspond positionally and that
their datatypes are compatible.
D Ensure that the datatype of the identifiers match the datatype of the columns by
using the %TYPE attribute. The datatype and number of variables in the INTO
clause match those in the SELECT list.
D Terminate the PL/SQL block with the END statement. You can add the name of
the subprogram after the keyword END for clarity.
D Include at least one RETURN statement in a function.
D Use group functions, such as SUM, in a SQL statement since group functions
apply to groups of rows in a table.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder22Ć10
Interacting with Oracle 22Ć11
Retrieving Data Using PL/SQL continued
A PL/SQL RECORD can be used to easily create fields that match a database table’s
columns. Each field has the same name and datatype as a column in the table. When
retrieving all columns from a table, use a PL/SQL RECORD to hold the retrieved
values.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder22Ć12
Interacting with Oracle 22Ć13
Retrieving Data Using PL/SQL continued
Avoid ambiguity in the WHERE clause by adhering to a naming convention that
distinguishes database column names from PL/SQL variable names.
Example
Retrieve the order date and the ship date for the specified order. This example raises
an unhandled runtime exception.
PROCEDURE order_info
(id s_ord.id%TYPE)
IS
date_ordered s_ord.date_ordered%TYPE;
date_shipped s_ord.date_shipped%TYPE;
BEGIN
SELECT date_ordered, date_shipped
INTO date_ordered, date_shipped
FROM s_ord
WHERE id = id;

END order_info;
PL/SQL> order_info (100);
ERROR 0 at line 1, column 0
Unhandled exception ORA-01422: exact fetch returns
more than requested number of rows
ORA-06512: at line 7
PL/SQL checks whether an identifier is a column in the database; if not, it is assumed
to be a PL/SQL identifier.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder22Ć14

Xem chi tiết: Tài liệu interating with oracle ppt