Oracle--Produce

Produce例：

例１：

CREATE OR REPLACE PROCEDURE ShanChuBiao
IS
BEGIN
 dbms_output.put_line('1');
END ;

例2：

CREATE OR REPLACE PROCEDURE sp_test(BiaoMing in varchar2)
IS
 vsql varchar2(3000);
 tnameall varchar2(4000);
 tname varchar2(300);
 beginpoint int;
 endpoint int;
BEGIN
  tnameall := BiaoMing;
  vsql := '';
  beginpoint := 0;
  dbms_output.put_line(subStr(tnameall,beginpoint,endpoint));
  while(instr(tnameall, ',')>0) Loop
    endpoint := instr(tnameall,',');
    tname := subStr(tnameall,beginpoint,endpoint-1);
    vsql := vsql || ' DELETE FROM '|| tname || ' where INSERT_TIMESTAMP >= TO_DATE(''2017-08-17'', ''yyyy-mm-dd'');'||chr(13) ;
    tnameall := subStr(tnameall,endpoint+1,length(tnameall));
  End Loop;
  dbms_output.put_line(vsql);
   If 1=1 then
      dbms_output.put_line('1');
    Elsif 1=1 then
     dbms_output.put_line('2');
    else
      dbms_output.put_line('3');   
    End if;
--EXECUTE IMMEDIATE (vsql);
END sp_test;
/
begin
  sp_test('TB_BA_FZXX,
TB_BA_JBXX,
TB_BA_SSXX,
TB_BA_ZDXX,
TB_HIS_SSJL,
TB_HIS_SSMX,
TB_HZXX,
TB_JBJKXX,
TB_KSZDB,
TB_LIS_JYBG,
TB_LIS_JYJGZB,
TB_LIS_XJJG,
TB_LIS_YMJG,
TB_MZ_CFMX,
TB_MZ_CFZB,
TB_MZ_GHMXB,
TB_MZ_JSB,
TB_MZ_JZJL,
TB_MZ_SFMXB,
TB_RIS_JCBG,
TB_RIS_YXWJ,
TB_RLZY_RYGC,
TB_WSSJZY,
TB_XZ_YXMX,
TB_YHRYXXB,
TB_YL_TJBG,
TB_YL_TJBGSY,
TB_YL_TJMX,
TB_YLJGXXB,
TB_YQSB,
TB_ZL_CZJLB,
TB_ZY_CYDJB,
TB_ZY_CYXJ,
TB_ZY_RYDJB,');
end;

例３：（游标遍历表，获取指定值）

CREATE OR REPLACE PROCEDURE TongJi
IS
total int;
str varchar(1000);
BEGIN
 FOR re IN (select table_name from all_tables where owner='YLFW')  LOOP
   DBMS_OUTPUT.PUT_LINE(re.table_name);
   str := 'select count(*) as total from '|| re.table_name;
   execute immediate str into total;
   DBMS_OUTPUT.PUT_LINE(total);
  END LOOP;
END ;
/

PLSQL跟踪存储过程操作