SQL_PLSQL培训.doc

SQL PL/SQL语言基础参考资料l SQL Reference: 语法及函数参考l Error Message : 出错信息l SQL Plus Guide and Referencel PL/SQL Users Guide and Referencel Application Developers Guide Fundamentals: 开发ORACLE应用的一般性指导l Supplied PL/SQL Packages Reference：PL/SQL内置Package参考SQL基础历史Dr. E. F. Codd于1970年在ACM(Association Of Computer Mechanism)月刊上发表了A Relational Model of Data for Large Shared Data Banks, 建立了关系模型。1979年, Relational Software, Inc. (现在的Oracle Corporation)发布了第一个SQL商业应用。标准l SQL-92: Entry, Transitional, Intermediate, and Fulll Core SQL-99参见SQL Reference 附录B：Oracle and Standard SQLBasic Elements of Oracle SQLl Literals (Text, Integer, Number, Interval)l Datatypesl Format Modelsl Nullsl Pseudocolumns sju:dou;l Commentsl Database Objectsl Schema Object Names and Qualifiersl Referring to Schema Objects and PartsLiteralsThe terms literal and constant value are synonymous and refer to a fixed datavalue.l Textjacks sword length 4000l Integerl Number38 digits of precision.l Intervalspecifies a period of timeDatatypesl Built-in Datatypesl User-defined typeBuilt-in Datatypesl VARCHAR2 页：2Special skills: the where clause like where col like :x1|% are inefficient, there index over column col will not be applied, use where col between :x1 and :x1|chr(255) instead. (size) NVARCHAR2 (size)l NUMBER (p, s) l LONG l DATE l RAW (size) LONG RAW l ROWID UROWID (size) l CHAR页：2There are two comparison rules when comparing character values: blank-padded comparison semantics and nonpadded comparison semantics. Blank-padded comparison is applied in CHAR/NCHAR, text literals. (size) NCHAR (size) l CLOB NCLOB BLOB BFILEMore information about Oracle datatype, see SQL Reference or Server Application Developers GuideROWID: (extended fmt: OOOOOO-FFF-BBBBBB-RRR; restricted fmt: BBBBBBBB.RRRR.FFFF, use DBMS_ROWID.ROWID_TO_RESTRICTED to make the conversion)Data Conversionl Implicit Data Conversion l Explicit Data ConversionTo get more information about explicit data conversion, see SQL Reference, from page 2-35 Data conversion to page 2-53; or see the references of relevant oracle functions.NULL To test for nulls, use only the comparison operators IS NULL and IS NOT NULL. If you use any other operator with nulls and the result depends on the value of the null, the result is UNKNOWN.Pseudocolumnsl CURRVAL and NEXTVALl LEVELl ROWIDl ROWNUMCommentHintThe optimizer uses these hints as suggestions for choosing an execution plan for the statement.Database Objectsl Schema objectsl Nonschema objectsSchema objectsA schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects页：3The naming rules of objects are described in SQL Reference, page 2-72. Namespace.:ClustersDatabase linksDatabase triggersDimensionsExternal procedure librariesIndex-organized tablesIndexes. Database ObjectsIndex typesJava classes, Java resources, Java sourcesMaterialized views / snapshotsMaterialized view logs / snapshot logsObject tablesObject typesObject viewsOperatorsPackagesSequencesStored functions, stored proceduresSynonymsTablesViewsNonschema objectsContextsDirectoriesProfilesRolesRollback segmentsTablespacesUsersNaming RulesNamespaceWithin a namespace, no two objects can have the same nameDouble quotesContain spacesBe case sensitiveBegin with a character other than an alphabetic character, such as a numeric characterContain characters other than alphanumeric characters and _, $, and #Be a reserved wordOperatorsUnary and Binary OperatorsPrecedenceArithmetic OperatorsConcatenation OperatorComparison OperatorsLogical OperatorsSet OperatorsOther Built-In OperatorsUser-Defined Operators.Unary and Binary OperatorsUnary :operator operandBinary :operand1 operator operand2PrecedenceThe following table lists levels of precedence among SQL operators from high to low. (Operators listed on the same line have the same precedence)+, - (Identity, negation)*, /+, -, |=, !=, , =, IS NULL, LIKE, BETWEEN, INNOTANDORSet OperatorsUNIONUNION ALLINTERSECTMINUSAdditional informationESCAPE OptionOuter join (+) / PRIORALL/ ANY/ BETWEEN AND/ EXIST/ LIKE/ NOT IN/ Functionsl SQL Functionsl User-Defined FunctionsSQL FunctionsSingle-Row FunctionAggregate FunctionAnalytic FunctionSingle-Row Functionl Number Functionsabs, ceil, floor, mod, sign, truncl Character Functionschr, lower, lpad, ltrim, replace, rpad, rtrim, substr, trim, upper, ascii, instr, lengthl Date Functionsadd_months, last_day, months_between, next_day, round, sysdate, truncnote: add_month(19990228,1) = 19990331l Conversion Functionsto_char, to_date, to_numberl Miscellaneous Single-Row Functionsdump, greatest, least, nvl, sys_context, uid, user, userenv, vsizeAggregate FunctionAvg, count, max, min, sumExpressions, Conditions, and QueriesExpressionsSimple ExpressionsCompound ExpressionsFunction ExpressionsDECODE ExpressionsCASE ExpressionsExpression ListDECODESyntax: decode( expr, search1, result1, search2, result2, search3, result3,default)In a DECODE expression, Oracle considers two nulls to be equivalent. If expr is null, Oracle returns the result of the first search that is also null.The maximum number of components in the DECODE expression, including expr, searches, results, and default is 255.CASESyntax:Case when expr1 then expra, when expr2 then exprb, else exprc endTo avoid exceeding the limit of 128 choices, you can nest CASE expressions. That is expr1 can itself be a CASE expression.Expression ListExample: (SCOTT, BLAKE, TAYLOR)Conditionsl simple_comparison_conditionl group_comparison页：7Any, some, all_conditionl membership_conditionl range_conditionl NULL_conditionl EXISTS_conditionl LIKE_conditionl compound_conditionQueries and SubqueriesSimple QueriesHierarchical QueriesStart with / Connect byExample: see demo.sqlSELECT LPAD( , 4 * (LEVEL - 1)|name FROM staffstart with id = 3connect by id = prior mgridSorting Query ResultsExample:Select empname, deptnam From emp e, dept d Where e.depid = d.depid Order by 2, 1;JoinSelf JoinsCartesian ProductsOuter JoinsUsing SubqueriesExample:UPDATE emp a SET salary_cat = (SELECT salary_cat FROM emp b WHERE a.mgrid = b.id) WHERE mgrid IN (SELECT 页：8Oracle offers statement-level consistency by default.id FROM emp);Dual tableSelecting from the DUAL table is useful for computing a constant expression with the SELECT statement.SQL StatementsSummaryl Data Definition Language (DDL) StatementsCreate, drop, 页：8Oracle implicitly commits the current transaction before and after every DDL statement.alter table, truncate, comment, revoke, grant, analyzel Data Manipulation Language (DML) StatementsSelect, delete, insert, update, lock table, call, explain planl Transaction Control StatementsCommit, rollback, savepoint, set transactionl Session Control StatementsAlter sessionl System Control StatementsAlter systemSQL *PLUSEditingAppend line/textChange textDelete line(s)Edit with system editorExamplesUsing bind variablesVARIABLE rate NUMBERVARIABLE table CHARACCEPT table PROMPT table name:;DECLARE tot_blks NUMBER; unused_blks NUMBER; hiwm_blks NUMBER; real_blks NUMBER;BEGIN SELECT blocks INTO tot_blks FROM user_segments WHERE segment_name = :table AND segment_type = TABLE; SELECT empty_blocks INTO unused_blks FROM user_tablesWHERE table_name = :table; hiwm_blks := (tot_blks - unused_blks) - 1; SELECT COUNT(DISTINCT SUBSTR(DBMS_ROWID.ROWID_TO_RESTRICTED(ROWID,0), 1, 8)| SUBSTR(DBMS_ROWID.ROWID_TO_RESTRICTED(ROWID,0),15, 4) INTO real_blks FROM SI2000.IC01; :rate := (hiwm_blks - real_blks) / hiwm_blks) * 100;END;/PRINT rate;Formating Resultscolumn tablespace_name format a25column ts format a25col Megabytes for 99999.99compute sum of bytes used free on reportbreak on reportspool tablespaceselect a.tablespace_name ts, a.file_id, trunc(sum(b.bytes)/(count(*)*1024*1024),2) Megabytes, trunc(sum(b.bytes)/count(*) - sum(a.bytes)/(1024*1024),2) used, trunc(sum(a.bytes)/(1024*1024),2) free, trunc(nvl(100-(sum(nvl(a.bytes,0)/(sum(nvl(b.bytes,0)/count(*)*100,0),2) pct_usedfrom sys.dba_free_space a, sys.dba_data_files bwhere a.tablespace_name = b.tablespace_name and a.file_id = b.file_idgroup by a.tablespace_name, a.file_id;spool offAutomationaccept obj_owner char prompt input owner:set pagesize 0spool recompile.sqlselect alter package |owner|.|object_name| compile | decode(object_type, PACKAGE,package, body)|; from dba_objects where owner=&obj_owner and object_type like PACK% and status=INVALID order by status desc,object_name;spool off!vi recompile.sqlrecompile.sql!rm recompile.sqlAccessing SQL DatabasesConnecting to the Default DatabaseConnecting to a Remote DatabaseSqlplus user/passworddatabaseCopying Data from One Database to AnotherSQL COPY FROM SCOTT/TIGERBOSTONDB -TO TODD/FOXCHICAGODB -CREATE NEWDEPT (DNUMBER, DNAME, CITY) -USING SELECT * FROM DEPTCopying Data between Tables on One DatabaseCOPYUsing copy command in SQL Plus to copy data from another database.l COMMIT CONTROLl BATCH Moving Example:set copycommit 1set arraysize 2000copy from user/passwdNT_SERV -replace tablename -using select * from tablenamePL/SQL GuidePL/SQL: A PeekBLOCKPL/SQL代码按节编写成为块DECLARE BEGIN EXCEPTION WHEN THEN END;块可以嵌套CURSORExplicit CursorDECLARE CURSOR cCur1 IS SELECT ;OPENFETCHCLOSECURSOR FOR LOOPFOR Cur_Rec IN cCur1 LOOP END LOOP;Cur_Rec不需要说明。引用规则：Cur_Rec.ColumnNameImplicit Cursor任何SQL语句将创建游标。查询v$session, v$cursor可以得到每个session最近一次打开的游标所执行的语句从v$sqlarea中可以得到更多信息Explicit/Implicit Cursor Attributes%FOUND%ISOPEN%NOTFOUND%ROWCOUNT引用：Explicit Cursor: IF c1%ROWCOUNT 10 THENImplicit Cursor: DELETE FROM emp WHERE empno = my_empno; IF SQL%FOUND THEN - delete succeededCursor with parametersDECLARE CURSOR C1(VIEW_PATTERN VARCHAR2) IS SELECT VIEW_NAME FROM ALL_VIEWS WHERE VIEW_NAME LIKE VIEW_PATTERN|% AND ROWNUM sal FOR UPDATE NOWAIT;Oracle provides two level of consistency: statement-level (default) and transaction-level. Consider this case: at the end of the transaction, you find the result set that you have got from a prior select statement has been modified by another transaction. FOR UPDATE clause provides a way to avoid that phenomena, because it locks every row affected by the select statement. Autonomous TransactionsAn Autonomous transaction is fully independent to the outer transaction. For example:PROCEDURE proc3 IS PRAGMA AUTON. dept_id NUMBER;BEGIN dept_id := 20; UPDATE . INSERT . UPDATE . COMMIT;END;NOTE: No changes made by the main transaction are visible to the autonomous transaction. On the other hand, after the autonomous transaction commits, all changes are visible to the main transaction unless the isolation level is set to serializable. Control Structurel Conditional Controll Iterative Controll Sequential ControlConditional ControlIF condition1 THEN Sequence_of_statements1ELSIF condition2 THEN Sequence_of_statements2ELSE Sequence_of_statements3END IF;Iterative Controll LOOPLOOP sequence_of_statementsEND LOOP;Complete a loop: EXIT / EXIT WHENl WHILELOOPl FOR LOOPSequential Controll GOTO statementRestrictions: Cannot branch into an IF statement, LOOP statement, or sub-block Cannot branch from an exception handler into the current block.l NULL statementSubprogramsl Procedurel FunctionDependencyWhen an object is referencing by another, there is a dependent relation between them. Use the following script to find out what objects are affected when a stored procedure:SELECT type| |owner|,|name FROM all_dependencies WHERE referenced_ower = UPPER(&1) AND referenced_name = UPPER(&2) ORDER BY type;ParametersINOUTIN OUTNOCOPY Compiler hintBy default, the OUT and IN OUT parameters are passed by value. That will slow down execution and use up memory. To prevent that, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference.Example:DECLARETYPE Staff IS VARRAY(200) OF Employee;PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff) IS .PackagesAdvantages of PackagesModularityEasier Application DesignInformation HidingAdded FunctionalityBetter PerformancePackage spec and Package Bodyl Package SpecIncluding Declaration of public procedures/functions.Including Declaration of public variables, constants and user-define exceptionsl Package BodyIncluding Declaration and Implementation of private procedures/functionsIncluding Declaration of private variables, constants and user-define exceptionsIncluding Implementation of public procedures/functionsOverloadingTake this as an example:FUNCTION NextWord (vStringToParse IN varchar2) RETURN varchar2;FUNCTION NextWord (vStringToParse IN varchar2, vDelimiter IN varchar2);PL/SQL determines which of the two procedures is being called by checking their formal parameters.Native Dynamic SQLThe Need For Dynamic SQLl DDL, DCL and session control statement cannot be executed statically in PL/SQL.l Want more flexibility. Used in applications that allow users to choose query search criteria or optimizer hints at runl To get better performance, use dynamic SQL other than the package DBMS_SQL (Pre-Oracle8 i PL/SQL solution).Using the EXECUTE IMMEDIATE StatementSyntax:EXECUTE IMMEDIATE dynamic_stringINTO define_variable, define_variable. | recordUSING IN | OUT | IN OUT bind_argument, IN | OUT | IN OUT bind_argument.RETURNING | RETURN INTO bind_argument, bind_argument.;Example:sql_stmt := INSERT INTO dept VALUES (:1, :2, :3);EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;Using the OPEN-FOR, FETCH, and CLOSE StatementsExample:DECLARE TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; emp_rec emp%ROWTYPE; sql_stmt VARCHAR2(200); my_job VARCHAR2(15) := CLERK;BEGIN sql_stmt := SELECT * FROM emp WHERE job = :j; OPEN emp_cv FOR sql_stmt USING my_job; LOOP FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%NOTFOUND; - process record END LOOP; CLOSE emp_cv;END