PLSQL 1.常用的訪問資料庫的相關技術 1) plsql 過程化的sql 2) proc/c++ 在c/c++語言中訪問oracle資料庫的技術 3) ado/odbc vc中訪問資料庫的技術 4) oci oracle底層提供的客戶端的連接介面 5) sql j/jdbc java訪問資料庫的 ...
PLSQL
1.常用的訪問資料庫的相關技術
1) plsql 過程化的sql
2) proc/c++ 在c/c++語言中訪問oracle資料庫的技術
3) ado/odbc vc中訪問資料庫的技術
4) oci oracle底層提供的客戶端的連接介面
5) sql j/jdbc java訪問資料庫的技術
2.PLSQL
2.1 概念
plsql(procedured language/sql)是在標準sql的基礎上增加了過程化的處理形成的語言
oracle客戶端工具訪問oracle伺服器的操作語言
是對sql的擴充
2.2 特點
結構化模塊化編程
良好的可移植性
良好的可維護性
提升系統性能
不便於向異構資料庫移植
3.SQL語言的特點
機器語言 彙編語言 高級語言 結構化語言
只管做什麼 不管怎麼做
沒有過程和控制結構
沒有演算法描述能力
4.plsql擴充了sql
1) 變數和數據類型
2) 控制語句
3) 過程和函數
4) 對象類型和方法
5.plsql的程式框架
declare
/* 聲明區 聲明變數、定義類型等 /
-- 單行註釋 '--'
-- 多行註釋 /內 容/
-- 如果沒有需要聲明、定義的內容,聲明區可以省略
begin
/ 執行區 執行sql或plsql語句 /
exception
/ 異常處理區 處理異常的區域 /
/ 可省略 */
end;
/ -- 表示plsql代碼結束 必須獨立占一行
6.plsql的開發工具
sqlplus 命令提示符的工具
plsqldeveloper 圖形化的開發工具
begin
dbms_output.put_line('Hello World!');
end;
/
-- 打開輸出功能
set serveroutput on
7.標識符
7.1 作用
給變數、數據類型、游標、過程、函數、觸發器、包等命名
7.2 使用變數
declare
變數名 數據類型;
變數名 數據類型 := 值;
begin
變數名 := 值;
-- 聲明兩個變數,賦值並輸出
declare
var_id number;
var_name varchar2(20):='test';
begin
var_id:=1;
dbms_output.put_line(var_id||','||var_name);
end;
/
8.變數和數據類型
8.1 數據類型
1) 標量類型
number binary_integer 數字類型
char varchar2 字元串類型
date 日期類型
boolean 布爾類型
2) 複合類型
record
table
3) 參考類型 ref
ref cursor(參考游標)
4) 大類型
BLOB 0-4g
CLOB 0-4g
BFILE
8.2 變數的修飾符
變數名 constant 數據類型;
變數名 數據類型 not null;
declare
-- (1)
-- var_id constant number;
var_id constant number:=1;
-- (3)
-- var_name varchar(20) not null;
var_name varchar(20) not null:='1';
begin
-- (2)
-- var_id:=1;
var_name:='test';
dbms_output.put_line(var_id);
dbms_output.put_line(var_name);
end;
/
(1) 用constant修飾的變數必須初始化
(2) 用constant修飾的變數不允許賦值
(3) 用not null修飾的變數必須初始化
任何類型的變數,在賦值前 初值都是null
8.3 使用binary_integer和boolean定義變數
true false null
declare
var_id binary_integer:=100;
var_flag boolean;
begin
var_flag:=true;
if var_flag then
dbms_output.put_line(var_id);
end if;
end;
/
8.4 聲明兩個變數,類型分別和s_emp表中id、first_name相同,用來接收id=1的員工的id和first_name,並輸出
declare
var_id number(7);
var_name varchar2(25);
begin
var_id:=1;
var_name:='Carmen';
dbms_output.put_line(var_id||','||var_name);
end;
/
獲取表中欄位的數據類型:表名.欄位名%type
使用select語句給變數賦值:
select 欄位列表 into 變數列表 from 表名 where 條件;
註意:欄位列表和變數列表的順序、數量和數據類型要一致
select語句有且只有一行結果
declare
var_id s_emp.id%type;
var_name s_emp.first_name%type;
begin
select id,first_name into var_id,var_name from s_emp
where id=1;
dbms_output.put_line(var_id||','||var_name);
end;
/
8.5 record類型 相當於C語言中的結構體
8.5.1 定義record類型
type 記錄類型名 is record(
欄位名 類型,
...
欄位名 類型
);
8.5.2 定義record類型,有三個欄位,類型分別和s_emp表中id,first_name,salary相同。聲明該類型變數,用於接收id=2的員工的id,first_name和salary,並輸出。
declare
-- 定義record類型
type emprecord is record(
id s_emp.id%type,
name s_emp.first_name%type,
sal number(11,2)
);
-- 聲明record類型變數
var_emp emprecord;
var_emp2 emprecord;
begin
select id,first_name,salary into var_emp from s_emp
where id=2;
dbms_output.put_line(var_emp.id||','||var_emp.name||','
||var_emp.sal);
var_emp2:=var_emp;
dbms_output.put_line(var_emp2.id||','
||var_emp2.name||','
||var_emp2.sal);
end;
/
-- 查詢的欄位的數量少於record類型的欄位的數量
declare
-- 定義record類型
type emprecord is record(
id s_emp.id%type,
name s_emp.first_name%type,
sal number(11,2)
);
-- 聲明record類型變數
var_emp emprecord;
begin
select id,first_name into var_emp.id,var_emp.name
from s_emp where id=2;
dbms_output.put_line(var_emp.id||','||var_emp.name||','
||var_emp.sal);
end;
/
-- 接收表中的全部欄位,record類型?
8.5.3 用於接收表中整行數據的類型
表名%rowtype
-- 查詢s_emp表中 id=3的員工的全部信息,保存在變數中並輸出
declare
var_emp s_emp%rowtype;
begin
select * into var_emp from s_emp where id=3;
dbms_output.put_line(var_emp.id||','||
var_emp.first_name||','||
var_emp.title);
end;
/
8.6 table類型 類似於C語言中的數組
8.6.1 定義table類型的語法
type table類型 is table of 元素的數據類型
index by binary_integer;
8.6.2 定義一個table類型,聲明變數,保存多個數字
declare
/* 定義table類型 /
type numstable is table of number
index by binary_integer;
/ 聲明table類型的變數 */
var_nums numstable;
begin
var_nums(3):=100;
var_nums(7):=200;
var_nums(1):=300;
dbms_output.put_line(var_nums(3));
-- dbms_output.put_line(var_nums(2));
end;
8.6.3 下標連續時,table類型變數的訪問
declare
/* 定義table類型 */
type numstable is table of number
index by binary_integer;
/* 聲明table類型的變數 */
var_nums numstable;
/* 聲明變數 表示下標 */
var_i binary_integer;
begin
var_nums(3):=100;
var_nums(5):=200;
var_nums(4):=300;
var_i:=3;
dbms_output.put_line(var_nums(var_i));
var_i:=var_i + 1;
dbms_output.put_line(var_nums(var_i));
var_i:=var_i + 1;
dbms_output.put_line(var_nums(var_i));
-- var_i:=var_i + 1;
end;
/
8.6.4 下標不連續時,對table類型變數的遍歷
first() -- 獲取第一個元素的下標
next(n) -- 獲取下標為n的元素的下一個元素的下標
last() -- 獲取最後一個元素的下標
declare
/* 定義table類型 */
type numstable is table of number
index by binary_integer;
/* 聲明table類型的變數 */
var_nums numstable;
/* 聲明變數 表示下標 */
var_i binary_integer;
begin
var_nums(3):=100;
var_nums(7):=200;
var_nums(4):=300;
var_i:=var_nums.first();
dbms_output.put_line(var_nums(var_i));
var_i:=var_nums.next(var_i);
dbms_output.put_line(var_nums(var_i));
var_i:=var_nums.next(var_i);
dbms_output.put_line(var_nums(var_i));
-- var_i:=var_nums.next(var_i);
end;
8.6.5 定義table類型變數,保存s_emp表中id為1,3,7的員工的信息,並輸出
declare
/* 定義table類型 /
type empstable is table of s_emp%rowtype
index by binary_integer;
/ 聲明table類型變數 /
var_emps empstable;
/ 聲明變數 保存下標 /
var_i binary_integer;
begin
select into var_emps(1) from s_emp where id=1;
select * into var_emps(3) from s_emp where id=3;
select * into var_emps(7) from s_emp where id=7;
var_i:=var_emps.first();
dbms_output.put_line(var_emps(var_i).id||','||
var_emps(var_i).first_name||','||
var_emps(var_i).salary);
var_i:=var_emps.next(var_i);
dbms_output.put_line(var_emps(var_i).id||','||
var_emps(var_i).first_name||','||
var_emps(var_i).salary);
var_i:=var_emps.next(var_i);
dbms_output.put_line(var_emps(var_i).id||','||
var_emps(var_i).first_name||','||
var_emps(var_i).salary);
var_i:=var_emps.next(var_i);
end;
8.6. 變數的作用域
declare
-- 全局
var_a number:=1;
begin
declare
-- 局部
var_b number:=2;
begin
dbms_output.put_line(var_a);
dbms_output.put_line(var_b);
end;
dbms_output.put_line(var_a);
-- dbms_output.put_line(var_b);
end;
局部既可以訪問局部變數,也可以訪問全局變數
全局 只可以訪問全局變數
局部變數和全局變數重名時,局部變數會覆蓋同名的全局變數
-- 局部訪問同名的全局變數:
使用標簽
定義標簽: <<標簽名>>
使用標簽: 標簽名.變數名
<<g>>
declare
-- 全局
var_a number:=1;
begin
declare
-- 局部
var_a number:=2;
begin
dbms_output.put_line(g.var_a);
end;
dbms_output.put_line(var_a);
end;
控制語句
9.1 if語句
9.1.1 語法
1) 簡單if語句
if 條件 then
操作
end if;
2) if...else語句
if 條件 then
操作1
else
操作2
end if;
3) 多分支if
if 條件1 then
操作1
elsif 條件2 then
操作2
....
else
操作n
end if;
4) 嵌入if
if 條件1 then
if 條件2 then
操作1
else
操作2
end if;
else
if 條件3 then
操作3
else
操作4
end if;
end if;9.1.2 練習:聲明三個數字類型變數並賦值,輸出最大值
-- 方法一:
declare
var_a number:=1;
var_b number:=200;
var_c number:=100;
begin
if var_a > var_b then
if var_a > var_c then
dbms_output.put_line(var_a);
else
dbms_output.put_line(var_c);
end if;
else
if var_b > var_c then
dbms_output.put_line(var_b);
else
dbms_output.put_line(var_c);
end if;
end if;
end;
/-- 方法二:
declare
var_a number:=&var_a;// 後面這'&'是輸入相當於c語言里的scanf
var_b number:=&var_b;
var_c number:=&var_c;
var_temp number;
begin
var_temp:=var_a;
if var_b > var_temp then
var_temp:=var_b;
end if;
if var_c >var_temp then
var_temp:=var_c;
end if;
dbms_output.put_line(var_temp);
end;
9.1.3 NULL的運算特點
declare
var_a number;
var_b number;
begin
if var_a > var_b then
dbms_output.put_line('a>b');
elsif var_a < var_b then
dbms_output.put_line('a<b');
elsif var_a = var_b then
dbms_output.put_line('a=b');
elsif var_a is null and var_b is null then
dbms_output.put_line(
'var_a is null and var_b is null');
end if;
end;
/
9.2 迴圈語句
迴圈變數初始化 迴圈條件 迴圈操作 條件的更新
9.2.1 簡單迴圈
1) 語法
loop
-- 迴圈操作
end loop;
2) 結束迴圈的方式
-- 方式一:
if 退出迴圈的條件 then
exit;
end if;
-- 方式二:
exit when 退出迴圈的條件;
3) 使用簡單迴圈輸出1..10
declare
var_i number:=1;
begin
loop
dbms_output.put_line(var_i);
exit when var_i=10;
var_i:=var_i+1;
end loop;
end;
/
-- 使用if退出迴圈
declare
var_i number:=10;
begin
loop
dbms_output.put_line(var_i);
if var_i=1 then
dbms_output.put_line('loop over!');
exit;
end if;
var_i:=var_i-1;
end loop;
end;
/
9.2.2 while迴圈
1) 語法
while 條件 loop
-- 迴圈操作
end loop;
2) 使用while迴圈輸出1..10
declare
var_i number:=1;
begin
while var_i<=10 loop
dbms_output.put_line(var_i);
var_i:=var_i+1;
end loop;
end;
-- 練習:從1..100累加,輸出和大於2000時對應的數字
declare
var_i number:=1;
var_sum number:=0;
begin
while var_i<=100 loop
var_sum:=var_sum + var_i;
if var_sum >=2000 then
dbms_output.put_line(var_i);
exit;
end if;
var_i := var_i + 1;
end loop;
end;
9.2.3 for 迴圈
智能迴圈
1) 語法
for 迴圈變數 in 區間 loop
-- 迴圈操作
end loop;
2) 使用for 迴圈輸出1..10
begin
for var_i in 1..10 loop
dbms_output.put_line(var_i);
end loop;
end;
-- 練習 使用for迴圈輸出10..1
begin
for var_i in 10..1 loop
dbms_output.put_line(var_i);
end loop;
end;
-- 上邊代碼迴圈一次也不執行
使用 reverse 實現倒序輸出
begin
for var_i in reverse 1..10 loop
dbms_output.put_line(var_i);
end loop;
end;
-- 使用退出迴圈的方式 提前結束迴圈
begin
for var_i in 1..10 loop
dbms_output.put_line(var_i);
if var_i=5 then
-- var_i:=11; 錯誤
end if;
end loop;
end;
a.迴圈變數不需要聲明
b.區間必須是從小到大 使用reverse實現反轉迴圈
c.迴圈變數不允許賦值
9.3 跳轉語句 goto
9.3.1 語法
<<標簽名>>
-- 這裡必須有語句
NULL; -- 空語句
goto 標簽名;
9.3.2 使用goto退出嵌套迴圈
begin
for var_i in 1..3 loop
for var_j in 1..5 loop
dbms_output.put_line(var_j);
if var_j=3 then
goto outer;
end if;
end loop;
end loop;
<
9.3.3 使用if..exit方式退出迴圈
begin
<
- plsql中使用sql語句
1) select語句
select語句要和into 配合使用
select 欄位列表 into 變數列表 from 表名 where 條件;
2) dml語句
tcl語句
可以直接在plsql中使用
3) ddl語句
不能直接在plsql中使用 需要用動態sql實現
11.動態sql
11.1 概念
把一條字元串對應的sql語句,當成真正的sql語句去執行
11.2 案例: 創建一張表
/* 錯誤:ddl語句不能直接在plsql中使用 */
begin
create table testdsql_zsm_00(id number);
end;
-- 在plsql中可以使用函數
declare
sqlstr varchar2(100);
begin
sqlstr:='create table testdsql_zsm_00(id number)';
sqlstr:=substr(sqlstr,1,length(sqlstr)-1)
||',name varchar2(20))';
-- dbms_output.put_line(sqlstr);
execute immediate sqlstr;
end;
11.3 DML語句的動態sql
1) 直接在plsql中使用dml和tcl語句
begin
insert into testdsql_zsm_00 values(1,'test1');
commit;
end;
2) 常規字元串的拼接
declare
sqlstr varchar2(100);
begin
sqlstr:= 'insert into testdsql_zsm_00 values(2,''test2'')';
execute immediate sqlstr;
commit;
end;
3)帶有變數的字元串的拼接
declare
var_id number:=3;
var_name varchar2(20):='test3';
sqlstr varchar2(100);
begin
sqlstr:='insert into testdsql_zsm_00 values('||var_id||
','''||var_name||''')';
-- dbms_output.put_line(sqlstr);
execute immediate sqlstr;
commit;
end;
4) 使用占位符 配合using解決字元串拼接的問題
占位符: :占位符名
execute immediate sqlstr using 變數列表;
declare
var_id number:=4;
var_name varchar2(20):='test4';
sqlstr varchar2(100);
begin
sqlstr:='insert into testdsql_zsm_00 values(:b0,:b1)'; //:b0 :b1 就是占位符
execute immediate sqlstr using var_id,var_name;
commit;
end;
11.4 select語句動態sql
必須是普通的select語句(不帶into)
select語句有且只有一行結果
declare
sqlstr varchar2(100);
var_name s_emp.first_name%type;
begin
sqlstr:='select first_name from s_emp where id=1';
execute immediate sqlstr into var_name;
dbms_output.put_line(var_name);
end;
總結:
1.變數和類型
定義和使用record和table類型
type 類型名 is record(
欄位 類型,
....
欄位 類型
);
type 類型名 is table of 元素類型 index by binary_integer;
表名.欄位%type
表名%rowtype
2.控制語句
分支:if
迴圈:簡單迴圈、while迴圈、for迴圈
跳轉語句:goto
結束迴圈的兩種方式:
exit when 退出迴圈的條件;
if 退出迴圈的條件 then
exit;
end if;
3.動態sql
sqlstr varchar2(200):='';
execute immediate sqlstr;
'insert into 表名 values(:b0,:b1,....)'
execute immediate sqlstr using 變數1,變數2,....;
練習:
1.定義record類型,聲明變數,保存s_dept表中id=31的部門的
信息
- 使用動態sql語句,刪除testdsql_zsm_00表中指定id 的信息