我有代码可以创建包含13个表的所有数据库。
我想增加一种可能性,删除这些表(已经有了(,并添加新的和空的表。
我的代码:
include 'connection.php';
$c = oci_connect($username, $password, $database);
if (!$c) {
$m = oci_error();
trigger_error('Could not connect to database: '. $m['message'], E_USER_ERROR);
}
$createDb = "CREATE TABLE adres (
adresid INTEGER NOT NULL,
miasto VARCHAR2(50 CHAR),
ulica VARCHAR2(50 CHAR),
kod_pocztowy CHAR(11 CHAR),
nr_mieszkania CHAR(5 CHAR),
nr_domu CHAR(5 CHAR)
);";
$ex = oci_parse($c,$createDb);
oci_execute($ex);
作为回应,我得到了警告:
Warning: oci_execute(): ORA-00922: missing or invalid option
您可以将所有的create语句放入一个表temp1中,您可以使用oci_parse和oci_execute创建并插入该表然后可以使用PLSQL块使用oci_parse和oci_execute运行代码。此外,在需要的地方删除分号。
步骤1
CREATE TABLE temp1
(
id NUMBER,
sql_text CLOB
);
步骤2
BEGIN
INSERT INTO temp1
VALUES (1,
'drop table t2 ;drop table t3;');
INSERT INTO temp1
VALUES (2,
'create table t2 (t number);create table t3 (t1 number);');
END;
步骤3
BEGIN
FOR rec IN (SELECT blk.id,
TRIM(Regexp_substr(sql_text, '[^;]+', 1, lvl)) sql_text
FROM (SELECT DISTINCT id,
LEVEL lvl
FROM temp1
CONNECT BY LEVEL <= Regexp_count(Trim(sql_text), '[^;]+'
))
blk,
temp1
WHERE blk.id = temp1.id
ORDER BY blk.id,
lvl) LOOP
BEGIN
EXECUTE IMMEDIATE rec.sql_text;
EXCEPTION
WHEN OTHERS THEN
dbms_output.Put_line(SQLERRM);
END;
END LOOP;
END;
或者,您可以将这三者组合为一个PLSQL块,并调用oci_parse和oci_execute
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE temp1';
EXECUTE IMMEDIATE ' CREATE TABLE temp1
(
id NUMBER,
sql_text CLOB
)';
INSERT INTO temp1
VALUES (1,
'drop table t2 ;drop table t3;');
INSERT INTO temp1
VALUES (2,
'create table t2 (t number);create table t3 (t1 number);');
FOR rec IN (SELECT blk.id,
TRIM(Regexp_substr(sql_text, '[^;]+', 1, lvl)) sql_text
FROM (SELECT DISTINCT id,
LEVEL lvl
FROM temp1
CONNECT BY LEVEL <= Regexp_count(Trim(sql_text), '[^;]+'
))
blk,
temp1
WHERE blk.id = temp1.id
ORDER BY blk.id,
lvl) LOOP
BEGIN
EXECUTE IMMEDIATE rec.sql_text;
EXCEPTION
WHEN OTHERS THEN
dbms_output.Put_line(SQLERRM);
END;
END LOOP;
END;