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 '雇员奖金';