Introduction

Introduction

Course Objectives • After completing this course, you should be able to do the following: • Design PL/SQL packages and program units that execute efficiently • Write code to interface with external applications and the operating system • Create PL/SQL applications that use collections • Write and tune PL/SQL code effectively to maximize performance • Implement a virtual private database with fine-grained access control • Write code to interface with large objects and use SecureFile LOBs • Perform code analysis to find program ambiguities, test, trace, and profile PL/SQL code

Oracle Complete Solution Clients Anybrowser Anymail client AnyFTP client Internet applications SQL Business logic and data Presentation and business logic Development tools PL/SQL System management Application servers Other Databases Java Network services

Lesson Agenda • Previewing the course agenda • Describing the development environments • Using SQL Developer • Using SQL*Plus • Identifying the tables, data, and tools used in this course

Course Agenda • Day 1 • Lesson 1: Introduction • Lesson 2: PL/SQL Review • Lesson 3: Designing PL/SQL Code • Lesson 4: Working with Collections • Day 2 • Lesson 4: Working with Collections • Lesson 5: Using Advanced Interface Methods • Lesson 6: Implementing Fine-Grained Access Control for VPD • Lesson 7: Manipulating Large Objects • Lesson 8: Administering SecureFile LOBs • Lesson 9: Performance and Tuning

Course Agenda • Day 3 • Lesson 10: Improving Performance with Caching • Lesson 11: Analyzing PL/SQL Code • Lesson 12: Profiling and Tracing PL/SQL Code • Lesson 13: Safeguarding Your Code Against SQL Injection Attacks

Development Environments: Overview • Introduction to SQL Developer • SQL*Plus SQL Developer

What Is Oracle SQL Developer? • Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. • You can connect to any target Oracle database schema by using the standard Oracle database authentication. • You can use either SQL Developer or SQL*Plus in this course. SQL Developer

Starting SQL Developer and Creating a Database Connection 1 3 2 4 5

Creating Schema Objects • You can create any schema object in SQL Developer by using one of the following methods: • Executing a SQL statement in the SQL worksheet • Using the context menu • Edit the objects by using an edit dialog box or one of the many context-sensitive menus • View the DDL for adjustments such as creating a new object or editing an existing schema object

Using the SQL Worksheet • Use the SQL worksheet to enter and execute SQL, PL/SQL, and SQL*Plus statements. • Specify any actions that can be processed by the database connection associated with the worksheet. Click the Open SQL Worksheet icon. Select SQL Worksheet from the Tools menu, or

Using the SQL Worksheet 8 2 4 6 5 9 7 3 1

Executing SQL Statements • Use the Enter SQL Statement box to enter single or multiple SQL statements. Use the Enter SQL Statement box to enter single or multiple SQL statements. View the results on the Script Output tabbed page.

Executing Saved Script Files: Method 1 Right-click in the SQL worksheet area, and then select Open File from the shortcut menu. To run the code, click the Run Script (F5) icon. Select (or navigate to) the script file that you want to open. Click Open.

Executing Saved SQL Scripts: Method 2 Use the @ command followed by the location and name of the file that you want to execute, and then click the Run Script icon. The output from the script is displayed on the Script Output tabbed page.

Creating an Anonymous Block • Create an anonymous block and display the output of the DBMS_OUTPUT package.

Editing the PL/SQL Code • Use the full-featured editor for PL/SQL program units.

Saving SQL Scripts Enter a file name and identify a location to save the file in, and then click Save. Click the Save icon to save your SQL statement to a file. The contents of the saved file are visible and editable in your SQL worksheet window.

Debugging Procedures and Functions • Use SQL Developer to debug PL/SQL functions and procedures. • Use the Compile for Debug option to perform a PL/SQL compilation so that the procedure can be debugged. • Use the Debug menu options to set breakpoints, and to perform step into and step over tasks.

Using SQL*Plus • You can invoke the SQL*Plus command-line interface from SQL Developer. • Close all SQL worksheets to enable the SQL*Plus menu option. Provide the location of the sqlplus.exe file only for the first time you invoke SQL*Plus.

Coding PL/SQL in SQL*Plus

Tables Used in This Course • The sample schemas used are: • Order Entry (OE) schema • Human Resources (HR) schema • Primarily, the OE schema is used. • The OE schema user can read data in the HR schema tables. • Appendix B contains more information about the sample schemas.

PROMOTIONS promo_id promo_name CUSTOMERS customer_id customer_first_name customer_last_name cust_address_typ phone_numbers nls_language nls_territory credit_limit cust_email account_mgr_id cust_geo_location date_of_birth marital_status gender income_level OE ORDERS order_id order_date order_mode customer_id order_status order_total sales_rep_id promotion_id ORDER_ITEMS order_id line_item_id product_id unit_price quantity PRODUCT_DESCRIPTIONS product_id language_id translated_name translated_description PRODUCT_ INFORMATION product_id product_name product_description category_id weight_class warranty_period supplier_id product_status list_price min_price catalog_url CATEGORIES_TAB category_id category_name category_description parent_category_id street_address postal_code city state_province country_id INVENTORIES product_id warehouse_id quantity_on_hand WAREHOUSES warehouse_id warehouse_spec warehouse_name location_id wh_geo_location The Order Entry Schema

The Human Resources Schema

Summary • In this lesson, you should have learned how to: • Describe the goals of the course • Identify the environments that can be used in this course • Describe the database schema and tables that are used in the course • List the available documentation and resources

