oracle创建和调试存储过程

迷南。 2022-04-23 12:02 231阅读 0赞

**目录**

一、添加存储过程

二、执行调试

三、附录-建表语句

--------------------

### 一、添加存储过程 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70][]

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语句创建

### 二、执行调试 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 1][]

或者

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 3][]

![20190219101429360.png][]

`grant`  `DEBUG``CONNECT` `SESSION ``to` `scott;`

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 4][]

`单步执行`

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 5][]

`输出信息`

![20190219102834597.png][]

结果查看

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 6][]

### 三、附录-建表语句 ###

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70]: /images/20220319/2647fe94a6ed4b6aa2efe9e9709ad759.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 1]: /images/20220319/e458eee2a18749a48bf85773acd27b72.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 2]: /images/20220319/97167bebc50f411698a4c7b0c54d7002.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 3]: /images/20220319/9e89308366ea48209aa09028d051a27a.png
[20190219101429360.png]: /images/20220319/166c4d4e241b48df9fada6de78b944e4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 4]: /images/20220319/31e6d0138a0d46d09e30dd2579f4c5cd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 5]: /images/20220319/79886ee7ef0d45a3b51a5c8d6435d330.png
[20190219102834597.png]: /images/20220319/bfdea140e87e4783819803de9502636a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 6]: /images/20220319/8b0a62bcafb04c0caa7d58b94962b285.png