2 Day Developer's Guide - Oracle Help Center

2y ago
58 Views
6 Downloads
1.74 MB
295 Pages
Last View : Today
Last Download : 2m ago
Upload by : Anton Mixon
Transcription

Oracle Database2 Day Developer's Guide19cE94801-01January 2019

Oracle Database 2 Day Developer's Guide, 19cE94801-01Copyright 1996, 2019, Oracle and/or its affiliates. All rights reserved.Primary Author: Chuck MurrayContributors: Eric Belden, Bjorn Engsig, Nancy Greenberg, Pat Huey, Christopher Jones, Sharon Kennedy,Thomas Kyte, Simon Law, Bryn Llewellen, Sheila MooreThis software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify,license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means.Reverse engineering, disassembly, or decompilation of this software, unless required by law forinteroperability, is prohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou find any errors, please report them to us in writing.If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it onbehalf of the U.S. Government, then the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software,any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are"commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agencyspecific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of theprograms, including any operating system, integrated software, any programs installed on the hardware,and/or documentation, shall be subject to license terms and license restrictions applicable to the programs.No other rights are granted to the U.S. Government.This software or hardware is developed for general use in a variety of information management applications.It is not developed or intended for use in any inherently dangerous applications, including applications thatmay create a risk of personal injury. If you use this software or hardware in dangerous applications, then youshall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure itssafe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of thissoftware or hardware in dangerous applications.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks oftheir respective owners.Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks areused under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron,the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced MicroDevices. UNIX is a registered trademark of The Open Group.This software or hardware and documentation may provide access to or information about content, products,and services from third parties. Oracle Corporation and its affiliates are not responsible for and expresslydisclaim all warranties of any kind with respect to third-party content, products, and services unless otherwiseset forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not beresponsible for any loss, costs, or damages incurred due to your access to or use of third-party content,products, or services, except as set forth in an applicable agreement between you and Oracle.

ContentsPreface1AudiencexiiDocumentation AccessibilityxiiRelated DocumentsxiiiConventionsxiiiIntroduction to 2 Day Oracle Database Development1.1About This Document1-11.2About Oracle Database1-21.321.2.1About Schema Objects1-21.2.2About Oracle Database Access1-41.2.2.1About SQL*Plus1-41.2.2.2About SQL Developer1-51.2.2.3About Structured Query Language (SQL)1-61.2.2.4About Procedural Language/SQL (PL/SQL)1-61.2.2.5About Other Client Programs, Languages, and DevelopmentTools1-7About Sample Schema HR1-12Connecting to Oracle Database and Exploring It2.1Connecting to Oracle Database from SQL*Plus2-12.2Connecting to Oracle Database from SQL Developer2-32.3Connecting to Oracle Database as User HR2-42.42.52.3.1Unlocking the HR Account2-52.3.2Connecting to Oracle Database as User HR from SQL*Plus2-52.3.3Connecting to Oracle Database as User HR from SQL Developer2-6Exploring Oracle Database with SQL*Plus2-72.4.1Viewing HR Schema Objects with SQL*Plus2-72.4.2Viewing EMPLOYEES Table Properties and Data with SQL*Plus2-8Exploring Oracle Database with SQL Developer2.5.1Tutorial: Viewing HR Schema Objects with SQL Developer2-102-10iii

2.5.22.6Tutorial: Viewing EMPLOYEES Table Properties and Data with SQLDeveloperSelecting Table DataAbout Queries2-132.6.2Running Queries in SQL Developer2-132.6.3Tutorial: Selecting All Columns of a Table2-142.6.4Tutorial: Selecting Specific Columns of a Table2-152.6.5Displaying Selected Columns Under New Headings2-162.6.6Selecting Data that Satisfies Specified Conditions2-172.6.7Sorting Selected Data2-192.6.8Selecting Data from Multiple Tables2-202.6.9Using Operators and Functions in Queries2-212.6.9.1Using Arithmetic Operators in Queries2-232.6.9.2Using Numeric Functions in Queries2-232.6.9.3Using the Concatenation Operator in Queries2-242.6.9.4Using Character Functions in Queries2-252.6.9.5Using Datetime Functions in Queries2-262.6.9.6Using Conversion Functions in Queries2-272.6.9.7Using Aggregate Functions in Queries2-292.6.9.8Using NULL-Related Functions in Queries2-312.6.9.9Using CASE Expressions in Queries2-32Using the DECODE Function in Queries2-34About DML Statements and Transactions3.142-122.6.12.6.9.1032-11About Data Manipulation Language (DML) Statements3-13.1.1About the INSERT Statement3-23.1.2About the UPDATE Statement3-43.1.3About the DELETE Statement3-53.2About Transaction Control Statements3-63.3Committing Transactions3-73.4Rolling Back Transactions3-83.5Setting Savepoints in Transactions3-11Creating and Managing Schema Objects4.1About Data Definition Language (DDL) Statements4-14.2Creating and Managing Tables4-24.2.1About SQL Data Types4-34.2.2Creating Tables4-34.2.2.1Tutorial: Creating a Table with the Create Table Tool4-44.2.2.2Creating Tables with the CREATE TABLE Statement4-5iv

