Oracle Database 11g PL SQL Part I -

1y ago
2.86 MB
31 Pages
Last View : 6m ago
Last Download : 23d ago
Upload by : Luis Waller

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 ordered%TYPE;v2 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.

Related Documents:

Oracle Database 11g SQL Fundamentals . – Refer to “ Installation –Database.ppt” for database installation. Structured Query Language (SQL) 4 SQL, PL/SQL, and SQL*PLUS

Oracle Database 11g Pl a n Ba sel ine Plan History HJ GB 4.Upgrade to 11g 5.Migrate stored outlines into SPM Figure 4 Bulk load the SMB after upgrade using Stored outlines. From the Cursor Cache Starting in Oracle Database 11g it is possible to load plans for statements directly from the cursor cache into the SQL Management Base.File Size: 417KBPage Count: 26

Oracle Database 11g Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions, Second Edition Dear Reader, Expert Oracle Database Architecture, 2nd Edition is a book that explores and defines the Oracle d

Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,

Oracle TIGHT / Oracle Database 11g & MySQL 5.6 Developer Handbook / Michael McLaughlin / 885-8 Oracle TIGHT / Oracle Database 11g & MySQL 5.6 Developer Handbook / Michael McLaughlin / 885-8 Chapter 1: Architecture 5 The client software component provides an interactive and batch interface that lets you and your programs interact with the database.

Oracle 11g New Features for Administrators . Summary Sheets . Version. 2.3 . . Oracle Database 11g New Features for DBAs and Developers, by Sam R. Alapati and Charles Kim, Apress, ISBN: 978-1-59059-910-5 . 2 . Book . Oracle Database 11g New Features by Rober G. Freeman, Oracle Press . 3 .

PeopleSoft Oracle JD Edwards Oracle Siebel Oracle Xtra Large Model Payroll E-Business Suite Oracle Middleware Performance Oracle Database JDE Enterprise One 9.1 Oracle VM 2.2 2,000 Users TPC-C Oracle 11g C240 M3 TPC-C Oracle DB 11g & OEL 1,244,550 OPTS/Sec C250 M2 Oracle E-Business Suite M

Oracle Database 11g reinvents files in the database . from within the database Produce PDF files from Result Implement database-resident Content Management System HTTP Call-Out . New in 11g release PL/SQL, Oracle Call Interface (OCI) and Pro*C Support.