求oracle数据库存储过程的增删改查语句

Question

  因为项目要用到存储过程，所以小弟我求增删改查例子的存储语句，以借鉴到项目里！！！
 
  表就用 emp  
   字段就用 
             empno number 主键，
             empname varchar2， 
             deptno number 外键。   谢好心人啊！！！

Answer 1

CREATE OR REPLACE PROCEDURE liaisonDynamic_inc_proc(
  i_MobileNumber VARCHAR2, 
  i_type NUMBER,
  i_content VARCHAR2,
  i_id NUMBER
)
IS
BEGIN
  -- 0: 圈子 --
  IF i_type=0 THEN
  BEGIN
    INSERT INTO liaisonDynamic(DymicID,ID,GroupID,MobileNumber,type,CreationDate, GroupOwnMobileNumber,Content)
    SELECT liaisonDynamic_seq.nextval DymicID, lg.GroupID ID, lg.GroupID, lmg.MobileNumber, 0 type, systimestamp CreationDate,
           lg.MobileNumber GroupOwnMobileNumber, i_content Content
      FROM liaisonGroups lg join liaisonMemberGroups lmg on lg.GroupID=lmg.GroupID
     WHERE lg.Status=0
       AND lmg.MobileNumber<>i_MobileNumber
       AND lmg.InviteStatus=0;
  END;
  -- 1: 活动 --
  ELSIF i_type=1 THEN
  BEGIN
    INSERT INTO liaisonDynamic
