oracle存储过程

查错

show errors;

查已建成的存储过程

select * from user_source where name=‘TESTINFO‘ order by line;    --存储过程名要大写

调用

exec testinfo;


--程序块中调用testinfo
--set serverout on
begin
  testinfo
end;

/**存储过程参数过程包括：in 输入参数、out 输出参数、in out可被修改的输入参数，并作为输出参数**/

in

create or replace procedure testinfo(v_id in int,v_name in varchar2)
  is
     begin
        insert into info values(v_id,v_name,‘6000‘，‘中国’);
        commit;
        dbms_output.put_line(‘插入新记录成功‘);
end;

--不按顺序传入参数，指定参数值
begin
  testinfo(v_name=>‘小米‘,v_lid=>‘210000‘);
end;

--按顺序传入参数
begin
  testinfo(210000‘’，‘小米‘);
end;

--混合传入参数
begin
 pro_insertDept(210000,v_name=>‘小米‘);
end;

out

create or replace procedure testinfo(v_id in int,v_name out info.name%type)
  as
     begin
        select name into v_name from info where id=v_id;
end;

declare
testname info.name%type;          
begin
  testinfo(‘210000‘,testname)
  dbms_output.put_line(testname);
end;

in out

create or replace procedure square(num in out int)
as 
 begin
   num:=powre(num,2)
 end;

declare
  n_num int;
  n_number int;
begin
     n_num:=5;
     n_number:=n_num;
     square(n_number);
     dbms_output.put_line(n_num||‘平方是‘||n_number);
end;

oracle存储过程

标签：char   基本   code   插入   包括   输入   serve   info   程序