创建存储过程示例

CREATE OR REPLACE PROCEDURE P_XT_TEST_ZWL IS   TESTVALUE VARCHAR2(500);   LS_ERR    VARCHAR2(500); --错误日志 BEGIN

  SELECT H.SQLXH INTO TESTVALUE FROM CX_CXDY H WHERE ROWNUM < 3;  COMMIT; EXCEPTION   WHEN OTHERS THEN

    LS_ERR := '错误：' || SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 500);  dbms_output.put_line(LS_ERR);   END;

 

调用

BEGIN
P_XT_TEST_ZWL;
END;

 

-- 创建输入参procedure:

drop procedure insert_student

create or replace procedure insert_student

(

  user_id Number,user_name varchar2,user_pass varchar2

)

as

begin

  insert into student values(user_id,user_name,user_pass);

end insert_student;

 

begin

  insert_student(1,'aaa','bbb');

  commit;

end;

 

创建输出参procedure:

drop procedure proc_test2

create or replace procedure proc_test2

(

  invalue out varchar2

)

as

begin

select user_name into invalue from user_info where user_id = 1001;

end proc_test2;

/

 

declare   

parameter varchar2(20);

begin

  proc_test2(parameter);

  dbms_output.put_line(parameter);

end;

 

--既有输入，又有输出

create or replace procedure proc_test3

(

  invalue in Integer,outvalue out varchar2

)

as

begin

select user_name into outvalue from user_info where user_id = invalue;

end proc_test3;

/

 

declare

parameter Integer := 1001;   

parameter1 varchar2(20);

begin

  proc_test3(parameter,parameter1);

  dbms_output.put_line(parameter1);

end;

转载于:https://www.cnblogs.com/zwl715/p/3739412.html