Oracle9i笔记1

Typographic Conventions 程序格式约定

Following are two lists of typographical conventions that used specifically within text or within code.

__________________________________________________________________________

After completing this course, you should be able to do the following: 1. Describe the purpose of PL/SQL.

2. Describe the use of PL/SQL for the developer as well as the DBA. 3. Explain the benefits of PL/SQL.

4. Create, execute, and maintain procedures, functions, packages, and database triggers. 5. Manage PL/SQL subprograms and triggers 6. Describe Oracle supplied packages. 7. Manipulate large objects(LOBs). Lesson Aim

In this course, you are introduced to the features and benefits of PL/SQL. You learn how to access the database using PL/SQL.

You can develop modularized applications with database procedures using database objects, such as the following:

你可以用像下面这些组件一样的数据库过程，开发模块化的应用程序。 Procedures and functions Packages

Database triggers

Modular applications improve: 使应用程序得到如下改善： Functionality 功能 Security 安全性

Overall performance 总体性能提升

1. Describe the purpose of PL/SQL. PL/SQL的目标。

1).PL/SQL is the procedural extension to SQL with design features of programming languages. PL/SQL 是SQL在設計程序上的延伸。

2).Data manipulation and query statements of SQL are included within procedural units of code. 操作數據和查詢語句都是用程序上的代碼來實現。

PL/SQL Environment

PL/SQL code can also be stored in the Oracle Server as subprograms that can be referenced by any number of applications connected to the database.

PL/SQL代碼也可以保存在Oracle 服務器上作為子程序，可以被所有的應用程序調用來操作使用數據庫。

Benefits of PL/SQL

PL/SQL bridges the gap between convenient access to database technology and the need for procedural programming capabilities.

PL/SQL 為數據庫提供的接口和程序需求之間架起了一座橋梁。

PL/SQL can improve the performance of an application. The benefits differ depending on the execution environment.

PL/SQL 能改善應用程序的性能。使執行環境不同。

PL/SQL can be used to group SQL statements together within a single block and to send the entire block to the server in a single call, thereby reducing networking traffic.

PL/SQL 把一組語句集成一個集合，應用程序只要發送一個命令調用就行了。因此使網絡流量減少，程序運行速度提升。

PL/SQL can also operate with Oracle Server application development tools such as Oracle Forms and Oracle Reports. By adding procedural processing power to these tools, PL/SQL enhances performance.

PL/SQL 也可以Oracle 的其它高級工具像Oracle Forms 和 Oracle Reports 一起工作。并增加程序能力，加強語言性能。

PL/SQL Block Structure

Every unit of PL/SQL comprises one or more blocks. These blocks can be entirely separate or nested one within another. The basic units(procedures, functions, and anonymous blocks ) that make up a PL/SQL program are logical blocks, which can contain any number of nested subblocks.

PL/SQL is portable. 方便

You can declare variables. 能定義變量。

PL/SQL is native to the Oracle server, you can move programs to any host environment that supports the Oracle server and PL/SQL. In other word,

PL/SQL programs can run anywhere the Oracle server can run; you do not need to tailor them to each new environment.

Oracle server 能運行的地方PL/SQL 也能運行。不用再為其構造環境。

You can program with procedural language control structures. PL/SQL can handle errors.

Benefits of Subprograms

Easy maintenance

Improved data security and integrity Improved performance Improved code clarity

Invoking Stored Procedures and Functions

*Subprograms are named PL/SQL BLOCKS, declared as either procedures or functions. *You can invoke subprograms from different environments.

*Named PL/SQL blocks are also known as subprograms or program units. Procedures, functions, packages, and triggers are different PL/SQL constructs. You can invoke subprograms from different environments.

Chapter 1

Declaring Variables

After completing this lesson, you should be able to do the following: Recongnize the basic PL/SQL block and its sections Describe the significance of variables in PL/SQL Declare PL/SQL variables Execute a PL/SQL block

一、 Section: 1. Declarative

Contains all variables, constants, cursors, and user defined exceptions that are referenced in the executable and declarative sections It’s optional. 2. Executable

Contains SQL statements to manipulate data in the database and PL/SQL statements to manipulate data in the block It’s mandatory。 3. exception handling

Specifies the actions to perform when errors and abnormal conditions arise in the executable section It’s optional.

There are three parameter modes, IN ,OUT , and IN OUT . Use the IN parameter to pass values to the subprogram being called. Use the OUT parameter to return values to the caller of a subprogram. And use the IN OUT parameter to pass initial values to the subprogram being called and to return updated values to the caller. We pass values into anonymous block via isql*PLUS substitution variables.

