1 / 19

PL/SQL

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

winter-zamora
Télécharger la présentation

PL/SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PL/SQL introduction

  2. 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

  3. Character set • Capital letters A – Z, small letters a – z • Numbers 0 – 9 • Symbols: ( ) + - * / < > = ! ~ ^ : . @ % , “ # $ & _ | { } ? [ ] • Tab, space, carriage return

  4. 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

  5. Comments • 1 line comment • Use ‘- -’ Ex) - - beginning SELECT salary INTO sal FROM emp WHERE empno = emp#; --current salary Bonus := sal * 0.5 – bonus calculation

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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.

  12. 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

  13. 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;

  14. 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;

  15. 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;

  16. 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;

  17. 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

  18. 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

  19. Execution part (cont.3) • GOTO statement • [format] GOTO label; . . . <<label>> EX) BEGIN . . . GOTO insert_a_row; . . . <<insert_a_row>> INSERT INTO emp VALUES … END

More Related