mylost存储过程和判断是否存在表的函数

create or replace procedure getMyLost(
season in number,--测试用
season_episode out varchar --最终返回结果
)
is
--演示游标的使用
cursor lost_cus is
        select season,episode,to_char(watched_date,'yyyy-mm-dd hh24:mi:ss') watched_date from lost_already  order by season,episode;
season_temp number(1);--随机得出的季
season_sql varchar2(100);--随机得出季的SQL
episode_sql varchar2(100);--得出季的集
episode_temp number(2);--得出季的集的SQL
watched boolean;--有看的控制条件
checkSQL varchar2(200);--检查是否已经看了的SQL
checkCount number(1);
recodeSQL varchar2(200);--记录当前得到的季和集
episodeSum number(2);--被选中季的总集数
begin
watched:=true;
season_sql:='select trunc(dbms_random.value*6)+1 from dual';
for lost in lost_cus
loop
   dbms_output.put_line(lost.season||'==>'||lost.episode||'==>'||lost.watched_date);
end loop;
while watched
loop
  execute immediate season_sql into season_temp;
   episode_sql:='select t.episodesum from lost_season_episode t where t.season = '||season_temp;
  execute immediate episode_sql into episodeSum;
  execute immediate 'select trunc(dbms_random.value*'||episodeSum||')+1 from dual ' into episode_temp;
   checkSQL:='select count(*) from lost_already t where t.season ='||season_temp||' and t.episode = '||episode_temp;
  execute immediate checkSQL into checkCount;
  if checkCount > 0 then
     watched:=true;
   else
     watched:=false;
  end if;
end loop;
recodeSQL:='insert into lost_already(season,episode,watched_date) values ('||season_temp||','||episode_temp||',sysdate)';
execute immediate recodeSQL;
commit;
season_episode:='第'||season_temp||'集 第'||episode_temp||'集';

 判断是否存在表的函数：

create or replace function existtable(
tableName varchar2
) return varchar2
is
sqlText varchar2(200);
begin
sqlText:='select * from '||tableName;
execute immediate sqlText;
return 'exists';
exception
when others then return 'not exists';
end;