PL/SQL variables:

1. Scalar 標量。Hold a single value. The main data types are those that correspond to

column types in Oracle server tables; Also support Boolean variables.

2. Composite data types, such as records, allow groups of fields to be defined and

manipulated in PL/SQL blocks.

3. Reference data types hold values, called pointers, that designate other program items.

Reference data types are not covered in this course.

4. LOB(large objects) data types hold values, called locators, that specify the location of

large objects(for example graphic images) that are stored out ofling.LOB data types are discussed in detail later in this course.

Guidelines for Declaring PL/SQL Variables。 聲明變量的潛規則。 Follow naming conventions 遵從命名約定

Initialize variables designated as NOT NULL and CONSTANT. 初始化指定變量和 NOT

NULL 和CONSTANT.

Declare one identifier per line 一行聲明一個變量名稱。

Initialize identifiers by using the assignment operator(:=) or the DEFAULT reserved word.

初始化變量名使用賦值符號（:=）或者 用 DEFAULT 保存值。

Naming Rules

Two variables can have the same name, provided they are in different blocks.

The variable name(identifier) should not be the same as the name of table columns used in

the block.

Using v_ as a prefix representing variable avoids naming conflicts with database objects. 使用v_ 前綴，防止和數據庫中已有的名稱沖突。

The names of the variables must not be longer than 30 characters. The first character must be a letter; the remaining characters can be letters, numbers, or special symbols.

變量名稱不能超過30個字符，第一個字符必須用英文字母；其它位置可以用字母，數字或其它符號。

Base scalar data types: CHAR() VARCHAR() LONG

LONG RAW

NUMBER(precesion,scale) BINARY_INTEGER

PLS_INTEGER : Base type for singed integers between -2147483647 and 2147483647. PLS_INTEGER values require less storage and are faster than NUMBER and

BINARY_INTEGER values.

BOOLEAN

2011-12-20看完55頁 第56頁

Base scalar data types: (date)

DATE The range for dates is between 4712 B.C. and 9999 A.D. TIMESTAMP()

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE : This data type differs from TIMESTAMP WITH TIME

ZONE in that when you insert a value into a database column, the values is normalized to the database time zone, and the time-zone displacements is not stored in the column. When you retrieve the value, Oracle returns the value in your local session time zone. INTERVAL YEAR TO MONTH : INTERVAL YEAR[(precision1)] TO MONTH

INTERVAL DAY TO SECOND : INTERVAL DAY[(precision1)] TO SECOND[(precesion2)]. Where

precision1 and precision2 specify the number of digits in the days field and seconds field, respectively. In both cases, you cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0..9. the defaults are 2 and 6, respectively.

Scalar Variable Declarations

The type %Attribute.

For example: v_v0 table_name%rowtype; v_v1 table_name.colname%type; V_balance number(7,2);

V_min_balance v_balance%type :=10;

A NOT NULL database column constraint does not apply to variables that are declared using %type. Therefore, if you declare a variable using the %TYPE attribute that uses a database column defined as NOT NULL, you can assign the NULL value to the variable.

Declaring Boolean variables

Composite Data Types

Composite data types(also known as collections) are of TABLE,RECORD,NESTED TABLE, and VARRAY types. Use the RECORD data type to treat related but dissimilar data as a logical unit. Use the TABLE data type to reference and manipulate collections of data as whole object. Both RECORD and TABLE data types are covered in detail in a subsequent lesson. NESTED TABLE and VARRAY data types are covered in the Advanced PL/SQL course. 復合數據類型，也被稱作集合。主要包括：表，記錄，嵌入表和數組。 記錄用來處理不同但組合到一起的數據。

表 用來把一個數據集合作為一個整體引用和操作。

記錄和表類型，下面的章節將詳細介紹。嵌入表和數組要看更深一步的 PL/SQL 課程。 For more information, see PL/SQL User’s Gride and reference,”Collections and records.”

LOB Data Type variables Book (CLOB) Photo (BLOB) Movie (BFILE) NCLOB

Bind Variables 綁定變量

To declare a bind variable in the Isql*Plus environment, use the command VARIABLE. For example, you declare a variable of type NUMBER and VARCHAR2 as follows: VARIABLE return_code NUMBER;

VARIABLE return_msg VARCHAR2(30);

Both SQL and i SQL*Plus can reference the bind variable, and I SQL*Plus can display its value through the I SQL*Plus PRINT command.

