-- create the PL/SQL functions, procedures and packagesCREATE PROCEDURE update_product_price( p_product_id IN products.id%TYPE, p_factor IN NUMBER) AS product_count INTEGER;BEGIN -- count the number of products with the -- supplied id (should be 1 if the product exists) SELECT COUNT(*) INTO product_count FROM products WHERE id = p_product_id; -- if the product exists (product_count = 1) then -- update that product's price IF product_count = 1 THEN UPDATE products SET price = price * p_factor; COMMIT; END IF;END update_product_price; / CREATE FUNCTION update_product_price_func( p_product_id IN products.id%TYPE, p_factor IN NUMBER) RETURN INTEGER AS product_count INTEGER;BEGIN SELECT COUNT(*) INTO product_count FROM products WHERE id = p_product_id; -- if the product doesn't exist then return 0, -- otherwise perform the update and return 1 IF product_count = 0 THEN RETURN 0; ELSE UPDATE products SET price = price * p_factor; COMMIT; RETURN 1; END IF;END update_product_price_func; / -- package ref_cursor_package illustrates the use of the -- REF CURSOR type CREATE OR REPLACE PACKAGE ref_cursor_package AS TYPE t_ref_cursor IS REF CURSOR; FUNCTION get_products_ref_cursor RETURN t_ref_cursor;END ref_cursor_package; /CREATE PACKAGE BODY ref_cursor_package AS -- function get_products_ref_cursor() returns a REF CURSOR FUNCTION get_products_ref_cursor RETURN t_ref_cursor IS products_ref_cursor t_ref_cursor; BEGIN -- get the REF CURSOR OPEN products_ref_cursor FOR SELECT id, name, price FROM products; -- return the REF CURSOR RETURN products_ref_cursor; END get_products_ref_cursor;END ref_cursor_package; /调用: /* PLSQLExample1.java shows how to call a PL/SQL procedure and function */// import the JDBC packages import java.sql.*;public class PLSQLExample1 { public static void main(String args []) throws SQLException { // register the Oracle JDBC drivers DriverManager.registerDriver( new oracle.jdbc.OracleDriver() ); // create a Connection object, and connect to the database // as store_user using the Oracle JDBC Thin driver Connection myConnection = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:OCL", "store_user", "store_password" ); // disable auto-commit mode myConnection.setAutoCommit(false); // create a Statement object Statement myStatement = myConnection.createStatement(); // display product #1's id and price System.out.println("Id and original price"); displayProduct(myStatement, 1); // create a CallableStatement object to call the // PL/SQL procedure update_product_price() CallableStatement myCallableStatement = myConnection.prepareCall( "{call update_product_price(?, ?)}" ); // bind values to the CallableStatement object's parameters myCallableStatement.setInt(1, 1); myCallableStatement.setDouble(2, 1.1); // execute the CallableStatement object - this increases the price // for product #1 by 10% myCallableStatement.execute(); System.out.println("Increased price by 10%"); displayProduct(myStatement, 1); // call the PL/SQL function update_product_price_func() myCallableStatement = myConnection.prepareCall( "{? = call update_product_price_func(?, ?)}" ); // register the output parameter, and bind values to // the CallableStatement object's parameters myCallableStatement.registerOutParameter(1, java.sql.Types.INTEGER); myCallableStatement.setInt(2, 1); myCallableStatement.setDouble(3, 0.8); // execute the CallableStatement object - this decreases the new // price for product #1 by 20% myCallableStatement.execute(); int result = myCallableStatement.getInt(1); System.out.println("Result returned from function = " + result); System.out.println("Decreased new price by 20%"); displayProduct(myStatement, 1); // reset the price back to the original value myStatement.execute( "UPDATE products " + "SET price = 19.95" + "WHERE id = 1" ); myConnection.commit(); System.out.println("Reset price back to 19.95"); // close the JDBC objects myCallableStatement.close(); myStatement.close(); myConnection.close(); } // end of main() public static void displayProduct( Statement myStatement, int id ) throws SQLException { // display the id and price columns ResultSet productResultSet = myStatement.executeQuery( "SELECT id, price " + "FROM products " + "WHERE id = " + id ); productResultSet.next(); System.out.println("id = " + productResultSet.getInt("id")); System.out.println("price = " + productResultSet.getDouble("price")); productResultSet.close(); } // end of displayProduct()}
p_factor IN NUMBER) AS product_count INTEGER;BEGIN -- count the number of products with the
-- supplied id (should be 1 if the product exists)
SELECT
COUNT(*)
INTO
product_count
FROM
products
WHERE
id = p_product_id; -- if the product exists (product_count = 1) then
-- update that product's price
IF product_count = 1 THEN
UPDATE
products
SET
price = price * p_factor;
COMMIT;
END IF;END update_product_price;
/
CREATE FUNCTION update_product_price_func( p_product_id IN products.id%TYPE,
p_factor IN NUMBER) RETURN INTEGER AS product_count INTEGER;BEGIN SELECT
COUNT(*)
INTO
product_count
FROM
products
WHERE
id = p_product_id; -- if the product doesn't exist then return 0,
-- otherwise perform the update and return 1
IF product_count = 0 THEN
RETURN 0;
ELSE
UPDATE
products
SET
price = price * p_factor;
COMMIT;
RETURN 1;
END IF;END update_product_price_func;
/
-- package ref_cursor_package illustrates the use of the
-- REF CURSOR type
CREATE OR REPLACE PACKAGE ref_cursor_package AS TYPE t_ref_cursor IS REF CURSOR;
FUNCTION get_products_ref_cursor RETURN t_ref_cursor;END ref_cursor_package;
/CREATE PACKAGE BODY ref_cursor_package AS -- function get_products_ref_cursor() returns a REF CURSOR
FUNCTION get_products_ref_cursor
RETURN t_ref_cursor IS products_ref_cursor t_ref_cursor; BEGIN -- get the REF CURSOR
OPEN products_ref_cursor FOR
SELECT
id, name, price
FROM
products; -- return the REF CURSOR
RETURN products_ref_cursor; END get_products_ref_cursor;END ref_cursor_package;
/调用:
/*
PLSQLExample1.java shows how to call a PL/SQL procedure
and function
*/// import the JDBC packages
import java.sql.*;public class PLSQLExample1 { public static void main(String args [])
throws SQLException { // register the Oracle JDBC drivers
DriverManager.registerDriver(
new oracle.jdbc.OracleDriver()
); // create a Connection object, and connect to the database
// as store_user using the Oracle JDBC Thin driver
Connection myConnection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:OCL",
"store_user",
"store_password"
); // disable auto-commit mode
myConnection.setAutoCommit(false); // create a Statement object
Statement myStatement = myConnection.createStatement(); // display product #1's id and price
System.out.println("Id and original price");
displayProduct(myStatement, 1); // create a CallableStatement object to call the
// PL/SQL procedure update_product_price()
CallableStatement myCallableStatement = myConnection.prepareCall(
"{call update_product_price(?, ?)}"
); // bind values to the CallableStatement object's parameters
myCallableStatement.setInt(1, 1);
myCallableStatement.setDouble(2, 1.1); // execute the CallableStatement object - this increases the price
// for product #1 by 10%
myCallableStatement.execute();
System.out.println("Increased price by 10%");
displayProduct(myStatement, 1); // call the PL/SQL function update_product_price_func()
myCallableStatement = myConnection.prepareCall(
"{? = call update_product_price_func(?, ?)}"
); // register the output parameter, and bind values to
// the CallableStatement object's parameters
myCallableStatement.registerOutParameter(1, java.sql.Types.INTEGER);
myCallableStatement.setInt(2, 1);
myCallableStatement.setDouble(3, 0.8); // execute the CallableStatement object - this decreases the new
// price for product #1 by 20%
myCallableStatement.execute();
int result = myCallableStatement.getInt(1);
System.out.println("Result returned from function = " + result);
System.out.println("Decreased new price by 20%");
displayProduct(myStatement, 1); // reset the price back to the original value
myStatement.execute(
"UPDATE products " +
"SET price = 19.95" +
"WHERE id = 1"
);
myConnection.commit();
System.out.println("Reset price back to 19.95"); // close the JDBC objects
myCallableStatement.close();
myStatement.close();
myConnection.close(); } // end of main()
public static void displayProduct(
Statement myStatement,
int id
) throws SQLException { // display the id and price columns
ResultSet productResultSet = myStatement.executeQuery(
"SELECT id, price " +
"FROM products " +
"WHERE id = " + id
);
productResultSet.next();
System.out.println("id = " + productResultSet.getInt("id"));
System.out.println("price = " + productResultSet.getDouble("price")); productResultSet.close(); } // end of displayProduct()}
insert,update,select,delete
insert的参数一般和表的字段数相同,如果有序列的话,可以少一个参数
其他的三个一般都按主键来操作
想要代码,留e_mail给我,我发给你