PLSQL编程（PLSQL programming）.doc

PLSQL编程（PLSQL programming） PL/SQL program block structure (a PL/SQL block can be an anonymous block, process, or function) : DECLARE - optional / * variable, cursor, user-defined special type * / The BEGIN - must / * * / SQL statements / * * / PL/SQL statements The EXCEPTION - optional / * processing actions when the error occurs * / The END; - must be Example: DECLARE V_variable VARCHAR2 (5); The BEGIN The SELECT column_name INTO v_variabe The FROM table_name; The EXCEPTION The WHEN exception_name THEN ... The END; PL/SQL data type PL/SQL variables and constants: PL/SQL variables: Scalar: stores a single value, and the generic data types used by scalar variables include characters, Numbers, dates, and Boolean types Compound: store multiple values Reference: reference external variables Example: SQL VARIABLE emp_num NUMBER SQL the BEGIN SELECT COUNT (*) INTO: emp_num 3 the FROM emp; 4 the END; 5 / The PL/SQL procedure has been successfully completed SQL PRINT emp_num EMP_NUM -- -- -- -- -- -- -- -- -- -- -- -- -- 13 LOB (large object) : CLOB (recipe), BLOB (photo), BFILE (movie), NCLOB External variables: Bind variables and pivot variables Note: variables can be in DECLARE and BEGIN... The END section assigns the value, but the constant can only be assigned to the DECLARE section. = the assignment symbol for PL/SQL. Scope: for a single block, defined the scope of the variable is the block, the parent block variables defined in the scope of the block itself is father, and the nested children. The variables defined in the subblock are only the subblocks themselves that are part of its scope. Each line in PL/SQL declares an identifier at most. Two variables in different blocks can be of the same name. The name of the variable (identifier) cannot be the same name as the column in the block. Initialization and keywords of variables: : = assignment operator The DEFAULT DEFAULT NOT NULL is NOT empty Basic scalar type VARCHAR2 NUMBER The DATE CHAR LONG LONG RAW BOOLEAN B