从Oracle的SGA的构成来看，它是推崇使用 参数绑定的。使用参数绑定可以有效的使用Share Po

ol，对已经缓存的SQL不用再硬解析，能明显的提高性能。 具体实践如下：

SQL>create table test (a number(10)); 再创建一个存储过程：

create or replace procedure p_test is i number(10); begin i := 0;

while i <= 100000 loop

execute immediate ' insert into test values (' || to_char(i) || ')'; i := i + 1; end loop; commit; end p_test;

先测试没有使用参数绑定的： 运行p_test 后，用时91.111秒 再创建一个使用参数绑定的：

create or replace procedure p_test is i number(10); begin i := 0;

while i <= 100000 loop

execute immediate ' insert into test values (:a)' using i; i := i + 1; end loop; commit; end p_test;

运行p_test 后，用时55.099秒.

从上面的运行时间可以看出，两者性相差 39.525％，可见，用不用参数绑定在性能上相差是比较大的。

create table testaa (id number)

select * from testaa

create or replace procedure p_test is i number(10); begin i:=0;

while i <=10000 loop

execute immediate 'insert into testaa values(:a)' using i; i:=i+1; end loop; commit; end p_test; --6.162s

create or replace procedure p_test is i number(10); begin i:=0;

while i <=10000 loop

execute immediate 'insert into testaa values('||to_char(i)||')' ; i:=i+1; end loop; commit; end p_test; --16.692s

begin p_test(); end;

delete testaa --2.153s

truncate table testaa --1.092s 還是用綁定變量快。

Referencing Non-PL/SQL Variables 引用非 PL/SQL 變量

DBMS_OUTPUT.PUT_LINE

DBMX_OUTPUT is an Oracle-supplied package, and PUT_LINE is a procedure within that package.

Practice 1.b. 2.abc. 3

Charpter 2

Writing Executable Statements

After completing this lesson, you should be able to do the following: Describe the significance of the executable section Use identifiers correctly

Write statements in the executable section Describe the rules of nested blocks Execute and text a PL/SQL block Use coding conventions

Identifiers are used to name PL/SQL program items and units, which include constants, variables, exceptions, cursors, cursor variables, subprograms and packages.

SQL Functions in PL/SQL: Examples

Most of the SQL function can be used in PL/SQL. These built-in functions help you to manipulate data; they fall into the following categories: Number Character

Conversion 換算 Date

Miscellaneous 雜項，其它

select chr(66) from dual B

PL/SQL has its own error handling functions which are: SQLCODE SQLERRM

Operators in PL/SQL Logical

Arithmetic 算法 Concatenation 連結

Parentheses to control order of operations Exponential operator (* *)

Operators in PL/SQL

Increment the counter for a loop: V_count := v_count+1;

Set the values of a Boolean flag. V_equal := (v_n1=v_n2);

Validate if an employee number contains a value. V_valid := (v_empno IS NOT NULL);

Programming Guidelines

Make code maintenance easier by: Documenting code with comments

Developing a case convention for the code. 用代碼慣例

Developing naming conventions for identifiers and other objects. Identifiers 命名慣例。 Enhancing readability by identing

程序格式習慣：

SQL statements, PL/SQL keywords, Datatypes 3類都用大寫字母 Identifiers,parameters ; database tables and columns 2類用小寫字母

Chapter 3

Interacting with the oracle sever

After completing this lesson, you should be able to do the following: Write a successful select statement in PL/SQL Write DML statements in PL/SQL Control transactions in PL/SQL

Determine the outcome of SQL Data Manipulation Language(DML) statements

新DML命令 Merge 格式 DECLARE

V_EMPNO EMPLOYEES.EMPLOYEE_ID%TYPE :=100; BEGIN

MERGE INTO copy_emp c USING employees e

ON (c.employee_id =v_empno) WHEN MATCHED THEN UPDATE SET

c.first_name = e.first_name, st_name =st_name, c.email =e.email, ……………

WHEN NOT MATCHED THEN

INSERT VALUES(e.employee_id,e.first_name,st_name,e.email….); End; 混合表

Implicit cursor oracle server在內存上自動創建一個用來執行命令的內存區域。 Explicit cursor 自己申明定義創建的 cursor。

SQL Cursor Attributes 游標特性

Using SQL cursor attributes, you can test the outcome of you SQL statements. 可以查驗SQL語句結果。

SQL%ROWCOUNT: Number of rows affected by the most recent SQL statement(an integer value)