4.2.3About Constraints4-64.2.3.2Tutorial: Adding Constraints to Existing Tables4-84.2.4Tutorial: Adding Rows to Tables with the Insert Row Tool4-124.2.5Tutorial: Changing Data in Tables in the Data Pane4-134.2.6Tutorial: Deleting Rows from Tables with the Delete Selected Row(s)Tool4-14Managing Indexes4-154.2.7.1Tutorial: Adding an Index with the Create Index Tool4-164.2.7.2Tutorial: Changing an Index with the Edit Index Tool4-174.2.7.3Tutorial: Dropping an Index4-174.2.84.55Dropping TablesCreating and Managing Views4.3.14.44-64.2.3.14.2.74.3Ensuring Data Integrity in TablesCreating Views4-184-194-194.3.1.1Tutorial: Creating a View with the Create View Tool4-204.3.1.2Creating Views with the CREATE VIEW Statement4-214.3.2Changing Queries in Views4-214.3.3Tutorial: Changing View Names with the Rename Tool4-224.3.4Dropping a View4-22Creating and Managing Sequences4-234.4.1Tutorial: Creating a Sequence4-244.4.2Dropping Sequences4-25Creating and Managing Synonyms4-264.5.1Creating Synonyms4-264.5.2Dropping Synonyms4-27Developing Stored Subprograms and Packages5.1About Stored Subprograms5-25.2About Packages5-25.3About PL/SQL Identifiers5-35.4About PL/SQL Data Types5-45.5Creating and Managing Standalone Subprograms5-55.65.5.1About Subprogram Structure5-55.5.2Tutorial: Creating a Standalone Procedure5-75.5.3Tutorial: Creating a Standalone Function5-95.5.4Changing Standalone Subprograms5-105.5.5Tutorial: Testing a Standalone Function5-115.5.6Dropping Standalone Subprograms5-12Creating and Managing Packages5-125.6.1About Package Structure5-135.6.2Tutorial: Creating a Package Specification5-14v

5.6.3Tutorial: Changing a Package Specification5-155.6.4Tutorial: Creating a Package Body5-165.6.5Dropping a Package5-175.7Declaring and Assigning Values to Variables and Constants5-175.7.1Tutorial: Declaring Variables and Constants in a Subprogram5-185.7.2Ensuring that Variables, Constants, and Parameters Have Correct DataTypes5-205.7.3Tutorial: Changing Declarations to Use the %TYPE Attribute5-215.7.4Assigning Values to Variables5-225.85.7.4.1Assigning Values to Variables with the Assignment Operator5-235.7.4.2Assigning Values to Variables with the SELECT INTO Statement5-24Controlling Program Flow5-255.8.1About Control Statements5-255.8.2Using the IF Statement5-265.8.3Using the CASE Statement5-275.8.4Using the FOR LOOP Statement5-295.8.5Using the WHILE LOOP Statement5-305.8.6Using the Basic LOOP and EXIT WHEN Statements5-325.9Using Records and Cursors5-335.9.1About Records5-345.9.2Tutorial: Declaring a RECORD Type5-355.9.3Tutorial: Creating and Invoking a Subprogram with a Record Parameter5-365.9.4About Cursors5-385.9.5Using a Declared Cursor to Retrieve Result Set Rows One at a Time5-405.9.6Tutorial: Using a Declared Cursor to Retrieve Result Set Rows One at aTime5-415.9.7About Cursor Variables5-425.9.8Using a Cursor Variable to Retrieve Result Set Rows One at a Time5-425.9.9Tutorial: Using a Cursor Variable to Retrieve Result Set Rows One at aTime5-445.10Using Associative Arrays5-465.10.1About Collections5-475.10.2About Associative Arrays5-485.10.3Declaring Associative Arrays5-495.10.4Populating Associative Arrays5-515.10.5Traversing Dense Associative Arrays5-525.10.6Traversing Sparse Associative Arrays5-535.11Handling Exceptions (Runtime Errors)5-545.11.1About Exceptions and Exception Handlers5-545.11.2When to Use Exception Handlers5-555.11.3Handling Predefined Exceptions5-56vi

