PLSQL基础(二).ppt

PL/SQL开发 基础篇（二） 本篇主要讲解如下语言点： PL/SQL Data Types PL/SQL Control Statements PL/SQL Collections and Records PL/SQL Data Types SQL Data Types PLS_INTEGER and BINARY_INTEGER Data Types User-Defined PL/SQL Subtypes SQL Data Types CHAR与VARCHAR2类型 x VARCHAR2(10 CHAR) := ‘abc ’; y CHAR(10 CHAR) := ‘abc ’ Result: x is ‘abc ’ Result: y is ‘abc ’ PLS_INTEGER and BINARY_INTEGER Data Types 对于整数的定义，使用PLS_INTEGER要优于NUMBER，原因是处理速度快。 DECLARE i PLS_INTEGER RANGE 10 .. 20; j PLS_INTEGER := 10; BEGIN i := j + j; i := j + j + j; -- ERROR END; User-Defined PL/SQL Subtypes SUBTYPE subtype_name IS base_type [ NOT NULL ]； DECLARE SUBTYPE MySalary IS NUMBER NOT NULL; salary MySalary := 1000; BEGIN NULL; END; PL/SQL Control Statements IF THEN 语句 IF condition THEN statements END IF; IF THEN ELSE语句 IF condition THEN statements ELSE else_statements END IF; IF THEN ELSIF 语句 IF condition_1 THEN statements_1 ELSIF condition_2 THEN statements_2 END IF; PL/SQL Control Statements 简单的case语句 DECLARE grade CHAR(1); BEGIN grade := B; CASE grade WHEN A THEN DBMS_OUTPUT.PUT_LINE(Excellent); WHEN B THEN DBMS_OUTPUT.PUT_LINE(Very Good); WHEN C THEN DBMS_OUTPUT.PUT_LINE(Good); WHEN D THEN DBMS_OUTPUT.PUT_LINE(Fair); WHEN F THEN DBMS_OUTPUT.PUT_LINE(Poor); ELSE DBMS_OUTPUT.PUT_LINE(No such grade); END CASE; END; Result: Very Good PL/SQL Control Statements 选择分支的case语句 DECLARE grade CHAR(1); BEGIN grade := B; CASE WHEN grade = A THEN DBMS_OUTPUT.PUT_LINE(Excellent); WHEN grade = B THEN DBMS_OUTPUT.PUT_LINE(Very Good); WHEN grade = C THEN DBMS_OUTPUT.PUT_LINE(Good); WHEN grade = D THEN DBMS_OUTPUT.PUT_LINE(Fair); WHEN grade = F THEN DBMS_OUTPUT.PUT_LINE(Poor); ELSE DBMS_OUTPUT.PUT_LINE(No such grade); END CASE; END; Result: Very Good PL/SQL Control Statements 基本的LOOP循环语句 [ lab