14 创建过程和函数.pptVIP

What Are PL/SQL Program Units? Named PL/SQL blocks Three main categories: Procedures to perform actions Functions to compute a value Packages to bundle logically related procedures and functions Stored in the database or developed as an application subprogram. Subprogram Components Creating a Procedure: Syntax Create or replace PROCEDURE name [(parameter,...)] IS pl/sql_block; Procedural Parameter Modes Procedure or Function? Creating a Function: Syntax The %TYPE Attribute Declare a variable according to Another previously declared variable. A database column definition. Prefix %TYPE with The database table and column. The previously declared variable name. PL/SQL determines the datatype and size of the variable. The %TYPE Attribute: Examples Advantages of using the %TYPE attribute The datatype of the underlying database column may be unknown. The datatype of the underlying database column may change at runtime. PL/SQL RECORD Structure The %ROWTYPE Attribute Declare a variable according to a collection of columns in a database table or view. Prefix %ROWTYPE with the database table. Fields within the RECORD take their names and datatypes from the columns of the table or view. The %ROWTYPE Attribute: Advantages The number and datatypes of the underlying database columns may be unknown. The number and datatypes of the underlying database column may change at runtime. Useful when retrieving a row with the SELECT statement. Example SQL Commands in PL/SQL Extract a row of data from the database by using the SELECT command. Make changes to rows in the database by using DML commands. Control a transaction with COMMIT or ROLLBACK commands. Issue DDL and DCL statements with DBMS_SQL package. Retrieving Data: Syntax Retrieve data from the database with SELECT. INTO clause is required. Exactly one row must be returned. Full SELECT syntax is available. Retrieving Data: Example Retrieving Data: Example COMMIT and ROLLBACK Commands Initiate a transaction with the first DML