5.11.467Declaring and Handling User-Defined Exceptions5-57Using Triggers6.1About Triggers6-16.2Creating Triggers6-36.2.1About OLD and NEW Pseudorecords6-46.2.2Tutorial: Creating a Trigger that Logs Table Changes6-46.2.3Tutorial: Creating a Trigger that Generates a Primary Key for a RowBefore It Is Inserted6-56.2.4Creating an INSTEAD OF Trigger6-66.2.5Tutorial: Creating Triggers that Log LOGON and LOGOFF Events6-76.3Changing Triggers6-86.4Disabling and Enabling Triggers6-96.4.1Disabling or Enabling a Single Trigger6.4.2Disabling or Enabling All Triggers on a Single Table6-96-106.5About Trigger Compilation and Dependencies6-106.6Dropping Triggers6-11Working in a Global Environment7.1About Globalization Support Features7-17.1.1About Language Support7-27.1.2About Territory Support7-37.1.3About Date and Time Formats7-37.1.4About Calendar Formats7-47.1.5About Numeric and Monetary Formats7-57.1.6About Linguistic Sorting and String Searching7-67.1.7About Length Semantics7-67.1.8About Unicode and SQL National Character Data Types7-67.2About Initial NLS Parameter Values7-77.3Viewing NLS Parameter Values7-87.4Changing NLS Parameter Values7-97.57.4.1Changing NLS Parameter Values for All SQL Developer Connections7-107.4.2Changing NLS Parameter Values for the Current SQL FunctionInvocation7-11About Individual NLS Parameters7-127.5.1About Locale and the NLS LANG Parameter7-137.5.2About the NLS LANGUAGE Parameter7-147.5.3About the NLS TERRITORY Parameter7-167.5.4About the NLS DATE FORMAT Parameter7-177.5.5About the NLS DATE LANGUAGE Parameter7-20vii

7.5.67.68About NLS TIMESTAMP FORMAT andNLS TIMESTAMP TZ FORMAT Parameters7-217.5.7About the NLS CALENDAR Parameter7-227.5.8About the NLS NUMERIC CHARACTERS Parameter7-237.5.9About the NLS CURRENCY Parameter7-247.5.10About the NLS ISO CURRENCY Parameter7-267.5.11About the NLS DUAL CURRENCY Parameter7-277.5.12About the NLS SORT Parameter7-277.5.13About the NLS COMP Parameter7-297.5.14About the NLS LENGTH SEMANTICS Parameter7-31Using Unicode in Globalized Applications7-327.6.1Representing Unicode String Literals in SQL and PL/SQL7-337.6.2Avoiding Data Loss During Character-Set Conversion7-33Building Effective Applications8.1Building Scalable Applications8.1.1About Scalable Applications8-28.1.2Using Bind Variables to Improve Scalability8-28.1.3Using PL/SQL to Improve Scalability8-48.1.3.1How PL/SQL Minimizes Parsing8-58.1.3.2About the EXECUTE IMMEDIATE Statement8-58.1.3.3About OPEN FOR Statements8-68.1.3.4About the DBMS SQL Package8-68.1.3.5About Bulk SQL8-78.1.4About Concurrency and Scalability8-98.1.4.1About Sequences and Concurrency8-108.1.4.2About Latches and Concurrency8-118.1.4.3About Nonblocking Reads and Writes and Concurrency8-118.1.4.4About Shared SQL and Concurrency8-118.1.5Limiting the Number of Concurrent Sessions8-128.1.6Comparing Programming Techniques with Runstats8-128.1.6.1About Runstats8-128.1.6.2Setting Up Runstats8-138.1.6.3Using Runstats8-168.1.78.28-1Real-World Performance and Data Processing Techniques8-168.1.7.1About Iterative Data Processing8-178.1.7.2About Set-Based Processing8-20Recommended Programming Practices8-218.2.1Use Instrumentation Packages8-218.2.2Statistics Gathering and Application Tracing8-228.2.3Use Existing Functionality8-23viii

