11g: Program with PL/SQL - 1Z0-144

Oracle Database 11g: Program with PL/SQL 1Z0-144

https://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=5001&get_params=p_exam_id:1Z0-144#tabs-2

Exam Topic

Oracle Database 11g: PL/SQL Fundamentals

• Introduction to PL/SQL

https://www.tutorialspoint.com/plsql/index.htm Each block consists of three sub-parts: 1. declaration(optional); 2. Executable commands;3. Exception Handing(optional) Each block consists of three sub-parts: 1. declaration(optional) 2. Executable commands 3. Exception Handing(optional) Every PL/SQL statement ends wiDCL（Data Control Language，数据控制语言）：用于定义数据库用户的权限。 DCL包括： ALTER PASSWORD GRANT REVOKE CREATE SYNONYMth a semicolon (;). PL/SQL blocks can be nested within other PL/SQL blocks using BEGIN and END. PL/SQL Program Units, A PL/SQL unit is any one of the following: PL/SQL block Function Package Package body Procedure Trigger Type Type body ------------------------- 1). DML（Data Manipulation Language，数据操作语言）：用于检索或者修改数据。 DML包括：  SELECT：用于检索数据； INSERT：用于增加数据到数据库； UPDATE：用于从数据库中修改现存的数据  DELETE：用于从数据库中删除数据。 2). DDL（Data Definition Language，数据定义语言）： 用于定义数据的结构，比如 创建、修改或者删除数据库对象。 DDL包括：DDL语句可以用于创建用户和重建数据库对象。下面是DDL命令： CREATE TABLE：创建表 ALTER TABLE DROP TABLE：删除表 CREATE INDEX DROP INDEX 3). DCL（Data Control Language，数据控制语言）：用于定义数据库用户的权限。 DCL包括： ALTER PASSWORD  GRANT  REVOKE  CREATE SYNONYM

• Explain the need for PL/SQL
• Explain the benefits of PL/SQL
• Identify the different types of PL/SQL blocks

Here is the basic structure of a PL/SQL block: declare   variable declarations begin   sql statements   declare     variable declarations   begin     sql statements   exception     exception handler   end;   further-sql-statements exception   exception handler end;

• Output messages in PL/SQL

dbms_output.put_line('hello world!');

• Declaring PL/SQL Variables

declare    myname varchar2(20);    msg varchar2(20):='hello, world!';

• Recognize valid and invalid identifiers
• List the uses of variables, declare and initialize variables, use bind variables

1. bind variable SQL> variable x number; SQL> exec :x:=104; PL/SQL 过程已成功完成。 SQL> select * from employees where employee_id=:x; EMPLOYEE_ID FIRST_NAME                               LAST_NAME                                    SALARY DEPARMENT_ID ----------- ---------------------------------------- ---------------------------------------- ---------- ------------         104                                          Ernst                                          6000       60

• List and describe various data types using the %TYPE attribute

The %TYPE attribute lets you declare a constant, variable, field, or parameter to be of the same data type a previously declared variable, field, record, nested table, or database column. If the referenced item changes, your declaration is automatically updated. set serveroutput on; declare names varchar2(20):='John Smith'; lname names%type;   -- but it doesn't inherits data type and default value begin --lname:='nana'; DBMS_OUTPUT.PUT_LINE('lname: ' || lname); end; http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/type_attribute.htm

• Writing Executable Statements

Executable and Nonexecutable Statements Every statement is either executable or nonexecutable. In general, if a statement specifies an action to be taken at runtime, it is executable. Otherwise, it is nonexecutable. The nonexecutable statements specify attributes, such as type and size; determine arrangement or order; define initial data values; specify editing instructions; define statement functions; classify program units; and define entry points. In general, nonexecutable statements are completed before execution of the first executable statement. http://docs.oracle.com/cd/E19957-01/805-4939/z40007331fd1/index.html

• Identify lexical units in a PL/SQL block

lexical units词汇单位

• Use built-in SQL functions in PL/SQL and sequences in PL/SQL expressions

Built-in SQL function. http://psoug.org/reference/builtin_functions.html

• Describe when implicit conversions take place and when explicit conversions have to be dealt with

implicit conversion隐式转换

• Write nested blocks and qualify variables with labels

标签可以添加到语句块，以改进代码的可读性，以及限定嵌套语句块中存在相同名称的元素。语句块的名称必须在可执行代码第1行（或者BEGIN或者DECLARE）的前面;标签也可以出现在END 后面。目的为注释，也许会使用--，或者/*和*/。 set serveroutput on <<outer_block>> declare v_test number :=123; begin DBMS_OUTPUT.PUT_LINE('Outer_block, v_test:'|| v_test);   <<inner_block>>   declare   v_test number := 456;   begin   DBMS_OUTPUT.PUT_LINE('Inner_block,v_test:'|| v_test);   DBMS_OUTPUT.PUT_LINE('Inner_block,outer_block.v_test:'|| outer_block.v_test);   end inner_block; end outer_block; == output result == Outer_block, v_test:123 Inner_block,v_test:456 Inner_block,outer_block.v_test:123 http://blog.csdn.net/beixiaoyang/article/details/7959564

