orcl存储过程怎么写和调用

一、存储过程怎么写？

1.普通的声明变量存储过程

declare	--这个是调用，如果需要创建存储过程的话需要把declare改成创建语句，后面会写
v_char varchar2(100); --声明字符型变量
v_num number;	--声明数字型变量
v_tp dual.tp%TYPE;	--声明变量v_tp和dual表中的tp字段类型一样
begin
	 select har,num,tp into v_char,v_num,v_tp from dual where char = 'ca';
	 --将dual表中的har赋值给v_char,num赋值给v_num,tp赋值给v_tp
	 insert into dal(har,num,tp) values(v_har,v_num,v_tp);
	 --可执行增删查改这里
	 DBMS_OUTPUT.put_line(v_har||' '||v_num||' '||v_tp);
	 --执行的时候打印出v_har,v_num,v_tp
end;
--如果直接创建存储过程的话需要将declare改成如下代码
create or replace procedure dualtest --dualtest为自己存储过程的名字
is

2.带有if的存储过程

declare
  v_ename emp.ename%type:='&aa';
  n number;
begin
  select count(*) into n from emp where ename=v_ename;
  if n=0 then return 0;
  else return 1;
  end if;
end;
--如果直接创建存储过程的话需要将declare改成如下代码
create or replace procedure dualtest --dualtest为自己存储过程的名字
is

3.带有游标的存储过程
游标可以用来存储一个或多个字段的多个值，有点像java的集合，js的数组，具体使用如下

create or replace procedure delledlivery
is 
--或者将上面的改成declare
  cursor pk_cp is select vehicle from levm_cardmanage
  where (to_date(to_char(sysdate,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')-to_date(creationtime,'yyyy-mm-dd hh24-mi-ss'))>1
  and dr = '0' and vehicle<>'~';
  --创建游标pk_cp 
  cursor pk_confirm is select pk_comfirm,pk_delivery from levm_confirm
  where (outtime is null or outtime = '') and
  (to_date(to_char(sysdate,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')-to_date(intotime,'yyyy-mm-dd hh24-mi-ss'))>1
  and dr = '0';
   --创建游标pk_confirm 
  cursor pk_poundbill is select pk_poundbill from levm_poundbill where dr='0'
  and (nnet is null or nnet ='')
  and (to_date(to_char(sysdate,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')-to_date(creationtime,'yyyy-mm-dd hh24-mi-ss'))>1;
   --创建游标pk_poundbill 
  begin
    for cp in pk_cp loop
    --将pk_cp的第i个值赋值给cp
    --for循环，及如果cp有两个值则执行两次，3个值则执行3次
      DELETE from levm_cardmanage where vehicle = cp.vehicle;
      DELETE from  levm_cardrealinfo where pk_ehicle = cp.vehicle;
      delete from levm_queuemonitor where  vvehicle = cp.vehicle;
      Dbms_Output.put_line(cp.vehicle);
      --输出台打印cp.vehicle
      end loop;
    for confirm in pk_confirm loop
      delete from levm_confirm where  pk_comfirm = confirm.pk_comfirm;
      delete from levm_confirm_b where  pk_comfirm = confirm.pk_comfirm;
      delete from levm_deliverybill where  pk_delivery = confirm.pk_delivery;
      delete from levm_deliverybill_b where pk_delivery =  confirm.pk_delivery;
     Dbms_Output.put_line(confirm.pk_delivery||'  '||confirm.pk_comfirm);
      end loop;
    for poundbill in pk_poundbill loop
      delete from levm_poundbill where  pk_poundbill = poundbill.pk_poundbill;
      delete from levm_poundbill_b where pk_poundbill = poundbill.pk_poundbill;
      Dbms_Output.put_line(poundbill.pk_poundbill||'  '||poundbill.pk_poundbill);
      end loop;
      end;

4.调用存储过程

DECLARE
BEGIN
  DBMS_OUTPUT.put_line('在PL/SQL中打印的结果：'||delledlivery());
 END;

或者再plsql里面的procedures找到自己写的过程，然后右键编辑，按f8编译即可