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;