Designing PL/SQL Code. 2010, Oracle and/or its affiliates. All rights reserved. - PDF

Please download to get full document.

View again

of 35
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Information Report
Category:

Travel

Published:

Views: 13 | Pages: 35

Extension: PDF | Download: 0

Share
Related documents
Description
Designing PL/SQL Code Objectives After completing this lesson, you should be able to do the following: Identify guidelines for cursor design Use cursor variables Create subtypes based on the existing types
Transcript
Designing PL/SQL Code Objectives After completing this lesson, you should be able to do the following: Identify guidelines for cursor design Use cursor variables Create subtypes based on the existing types for an application 3-2 Lesson Agenda Identifying guidelines for cursor design Using cursor variables Creating subtypes based on existing types 3-3 Guidelines for Cursor Design Fetch into a record when fetching from a cursor. DECLARE CURSOR cur_cust IS SELECT customer_id, cust_last_name, cust_ FROM customers WHERE credit_limit = 1200; v_cust_record cur_cust%rowtype; BEGIN OPEN cur_cust; LOOP FETCH cur_cust INTO v_cust_record;... Benefit No individual variables declaration is needed. You can automatically use the structure of the SELECT column list. 3-4 Guidelines for Cursor Design Create cursors with parameters. CURSOR cur_cust (p_crd_limit NUMBER, p_acct_mgr NUMBER) IS SELECT customer_id, cust_last_name, cust_ FROM customers WHERE credit_limit = p_crd_limit AND account_mgr_id = p_acct_mgr; BEGIN OPEN cur_cust(p_crd_limit_in, p_acct_mgr_in);... CLOSE cur_cust;... OPEN cur_cust(v_credit_limit, 145);... END; Benefit Parameters increase the cursor s flexibility and reusability. Parameters help avoid scoping problems. 3-5 Guidelines for Cursor Design Reference implicit cursor attributes immediately after the SQL statement executes. BEGIN UPDATE customers SET credit_limit = p_credit_limit WHERE customer_id = p_cust_id; get_avg_order(p_cust_id); -- procedure call IF SQL%NOTFOUND THEN... Benefit Doing so ensures that you are dealing with the result of the correct SQL statement. 3-6 Guidelines for Cursor Design Simplify coding with cursor FOR loops. CREATE OR REPLACE PROCEDURE cust_pack (p_crd_limit_in NUMBER, p_acct_mgr_in NUMBER) IS v_credit_limit NUMBER := 1500; CURSOR cur_cust (p_crd_limit NUMBER, p_acct_mgr NUMBER) IS SELECT customer_id, cust_last_name, cust_ FROM customers WHERE credit_limit = p_crd_limit AND account_mgr_id = p_acct_mgr; BEGIN FOR cur_rec IN cur_cust (p_crd_limit_in, p_acct_mgr_in) LOOP -- implicit open and fetch... END LOOP; -- implicit close... END; Benefit Reduces the volume of code Automatically handles the open, fetch, and close operations, and defines a record type that matches the cursor definition 3-7 Guidelines for Cursor Design Close a cursor when it is no longer needed. Use column aliases in cursors for calculated columns fetched into records declared with %ROWTYPE. CREATE OR REPLACE PROCEDURE cust_list IS CURSOR cur_cust IS SELECT customer_id, cust_last_name, credit_limit*1.1 FROM customers; cust_record cur_cust%rowtype; BEGIN OPEN cur_cust; LOOP FETCH cur_cust INTO cust_record; DBMS_OUTPUT.PUT_LINE('Customer ' cust_record.cust_last_name ' wants credit ' cust_record.(credit_limit * 1.1)); EXIT WHEN cur_cust%notfound; END LOOP;... Use col. alias 3-8 Lesson Agenda Identifying guidelines for cursor design Using cursor variables Creating subtypes based on existing types 3-9 Cursor Variables: Overview Memory 1 Southlake, Texas San Francisco New Jersey Seattle, Washington Toronto 1800 REF CURSOR memory locator 3-10 Working with Cursor Variables Define and declare the cursor variable. Open the cursor variable. Fetch rows from the result set Close the cursor variable. 3-11 Strong Versus Weak REF CURSOR Variables Strong REF CURSOR: Is restrictive Specifies a RETURN type Associates only with type-compatible queries Is less error prone Weak REF CURSOR: Is nonrestrictive Associates with any query Is very flexible 3-12 Step 1: Defining a REF CURSOR Type Define a REF CURSOR type: TYPE ref_type_name IS REF CURSOR [RETURN return_type]; ref_type_name is a type specified in subsequent declarations. return_type represents a record type. RETURN keyword indicates a strong cursor. DECLARE TYPE rt_cust IS REF CURSOR RETURN customers%rowtype; Step 1: Declaring a Cursor Variable Declare a cursor variable of a cursor type: cursor_variable_name ref_type_name; cursor_variable_name is the name of the cursor variable. ref_type_name is the name of a REF CURSOR type. DECLARE TYPE rt_cust IS REF CURSOR RETURN customers%rowtype; cv_cust rt_cust; 3-14 Step 1: Declaring a REF CURSOR Return Type Options: Use %TYPE and %ROWTYPE. Specify a user-defined record in the RETURN clause. Declare the cursor variable as the formal parameter of a stored procedure or function. 3-15 Step 2: Opening a Cursor Variable Associate a cursor variable with a multiple-row SELECT statement. Execute the query. Identify the result set: OPEN cursor_variable_name FOR select_statement; cursor_variable_name is the name of the cursor variable. select_statement is the SQL SELECT statement. 3-16 Step 3: Fetching from a Cursor Variable Retrieve rows from the result set one at a time. FETCH cursor_variable_name INTO variable_name1 [,variable_name2,...] record_name; The return type of the cursor variable must be compatible with the variables named in the INTO clause of the FETCH statement. 3-18 Step 4: Closing a Cursor Variable Disable a cursor variable. The result set is undefined. CLOSE cursor_variable_name; Accessing the cursor variable after it is closed raises the INVALID_CURSOR predefined exception. 3-19 Passing Cursor Variables as Arguments You can pass query result sets among PL/SQL-stored subprograms and various clients. Pointer to the result set Access by a host variable on the client side 3-20 Passing Cursor Variables as Arguments 3-21 Using the SYS_REFCURSOR Predefined Type CREATE OR REPLACE PROCEDURE REFCUR (p_num IN NUMBER) IS refcur sys_refcursor; SYS_REFCURSOR is a built-in empno emp.empno%type; REF CURSOR type that allows ename emp.ename%type; any result set to be associated BEGIN with it. IF p_num = 1 THEN OPEN refcur FOR SELECT empno, ename FROM emp; DBMS_OUTPUT.PUT_LINE('Employee# Name'); DBMS_OUTPUT.PUT_LINE(' '); LOOP FETCH refcur INTO empno, ename; EXIT WHEN refcur%notfound; DBMS_OUTPUT.PUT_LINE(empno ' ' ename); END LOOP; ELSE Rules for Cursor Variables You cannot use cursor variables with remote subprograms on another server. You cannot use comparison operators to test cursor variables. You cannot assign a null value to cursor variables. You cannot use REF CURSOR types in CREATE TABLE or VIEW statements. Cursors and cursor variables are not interoperable. 3-25 Comparing Cursor Variables with Static Cursors Cursor variables have the following benefits: Are dynamic and ensure more flexibility Are not tied to a single SELECT statement Hold the value of a pointer Can reduce network traffic Give access to query work areas after a block completes 3-26 Lesson Agenda Identifying guidelines for cursor design Using Cursor Variables Creating subtypes based on existing types 3-27 Predefined PL/SQL Data Types Scalar Types BINARY_DOUBLE BINARY_FLOAT BINARY_INTEGER DEC DECIMAL DOUBLE PRECISION FLOAT INT INTEGER NATURAL NATURALN NUMBER NUMERIC PLS_INTEGER POSITIVE POSITIVEN REAL SIGNTYPE SMALLINT CHAR CHARACTER LONG LONG RAW NCHAR NVARCHAR2 RAW ROWID STRING UROWID VARCHAR VARCHAR2 BOOLEAN DATE INTERVAL TIMESTAMP Composite Types RECORD TABLE VARRAY Reference Types REF CURSOR REF object_type LOB Types BFILE BLOB CLOB NCLOB 3-28 Subtypes: Overview A subtype is a subset of an existing data type that may place a constraint on its base type. PL/SQL-predefined Scalar data type Subtype User-defined 3-29 Benefits of Subtypes Subtypes: Increase reliability Provide compatibility with ANSI/ISO and IBM types Promote reusability Improve readability Clarity Code self-documents 3-31 Declaring Subtypes Subtypes are defined in the declarative section of a PL/SQL block. SUBTYPE subtype_name IS base_type [(constraint)] [NOT NULL]; subtype_name is a type specifier used in subsequent declarations. base_type is any scalar or user-defined PL/SQL type. 3-32 Using Subtypes Define a variable that uses the subtype in the declarative section. identifier_name subtype_name; You can constrain a user-defined subtype when declaring variables of that type. identifier_name subtype_name(size); You can constrain a user-defined subtype when declaring the subtype. 3-33 Subtype Compatibility An unconstrained subtype is interchangeable with its base type. DECLARE SUBTYPE Accumulator IS NUMBER (4,2); v_amount accumulator; v_total NUMBER; BEGIN v_amount := 99.99; v_total := ; dbms_output.put_line('amount is: ' v_amount); dbms_output.put_line('total is: ' v_total); v_total := v_amount; dbms_output.put_line('this works too: ' v_total); -- v_amount := v_amount + 1; Will show value error END; / 3-34 Quiz Which of the following are true? a. Fetching into a multiple variables when fetching from a cursor gives you the advantage of automatic usage of the structure of the SELECT column list. b. Creating cursors with parameters helps in avoiding scoping problems. c. Close a cursor when it is no longer needed. d. All of the above 3-35 Quiz Strong REF CURSOR: a. Is nonrestrictive b. Specifies a RETURN type c. Associates only with type-compatible queries d. Is less error prone 3-36 Quiz A subtype is a subset of an existing data type that may place a constraint on its base type. a. True b. False 3-37 Summary In this lesson, you should have learned how to: Use guidelines for cursor design Declare, define, and use cursor variables Use subtypes as data types 3-38 Practice 3: Overview This practice covers the following topics: Determining the output of a PL/SQL block Improving the performance of a PL/SQL block Implementing subtypes Using cursor variables 3-39
Recommended
View more...
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks
SAVE OUR EARTH

We need your sign to support Project to invent "SMART AND CONTROLLABLE REFLECTIVE BALLOONS" to cover the Sun and Save Our Earth.

More details...

Sign Now!

We are very appreciated for your Prompt Action!

x