oracle| Stored Procedures Xiayi
ASP Dynamic Web site development technology
With the deepening of people's understanding of the Internet and the development of IT technology, static Web pages have become more and more unable to meet the needs of information interaction and e-commerce, so it is a trend to develop web pages that can realize information interaction and personalized service with database as the core. In order to meet the trend of dynamic Interactive Web page development, there are web development technologies that can interact with the backstage database, and the most popular one is the ASP (Active Server Pages) technology based on the Microsoft Windows platform IIS. It embeds specific scripting languages such as VBScript and JavaScript into HTML, and when the browser is requested, the Web server uses the corresponding script interpretation engine to interpret the script, complete the query, modify, and so on. The results are dynamically formatted into HTML and sent back to the browser request side.
Oracle database is the most commonly used database at home and abroad, with the release of Oracle 8i, it adds to the Java and object support, greatly facilitates the development of network software. Therefore, the development of ASP applications in many cases needs to deal with the background Oracle database. Below, we will take the application of several panels as an example, highlighting how ASP scripts can access the stored procedures of the database using the Command object of the ADO component.
The information submitted by the Multiple panel users is uniformly stored in the database tables in the following structure:
CREATE TABLE Messages (
m_id number,
M_forumname varchar2 () not NULL,
M_subject varchar2 () not NULL,
M_username varchar2 () not NULL,
M_email varchar2 () not NULL,
M_entrydate Date Default Sysdate,
M_message VARCHAR2 (200),
M_ordernum number,
m_reply number);
The meanings of each of these fields are as follows:
M_ID: The identification number uniquely identified by each message;
M_forumname: Discussion topic title;
M_subject: Message subject;
M_username: User name;
M_email:e-mail address;
M_entrydate: Submission time;
M_message: Message content;
M_ordernum: Sequence number;
M_reply: Is the answer message.
Two, ASP script calls the stored procedure in Oracle database package
1. Create a database package
Assuming that there are several thematic discussions already in the database table, and if you want to display the names of each topic and the number of messages on each topic separately on the page, first define a stored procedure named Getforum in the Oracle database and place it in a package named Forum_retrieve.
If the stored procedure returns a multiline dataset, the procedure must be placed in a package. A package is an object of an Oracle database that encapsulates data types, stored procedures, functions, variables, and constants, similar to the standard code modules in VB. The package is divided into two parts of Baotou and the package separately, so the two parts should be established separately, and Baotou is used to define the elements that can be referenced externally, and the package body defines the actual code, that is, the logical execution part.
The Baotou and inclusion of this example are defined as follows:
CREATE OR REPLACE PACKAGE forum_retrieve
As
TYPE Tforumname is TABLE of the Messages.m_forumname%TYPE INDEX by Binary_integer;
TYPE Tcount is TABLE of number INDEX by Binary_integer;
PROCEDURE Getforums
(
Forumname out Tforumname,
Thecount out Tcount
);
This is the definition of header, which first declares the two Pl/sql table types Tforumname and Tcount, and then declares the stored procedures that are called externally.
CREATE OR REPLACE PACKAGE body Forum_retrieve
As
PROCEDURE Getforums
(
Forumname out Tforumname,
Thecount out Tcount
)
As
CURSOR C1 is
SELECT M_forumname
From messages
GROUP by M_forumname
Order BY M_forumname;
Counter number DEFAULT 1;
BEGIN
For C in C1 loop
Forumname (counter): =c.m_forumname;
SELECT COUNT (*)
into Thecount (counter)
From messages
WHERE m_forumname=forumname (counter);
Counter: =counter+1;
End LOOP;
End;
The above is a definition of the package body, which contains only a stored procedure named Getforums.
Because Oracle is different from other databases, such as SQL Server, you cannot return multiple rows of a query directly to the caller in a stored procedure, and you can only return one row of results at a time, but here we query the name and number of the discussion group to return multiple rows of data, so we used the PL/SQL cursor C1 It first queries the result set of the different topic names, and then establishes a cursor loop to separate the number of messages for each discussion group topic, saving the topic name and number of messages to the Pl/sql table variable named Forumname and Thecount, and returning to the caller as an output variable. Tforumname and Tcount are Pl/sql's two table types, similar to arrays in other programming languages, whose definitions are not possible in stored procedures, so this is why getforums stored procedures must be placed in a package.
If the above Forum_retrieve package does not have an error in the creation process, it has been compiled and stored in Oracle's data dictionary, under Sql*plus type the following command:
SELECT * from user_objects WHERE object_name= ' Forum_retrieve ';
You will see the state of the header and package named Forum_retrieve, which should normally be a valid state.
2. Call a stored procedure in an ASP file
Next we can use the ADO Command object in the client's ASP file to invoke the stored procedure. The first thing to do is to establish a DSN connection to the Oracle database ODBC on the host on which IIS resides, or to establish a dsn-less connection directly in the ASP file, which can be selected according to your preferences and circumstances, as follows:
In the above example, the stored procedure has only output variables. If the stored procedure being invoked also contains an input variable, the SQL connection string should be modified as follows:
Sqlstr={call package.procedure (?,.., {resultset 100,output1,output2,...}) ｝
which is a placeholder for each input variable, corresponding to the number of input variables one by one. Also, assign a value to each input variable separately, in the following format:
Cmd. Parameters.Append CMD. CreateParameter ("Input variable name", advarchar,adparaminput,30, assignment variable)
Where adVarChar specifies that the variable type is a string; adParamInput specifies that the variable is an input variable, 30 is the maximum length of the specified string, and if the input variable is an integer or other type, you do not need to set this item. The definitions of all these constants are contained in the Adovbs.inc file, and can be set for different parameter types.
ASP script calls separate stored procedures in Oracle Database
An independent stored procedure is a process that is stored in an Oracle data dictionary as a separate object, not in a package, and the ASP script's invocation of such a stored procedure differs slightly from the above method, as illustrated below. For example, to invoke a separate stored procedure named Getmessages in an ASP script, the code is as follows:
CREATE OR REPLACE PROCEDURE getmessages
(
Forumname in Messages.m_forumname%type,
Lastmessage out Messages.m_id%type
)
As
BEGIN
... ' content omitted
Endgetmessages;
This procedure is also invoked using the ADO Command object in the ASP file, as follows:
Four, why to use the deposit
