Basic knowledge of oracle Database PL/SQL

Basic knowledge of oracle Database PL/SQL 1) Overview: the programming language formed by oracle procedural expansion in the standard SQL language is a unique feature of oracle, languages that support application development. Www.2cto.com 2) Syntax: 1. basic Structure declare -- declare some variables, constants, user-defined data types, cursors, and so on -- this part is optional. If you do not need to write begin, the main program body exception, and the exception handling end; /2. character Set 1) uppercase/lowercase letters 2) Numbers 3) Non-displayed characters, tabs, spaces, and carriage returns 4) mathematical symbols +,-, *,/, <,>, =. 5) delimiter, including (), {}, [],?,!,;, :, ", '@, #, %, $, ^, &, Etc.; 3. data Type 1) NUMBER (P, S), which can be an integer or floating point NUMBER. P indicates the NUMBER of valid digits, and S indicates the NUMBER of digits on the right of the decimal point; the last two types of PLS_INTEGERBINARY_INTEGER can only store sub-types equivalent to Integers and numbers: DEC, DECIMAL, DOUBLE, PRECISION, INTEGER, INT, NUMERIC, REAL, SMALLINT... 2) character type VARCHAR2: stores a variable-length string, VARCHAR2 (maxlength) 32767 bytes, the maximum length of this type in the database is 4000 bytes; CHAR: stores a fixed-length string, CHAR (maxlength) 32767 bytes. in the database, the maximum length of this type is 2000 bytes LONG: LONG strings are stored, and the maximum length is 32760 bytes, in the database, the maximum length of this type is 2GBNCHARNVARCHAR2. The length of the last two types varies according to the character sets of different countries. 3) date DATA, 7 characters Section, Century, year, month, day, hour, minute, and second; 4) Boolean BOOLEAN5) type defined data TYPE (RECORD) type type_name is record (variable_name datatype [, variable_name datatype [,...); real_name type_name; record example: emp. eid % type declaretype myrecord is record (id varchar2 <10>, name varchar2 <10>); real_record myrecord; BEGINSELECT eid, ename INTO real_record FROM emp WHERE eid = '001 '; DBMS_OUTPUT.PUT_LINE (real_record.id | ',' | real_reacord.name); END; DECLARETYPE myreco Rd is record (id emp. eid % TYPE, name varchar2 <10>); real_record myrecord; BEGINSELECT eid, ename INTO real_record FROM emp WHERE eid = '001'; DBMS_OUTPUT.PUT_LINE (real_record.id | ', '| real_reacord.name); END; DECLAREmyrec emp % ROWTYPEBEGINSELECT * INTO myrec FROM emp WHERE eid = '001'; DBMS_OUTPUT.PUT_LINE (myrec. eid | ',' | myrec. ename); END 4. control Structure 1) IF statement IF condition expression 1 THEN statement sequence 1; [ELSIF condition expression 2 THEN statement sequence 2;] [ELSE statement Sequence Column 3;] end if; 2) CASE statement CASE detection expression WHEN expression 1 THEN statement sequence 1 ...... WHEN expression n THEN statement sequence n [ELSE other statement sequence] END; 3) NULL Value Check 4) LOOP structure LOOP... EXIT... ENDcontrol_var: = 0; loop if control_var> 5 then exit; end if; control_var: = control_var + 1; end loop; LOOP... exit when... ENDcontrol_var: = 0; loop exit when control_var> 5 control_var: = control_var + 1; end loop; WHILE... LOOP... ENDcontrol_var: = 0; WHILE control_var <= 5 LOOP control_var: = con Trol_val + 1; end loop;... IN... LOOP... ENDFRO control_var in 0... 5 loop null; end loop; GOTO lable ;... <mark>... IF no> 98050 then goto goto_mark ;... 5. expression 1) character expression 'hello' | 'World' | '! '= Hello world! 2) Boolean expression ANDORNOT