Oracle数据库创建存储过程的示例详解

create [ or replace ] procedure 存储过程名（param1 in type，param2 out type）

as

  变量1 类型（值范围）;

  变量2 类型（值范围）;

begin

  select count (*) into 变量1 from 表A where 列名=param1；

  if (判断条件) then

  select 列名 into 变量2 from 表A where 列名=param1；

  dbms_output.Put_line( '打印信息' );

  elsif (判断条件) then

  dbms_output.Put_line( '打印信息' );

  else

  raise 异常名（NO_DATA_FOUND）;

  end if;

exception

  when others then

  rollback ;

end ;

CREATE OR REPLACE PROCEDURE MYDEMO02

AS

  name VARCHAR (10);

  age NUMBER(10);

BEGIN

  name := 'xiaoming' ; --:=则是对属性进行赋值

  age := 18;

  dbms_output.put_line ( 'name=' || name || ', age=' || age ); --这条是输出语句

END ;

--存储过程调用(下面只是调用存储过程语法)

BEGIN

  MYDEMO02();

END ;

CREATE OR REPLACE procedure MYDEMO03( name in varchar ,age in int )

AS

BEGIN

  dbms_output.put_line( 'name=' || name || ', age=' ||age);

END ;

--存储过程调用

BEGIN

  MYDEMO03( '姜煜' ,18);

END ;

CREATE OR REPLACE PROCEDURE MYDEMO04

AS

  age INT;

BEGIN

  age:=10/0;

  dbms_output.put_line(age);

EXCEPTION when others then --处理异常

  dbms_output.put_line('error');

END;

--调用存储过程

BEGIN

  MYDEMO04;

END;

CREATE OR REPLACE PROCEDURE TEST_COUNT01

IS

  v_total int ;

  v_date varchar (20);

BEGIN

  select count (*) into v_total from EMP_TEST WHERE ENAME = '燕小六' ; --into是赋值的关键字

  select to_char(sysdate, 'yyyy-mm-dd' ) into v_date FROM EMP_TEST WHERE ENAME = '郭芙蓉' ;

  DBMS_OUTPUT.put_line( '总人数:' ||v_total);

  DBMS_OUTPUT.put_line( '当前日期' ||v_date);

END ;

--调用存储过程

BEGIN

  TEST_COUNT01();

END ;

CREATE OR REPLACE PROCEDURE TEST_COUNT04(v_id in int ,v_name out varchar2)

IS

BEGIN

  SELECT ENAME into v_name FROM EMP_TEST WHERE EMPNO = v_id;

  dbms_output.put_line(v_name);

EXCEPTION

  when no_data_found then dbms_output.put_line( 'no_data_found' );

END ;

--调用存储过程

DECLARE

  v_name varchar (200);

BEGIN

  TEST_COUNT04( '1002' ,v_name);

END ;

CREATE OR REPLACE PROCEDURE job_day04(de in varchar,name out varchar,App_Code out varchar,error_Msg out varchar)

AS

BEGIN

  SELECT ENAME into name FROM EMP_TEST WHERE ENAME=de;

EXCEPTION WHEN others THEN

  error_Msg:= '未找到数据' ;

END;

--调用存储过程

DECLARE

   de varchar( 10 );

   ab varchar( 10 );

    appcode varchar( 20 );

    ermg varchar( 20 );

BEGIN

    de:= '张三丰' ;

    JOB_DAY04(de,ab,appcode,ermg);

    dbms_output.put_line(ermg);

END;

CREATE OR REPLACE PROCEDURE job_day05(do1 in varchar ,dn1 in varchar ,eo1 in number,en1 in varchar ,App_Code out varchar ,error_Msg out varchar )

AS

BEGIN

  INSERT INTO STUDENT( NAME ,CLASS) VALUES (do1,dn1);

  INSERT INTO COMPANY(EMPID, NAME ,DEPARNAME) VALUES (eo1,en1,do1);

COMMIT ;

EXCEPTION WHEN OTHERS THEN

  App_Code:=-1;

  error_Msg:= '插入失败' ;

END ;

--调用存储过程

DECLARE

   do1 varchar (10);

   dn1 varchar (10);

    eo1 number(20);

    App_Code varchar (20);

  error_Msg varchar (20);

BEGIN

    do1:= '张三丰' ;

  dn1:= '新桥' ;

  eo1:=1001;

    JOB_DAY04(do1,dn1,App_Code,error_Msg);

    dbms_output.put_line(ermg);

END ;

CREATE OR REPLACE PROCEDURE job_day06(epo in number)

  AS

BEGIN

  UPDATE EMPS SET SAL=(SAL+100) WHERE empno = epo;

  IF SQL%FOUND --SQL%FOUND是隐式游标 作用:判断SQL语句是否成功执行,当有作用行时则成功执行为true，否则为false。 6 THEN

  DBMS_OUTPUT.PUT_LINE( '成功修改雇员工资！' );

  commit ;

  else

  DBMS_OUTPUT.PUT_LINE( '修改雇员工资失败！' );

  END IF;

END ;

--调用存储过程

declare

e_number number;

begin

e_number:=1001;

job_day06(e_number);

end ;

CREATE OR REPLACE PROCEDURE job_day07

IS

BEGIN

DECLARE

  cursor emp_sor is select name ,sal FROM EMPS WHERE EMPNO = '1001' ; --声明游标

  cname EMPS. NAME %type; --%type 作用: 声明的变量ename与EMPS表的NAME列类型一样

  csal EMPS.SAL%type;

BEGIN

  open emp_sor; --打开游标

  loop

-- 取游标值给变量

  FETCH emp_sor into cname,csal;

  dbms_output.put_line( 'name:' ||cname);

  exit when emp_sor%notfound;

  end loop;

  close emp_sor; --关闭游标

end ;

end ;

--调用存储过程

BEGIN

  job_day07();

END ;