1 / 71

Chapter Fifteen PL/SQL (Procedural Language)

Chapter Fifteen PL/SQL (Procedural Language). Objective: Introduction to PL/SQL Programming. PL/SQL. SQL (Structured Query Language) PL/SQL (Procedural Language): Oracle language for stored procedures Characteristics of PL/SQL Highly structured, accessible

duyen
Download Presentation

Chapter Fifteen PL/SQL (Procedural Language)

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. Chapter FifteenPL/SQL (Procedural Language) Objective: Introduction to PL/SQL Programming Chapter 15: PL/SQL

  2. PL/SQL • SQL (Structured Query Language) • PL/SQL (Procedural Language): Oracle language for stored procedures • Characteristics of PL/SQL • Highly structured, accessible • Standard language for Oracle development • Embedded language • Powerful error handling • Call Java class from PL/SQL Chapter 15: PL/SQL

  3. PL/SQL • Why PL/SQL: • Programs are portable: Applications written in Pl/SQL can run on any operating systems & platforms • Support for Object Oriented programming • Group of statements execute at one time • Modularized program development • Error handling • Supports • Declaration • Print Output • Define functions & Procedures. • Cursors Chapter 15: PL/SQL

  4. Example DECLARE S_GPA NUMBER(3,1); BEGIN SELECT GPA INTO S_GPA FROM Student WHERE ID = 1111 FOR UPDATE OF GPA; S_GPA:= S_GPA * 0.75; UPDATE Student SET GPA = S_GPA WHERE ID = 1111; COMMIT; END; Chapter 15: PL/SQL

  5. Scripting Languages • Programming Languages: • Java,C++,… • Scripting Languages: • PL/SQL, Perl, PHP, Unix Shell, Python, Javascript • Properties of scripting languages: • Interpreted • Do not require functions/procedures • Weakly typed Chapter 15: PL/SQL

  6. PL/SQL Symbols • Comments -- /* */ rem • BEGIN END (not { }) • AND OR (not &&, ||) • End Statement ; • Host Variable Indicator : Chapter 15: PL/SQL

  7. PL/SQL Symbols • Attribute Indicator % Cursor attributes: %ISOPEN Declaration Attributes: %ROWTYPE • Remote Access Indicator @ • Assignment Operator := Chapter 15: PL/SQL

  8. The Basics • Identifiers: constants, variables, exceptions, cursors, cursor variables, subprograms, packages • Reserved words • Literals: Numeric -10 Character ‘%’ String ‘Hello world’ Boolean TRUE Date ‘2002-11-12’ Date &Time ‘2002-11-12 12:01:02’ Chapter 15: PL/SQL

  9. Declarations Id [CONSTANT] datatype [NOT NULL] [DEFAULT | := Expres]; • Example DECLARE Birthday Date; Mid_Name Char:=‘M’; Temp SMALLINT:=0; Flag BOOLEAN:=FALSE; Pi CONSTANT NUMBER(9,7) :=3.1415927; P1 REAL NOT NULL :=3; P2 REAL:=3.15; P3 REAL:=P1*P2; BEGIN NULL; END; Chapter 15: PL/SQL

  10. Declaration DECLARE A INTEGER:=0; B INTEGER DEFAULT 10; C NUMBER(3,2) NOT NULL :=1.0; D POSITIVE:=0; E A%TYPE; My_ID Student.ID%TYPE; My_Rec Student%ROWTYPE; Chapter 15: PL/SQL

  11. Declaration DECLARE Name varchar2(40):= ‘Lory Latoo’; UpperName Name % TYPE:= UPPER(Name); LowerName Name %TYPE:= LOWER(Name); BEGIN --print the name DBMS_OUTPUT.PUT_LINE(Name || UpperName || LowerName); END; Chapter 15: PL/SQL

  12. Notes: • No forward references. • No multiple declarations: a,b,c NUMBER(4); • All variables with no assigned values are set to null. • No constraint is inherited from table declaration. Chapter 15: PL/SQL

  13. Identifiers • Names: User Identifiers: • 1-30 characters • Start with an alphabetic character • Followed by alphabet, digit, _ • Unique • Not reserved • Not case sensitive • $, # Chapter 15: PL/SQL

  14. Types of Variables • PL/SQL Variables • Scalar: • Composite: • Reference: • LOB: • Non-PL/SQL Variables • Host language variables (Bond Variables) Chapter 15: PL/SQL

  15. Data Types • Scalar Types: • Characters • Numbers • Boolean • Date • COMPOSITE TYPES: • RECORD • TABLE • VARRAY • Reference Types: • REF CURSOR • REF object_type • LOB Types: • BFILE • BLOB • CLOB • NCLOB Chapter 15: PL/SQL

  16. Data Types • Scalar Types • Character: CHAR [(Size [CHAR|BYTE])] CHAR CHAR(25) CHAR (25 BYTE) MaxSize is 32767 VARCHAR2 [(MaxSize [CHAR|BYTE])] (continued) Chapter 15: PL/SQL

  17. Predefined Data Types (Scalar Types) CHAR CHARACTER STRING VARCHAR VARCHAR2 LONG LONG RAW NCHAR NVARCHAR2 Chapter 15: PL/SQL

  18. Predefined Data Types (Scalar Types) • Number: BINARY_INTEGER (-2**31 To 2**31) NATURAL POSITIVE POSITIVEN SIGNTYPE PLS_INTEGER (-2**30 To 2**31) NUMBER (Precision, Scale) Number Number(5) DEC DECIMAL NUMERIC DOUBLE PRECISION FLOAT INT INTEGER SMALLINT REAL (continued) Chapter 15: PL/SQL

  19. Predefined Data Types (Scalar Types) • BOOLEAN TRUE, FALSE, NULL • DATE MyBirthdate DATE := DATE ‘2001-08-12’; (4712BC to DEC 31, 9999 AD) Today Date:= SYSDATE; Chapter 15: PL/SQL

  20. Predefined Data Types (Scalar Types) • TIMESTAMP Checkout TIMESTAMP := TIMESTAMP ‘2001-08-12 08:25:44’; DECLARE checkout TIMESTAMP; BEGIN checkout := ‘2001-08-12 08:25:44’;DBMS_OUTPUT.PUT_LINE( TO_CHAR(checkout)); END; Chapter 15: PL/SQL

  21. Timestamp With Time Zone • T1 TIMESTAMP with Time Zone:= ‘2003-01-01 9:00:00 –8:00’ • T2 TIMESTAMP with Time Zone:= ‘2003-01-01 12:00:00 –5:00’ (9am Pacific Standard Time = 12:00pm Eastern Standard Time) Function: CURRENT_TIMESTAMP( ) Chapter 15: PL/SQL

  22. Intervals • INTERVAL YEAR To MONTH • INTERVAL DAY To SECOND Chapter 15: PL/SQL

  23. INTERVALs DECLARE worked INTERVAL YEAR(3) TO MONTH; BEGIN worked := INTERVAL '121-3' YEAR TO MONTH; worked := '121-3'; -- implicit conversion from character type worked := INTERVAL '121' YEAR; -- Can specify just the years worked := INTERVAL '3' MONTH; -- Can specify just the months END; / Chapter 15: PL/SQL

  24. Intervals DECLARE Service INTERVAL YEAR TO MONTH; Yearofservice NUMBER; Monthofservice NUMBER; end_date DATA; start_date DATE; BEGIN SELECT L_date, S_date INTO end_date, start_date FROM faculty WHERE id=11111; Service := (end_date – start_date) YEAR To MONTH; Yearofservice := EXTRACT (YEAR FROM Service); Monthofservice := EXTRACT (MONTH FROM Service); DBMS_OUTPUT.PUT_LINE (Yearofservice || - || Monthofservice ); END; / Chapter 15: PL/SQL

  25. Predefined Data types (Scalar Types) • ROWID SELECT ROWID, Name FROM Student WHERE ID=1111; ROWID Name OOOOOOFFFBBBBBBRRR JOHN SMITH Object No. File No. Block No. Row No. UROWID : Logical position of a row ROWIDTOCHAR( ROWID) Chapter 15: PL/SQL

  26. Predefined Data types (Scalar Types) Example: SELECT ROWID FROM Student WHERE ROWIDTOCHAR( ROWID) LIKE ‘%SAAb%’; Chapter 15: PL/SQL

  27. Predefined Data types (Scalar Types) 6. ROWNUM SELECT Name, GPA FROM Student WHERE ROWNUM<5 ORDER BY GPA; Chapter 15: PL/SQL

  28. Predefined Data Types (Scalar Types) 7. %TYPE Name Student.name%Type; balance Number(6,2); Newbalance balance%Type; Mybalance balance%Type:=20; Chapter 15: PL/SQL

  29. Predefined Data types (Scalar Types) 8. User defined data types SUBTYPE ….. IS ….. DECLARE SUBTYPE MyNumber is NUMBER(5); Max MyNumber; BEGIN MyNumber := 11111; Chapter 15: PL/SQL

  30. Predefined Data Types • Composite Types (Chapter 16) RECORD TABLE VARRAY C. Reference Types (Chapter 18) REF CURSOR REF object_type Chapter 15: PL/SQL

  31. Predefined Data Types • LOB Types (Long OBject) (Max 4 Gig) Stores block of unstructured data BFILE: Binary File BLOB: Binary Object CLOB: Block of CHAR NCLOB: Block of N CHAR Chapter 15: PL/SQL

  32. Constants Total CONSTANT NUMBER(2) :=45 Total NUMBER(2) DEFAULT 45; • Syntax: Identifier [CONSTANT] datatype [NOT NULL] [:= expression]; Identifier datatype [NOT NULL] DEFAULT expression ; Chapter 15: PL/SQL

  33. General Guidelines • Statements can continue over several lines. • Lexical units can be separated by: • Space • Delimiters • Identifiers • Literals • Comments • Case Sensitive • PL/SQL: Terminate With / (continued) Chapter 15: PL/SQL

  34. General Guidelines • Functions in SQL that will not work with PL/SQL • DECODE • Group Functions Chapter 15: PL/SQL

  35. Block Structure DECLARE variables, constants, cursors BEGIN SQL & PL/SQL statements EXCEPTION --optional … END; / --to execute Chapter 15: PL/SQL

  36. Block • PL/SQL code is grouped into structures called blocks. • If a block of code does not have a name, it is called Anonymous Block. • A Block contains: • Declaration • Executable Commands • Exception Handling • Named Blocks: • Procedures • Functions Chapter 15: PL/SQL

  37. Program Constructs • Blocks can be used in: • Anonymous block • Application trigger • Database trigger • Stored Procedure or Function • Application Procedure or Function • Packaged Procedure or Function Chapter 15: PL/SQL

  38. Example DECLARE S_GPA NUMBER(3,1); BEGIN SELECT GPA INTO S_GPA FROM Student WHERE ID = 1111; IF S_GPA > 3 THEN INSERT INTO Tempfile1 VALUES (S_GPA); ELSE INSERT INTO Tempfile2 VALUES (S_GPA); END IF; COMMIT; END; Chapter 15: PL/SQL

  39. Expressions:Hierarchical Operations ( ) ** - + / * + - || = != <> <= >= IS NULL, LIKE, BETWEEN, IN NOT AND OR (Continued) Chapter 15: PL/SQL

  40. Expressions DECLARE A NUMBER (10); B CHAR(5); C CHAR(20); D BOOLEAN; BEGIN A := 2+3*4-2; B := ‘abcde’; (continued) Chapter 15: PL/SQL

  41. Expressions -- Character Operations C := B || ‘xy’ ; -- Concatenation -- Boolean Operations D := B= C; D := NOT TRUE; -- Like, Between, In END; Chapter 15: PL/SQL

  42. Non-PL/SQL Variables (Bind Variables) VARIABLE salary NUMBER DECLARE A NUMBER:=25000; BEGIN :salary := A; END; / Chapter 15: PL/SQL

  43. Non-PL/SQL Variables (Bind Variables) You can print value of the bind variable using PRINT SQL> PRINT salary SALARY ---------- 25000 Chapter 15: PL/SQL

  44. Interaction with The Oracle Server • Retrieve data from a database: SELECT List INTO Variable FROM Table WHERE Condition; (continued) Chapter 15: PL/SQL

  45. Interaction with The Oracle Server • Example DECLARE V_Name VARCHAR(10); V_ID NUMBER(6); V_GPA NUMBER(3,1); BEGIN SELECT Name, ID, GPA INTO V_Name, V_ID, V_GPA FROM Student WHERE ID = 1111; END; / (continued) Chapter 15: PL/SQL

  46. Interaction with The Oracle Server DECLARE V_Name Student.Name%TYPE; V_ID Student.ID%TYPE; V_GPA Student.GPA%TYPE; R_Data Student%ROWTYPE; Chapter 15: PL/SQL

  47. INSERT INTO DECLARE V_Name Student.Name%TYPE; V_ID Student.ID%TYPE; V_GPA Student.GPA%TYPE; BEGIN SELECT Name, ID, GPA INTO V_Name, V_ID, V_GPA FROM Student WHERE ID = 1111; INSERT INTO TempTable (T_Name, T_ID, T_ GPA) VALUES (V_Name, V_ID, V_GPA) ; END; / Chapter 15: PL/SQL

  48. UPDATE INTO DECLARE V_Name Student.Name%TYPE; V_ID Student.ID%TYPE; V_GPA Student.GPA%TYPE; BEGIN SELECT Name, ID, GPA INTO V_Name, V_ID, V_GPA FROM Student WHERE ID = 1111; UPDATE TempTable SET T_Name=V_Name WHERE T_ID=1111; END; / Chapter 15: PL/SQL

  49. DELETE FROM DECLARE V_Name Student.Name%TYPE; V_ID Student.ID%TYPE; V_GPA Student.GPA%TYPE; BEGIN SELECT Name, ID, GPA INTO V_Name, V_ID, V_GPA FROM Student WHERE ID = 1111; DELETE FROM TempTable WHERE T_Name = V_Name ; END; / Chapter 15: PL/SQL

  50. RETURNING DECLARE V_rowid ROWID; V_Name Student.Name%TYPE; V_ID Student.ID%TYPE; V_GPA Student.GPA%TYPE; BEGIN SELECT Name, ID, GPA INTO V_Name, V_ID, V_GPA FROM Student WHERE ID = 1111; INSERT INTO TempTable (T_Name, T_ID, T_ GPA) VALUES (V_Name, V_ID, V_GPA) RETURNING ROWID INTO V_rowid ; DBMS_OUTPUT.PUT_LANE(‘New record is inserted in row ’|| V_rowid) END; / Chapter 15: PL/SQL

More Related