Announcements‎ > ‎

### Whiteboard - July 9th 2012 (Unedited)

posted Jul 9, 2012, 9:23 PM by Sukhjit Singh
 NotesDay 2 - Intro to PL/SQL @ De Anza College.Question - Where do I find the practice exercises?Review >>Introduction to PL/SQL EnvironmentBenefits of PL/SQLWriting procedures and functions.Declaring Variables x INTEGER := 55; NOT NULL  DEFAULT VALUEDATATYPES SCALAR COMPOSITE REFERENCE LOBBIND VARIABLES <-- Declared in PL/SQL Environment.Reviewed video lecture:Difference between Binary_integer and PLS_IntegerWhat 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 VARIABLESBEGIN --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 COMMENTWHEN 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 3Using 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 .;ENDUse 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 USING 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)ROWCOUNTFOUNDNOTFOUNDISOPENWORK WITH INSERT/UPDATE -- COMMIT/ROLLBACK.WORKING WITH CONTROL STRUCTURESIF, ELSE AND ELSIF IF CONDITION ACTION ELSE ACTION END IF; IF CONDITION1 ACTION1 ELSIF CONDITION2 ACTION2 ELSIF CONDITION3 ACTION3 ELSE ACTION4 END IF; CASE WHEN THEN ACTION1; WHEN THEN ACTION2; WHEN THEN ACTION3; ELSE END;LOOPING CONSTRUCTSLOOP EXIT (WHEN CONDITION);END LOOP;WHILE (CONDITION) LOOP --PRE-TEST LOOP.END LOOP;FOR COUNTER IN LOW..HIGH LOOPEND LOOP; FOR i in 1..22 loop --statements. end loop;EXIT WHEN CONDITION..ACTION - WATCH VIDEO LECTURE FOR COMPOSITE DATATYPES -