• Write readable code with appropriate indentation
• Interacting with the Oracle Database Server
• Create PL/SQL executable blocks using DML and transaction control statements

transaction

• Make use of the INTO clause to hold the values returned by a SQL statement

http://psoug.org/definition/INTO.htm  DECLARE  L_EMPNONUMBER;  L_ENAMEVARCHAR2(1000);  L_DEPTNONUMBER;  L_SALARYNUMBER;   BEGIN  SELECTEMPNO, ENAME, DEPTNO, SALARY INTO L_EMPNO, L_ENAME, L_DEPTNO, L_SALARY FROMEMPLOYEES  WHEREEMPNO=100;  END;

• Writing Control Structures

http://docs.oracle.com/cd/A87860_01/doc/appdev.817/a77069/03_struc.htm  1.conditional control  2.Iterativecontrol  3.Sequencecontrol

• Identify the uses and types of control structures (IF, CASE statements and expressions)
• Construct and identify loop statements
• Apply guidelines when using conditional control structures
• Working with Composite Data Types

http://www.linuxidc.com/Linux/2014-11/109272.htm Oracle PL/SQL复合数据类型 http://docs.oracle.com/cd/E18283_01/timesten.112/e13076/dtypesfunc.htm#CIHEGAHD

• Create user-defined PL/SQL records
• Create a record with the %ROWTYPE attribute

• Create an INDEX BY table and INDEX BY table of records
• Describe the differences among records, tables, and tables of records
• Using Explicit Cursors

使用显式游标

• Distinguish between usage of implicit and explicit cursors, use SQL cursor attributes
• Declare and control explicit cursors, use simple loops and cursor FOR loops to fetch data
• Declare and use cursors with parameters
• Lock rows with the FOR UPDATE clause and reference the current row with the WHERE CURRENT OF clause
• Handling Exceptions
• Define PL/SQL exceptions
• Recognize unhandled exceptions
• Handle different types of exceptions (pre-defined exceptions, non-predefined exceptions and user-defined exceptions)
• Propagate exceptions in nested blocks and call applications
• Creating Stored Procedures and Functions
• Differentiate between anonymous blocks and subprograms
• Create a simple procedure and invoke it from an anonymous block
• Create a simple function
• Create a simple function that accepts a parameter
• Differentiate between procedures and functions

Oracle Database 11g: Develop PL/SQL Program Units

• Creating Procedures
• Differentiate between anonymous blocks and subprograms, use a modularized and layered subprogram design, and identify the benefits of subprograms
• Create a simple procedure and invoke it from an anonymous block
• Work with procedures
• Handle exceptions in procedures, remove a procedure, and display a procedure's information
• Creating Functions
• Differentiate between a procedure and a function
• Describe the uses of functions
• Work with functions (create, invoke and remove functions)
• Creating Packages
• Identify the benefits and the components of packages
• Work with packages (create package specification and body, invoke package subprograms, remove a package and display package information)
• Working with Packages
• Overload package subprograms, use forward declarations
• Create an initialization block in a package body
• Manage persistent package data states for the life of a session and use PL/SQL tables and records in packages
• Using Oracle-Supplied Packages in Application Development
• Describe how the DBMS_OUTPUT package works
• Use UTL_FILE to direct output to operating system files
• Describe the main features of UTL_MAIL
• Using Dynamic SQL
• Describe the execution flow of SQL statements
• Use Native Dynamic SQL (NDS)
• Use the DBMS_SQL package
• Design Considerations for PL/SQL Code
• Create standard constants and exceptions
• Write and call local subprograms
• Control the run-time privileges of a subprogram
• Perform autonomous transactions
• Use NOCOPY hint, PARALLEL ENABLE hint and DETERMINISTIC clause
• Use bulk binding and the RETURNING clause with DML
• Creating Triggers
• Describe different types of triggers and their uses
• Create database triggers
• Manage triggers
• Creating Compound, DDL, and Event Database Triggers
• Create triggers on DDL statements
• Create triggers on system events
• Using the PL/SQL Compiler
• Describe the new PL/SQL compiler and features
• Use the new PL/SQL compiler initialization parameters
• Use the new PL/SQL compile time warnings
• Managing PL/SQL Code
• Describe and use conditional compilation
• Hide PL/SQL source code using dynamic obfuscation and the Wrap utility
• Managing Dependencies
• Track and manage procedural dependencies