oracle创建和调试存储过程

目录

一、添加存储过程

二、执行调试

三、附录-建表语句

---

一、添加存储过程

create or replace procedure add_emp(empno    number,
                                    ename    varchar2,
                                    job      varchar2,
                                    mgr      NUMBER,
                                    hiredate DATE,
                                    sal      number,
                                    com      NUMBER,
                                    deptno   number) is
BEGIN
  dbms_output.put_line('添加员工信息');
  INSERT INTO emp
  VALUES
    (empno, ename, job, mgr, hiredate, sal, com, deptno);
  commit;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('添加员工失败');
end add_emp;
使用scott用户登录oracle，自带emp、dept表，如果没有可以使用附录sql语句创建

二、执行调试

或者

grant  DEBUG CONNECT SESSION to scott;

单步执行

输出信息

结果查看

三、附录-建表语句

EMP.SQL

-- Create table
create table EMP
(
  empno    NUMBER(4) not null,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column EMP.empno
  is '雇员编号';
comment on column EMP.ename
  is '雇员姓名';
comment on column EMP.job
  is '雇员职位';
comment on column EMP.mgr
  is '雇员对应的领导的编号';
comment on column EMP.hiredate
  is '雇员的雇佣日期';
comment on column EMP.sal
  is '雇员的基本工资';
comment on column EMP.comm
  is '雇员的奖金';
comment on column EMP.deptno
  is '雇员所在部门编号';
-- Create/Recreate primary, unique and foreign key constraints 
alter table EMP
  add constraint PK_EMP primary key (EMPNO)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table EMP
  add constraint FK_DEPTNO foreign key (DEPTNO)
  references DEPT (DEPTNO);

DEPT.SQL

-- Create table
create table DEPT
(
  deptno NUMBER(2) not null,
  dname  VARCHAR2(14),
  loc    VARCHAR2(13)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column DEPT.deptno
  is '部门编号';
comment on column DEPT.dname
  is '部门名称';
comment on column DEPT.loc
  is '部门所在位置';
-- Create/Recreate primary, unique and foreign key constraints 
alter table DEPT
  add constraint PK_DEPT primary key (DEPTNO)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

SALGRADE.SQL
-- Create table
create table SALGRADE
(
  grade NUMBER,
  losal NUMBER,
  hisal NUMBER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column SALGRADE.grade
  is '工资等级';
comment on column SALGRADE.losal
  is '此等级的最低工资';
comment on column SALGRADE.hisal
  is '此等级的最高工资';

BONUS.SQL
-- Create table
create table BONUS
(
  ename VARCHAR2(10),
  job   VARCHAR2(9),
  sal   NUMBER,
  comm  NUMBER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Add comments to the columns 
comment on column BONUS.ename
  is '雇员姓名';
comment on column BONUS.job
  is '雇员职位';
comment on column BONUS.sal
  is '雇员工资';
comment on column BONUS.comm
  is '雇员奖金';