Notes Day 2 - Intro to PL/SQL @ De Anza College. Question - Where do I find the practice exercises? Review >> Introduction to PL/SQL Environment Benefits of PL/SQL Writing procedures and functions. Declaring Variables x INTEGER := 55; NOT NULL DEFAULT VALUE DATATYPES SCALAR COMPOSITE REFERENCE LOB BIND VARIABLES <-- Declared in PL/SQL Environment. Reviewed video lecture: Difference between Binary_integer and PLS_Integer What is the difference between Long RAW, LONG and VARCHAR2? Different types of Timestamp? <-- Illustrate the usage of timestamp datatypes using an example. usage of %TYPE keyword. How to create a script? Use notepad or your favorite text editor -- create your script and save it. Run it --> Simply copy and paste in your telnet session. Writing Executable Statements: DECLARE --DECLARE VARIABLES BEGIN --CODE IS WRITTEIN IN BETWEEN BEGIN AND EXCEPTION. EXCEPTION --EXCEPTION PART OF CODE IS EXECUTED IF THERE IS AN ISSUE WITH CODE UNDER BEGIN CLAUSE. END; WRITING COMMENTS IN PL/SQL /* MULTI-LINE COMMENT. */ --SINGLE LINE COMMENT WHEN WRITING PROGRAM BLOCKS - BEGIN x := y || CHR(10) || z; SQL functions -- number, character, data conversion, date etc. scope of a variables and nested blocks. Variable declared in outer block is accessible in the inner blocks - but not the other way around. x integer; begin declare y integer; begin y := y + x; end; x := x + y; -- error end; Operators All SQL operators Logical, arithmetic, like, || % etc, ** Logical NOT true false false true null null AND 1 0 N 1 1 0 N 0 0 0 0 N N 0 N OR 1 0 N 1 1 1 1 0 1 0 N N T N N All operations in PL/SQL are solved in Precedence and Associativity. Programming guidelines. Chapter 3 Using DML ops in PL/SQL:: Revision to Select statment -- to read values in bind variables or PL/SQL variables. DECLARE X emp.ename%TYPE; BEGIN SELECT col1, col2 INTO var1, var2 FROM WHERE .; END Use bind variables for data input and then associate/use in DML Statements: slide 3.11, 3.12.. Exam questions MERGE - INSERTS OR UPDATES DATA INTO A NEW TABLE USING DATA FROM AN EXISTING TABLE USING SOME CONDITIONAL LOGIC. MERGE INTO <TABLENAME> USING <SRC TABLE NAME> ON < CONDITION USING COLUMNS> WHEN MATCHED THEN ....INSERT/UPDATE STATEMENT WHEN NOT MATCHED THEN ....INSERT/UPDATE STATEMENT END; / CURSOR ATTRIBUTES (IMPLICIT AND EXPLICIT CURSOR TYPES) ROWCOUNT FOUND NOTFOUND ISOPEN WORK WITH INSERT/UPDATE -- COMMIT/ROLLBACK. WORKING WITH CONTROL STRUCTURES IF, ELSE AND ELSIF IF CONDITION ACTION ELSE ACTION END IF; IF CONDITION1 ACTION1 ELSIF CONDITION2 ACTION2 ELSIF CONDITION3 ACTION3 ELSE ACTION4 END IF; CASE <varname> WHEN <VAL> THEN ACTION1; WHEN <VAL2> THEN ACTION2; WHEN <VAL3> THEN ACTION3; ELSE <DEFAULT ACTION> END; LOOPING CONSTRUCTS LOOP EXIT (WHEN CONDITION); END LOOP; WHILE (CONDITION) LOOP --PRE-TEST LOOP. END LOOP; FOR COUNTER IN LOW..HIGH LOOP END LOOP; FOR i in 1..22 loop --statements. end loop; EXIT WHEN CONDITION.. ACTION - WATCH VIDEO LECTURE FOR COMPOSITE DATATYPES - |
Announcements >