Oracle Database 11g PL SQL Part I - Omarr45.github.io

Oracle Database 11gPL SQL – Part I

OverviewWhat is PL/SQL? PL/SQL is an extension to SQL with design features ofprogramming languages. Data manipulation and query statements are included withinprocedural units of code.Benefits of PL/SQL: Modularize program development A procedural language with control structures Handle errors2

PL/SQL Block StructureDECLARE (Optional)Variables, constants, cursors, userdefined exceptionsBEGIN (Mandatory)SQL statementsPL/SQL control statementsEXCEPTION (Optional)Actions to perform when errors occurEND; (Mandatory)3

Block TypesAnonymousProcedureFunction[DECLARE]PROCEDURE ][EXCEPTION]FUNCTION nameRETURN datatypeISBEGIN--statementsRETURN value;[EXCEPTION]END;END;END;4

Developing a SimplePL/SQL Block5

Handling Variables in PL/SQL Declare and initializedeclaration section. Assign new values to variables within theexecutable section.6variableswithinthe

Declaring Variables and Constants: Syntaxidentifier [CONSTANT] datatype [NOT NULL][: DEFAULT expr];Guidelines Initialize constants designated as NOT NULL. Initialize identifiers by using the assignmentoperator (: ) or by the DEFAULT reserved word. Declare at most one identifier per line.7

Declaring Scalar Variables Have no internal components. Hold a single value. Base Types:–BINARY INTEGER–NUMBER [(precision, scale)]–CHAR [(maximum length)]–VARCHAR2(maximum length)–DATE–BOOLEAN8

Scalar VariableDeclarations: Examplesv genderCHAR(1);v countBINARY INTEGER : 0;v total salNUMBER(9) : 0;v order date DATE : SYSDATE 7;c tax rateCONSTANT NUMBER(3,2) : 8.25;v validBOOLEAN NOT NULL : TRUE;9

Operators in PL/SQL: Examples Set the value of a Boolean flag.v equal : (v n1 v n2);Validate an employee number if it contains a value.v valid: (v emp id IS NOT NULL);10

Nested Blocks and Variable Scope Statements can be nested wherever an executablestatement is allowed. Nested block becomes a statement. Identifier is visible in the regions in which you canreference the unqualified identifier.Exception section can contain nested blocks.Scope of an object is the region of the program thatcan refer to the object.–A block can look up to the enclosing block.–A block cannot look down to enclosed blocks.11

Nested Blocks andVariable Scope: ExampleDECLAREx BINARY INTEGER;BEGIN.DECLAREy NUMBER;BEGIN.END;.END;Scope of xScope of y12

Commenting CodeComment code by Prefixing the comment with two dashes (- -).Placing the comment between /* and */.Example.v sal NUMBER (9,2);BEGIN/* Compute the annual salary based on themonthly salary input from the user */v sal : v sal * 12;END;13

DataType Conversion14

Datatype Conversion Convert data to comparable datatypes. Mixed datatypes can result in an error and affectperformance. Conversion functions:–TO CHAR–TO DATE–TO NUMBER15

Datatype Conversion: Example This statement produces a compile error.v1 : USER SYSDATE; To correct the error, the TO CHAR conversionfunction is used.v1 : USER TO CHAR(SYSDATE);16

%TYPE Attribute17

The %TYPE Attribute Declare a variable according to– Another previously declared variable.– A database column definition. Prefix %TYPE with– The database table and column.– The previously declared variable name. PL/SQL determines the datatype and size of thevariable.18

The %TYPE Attribute: Examples.v last namev first namev balancev2 balance.emp.last name%TYPE;emp.first name%TYPE;NUMBER(7);v balance%TYPE : 10;Advantages of using the %TYPE attribute The datatype of the underlying database columnmay be unknown. The datatype of the underlying database columnmay change at runtime.19

%ROWTYPE Attribute20

The %ROWTYPE Attribute Declare a variable according to a collection of columnsin a database table or view. Prefix %ROWTYPE with the database table or view. Fields in the record take their names and data typesfrom the columns of the table or view. ept recorddept%ROWTYPE;21

Advantages of Using %ROWTYPE The number and data types of the underlyingdatabase columns may be unknown. The number and data types of the underlyingdatabase column may change at run time. The attribute is useful when retrieving a rowwith the SELECT * statement.22

Interacting with theDatabase23

SQL Commands in PL/SQL Extract a row of data from the database by usingthe SELECT command. Make changes to rows in the database by usingDML commands. ControlatransactionROLLBACK commands.24withCOMMITor

Retrieving Data: SyntaxRetrieve data from the database with SELECT.SELECTINTOFROMselect listvariable name tableWHEREcondition;record name INTO clause is required. Exactly one row must be returned. Full SELECT syntax is available.25

Retrieving Data: ExampleRetrieve the order date and the ship date for thespecified order.DECLAREv1 orders.date ordered%TYPE;v2 orders.date shipped%TYPE;BEGINSELECT date ordered, date shippedINTOv1, v2FROMordersWHERE order id 102;END;26

Retrieving Data: ExampleReturn the sum of the salaries for all employees in thespecified department.DECLAREv sum salary emp.salary%TYPE;BEGINSELECT SUM(salary)--group functionINTOv sum salaryFROMempWHEREdept id 30;END;27

Retrieving Data: ExampleRetrieve all information about the specified departmentDECLAREdept recorddept%ROWTYPE;BEGINSELECT*INTO dept record--PL/SQL RECORDFROM deptWHERE dept id 30;.END;28

SELECT Exceptions SELECT statements in PL/SQL must retrieve exactlyone row. If zero or more than one row is retrieved, anexception is raised. SELECT exceptions:–TOO MANY ROWS–NO DATA FOUND29

TOO MANY ROWS Exception: ExampleRetrieve the order for customer number 208.BEGINSELECT order idINTO v1FROM ordersWHERE customer id 208;end;TOO MANY ROWSCustomer 208 has several orders.30

NO DATA FOUND Exception: ExampleRetrieve the order for customer number 999.BEGINSELECT order idINTO v1FROM ordersWHERE customer id 999;end;NO DATA FOUND31

11 Nested Blocks and Variable Scope Statements can be nested wherever an executable statement is allowed. Nested block becomes a statement. Exception section can contain nested blocks. Scope of an object is the region of the program that can refer to the object. Identifier is visible in the regions in which you can reference the unqualified identifier.