A. 結構體求解
sqlstm = {12,111}——聲明了一個struct sqlexd結構類型的變數sqlstm，並給其成員成員sqlvsn賦值12、給成員arrsiz賦值111。其餘成員均置0(NULL、""等)。
B. 關於SQL資料庫以及JAVA的一些問題,在線急等
1 付鍵 也就是FK formary key
www.cxrs.net/bbs 程序交流中心，這是專門為新手學習設立的論壇
二、SQL
Structur query language
結構化查詢語言，是操作關系型資料庫中的對象。
DDL（Data definition language 數據定義語言），用於建表或刪表操作，以及對表約束進行修改
create table , alter table , drop table 對表結構的增刪操作。
DML（Data manipulation language 數據操作語言），向表中插入紀錄，修改紀錄
insert , update , delete , merge
transaction ,事務控制語言，由DML語句組成的，commit; ,rollback;
select 查詢語句
dcl 授權語句 grant
三、Oracle
DBMS 資料庫管理系統
有Oracle提供，還提供AS，應用伺服器
DBA 資料庫管理員
四、相關操作
1、sqlplus 訪問資料庫命令（本地訪問/遠程訪問），和資料庫建立連接的命令，是資料庫操作的環境
sqlplus 用戶名/密碼
2、show user 顯示當前用戶的用戶名
改變身份可以直接connect 用戶名/密碼 --- 這個是sqlplus命令
在sqlplus中可以使用 ! 可以在shell和sqlplus間切換，!shell命令 可以在sqlplus中使用shell命令。
實際上是sqlplus開了子進程來執行shell命令。
3、Oracle資料庫中的表分兩類：用戶表（用戶使用操作的表），系統表（資料庫系統維護的表，也叫數據字典）
對用戶表的DDL操作出發了對系統表的DML操作！
五、基本語法
1、select查詢語句
select table_name from user_tables;（查詢系統表）
以上的查詢語句就是查詢本用戶下所擁有的所有表的表名。
投影操作，只查看選擇的欄位的信息。
選擇操作，查看欄位中的特定某些信息。
聯接操作，多表查詢，通過表間連接，查尋出多表中的信息
（1）select table_name from user_tables;（查詢系統表）
以上的查詢語句就是查詢本用戶下所擁有的所有表的表名。
（2）sqlplus的buffer中會緩存最後一條sql語句，可以使用"/"來執行這最後一條sql語句，也可以使用
edit命令來編輯最後一條sql語句。
l命令（list）（sqlplus命令）可以顯示buffer中最後一條命令。
sqlplus命令可以縮寫
（3）desc [表名]
這是一條sqlplus命令，注意他不是sql語句，這條命令用於查看錶的結構。descript的縮寫
[欄位名] [欄位的類型]，這是使用完desc命令後顯示的表結構。
（4）select [表的欄位名1],[表的欄位名2], ... from 表名;
select * from 表名; 查尋表中所有欄位的信息
（5）關鍵字不等拆分，sql語句，以及表名，欄位名是大小寫不敏感的。
sql語句要以";"結尾，來表示sql語句結束，如果不加";"系統不會執行此條sql語句，並提示。
在Oracle中字元顯示是左對齊，數值右對齊。
（6）在select 語句中可以使用數學表達式。
select [表達式（必須包含本表欄位名）],[...],.... from 表名；
運算的優先順序的先乘除後加減，同級自左向右運算，括弧改變優先順序。
（7）別名
select [欄位名或表達式] ["別名"]，[...] ["..."],.... from 表名；
可以通過在欄位名或表達式後加空格"別名"，可以給列，或者表達式結果其別名。
表達別名必須加雙引號。
（8）字元串拼接使用||符號
select 目標欄位名||" "||目標欄位名 from 表名；
注意：在Oracle中的字元串要用'..'包含
別名中需要使用空格，或是大小寫敏感時需要用".."包含。
練習：
自己寫一條SQL語句，執行的結果是select * from ...;
其中...是每張系統表的表名
即在每張系統表的表名前加「select * from」 ，後加「;」
select 'select * from '||table_name||';' from user_tables;
2、處理錯誤
（1）!oerr ora [錯誤號] ，系統可以顯示錯誤的原因和如何修改。如果命令錯誤輸入可以使用edit或ed來修改輸入錯誤。
實際上是在編輯緩存文件中的最後一條sql語句。
也可以使用 (change) c /錯誤欄位/正確欄位，來進行替換操作進行修改。
只有在Linux平台使用
! 相當於 host ，沒有斷連接，只是切換了一下，執行shell命令
（2）edit命令來編輯最後一條sql語句。
3、sqlplus設置
set pause on 回車響應，分屏顯示，只在本會話中有效
set pause off 關閉分屏顯示。
set pause "..." 設置分屏顯示的提示信息。
set pause on 先輸出提示信息，回車響應，分屏顯示
set head off 提頭輸出關閉
set feed off 結尾輸出關閉
set echo off 回寫關閉
spool 文件名.sql 寫入指定文件
spool off 關閉寫入。
4、sql腳本
也就是在文件中寫有sql語句的文件，可以在sqlplus中運行。
引入sql腳本
sqlplus 用戶名/密碼 @sql腳本 （注意：在用戶名密碼輸入結束後一定要加空格然後再寫@sql腳本）
在腳本中最後一行寫上「exit」，則運行完腳本以後，回到shell上
5、
Oracle中的空值 空值會當無窮大處理，其實空值根本就不會存儲，只是看作是無窮大。
Oracle中控制處理函數 NVL(欄位名，值)，這個欄位中的空值替換為指定值，如果不為空，則會返回其原值。
例：select (salary*12)*(NVL(commission_pct,0)/100+1) salary,first_name from s_emp;
distinct關鍵字，去掉重復行（這個關鍵字會觸發排序操作）
例： select distinct dept_id,title from s_emp;
dept_id與title的聯合不唯一
注意：distinct，關鍵字之後會對from之前的欄位進行排重操作。
6、column命令 --- sqlplus命令
column命令 列格式的定義
column 目標列名 查看這個類是否定義了格式
column 目標列名 format a.. 設置列寬。
column last_name heading 'Employee|Name' FORMAT A15
設置題頭
這其中的'|'是換行符
column salary justify left format $99,990.00
定義數字顯示格式
注意：如果不滿足顯示的格式，就會把數據顯示為"#"
column salary justify left format $00,000.00
會出現$00,928.00 ,用0補齊
column 列名 clear （清除列格式定義）
注意：只有sqlplus命令才有簡寫，並且在使用sqlplus命令時結尾也不能加分號。
六、選擇操作
1、order by
排序子句 ASC（默認，升序） DESC（降序）
order by 目標列名（別名） 排序順序（不寫排序順序，會默認為升序排序）
例：select first_name from s_emp order by first_name;
select first_name from s_emp order by first_name desc;
注意：升序空值在結果的末尾，降序空值在結果的最前面。
2、where子句
where子句使用在 select ... from ... 後面，用來選擇所需（符合條件的）的記錄
where後面跟的是表達式 也就是 XXX=XXX， XXX between X and X ，XXX in（X，X，X）
like '...' 通配查詢
between ... and ... ,表示結果在這之間，between and是一個閉區間，
也就相當於... <= ... and ... >= ... 。
!=，<>，^=，這三個都標識不等於，<=，>=，=，這些運算符都可以使用。
... in (va1,val2,...) 判斷結果是否在這個枚舉中存在
like '...' 字元串通配查詢，'%'表示多個字元，'_'，表示一個字元。
注意：轉義的用法：like 『S\_%』 escape 『\』
... and ... 表示只有兩個條件同時滿足
... or ... 表示條件只要滿足其中只一就可以
all ... 是要求都滿足條件。
not .....，則是可以與以上的條件產生反效果。
空值會對not in造成影響，也就是不等於任何值，但是空值例外。
... is null 使用來判斷值是否為空。
注意：Oracle中的字元串是嚴格區分大小寫的。
（1）注意數據類型，數字類型直接寫，字元用'......' ，預設格式的Date可以用'......'，只有別名
才用" "包含。
（2）選擇合適的運算符
七、單行函數
1.字元函數
字元是大小寫敏感的
轉小寫 lower(欄位名) --- 其中的參數可以是一個字元串常量或是一個欄位名
轉大寫 upper(欄位名)
首字母大寫 initcap(欄位名)
字元串拼接 concat(欄位1, 欄位2)
截取子串 substr(欄位名, 起始位置，取字元個數)
al表，是專門用於函數測試和運算的，他只有一條記錄
字元串拼接 concat(...,....)
求指定子串 substr(...,起始位置，取字元個數)
可以使用"-"表示從右向左取，取的時候可以從左往友取。
例：select substr(first_name,-2,2) sub from s_emp;（取後兩個）
select substr(first_name,2,2) sub from s_emp;（取前兩個）
2,數值函數
四捨五入 round(數據,保留小數點後幾位)
可以用負數表示小數點前，0，表示小數點後第一位，也就是保留個位，-1表示個位（保留到十 位）。
例：select round(15.36,1) from al;
截取數字函數 trunc(數據，保留的位數（小數點後位數）) 截取個位之後補0
例：select trunc(123.456,1) from al;
3，日期函數
日期格式，
全日期格式 世紀信息，年月日，時分秒。
預設日期格式，日-月-年 dd-mon-rr
修改當前會話的日期格式，會按照指定的格式輸出日期
alter session set nls_date_format='yyyy mm dd hh24:mi:ss';
返回當前日期 sysdate
例：select sysdate from al;
select sysdate+1 from al; 獲得明天的日期，加1，單位是天
日期是格式敏感的
求兩個日期間相隔了多少個月 months_between(date1,date2)
加減指定數量的月份 add_months(date,月數)，月數可以為負，負值就是減去相應的月數。
從date日期開始的第一個星期五 next_day(date,FriDay)
返回月末的日期 last_day(date)
截取日期 trunc(date,'年或月或日或時分秒')
例：select next_day(sysdate,2) from al;
例：select trunc(add_months(sysdate,1),'month') from al;
ROUND('25-MAY-95','MONTH') 01-JUN-95
ROUND('25-MAY-95 ','YEAR') 01-JAN-95
TRUNC('25-MAY-95 ','MONTH') 01-MAY-95
TRUNC('25-MAY-95 ','YEAR') 01-JAN-95
練習：
返回下個月的第一天的日期
select round(last_day(sysdate),'MONTH') from al;
select add_months(trunc(sysdate,'MONTH'),1);
4，不同數據類型間轉換函數
將日期轉成字元 tochar(date,'日期格式')
日期格式要用有效格式，格式大小寫敏感 'yyyy mm dd hh24:mi:ss',
'year'(全拼的年),'mm'(數字表示的月) 'month'(全拼的月)，'day'(星期的全拼)，'ddspth' (日期的全拼) 'yy mm dd'
例：select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from al;
將字元轉換成數字 to_number('...')
將數字轉字元to_char(number，'fmt') fmt是數字格式
將字元串轉成日期 to_date('...','日期格式')
例：select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from al；
1、等值連接
select [表別名1.欄位名1]，[表別名2.欄位名2],...
from 表1 表別名1 ，表2 表別名2
where 表別名1.欄位名3=表別名2.欄位名4;
表連接時，當表與表之間有同名欄位時，可以加上表名或表的別名，加以區分，使用時要用
表名.欄位名或表別名.欄位名（列名）。當表的欄位名是唯一時，可以不用加上表名或表的別名。
注意：當為表起了別名，就不能再使用表名.欄位名。
例：select a.first_name,a.last_name,b.name
from s_emp a,s_dept b
where a.dept_id=b.id;
2、非等值連接
select [表別名1.欄位名1]，[表別名2.欄位名2],...
from 表1 表別名1 ，表2 表別名2
where 表別名1.欄位名3 ..... 表別名2.欄位名4
....可以使比較運算符，也可以使其他的除了'='的運算符
例：select e.ename, d.grade,e.sal
from emp e,salgrade d
where e.sal between d.losal and d.hisal;
3、自連接
用別名把一張表中的數據分成兩部分，然後在使用條件過濾。
select [表別名1.欄位名1]，[表別名2.欄位名2],...
from 表1 表別名1 ，表1 表別名2
where 表別名1.欄位名3=表別名2.欄位名4;
例：select a.first_name ename,b.first_name cname
from s_emp a,s_emp b
where a.manager_id=b.id;
以上所提到的表連接，都叫做內連接，嚴格匹配兩表的記錄。
4、外連接
會使用一方表中的所有記錄去和另一格表中的記錄按條件匹配，空值也會匹配，這個表中的所有記錄都會顯示，資料庫會模擬出記錄去和那些不匹配的記錄匹配。
例：select a.first_name enamei,a.id,b.first_name cname,b.id
from s_emp a,s_emp b
where a.manager_id=b.id(+);
即用a表中的數據去匹配b表的，若b表中有null，系統模擬紀錄與其匹配
注意：要把那一方的記錄全部都顯示出來，還有注意條件(+)跟在要全部選出的對端。
外連接的應用：
列出哪個部門沒有員工
select e.deptno,d.deptno
from emp e,dept d
where e.deptno(+)=d.deptno
and e.deptno is null;
三、組函數
group 組
group by 分組子句，按指定的分組規則分組 ，這個group by 子句可以跟在 select 語句後或是 having後面。
group by子句也會出發排序操作，會按分組欄位排序。
select [組函數或分組的欄位名] ，... from 表名 group by [欄位名1],[欄位名2],.....；
例：select avg(salary) from s_emp group by dept_id;
注意：組函數可以處理一組數據，返回一個值。
組函數會忽略空值。
avg(..),求平均值，sum(..),求和 這兩個函數的參數只能是number型的。
以下所提到的函數可以使用任意類型做參數。
count(..)，用來統計記錄數，可以使用排重命令。count(...)默認使用的是all。
max(..),min(..)求最大值和最小值，
count(*),統計表中記錄數。
例：select max(b.name),avg(a.salary), max(c.name)
from s_emp a,s_dept b,s_region c
where a.dept_id=b.id and b.region_id=c.id
group by b.dept_id;
注意：只要寫了group by子句，
*** select後就只能用group by後的欄位或者是組函數。 ***
where子句只能夠過濾記錄，放單行函數。
having子句可以過濾組函數結果或是分組的信息，且寫在group by子句後。
例:
select max(b.name),avg(a.salary), max(c.name)
from s_emp a,s_dept b,s_region c
where a.dept_id=b.id and b.region_id=c.id
group by b.id
having sum(a.salary)>4000;
column 也可以定義有別名的列的格式。
column "別名" 格式定義
注意：要先過濾掉不需要的記錄，然後再進行分組操作，提高效率。
四、子查詢
子查詢，就是可以嵌在任何的sql語句中的select語句。
在select語句中嵌套子查詢時，會先執行子查詢。一般的會將子查詢放在運算符的右邊。
注意：在使用子查詢時，要注意這個運算符是單行的（也就是只能是單值），還是多行運算符（范圍，多值，in）。
配合使用子查詢返回的結果必須符合運算符的用法。
例:
select first_name,title
from s_emp
where title=any(select title from s_emp
where last_name='Smith')
and upper(last_name)!='SMITH';
select first_name,title
from s_emp
where title in (select title from s_emp
where last_name='Smith')
and upper(last_name)!='SMITH';
五、將業務需求轉換成可操作的表
一: 需求分析
二: 畫E-R圖
三: 轉換成表關系
四: 割接（新老系統交接）
五:
E-R圖屬性:
* 為強制且非空屬性
o 可選屬性(可以有值也可以沒有)
#* 表示此屬性唯一且非空
實體關系:
mastbean maybean
數量關系: 多對一關系
一對多關系
一對一關系
多對多關系
第一範式，所有的屬性都必須是單值，也就是屬性只表示單一的意義。（記錄可以重復，沒有任何限制）
第二範式，屬性要求唯一且非空，（記錄不可重復，但是數據可能會出現冗餘）。
第三範式，非主屬性只能依賴於主屬性，不能依賴於其他非主屬性。（解決數據冗餘問題）
六、約束
約束是針對表中的欄位進行定義的。
primary key （主鍵約束 PK）保證實體的完整性，保證記錄的唯一
主鍵約束，唯一且非空，並且每一個表中只能有一個主鍵，有兩個欄位聯合作為主鍵，只有兩個欄位放在一起唯一標識記錄，叫做聯合主鍵。
foreign key （外建約束 FK）保證引用的完整性，
外鍵約束，外鍵的取值是受另外一張表中的主鍵或唯一值得約束，不能夠取其他值，只能夠引用主鍵會唯一鍵的值，被引用的表，叫做parent table（父表），引用方的表叫做child table（子表），要想創建子表，就要先創建父表，後創建子表，記錄的插入也是如此，先父表後子表，刪除記錄，要先刪除子表記錄，後刪除父表記錄，要修改記錄，如果要修改父表的記錄要保證沒有被子表引用。要刪表時，要先刪子表，後刪除父表。
unuque key（唯一鍵），值為唯一
index（索引）是資料庫特有的一類對象，view（示圖）
典型的一對多 class 對應多個學生。
student table class table
______________________________ _________________________
| id | name | address| class_id| | id |class_desc|class_num|
|(PK)|______|________|___(FK)__| |(pk)|__________|_________|
| | | | | | | | |
一對一
student tabel shenfenzheng table
____________________ _________________________________
| id | name | address| | s_id |shenfen_desc|shenfen_num|
|(PK)|______|________| |(PK，FK)|____________|___________|
| | | | | | | |
多對多
student tabel zhongjian table kecheng table
____________________ _________________________________ __________________
| id | name | address| | s_id |shenfen_desc|shenfen_num| | kid | kechengname|
|(PK)|______|________| |(FK，FK)|____________|___________| | (PK)|____________|
| | | | |聯合主鍵| | | | | |
引用對方表的主鍵,當作本身的主鍵,所以這個表的主鍵,既是主鍵又是外建
建表和其他相關操作
DDL語句
創建表：
create table 表名 ( 欄位名1 類型(數據長度)(default ...) 約束條件， 欄位名2 類型(數據長度) 約束條件 );
Oracle資料庫中的數據類型
varchar(長度)，可變長字元串，char(長度) 定長
number(..,..),number 表示浮點數，或者是整數
long 大對象，clog 字元的大對象，相當於文本文件在表中只存放一個相當於只針對值
blog 二進制的大對象，也是以相當於指針的形式存放的。
primary key約束：
主鍵約束的定義：
第一種定義形式：
create table test(c number primary key ); 列級約束
第二種定義形式：
create table test(c number , primary key(c) ) ; 表級約束
create table test( c1 number constraints pkc1 primary key ); 此約束有名字: pkc1
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表級約束可以實現聯合主鍵
foregin key (fk) 外鍵約束:
(先定義父表，再定義子表）
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表級約束定義:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
如果兩個欄位都為唯一且非空,這時可以定義成UK+NOT NULL
(PK或UK)一對多(FK)
(PK+UK)一對一(FK) 或 (PK)一對一(PK)
多對對多關系,一般都通過一張中間表來分解成兩個一對多的表
建立表
create table[schema]table
schema: 一個用戶對應一個schema 不同用戶下的表不能互相查看
select count(*) from s_dept; <===> select count(*) from sd0611.s_dept;
一個表中只能存儲一個LONG類型
CLOB 存儲大的文本對象
BLOB 存儲大的二進制對象
create table test(c1 number primary key); 設置主鍵
create table test(c1 number constraints test_c1 primary key); 定義約束名,默認約束名為SYS_ 在列後面定義約束稱為列級約束
create table test(c1 number primary key(c1)); 所有列定義完後再定義約束稱為表級約束(能定義聯合主鍵)
cretae table test(c1 number,c2 number,priary key(c1,c2)); 定義聯合主鍵
create table child(c1 number primary key); 先要定義父表
create table child(c1 number primary key, c2 number references parent(c1)); 然後定義子表 references parent定義外鍵
create table child(c1 number primary key, c2 number references parent(c1) on delete cascate); on delete cascate為級聯刪除
create table child(c1 number primary key, c2 number references parent(c1) on delete set null); on delete set null刪除後將外鍵置空
create table child (c1 number primary key, c2 number,foreignkey(c2) references parent(c1));
二、約束
1、非空約束（not null）
這是一個列級約束
在建表時,在數據類型的後面加上 not null ，也就是在插入時不允許插入空值。
例：create table student(id number primary key,name varchar2(32) not null,address varchar2(32));
2、unique 唯一約束
唯一約束，是會忽略空值的，唯一約束，要求插入的記錄中的值是為一的。
例：create table student(id number，name varchar2(32),address varchar2(32),primary key (id),unique (address));
如果創建一個uk，系統自動建一個唯一索引
3、pk、uk
Oralce支持級聯刪除，不支持級聯更新
4、check約束
檢查約束，可以按照指定條件，檢查記錄的插入。check中不能使用尾列，不能使用函數，不能引用其他欄位。
例：create table sal (a1 number , check(a1>1000));
C. 靜態SQL和動態SQL的區別和測試實例
所謂SQL的動態和靜態，是指SQL語句在何時被編譯和執行，二者都是用在SQL嵌入式編程中的。
靜態SQL：在高級語言中，如果嵌入了SQL語句，而這個SQL語句的主體結構已經明確，例如在c的一段代碼中有一個待執行的SQL「select * from t1 where c1>5」，在編譯階段，就可以將這段SQL交給資料庫管理系統去分析，資料庫軟體可以對這段SQL進行語法解析，生成資料庫方面的可執行代碼，這樣的SQL稱為靜態SQL，即在編譯階段就可以確定資料庫要做什麼事情。
動態SQL：如果嵌入的SQL沒有明確給出，如在c中定義了一個字元數組類型的變數name：char name[32];，然後採用prepared Statement對象的execute方法去執行這個sql，該sql的值可能等於從文本框中讀取的一個SQL或者從鍵盤輸入的SQL，但具體是什麼，在編譯時無法確定，只有等到程序運行起來，在執行的過程中才能確定，這種SQL叫做動態SQL。例如每一種資料庫軟體都有能夠執行SQL語句的界面，那個界面接收的SQL就是動態SQL，因為資料庫廠商在做這個界面時，並不知道用戶會輸入哪些SQL，只有在該界面執行後，接收了用戶的實際輸入，才知道SQL是什麼。
注意：在SQL中如果某些參數沒有確定，如」select * from t1 where c1>? and c2#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<unistd.h>
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
/*此函數屬於靜態SQL編程*/
int DBSelect_static(){
EXEC SQL BEGIN DECLARE SECTION;
char _typename[32];
short _length;
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT typename,length
INTO :_typename,:_length
FROM syscat.columns
WHERE tabname='SYSCOLUMNS' and colname='DEFAULT';
printf("【typename:%s】【length:%d】\n",_typename,_length);
}
/*此函數屬於靜態SQL編程：指定函數參數作為SQL語句的變數*/
int DBSelect_static_param(char *tbl_str,char *col_str){
EXEC SQL BEGIN DECLARE SECTION;
char _typename1[32];
short _length1;
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT typename,length
INTO :_typename1,:_length1
FROM syscat.columns
WHERE tabname='tbl_str' and colname='col_str';
printf("【typename:%s】【length:%d】\n",_typename1,_length1);
}
/*此函數屬於動態SQL編程：SQL語句的結構是不確定的，需要根據用戶的輸入補全SQL語句*/
int DBUpdate_dynamic(){
EXEC SQL BEGIN DECLARE SECTION;
char _address1[32];
char _tablename[32];
char _tmp[32];
char buf[256];
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT count(address1) INTO :_tmp FROM cisaddressinfo WHERE customid='100000100000000000178';
if(0==_tmp) {printf("Not Found!\n");return -1;}
memset(buf,0x00,sizeof(buf));
sprintf(buf,"update ");
printf("Pls input : tablename ->");
scanf("%s",&_tablename);
strcat(buf,_tablename);
strcat(buf," set address1=? where customid='100000100000000000178'");
EXEC SQL PREPARE project FROM :buf;
if(sqlca.sqlcode) perror("PREPARE");
printf("Pls input : address1 ->");
scanf("%s",&_address1);
EXEC SQL EXECUTE project USING :_address1;
if(sqlca.sqlcode) perror("EXECUTE");
EXEC SQL COMMIT WORK;
if(sqlca.sqlcode) perror("COMMIT");
}
/*此函數屬於動態SQL編程：使用sqlda數據結構組裝復雜多變的動態SQL*/
int DBSelect_dynamic(){
EXEC SQL BEGIN DECLARE SECTION;
char hostVarStmt[256];
EXEC SQL END DECLARE SECTION;
// 聲明兩個 SQLDA 指針，minsqlda 將是一個最小的 SQLDA 結構，用於 PREPARE 語句，
// 此時結果集的欄位數量未知，所以只需一個最小的 SQLDA，即包含 HEADER 和一個 SQLVAR
struct sqlda * minsqlda = (struct sqlda*)malloc(SQLDASIZE(1));
struct sqlda * fulsqlda = NULL;
strcpy(hostVarStmt, "select WUID from workprocess where muid = '185001'");
// PREPARE 將填寫 minsqlda 的 header，sqldabc 為 SQLDA 總長度，sqln 為 SQLVAR 數量，即欄位數量
EXEC SQL PREPARE STMT INTO :*minsqlda FROM :hostVarStmt;
// 根據從 minsqlda 中獲取的長度，分配完整的 SQLDA 結構 fulsqlda，其中將包括合適數量的 SQLVAR 結構
//結構sqlda的成員變數sqld返回select查詢語句的欄位的數目，可以根據此變數分配內存
fulsqlda = (struct sqlda *)malloc(SQLDASIZE(minsqlda->sqld));
// 使用 DESCRIBE 語句，獲取結果集中每個欄位的描述信息，包括各欄位的類型 (sqltype) 和長度 (sqllen)
EXEC SQL DESCRIBE STMT INTO :*fulsqlda;
int i;
for(i=0;i<minsqlda->sqld;i++)
{
// 根據每個欄位的長度，分配內存，將地址存儲在對應 SQLVAR 的 sqldata 中
// fulsqlda->sqlvar[i].sqldata=malloc(fulsqlda->sqlvar[i].sqllen);
fulsqlda->sqlvar[i].sqldata=malloc(32);
fulsqlda->sqlvar[i].sqlind=malloc(sizeof(short));
}
// 聲明游標
EXEC SQL DECLARE c1 CURSOR FOR STMT;
EXEC SQL OPEN c1;
EXEC SQL WHENEVER not found goto no_more_data;
// 讀取記錄，記錄中每個欄位的內容將寫入 fulsqlda 中對應 SQLVAR 結構的 sqldata 指向的內存
// EXEC SQL FETCH c1 USING DESCRIPTOR :*fulsqlda;
// 循環讀取所有記錄
for (;;)
{
EXEC SQL FETCH c1 USING DESCRIPTOR :*fulsqlda;
for(i=0;i<minsqlda->sqld;i++){
printf("%d %s\n",fulsqlda->sqlvar[i].sqltype,fulsqlda->sqlvar[i].sqldata);
usleep(10000);
}
}
return 0;
no_more_data:
printf("\nEND of Data\n");
free(minsqlda);
free(fulsqlda);
EXEC SQL CLOSE c1;
return 0;
}
int main(){
/*連接資料庫*/
EXEC SQL CONNECT TO ezeelink USER ezeelink USING EA704075ezeelink;
DBSelect_static();
DBSelect_static_param("SYSCOLUMNS","DEFAULT");
DBUpdate_dynamic();
DBSelect_dynamic();
no_more_data:
;
return 0;
}
案例輸出結果如下：
【typename:VARCHAR】【length:254】
【typename:VARCHAR】【length:254】
Pls input : tablename ->cisaddressinfo
Pls input : address1 ->ShangHai
452 cis505
452 cis506
452 pub806
452 ips007
452 ips032
452 dps302
END of Data
注意：
如果使用動態SQL編程編寫select查詢語句並保存結果，需要使用sqlda數據結構的，同時使用SQL的特性和功能，如：PREPARE ,EXECUTE ,DESCRIBE , DECLARE CURSE C1 FOR … , OPEN CURSE , CLOSE CURSE ….等等
建議：
動態SQL適用於表名及查詢欄位名未知的情況。在已知查詢欄位名及表名的情況下，使用動態SQL(字元串拼接方式)會增加硬解析的開銷，在這種情況下，建議使用靜態SQL，這樣可以提高執行效率。在過程過程用拼湊的動態sql效率並不高，有時候還不如程序直接傳遞sql.靜態SQL是前置編譯綁定，動態SQL是後期執行時才編譯綁定
D. SQL語句執行過程詳解
SQL語句執行過程詳解
一條sql，plsql的執行到底是怎樣執行的呢？
一、SQL語句執行原理：
第一步:客戶端把語句發給伺服器端執行當我們在客戶端執行 select 語句時,客戶端會把這條 SQL 語句發送給伺服器端,讓伺服器端的
進程來處理這語句。也就是說,Oracle 客戶端是不會做任何的操作,他的主要任務就是把客戶端產生
的一些 SQL 語句發送給伺服器端。雖然在客戶端也有一個資料庫進程,但是,這個進程的作用跟伺服器
上的進程作用事不相同的。伺服器上的資料庫進程才會對SQL 語句進行相關的處理。不過,有個問題需
要說明,就是客戶端的進程跟伺服器的進程是一一對應的。也就是說,在客戶端連接上伺服器後,在客戶
端與伺服器端都會形成一個進程,客戶端上的我們叫做客戶端進程;而伺服器上的我們叫做伺服器進程。
第二步:語句解析
當客戶端把 SQL 語句傳送到伺服器後,伺服器進程會對該語句進行解析。同理,這個解析的工作,
也是在伺服器端所進行的。雖然這只是一個解析的動作,但是,其會做很多「小動作」。
1. 查詢高速緩存(library cache)。伺服器進程在接到客戶端傳送過來的 SQL 語句時,不
會直接去資料庫查詢。而是會先在資料庫的高速緩存中去查找,是否存在相同語句的執行計劃。如果在
數據高速緩存中,則伺服器進程就會直接執行這個 SQL 語句,省去後續的工作。所以,採用高速數據緩
存的話,可以提高 SQL 語句的查詢效率。一方面是從內存中讀取數據要比從硬碟中的數據文件中讀取
數據效率要高,另一方面,也是因為這個語句解析的原因。
不過這里要注意一點,這個數據緩存跟有些客戶端軟體的數據緩存是兩碼事。有些客戶端軟體為了
提高查詢效率,會在應用軟體的客戶端設置數據緩存。由於這些數據緩存的存在,可以提高客戶端應用軟
件的查詢效率。但是,若其他人在伺服器進行了相關的修改,由於應用軟體數據緩存的存在,導致修改的
數據不能及時反映到客戶端上。從這也可以看出,應用軟體的數據緩存跟資料庫伺服器的高速數據緩存
不是一碼事。
2. 語句合法性檢查(data dict cache)。當在高速緩存中找不到對應的 SQL 語句時,則服
務器進程就會開始檢查這條語句的合法性。這里主要是對 SQL 語句的語法進行檢查,看看其是否合乎
語法規則。如果伺服器進程認為這條 SQL 語句不符合語法規則的時候,就會把這個錯誤信息,反饋給客
戶端。在這個語法檢查的過程中,不會對 SQL 語句中所包含的表名、列名等等進行 SQL 他只是語法
上的檢查。
3. 語言含義檢查(data dict cache)。若 SQL 語句符合語法上的定義的話,則伺服器進程
接下去會對語句中的欄位、表等內容進行檢查。看看這些欄位、表是否在資料庫中。如果表名與列名不
准確的話,則資料庫會就會反饋錯誤信息給客戶端。所以,有時候我們寫 select 語句的時候,若語法
與表名或者列名同時寫錯的話,則系統是先提示說語法錯誤,等到語法完全正確後,再提示說列名或表名
錯誤。
4. 獲得對象解析鎖(control structer)。當語法、語義都正確後,系統就會對我們需要查詢
的對象加鎖。這主要是為了保障數據的一致性,防止我們在查詢的過程中,其他用戶對這個對象的結構發
生改變。
5. 數據訪問許可權的核對(data dict cache)。當語法、語義通過檢查之後,客戶端還不一定
能夠取得數據。伺服器進程還會檢查,你所連接的用戶是否有這個數據訪問的許可權。若你連接上伺服器
的用戶不具有數據訪問許可權的話,則客戶端就不能夠取得這些數據。有時候我們查詢數據的時候,辛辛苦
苦地把 SQL 語句寫好、編譯通過,但是,最後系統返回個 「沒有許可權訪問數據」的錯誤信息,讓我們氣
半死。這在前端應用軟體開發調試的過程中,可能會碰到。所以,要注意這個問題,資料庫伺服器進程先
檢查語法與語義,然後才會檢查訪問許可權。
6. 確定最佳執行計劃 ?。當語句與語法都沒有問題,許可權也匹配的話,伺服器進程還是不會直接對
資料庫文件進行查詢。伺服器進程會根據一定的規則,對這條語句進行優化。不過要注意,這個優化是有
限的。一般在應用軟體開發的過程中,需要對資料庫的 sql 語言進行優化,這個優化的作用要大大地大
於伺服器進程的自我優化。所以,一般在應用軟體開發的時候,資料庫的優化是少不了的。當伺服器進程
的優化器確定這條查詢語句的最佳執行計劃後,就會將這條 SQL 語句與執行計劃保存到數據高速緩存
(library cache)。如此的話,等以後還有這個查詢時,就會省略以上的語法、語義與許可權檢查的步驟,
而直接執行 SQL 語句,提高 SQL 語句處理效率。
第三步:語句執行
語句解析只是對 SQL 語句的語法進行解析,以確保伺服器能夠知道這條語句到底表達的是什麼意
思。等到語句解析完成之後,資料庫伺服器進程才會真正的執行這條 SQL 語句。這個語句執行也分兩
種情況。
一是若被選擇行所在的數據塊已經被讀取到數據緩沖區的話,則伺服器進程會直接把這個數據傳遞
給客戶端,而不是從資料庫文件中去查詢數據。
若數據不在緩沖區中,則伺服器進程將從資料庫文件中查詢相關數據,並把這些數據放入到數據緩沖
區中(buffer cache)。
第四步:提取數據
當語句執行完成之後,查詢到的數據還是在伺服器進程中,還沒有被傳送到客戶端的用戶進程。所以,
在伺服器端的進程中,有一個專門負責數據提取的一段代碼。他的作用就是把查詢到的數據結果返回給
用戶端進程,從而完成整個查詢動作。從這整個查詢處理過程中,我們在資料庫開發或者應用軟體開發過
程中,需要注意以下幾點:
一是要了解資料庫緩存跟應用軟體緩存是兩碼事情。資料庫緩存只有在資料庫伺服器端才存在,在
客戶端是不存在的。只有如此,才能夠保證資料庫緩存中的內容跟資料庫文件的內容一致。才能夠根據
相關的規則,防止數據臟讀、錯讀的發生。而應用軟體所涉及的數據緩存,由於跟資料庫緩存不是一碼事
情,所以,應用軟體的數據緩存雖然可以提高數據的查詢效率,但是,卻打破了數據一致性的要求,有時候
會發生臟讀、錯讀等情況的發生。所以,有時候,在應用軟體上有專門一個功能,用來在必要的時候清除
數據緩存。不過,這個數據緩存的清除,也只是清除本機上的數據緩存,或者說,只是清除這個應用程序
的數據緩存,而不會清除資料庫的數據緩存。
二是絕大部分 SQL 語句都是按照這個處理過程處理的。我們 DBA 或者基於 Oracle 資料庫的
開發人員了解這些語句的處理過程,對於我們進行涉及到 SQL 語句的開發與調試,是非常有幫助的。有
時候,掌握這些處理原則,可以減少我們排錯的時間。特別要注意,資料庫是把數據查詢許可權的審查放在
語法語義的後面進行檢查的。所以,有時會若光用資料庫的許可權控制原則,可能還不能滿足應用軟體許可權
控制的需要。此時,就需要應用軟體的前台設置,實現許可權管理的要求。而且,有時應用資料庫的許可權管
理,也有點顯得繁瑣,會增加伺服器處理的工作量。因此,對於記錄、欄位等的查詢許可權控制,大部分程
序涉及人員喜歡在應用程序中實現,而不是在資料庫上實現。
DBCC DROPCLEANBUFFERS
從緩沖池中刪除所有清除緩沖區。
DBCC FREEPROCCACHE
從過程緩存中刪除所有元素。
DBCC FREESYSTEMCACHE
從所有緩存中釋放所有未使用的緩存條目
SQL語句中的函數、關鍵字、排序等執行順序:
1. FROM 子句返回初始結果集。
2. WHERE 子句排除不滿足搜索條件的行。
3. GROUP BY 子句將選定的行收集到 GROUP BY 子句中各個唯一值的組中。
4. 選擇列表中指定的聚合函數可以計算各組的匯總值。
5. 此外,HAVING 子句排除不滿足搜索條件的行。
6. 計算所有的表達式;
7. 使用 order by 對結果集進行排序。
8. 查找你要搜索的欄位。
二、SQL語句執行完整過程：
1.用戶進程提交一個 sql 語句:
update temp set a=a*2，給伺服器進程。
2.伺服器進程從用戶進程把信息接收到後,在 PGA 中就要此進程分配所需內存,存儲相關的信息,如在會
話內存存儲相關的登錄信息等。
3.伺服器進程把這個 sql 語句的字元轉化為 ASCII 等效數字碼,接著這個 ASCII 碼被傳遞給一個
HASH 函數,並返回一個 hash 值,然後伺服器進程將到shared pool 中的 library cache 中去查找是否存在相
同的 hash 值,如果存在,伺服器進程將使用這條語句已高速緩存在 SHARED POOL 的library cache 中的已
分析過的版本來執行。
4.如果不存在,伺服器進程將在 CGA 中,配合 UGA 內容對 sql,進行語法分析,首先檢查語法的正確性,接
著對語句中涉及的表,索引,視圖等對象進行解析,並對照數據字典檢查這些對象的名稱以及相關結構，並根據
ORACLE 選用的優化模式以及數據字典中是否存在相應對象的統計數據和是否使用了存儲大綱來生成一個
執行計劃或從存儲大綱中選用一個執行計劃,然後再用數據字典核對此用戶對相應對象的執行許可權,最後生成
一個編譯代碼。
5.ORACLE 將這條 sql 語句的本身實際文本、HASH 值、編譯代碼、與此語名相關聯的任何統計數據
和該語句的執行計劃緩存在 SHARED POOL 的 library cache中。伺服器進程通過 SHARED POOL 鎖存
器(shared pool latch)來申請可以向哪些共享 PL/SQL 區中緩存這此內容,也就是說被SHARED POOL 鎖存
器鎖定的 PL/SQL 區中的塊不可被覆蓋,因為這些塊可能被其它進程所使用。
6.在 SQL 分析階段將用到 LIBRARY
CACHE,從數據字典中核對表、視圖等結構的時候,需要將數據
字典從磁碟讀入 LIBRARY
CACHE,因此,在讀入之前也要使用LIBRARY
CACHE 鎖存器(library cache
pin,library cache lock)來申請用於緩存數據字典。 到現在為止,這個 sql 語句已經被編譯成可執行的代碼了,
但還不知道要操作哪些數據,所以伺服器進程還要為這個 sql 准備預處理數據。
7.首先伺服器進程要判斷所需數據是否在 db buffer 存在,如果存在且可用,則直接獲取該數據,同時根據
LRU 演算法增加其訪問計數;如果 buffer 不存在所需數據,則要從數據文件上讀取首先伺服器進程將在表頭部
請求 TM 鎖(保證此事務執行過程其他用戶不能修改表的結構),如果成功加 TM 鎖,再請求一些行級鎖(TX
鎖),如果 TM、TX 鎖都成功加鎖,那麼才開始從數據文件讀數據,在讀數據之前,要先為讀取的文件准備好
buffer 空間。伺服器進程需要掃面 LRU list 尋找 free db buffer,掃描的過程中,伺服器進程會把發現的所有
已經被修改過的 db buffer 注冊到 dirty list 中, 這些 dirty buffer 會通過 dbwr 的觸發條件,隨後會被寫出到
數據文件,找到了足夠的空閑 buffer,就可以把請求的數據行所在的數據塊放入到 db buffer 的空閑區域或者
覆蓋已經被擠出 LRU list 的非臟數據塊緩沖區,並排列在 LRU list 的頭部,也就是在數據塊放入 DB
BUFFER 之前也是要先申請 db buffer 中的鎖存器,成功加鎖後,才能讀數據到 db buffer。
8.記日誌 現在數據已經被讀入到 db buffer 了,現在伺服器進程將該語句所影響的並被讀
入 db buffer 中的這些行數據的 rowid 及要更新的原值和新值及 scn 等信息從 PGA 逐條的寫入 redo log
buffer 中。在寫入 redo log buffer 之前也要事先請求 redo log buffer 的鎖存器,成功加鎖後才開始寫入,當
寫入達到 redo log buffer 大小的三分之一或寫入量達到 1M 或超過三秒後或發生檢查點時或者 dbwr 之前
發生,都會觸發 lgwr 進程把 redo log buffer 的數據寫入磁碟上的 redo file 文件中(這個時候會產生log file
sync 等待事件)
已經被寫入 redofile 的 redo log buffer 所持有的鎖存器會被釋放,並可被後來的寫入信息覆蓋,
redo log buffer是循環使用的。Redo file 也是循環使用的,當一個 redo file 寫滿後,lgwr 進程會自動切換到
下一 redo file(這個時候可能出現 log fileswitch(checkpoint complete)等待事件)。如果是歸檔模式,歸檔進
程還要將前一個寫滿的 redo file 文件的內容寫到歸檔日誌文件中(這個時候可能出現 log file
switch(archiving needed)。
9.為事務建立回滾段 在完成本事務所有相關的 redo log buffer 之後,伺服器進程開始改寫這個 db buffer
的塊頭部事務列表並寫入 scn,然後 包含這個塊的頭部事務列表及 scn 信息的數據副本放入回滾段中,將
這時回滾段中的信息稱為數據塊的「前映像「,這個」前映像「用於以後的回滾、恢復和一致性讀。(回滾段可以
存儲在專門的回滾表空間中,這個表空間由一個或多個物理文件組成,並專用於回滾表空間,回滾段也可在其它
表空間中的數據文件中開辟。
10.本事務修改數據塊 准備工作都已經做好了,現在可以改寫 db buffer 塊的數據內容了,並在塊的頭部寫
入回滾段的地址。
11.放入 dirty list 如果一個行數據多次 update 而未 commit,則在回滾段中將會有多個「前映像「,除了第
一個」前映像「含有 scn 信息外,其他每個「前映像「的頭部都有 scn 信息和「前前映像」回滾段地址。一個
update 只對應一個 scn,然後伺服器進程將在 dirty list 中建立一
條指向此 db buffer 塊的指針(方便 dbwr 進程可以找到 dirty list 的 db buffer 數據塊並寫入數據文件中)。
接著伺服器進程會從數據文件中繼續讀入第二個數據塊,重復前一數據塊的動作,數據塊的讀入、記日誌、建
立回滾段、修改數據塊、放入 dirty list。當 dirty queue 的長度達到閥值(一般是 25%),伺服器進程將通知
dbwr 把臟數據寫出,就是釋放 db buffer 上的鎖存器,騰出更多的 free db buffer。前面一直都是在說明
oracle 一次讀一個數據塊,其實 oracle 可以一次讀入多個數據塊(db_file_multiblock_read_count 來設置一
次讀入塊的個數)
說明:
在預處理的數據已經緩存在 db buffer 或剛剛被從數據文件讀入到 db buffer 中,就要根據 sql 語句
的類型來決定接下來如何操作。
1>如果是 select 語句,則要查看 db buffer 塊的頭部是否有事務,如果有事務,則從回滾段中讀取數據;如
果沒有事務,則比較 select 的 scn 和 db buffer 塊頭部的 scn,如果前者小於後者,仍然要從回滾段中讀取數據;
如果前者大於後者,說明這是一非臟緩存,可以直接讀取這個 db buffer 塊的中內容。
2>如果是 DML 操作,則即使在 db buffer 中找到一個沒有事務,而且 SCN 比自己小的非臟
緩存數據塊,伺服器進程仍然要到表的頭部對這條記錄申請加鎖,加鎖成功才能進行後續動作,如果不成功,則要
等待前面的進程解鎖後才能進行動作(這個時候阻塞是 tx 鎖阻塞)。
用戶 commit 或 rollback 到現在為止,數據已經在 db buffer 或數據文件中修改完
成,但是否要永久寫到數文件中,要由用戶來決定 commit(保存更改到數據文件) rollback 撤銷數據的更改)。
1.用戶執行 commit 命令
只有當 sql 語句所影響的所有行所在的最後一個塊被讀入 db buffer 並且重做信息被寫入 redo log
buffer(僅指日誌緩沖區,而不包括日誌文件)之後,用戶才可以發去 commit 命令,commit 觸發 lgwr 進程,但不
強制立即 dbwr來釋放所有相應 db buffer 塊的鎖(也就是no-force-at-commit,即提交不強制寫),也就是說有
可能雖然已經 commit 了,但在隨後的一段時間內 dbwr 還在寫這條 sql 語句所涉及的數據塊。表頭部的行鎖
並不在 commit 之後立即釋放,而是要等 dbwr 進程完成之後才釋放,這就可能會出現一個用戶請求另一用戶
已經 commit 的資源不成功的現象。
A .從 Commit 和 dbwr 進程結束之間的時間很短,如果恰巧在 commit 之後,dbwr 未結束之前斷電,因為
commit 之後的數據已經屬於數據文件的內容,但這部分文件沒有完全寫入到數據文件中。所以需要前滾。由
於 commit 已經觸發 lgwr,這些所有未來得及寫入數據文件的更改會在實例重啟後,由 smon 進程根據重做日
志文件來前滾,完成之前 commit 未完成的工作(即把更改寫入數據文件)。
B.如果未 commit 就斷電了,因為數據已經在 db buffer 更改了,沒有 commit,說明這部分數據不屬於數
據文件,由於 dbwr 之前觸發 lgwr 也就是只要數據更改,(肯定要先有 log) 所有 DBWR,在數據文件上的修改
都會被先一步記入重做日誌文件,實例重啟後,SMON 進程再根據重做日誌文件來回滾。
其實 smon 的前滾回滾是根據檢查點來完成的,當一個全部檢查點發生的時候,首先讓 LGWR 進程將
redo log buffer 中的所有緩沖(包含未提交的重做信息)寫入重做日誌文件,然後讓 dbwr 進程將 db buffer 已
提交的緩沖寫入數據文件(不強制寫未提交的)。然後更新控制文件和數據文件頭部的 SCN,表明當前資料庫
是一致的,在相鄰的兩個檢查點之間有很多事務,有提交和未提交的。
像前面的前滾回滾比較完整的說法是如下的說明:
A.發生檢查點之前斷電,並且當時有一個未提交的改變正在進行,實例重啟之後,SMON 進程將從上一個
檢查點開始核對這個檢查點之後記錄在重做日誌文件中已提交的和未提交改變,因為
dbwr 之前會觸發 lgwr,所以 dbwr 對數據文件的修改一定會被先記錄在重做日誌文件中。因此,斷電前被
DBWN 寫進數據文件的改變將通過重做日誌文件中的記錄進行還原,叫做回滾,
B. 如果斷電時有一個已提交,但 dbwr 動作還沒有完全完成的改變存在,因為已經提交,提交會觸發 lgwr
進程,所以不管 dbwr 動作是否已完成,該語句將要影響的行及其產生的結果一定已經記錄在重做日誌文件中
了,則實例重啟後,SMON 進程根據重做日誌文件進行前滾.
實例失敗後用於恢復的時間由兩個檢查點之間的間隔大小來決定,可以通個四個參數設置檢查點執行的頻
率:
Log_checkpoint_interval:
決定兩個檢查點之間寫入重做日誌文件的系統物理塊(redo blocks)
的大小,默認值是 0,無限制。
log_checkpoint_timeout:
兩 個 檢 查 點 之 間 的 時 間 長 度（秒）默 認 值 1800s。
fast_start_io_target:
決定了用於恢復時需要處理的塊的多少,默認值是 0,無限制。
fast_start_mttr_target:
直接決定了用於恢復的時間的長短,默認值是 0,無限制(SMON 進程執行的前滾
和回滾與用戶的回滾是不同的,SMON 是根據重做日誌文件進行前滾或回滾,而用戶的回滾一定是根據回滾段
的內容進行回滾的。
在這里要說一下回滾段存儲的數據,假如是 delete 操作,則回滾段將會記錄整個行的數據,假如是 update,
則回滾段只記錄被修改了的欄位的變化前的數據(前映像),也就是沒有被修改的欄位是不會被記錄的,假如是
insert,則回滾段只記錄插入記錄的 rowid。 這樣假如事務提交,那回滾段中簡單標記該事務已經提交;假如是
回退,則如果操作是 delete,回退的時候把回滾段中數據重新寫回數據塊,操作如果是 update,則把變化前數據
修改回去,操作如果是 insert,則根據記錄的 rowid 把該記錄刪除。
2.如果用戶 rollback。
則伺服器進程會根據數據文件塊和 DB BUFFER 中塊的頭部的事務列表和 SCN 以及回滾段地址找到
回滾段中相應的修改前的副本,並且用這些原值來還原當前數據文件中已修改但未提交的改變。如果有多個
「前映像」,伺服器進程會在一個「前映像」的頭部找到「前前映像」的回滾段地址,一直找到同一事務下的最早的
一個「前映像」為止。一旦發出了 COMMIT,用戶就不能rollback,這使得 COMMIT 後 DBWR 進程還沒有
全部完成的後續動作得到了保障。到現在為例一個事務已經結束了。
說明:
TM 鎖:
符合 lock 機制的,用於保護對象的定義不被修改。 TX 鎖:
這個鎖代表一個事務,是行
級鎖,用數據塊頭、數據記錄頭的一些欄位表示,也是符合 lock 機制,有 resource structure、lock
structure、enqueue 演算法。
E. MySQL資料庫查詢欄位屬性（即表結構）的SQL語句怎麼寫
DESC mydb.mytable ;這樣可以不？
更詳細的，可以用SHOW FULL COLUMNS FROM mydb.mytable ;
F. 一條sql的整個過程是怎樣的
SQL語句執行過程詳解
一、SQL語句執行原理：
第一步:客戶端把語句發給伺服器端執行
當我們在客戶端執行 select 語句時,客戶端會把這條 SQL 語句發送給伺服器端,讓伺服器端的
進程來處理這語句。也就是說,Oracle 客戶端是不會做任何的操作,他的主要任務就是把客戶端產生
的一些 SQL 語句發送給伺服器端。雖然在客戶端也有一個資料庫進程,但是,這個進程的作用跟伺服器
上的進程作用事不相同的。伺服器上的資料庫進程才會對SQL 語句進行相關的處理。不過,有個問題需
要說明,就是客戶端的進程跟伺服器的進程是一一對應的。也就是說,在客戶端連接上伺服器後,在客戶
端與伺服器端都會形成一個進程,客戶端上的我們叫做客戶端進程;而伺服器上的我們叫做伺服器進程。
第二步:語句解析
當客戶端把 SQL 語句傳送到伺服器後,伺服器進程會對該語句進行解析。同理,這個解析的工作,
也是在伺服器端所進行的。雖然這只是一個解析的動作,但是,其會做很多「小動作」。
1. 查詢高速緩存(library cache)。伺服器進程在接到客戶端傳送過來的 SQL 語句時,不
會直接去資料庫查詢。而是會先在資料庫的高速緩存中去查找,是否存在相同語句的執行計劃。如果在
數據高速緩存中,則伺服器進程就會直接執行這個 SQL 語句,省去後續的工作。所以,採用高速數據緩
存的話,可以提高 SQL 語句的查詢效率。一方面是從內存中讀取數據要比從硬碟中的數據文件中讀取
數據效率要高,另一方面,也是因為這個語句解析的原因。
不過這里要注意一點,這個數據緩存跟有些客戶端軟體的數據緩存是兩碼事。有些客戶端軟體為了
提高查詢效率,會在應用軟體的客戶端設置數據緩存。由於這些數據緩存的存在,可以提高客戶端應用軟
件的查詢效率。但是,若其他人在伺服器進行了相關的修改,由於應用軟體數據緩存的存在,導致修改的
數據不能及時反映到客戶端上。從這也可以看出,應用軟體的數據緩存跟資料庫伺服器的高速數據緩存
不是一碼事。
2. 語句合法性檢查(data dict cache)。當在高速緩存中找不到對應的 SQL 語句時,則服
務器進程就會開始檢查這條語句的合法性。這里主要是對 SQL 語句的語法進行檢查,看看其是否合乎
語法規則。如果伺服器進程認為這條 SQL 語句不符合語法規則的時候,就會把這個錯誤信息,反饋給客
戶端。在這個語法檢查的過程中,不會對 SQL 語句中所包含的表名、列名等等進行 SQL 他只是語法
上的檢查。
3. 語言含義檢查(data dict cache)。若 SQL 語句符合語法上的定義的話,則伺服器進程
接下去會對語句中的欄位、表等內容進行檢查。看看這些欄位、表是否在資料庫中。如果表名與列名不
准確的話,則資料庫會就會反饋錯誤信息給客戶端。所以,有時候我們寫 select 語句的時候,若語法
與表名或者列名同時寫錯的話,則系統是先提示說語法錯誤,等到語法完全正確後,再提示說列名或表名
錯誤。
4. 獲得對象解析鎖(control structer)。當語法、語義都正確後,系統就會對我們需要查詢
的對象加鎖。這主要是為了保障數據的一致性,防止我們在查詢的過程中,其他用戶對這個對象的結構發
生改變。
5. 數據訪問許可權的核對(data dict cache)。當語法、語義通過檢查之後,客戶端還不一定
能夠取得數據。伺服器進程還會檢查,你所連接的用戶是否有這個數據訪問的許可權。若你連接上伺服器
的用戶不具有數據訪問許可權的話,則客戶端就不能夠取得這些數據。有時候我們查詢數據的時候,辛辛苦
苦地把 SQL 語句寫好、編譯通過,但是,最後系統返回個 「沒有許可權訪問數據」的錯誤信息,讓我們氣
半死。這在前端應用軟體開發調試的過程中,可能會碰到。所以,要注意這個問題,資料庫伺服器進程先
檢查語法與語義,然後才會檢查訪問許可權。
6. 確定最佳執行計劃 。當語句與語法都沒有問題,許可權也匹配的話,伺服器進程還是不會直接對
資料庫文件進行查詢。伺服器進程會根據一定的規則,對這條語句進行優化。不過要注意,這個優化是有
限的。一般在應用軟體開發的過程中,需要對資料庫的 sql 語言進行優化,這個優化的作用要大大地大
於伺服器進程的自我優化。所以,一般在應用軟體開發的時候,資料庫的優化是少不了的。當伺服器進程
的優化器確定這條查詢語句的最佳執行計劃後,就會將這條 SQL 語句與執行計劃保存到數據高速緩存
(library cache)。如此的話,等以後還有這個查詢時,就會省略以上的語法、語義與許可權檢查的步驟,
而直接執行 SQL 語句,提高 SQL 語句處理效率。
第三步:語句執行
語句解析只是對 SQL 語句的語法進行解析,以確保伺服器能夠知道這條語句到底表達的是什麼意
思。等到語句解析完成之後,資料庫伺服器進程才會真正的執行這條 SQL 語句。這個語句執行也分兩
種情況。
一是若被選擇行所在的數據塊已經被讀取到數據緩沖區的話,則伺服器進程會直接把這個數據傳遞
給客戶端,而不是從資料庫文件中去查詢數據。
若數據不在緩沖區中,則伺服器進程將從資料庫文件中查詢相關數據,並把這些數據放入到數據緩沖
區中(buffer cache)。
第四步:提取數據
當語句執行完成之後,查詢到的數據還是在伺服器進程中,還沒有被傳送到客戶端的用戶進程。所以,
在伺服器端的進程中,有一個專門負責數據提取的一段代碼。他的作用就是把查詢到的數據結果返回給
用戶端進程,從而完成整個查詢動作。從這整個查詢處理過程中,我們在資料庫開發或者應用軟體開發過
程中,需要注意以下幾點:
一是要了解資料庫緩存跟應用軟體緩存是兩碼事情。資料庫緩存只有在資料庫伺服器端才存在,在
客戶端是不存在的。只有如此,才能夠保證資料庫緩存中的內容跟資料庫文件的內容一致。才能夠根據
相關的規則,防止數據臟讀、錯讀的發生。而應用軟體所涉及的數據緩存,由於跟資料庫緩存不是一碼事
情,所以,應用軟體的數據緩存雖然可以提高數據的查詢效率,但是,卻打破了數據一致性的要求,有時候
會發生臟讀、錯讀等情況的發生。所以,有時候,在應用軟體上有專門一個功能,用來在必要的時候清除
數據緩存。不過,這個數據緩存的清除,也只是清除本機上的數據緩存,或者說,只是清除這個應用程序
的數據緩存,而不會清除資料庫的數據緩存。
二是絕大部分 SQL 語句都是按照這個處理過程處理的。我們 DBA 或者基於 Oracle 資料庫的
開發人員了解這些語句的處理過程,對於我們進行涉及到 SQL 語句的開發與調試,是非常有幫助的。有
時候,掌握這些處理原則,可以減少我們排錯的時間。特別要注意,資料庫是把數據查詢許可權的審查放在
語法語義的後面進行檢查的。所以,有時會若光用資料庫的許可權控制原則,可能還不能滿足應用軟體許可權
控制的需要。此時,就需要應用軟體的前台設置,實現許可權管理的要求。而且,有時應用資料庫的許可權管
理,也有點顯得繁瑣,會增加伺服器處理的工作量。因此,對於記錄、欄位等的查詢許可權控制,大部分程
序涉及人員喜歡在應用程序中實現,而不是在資料庫上實現。
DBCC DROPCLEANBUFFERS
從緩沖池中刪除所有清除緩沖區。
DBCC FREEPROCCACHE
從過程緩存中刪除所有元素。
DBCC FREESYSTEMCACHE
從所有緩存中釋放所有未使用的緩存條目
SQL語句中的函數、關鍵字、排序等執行順序:
1. FROM 子句返回初始結果集。
2. WHERE 子句排除不滿足搜索條件的行。
3. GROUP BY 子句將選定的行收集到 GROUP BY 子句中各個唯一值的組中。
4. 選擇列表中指定的聚合函數可以計算各組的匯總值。
5. 此外,HAVING 子句排除不滿足搜索條件的行。
6. 計算所有的表達式;
7. 使用 order by 對結果集進行排序。
8. 查找你要搜索的欄位。