Practice 1 Overview: Getting Started • This practice covers the following topics: • Reviewing the available SQL Developer resources • Starting SQL Developer and creating new database connections and browsing the HR, OE, and SH tables • Setting some SQL Developer preferences • Executing SQL statements and an anonymous PL/SQL block by using SQL worksheet • Accessing and bookmarking the Oracle Database 11g documentation and other useful Web sites

PL/SQL Programming Concepts: Review

Objectives • After completing this lesson, you should be able to do the following: • Describe PL/SQL basics • List restrictions on calling functions from SQL expressions • Identify how explicit cursors are processed • Handle exceptions • Use the raise_application_error procedure • Manage dependencies • Use Oracle-supplied packages

Lesson Agenda • Describing PL/SQL basics • Listing restrictions on calling functions from SQL expressions • Reviewing PL/SQL packages • Identifying how explicit cursors are processed • Handling exceptions • Using the raise_application_error procedure • Managing dependencies • Using Oracle-supplied packages

PL/SQL Block Structure <header> IS|AS BEGIN EXCEPTION END; DECLARE BEGIN EXCEPTION END; Anonymous PL/SQL block Stored program unit

Naming Conventions • Advantages of proper naming conventions: • Easier to read • Understandable • Gives information about the functionality • Easier to debug • Ensures consistency • Can improve performance

Procedures • A procedure is: • A named PL/SQL block that performs a sequence of actions and optionally returns a value or values • Stored in the database as a schema object • Used to promote reusability and maintainability CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode] datatype1, parameter2 [mode] datatype2, ...)] IS|AS [local_variable_declarations; …] BEGIN -- actions; END [procedure_name];

Procedure: Example CREATE OR REPLACE PROCEDURE get_avg_order (p_cust_id NUMBER, p_cust_last_name VARCHAR2, p_order_tot NUMBER) IS v_cust_ID customers.customer_id%type; v_cust_name customers.cust_last_name%type; v_avg_order NUMBER; BEGIN SELECT customers.customer_id, customers.cust_last_name,AVG(orders.order_total) INTO v_cust_id, v_cust_name, v_avg_order FROM CUSTOMERS, ORDERS WHERE customers.customer_id=orders.customer_id GROUP BY customers.customer_id, customers.cust_last_name; END; /

Functions • A function is: • A named block that must return a value • Stored in the database as a schema object • Called as part of an expression or used to provide a parameter value CREATE [OR REPLACE] FUNCTION function_name[(parameter1 [mode1] datatype1, ...)] RETURN datatype IS|AS [local_variable_declarations; …] BEGIN -- actions; RETURN expression; END [function_name];

Function: Example • Create the function: • Invoke the function as an expression or as a parameter value: CREATE OR REPLACE FUNCTION get_credit (v_id customers.customer_id%TYPE) RETURN NUMBER IS v_credit customers.credit_limit%TYPE := 0; BEGIN SELECT credit_limit INTO v_credit FROM customers WHERE customer_id = v_id; RETURN (v_credit); END get_credit; / EXECUTE dbms_output.put_line(get_credit(101))

Ways to Execute Functions • Invoke as part of a PL/SQL expression • Using a host variable to obtain the result: • Using a local variable to obtain the result: • Use as a parameter to another subprogram • Use in a SQL statement (subject to restrictions) VARIABLE v_credit NUMBER EXECUTE :v_credit := get_credit(101) DECLARE v_credit customers.credit_limit%type; BEGIN v_credit := get_credit(101); ... END; EXECUTE dbms_output.put_line(get_credit(101)) SELECT get_credit(customer_id) FROM customers;

Restrictions on Calling Functionsfrom SQL Expressions • User-defined functions that are callable from SQL expressions must: • Be stored in the database • Accept only IN parameters with valid SQL data types, not PL/SQL-specific types • Return valid SQL data types, not PL/SQL-specific types • When calling functions in SQL statements: • Parameters must be specified with positional notation • You must own the function or have the EXECUTE privilege

Restrictions on Calling Functions from SQL Expressions • Functions called from: • A SELECT statement cannot contain DML statements • An UPDATE or DELETE statement on a table T cannot query or contain DML on the same table T • SQL statements cannot end transactions (that is, cannot execute COMMIT or ROLLBACK operations) • Note: Calls to subprograms that break these restrictions are also not allowed in the function.

PL/SQL Packages: Review • PL/SQL packages: • Group logically related components: • PL/SQL types • Variables, data structures, and exceptions • Subprograms: procedures and functions • Consist of two parts: • A specification • A body • Enable the Oracle server to read multiple objects into memory simultaneously

Components of a PL/SQL Package Package specification variable Public Procedure A declaration; variable Procedure B definition … Procedure A definition BEGIN … END; Private variable Package body

Creating the Package Specification • Syntax: • The OR REPLACE option drops and re-creates the package specification. • Variables declared in the package specification are initialized to NULL by default. • All constructs declared in a package specification are visible to users who are granted privileges on the package. CREATE [OR REPLACE] PACKAGE package_name IS|AS public type and variable declarations subprogram specifications END [package_name];

Creating the Package Body • Syntax: • The OR REPLACE option drops and re-creates the package body. • Identifiers defined in the package body are private and not visible outside the package body. • All private constructs must be declared before they are referenced. • Public constructs are visible to the package body. CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS private type and variable declarations subprogram bodies [BEGIN initialization statements] END [package_name];

Cursor • A cursor is a pointer to the private memory area allocated by the Oracle server. • There are two types of cursors: • Implicit cursors: Created and managed internally by the Oracle server to process SQL statements • Explicit cursors: Explicitly declared by the programmer

Introduction

Introduction

Presentation Transcript

Introduction to introduction to introduction to … Optimization

INTRODUCTION/ INTRODUCTION

Introduction

INTRODUCTION

Introduction

Introduction