SQL%FOUND: Boolean attribute that evaluates to true if the most recent SQL statement affects one or more rows

SQL%NOTFOUND: Boolean attribute that evaluates to true if the most recent SQL statement does not affect any rows

SQL%ISOPEN: Always evaluates to false because PL/SQL closes implicit cursors immediately after they are executed.

Chapter 4

Writing control structures

After completing this lesson, you should be able to do the following: Identify the uses and types of control structures Construct an IF statement Use CASE expressions

Construct and identify different loop statements Use logic tables

Control block flow using nested loops and labels

Loop:

Counter is an implicitly declared integer whose values automatically increases of decreases by 1 on each iteration of the loop unitl the upper or lower bound is reached.

Use the basic loop when the statements inside the loop must execute at least once. Use the while loop if the condition has to be evaluated at the start of each iteration. Use a for loop if the number of iterations is known.

Case 例，

declare

ab emp%rowtype; cd number(8,2);

cursor c_emp is select * from emp order by ename; begin

open c_emp;

loop

fetch c_emp into ab;

exit when c_emp%notfound; case

when ab.salary between 0 and 5000 then cd:= ab.salary*0.1; when ab.salary between 5000 and 10000 then cd:=ab.salary*.15; when ab.salary >10000 then cd:=ab.salary*.2; end case;

dbms_output.put_line('姓名: ' ||ab.ename ||' 工資：'|| ab.salary ||' 獎金:'|| cd); end loop; end;

declare

ab emp%rowtype; cd number(8,2); ef varchar2(50); i number:=1;

cursor c_emp is select * from emp order by ename; begin

open c_emp;

loop

fetch c_emp into ab; ef:=''; i:=1;

exit when c_emp%notfound;

while i <= ceil(ab.salary/1000) loop ef:=ef || '*'; i:=i+1; end loop;

--dbms_output.put_line(ef);

update emp set stars =ef where ename = ab.ename and empno=ab.empno ;

end loop; end;

Chapter 5

Working with composite data types

After completing this lesson, you should be able to do the following: Create user-defined PL/SQL records

Create a record with the %rowtype attribute Create a INDEX by table

Create a INDEX by table records

Describe the difference between records, tables, and tables of records. Records 可以按定義保存多個標題。

Index by table 相當于一個加了行數的臨時表，可以根據行數取值。（兩列） Index by table of record相當于一個加了行數的臨時表，可以根據行數取值。（N列）

Composite data types Are of two types: PL/SQL RECORDS PL/SQL Collections INDEX BY Table Nested Table VARRAY

Contain internal components Are reusable

Creating a PL/SQL Record Syntax:

TYPE type_name IS RECORD(

Field_declaration [,field_declaration]… );

Identifier type_name; mds_gen_org_history 例： declare

type emp_table_type is table of emp%rowtype

index by binary_integer; emp_table emp_table_type; begin

select * into emp_table(6) from EMP WHERE ENAME='ALLEN'; DBMS_OUTPUT.PUT_LINE(EMP_TABLE(6).SALARY); end;

index by table 的例子： declare

type emp_table_type is table of EMP%rowtype

index by binary_integer; emp_table emp_table_type; i pls_integer:=1;

cursor c_emp is select * from emp order by ename; begin

open c_emp; loop

exit when c_emp%notfound; fetch c_emp into emp_table(i); i:=i+1;

end loop;

for i in EMP_TABLE.FIRST .. EMP_ST loop DBMS_OUTPUT.PUT_LINE(TO_CHAR(i) ||' '|| EMP_TABLE(i).salary); end loop; exception

when no_data_found then

DBMS_OUTPUT.PUT_LINE('未發現數據'); when others then

EMP_TABLE(i).ename ||' '||

DBMS_OUTPUT.PUT_LINE('錯誤'); end;

index by table 相當于一個計數列和表里的一個字段 index by table of record 一個計數列和一個表 附例： declare

type get_dept is table of dept.dept_name%type index by binary_integer; get_dpt get_dept; i number:=1;

cursor c_dpt is select dept.dept_name from dept order by dept.dept_name; begin

open c_dpt; loop

exit when c_dpt%notfound ; fetch c_dpt into get_dpt(i); i:=i+1; end loop; close c_dpt;

for i in get_dpt.first .. get_st loop

DBMS_OUTPUT.PUT_LINE('Department: '||get_dpt(i)); end loop;

exception

when no_data_found then

DBMS_OUTPUT.PUT_LINE('無數據!'); end;

