"Go" a few common Oracle stored procedures

http://blog.bossma.cn/database/some-oracle-storing-process/Several common Oracle stored procedures

Date: January 6, 2008/Category: DataBase/ 7,268 views/Comments

Beginner Oracle, learn about Oracle's stored procedures, run a few examples, and make a memo to facilitate later use:
1. Insert Data:
Insert several data into a table

0123456789101112131415161718192021222324252627282930313233

/*Create a table tb1contains two fields col1, col2*/Create table tb1( col1 Char(+), col2 Char(+)); / * stored procedure for inserting data * /CREATE OR REPLACE PROCEDURE insertamounttest (st_num in number,/* Start value * /ed_num in number/* End value * /) isBEGINDeclare I number ; begin/ * Loop Insert * /for i in st_num. ed_num LOOP INSERT into tb1 values(i,' Test '); END LOOP; end; commit; / * Exception handling * /EXCEPTIONwhen OTHERS Thenrollback; END; / * Execute under Commondline * /exec insertamounttest(1,+);

2. Update data

0123456789101112131415

CREATE OR REPLACE PROCEDURE updateamounttest (where_num in VARCHAR2/*sql Statement condition value * /) isBEGINUPDATE tb1 SET col2=' test2 ' where col1 like where_num| | '% '; / * Exception handling * /EXCEPTIONwhen OTHERS Thenrollback; END; / * Execute under Commondline * /exec updateamounttest(' one ');

3. Calls between stored procedures
Update with a query as an example

C #

0123456789101112131415161718192021222324252627282930313233343536

/*query Update stored procedure, call Update stored procedure UPDATEAMOUNTTEST2*/CREATE OR REPLACE PROCEDURE selectupdatetest (tjstr in CHAR ) is upstr CHAR(+); begin/*querying for field values to update*/Select a. Col2 to upstr from tb1 a where a. col1 = tjstr; /*call the update stored proceduretwo parameters passed in for update*/UPDATEAMOUNTTEST2(' One ',upstr); end selectupdatetest; /*updated stored procedure, called by Selectupdatetest*/CREATE OR REPLACE PROCEDURE UPDATEAMOUNTTEST2 (where_num in VARCHAR2,/* Update condition * /set_name in VARCHAR2/* Updated field values * /) isBEGINUPDATE tb1 SET col2=set_name where col1 like where_num| | '% '; EXCEPTIONwhen OTHERS Thenrollback; END;

"Go" a few common Oracle stored procedures