(DymicID,ID,GroupID,MobileNumber,type,CreationDate,GroupOwnMobileNumber,DoingMobileNumber,DoingNickName,DoingTopic,DoingStartTime,
                               DoingInviteCount, DoingParticipateCount, DoingLocation, 
                               --DoingInviteStatus, 
                               DoingPostNum)
    with lspc AS(SELECT COUNT(1) PostNum FROM liaisonPost WHERE ThreadID=i_ID AND TypeNo=i_type)
    SELECT liaisonDynamic_seq.nextval DymicID, ld.DoingID ID, ld.GroupID, lmg.MobileNumber, 1 type, systimestamp CreationDate, 
           lg.MobileNumber GroupOwnMobileNumber, ld.MobileNumber DoingMobileNumber, lm.Nickname DoingNickName, ld.Topic DoingTopic, 
           ld.StartTime DoingStartTime, ld.InviteCount DoingInviteCount, ld.ParticipateCount DoingParticipateCount, 
           ld.Location DoingLocation, 
           --lmd.InviteStatus DoingInviteStatus, 
           lspc.PostNum DoingPostNum
    FROM  liaisonGroups lg join liaisonDoing ld  on ld.GroupID=lg.GroupID
          join liaisonMemberGroups lmg on lmg.GroupID=lg.GroupID
          --join liaisonMemberDoing lmd on lmd.DoingID=ld.DoingID AND lmd.MObileNumber=lmg.MobileNumber
          join liaisonMember lm on lm.MobileNumber=ld.MobileNumber
          left join lspc on 1=1
    WHERE ld.DoingID=i_ID
      AND lg.Status=0
      AND lmg.InviteStatus=0
      AND lmg.MobileNumber<>i_MobileNumber;
  END;
  -- 2: 投票 --
  ELSIF i_type=2 THEN
  BEGIN
    INSERT INTO liaisonDynamic(DymicID,ID,GroupID,MobileNumber,type,CreationDate,GroupOwnMobileNumber,VoteMobileNumber,VoteNickName,VoteTopic,VoteEndTime,
                               VoteParticipateCount,VoteMaxVoteTitle, VoteMinVoteTitle,VotePostNum)
    with lvimax as( SELECT t1.title VoteMaxVoteTitle FROM liaisonVoteItems t1
                     WHERE t1.VoteID=i_ID 
                       AND exists (select 1 from liaisonVoteItems t2 
                                    where t2.VoteID=i_ID and t2.title=t1.title 
                                    group by t2.title 
                                   having max(t2.CatchingCount)=CatchingCount
                                      and min(t2.CatchingCount)<>max(t2.CatchingCount))
                  ),
         lvimin as( SELECT t1.title VoteMinVoteTitle FROM liaisonVoteItems t1
                     WHERE t1.VoteID=i_ID 
                       AND exists (select 1 from liaisonVoteItems t2 
                                    where t2.VoteID=i_ID and t2.title=t1.title 
                                    group by t2.title 
                                   having min(t2.CatchingCount)=CatchingCount 
                                      and min(t2.CatchingCount)<>max(t2.CatchingCount))
                  ),
        lvimax2 as( SELECT t3.VoteMaxVoteTitle from lvimax t3 where rownum=1),
        lvimin2 as( SELECT t4.VoteMinVoteTitle from lvimin t4 where rownum=1),
        lspc as(SELECT COUNT(1) VotePostNum FROM liaisonPost WHERE ThreadID=i_ID AND TypeNo=i_type)
    SELECT liaisonDynamic_seq.nextval DymicID, lv.VoteID ID, lv.GroupID, lmg.MobileNumber, 2 type, systimestamp CreationDate,
           lg.MobileNumber GroupOwnMobileNumber, lv.MobileNumber VoteMobileNumber, lm.NickName VoteNickName, lv.Topic VoteTopic,
           lv.EndTime VoteEndTime, lv.ParticipateCount VoteParticipateCount, lvimax2.VoteMaxVoteTitle, lvimin2.VoteMinVoteTitle, lspc.VotePostNum
      FROM liaisonGroups lg join liaisonVote lv on lv.GroupID=lg.GroupID
           join liaisonMemberGroups lmg on lmg.GroupID=lg.GroupID
           join liaisonMember lm on lm.MobileNumber=lv.MobileNumber
           left join lvimax2 on 1=1 
           left join lvimin2 on 1=1
           left join lspc on 1=1
    WHERE lv.VoteID=i_ID
      AND lg.Status=0
      AND lmg.InviteStatus=0
      AND lmg.MobileNumber<>i_MobileNumber;
  END;
  -- 3: 讨论 --
  ELSIF i_type=3 THEN
  BEGIN
    INSERT INTO liaisonDynamic(DymicID,ID,GroupID,MobileNumber,type,CreationDate,GroupOwnMobileNumber,ThreadMobileNumber,
                               ThreadNickName,ThreadTopic,ThreadPhotosUrl,ThreadPostNum)
    WITH lspc AS(SELECT COUNT(1) ThreadPostNum FROM liaisonPost WHERE ThreadID=i_ID AND TypeNo=i_type)
    SELECT liaisonDynamic_seq.nextval DymicID, lt.ThreadID ID, lt.GroupID, lmg.MobileNumber, 3 type, systimestamp CreationDate, lg.MobileNumber GroupOwnMobileNumber,
           lt.MobileNumber ThreadMobileNumber, lm.Nickname ThreadNickName, lt.Topic ThreadTopic, lt.PhotosUrl ThreadPhotosUrl, lspc.ThreadPostNum
      FROM liaisonGroups lg join liaisonThread lt on lt.GroupID=lg.GroupID
           join liaisonMemberGroups lmg on lmg.GroupID=lg.GroupID
           join liaisonMember lm on lm.MobileNumber=lt.MobileNumber
           left join lspc on 1=1
    WHERE lt.ThreadID=i_ID
      AND lg.Status=0
      AND lmg.InviteStatus=0
      AND lmg.MobileNumber<>i_MobileNumber;
  END;
  END IF;  COMMIT;
END;
/

Answer 2

-- i_address 是存储过程的输入参数，o_cur是存储过程的输出游标参数，用以获取返回的结果集!
CREATE OR REPLACE PROCEDURE userinfo_proc(i_address VARCHAR2, o_cur OUT SYS_REFCURSOR)
IS
  sqlstr VARCHAR2(200); -- 定义变量，用以存放SQL语句
BEGIN
  sqlstr := 'SELECT Id, Name, Sex, Age, Address FROM userinfo WHERE Address = :i_address'; -- 给SQL变量赋值，其中 :i_address 是绑定变量，以提高执行效率!
  OPEN o_cur FOR sqlstr USING i_address; -- 给游标变量赋值
END;
/set serveroutput on;
var c_cur refcursor;
exec userinfo_proc('北京',:c_cur);
print c_cur;

Answer 3

create  or  replace   procedure  kk(kk in  number,mm  in  varchar2)
as 
   cursor   c_kk  is  
       select   *  from  kk;--游标
   v_kk   c_kk%rowtype;begin
         --写你的增删改查语句  end;  
  

