Ingeniera

Understanding the Main Features of PL/SQL
A good way to get acquainted with PL/SQL is to look at a sample program. The
program below processes an order for a tennis racket. First, it declares a variable of
type NUMBER to store the quantity of tennis rackets on hand. Then, it retrieves the
quantity on hand from a database table named inventory. If the quantity is
greater than zero, theprogram updates the table and inserts a purchase record into
another table named purchase_record. Otherwise, the program inserts an
out-of-stock record into the purchase_record table.
-- available online in file ’examp1’
DECLARE
qty_on_hand NUMBER(5);
BEGIN
SELECT quantity INTO qty_on_hand FROM inventory
WHERE product = ’TENNIS RACKET’
FOR UPDATE OF quantity;
IF qty_on_hand > 0 THEN -- checkquantity
UPDATE inventory SET quantity = quantity - 1
WHERE product = ’TENNIS RACKET’;
INSERT INTO purchase_record
VALUES (’Tennis racket purchased’, SYSDATE);
ELSE
INSERT INTO purchase_record
VALUES (’Out of tennis rackets’, SYSDATE);
END IF;
COMMIT;
END;
With PL/SQL, you can use SQL statements to manipulate Oracle data and
flow-of-control statements to process the data. You can alsodeclare constants and
variables, define procedures and functions, and trap runtime errors. Thus, PL/SQL
combines the data manipulating power of SQL with the data processing power of
procedural languages.
Block Structure
PL/SQL is a block-structured language. That is, the basic units (procedures,
functions, and anonymous blocks) that make up a PL/SQL program are logical
blocks, which cancontain any number of nested sub-blocks. Typically, each logical
block corresponds to a problem or subproblem to be solved. Thus, PL/SQL
supports the divide-and-conquer approach to problem solving called stepwise
refinement.

Declaring Variables
Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or any
PL/SQL datatype, such as BOOLEAN or BINARY_INTEGER. For example, assumethat you want to declare a variable named part_no to hold 4-digit numbers and a
variable named in_stock to hold the Boolean value TRUE or FALSE. You declare
these variables as follows:
Cursor Variables
Como un Cursor, a cursor variable points to the current row in the result set of a
multi-row query. . Pero, desemejante de un cursor, una variable del cursor se puede abrir para cualquier preguntatipo-compatible. No se ata a una pregunta específica. Las variables del cursor son las variables verdaderas de PL/SQL, a las cuales usted puede asignar nuevos valores y a las cuales usted puede pasar a los subprogramas almacenados en una base de datos de Oracle. Esto le da más flexibilidad y una manera conveniente de centralizar la recuperación de datos.


Typically, you open a cursor variableby passing it to a stored procedure that
declares a cursor variable as one of its formal parameters. The following procedure
opens the cursor variable generic_cv para query escogido:


PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice NUMBER) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
ELSIFchoice = 3 THEN
OPEN generic_cv FOR SELECT * FROM salgrade;
END IF;
...
END;

Attributes

PL/SQL variables and cursors have attributes, which are properties that let you(que le permite)
reference the datatype and structure of an item sin repetir su definicion
Database columns and tables have similar attributes, which you can use to ease
Maintenance (para facilitar el mantenimiento). Apercent sign (%) serves as the attribute indicator (un buen percentage sirve como indicador de atributo).

%TYPE
The %TYPE attribute provides the datatype of a variable or database column. This is
particularly useful when declaring variables that contenga database values. For
example, assume there is a column named title in a table named books. To
declare a variable named my_title that has...