create or replace procedure tongji is v_sql varchar2(100); begin v_sql :='create table emp_work_detail as select distinct name,id,mouth,startwork,endwork, case when to_char(startwork,'hh24:mi')>'09:00' then '是' else '否' end chidao, case when (endwork-startwork)*24>8.5 then '是' else '否' end jiaban, case when to_char(endwork,'hh24:mi')<'17:30' then '是' else '否' end zhaotui from( select distinct name,id,to_char(SIGNIN_DATETIME,'yyyy-mm') mouth, first_value(signin_datetime) over(partition by id,to_char(signin_datetime,'dd') order by signin_datetime) startwork, last_value(signin_datetime) over(partition by id,to_char(signin_datetime,'dd')) endwork from emp_work order by id)'; execute immediate v_sql; end; /

这段代码存在一个语法错误，即在动态 SQL 语句中使用了单引号，但是单引号已经被用作字符串的标识符了。解决方法是将单引号进行转义，例如将 'hh24:mi' 改为 ''hh24:mi''。修改后的代码如下：

create or replace procedure tongji is 
  v_sql varchar2(100); 
begin 
  v_sql := 'create table emp_work_detail as 
             select distinct name, id, to_char(signin_datetime, ''yyyy-mm'') mouth, 
             first_value(signin_datetime) over(partition by id, to_char(signin_datetime, ''dd'') order by signin_datetime) startwork, 
             last_value(signin_datetime) over(partition by id, to_char(signin_datetime, ''dd'')) endwork, 
             case when to_char(first_value(signin_datetime) over(partition by id, to_char(signin_datetime, ''dd'') order by signin_datetime), ''hh24:mi'') > ''09:00'' then ''是'' else ''否'' end chidao, 
             case when (last_value(signin_datetime) over(partition by id, to_char(signin_datetime, ''dd'')) - first_value(signin_datetime) over(partition by id, to_char(signin_datetime, ''dd'')))*24 > 8.5 then ''是'' else ''否'' end jiaban, 
             case when to_char(last_value(signin_datetime) over(partition by id, to_char(signin_datetime, ''dd'')), ''hh24:mi'') < ''17:30'' then ''是'' else ''否'' end zhaotui 
             from emp_work order by id'; 
  execute immediate v_sql; 
end; 
/

修改后的代码使用了两个单引号来表示一个单引号，从而避免了语法错误。同时，对动态 SQL 语句进行了一些微调，使得结果更加准确。