随笔分类 - 11G PL/SQL FDM
摘要：Has three forms:SELECT AS ...SELECT ...SELECT = ...推荐使用AS，这样不容易出错。别名的使用范围列的别名.表的别名.
阅读全文
摘要：Autonomous TransactionsAutonomous Transaction(AT)，通过PRAGMA AUTONOMOUS_TRANSACTION;指明当前的事务操作只针对自身有效，不涉及到其他事务.Trapping User-Defined ExceptionsExamples:User-Defined ExceptionDECLARE v_deptno NUMBER := 500; v_name VARCHAR2(20) := 'Testing'; e_invalid_department EXCEPTION;BEGIN ...
阅读全文
摘要：Exception TypesPredefined Oracle Server --Implicitly raisedNon-predefined Oracle Server --Implicitly raisedUser-defined --Explicitly raisedSyntax to Trap ExceptionsSyntaxEXCEPTION WHEN exception1 [OR exception2...] THEN statement1; statemen...
阅读全文
摘要：ObjectivesAfter completing this lesson,you should be able to do the following:Define PL/SQL execeptionsRecognize unhandled exceptionsList and use different types of PL/SQL execption handlersTrap unanticipated errors.Describe the effect of exception propagation in nested blocksCustomize PL/SQL except
阅读全文
摘要：FOR Loops RulesReference the counter only within the loop;it is undefined outside the loop.Do not reference the counter as the target of an assignment.Neither loop bound should be NULL.Use the REVERSE keyword to force the loop to decrement from the upper bound to the lower bound.You must still make
阅读全文
摘要：Logic TablesBuild a simple Boolean condition with a comparision operator.ANDTRUEFALSENULLTRUETRUEFALSENULLFALSEFALSEFALSEFALSENULLNULLFALSENULL ORTRUEFALSENULLTRUETRUETRUETRUEFALSETRUEFALSENULLNULLTRUENULLNULLNOTTRUEFALSEFALSETRUENULLNULLBoolean Expression or Logincal ExpressionWhat is the valu...
阅读全文
摘要：ObjectivesAfter completing this lesson,you should be able to do the following:Identify the uses and types of control structuresConstruct an IF statementUse CASE statements and CASE expressionsConstruct and identify loop statementsUse guidelines when using conditional control structuresPL/SQL中，要区分sta
阅读全文
摘要：1、在一个pl/sql block中，可以有多个transaction，一个transaction可以跨越多个PL/SQL block;PL/SQL块执行完成，但是事务没有提交SQL> begin 2 insert into tt values(1); 3 end; 4 /PL/SQL procedure successfully completed.SQL> select * from tt; ID---------- 0 1验证事务没有提交方法SQL> select addr from v$transaction;ADDR-----...
阅读全文
摘要：ObjectivesAfter completing this lesson,you should be table to do the following:Determine the SQL statements that can be directly included in a PL/SQL executeable block.Manipulate data with DML statements in PL/SQLUse transaction control statements in PL/SQLMake use of the INTO clause to hold the val
阅读全文
摘要：Nested Tables[表中有表]关联数组，是用在PL/SQL中的一种普通数据类型，无法存储在数据库中；嵌套表既可以在PL/SQL中使用，也可以存储在数据库中.VARRAY可变数组，既可以在PL/SQL中使用，也可以存储在数据库中.Summary of Collection TypesQuizIdentify situations in which you can use the %ROWTYPE attribute.When you are not sure about the structure of the underlying database table.When you wan
阅读全文
摘要：DECLARE SUBTYPE location_t IS VARCHAR2(64); TYPE population_type IS TABLE OF NUMBER INDEX BY location_t; l_country_population population_type; l_count PLS_INTEGER; l_location location_t;BEGIN l_country_population('Greenland') := 100000; l_country_popu...
阅读全文
摘要：Associative Arrays(INDEX BY Tables)An associative array is a PL/SQL collection with two columns:Primary Key of integer or string data type(主键可以使整型值，可以使字符串值)Column of scalar or record data typeIOT表表示存储在数据库中的一种数据类型.INDEX BY Table只能用在PL/SQL中，不能存储在数据库中.Associative Array StructureThe Order of Associative
阅读全文
摘要：The PL/SQL Text Book《Oracle PL/SQL Programming》Anchored DeclarationsWhen you anchor a datatype,you tell PL/SQL to set the datatype of your variable based on the datatype of an already defined data structure-another PL/SQL variable,a predefined TYPE or SUBTYPE,a database table,or a specific column in
阅读全文
摘要：ObjectivesAfter completing this lesson,you should be able to do the following:Describe PL/SQL collections and recordsCreate user-defined PL/SQL recordsCreate a PL/SQL record with the %ROWTYPE attributeCreate associative arraysOracle数据库中，复合数据类型包含:集合(collections)、记录(records)Oracle数据库中，集合的类型:(关联数组、嵌套表、
阅读全文
摘要：1、FOR循环下，不需要显示关闭游标DECLARE CURSOR c_emp_cursor ( deptno NUMBER ) IS SELECT employee_id,last_name FROM employees WHERE department_id = deptno; r c_emp_cursor%ROWTYPE;BEGIN FOR i IN c_emp_cursor(10) LOOP DBMS_...
阅读全文
摘要：Cursor For LoopsSyntax:FOR record_name IN cursor_name LOOP statement1; statement2; ... END LOOP;The cursor FOR loop is a shortcut to process explict cursors.Implict open,fetch,exit and close cursor.The record isimplicit declared.Example:DECLARE CURSOR c_emp_curs...
阅读全文
摘要：ObjectivesAfter completing this lesson,you should be able to do the following:Distinguish between implict and explicit cursorsDiscuss the reasons for using explicti cursorsDeclare and control explicit cursorsUse simple loops and cursor FOR loops to fetch dataDeclare and use cursors with parametersLo
阅读全文
摘要：ObjectivesAfter completing this lesson,you should be able to to do the following:Differentate between a procedure and a functionDescribe the uses of functionsCreate stored functionsInvoke a functionRemove a functionLesson AgendaWorking with functions:-Defferentiating between a procedure and a functi
阅读全文
摘要：Exceptions Not HandledCalling PathSome Procedures Demo可参考脚本:192.168.1.200[oracle@DG1 scripts]$ pwd/u01/oracle/scripts[oracle@DG1 scripts]$ lltotal 60-rw-r--r-- 1 oracle oinstall 886 Apr 19 15:35 call_calculate_blance.sql-rw-r--r-- 1 oracle oinstall 220 Apr 19 14:26 s09_01.sql-rw-r--r-- 1 oracle oins
阅读全文
摘要：Passing Actula Parameters:Creating the add_dept ProcedureView Code CREATE OR REPLACE PROCEDURE add_dept( p_name IN dept.department_name%TYPE, p_loc IN dept.location_id%TYPE)ISBEGIN INSERT INTO dept(department_id,department_name,location_id) VALUES(departments_seq.NEXTVAL...
阅读全文