8.2.48.39Cover Database Tables with Editioning ViewsRecommended Security Practices8-258-25Developing a Simple Oracle Database Application9.1About the Application9-19.1.1Purpose of the Application9-19.1.2Structure of the Application9-29.1.2.1Schema Objects of the Application9-29.1.2.2Schemas for the Application9-3Naming Conventions in the Application9-49.1.39.2Creating the Schemas for the Application9-59.3Granting Privileges to the Schemas9-69.49.59.69.3.1Granting Privileges to the app data Schema9-69.3.2Granting Privileges to the app code Schema9-79.3.3Granting Privileges to the app admin Schema9-79.3.4Granting Privileges to the app user and app admin user Schemas9-7Creating the Schema Objects and Loading the Data9-89.4.1Creating the Tables9-89.4.2Creating the Editioning Views9-119.4.3Creating the Triggers9-119.4.3.1Creating the Trigger to Enforce the First Business Rule9-129.4.3.2Creating the Trigger to Enforce the Second Business Rule9-139.4.4Creating the Sequences9-149.4.5Loading the Data9-159.4.6Adding the Foreign Key Constraint9-179.4.7Granting Privileges on the Schema Objects to Users9-18Creating the employees pkg Package9-189.5.1Creating the Package Specification for employees pkg9-199.5.2Creating the Package Body for employees pkg9-209.5.3Tutorial: Showing How the employees pkg Subprograms Work9-229.5.4Granting the Execute Privilege to app user and app admin user9-259.5.5Tutorial: Invoking get job history as app user or app admin user9-26Creating the admin pkg Package9-269.6.1Creating the Package Specification for admin pkg9-279.6.2Creating the Package Body for admin pkg9-289.6.3Tutorial: Showing How the admin pkg Subprograms Work9-309.6.4Granting the Execute Privilege to app admin user9-319.6.5Tutorial: Invoking add department as app admin user9-32ix

10Deploying an Oracle Database Application10.1About Development and Deployment Environments10-110.2About Installation Scripts10-110.2.1About DDL Statements and Schema Object Dependencies10-210.2.2About INSERT Statements and Constraints10-210.3Creating Installation Scripts10-310.3.1Creating Installation Scripts with the Cart10-410.3.2Creating an Installation Script with the Database Export Wizard10-510.3.3Editing Installation Scripts that Create Sequences10-710.3.4Editing Installation Scripts that Create Triggers10-810.3.5Creating Installation Scripts for the Sample Application10-910.3.5.1Creating Installation Script schemas.sql10-910.3.5.2Creating Installation Script objects.sql10-1110.3.5.3Creating Installation Script employees.sql10-1410.3.5.4Creating Installation Script admin.sql10-1710.3.5.5Creating Master Installation Script create app.sql10-1910.4Deploying the Sample Application10-2010.5Checking the Validity of an Installation10-2110.6Archiving the Installation Scripts10-22Indexx

List of Tables5-1Cursor Attribute Values7-1Initial Values of NLS Parameters in SQL Developer5-397-7xi

PrefacePrefaceThis is the preface to the Oracle Database 2 Day Developer’s Guide.This document explains basic concepts behind application development with OracleDatabase. It provides instructions for using the basic features of topics throughStructured Query Language (SQL), and the Oracle server-based procedural extensionto the SQL database language, Procedural Language/Structured Query Language(PL/SQL). AudienceThis document is intended for anyone who wants to learn about Oracle Databaseapplication development, and is primarily an introduction to applicationdevelopment for developers who are new to Oracle Database. Documentation Accessibility Related DocumentsWhen you are comfortable with the concepts and tasks in Oracle Database 2 DayDeveloper’s Guide, Oracle recommends that you consult these other OracleDatabase development documents. ConventionsOracle Database 2 Day Developer’s Guide uses these text conventions.AudienceThis document is intended for anyone who wants to learn about Oracle Databaseapplication development, and is primarily an introduction to application developmentfor developers who are new to Oracle Database.This document assumes that you have a general understanding of relational databaseconcepts and an understanding of the operating system environment that you will useto develop applications with Oracle Database.Documentation AccessibilityFor information about Oracle's commitment to accessibility, visit the OracleAccessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx acc&id docacc.Access to Oracle SupportOracle customers that have purchased support have access to electronic supportthrough My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx acc&id info or visit http://www.oracle.com/pls/topic/lookup?ctx acc&id trsif you are hearing impaired.xii

PrefaceRelated DocumentsWhen you are comfortable with the concepts and tasks in Oracle Database 2 DayDeveloper’s Guide, Oracle recommends that you consult these other Oracle Databasedevelopment documents. Oracle Application Express App Builder User's Guide Oracle Database 2 Day Java Developer's GuideFor more information, see: Oracle Database Concepts Oracle Database Development Guide Oracle Database SQL Language Reference Oracle Database PL/SQL Language ReferenceConventionsOracle Database 2 Day Developer’s Guide uses these text conventions.ConventionMeaningboldfaceBoldface type indicates graphical user interface elements associatedwith an action, or terms defined in text or the glossary.italicItalic type indicates book titles, emphasis, or placeholder variables forwhich you supply particular values.monospaceMonospace type indicates commands within a paragraph, URLs, codein examples, text that appears on the screen, or text that you enter.xiii

