DB: Oracle Tool: SQL Developer.
创建存储过程 :
CREATE OR REPLACE PROCEDURE procedure_name ( param IN NUMBER, param2 IN VARCHAR2, retValue OUT NUMBER) IS
-- declare variable to be used inside the SP --
v_variable_1 NUMBER;
v_variable_2 VARCHAR2;
...
-- execute sql statements , must start with BEGIN and end with END --
BEGIN
v_variable_2 :='www.google.com';
--sql statements ; --
BEGIN
SELECT SITE_ID INTO v_variable_1 FROM SITE WHERE UPPER(SITE_URL) =UPPER(v_variable_2);
DBMS_OUTPUT.PUT_LINE('site id is :'|| v_variable);
EXCEPTION
WHEN NO DATA FOUND THEN
v_variable_1 :=0 ;
END;
--any other sql statements --
EXCEPTION
-- handle exceptions here --
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error encountered:'||SQLERRM(SQLCODE));
ROLLBACK;
END;
/
1. IN 表示传入参数, OUT表示SP执行后的返回值(如果需要SP带回一个返回值).
2.在存储过程的中需要的变量定义(声明)时不用Declare关键字,直接定义即可.
3. 执行sql语句必须以BEGIN开始,END结束. EXCEPTION部分表示异常处理.
4. 若上面的select into语句中没有查询到site_id ,则会产生no data found exception , 此时要有异常处理语句处理该异常,否则,SP将终止执行!