--in 代表输入参数,out 代表输出参数
create or replace procedure myproc(id in int, v_message out varchar2) is
--定义临时变量number a;
begin
deletefrom emp where empno=id;
commit;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
v_message := '错误行号:' || DBMS_UTILITY.format_error_backtrace () || '错误代码:'|| SQLCODE|| '错误提示'||SQLERRM;
DBMS_OUTPUT.put_line (v_message);
end myproc;
调用
declare
ids number:=1;
ma varchar(20):='';
begin
myproc(ids,ma);
dbms_output.put_line(ma);
end;
也可以在cmd中直接调用
如果要查询执行时间,可以先执行
set time on;
set timing on;
使用oracle 数据库自带表
scott 密码 tiger
如果不能登录
执行
alter user scott account unlock
oracle 包
create or replace package mypack is
-- Author : PC
-- Created : 2018/4/25 10:23:03
-- Purpose :
procedure myproc(id in int, v_message out varchar2);
end mypack;
/
create or replace package body mypack is
procedure myproc(id in int, v_message out varchar2) is
a number;
begin
delete from emp where empno=id;
commit;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
v_message := '错误行号:' || DBMS_UTILITY.format_error_backtrace () || '错误代码:'|| SQLCODE|| '错误提示'|| SQLERRM;
DBMS_OUTPUT.put_line (v_message);
end myproc;
end mypack;
/
执行包
declare
ids int:=7369;
ma varchar(20):='';
begin
mypack.myproc(ids,ma);
dbms_output.put_line(ma);
end;