創建一個存儲過程的語法
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN|OUT|IN OUT] type [,...])]
{IS | AS}
BEGIN
procedure_name
END procedure_name;
SQL> CREATE OR REPLACE PROCEDURE update_product_price (
2 p_product_id IN products.product_id%TYPE,3 p_factor IN NUMBER
4 ) AS
5 v_product_count INTEGER;
6 BEGIN
7 -- count the number of products with the
8 -- supplied product_id (should be 1 if the product exists)
9 SELECT COUNT(*)
10 INTO v_product_count
11 FROM products
12 WHERE product_id = p_product_id;
13
14 -- if the product exists (v_product_count = 1) then
15 -- update that product's price
16 IF v_product_count = 1 THEN
17 UPDATE products
18 SET price = price * p_factor
19 WHERE product_id = p_product_id;
20 COMMIT;
21 END IF;
22 EXCEPTION
23 WHEN OTHERS THEN
24 ROLLBACK;
25 END update_product_price;
26 /
已建立程序.
SQL> SELECT price
2 FROM products
3 WHERE product_id = 1;
PRICE
----------
19.95
SQL> CALL update_product_price(1,1.5);
已完成呼叫.
SQL> SELECT price
2 FROM products
3 WHERE product_id = 1;
PRICE
----------
29.93
SQL> SELECT object_name,aggregate,parallel
2 FROM user_procedures
3 WHERE object_name = 'UPDATE_PRODUCT_PRICE';
OBJECT_NAME AGG PAR
------------------------------ --- ---
UPDATE_PRODUCT_PRICE NO NO
SQL> DROP PROCEDURE update_product_price;
已刪除程序.
SQL> --查看過程中的錯誤
SQL> CREATE OR REPLACE PROCEDURE update_customer_dob (
2 p_customer_id INTEGER,p_dob DATE
3 ) AS
4 BEGIN
5 UPDATE customers
6 SET dob = p_dobs
7 WHERE customer_id = p_customer_id;
8 END update_customer_dob;
9 /
警告: 建立的程序含有編譯錯誤.
SQL> SHOW ERRORS;
PROCEDURE UPDATE_CUSTOMER_DOB 發生錯誤:
LINE/COL ERROR
-------- ----------------------------------------------------------------
5/2 PL/SQL: SQL Statement ignored
6/12 PL/SQL: ORA-00904: "P_DOBS": 無效的 ID
SQL> --更改過程中的錯誤
SQL> CREATE OR REPLACE PROCEDURE update_customer_dob (
2 p_customer_id INTEGER,p_dob DATE
3 ) AS
4 BEGIN
5 UPDATE customers
6 SET dob = p_dob
7 WHERE customer_id = p_customer_id;
8 END update_customer_dob;
9 /
已建立程序.