Answer 4

select * from emp;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3100.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
 6688 yeexun     clerk      7782 2011-1-13     2000.00    100.00     40
--根据提供员工编号查询员工信息
create or replace procedure pro_show_employee(empno_in in emp.empno%type)
as 
   v_sign number;
   v_empno emp.empno%type;
   v_ename emp.ename%type;
   v_deptno emp.deptno%type;
begin
   select 1 into v_sign
   from dual
   where exists(select count(*) from emp where empno=empno_in);
   if v_sign=1 then
      select empno,ename,deptno into v_empno,v_ename,v_deptno
      from emp where empno=empno_in;
      dbms_output.put_line('information of '||empno_in||' are:');
      dbms_output.put_line('empno:'||v_empno||',ename:'||v_ename||',deptno:'||v_deptno);
   end if;
   exception
            when others then
            dbms_output.put_line('no data found');
end pro_show_employee;
--
SQL> exec pro_show_employee(7369);
information of 7369 are:
empno:7369,ename:SMITH,deptno:20
SQL> exec show_employee(112);
no data found
--
--根据提供的empno和sal跟新员工薪水。
create or replace procedure pro_update_emp(empno_in in emp.empno%type,sal_in in emp.sal%type)
as
  v_sign number;
begin
     select 1 into v_sign 
     from dual
     where exists(select count(*) from emp where empno=empno_in);
     if v_sign=1 then
        update emp
        set sal=sal_in
        where empno=empno_in;
        commit;
      end if;
      exception
               when others then
               dbms_output.put_line('employee not exists,check you empno.');
               rollback;
end pro_update_emp;
--
SQL> select * from emp where empno=7788;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20SQL> exec pro_update_emp(7788,3100);
PL/SQL procedure successfully completedSQL> select * from emp where empno=7788;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7788 SCOTT      ANALYST    7566 1987-4-19     3100.00               20
--
--根据提供员工empno，计算员工年薪
create or replace procedure pro_ann_sal(empno_in in emp.empno%type)
as
  v_sign number;
  v_ann_sal number;
begin
     select 1 into v_sign
     from dual
     where exists(select count(*) from emp where empno=empno_in);
     if v_sign=1 then
        select sal*12+nvl(comm,0)*12 into v_ann_sal
        from emp
        where empno=empno_in;
        dbms_output.put_line('the annual salary of employee '||empno_in||' are:'||v_ann_sal);
     end if;
     exception
              when others then
              dbms_output.put_line('employee not exists,check you empno.');    
end pro_ann_sal;
--
SQL> exec pro_ann_sal(7654);
the annual salary of employee 7654 are:31800SQL> exec pro_ann_sal(7788);
the annual salary of employee 7788 are:37200SQL> select sal,comm from emp where empno=7788;
      SAL      COMM
--------- ---------
  3100.00           3100*12=37200SQL> select sal,comm from emp where empno=7654;
      SAL      COMM
--------- ---------
  1250.00   1400.00  1250*12+1400*12=15000+16800=31800
--
--根据提供员工号empno删除他
create or replace procedure pro_delete_emp(empno_in in emp.empno%type)
as
  v_sign number;
begin
     select 1 into v_sign
     from dual
     where exists(select count(*) from emp where empno=empno_in);
     if v_sign=1 then
        delete from emp where empno=empno_in;
        commit;
     end if;
     exception
              when others then
              dbms_output.put_line('employee '||empno_in||' is not exists');
              rollback;
end pro_delete_emp;
--
SQL> select count(*) from emp where empno=6688;
  COUNT(*)
----------
         0

Answer 5

--根据提供员工号empno删除他
create or replace procedure pro_delete_emp(empno_in in emp.empno%type)
as
  v_sign number;
begin
     select 1 into v_sign
     from dual
     where exists(select count(*) from emp where empno=empno_in);
     if v_sign=1 then
        delete from emp where empno=empno_in;
        commit;
     end if;
     exception
              when others then
              dbms_output.put_line('employee '||empno_in||' is not exists');
              rollback;
end pro_delete_emp;
--
SQL> exec pro_delete_emp(6688);
PL/SQL procedure successfully completedSQL> select count(*) from emp where empno=6688;
  COUNT(*)
----------
         0