www.pudn.com > 如何调用oracle的函数、存储过程.rar > main.pas
(*
Author Huet Bartels
Verion Delphi CS 2.0
Date 27/05/97
Purpose To show you how to uses some of the Oracle Functions,
Stored Procedures,and standard Select Statements.
Mail Huet1Cix@Compulink.co.uk (Home)
Mail Huet1@mdx.ac.uk (Work)
This demo is freeware, do what ever you what with it.
I would like feedback, to keep this demo upto date, if
you have any examples of functions or routines I have not used
please mail me them so I can add them to the demo.
How to get this work.
You will need the C/S version of Delphi.
You will need to config the BDE you use the Oracle Driver
I have include a BDE CFG file, if you use the ORA32.CFG you will
need to change the following fields.
1) Server Name. **If you are using SQL*NET 2 make sure you
have the .WORLD added after the server name
2) NET PROTCOL should be TNS if using SQL*NET V2 otherwise TCP/IP or
what ever you default protocol is
3) VENDOR INIT Change it to match you version of Oracle ie ORA72
is for Oracle Version 7.2 ORA73 is version 7.3....
You will also need to execute the Stored Proecdure script named emproc.sql
included with this demo in the SCOTT/TIGER ACCOUNT
*)
unit main;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
DBTables, DB, StdCtrls, ComCtrls, Grids, DBGrids, ExtCtrls;
type
TForm1 = class(TForm)
Database1: TDatabase;
StoredProc1: TStoredProc;
Query1: TQuery;
Panel1: TPanel;
Label1: TLabel;
Label2: TLabel;
DBGrid1: TDBGrid;
PageControl1: TPageControl;
TabSheet1: TTabSheet;
Panel3: TPanel;
Memo1: TMemo;
Panel4: TPanel;
Button1: TButton;
Button2: TButton;
Button3: TButton;
Query2: TQuery;
DataSource1: TDataSource;
Query1EMPNO: TFloatField;
Query1ENAME: TStringField;
Query1JOB: TStringField;
Query1SAL: TFloatField;
Query1DEPTNO: TFloatField;
Memo2: TMemo;
ComboBox1: TComboBox;
Q1: TQuery;
Q1ENAME: TStringField;
Query3: TQuery;
Query3ENAME: TStringField;
Query3JOB: TStringField;
Query3DNAME: TStringField;
Query3LOC: TStringField;
Label3: TLabel;
Button4: TButton;
Query4: TQuery;
TabSheet2: TTabSheet;
Query4TERM: TStringField;
Query4SESS: TFloatField;
Query4LAN: TStringField;
PageControl2: TPageControl;
TabSheet3: TTabSheet;
Label4: TLabel;
Bevel1: TBevel;
Label5: TLabel;
Edit1: TEdit;
Label7: TLabel;
Edit3: TEdit;
Label6: TLabel;
Edit2: TEdit;
Button6: TButton;
TabSheet4: TTabSheet;
Query5: TQuery;
Button5: TButton;
Edit5: TEdit;
Label11: TLabel;
Query5DAYNAME: TStringField;
Query5FULLDATE: TStringField;
Edit4: TEdit;
Label8: TLabel;
Query5FULLDATE1: TStringField;
Edit6: TEdit;
Query5FULLDATE2: TStringField;
Edit7: TEdit;
Label9: TLabel;
Label10: TLabel;
Edit8: TEdit;
Label12: TLabel;
Query5FULLDATE3: TStringField;
Query5FULLDATE4: TStringField;
Query5FULLDATE5: TStringField;
Edit9: TEdit;
Edit10: TEdit;
Label13: TLabel;
Label14: TLabel;
TabSheet5: TTabSheet;
Query6: TQuery;
Query6I1: TStringField;
Query6I2: TStringField;
Query6I3: TStringField;
Edit11: TEdit;
Edit12: TEdit;
Edit13: TEdit;
Label15: TLabel;
Label16: TLabel;
Label17: TLabel;
Button7: TButton;
TabSheet6: TTabSheet;
Edit14: TEdit;
Label18: TLabel;
Query7: TQuery;
Button8: TButton;
Query7LD: TDateTimeField;
Edit15: TEdit;
Label19: TLabel;
Query7ND: TDateTimeField;
Label20: TLabel;
Label21: TLabel;
TabSheet7: TTabSheet;
Edit16: TEdit;
Label22: TLabel;
Edit17: TEdit;
Label23: TLabel;
Edit18: TEdit;
Label24: TLabel;
Edit19: TEdit;
Label25: TLabel;
Query8: TQuery;
Query8CH: TFloatField;
Query8CO: TFloatField;
Query8SI: TFloatField;
Query8SH: TFloatField;
Button9: TButton;
Edit20: TEdit;
Label26: TLabel;
Query3SAL: TFloatField;
procedure Database1Login(Database: TDatabase; LoginParams: TStrings);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure ComboBox1DropDown(Sender: TObject);
procedure Button6Click(Sender: TObject);
procedure Button5Click(Sender: TObject);
procedure PageControl1Change(Sender: TObject);
procedure Button7Click(Sender: TObject);
procedure Button8Click(Sender: TObject);
procedure Button9Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.DFM}
procedure TForm1.Database1Login(Database: TDatabase;
LoginParams: TStrings);
begin
(*The OnLogin event is activated whenever a TDatabase component assigned to an
SQL database is opened and the LoginPrompt property is True. Use the OnLogin
event to set login parameters. The OnLogin event gets a copy of the
TDatabase's login parameters array, Params. Use the Values property to change
these parameters:
*)
LoginParams.Values['USER NAME'] := 'SCOTT';
LoginParams.Values['PASSWORD'] := 'TIGER';
end;
procedure TForm1.Button1Click(Sender: TObject);
begin
DataSource1.DataSet:=Query1; //Assign Query to DBGrid
Query1.Open;
If Query2.active then Query2.Close;
If Query3.active then Query3.Close;
Memo2.lines:=Query1.Sql;
Memo2.Lines.Add('');
Memo2.Lines.Add('This is a standard Query that will display all rows in a table');
Memo2.Lines.Add('The select will only show the columns that have been defined');
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
If Query1.active then Query1.Close;
If Query3.active then Query3.Close;
DataSource1.DataSet:=Query2; //Assign Query to DBGrid
Query2.Open;
Memo2.Lines:=Query2.Sql;
Memo2.Lines.Add('');
Memo2.Lines.Add('This query show you how two join to tables together, the query');
Memo2.Lines.Add('joins the EMP & DEPT tables within Oracle on the DEPTNO field');
Memo2.Lines.Add('By joining the tables you can display the Department Name (DNAME) and');
Memo2.Lines.Add('Location (LOC) of the employee from the EMP table');
end;
procedure TForm1.Button3Click(Sender: TObject);
begin
If ComboBox1.Text = '' then
ShowMessage('You must select a name from the drop down list for this example to work.')
else
begin
DataSource1.DataSet:=Query3; //Assign Query to DBGrid
Query3.Close;
(* The Prepare method sends a parameterized query to the database engine for
parsing and optimization. A call to Prepare is not required to use a
parameterized query. However, it is strongly recommended, because it will
improve performance for dynamic queries that will be executed more than
once. If a query is not explicitly prepared, each time it is executed,
Delphi automatically prepares it.
*)
Query3.Prepare;
Query3.Params[0].AsString:=ComboBox1.Text;
Query3.Open;
Memo2.lines:=Query3.Sql;
Memo2.Lines.Add('');
Memo2.Lines.Add('This query takes 1 Parameter which is selected from the combolist box');
Memo2.Lines.Add('The Param is passed into the query via the variable :User');
Memo2.Lines.Add('The ComboBox is acting like a DBLookup list without the overhead');
end;
end;
procedure TForm1.ComboBox1DropDown(Sender: TObject);
begin
(* This procedure is used to fill a standard Combobox with values taken from
the Oracle database. I intened to use no Database aware objects, to keep the
size of the exe as small as I can
*)
Q1.Open;
While not Q1.Eof do
begin
ComboBox1.Items.Add(Q1Ename.Text);
Q1.next;
end;
end;
procedure TForm1.Button6Click(Sender: TObject);
begin
Query4.Open;
Edit1.Text:=Query4Term.text;
Edit2.Text:=Query4Sess.text;
Edit3.Text:=Query4Lan.text;
end;
procedure TForm1.Button5Click(Sender: TObject);
begin
Query5.Open;
Edit4.Text:=Query5FullDate.text;
Edit5.Text:=Query5DayName.Text;
Edit6.Text:=Query5FullDate1.text;
Edit7.Text:=Query5FullDate2.text;
Edit8.Text:='Total days so far this year '+Query5FullDate3.text;
Edit9.Text:='Total weeks so far this year '+Query5FullDate4.text;
Edit10.Text:='Week number this month '+Query5FullDate5.text;
Query5.Close;
end;
procedure TForm1.PageControl1Change(Sender: TObject);
begin
if PageControl1.Activepage.Pageindex = 0 then
DBGrid1.Visible:=True
else
DBGrid1.Visible:=False;
end;
procedure TForm1.Button7Click(Sender: TObject);
begin
Query6.Close;
Query6.Open;
Edit11.Text:=Query6I1.Text;
Edit12.Text:=Query6I2.Text;
Edit13.Text:=Query6I3.Text;
end;
procedure TForm1.Button8Click(Sender: TObject);
begin
Query7.Open;
EDit14.Text:=Query7LD.Text;
EDit15.Text:=Query7ND.Text;
end;
procedure TForm1.Button9Click(Sender: TObject);
begin
Query8.Open;
Edit16.Text:=Query8CH.Text;
Edit17.Text:=Query8CO.Text;
Edit18.Text:=Query8SI.Text;
Edit19.Text:=Query8SH.Text;
end;
procedure TForm1.Button4Click(Sender: TObject);
begin
If ((ComboBox1.Text = '') or (Edit20.text = '' ))then
ShowMessage('You must select a name from the drop down list, and enter a salary for this example to work.')
else
begin
(*
There seems to be a bug in the C/S Version of Delphi 2.17.53.0 or my
version any way, may work ok in 2.01. You seem to have to reverse the
params in-order to get the stored procedure to work correctly. I
tested the stored procedure from within SQL*PLUS with the execute statement
under ORACLE 7.2 for NT and it works fine ie
EXECUTE UPDATEEMP('JAMES','3000'); So it must be Delphi at fault :-)
Any ideas ???
*)
StoredProc1.Params[1].asString:=Edit20.Text; //This should be Param[0]
StoredProc1.Params[0].asString:=ComboBox1.Text; //This should be Param[1]
StoredProc1.Prepare;
StoredProc1.ExecProc;
end;
DataSource1.DataSet:=Query3; //Assign Query to DBGrid
Query3.Close;
(* The Prepare method sends a parameterized query to the database engine for
parsing and optimization. A call to Prepare is not required to use a
parameterized query. However, it is strongly recommended, because it will
improve performance for dynamic queries that will be executed more than
once. If a query is not explicitly prepared, each time it is executed,
Delphi automatically prepares it.
*)
Query3.Prepare;
Query3.Params[0].AsString:=ComboBox1.Text;
Query3.Open;
Memo2.Clear;
Memo2.Lines.Add('');
Memo2.Lines.Add('This is the results from executing a stored procedure');
Memo2.Lines.Add('The Value you typed into the salary field, should now');
Memo2.Lines.Add('be display in the DBGRID below.');
end;
end.