Download
1 / 19
190 likes | 290 Views
PL/SQL. introduction. PL/SQL. Procedural Language extension to SQL Extended version of SQL Procedural programming Several SQL statements can be grouped as a block Lesser server calls. Character set. Capital letters A – Z, small letters a – z Numbers 0 – 9
E N D
PL/SQL introduction
PL/SQL • Procedural Language extension to SQL • Extended version of SQL • Procedural programming • Several SQL statements can be grouped as a block • Lesser server calls
Character set • Capital letters A – Z, small letters a – z • Numbers 0 – 9 • Symbols: ( ) + - * / < > = ! ~ ^ : . @ % , “ # $ & _ | { } ? [ ] • Tab, space, carriage return
Identifier • Used for constants, variables, names, etc. • Begins with a character • Follows with numbers, $, _, # Ex) money$$$tree, SN##, try_again Ex) wrong identifiers mine&yours, debit-amount, on/off, user id • Large and small characters are treated as the same Ex) the followings are identical lastname, LastName, LASTNAME
Comments • 1 line comment • Use ‘- -’ Ex) - - beginning SELECT salary INTO sal FROM emp WHERE empno = emp#; --current salary Bonus := sal * 0.5 – bonus calculation
Comments (cont.) • Multi-line comment • Use ‘/*’ and ‘*/’ Ex) /* bonus calculation : bonus rate is 100 % for good employees, Otherwise 50% */ IF rating > 80 THEN Bonus := sal * 1.0; ELSE Bonus := sal * 0.5; END IF
Data types • BINARY_INTEGER • Signed integer in –2,147,483,647 (= -231) ~ 2,147,483,647(= 231) • Subtypes of BINARY_INTEGER • NATURAL: 0~ 2,147,483,647(= 231) • POSITIVE: 1~ 2,147,483,647(= 231) • SIGNTYPE: -1, 0, 1
Data types (cont.1) • NUMBER • Stores fixed or floating point number • NUMBER(precision, scale) • Precision: upto 38 • Scale: -84~127 • Subtypes • DEC, DECIMAL, NUMERIC: fixed point number upto 38 digits • DOUBLE PRECISION, FLOAT: floating point number upto 10126 • REAL: floating point number upto 1063 • INTEGER, INT, SMALLINT: integer upto 38 digits
Data types (cont.2) • CHAR[(length)] • Fixed length string • Length upto 32,767 bytes • If length is omitted, 1 is assumed. • VARCHAR2(max length) • variable length string • Length upto 32,767 bytes
Data types (cont.3) • LONG • Variable length string • Length upto 32,760 bytes • LONG RAW • Variable length binary data & binary string • Length upto 32,760 bytes • RAW(max length) • Variable length binary data & binary string • Length upto 32,767 bytes
Data types (cont.4) • BOOLEAN • TRUE, FALSE, NULL values only • Logic calculation only • DATE • Stores date & time values • Values range: 4712/01/01 B.C. ~ 9999/12/31 A.D.
Structure of PL/SQL • Declaration part • Declares variables and constants • Can be omitted • Execution part • Contains SQL statements and control statements • Exception part • Contains error processing statements • Can be omitted
Declaration part • Declares variables and constants • Variable declaration • Begins with keyword ‘DECLARE’ • One variable declaration per one line Ex) DECLARE v_job VARCHAR2(9); v_count BINARY_INTEGER NOT NULL :=0; -- default value 0 v_hired_date DATE;
Declaration part (cont.1) • Constant declaration • use keyword ‘CONSTANT’ • Constant value cannot be changed Ex) DECLARE c_num CONSTANT NUMBER :=100; BEGIN c_num := 200; -- error END;
Execution part • IF statement • [format] IF condition THEN statement; [ELSEIF condition THEN statement; . . . ELSE statement;] END IF; EX) BEGIN IF sal > 3000000 THEN bonus := 1000000; ELSE bonus := 500000; END IF; END;
Execution part (cont.1) • WHILE LOOP statement • [format] WHILE condition LOOP statements END LOOP; EX) SQL> DECLARE j POSITIVE:=1; sumj NATURAL:=0; BEGIN WHILE j <=10 LOOP sumj :=sumj + j; j:= j+1; END LOOP; DBMS_OUTPUT.PUT_LINE(‘SUM : ‘ || sumj); END;
Exercise • Write a block that can calculate the sum of 1 to 50. • Hint • Modify the code in the previous slide • To finish entering PL/SQL code, enter ‘/’ • To execute the PL/SQL code, enter ‘run’ • To see the result from console enter • SQL>Set serveroutput on
Execution part (cont.2) • FOR LOOP • [format] FOR loop_variable IN [REVERSE] lower_limit..upper_limit LOOP statements END LOOP; EX) FOR j IN 1..10 LOOP EX) write a for loop that computes 1 + 2 + … + 100
Execution part (cont.3) • GOTO statement • [format] GOTO label; . . . <<label>> EX) BEGIN . . . GOTO insert_a_row; . . . <<insert_a_row>> INSERT INTO emp VALUES … END
