Oracle存储过程实例

/*不带任何参数存储过程(输出系统日期)*/
create or replace procedure output_date is
begin
dbms_output.put_line(sysdate);
end output_date;

/*带参数in和out的存储过程*/
create or replace procedure get_username(v_id in number,v_username out varchar2)
as
begin
  select username into v_username from tab_user where id = v_id; --变量赋值 
exception
when no_data_found then 
raise_application_error(-20001,'ID不存在!');
end get_username;

语法

CREATE[ORReplace]PROCEDURE[schema.]procedure_name
　　[(argument[{IN|OUT|INOUT}]datatype,
　　...
　　argument[{IN|OUT|INOUT}]datatype)]
　　{IS|AS}
　　[descriptionpart说明部分]
　　BEGIN
　　SQLSTATEMENT语句序列
　　[EXCEPTION例外处理]
　　END[procedureName过程名];

语法分析

 
 

  
  ORREPLACE
　　是一个可选的关键字，建议用户使用此关键字。如果过程已经存在，该关键字将重新创建过程，这样就不必删除和重新创建过程。
　　关键字IS和AS均可，
　　它们本身没有区别。IS后面是一个完整的
  
  PL/SQL块，可以定义
  
  局部变量，但不能以
  
  DECLARE开始。局部变量在过程内部存放值。
　　
  
  形式参数可以有三种模式：IN、OUT、INOUT。如果没有为形式参数指定模式，那么默认的模式是IN。
　　IN表示输入参数
　　OUT表示输出参数
 
 
 
 

  
  HelloWorld！
　　创建第一个
  
  存储过程HelloWorld
　　SQL>createorreplaceprocedurehelloworldasbegindbms_output.put_line('
  
  helloworld');end;
　　注：需要在存储过程输入完成后回车，下一行输入”/”回车，才会创建成功。
　　当提示Procedurecreated表示存储过程创建成功。
　　调用
  
  HelloWorld！
　　执行
　　SQL>setserveroutputon;
　　SQL>exec[ute]helloworld;
　　显示如下结果
　　
  
  helloworld
　　PL/SQLproceduresuccessfullycompleted
 
 
带参存储过程

 
 

  
  模式描述IN参数（默认模式）（输入参数）用来从调用环境中向
  
  存储过程传递值，不能给IN参数赋值，给此
  
  参数传递的值可以是常量、有值的变量、表达式等。
　　OUT参数（输出参数）用来从过程中返回值给调用者，不能将此参数的值赋给另一个变量，不能是常量或表达式。在过程体内，必须给OUT参数
  
  赋值。INOUT参数（输入输出参数）既可以从调用者向过程中传递值，执行过程后还可返回可能改变了的值给调用者。
 
 
 
 
模式
描述
IN参数（默认模式）（输入参数）
用来从调用环境中向存储过程传递值，不能给IN参数赋值，给此参数传递的值可以是常量、有值的变量、表达式等。
OUT参数（输出参数）
用来从过程中返回值给调用者，不能将此参数的值赋给另一个变量，不能是常量或表达式。在过程体内，必须给OUT参数赋值。
INOUT参数（输入输出参数）
既可以从调用者向过程中传递值，执行过程后还可返回可能改变了的值给调用者。
 
 

  
  模式
 
 
 
 

  
  描述
 
 
 
 

  
  用来从调用环境中向存储过程传递值，不能给IN参数
  
  赋值，
 
 
 
 

  
  给此
  
  参数传递的值可以是常量、有值的变量、表达式等。
 
 
 
 

  
  用来从过程中
  
  返回值给调用者，不能将此参数的值赋给另
 
 
 
 

  
  一个变量，不能是常量或表达式。在过程体内，必须给
 
 
 
 

  
  OUT参数赋值。
 
 
 
 

  
  既可以从调用者向过程中传递值，执行过程后还可返回可能
 
 
 
 

  
  改变了的值给调用者。
 
 
 
 

  
  
  
  
 
 
1、带参存储过程（输入参数）
 
 

  
  创建
  
  存储过程Hello‘Tom’
　　SQL>createorreplaceprocedurehelloTom(pnameinvarchar2)asbegindbms_output.put_line('hello'||pname||'!');end;
　　SQL>/
　　执行
　　SQL>setserveroutputon;
　　SQL>exechelloTom('jerry');
　　显示如下结果
　　hellojerry!
　　PL/SQLproceduresuccessfullycompleted
　　注：由于
  
  存储过程是高度过程化语言，所以‘+’为能像
  
  面向对象一样被
  
  重载为字符串相加，所以在存储过程语法中使用‘||’表示字符串相加操作。输入参数可省略in如(pname varchar2)
 
 
 
 

  
  
  
  
  
  
 
 
2、带参存储过程（输出参数）
 
 

  
  创建
  
  存储过程Write‘Tom’
　　SQL>createorreplaceprocedurewriteTom(pnameoutvarchar2)asbeginselectnameintopnamefromt1whererownum=1;end;
　　SQL>/
　　带输出参数存储过程的调用格式：
　　(1)绑定参数值
　　variable输出参数变量1,输出参数变量2…;
　　调用存储过程
　　EXEC[UTE]procedure_name(参数值1…参数名n,:
  
  绑定变量1,,:绑定变量2….);
　　SQL>variablepnamevarchar2(40);
　　SQL>execwriteTom(:pname);
　　带输出参数
  
  存储过程的调用格式：
　　(2)在程序块中调用存储过程
　　SQL>
  
  declare
　　2pnamevarchar2(40);
　　3begin
　　4writeTom(pname);
　　5dbms_output.put_line(pname);
　　6end;
　　7/
 
 
 
 

  
  
  
  
  
  
 
 
3、带参存储过程（inout参数）
 
 

  
  创建
  
  存储过程InoutTom向t1表插入一条记录
　　SQL>createorreplaceprocedureinoutTom(pnameinoutvarchar2)asbeginselectnameintopnamefromt1whereid=pname;end;
　　SQL>/
　　执行
　　SQL>
  
  declare
　　2pnamevarchar2(30):='1';
　　3begin
　　4inoutTom(pname);
　　5dbms_output.put_line(pname);
　　6end;
　　7/
　　注：
  
  赋值运算符为’:=’，用于给参数赋默认值
 
 
 
 

  
  
  
  
  
  
 
 
4、参数规则
 
 

  
  如果
  
  形式参数是IN模式的参数，
  
  实际参数可以是一个具体的值或一个有值的变量；
　　如果形式参数是OUT模式的参数，实际参数必须是一个变量，当调用过程后，此变量就被
  
  赋值了。可以输出此变量的值来测试过程执行的结果。
　　如果形式参数是INOUT模式的参数，则实际参数必须是一个预先已经赋值的变量。执行完过程后，该变量被重新赋值，可以输出此变量的值来测试过程执行结果。