Chapter 12 creating packages
Objectives
After completing this lesson, you should be able to do the following: ? Describe packages and list their possible components
? Create a package to group together related variables, cursors, constants, exceptions,
procedures, and functions.
? Designate a package construct as either public or private ? Invoke a package construct
? Describe a use for a bodiless package Packages
? Group logically related PL/SQL types, items, and subprograms ? Consist of two parts:
? Specification ? Body ? Cannot be invoked, parameterized, or nested
? Allow the oracle server to read multiple objects into memory at once.
How to Develop a package
There are three basic steps to developing a package, similar to those steps that are used to develop a stand-alone procedure.
1. Write the text of the CREATE PACKAGE statement within a SQL script file to create the
package specification and run the script file. The source code is compiled into P code and is stored within the data dictionary.
2. Write the text of the CREATE PACKAGE BODY statement within a SQL script file to create the
package body and run the script file.
The source code is compiled into P code and is also stored within the data dictionary. 3. Invoke any public construct within the package from an oracle server environment.
create or replace package package_wolfy
as
procedure add_dept(deptno in number,deptname in varchar2);
procedure add_emp(empno1 IN number, ename1 IN varchar2,salary1 IN number,deportno1 IN number,DPNAME IN VARCHAR); procedure del_emp(ID IN NUMBER);
function numtochar2(num in number) return varchar2; end;
create or replace package body package_wolfy as end;
Advantages of Packages 1
? Modularity: encapsulate related constructs. 模塊化：壓縮有關系的構件。 ? Easier application design: code and compile specification and body separately
便于應用程序設計：代碼，編譯和主體分開。 ? Hiding information:
? Only the declarations in the package specification are visible and accessible to
applications. 應用程序中只有注明包才能調用 ? Private constructs in the package body are hidden and inaccessible. 私有構件外部程序
看不到。 ? All coding is hidden in the package body. Advantages of Packages 2
? Added functionality: persistency of variables and cursors. 功能性：使變量和游標更穩定 ? Better performance: 更好的績效
? The entire package is loaded into memory
When the package is first referenced 當首先調用包時，整個飯會被讀取到內存中 ? There is only one copy in memory for all users 所用用戶調用內存中的一個包就行了。 ? The dependency hierarchy is simplified. 簡化明了了子程序的歸屬。
Chapter 13 more package concepts
After completing this lesson, you should be able to do the following:
? Write packages that use the overloading feature. 寫一個有超載特色的包
? Describe errors with mutually referential subprograms. 相互有關系的子程序之間互相提供
報錯。 ? Initialize variables with a one-time-only procedure. 在一個執行一次的過程中初始化一個
變量。
? List the four purity levels of a function.列出四個PURITY函數。 ? Identify persistent states.
Overloading:
? Enables you to use the same name for different subprograms inside a PL/SQL block, a
subprogram, or a package. 同名子程序放到一個程序組或包中。 ? Requires the formal parameters of the subprograms to differ in number, order, or datatype family. 參數在數量，順序或數據類型（同一FAMILY）不同。
? Enables you to build more flexibility because a user or application is not restricted by the
specific datatype or number of formal parameters 讓你的工程很靈活。
Note: only local or packaged subprograms can be overloaded. You connot overload stand-alone subprograms. 只有本地或包里的子程序能超載。STAND-ALONE 子程序不能超載。
超載的例子： To_char在包中有四個同名的子程序。
Using forward declaration
在包中調用子過程，需要把子過程放到前面。
Create a One-Time-Only Procedure 用來給包的公有變量賦值。
Overload 兩個同名函數或過程在同一個包中，程序會根據輸入參數格式自動選擇比較適合當前輸入參數的一個函數或過程。
Chapter 14 oracle supplied packages
After completing this lesson, you should be able to do the following:
? Write dynamic SQL statements using DBMS_SQL and EXECUTE IMMEDIATE. ? Describe the use and application of some Oracle server supplied packages:
? DBMS_DLL ? DBMS_JOB ? DBMS_OUTPUT ? UTL_FILE
? UTL_HTTP and UTL_TCP
Using the DBMS_SQL Package
The DMBS_SQL package is used to write dynamic SQL in stored procedures and to parse DDL statements. Some of the procedures and functions of the package include:
? OPEN_CURSOR
Open a new cursor and assigns a cursor ID number
? PARSE
Parses the DDL or DML statement: that is checks the statement’s syntax and associates it with the opened cursor (DDL statements are immediately executed when parsed) ? BIND_VARIABLE
Binds the given value to the variable identified by its name in the parsed statement in the given cursor. ? EXECUTE
Execute the SQL statement and returns the number of rows processed. ? FETCH_ROWS
Retrieves a row for the specified cursor (for multiple rows, call in a loop) ? CLOSE_CURSOR
Closes the specified cursor
Using DBMS_SQL
PROCEDURE DELETE_ALL_ROWS2(a in varchar2,b out number) IS
Cursor_name integer;
BEGIN
cursor_name:=dbms_sql.open_cursor;
dbms_sql.parse(cursor_name,'delete from ' ||a,dbms_sql.native); b:=dbms_sql.execute(cursor_name); dbms_sql.close_cursor(cursor_name); END;
How to Process Dynamic DML 動態 DML是怎么運行?
1. Use OPEN_CURSOR to establish an area in memory to process a SQL statement. 2. Use PARSE to establish the validity of the SQL statement.
3. Use the EXECUTE function to run the SQL statement. This function returns the number of row processed.
4. Use CLOSE_CURSOR to close the cursor.
Using the EXECUTE IMMEDIATE statement
EXECUTE IMMEDIATE dynamic_sqlstr
[INTO {define_variable[,define_variable] …|record}] [USING [IN|OUT|IN OUT] bind_argument [IN|OUT|IN OUT] bind_qrgument] …];
? INTO 后面是保存語句產生內容的變量。格式需要與語句產生變量的格式相同。 ? RETURNING INTO 主要用于有返回值的DML語句 ? USING 語句后跟綁定變量的值
Using the DBMS_DDL Package
The DBMS_DDL Package:
? Provides access to some SQL DDL statements from stored procedures. ? Includes some procedures:
? Alter_compile(object_type,owner,object_name)
例：DBMS_DDL.ALTER_COMPILE(‘PROCEDURE’,’A_USER’,’QUERY_EMP’) ? ANALYZE_OBJECT(object_type,owner,name,method)
例：DBMS_DDL.ANALYZE_OBJECT(‘TABLE’,’A_USER’,’JOBS’,’COMPUTE’)
NOTE: This package runs with the privileges of calling user,rather than the package owner SYS.
This package gives developers access to ALTER and ANALYZE SQL statements through PL/SQL environments.
Using DBMS_JOB for Scheduling
DBMS_JOB Subprograms
? SUBMIT submits a job to the job queue
? REMOVE removes a specified job from the job queue
? CHANGE alters a specified job that has already been submitted to the job queue(you can
alter the job description, the time at which the job will be run, or the interval between executions of the job )
? WHAT alter the job description for a specified job ? NEXT_DATE alter the next execution time for a specified job ? INTERVAL alter the interval between executions for a specified job
? BROKEN disables job execution(if a job is marked as broken, the oracle server does not attempt to execute it)
? RUN forces a specified job to run ?
Submitting jobs
You can submit jobs by using DBMS_JOB.SUBMIT. available parameters include: ? JOB OUT BINARY_INTEGER ? WHAT IN VARCHAR2
? NEXT_DATE IN DATE DEFAULT SYSDATE ? INTERVAL IN VARCHAR2 DEFAULT ’NULL’
? NO_PARSE IN BOOLEAN DEFAULT FALSE
Boolean flag that indicates whether to parse the job at job submission(the default is false) Note: an exception is raised if the interval does not evaluate to a time in the future. 格式：
Variable jobno number Begin
DBMS_JOB.SUBMIT( :Jobno,
‘Package_wolfy.add_dept(90,’’education’’);’, Trunc(sysdate+1), ‘trunc(sysdate+1)’ );
Commit;
End;
Changing Job Characteristics
? DBMS_JOB.CHANGE: you can change the WHAT,NEXT_DATE, and INTERVAL parameters. ? DBMS_JOB.INTERVAL: you can change the INTERVAL parametner. ? DBMS_JOB.NEXT_DATE：you can change the next execution date. ? DBMS_JOB.WHAT: you can change the WHAT parameter.