1Introduction to 2 Day Oracle DatabaseDevelopmentAn Oracle Database developer is responsible for creating or maintaining the databasecomponents of an application that uses the Oracle technology stack. Oracle Databasedevelopers either develop applications or convert existing applications to run in theOracle Database environment. About This DocumentThis document is the entry into the Oracle Database documentation set forapplication developers. About Oracle DatabaseOracle Database groups related information into logical structures calledschemas. The logical structures contain schema objects. About Sample Schema HRThe HR sample schema can be installed with Oracle Database. This schemacontains information about employees—departments, locations, work histories,and related information. Like all schemas, HR has tables, views, indexes,procedures, functions, and other attributes. The examples and tutorials in thisdocument use the schema.See Also:Oracle Database Concepts for more information about the duties of OracleDatabase developers1.1 About This DocumentThis document is the entry into the Oracle Database documentation set for

2.3.3 Connecting to Oracle Database as User HR from SQL Developer 2-6 2.4 Exploring Oracle Database with SQL*Plus 2-7 2.4.1 Viewing HR Schema Objects with SQL*Plus 2-7 2.4.2 Viewing EMPLOYEES Table Properties and Data with SQL*Plus 2-8 2.5 Exploring Oracle Database with SQL Developer 2-10 2.5.1 Tutorial: Viewing HR Schema Objects with SQL .

Related Documents:

Changes in Oracle SQL Developer Release 18.1 xlviii 1 SQL Developer Concepts and Usage 1.1 About SQL Developer 1-2 1.2 Installing and Getting Started with SQL Developer 1-2 1.3 SQL Developer User Interface 1-3 1.3.1 Menus for SQL Developer

Developer,Java Developer fresher from UPTU looking for Android App Developer job. . s Resume 1.07 Android Developer Gurgaon 47221857 Manas Ranjan SOFTWARE DEVELOPER 1.02 Delhi 47421087 Pankaj Kumar Software developer/ Android /Java 1.00 Noi

Mar 16, 2016 · CLEANSE DAY OPTIONS/SUPPORT: 2 Isagenix Snacks† . CLEANSING CALENDAR (START ON ANY DAY OF THE WEEK) Track Your Progress MEASUREMENT TRACKER S Day 1 S Day 2 S Day 3 S Day 4 S Day 5 S Day 6 C Day 7 S Day 8 S Day 9 S Day 10 S Day 11 S Day 12 S Day 13 C Day 14 S

CLEANSE DAY OPTIONS/SUPPORT: 2 Isagenix Snacks† . CLEANSING CALENDAR (START ON ANY DAY OF THE WEEK) Track Your Progress MEASUREMENT TRACKER S Day 1 S Day 2 S Day 3 S Day 4 S Day 5 S Day 6 C Day 7 S Day 8 Day 9 Day 10 Day 11 Day 12 Day 13 C Day 14 S

INSTEON Developer’s Guide The book-length INSTEON Developer’s Guide, 2 nd Edition is the primary source for the information contained in this (much shorter) INSTEON Hub Developer’s Guide . Some links in this document refer to information found there. Developers who purchase an INSTEON Software Developer’s Kit may download

the developer kit. Without a jumper, the developer kit can be powered by J28 Micro-USB connector. With a jumper, no power is drawn from J28 , and the developer kit can be powered via J25 power jack. Power Guide Jetson Nano Developer Kit requires a 5V power supply capable of supplying 2A current. Micro-USB Power Supply Options

xii Oracle Workflow Developer's Guide Audience for This Guide Welcome to the Oracle Workflow Developer's Guide. This guide assumes you have a working knowledge of the following: The principles and customary practices of your business area. Oracle Workflow Oracle Workflow Developer's Guide. Workflow.

-15 high risk days-26 days patrolling-7 knots tested-3 obstacle courses-3 airborne operations-4 air assaults-4 boat movements-2-3 graded patrols per phase . CAO RC 08-19 7. AIRBORNE AND RANGER TRAINING BRIGADE POC: DTG: RLTW! 8 DAY 7 DAY 8 DAY 9 DAY 10 DAY 11 DAY 12 DAY 13 DAY 14 DAY 15 DAY 16 DAY 17 DAY 18 DAY 19 DAY 20 23 -2