sql - 修复存储过程pl/sql中的代码（缺少右括号）

Question

我是 pl/sql 的新手，这就是我在这里寻求帮助的原因。我有一个必须生成日志表和触发器的存储过程，但它显示错误：

错误报告 - ORA-00907：缺少右括号 ORA-06512：在“ASH.CREATE_LOG_TRIGGER”，第 28 行 ORA-06512：在第 2 行 00907. 00000 -“缺少右括号”

这是我的代码：

create or replace procedure create_log_trigger(p_table_name varchar2) IS
var2 varchar2(10000);
var1 varchar2(4000) := 'country VARCHAR2(100), iso_code VARCHAR2(20), vaccination_date DATE, total_vaccinations NUMBER(26, 5), 
people_vaccinated NUMBER(26, 5), people_fully_vaccinated NUMBER(26, 5), daily_vaccinations_raw NUMBER(26, 5), daily_vaccinations NUMBER(26, 5),
total_vaccinations_per_hundred NUMBER(26, 5), people_vaccinations_per_hundred NUMBER(26, 5), fully_vaccinations_per_hundred NUMBER(26, 5),
daily_vaccinations_per_million NUMBER(26, 5), vaccines VARCHAR2(128), source_name VARCHAR2(20), source_website VARCHAR2(20), 
 ACTION VARCHAR2(20), ACTIONAUTHOR VARCHAR2(20)';
var3 varchar2(4000) := 'country, iso_code, vaccination_date, total_vaccinations, 
people_vaccinated, people_fully_vaccinated, daily_vaccinations_raw, daily_vaccinations,
total_vaccinations_per_hundred, people_vaccinations_per_hundred, fully_vaccinations_per_hundred,
daily_vaccinations_per_million, vaccines, source_name, source_website, 
 ACTION, ACTIONAUTHOR';
var4 varchar2(4000) := 'new_country, new_iso_code, sysdate, new_ total_vaccinations, 
new_people_vaccinated, new_ people_fully_vaccinated, new_daily_vaccinations_raw, new_daily_vaccinations,
new_total_vaccinations_per_hundred, new_people_vaccinations_per_hundred, new_fully_vaccinations_per_hundred,
new_daily_vaccinations_per_million, new_ vaccines, new_source_name, new_source_website';
var5 varchar2(4000);
CURSOR cur_table is
    select column_name,data_type
    from user_tab_columns
    where table_name = p_table_name;
begin
    for i IN cur_table loop
        var1 := var1 ||', '||'old_'||i.column_name ||' '||i.data_type||', '||'new_'||i.column_name|| ' ' ||i.data_type;
        var3 := var3 ||', '||'old_'||i.column_name ||', '||'new_'||i.column_name;
        var5 := var5 ||', '||':old.'||i.column_name ||', '||':new.'||i.column_name;
    end loop;
    EXECUTE IMMEDIATE 'create table ' || p_table_name || '_LOG' || '(' || REPLACE(var1, 'VARCHAR2', 'VARCHAR(255)') || ')'; 
     var2 := 'create or replace trigger ' || p_table_name ||'_trig  
    AFTER DELETE OR INSERT OR UPDATE on '|| p_table_name ||' FOR EACH ROW
    declare
        new_country varchar2(20);
    BEGIN
        IF DELETING THEN
            select NVL(MAX(id), 0)+1 into new_country from ' || p_table_name || '_LOG;
            INSERT INTO ' || p_table_name || '_log('||var3||')
            VALUES('||var4||',''delete'',user'||var5||');
        END IF; 
        IF INSERTING THEN
            select NVL(MAX(id), 0)+1 into new_country from ' || p_table_name || '_LOG;
            INSERT INTO ' || p_table_name || '_log('||var3||')
            VALUES('||var4||',''insert'',user'||var5||');
        END IF;
        IF UPDATING THEN
            select NVL(MAX(id), 0)+1 into new_country from ' || p_table_name || '_LOG;
            INSERT INTO ' || p_table_name || '_log('||var3||')
            VALUES('||var4||',''update'',user'||var5||');
        END IF;
    END;';
    EXECUTE IMMEDIATE var2;
end;

标签： sqloracleplsqlprocedure