Chaper 6

After completing this lesson, you should be able to do the following: Distinguish between an implicit and an explicit cursor Discuss when and why to use an explicit cursor Use a PL/SQL record variable Write a cursor for loop

Control ling explicit cursors Declare, open, fetch,close.

假如取值有順序，定義時用order by 語句。

如：cursor c_emp is select * from emp order by ename; 可以是幾個表聯合起來定義 cursor.

Cursor 的四種狀態信息： 1. %ISOPEN

例：IF NOT emp_cursor%ISOPEN THEN OPEN emp_cursor; End if;

2. %ROWCOUNT

TO retrieve an exact number of rows

例： LOOP

FETCH emp_cursor INTO ename,deportno; If emp_cursor%ROWCOUNT>10 THEN …. END IF;

3. %NOTFOUND

TO determine when to exit the loop

例：exit when emp_cursor%NOTFOUND

If FETCH never executes successfully, the loop is never exited. Use the following EXIT statement instead:

EXIT WHEN emp_cursor%NOTFOUND OR emp_cursro%NOTFOUND IS NULL

4 %FOUND

_____________________________________________ CURSOR AND RECORDS DECLARE

CURSOR emp_cursor IS SELECT …. FROM …; Emp_record emp_cursor%rowtype;

Begin

OPEN emp_cursor; LOOP

FETCH emp_cursor INTO emp_record; EXIT WHEN emp_cursor%NOTFOUND …..

CURSOR FOR LOOPS 例： DECLARE

CURSOR emp_cursor IS selec * from emp Begin

FOR emp_record IN emp_cursor LOOP IF emp_record.deportno =1 then

DBMS_OUTPUT.PUT_LINE(emp_record.ename ||emp_record….); END IF;

END LOOP; END; --其中，CURSOR可以用后面的select語句代替。 練習：

create or replace procedure get_salary(a in number) is begin

-- SELECT * FROM (select A.*,rownum RN from emp A, dual) B WHERE RN<=2

for r_sal in (SELECT distinct salary FROM (select A.salary,rownum RN from emp A, dual) B WHERE RN<=a ) loop

dbms_output.put_line(to_char(a)||': '||to_char(r_sal.salary)); --insert into sal_table(salary) values(r_sal.salary); end loop; end;

--------------------------------------------------------------------- begin

get_salary(10); end;

Chapter 7

Advanced explicit cursor concepts

After completing this lesson, you should be able to do the following: Write a cursor that uses parameters

Determine when a FOR UPDATE clause in a cursor is required Determine when to use the WHERE CURRENT OF clause Write a cursor that uses a subquery

帶參數的CURSOR DECLARE

CURSOR emp_cursor

(p_deptno number, p_job varchar2) IS SELECT empno,ename FROM emp

WHERE deportno=p_deptno AND job_id = p_job; BEGIN

OPEN emp_cursor(1,’saller’); …

Close emp_cursor;

OPEN emp_cursor(2,’IT’); END;

例：

DECLARE

CURSOR C_GETSAL(a NUMBER) IS (SELECT distinct salary FROM (select A.salary,rownum RN from emp A) B WHERE RN<=a) ; begin

for r_sal in C_GETSAL(100) loop

dbms_output.put_line(to_char(111222)||': '||to_char(r_sal.salary)); end loop; end;

The FOR UPDATE Clause

The WHERE CURRENT OF Clause

例： DECLARE

CURSOR sal_cursor IS

SELECT * FROM emp e, dept d WHERE d.dept_no=e.deportno AND d.dept_no =1

FOR UPDATE OF salary NOWAIT; BEGIN

FOR emp_record IN sal_cursor LOOP

IF emp_record.salary < 5000 THEN UPDATE emp

SET salary = emp_record.salary*1.1 WHERE CURRENT OF sal_cursor; END IF; END LOOP;

END;

CURSOR with Subqueries

例：

DECLARE

CURSOR my_cursor IS

SELECT * FROM DEPT D, (SELECT DEPORTNO,COUNT(*) AS STAFF FROM EMP GROUP BY DEPORTNO ) E

WHERE E.DEPORTNO=D.DEPT_NO; begin

for r_cu in my_cursor loop

DBMS_OUTPUT.PUT_LINE(r_cu.dept_name ||' : '||r_cu.staff); end loop; end;

chapter 8 Handing Exceptions

After completing this lesson, you should be able to do the following: Define PL/SQL exceptions

Recognize unhandled exceptions

List and use different types of PL/SQL exception handlers Trap unanticipated errors

