710 likes | 964 Views
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
E N D
Chapter FifteenPL/SQL (Procedural Language) Objective: Introduction to PL/SQL Programming Chapter 15: PL/SQL
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
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
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
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
PL/SQL Symbols • Comments -- /* */ rem • BEGIN END (not { }) • AND OR (not &&, ||) • End Statement ; • Host Variable Indicator : Chapter 15: PL/SQL
PL/SQL Symbols • Attribute Indicator % Cursor attributes: %ISOPEN Declaration Attributes: %ROWTYPE • Remote Access Indicator @ • Assignment Operator := Chapter 15: PL/SQL
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
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
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
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
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
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
Types of Variables • PL/SQL Variables • Scalar: • Composite: • Reference: • LOB: • Non-PL/SQL Variables • Host language variables (Bond Variables) Chapter 15: PL/SQL
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
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
Predefined Data Types (Scalar Types) CHAR CHARACTER STRING VARCHAR VARCHAR2 LONG LONG RAW NCHAR NVARCHAR2 Chapter 15: PL/SQL
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
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
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
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
Intervals • INTERVAL YEAR To MONTH • INTERVAL DAY To SECOND Chapter 15: PL/SQL
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
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
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
Predefined Data types (Scalar Types) Example: SELECT ROWID FROM Student WHERE ROWIDTOCHAR( ROWID) LIKE ‘%SAAb%’; Chapter 15: PL/SQL
Predefined Data types (Scalar Types) 6. ROWNUM SELECT Name, GPA FROM Student WHERE ROWNUM<5 ORDER BY GPA; Chapter 15: PL/SQL
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
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
Predefined Data Types • Composite Types (Chapter 16) RECORD TABLE VARRAY C. Reference Types (Chapter 18) REF CURSOR REF object_type Chapter 15: PL/SQL
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
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
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
General Guidelines • Functions in SQL that will not work with PL/SQL • DECODE • Group Functions Chapter 15: PL/SQL
Block Structure DECLARE variables, constants, cursors BEGIN SQL & PL/SQL statements EXCEPTION --optional … END; / --to execute Chapter 15: PL/SQL
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
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
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
Expressions:Hierarchical Operations ( ) ** - + / * + - || = != <> <= >= IS NULL, LIKE, BETWEEN, IN NOT AND OR (Continued) Chapter 15: PL/SQL
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
Expressions -- Character Operations C := B || ‘xy’ ; -- Concatenation -- Boolean Operations D := B= C; D := NOT TRUE; -- Like, Between, In END; Chapter 15: PL/SQL
Non-PL/SQL Variables (Bind Variables) VARIABLE salary NUMBER DECLARE A NUMBER:=25000; BEGIN :salary := A; END; / Chapter 15: PL/SQL
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
Interaction with The Oracle Server • Retrieve data from a database: SELECT List INTO Variable FROM Table WHERE Condition; (continued) Chapter 15: PL/SQL
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
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
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
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
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
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