procedure存储过程创建执行

1.创建存储过程

create or replace procedure PRO_RELATION(SY in char, DY in char) [authid current_user] as
  sql_stmt VARCHAR2(5000);
begin
  --PRO_RELATION是存储过程名称
  --SY,DY是需传值的参数
  --authid current_user解决存储过程中有权限问题
  sql_stmt := 'create table B33_PES.PE_RELATION_A
as
select t1.yhangh,num1,num2 from (
select a.yhangh,listagg(a.ygongh||a.bilifc) within
group (order by bilifc) as num1
from B33_PES.PE_RELATION_TODAY_A a
where a.nowdate=' || DY || ' 
group by a.yhangh
) t1 inner join (
select a.yhangh,listagg(a.ygongh||a.bilifc) within
group (order by bilifc) as num2
from B33_PES.PE_RELATION_TODAY_A a
where a.nowdate=' || SY || ' 
group by a.yhangh
) t2 on t1.yhangh=t2.yhangh and
num1<>num2';
  execute immediate sql_stmt;
  --immediate  表示强制执行该语句
  commit;
end PRO_RELATION;

2.执行存储过程

begin
  --Call the procedure
  pro_relation(sy => :sy, dy => :dy);
end;

变量    类型    值
sy      String    20171031
dy      String    20171130
找到存储过程，右键点击test

3.存储过程中sql语句用单引号引起，所以sql函数中牵扯到的单引号要用双单引号（转译）
4.打印存储过程中带传入参数的sql脚本是否与自己预想一致需要

begin
  dbms_output.put_line(sql_stmt);
end;

或者右键test中选择DBMS Output tab页查看
5.存储过程中赋给权限需要在参数后加上authid current_user

create or replace procedure pro_relation_zhgsbd(vsymd in char,
                                                vdymd in char) authid current_user is
  sql_select varchar2(8000);
begin
  --......
end pro_reation_zhgsbd;