26.oracleocp题库dba经典辅助学习

1、Database InterfacesObjectivesAfter completing this lesson, you should be able to do the following:Use SQL*Plus and iSQL*Plus to access the Oracle Database 10gDescribe the logical structure of tablesUse SQL to query, manipulate, and define dataIdentify common database interfacesWhat Is SQL?SQL provid

2、es statements for a variety of tasks, including:Querying data Inserting, updating, and deleting rows in a table Creating, replacing, altering, and dropping objects Controlling access to the database and its objects SQL unifies all of the preceding tasks in one consistent language.Using SQLThere are

3、several tools for interfacing with the database using SQL:Oracle SQL*Plus and iSQL*Plus Oracle Forms, Reports, and DiscovererOracle Enterprise Manager Third-party toolsEnterprise Manager: Seeing the SQLWhat Is SQL*Plus?Command-line toolUsed interactively or in batch mode$ sqlplus /nologSQL*Plus: Rel

4、ease .0 - Production on Tue Feb 17 06:17:14 2004Copyright (c) 1982, 2004, Oracle. All rights reserved.SQL connect ricEnter password:Connected.SQL SELECT * FROM dual;D-XSQLWhat Is iSQL*Plus?What Is iSQL*Plus?Full Notes PageUsing iSQL*PlusDescribing DataQuerying DataThe SELECT has three basic

5、parts:The SELECT ListThe FROM clauseThe WHERE condition (optional)Sorting the DataSQL SELECT last_name, department_id, phone_number 2 FROM employees 3 ORDER BY last_name;LAST_NAME DEPARTMENT_ID PHONE_NUMBER Abel 80 011.44.1644.429267Ande 80 011.44.1346.629268Atkinson 50 650.124.6234Austin 60 590.423

6、.4569Baer 70 515.123.8888Baida 30 515.127.4563Banda 80 011.44.1346.729268Joining TablesGetting data from more than one tableJoining TablesFull Notes PageManipulating DataSQL INSERT INTO employees 2 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER, 3 MISSION_PCT, 4 MANAGER_ID,DEPARTMENT_ID) 5 VAL

7、UES 6 (9999,Bob,Builder, ,NULL,sysdate, 7 IT_PROG,NULL,NULL,100,90);1 row created.SQL UPDATE employees SET SALARY=6000 2 WHERE EMPLOYEE_ID = 9999;1 row updated.SQL DELETE from employees 2 WHERE EMPLOYEE_ID = 9999;1 row deleted.Defining DataOverview of TransactionsTransaction 1Transaction 2COMMIT;Tra

8、nsaction Control StatementsSQL SELECT * FROM local_temp;no rows selectedSQL INSERT INTO local_temp VALUES 2 (SYSDATE, 76, 58);1 row created.SQL SELECT * from local_temp;TEMP_DATE HI_TEMP LO_TEMP 27-OCT-03 76 58SQL ROLLBACK;Rollback complete.SQL SELECT * FROM local_temp;no rows selectedLocking DataOr

9、acle Database 10g automatically locks data so that only one user can make changes at a time.Other Statement Categories Session control statements: Manage the properties of a user sessionSystem control statement: Manages the properties of an Oracle instanceEmbedded SQL statements: SQL statements with

10、in a procedural language programWhat Is PL/SQL?PL/SQL is a block-structured language, which extends SQL with:Declarations:VariablesConstantsCursorsControl structures:Conditional controlIterative controlSequential controlError handlingExample PL/SQL BlockDECLARE qty_on_hand NUMBER(5);BEGIN SELECT qua

11、ntity INTO qty_on_hand FROM inventory WHERE product = TENNIS RACKET FOR UPDATE OF quantity; IF qty_on_hand 0 THEN - check quantity UPDATE inventory SET quantity = quantity - 1 WHERE product = TENNIS RACKET; INSERT INTO purchase_record VALUES (Tennis racket purchased, SYSDATE); ELSE INSERT INTO purch

12、ase_record VALUES (Out of tennis rackets, SYSDATE); END IF; COMMIT;END;Uses of PL/SQLBlocks of PL/SQL are used in:Anonymous blocksFunctionsProceduresPackagesTriggersObject typesWhat Is Java?Java is an industry-standard, object-oriented programming language. It includes the following concepts:A Java

13、Virtual Machine (JVM), which provides platform independenceAutomated storage management techniquesLanguage syntax that borrows from C and enforces strong typingOracle and JavaA PL/SQL function:CallableStatement cstmt = conn.prepareCall(? = CALL balance(?); cstmt.registerOutParameter(1, Types.FLOAT);

14、 cstmt.setInt(2, acctNo); cstmt.executeUpdate();float acctBal = cstmt.getFloat(1);FUNCTION balance (acct_id NUMBER) RETURN NUMBER ISacct_bal NUMBER;BEGIN SELECT bal INTO acct_bal FROM accts WHERE acct_no = acct_id; RETURN acct_bal;END;Calling the function with Java:What Is OCI?OCI provides for:The O

15、racle Call Interface (OCI) is how all database features are made accessible to application developers.OCI makes scalable and high-performance applications possible.Higher-level APIs and tools use OCI indirectly for database access.Other APIsJava Database Connectivity (JDBC)Pro*C/C+Pro*COBOLOracle C+ Interface (OCCI)Open Database Connectivity (ODBC)Oracle Data Provider for .NET ( )Oracle Objects for OLE (OO4O)Other APIsFull Notes PageSummaryIn this lesson, you should have learned how to: Use SQL*Plus and iSQL*Plus to access Oracle Database 10gDescribe the logical structure of tablesUse