Describe the effect of exception propagation in nested blocks Customize PL/SQL exception messages

Handling Exceptions with PL/SQL

An Exception is an identifier in PL/SQL that is raised during execution. How is it raised?

An Oracle error occurs. You raise it explicitly. How do you handle it? Trap it with a handler.

Propagate it to calling environment. Three types of exception

1. Predefined oracle server error

Do not declare and allow the oracle server to raise them implicitly. 2. Nonpredefined oracle server error.

Declare within the declarative section and allow the oracle server to raise them implicitly.

例：(nonpredefined) define pn =10

declare

eer exception;

pragma exception_init (eer,-2292); begin

delete from dept

where dept_no =&pn; commit; exception when eer then

DBMS_OUTPUT.PUT_LINE('Cannot remove dept ' || to_char(&pn) || '. employee exist.'); end;

3. User-defined error.

Declare within the declarative section, and raise explicitly. 例： DECLARE

e_invalid_dpt exception; begin

UPDATE dept

SET dept_name =&p_dept_desc WHERE dept_no = &p_dept_id; IF SQL%NOTFOUND THEN RAISE e_invalide_dpt; END IF; COMMIT; EXCEPTION

WHEN e_invalide_dpt THEN

DBMS_OUTPUT.PUT_LINE(‘NO SUCH DEPARTMENT ID!’); END;

Chapter 9 creating procedures

After completing this lesson, you should be able to do the following:

Distinguish anonymous PL/SQL blocks from named PL/SQL blocks(subprograms) List the benefits of using subprograms

List the different environments from which subprograms can be invoked.

2012-12-27 看到 263頁 Developing Procedures

Parameters: In , out , in out.

In 傳入參數 Out 傳出參數

In out 傳入并可傳出的參數。 異常：

When an exception is raised in a called procedure, control immediately goes to the exception section of that block. If the exception is unhandled, the block terminates, and control goes to the exception section of the calling procedure. PL/SQL does not roll back database work that is done by the subprogram.

If the exception is handled in the calling procedure, all DML statements in the calling procedure and in the called procedure reaming as part of the transaction.

If the exception is unhandled in the calling procedure, the calling procedure terminates and the exception propagates to the calling environment. All the DML statements in the calling procedure and the called procedure are rolled back along with any changes to any host variables. The host environment determines the outcome for the unhandled exception.

當子過程中出現一個異常時，會提交到子過程的異常處理。如果沒子過程沒有定義異常處理，子過程結束，異常被提交到主程序的異常處理。另外，子程序的命令不會返回到未執行狀態。

如果主過程中定義了異常，所有主過程和子過程中的DML語句，保留部分的數據交換。

如果在主過程中也沒有定義異常，所有主過程和子過程所有的DML語句返回到未執行狀態。所有的改變和變量賦值恢復到過程未執行之前。主機準定未定義的異常的結果。

Benefits of Subprograms

Easy maintenance

1.修改程序不影響別人。2修改一個程序可以對很多應用程序有效果。3.減少重復勞動。 Improved data security and integrity

1. 沒有權限的用戶不直接使用數據庫組件。只能使用子程序的權限很容易控制。 2. 確保數據關系的整體性。避免漏掉有用的關系表。 Improved performance

1.避免多用戶反復編寫。2.避免在編譯時間運行。3.減少從應用程序訪問數據庫的命令。

從而減少流量。 Improved code clarity.

練習：

create OR REPLACE procedure add_emp(empno1 IN number, ename1 IN varchar2,salary1 IN number,deportno1 IN number,DPNAME IN VARCHAR)

IS

V_C PLS_INTEGER; begin

SELECT COUNT(*) INTO V_C FROM DEPT WHERE DEPT_NO = DEPORTNO1; IF V_C = 0 THEN

insert into dept(dept_no,dept_name) values(deportno1,dpname); END IF; insert into emp(empno,ename,salary,deportno) values(empno1,ename1,salary1,deportno1); end;

可分為： --添加部門

1. Create or replace procedure add_dept(deptno in number,deptname in varchar2) is V_C PLS_INTEGER; erro exception; begin

SELECT COUNT(*) INTO V_C FROM DEPT WHERE DEPT_NO = deptno; IF V_C = 0 THEN

insert into dept(dept_no,dept_name) values(deptno,deptname); END IF; exception

when others then

dbms_output.put_line('add_dept過程未知錯誤'); End;

本文来源：https://www.bwwdw.com/article/rp2j.html