Oracle 存储过程之增删改查

1、创建数据库表emp

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    minextents 1    maxextents unlimited  );-- 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    minextents 1    maxextents unlimited  );alter table EMP  add constraint FK_DEPTNO foreign key (DEPTNO)  references DEPT (DEPTNO);

2、存储过程基本格式

CREATE PROCEDURE 存储过程名字

IS

BEGIN

    --执行的内容

END 存储过程名字;

3、存储过程：插入数据（无参/有参）

--插入数据（无参）存储过程 emp_insert_proccreate or replace procedure emp_insert_proc isbegin  INSERT INTO emp VALUES(7779,'Hello Java','SALESMAN',7839,SYSDATE,1800,4000,30);   dbms_output.put_line('添加数据的id： '||'7779');end emp_insert_proc ;--执行存储过程 emp_insert_procbegin emp_insert_proc;end;

--插入数据（有参）存储过程 emp_insert_para_proccreate or replace procedure emp_insert_para_proc(myempno NUMBER, myename VARCHAR2, myjob VARCHAR2, mymgr NUMBER, mydate DATE, mysal NUMBER, mycomm NUMBER, mydeptno NUMBER)isbegin  INSERT INTO emp VALUES(myempno,myename,myjob,mymgr,mydate,mysal,mycomm,mydeptno);  dbms_output.put_line('插入数据的id：'||myempno);end emp_insert_para_proc;-- 调用插入数据有参存储过程begin   emp_insert_para_proc(111,'三九','坑爹',7689,SYSDATE,1800,4000,30);                       end;

4、存储过程：删除数据

--创建存储过程：按照id删除记录create or replace procedure emp_del_proc(myemp NUMBER) isbegin  DELETE FROM emp WHERE empno=myemp;  dbms_output.put_line('被删除的记录的empno='||myemp); end emp_del_proc;-- 调用存储过程begin  -- Test statements here  emp_del_proc(111);  commit;end;

5、存储过程：修改数据

--更新数据存储过程--按照id修改名字create or replace procedure emp_update_proc(myempno IN NUMBER, myename IN VARCHAR2 ) isbegin  UPDATE emp SET ename=myename WHERE empno=myempno;   dbms_output.put_line('被修改的数据的myempno='||myempno); end emp_update_proc;--调用修改数据存储过程--按照id修改名字存储过程begin  emp_update_proc(111,'改后名');  commit;end;

6、存储过程：查询数据

--创建存储过程：按照id查询单条数据create or replace procedure emp_selectnamebyid_proc(myempno in number,myname out varchar2) isbegin  SELECT ename into myname FROM emp WHERE empno=myempno;  --dbms_output.put_line('被查询的ename='||myname); end emp_selectnamebyid_proc;-- 调用 查询存储过程emp_selectnamebyid_procdeclare   -- Local variables here  myname VARCHAR2(50);  begin  -- Test statements here  emp_selectnamebyid_proc(123,myname);end;

-- 创建存储过程：查询所有的结果（多行用游标显示）create or replace procedure emp_selectempAll_proc   is  CURSOR sel_emp IS SELECT * FROM emp;--定义游标,该游标指向查询结果  rowresult emp%ROWTYPE;begin  OPEN sel_emp;--打开游标  LOOP FETCH sel_emp INTO rowresult;--将游标中的值赋给rowresult       EXIT WHEN sel_emp%NOTFOUND;--判断：游标不存在时跳出循环       dbms_output.put_line('员工名ename='||rowresult.ename||', 工资sal='||rowresult.sal);   END LOOP;  CLOSE sel_emp;--关闭游标           end emp_selectempAll_proc  ;-- 调用存储过程：查询所有的结果（多行用游标显示）begin   emp_selectempall_proc;end;