oracle存储过程学习（二）-增删改查

1.插入，判断某些外键是否存在，存在再添加数据

--插入
create or replace procedure ADD_PACT_TYPE(typeName in varchar2,parentId in number , userId in number,bidden in number)
is
--定义变量
countParent number(6):=0;
countUser number(6):=0;
begin
     select count(*) into countParent from crm_pact_type t where t.parent_id = parentId;
     select count(*) into countUser from sys_user s where s.user_id = userId;
     --判断都大于0才执行
     if(countParent>0 and countUser>0) then
       insert into  crm_pact_type(pact_type_id,pact_type_name,parent_id,create_user_id,create_datetime,forbidden) 
       values(crm_pact_type_id_seq.nextval,typeName,parentId,userId,sysdate,bidden);
       commit;
     end if;
end ;

2.更新

--更新
create or replace procedure I[DATE_PACT_TYPE(typeName in varchar2,bidden in number)
is
typeId number;
       cursor c is select t.pact_type_id  from crm_pact_type t where t.pact_type_name like '%'||typeName||'%';
begin
  --打开游标
     open c;
     loop
       --遍历 
       fetch c into typeId;
       exit when c%notfound;
         update crm_pact_type r set r.forbidden = bidden where r.pact_type_id = typeId ;  
     end loop;
     commit;
     close c;
end ;

3.删除

create or replace procedure DELETE_PACT_TYPE(typeName in varchar2)
is
typeId number;
       cursor c is select t.pact_type_id  from crm_pact_type t where t.pact_type_name like '%'||typeName||'%';
begin
  --打开游标
     open c;
     loop
       --遍历 
       fetch c into typeId;
       exit when c%notfound;
         delete crm_pact_type r  where r.pact_type_id = typeId ;  
     end loop;
     commit;
     close c;
end ;

4.简单的查询，in表示输入 ，out 表示输出

create or replace procedure SELECT_PACT_TYPE(typeId in number, tname out varchar2)
is
begin
       select t.pact_type_name into tname from crm_pact_type t where t.pact_type_id = typeId;
       exception
         when NO_DATA_FOUND then null;  
end SELECT_PACT_TYPE;

测试

declare
id number(6);
tname varchar2(50);
begin
  id := 12;
  select_pact_type(typeid => id,tname => tname);
  dbms_output.put_line('名称：'||tname);            
end;