MySQL8.0 開始支持原⼦ DDL(atomic DDL)，數據字典的更新，存儲引擎操作，寫⼆進制日誌結合成了一個事務。在沒有原⼦DDL之前，DROP TABLE test1,test2；如遇到server crash，可能會有test1被drop了，test2沒有被drop掉。下面來看下在MySQL8.0之前和MySQL8.0 數據字典的區別
在MySQL8.0 之前，Data Dictionary除了存在與.FRM, .TRG, .OPT ⽂件外，還存在於系統表中（MyISAM ⾮事務引擎表中），在MySQL8.0 ，Data Dictionary 全部存在於Data Dictionary Storage Engine（即 InnoDB表中），這使crash recovery 維持原⼦性成為了可能
存儲引擎⽀持
目前，只有InnoDB存儲引擎⽀持原子DDL，為了實現原子DDL，Innodb要寫DDL logs 到 mysql.innodb_ddl_log 表，這是⼀個隱藏在mysql.ibd 數據字典表空間⾥的數據字典表。要看mysql.innodb_ddl_log 中的內容，需要
SET GLOBALLOG_ERROR_VERBOSITY=3;（MySQL 8.0 默認為2，error log 記錄Errors and
warnings，不不記錄notes）
SET GLOBAL innodb_print_ddl_logs=1;
CREATE TABLEt1 (c1 INT)ENGINE=InnoDB;
查看error log
[Note] [MY-011066] InnoDB: DDL loginsert: [DDLrecord:DELETE SPACE,id=30,
thread_id=25, space_id=9, old_file_path=./test/t1.ibd]
[Note] [MY-011066]InnoDB:DDL logdelete:by id30
[Note] [MY-011066]InnoDB:DDL loginsert: [DDLrecord: REMOVECACHE,id=31,
thread_id=25, table_id=1066, new_file_path=test/t1]
[Note] [MY-011066]InnoDB:DDL logdelete:by id31
[Note] [MY-011066]InnoDB:DDL loginsert: [DDLrecord: FREE,id=32, thread_
id=25, space_id=9, index_id=143, page_no=4]
[Note] [MY-011066]InnoDB:DDL log delete:by id32
[Note] [MY-011066]InnoDB:DDL logpost ddl :begin for thread id: 25
[Note] [MY-011066]InnoDB:DDL logpost ddl :end for thread id: 25
原子DDL 操作步驟
准備：創建所需的對象並將DDL⽇志寫入 mysql.innodb_ddl_log表中。DDL日誌定義了如何前滾和回滾DDL操作。
執行：執⾏DDL操作。例如，為CREATE TABLE操作執⾏創建。
提交：更新數據字典並提交數據字典事務。
Post-DDL：重播並從mysql.innodb_ddl_log表格中刪除DDL⽇志。為確保回滾可以安全執⾏⽽不引⼊不⼀致性，在此最後階段執⾏⽂件操作（如重命名或刪除數據文件）。這一階段還從 mysql.innodb_dynamic_metadata的數據字典表刪除的動態元數據為了DROP TABLE，TRUNCATE和其它重建表的DDL操作。
⽆論事務是提交還是回滾，DDL日誌都會mysql.innodb_ddl_log在Post-DDL階段重播並從表中刪除 。mysql.innodb_ddl_log如果伺服器在DDL操作期間暫停，DDL⽇志應該只保留在表中。在這種情況下，DDL⽇志會在恢復後重播並刪除。
在恢復情況下，當伺服器重新啟動時，可能會提交或回退DDL事務。如果在重做⽇志和⼆進制日誌中存在DDL操作的提交階段期間執⾏的數據字典事務，則該操作被認為是成功的並且被前滾。否則，在InnoDB重放數據字典重做日誌時回滾不完整的數據字典事務 ，並且回滾DDL事務。
原⼦DDL ⽀持類型
• DROP TABLES , all tables dropped or none
• DROP SCHEMA, all entities in the schema are dropped, or none
• Note that atomic DDL statements will be rolled back or committed even in case of crash, e.g. RENAME TABLES
• CREATE TABLE would be successfully committed or rolled back (no orphan ibd left)
• TRUNCATE TABLE (including InnoDB tables with FTS AUX tables) would be successfully committed or rolled back
• RENAME TABLES, all or none
• ALTER TABLE successful or not done
示例
結論
在MySQL8.0之前，alter table 操作在server crash的情況下，會遺留.frm,.ibd文件。MySQL8.0 能實現原⼦DDL（包括 DROP TABLE, DROP SCHEMA, CREATE TABLE, TRUNCATE TABLE, ALTER TABLE），alter table 操作，在server crash的情況下，不會遺留.frm，.ibd臨時文件。讓我們⼀起期待MySQL8.0 GA的到來吧！
『貳』 sql無法創建表
你點在資料庫關系圖上，跟能不能建表沒關系，你滑鼠要點在「表」那個字上，然後右鍵-新建表，如圖：
『叄』 如何使用動態SQL語句
SQL文本在編譯時是未知的。
例如，SELECT語句包含的標識符(如表名)在編譯時是未知的，或者WHERE子句的條件數量在編譯時是未知。
靜態SQL不支持
例如，在PL/SQL中用靜態SQL只能執行查詢以及DML語句。如果想要執行DDL語句，只能使用動態SQL。
當讓使用靜態SQL，也有它的好處：
編譯成功驗證了靜態SQL語句引用有效的資料庫對象和訪問這些對象的許可權
編譯成功創建了模式對象的依賴關系
動態SQL語句是一個最多隻能返回一行的SELECT語句
使用動態SQL語句返回單列，查詢SCOTT的薪水：
declare
v_sql_text varchar2(1000);
v_sal number;
v_ename emp.ename%type := 'SCOTT';
begin
v_sql_text := 'select e.sal from emp e where e.ename = :ename';
execute immediate v_sql_text
into v_sal
using v_ename;
dbms_output.put_line(v_ename || ':' || v_sal);
end;
使用動態SQL返回一條記錄，查詢SCOTT的基本信息：
declare
v_sql_text varchar2(1000);
v_ename emp.ename%type := 'SCOTT';
vrt_emp emp%rowtype;
begin
v_sql_text := 'select * from emp e where e.ename = :ename';
execute immediate v_sql_text
into vrt_emp
using v_ename;
dbms_output.put_line(v_ename || '的基本信息:');
dbms_output.put_line('工號:' || vrt_emp.empno);
dbms_output.put_line('工資:' || vrt_emp.sal);
dbms_output.put_line('入職日期:' || vrt_emp.hiredate);
end;
『肆』 plsql怎麼生成ddl文件
首先打開軟體Visual studio 2008,菜單中選擇文件-> 新建 -> 項目
『伍』 如何動態執行sql語句
這里只介紹動態SQL的使用。關於動態SQL語句的語法，參見：http://blog.csdn.NET/chiclewu/article/details/16097133
1.什麼是時候需要使用動態SQL？
SQL文本在編譯時是未知的。
例如，SELECT語句包含的標識符(如表名)在編譯時是未知的，或者WHERE子句的條件數量在編譯時是未知。
靜態SQL不支持
例如，在PL/SQL中用靜態SQL只能執行查詢以及DML語句。如果想要執行DDL語句，只能使用動態SQL。
當讓使用靜態SQL，也有它的好處：
編譯成功驗證了靜態SQL語句引用有效的資料庫對象和訪問這些對象的許可權
編譯成功創建了模式對象的依賴關系
2.EXECUTE IMMEDIATE語句
EXECUTE IMMEDIATE語句的意思是使用本地動態SQL處理大多數動態SQL語句。
如果動態SQL語句是自包含的（也就是說，它的綁定參數沒有佔位符，並且結果不可能返回錯誤），則EXECUTE IMMEDIATE語句不需要子句。
如果動態SQL語句包行佔位符綁定參數，每個佔位符在EXECUTE IMMEDIATE語句的子句中必須有一個相應的綁定參數，具體如下：
如果動態SQL語句是一個最多隻能返回一行的SELECT語句，OUT綁定參數放置在INTO子句，IN綁定參數放置在USING子句。
如果動態SQL語句是一個可以返回多行的SELECT語句，OUT綁定參數放置在BULK COLLECT INTO子句，IN綁定參數放置在USING子句。
如果動態SQL語句是一個除了SELECT以外的其他DML語句，且沒有RETURNING INTO子句，所有的綁定參數放置在USING子句中。
如果動態SQL還語句一個匿名PL/SQL塊或CALL語句，把所有的綁定參數放置在USING子句中。
如果動態SQL語句調用一個子程序，請確保：
每個對應子程序參數佔位符的綁定參數與子程序參數具有相同的參數模式和兼容的數據類型。
綁定參數不要有SQL不支持的數據類型（例如，布爾類型,關聯數組，以及用戶自定的記錄類型）
USING子句不能包含NULL字面量。如果想要在USING子句中使用NULL值，可以使用位初始化的變數或者函數顯示將NULL轉換成一個有類型的值。
2.1動態SQL語句是一個最多隻能返回一行的SELECT語句
使用動態SQL語句返回單列，查詢SCOTT的薪水：
declare
v_sql_text varchar2(1000);
v_sal number;
v_ename emp.ename%type := 'SCOTT';
begin
v_sql_text := 'select e.sal from emp e where e.ename = :ename';
execute immediate v_sql_text
into v_sal
using v_ename;
dbms_output.put_line(v_ename || ':' || v_sal);
end;
使用動態SQL返回一條記錄，查詢SCOTT的基本信息：
declare
v_sql_text varchar2(1000);
v_ename emp.ename%type := 'SCOTT';
vrt_emp emp%rowtype;
begin
v_sql_text := 'select * from emp e where e.ename = :ename';
execute immediate v_sql_text
into vrt_emp
using v_ename;
dbms_output.put_line(v_ename || '的基本信息:');
dbms_output.put_line('工號:' || vrt_emp.empno);
dbms_output.put_line('工資:' || vrt_emp.sal);
dbms_output.put_line('入職日期:' || vrt_emp.hiredate);
end;
2.2動態SQL語句是一個可以返回多行的SELECT語句
2.2.1隻有一個佔位符
使用動態SQL語句返回多行記錄，查詢30部門的員工基本信息：
declare
v_sql_text varchar2(1000);
v_deptno emp.deptno%type := 30;
type nt_emp is table of emp%rowtype;
vnt_emp nt_emp;
begin
v_sql_text := 'select * from emp e where e.deptno = :deptno';
execute immediate v_sql_text bulk collect
into vnt_emp
using v_deptno;
for i in 1 .. vnt_emp.count loop
dbms_output.put_line(vnt_emp(i).ename || '的基本信息:');
dbms_output.put_line('工號:' || vnt_emp(i).empno);
dbms_output.put_line('工資:' || vnt_emp(i).sal);
dbms_output.put_line('入職日期:' || vnt_emp(i).hiredate);
dbms_output.put_line('');
end loop;
end
2.2.2多個佔位符
查詢20部門工資大於2000的員工基本信息：
declare
v_sql_text varchar2(1000);
v_deptno emp.deptno%type := 20;
v_sal number := 2000;
type nt_emp is table of emp%rowtype;
vnt_emp nt_emp;
begin
v_sql_text := 'select * from emp e where e.sal>:sal and e.deptno = :deptno';
execute immediate v_sql_text bulk collect
into vnt_emp
using v_sal, v_deptno; --注意綁定多個變數時，綁定變數只與佔位符位置有關，與佔位符名稱無關，
for i in 1 .. vnt_emp.count loop
dbms_output.put_line(vnt_emp(i).ename || '的基本信息:');
dbms_output.put_line('工號:' || vnt_emp(i).empno);
dbms_output.put_line('工資:' || vnt_emp(i).sal);
dbms_output.put_line('入職日期:' || vnt_emp(i).hiredate);
dbms_output.put_line('');
end loop;
注意：對於SQL文本，佔位符名稱是沒有意義的，綁定變數與佔位符名稱無關，只與佔位符的配置有關。即使有多個相同名稱佔位符，也需要每個佔位符對應一個綁定變數。對於PL/SQL塊，佔位符名稱是有意義的，相同名稱的佔位符，只需要第一個佔位符綁定變數。
2.3動態SQL語句是一個帶有RETURNING子句的DML語句
KING的工資增長20%，返回增長後的工資：
eclare
v_sql_text varchar2(1000);
v_sal number;
v_ename emp.ename%type := 'KING';
begin
v_sql_text := 'update emp e set e.sal= e.sal*1.2 where e.ename = :ename returning e.sal into :sal';
execute immediate v_sql_text
using v_ename
returning into v_sal;
dbms_output.put_line(v_ename || ':' || v_sal);
end;
注意：只有當v_sql_text語句有returning into子句時,動態SQL語句才能使用returning into子句。
2.4給佔位符傳遞NULL值
2.4.1通過未初始化變數傳遞NULL值
declare
v_sql_text varchar2(1000);
v_deptno emp.ename%type := 'ALLEN';
v_comm emp.comm%type;
begin
v_sql_text := 'update emp e set e.comm = :comm where e.ename =:ename';
execute immediate v_sql_text
using v_comm, v_deptno;
end;
2.4.2通過函數將NULL值顯式的轉換成一個有類型的值
declare
v_sql_text varchar2(1000);
v_deptno emp.ename%type := 'ALLEN';
begin
v_sql_text := 'update emp e set e.comm = :comm where e.ename =:ename';
execute immediate v_sql_text
using to_number(null), v_deptno;
end;
3.OPEN FOR語句
PL/SQL引入OPEN FOR語句實際上並不是為了支持本地動態SQL，而是為了支持游標變數。現在它以一種極其優雅的方式實現了多行的動態查詢。
使用OPEN FOR語句來關聯動態SQL語句的游標變數，在OPEN FOR語句的USING子句中，指定動態SQL語句每個佔位符的綁定參數。
使用FETCH語句獲取運行時結果集。
使用CLOSE語句關閉游標變數
使用OPEN FOR語句查詢出10部門的員工的基本信息：
declare
type rc_emp is ref cursor;
vrc_emp rc_emp;
v_sql_text varchar2(1000);
v_deptno emp.deptno%type := 10;
vrt_emp emp%rowtype;
begin
v_sql_text := 'select * from emp e where e.deptno=:deptno';
open vrc_emp for v_sql_text
using v_deptno;
loop
exit when vrc_emp%notfound;
fetch vrc_emp
into vrt_emp;
dbms_output.put_line(vrt_emp.ename || '的基本信息:');
dbms_output.put_line('工號:' || vrt_emp.empno);
dbms_output.put_line('工資:' || vrt_emp.sal);
dbms_output.put_line('入職日期:' || vrt_emp.hiredate);
dbms_output.put_line('');
end loop;
close vrc_emp;
end;
4.重復的佔位符名稱
如果在動態SQL語句重復佔位符名稱，要知道佔位符關聯綁定參數的方式依賴於動態語句的類型。
如果執行的是一個動態SQL字元串，則必須為每一個佔位符提供一個綁定參數，即使這些佔位符是重復的。
如果執行的是一個動態PL/SQL塊，則必須為每一個唯一佔位符提供一個綁定參數，即重復的佔位符只需要提供一個綁定參數。
4.1重復佔位符的動態SQL字元串
declare
v_sql_text varchar2(1000);
v_sal emp.sal%type := 4000;
v_comm emp.comm%type;
v_ename emp.ename%type := 'SCOTT';
begin
v_sql_text := 'update emp e set e.sal=:sal , e.comm = :sal*0.1 where e.ename =:ename returning e.comm into :comm ';
execute immediate v_sql_text
using v_sal, v_sal, in v_ename
returning into v_comm;
dbms_output.put_line(v_ename || '分紅:' || v_comm);
end;
4.2重復佔位符的動態PL/SQL塊
declare
v_sql_text varchar2(1000);
v_sal number;
v_ename emp.ename%type := 'KING';
begin
v_sql_text := ' begin select e.sal,e.ename into :sal,:ename from emp e where e.ename =:ename; end;';
execute immediate v_sql_text
using out v_sal, in out v_ename;
dbms_output.put_line(v_ename || ':' || v_sal);
end;
『陸』 PL/SQL開發中動態SQL的使用方法
內容摘要 在PL/SQL開發過程中 使用SQL PL/SQL可禪仔以實現大部份的需求 但是在某些特殊的情況下 在PL/SQL中使用標準的SQL語句或DML語句不能實現自己的需求 比如需要動態建表或某個不確定的操作需要動態正宏執行 這就需要使用動態SQL來實現 本文通過幾個實例來詳細的講解動態SQL的使用 本文適宜讀者范圍 Oracle初級 中級 系統環境 OS windows Professional (英文版)Oracle 正文 一般的PL/SQL程序設計中 在DML和事務控制的語句中可以直接使用SQL 但是DDL語句及系統控制語句卻不能在PL/SQL中直接使用 要想實現在PL/SQL中使用DDL語句及系統控制語句 可以通過使用動態SQL來實現 首先我們應該了解舉襲冊什麼是動態SQL 在Oracle資料庫開發PL/SQL塊中我們使用的SQL分為 靜態SQL語句和動態SQL語句 所謂靜態SQL指在PL/SQL塊中使用的SQL語句在編譯時是明確的 執行的是確定對象 而動態SQL是指在PL/SQL塊編譯時SQL語句是不確定的 如根據用戶輸入的參數的不同而執行不同的操作 編譯程序對動態語句部分不進行處理 只是在程序運行時動態地創建語句 對語句進行語法分析並執行該語句 Oracle中動態SQL可以通過本地動態SQL來執行 也可以通過DBMS_SQL包來執行 下面就這兩種情況分別進行說明 一 本地動態SQL 本地動態SQL是使用EXECUTE IMMEDIATE語句來實現的 本地動態SQL執行DDL語句 需求 根據用戶輸入的表名及欄位名等參數動態建表 create or replace procere proc_test(table_name in varchar 表名field in varchar 欄位名datatype in varchar 欄位類型field in varchar 欄位名datatype in varchar 欄位類型) asstr_sql varchar ( );beginstr_sql:= create table ||table_name|| ( ||field || ||datatype || ||field || ||datatype || ) ;execute immediate str_sql; 動態執行DDL語句exceptionwhen others thennull;end ;以上是編譯通過的存儲過程代碼 下面執行存儲過程動態建表 SQL> execute proc_test( dinya_test id number( ) not null name varchar ( ) );PL/SQL procere successfully pletedSQL> desc dinya_test;Name TypeNullable Default Comments ID NUMBER( )NAME VARCHAR ( ) YSQL>到這里 就實現了我們的需求 使用本地動態SQL根據用戶輸入的表名及欄位名 欄位類型等參數來實現動態執行DDL語句 本地動態SQL執行DML語句 需求 將用戶輸入的值插入到上例中建好的dinya_test表中 create or replace procere proc_insert(id in number 輸入序號name in varchar 輸入姓名) asstr_sql varchar ( );beginstr_sql:= insert into dinya_test values(: : ) ;execute immediate str_sql using id name; 動態執行插入操作exceptionwhen others thennull;end ;執行存儲過程 插入數據到測試表中 SQL> execute proc_insert( dinya );PL/SQL procere successfully pletedSQL> select * from dinya_test;IDNAME dinya在上例中 本地動態SQL執行DML語句時使用了using子句 按順序將輸入的值綁定到變數 如果需要輸出參數 可以在執行動態SQL的時候 使用RETURNING INTO 子句 如 declarep_id number:= ;v_count number;beginv_string:= select count(*) from table_name a where a id=:id ;execute immediate v_string into v_count using p_id;end ;更多的關於動態SQL中關於返回值及為輸出輸入綁定變數執行參數模式的問題 請讀者自行做測試 二 使用DBMS_SQL包 使用DBMS_SQL包實現動態SQL的步驟如下 A 先將要執行的SQL語句或一個語句塊放到一個字元串變數中 B 使用DBMS_SQL包的parse過程來分析該字元串 C 使用DBMS_SQL包的bind_variable過程來綁定變數 D 使用DBMS_SQL包的execute函數來執行語句 使用DBMS_SQL包執行DDL語句 需求 使用DBMS_SQL包根據用戶輸入的表名 欄位名及欄位類型建表 create or replace procere proc_dbms_sql(table_name in varchar 表名field_name in varchar 欄位名datatype in varchar 欄位類型field_name in varchar 欄位名datatype in varchar 欄位類型)asv_cursor number; 定義游標v_string varchar ( ); 定義字元串變數v_row number; 行數beginv_cursor:=dbms_sql open_cursor; 為處理打開游標v_string:= create table ||table_name|| ( ||field_name || ||datatype || ||field_name || ||datatype || ) ;dbms_sql parse(v_cursor v_string dbms_sql native); 分析語句v_row:=dbms_sql execute(v_cursor); 執行語句dbms_sql close_cursor(v_cursor); 關閉游標exceptionwhen others thendbms_sql close_cursor(v_cursor); 關閉游標raise;end;以上過程編譯通過後 執行過程創建表結構 SQL> execute proc_dbms_sql( dinya_test id number( ) not null name varchar ( ) );PL/SQL procere successfully pletedSQL> desc dinya_test ;Name TypeNullable Default Comments ID NUMBER( )NAME VARCHAR ( ) YSQL> 使用DBMS_SQL包執行DML語句 需求 使用DBMS_SQL包根據用戶輸入的值更新表中相對應的記錄 查看錶中已有記錄 SQL> select * from dinya_test ;ID NAME Oracle CSDN ERPSQL>建存儲過程 並編譯通過 create or replace procere proc_dbms_sql_update(id number name varchar )asv_cursor number; 定義游標v_string varchar ( ); 字元串變數v_row number; 行數beginv_cursor:=dbms_sql open_cursor; 為處理打開游標v_string:= update dinya_test a set a name=:p_name where a id=:p_id ;dbms_sql parse(v_cursor v_string dbms_sql native); 分析語句dbms_sql bind_variable(v_cursor :p_name name); 綁定變數dbms_sql bind_variable(v_cursor :p_id id); 綁定變數v_row:=dbms_sql execute(v_cursor); 執行動態SQLdbms_sql close_cursor(v_cursor); 關閉游標exceptionwhen others thendbms_sql close_cursor(v_cursor); 關閉游標raise;end;執行過程 根據用戶輸入的參數更新表中的數據 SQL> execute proc_dbms_sql_update( csdn_dinya );PL/SQL procere successfully pletedSQL> select * from dinya_test ;ID NAME Oracle csdn_dinya ERPSQL>執行過程後將第二條的name欄位的數據更新為新值csdn_dinya 這樣就完成了使用dbms_sql包來執行DML語句的功能 使用DBMS_SQL中 如果要執行的動態語句不是查詢語句 使用DBMS_SQL Execute或DBMS_SQL Variable_Value來執行 如果要執行動態語句是查詢語句 則要使用DBMS_SQL define_column定義輸出變數 然後使用DBMS_SQL Execute DBMS_SQL Fetch_Rows DBMS_SQL Column_Value及DBMS_SQL Variable_Value來執行查詢並得到結果 總結說明 在Oracle開發過程中 我們可以使用動態SQL來執行DDL語句 DML語句 事務控制語句及系統控制語句 但是需要注意的是 PL/SQL塊中使用動態SQL執行DDL語句的時候與別的不同 在DDL中使用綁定變數是非法的（bind_variable(v_cursor :p_name name)） 分析後不需要執行DBMS_SQL Bind_Variable 直接將輸入的變數加到字元串中即可 另外 DDL是在調用DBMS_SQL lishixin/Article/program/SQLServer/201311/22089