SQL ************************************* ********** Create Table AA
(
A_id number (10) primary key,
A_name varchar2 (50 ),
A_sex varchar2 (4 ),
A_date date,
A_money number (8, 2)
) -- Check whether the operation is successful.
Select * from AA; -- Create a trigger
Create sequence aa_id_seq increment by 1 start with 1; -- Insert a piece of data to test
Insert into AA (a_id, a_name, a_sex, a_date, a_money) values (1, 'chen feilong ', 'male', sysdate, 55.55); -- Success
Insert into AA (a_name, a_sex, a_date, a_money) values ('chen feilong ', 'male', sysdate, 55.55); -- not successful, there is a sequence but not auto-incrementing -- Create a trigger
Create or replace trigger aa_id_tigger
Before insert on AA for each row
Begin
If: New. a_id is null
Then
Select aa_id_seq.nextval into: New. a_id from dual;
End if;
End;
/ -- Insert a data to test
Delete from AA;
Insert into AA (a_name, a_sex, a_date, a_money) values ('chen xiaolong', 'male', sysdate, 66.55); -- Success -- Stored procedure for inserting information
Create or replace procedure aa_insert_pro
(
B _name in AA. a_name % type,
B _sex in AA. a_sex % type,
B _money in AA. a_money % Type
)
As
Begin
Insert into AA (a_name, a_sex, a_date, a_money) values (B _name, B _sex, sysdate, B _money );
End aa_insert_pro;
/
-- Deleted Stored Procedure
Create or replace procedure aa_delete_pro
(
B _id in number
)
As
Begin
Delete from AA where a_id = B _id;
End aa_delete_pro;
/
-- Modify the stored procedure of table AA
Create or replace procedure aa_update_pro
(
B _id in number,
B _name in varchar2,
B _sex in AA. a_sex % type,
B _money in AA. a_money % Type
)
As
Begin
Update AA set a_name = B _name, a_sex = B _sex, a_date = sysdate, a_money = B _money where a_id = B _id;
End aa_update_pro;
/
-- Query certain data fields of a piece of data
Create or replace procedure aa_selecebyid_pro
(
B _id in number,
B _name out varchar2,
B _sex out varchar2,
B _date out date,
B _money out number
)
As
Begin
Select a_name, a_sex, a_date, a_money into B _name, B _sex, B _date, B _money from AA where a_id = B _id;
End aa_selecebyid_pro;
/
-------------------------------------------------------------------------------
-- Query multiple data. Step 1 and 2
-- Step 1: Create a package
Create or replace package aa_package
Type aa_all is ref cursor;
End aa_package;
/
-- Step 2: Use the package to create multiple queries
Create or replace procedure aa_selectmore_pro
(
B _all out aa_package.aa_all
)
As
Begin
Open B _all for select * from AA;
End aa_selectmore_pro;
/ Select * from AA where a_id = 2; For java files ************************************ **************************************** ************* Class dB, connected to the database: Package com. dB; Import java. SQL. connection;
Import java. SQL. drivermanager;
Import java. SQL. preparedstatement;
Import java. SQL. resultset; Public class DB
{
Private Static connection con = NULL;
Private Static resultset rs = NULL;
Private Static preparedstatement PSTM = NULL; Public static connection getcon ()
{ String driver = "oracle. JDBC. Driver. oracledriver ";
String url = "JDBC: oracle: thin :@ localhost: 1521: orcl ";
String uid = "Scott ";
String pid = "tiger "; Try
{
Class. forname (driver );
Con = drivermanager. getconnection (URL, uid, pid );
} Catch (exception E)
{
E. printstacktrace ();
Con = NULL;
} System. Out. println ("open ");
Return con;
} Public static void closecon ()
{
Try
{
If (RS! = NULL)
{
Rs = NULL;
}
If (con! = NULL)
{
Con = NULL;
}
If (PSTM! = NULL)
{
PSTM = NULL;
}
} Catch (exception E)
{
E. printstacktrace ();
} Finally
{
Rs = NULL;
PSTM = NULL;
Con = NULL;
System. Out. println ("disabled ");
}
} /**
* @ Param ARGs
*/
Public static void main (string [] ARGs)
{
DB = new dB ();
System. Out. println (db. getcon ());
DB. closecon ();
} } Class testaa, Operation Stored Procedure Package com. Chen; Import java. SQL. callablestatement;
Import java. SQL. connection;
Import java. SQL. resultset;
Import java. SQL. types;
Import java. util. date; Import com. DB. dB; /** Call the stored procedure to operate the database */
Public class testaa
{
Private connection con = NULL;
Resultset rs = NULL;
Callablestatement CSTM = NULL;
/** Add data */
Public void insertaa (string name, string sex, double money)
{
Try
{
String SQL = "call Scott. aa_insert_pro (?,?,?) ";
If (con = NULL) con = dB. getcon ();
CSTM = con. preparecall (SQL );
CSTM. setstring (1, name );
CSTM. setstring (2, sex );
CSTM. setdouble (3, money );
Boolean flag = c0000.exe cute ();
System. Out. println ("Write completed:" + flag );
} Catch (exception E)
{
E. printstacktrace ();
}
Finally
{
DB. closecon ();
}
}
/** Delete data */
Public void deleteaa (int id)
{
Try
{
String SQL = "Call aa_delete_pro (?) ";
If (con = NULL) con = dB. getcon ();
CSTM = con. preparecall (SQL );
CSTM. setint (1, ID );
Boolean flag = c0000.exe cute ();
System. Out. println ("deletion Result:" + flag );
} Catch (exception E)
{
E. printstacktrace ();
}
Finally
{
DB. closecon ();
}
}
/** Modify data */
Public void updateaa (int id, string name, string sex, double money)
{
Try
{
String SQL = "Call aa_update_pro (?,?,?,?) ";
If (con = NULL) con = dB. getcon ();
CSTM = con. preparecall (SQL );
CSTM. setint (1, ID );
CSTM. setstring (2, name );
CSTM. setstring (3, sex );
CSTM. setdouble (4, money );
Int K = c0000.exe cuteupdate ();
Boolean flag;
If (k> 0)
{
Flag = true;
} Else
{Flag = false ;}
System. Out. println ("Modification result:" + flag );
} Catch (exception E)
{
E. printstacktrace ();
}
Finally
{
DB. closecon ();
}
}
/** Query certain fields of a record */
Public void selectsomebyid (int id)
{
Try
{
String SQL = "Call aa_selecebyid_pro (?,?,?,?,?) ";
If (con = NULL) con = dB. getcon ();
CSTM = con. preparecall (SQL );
CSTM. setint (1, ID );
CSTM. registeroutparameter (2, types. varchar );
CSTM. registeroutparameter (3, types. varchar );
CSTM. registeroutparameter (4, types. date );
CSTM. registeroutparameter (5, types. integer );
Ccmd.exe cute ();
String name = CSTM. getstring (2 );
String sex = CSTM. getstring (3 );
Date = CSTM. getdate (4 );
Int money = CSTM. getint (5 );
System. Out. println ("name =" + name + "\ tsex =" + sex + "\ tdate =" + date + "\ tmoney =" + money );
} Catch (exception E)
{
E. printstacktrace ();
}
Finally
{
DB. closecon ();
}
}
/** Query all data */
Public void selectallaa ()
{
Try
{
String SQL = "Call aa_selectmore_pro (?) ";
If (con = NULL) con = dB. getcon ();
CSTM = con. preparecall (SQL );
CSTM. registeroutparameter (1, Oracle. JDBC. oracletypes. cursor );
// Rs = c0000.exe cutequery ();
Ccmd.exe cute ();
Rs = (resultset) CSTM. GetObject (1 );
System. Out. println ("query result :");
// System. Out. println (RS );
While (Rs. Next ())
{
// System. out. println ("a_id =" + Rs. getint (1) + "\ ta_name =" + Rs. getstring (2) + "\ ta_sex =" + Rs. getstring (3) + "\ ta_date =" + Rs. getdate (4) + "\ ta_money" + Rs. getdouble (5 ));
System. out. println ("-************************************ *****************************--------------********** *");
System. out. println ("a_id =" + Rs. getint ("a_id") + "\ ta_name =" + Rs. getstring ("a_name") + "\ ta_sex =" + Rs. getstring ("a_sex") + "\ ta_date =" + Rs. getdate ("a_date") + "\ ta_money" + Rs. getdouble ("a_money "));
}
} Catch (exception E)
{
E. printstacktrace ();
}
Finally
{
DB. closecon ();
}
} /**
* @ Param ARGs
*/
Public static void main (string [] ARGs)
{
Testaa TA = new testaa ();
// Ta. insertaa ("Zhu laosan", "male", 99.63 );
// Ta. insertaa ("Zhu chuanwen", "male", 869.63 );
// Ta. insertaa ("Wang xiaoya", "female", 299.63 );
// Ta. insertaa ("Wang Dana", "male", 199.63 );
// Ta. deleteaa (9 );
Ta. updateaa (5, "Mei", "female", 594.21); // -- the problem persists.
Ta. selectsomebyid (5 );
// Ta. selectallaa (); } } |
