第一部分 基本概念
一、查詢系統表select * from user_tables 查詢目前使用者所有表
select * from user_triggers 查詢目前使用者所有觸發器
select * from user_procedures 查詢目前使用者所有存儲過程
二、分組函數的用法max()
min()
avg()
sum()
count()
rollup()
注意：
1)分組函數隻能出現在 group by ,having,order by 子句中，并且order by 隻能放在最後
2)如果選擇清單中有列，表達式和分組函數，那麼列，表達式必須要出現在group by 函數中
3)當限制分組顯示結果時，必須出現在having子句中，而不能在where子句中
rollup 函數 函數用于形成小合計
Group by 隻會生成列相應資料統計
select deptno,job,avg(sal) from emp group by deptno,job;
rollup 會在原來的統計基礎上，生成小統計
select deptno,job,avg(sal) from emp group by rollup (deptno,job);
顯示每個崗位的平均工資，每個部門的平均工資，和所有雇員的平均工資
cube 提供按多個字段彙總的功能
顯示每個部門每個崗位的平均工資，每個崗位的平均工資，每個部門的平均工資，和所有雇員的平均工資
select deptno,job,avg(sal) from emp group by cube (deptno,job);
多種分組資料結果 grouping sets 操作
顯示部門平均工資
select deptno,avg(sal) from emp group by emp.deptno,
顯示崗位平均工資
select job,avg(sal) from emp group by emp.job
顯示部門和崗位平均工資，合并上面兩個結果
select deptno,job,avg(sal) from emp group by grouping sets(emp.deptno,emp.job);
等價與:
select deptno,null,avg(sal) from emp group by emp.deptno
union all
select null,job,avg(sal) from emp group by emp.job
三 (+) 連接配接+ 隻能用在where子句中，而且放在顯示較少的行那一邊，不能和outer join文法同時使用
+ 如果在where 子句中有多個條件，每個條件都必須加上+
+ 不能用于表達式，隻能用于列，不能和in or一起使用。
四 字元函數instr() substr() ltrim()
lower('SQL server') sql server 都轉換為小寫字元
upper('morning') MORNING 都轉換為大寫字元
select instr('monring','ng') from dual; 子字元串在字元串中的位置 傳回6
ltrim() 從左端掃描去掉出現字元set的字元，直到遇到不是set的值傳回
select ltrim('Mmoning' 'M') from dual;
傳回moning
substr('monring',1,3) 取到子字元串,第二個參數為起始位置，3為長度 結果傳回mon
第二部分 pl/sql 基礎
一、pl/sql 資料類型boolean 類型定義，
v_bflag boolean not null default false;
常量定義
v_tax_rate constant number(3,2):=0.03
定義數組
type arrays is table of varchar2(200);
使用 %type類型
使用這個屬性時候，它會按照資料庫列或其它變量來确定新變量的類型和長度
v_name emp.name%type
1)單行多列 使用pl/sql 記錄 a.自定義
type emp_record_type is RECORD(
name emp.name%TYPE,--定義成emp表中name的資料類型
sal emp.sal ,
);
emp_record emp_record_type;
使用：
emp_record.name
b.定義行記錄
dept_record dept%ROWTYPE
2)為了處理多行單列，多行多列使用pl/sql集合如： 索引表，嵌套表，varray a) pl/sql表(索引表)。隻能用在pl/sql中同種類型的一維、無邊界的同類元素稀疏集合，且隻能是由一列組成。
定義：type ename_table_type is table of varchar2(200) index by binary_integer;
或者type ename_table_type is table of emp.ename%type index by binary_integer;
聲明：ename_table ename_table_type;
select ename into arrays(-1) from emp where empno=7788 把員工姓名放入 下标為-1的空間。
b) 嵌套表 也是一維、無邊界稀疏集合
定義：type ename_table_type is table of varchar2(200);
或者type arrays is table of emp%name;
嵌套表的下标從1開始，也是屬于稀疏集合，和索引表的差別除了開始下标以為，而且嵌套表是可以做為 可以做為表列的資料類型，
而索引表不可以。并且必須使用初始化構造函數初始化
emp_array ename_table_type=:ename_table_type();//構造方法初始化
eg:
type emp_table_type is table of emp.name%type;
emp_table emp_table_type:=emp_table_type();
select ename into emp_table(2) from emp where empno='1779';
dbms_output.putLine(emp_table(2));
結果：scott
--定義數組類型
type arrays is table of varchar2(200);
--初始化
dataList arrays := arrays();
--作為參數傳遞
procedure pro_name(dataList is arrays) is
begin
end ;
/*
用指定字元将字元串分隔為數組
str 字元串
str_split 分割字元
return 分割後的數組
*/
function splitString(sourceString varchar2, splitFlag varchar2)
return arrays is
dataList arrays := arrays(); --一定要，構造函數初始化
temp varchar2(200); --存儲臨時資料
tempSource varchar2(1000) := sourceString; --存儲需要分割的字元串()
splitIndex number(2); --分隔符在字元串中首次出現的位置
splitLength number(2) := length(splitFlag); --分割符長度
begin
while instr(tempSource, splitFlag) > 0 loop
splitIndex := instr(tempSource, splitFlag);
--取出第一個分割符前的字元串
temp := substr(tempSource, 1, splitIndex - splitLength);
--減去第一個分隔符前的字元串
tempSource := substr(tempSource,
splitIndex + splitLength,
length(tempSource));
if temp is not null then
dataList.extend(1);
dataList(dataList.count) := temp;
end if;
end loop;
if tempSource is not null then
dataList.extend(1);
dataList(dataList.count) := tempSource;
end if;
return dataList;
end splitString;
c) varray 變長數組 做為表列的資料類型，并且必須指定數組的大小
TYPE t_name IS VARRAY(2) OF VARCHAR2 (10); 定義了一個字元類型的數組，并且隻能存儲兩個元素
存儲對象類型：
create type article_type as OBJETC(
title varchar2(20),
pubDate DATE
)
create type article_array is varray(2) of article_type
可以當作使用者自定義資料類型來引用：
create table author(
id number,
name varchar2,
arti article
)
這些集合内置的一些方法：
COUNT 傳回集合中元素的個數
DELETE 删除集合中所有元素
DELETE(x) 删除元素下标為x的元素 對VARRAY非法
DELETE(x,y) 删除元素下标從X到Y的元素 對VARRAY非法
EXIST(x) 如果集合元素x已經初始化，則傳回TRUE, 否則傳回FALSE
EXTEND 在集合末尾添加一個元素 對Index_by非法
EXTEND(x) 在集合末尾添加x個元素 對Index_by非法
EXTEND(x,n) 在集合末尾添加元素n的x個副本 對Index_by非法
FIRST 傳回集合中的第一個元素的下标号，對于VARRAY集合始終傳回1。
LAST 傳回集合中最後一個元素的下标号, 對于VARRAY傳回值始終等于COUNT.
3) 為了處理多行多列 pl/sql 記錄表
type emp_table_type is table(
)
二、控制結構 分支，循環，順序
if then
statement;
else
statement;
end if;
case 在多重分支應用
case op
when exp1 then;
when exp2 then;
循環
基本循環至少執行一次
loop
statement;
exit(when condition) condition為true 退出循環
end loop
隻有條件為真時才執行循環體
while condition loop
statement
end loop;
for int i in 1..N loop --循環變量由oracle隐式定義，不需要顯示定義
statement
end loop;
三、遊标(1) 遊标類型分為靜态遊标和動态遊标
1)靜态遊标
靜态遊标又分為隐式和顯示兩種
a、隐式遊标(開發人員沒有為sql語句顯示聲明一個遊标)是當你在執行DML操作傳回單行記錄時(update insert delete)
會自動聲明一個隐式遊标。
隐式遊标屬性:
SQL%found, sql語句執行是否成功，如果作用行則為true,如果沒有則為false
SQL%notfound,
SQL%rowcount sql語句作用的總行數。
SQL%isopen
b、顯示遊标 傳回多行。開發中一般都用顯示遊标。
帶參數的顯式遊标
cursor name_cur(com_id in number)
is select name from company where company_id = com_id;
使用
for temp_cur in name_cur(0) loop
end loop;
帶傳回值的顯式遊标
cursor emp_cur return employee%rowType --遊标聲明
is select * from employee --遊标體
where department_id =10
2)動态遊标
(1)動态遊标例子
strSql:=' select tvi.gap ';
strSql:=strSql||' from TEMP_VD_RP_AJUST tvi ';
strSql:=strSql||' where tvi.week in('||nWeeks||')';
strSql:=strSql||' group by tvi.ship_to_code, tvi.product_name, tvi.week, tvi.gap ';
open gapCur for strSql;
loop
fetch gapCur into gap;
exit when gapCur%notfound; --沒有記錄退出
if(gap<0) then
bFlag := true;
exit; --隻要存在一個為負值，退出
end if;
end loop;
close gapCur;
(2) 使用遊标更改資料
使用顯示遊标在更改目前遊标行的時候必須帶for update
cursor cur_name is select * from emp for update(of column)
在插入或者删除子句中必須加where current of cur_name;
eg: 給工資少于2000的員工加100工資
cursor emp_cur is select emp.name,emp.sal form emp for update;
for temp_emp fro emp_cur loop;
if(emp.sal<2000) then
update emp set emp.sal=sal+100 where current of emp_cur;
end if;
end loop;
(3) 遊标變量
聲明一個引用資料庫中遊标對象的變量，該變量可以在不同時間指向不同的sql語句，
遊标變量是指向多行查遊标詢的結果集的目前行,遊标是靜态的,遊标變量是動态的
遊标變量并不參與與特定的查詢綁定,是以可以為任何相容的查詢打開遊标變量
還可以将新的值賦予遊标變量 , 将它作為參數傳遞給本地和存儲過程。
注 : 遊标總是指向相同的查詢工作區 , 遊标變量能夠指向不同的工作區 , 是以遊标和遊标變量不能互操作
type cur_type is ref cursor return emp%rowType;
emp_cur = cur_type;
open emp_cur for select * from emp;
loop
fetch emp_cur into v_1,v_2;
end loop;
close emp_cur;
emp_cur 可以為不同的sql查詢，每次都執行查詢結果集合的第一行。
注意 :
REF CURSOR 類型既可以是強類型 , 也可以是弱類型 , 差別是強類型有傳回類型 , 弱類型沒有
當作為函數參數傳遞
PROCEDURE open_emp (emp_cur IN OUT cur_type)IS
begin
open emp_cur for select * from emp;
end
定義遊标變量的例子：
type rc is ref cursor;
cursor c is select * from dual;
l_cursor rc;
begin
if ( to_char(sysdate,'dd') = 30 ) then
open l_cursor for 'select * from emp';
elsif ( to_char(sysdate,'dd') = 29 ) then
open l_cursor for select * from dept;
else
open l_cursor for select * from dual;
end if;
open c;
end;
rc根據邏輯動态打開；而遊标c定義好了隻有就無法修改了。
ref cursor可以傳回給用戶端，cursor則不行。
cursor可以是全局的global ，ref cursor則必須定義在過程或函數中。
ref cursor可以在子程式間傳遞，cursor則不行。
cursor中定義的靜态sql比ref cursor效率高，是以ref cursor通常用在向用戶端傳回結果集。
(4) 定義遊标
顯示遊标 傳回多行。開發中一般都用顯示遊标
提取遊标資料
fetch emp_cur into variable1,variable2
提取所有資料
fetch..bulk collect into
eg:
type emp_table is table of varchar2(10)
fetch emp_cur bulk collect into emp_table
for i in 1.. emp_table.count loop
dbms_output.putLine(emp_table(i));
end loop;
使用遊标屬性
emp_cur%isopen,
emp_cur%found, --是否從結果集中提取到了資料
emp_cur%notfound
emp_cur%rowcount --目前已經提取到的實際行數
隐式遊标,自動做了打開關閉
type cursor emp_cur is select * from emp_cur;
emp_record dept%ROWTYPE
for emp_record in emp_cur loop
dbms_output.putLine(emp_record.name);
end loop;
四、動态sql 注意：靜态sql不能處理DDL操作
(1) using 子句 預設的綁定參數類型是in
可以使用參數 using param1,param2
procedure execImmediate is
strSql varchar2(200);
begin
strSql:='select count(*) from emp t where t.ename = :1 ';
execute immediate strSql using 'SCOTT';
end execImmediate;
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)