教學講稿oracle官方les

1、Declaring PL/SQL VariablesObjectivesAfter completing this lesson, you should be able to do the following:Recognize valid and invalid identifiersList the uses of variablesDeclare and initialize variablesList and describe various data typesIdentify the benefits of using the %TYPE attributeDeclare, use

2、, and print bind variablesUse of VariablesVariables can be used for:Temporary storage of dataManipulation of stored valuesReusabilitySELECT first_name, department_id INTO v_fname,v_deptnoFROM v_fnamev_deptnoJennifer10Requirements for Variable NamesA variable name:Must start with a letter Can include

3、 letters or numbersCan include special characters (such as $, _, and # )Must contain no more than 30 charactersMust not include reserved wordsHandling Variables in PL/SQLVariables are:Declared and initialized in the declarative sectionUsed and assigned new values in the executable sectionPassed as p

4、arameters to PL/SQL subprogramsUsed to hold the output of a PL/SQL subprogramDeclaring and Initializing PL/SQL VariablesSyntax:Examples:identifier CONSTANT datatype NOT NULL := | DEFAULT expr;DECLARE v_hiredateDATE; v_deptnoNUMBER(2) NOT NULL := 10; v_location VARCHAR2(13) := Atlanta; mCONSTANT NUMB

5、ER := 1400; Declaring and Initializing PL/SQL VariablesDECLARE v_myName VARCHAR2(20);BEGIN DBMS_OUTPUT.PUT_LINE(My name is: | v_myName); v_myName := John; DBMS_OUTPUT.PUT_LINE(My name is: | v_myName);END;/DECLARE v_myName VARCHAR2(20):= John;BEGIN v_myName := Steven; DBMS_OUTPUT.PUT_LINE(My name is:

6、 | v_myName);END; /12Delimiters in String LiteralsDECLARE v_event VARCHAR2(15);BEGIN v_event := q!Fathers day!; DBMS_OUTPUT.PUT_LINE(3rd Sunday in June is : | v_event ); v_event := qMothers day; DBMS_OUTPUT.PUT_LINE(2nd Sunday in May is : | v_event );END;/Types of VariablesPL/SQL variables:ScalarCom

7、positeReferenceLarge object (LOB)Non-PL/SQL variables: Bind variablesTRUETypes of Variables25-JAN-01Atlanta256120.08Snow WhiteLong, long ago, in a land far, far away, there lived a princess called Snow White. . . Guidelines for Declaring and InitializingPL/SQL VariablesFollow naming conventions.Use

8、meaningful identifiers for variables.Initialize variables designated as NOT NULL and CONSTANT.Initialize variables with the assignment operator (:=) or the DEFAULT keyword:Declare one identifier per line for better readability and code maintenance.v_myName VARCHAR2(20):=John;v_myName VARCHAR2(20) DE

9、FAULT John;Guidelines for Declaring PL/SQL VariablesAvoid using column names as identifiers.Use the NOT NULL constraint when the variable must hold a value.DECLARE employee_idNUMBER(6);BEGIN SELECT employee_id INTOemployee_id FROMemployees WHERE last_name = Kochhar;END;/Scalar Data TypesHold a singl

10、e valueHave no internal componentsAtlantaTRUE25-JAN-01256120.08The soul of the lazy man desires, and he has nothing; but the soul of the diligent shall be made rich. Base Scalar Data TypesCHAR (maximum_length)VARCHAR2 (maximum_length)NUMBER (precision, scale)BINARY_INTEGERPLS_INTEGERBOOLEANBINARY_FL

11、OATBINARY_DOUBLEBase Scalar Data TypesDATETIMESTAMPTIMESTAMP WITH TIME ZONETIMESTAMP WITH LOCAL TIME ZONEINTERVAL YEAR TO MONTHINTERVAL DAY TO SECONDDeclaring Scalar Variables Examples:DECLARE v_emp_jobVARCHAR2(9); v_count_loop BINARY_INTEGER := 0; v_dept_total_salNUMBER(9,2) := 0; v_orderdate DATE

12、:= SYSDATE + 7; c_tax_rateCONSTANT NUMBER(3,2) := 8.25; v_valid BOOLEAN NOT NULL := TRUE; . %TYPE AttributeIs used to declare a variable according to: A database column definitionAnother declared variableIs prefixed with:The database table and columnThe name of the declared variableDeclaring Variabl

13、es with the %TYPE AttributeSyntaxExamples. emp_lname employees.last_name%TYPE;.identifiertable.column_name%TYPE;. balance NUMBER(7,2); min_balance balance%TYPE := 1000;.Declaring Boolean VariablesOnly the TRUE, FALSE, and NULL values can be assigned to a Boolean variable.Conditional expressions use

14、the logical operators AND and OR and the unary operator NOT to check the variable values. The variables always yield TRUE, FALSE, or NULL.Arithmetic, character, and date expressions can be used to return a Boolean value.Bind VariablesBind variables are:Created in the environment Also called host var

15、iablesCreated with the VARIABLE keywordUsed in SQL statements and PL/SQL blocksAccessed even after the PL/SQL block is executedReferenced with a preceding colonPrinting Bind VariablesExample:VARIABLE b_emp_salary NUMBERBEGIN SELECT salary INTO :b_emp_salary FROM employees WHERE employee_id = 178; EN

16、D;/PRINT b_emp_salarySELECT first_name, last_name FROM employees WHERE salary=:b_emp_salary;Printing Bind VariablesExample:VARIABLE b_emp_salary NUMBERSET AUTOPRINT ONDECLARE v_empno NUMBER(6):=&empno;BEGIN SELECT salary INTO :b_emp_salary FROM employees WHERE employee_id = v_empno; END;7000Output:L

17、OB Data Type VariablesBook(CLOB)Photo(BLOB)Movie(BFILE)NCLOBComposite Data Types TRUE 23-DEC-98ATLANTA 15000 22345 312 43456 1SMITH 2JONES 3NANCY 4TIMPL/SQL table structurePL/SQL table structurePLS_INTEGER VARCHAR2PLS_INTEGER NUMBERSummaryIn this lesson, you should have learned how to: Recognize valid and invalid identifiersDeclare variables in the declarative section of a PL/SQL blockInitialize variables and use them in the executable sectionDifferentiate between scalar and composite data typesUse the %TYPE attributeUse bind variablesPra