Oracle PLSQL Language- P17

Tham khảo tài liệu oracle plsql language- p17, công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả
Nội dung trích xuất từ tài liệu:
Oracle PLSQL Language- P17 Database trigger Record or column of table The body of the trigger is coded in PL/ SQL. While the trigger has a name, the PL/SQL code itself is unnamed, hence anonymous. Script SQL*Plus and SQL*DBA Ad hoc programs and batch processing scripts written in SQL*Plus are always anonymous blocks (which may then call procedures or functions). Embedded PL/SQL programs Pro* embedded languages Embed PL/SQL blocks to execute statements inside the database server. Whenever you attach PL/SQL code to a trigger or field in a tool, that code forms an anonymous PL/ SQL block. When you write this code you can enter a fully specified PL/SQL block (declaration, execution, and exception sections), or you can enter only the executable section. 15.3.4 Nested Blocks PL/SQL allows you to nest or embed anonymous blocks within another PL/SQL block. You can also nest anonymous blocks within anonymous blocks for more than one level, as shown in Figure 15.6. Figure 15.6: Anonymous blocks nested three levels deep 15.3.4.1 Nested block terminology A PL/SQL block nested within another PL/SQL block may be called by any of the following: nestedPlease purchase PDF Split-Merge on www.verypdf.com to remove this watermark. block, enclosed block, child block or sub-block. A PL/SQL block that calls another PL/SQL block (anonymous or named) may be referred to as either the enclosing block or the parent block. 15.3.4.2 Nested blocks provide scope The general advantage of a nested block is that you create a scope for all the declared objects and executable statements in that block. You can use this scope to improve your control over activity in your program. For a discussion, see Section 15.3.5, Scope and Visibility later in this chapter. Consider the following procedure, in which the president and vice-president of the specified company request their salaries be cut in half if their current salaries are more than ten times the average salary in the company. Its the sort of incentive plan that would encourage executives to share the wealth. PROCEDURE update_management (company_id_in IN NUMBER, avgsal_in IN NUMBER) IS BEGIN -- The vice-president shows his generosity... BEGIN SELECT salary INTO v_sal FROM employee WHERE company_id = company_id_in AND title = VICE-PRESIDENT; IF v_sal > avgsal_in * 10 THEN UPDATE employee SET salary := salary * .50 WHERE company_id = company_id_in AND title = VICE-PRESIDENT; ELSE DBMS_OUTPUT.PUT_LINE (The VP is OK!); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; -- The president shows her generosity... BEGIN SELECT salary INTO v_sal FROM employee WHERE company_id = company_id_in AND title = PRESIDENT; IF v_sal > avgsal_in * 10 THENPlease purchase PDF Split-Merge on www.verypdf.com to remove this watermark. UPDATE employee SET salary := salary * .50 WHERE company_id = company_id_in AND title = `PRESIDENT; ELSE DBMS_OUTPUT.PUT_LINE (The Prez is a pal!); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END; Each of the two SELECT-UPDATE combinations is embedded in its own anonymous PL/SQL block. Each block has its own exception section. Why go to all this trouble? Why couldnt the programmer just create a little script that will update the salary for a specified title? The following statement, saved to the updemp.sql file, would do the trick (&N is the syntax used to supply arguments to a SQL*Plus script): UPDATE employee SET salary := salary * .50 WHERE company_id = &1 AND title = &2 AND salary > &3 * 10; and then execute the SQL script in SQL*Plus using the START command: SQL> start updemp 1100 VICE-PRESIDENT SQL> start updemp 1100 PRESIDENT The programmer who was assigned this task took several things into account: q The executives might decide they will want to take such acti ...