oracle - DBD::Oracle - PL/SQL block 不起作用

Question

我正在尝试使用 DBD::Oracle 执行 PL/SQL 代码。它执行时没有任何错误，但表不会被删除。

这是代码:

my @tabs = qw!actor address category city country customer film film_features
film_actor film_category film_text inventory language payment rental staff 
store!; 

for my $ts (@tabs){

    my $csr = $ora->prepare(q{
         DECLARE
              var_count INT;
         BEGIN
             SELECT COUNT(*) INTO var_count
             FROM sys.all_tables WHERE OWNER = 'SAKILA' AND table_name = :ts ;
                 if var_count > 0 then
                     EXECUTE IMMEDIATE 'DROP TABLE :ts CASCADE CONSTRAINTS' ;
                 end if;
          END;
    });
    $csr->bind_param(":ts", $ts);
    $csr->execute;

}

Answer 1

No.1 - 将表格列表更改为大写

第二个 - 改变:

来自

EXECUTE IMMEDIATE 'DROP TABLE :ts CASCADE CONSTRAINTS' ;

致:

EXECUTE IMMEDIATE 'DROP TABLE '||:ts||' CASCADE CONSTRAINTS' ;

这是完整的代码:

my @tabs = qw!ACTOR ADDRESS CATEGORY CITY COUNTRY CUSTOMER FILM FILM_FEATURES FILM_ACTOR FILM_CATEGORY FILM_TEXT INVENTORY LANGUAGE PAYMENT RENTAL STAFF STORE!;
  for my $ts (@tabs){
     chomp $ts;
     my $csr = $ora->prepare(q{ 
        DECLARE
          var_count INT;
        BEGIN
          SELECT COUNT(*) INTO var_count
          FROM sys.all_tables WHERE OWNER = 'SAKILA' AND table_name = :ts ;
            if var_count > 0 then
              EXECUTE IMMEDIATE 'DROP TABLE '||:ts||' CASCADE CONSTRAINTS' ;
            end if;
        END;
     });
$csr->bind_param(":ts", $ts);
$csr->execute;
}