PL-SQL(存储过程培训).pptVIP

Programming in Oracle with PL/SQL PL/SQL Allows using general programming tools with SQL, for example: loops, conditions, functions, etc. This allows a lot more freedom than general SQL, and is lighter-weight than JDBC. We write PL/SQL code in a regular file, for example PL.sql, and load it with @PL in the sqlplus console. PL/SQL Blocks PL/SQL code is built of Blocks, with a unique structure. There are two types of blocks in PL/SQL: Anonymous Blocks: have no name (like scripts) can be written and executed immediately in SQLPLUS can be used in a trigger 2. Named Blocks: Procedures Functions Anonymous Block Structure: DECLARE (optional) /* Here you declare the variables you will use in this block */ BEGIN (mandatory) /* Here you define the executable statements (what the block DOES!)*/ EXCEPTION (optional) /* Here you define the actions that take place if an exception is thrown during the run of this block */ END; (mandatory) / DECLARE Declaring Variables with the %TYPE Attribute Examples Declaring Variables with the %ROWTYPE Attribute Declare a variable with the type of a ROW of a table. And how do we access the fields in reserves_record? Creating a PL/SQL Record A record is a type of variable which we can define (like ‘struct’ in C or ‘object’ in Java) Creating a Cursor We create a Cursor when we want to go over a result of a query (like ResultSet in JDBC) Syntax Example: DECLARE cursor c is select * from sailors; sailorData sailors%ROWTYPE; BEGIN open c; fetch c into sailorData; Example DECLARE Pi constant NUMBER(8,7) := 3.1415926; area NUMBER(14,2); cursor rad_cursor is select * from RAD_VALS; rad_value rad_cursor%ROWTYPE; BEGIN open rad_cursor; fetch rad_cursor into rad_val; area:=pi*power(rad_val.radius,2); insert into AREAS values (rad_val.radius, area); close rad_cursor; END; / DECLARE … cursor rad_cursor is select * from RAD_VALS; rad_value rad_cursor%