Oracle存储过程范例

Oracle存储过程实例

1.首先创建数据库

?

-- Create tablecreate table XUESHENG(  ID        INTEGER,  XING_MING VARCHAR2(25),  YU_WEN    NUMBER,  SHU_XUE   NUMBER)

?2.编写存储过程

?

create or replace procedure xs_detail(xs_name in varchar2,xs_info out varchar2) is       xdid number;       xsname varchar2(255);       xsyuwen number;       xsshuxue number;begin       select id,xing_ming,yu_wen,shu_xue into xdid,xsname,xsyuwen,xsshuxue from xuesheng where xing_ming=xs_name;       xs_info := 'ID:' || xdid || ' NAME:' || xsname || ' YU_WEN:' || xsyuwen || ' SHU_XUE:' || xsshuxue;       dbms_output.put_line(xs_info);end xs_detail;

?3.调用存储过程

?

declare  xs_name varchar2(55);  xs_info varchar2(255);begin  xs_name := 'lisi';  xs_detail(xs_name,xs_info);end;

执行结果：ID:2 NAME:lisi YU_WEN:85 SHU_XUE:87