PL/SQL語句可以在Oracle用戶端的 SQL視窗或者 command 視窗中運作
在SQL視窗中運作步驟同 SQL語句
在command 視窗中運作的步驟如下:
1)File—new command window,出現下圖
2)輸入指令:set serveroutput on ,回車執行
作用:開啟輸出服務
3)輸入指令:ed ,回車執行
作用:打開PL/SQL文本編輯器,可以在此文本編輯器中寫PL/SQL語句
4)輸入指令:/ ,回車執行
作用:執行PL/SQL語句
1.PL/SQL文法格式
[文法格式]
--declare
--聲明的變量、記錄類型、遊标
begin
--程式的執行部分(類似于java裡的main()方法)
dbms_output.put_line('helloworld');
--exception
--針對begin塊中出現的異常,提供處理的機制
--when .... then ...
--when .... then ...
end;
--其中begin,end 必不可少
2.變量命名規則
3.聲明變量類型
3.1正常變量
char,varchar2,date,number,boolean,long
declare
--聲明變量
v_name varchar2(25);
v_email varchar2(25);
v_salary number(8, 2);
v_job_id varchar2(10);
begin
--通過 select ... into ... 語句為變量指派
--被指派的變量與SELECT中的列名要一一對應
select last_name, email, salary, job_id into v_name, v_email, v_salary, v_job_id
from employees
where employee_id = 186;
-- 列印變量的值
dbms_output.put_line(v_name || ', ' || v_email || ', ' || v_salary || ', ' || v_job_id);
end;
3.2記錄類型
記錄類型類似于java 的類
文法格式:
TYPE record_type IS RECORD(
Field1 type1 [NOT NULL] [:= exp1 ],
Field2 type2 [NOT NULL] [:= exp2 ],
. . . . . .
Fieldn typen [NOT NULL] [:= expn ]
) ;
自定義記錄類型示例:
declare
--定義一個記錄類型
type emp_record is record(
v_name varchar2(25),
v_email varchar2(25),
v_salary number(8, 2),
v_job_id varchar2(10)
);
--聲明自定義記錄類型的變量
v_emp_record emp_record;
begin
--通過 select ... into ... 語句為變量指派
select last_name, email, salary, job_id into v_emp_record
from employees
where employee_id = 186;
-- 列印變量的值
dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' || v_emp_record.v_salary || ', ' || v_emp_record.v_job_id);
end;
3.3 %type
使用 %type 定義變量,動态的擷取資料的聲明類型
定義一個變量,其資料類型與已經定義的某個資料變量的類型相同,或者與資料庫表的某個列的資料類型相同,這時可以使用%TYPE
declare
--定義一個記錄類型
type emp_record is record(
v_name employees.last_name%type, --使v_name的類型與employees表中last_name類型保持一緻
v_email employees.email%type,
v_salary employees.salary%type,
v_job_id employees.job_id%type
);
--聲明自定義記錄類型的變量
v_emp_record emp_record;
begin
--通過 select ... into ... 語句為變量指派
select last_name, email, salary, job_id into v_emp_record
from employees
where employee_id = 186;
-- 列印變量的值
dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' ||
v_emp_record.v_salary || ', ' || v_emp_record.v_job_id);
end;
3.4 %rowtype
PL/SQL 提供%ROWTYPE 操作符, 傳回一個記錄類型, 其資料類型和資料庫表的資料結構相一緻
declare
--聲明一個記錄類型的變量
v_emp_record employees%rowtype;
begin
--通過 select ... into ... 語句為變量指派
select * into v_emp_record
from employees
where employee_id = 186;
-- 列印變量的值
dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' ||
v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' ||
v_emp_record.hire_date);
end;
4. 變量指派
4.1 :=
:= 指派号 (指派操作)
= 等于 (判斷操作)
=> (用于參數指派)
文法格式:
variable := expression ;
--variable 是一個 PL/SQL 變量, expression 是一個 PL/SQL 表達式.
4.2 資料庫指派
資料庫指派是通過 SELECT語句來完成的,每次執行 SELECT語句就指派一次, 一般要求被指派的變量與SELECT中的列名要一一對應。
如: 通過變量實作查詢語句
declare
v_emp_record employees%rowtype;
v_employee_id employees.employee_id%type;
begin
--使用指派符号為變量進行指派
v_employee_id := 186;
--通過 select ... into ... 語句為變量指派
select * into v_emp_record
from employees
where employee_id = v_employee_id;
-- 列印變量的值
dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' ||
v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' ||
v_emp_record.hire_date);
end;
5. 通過變量實作DELETE、INSERT、UPDATE等操作
declare
v_emp_id employees.employee_id%type;
begin
v_emp_id := 109;
delete from employees
where employee_id = v_emp_id;
--commit;
end;
6.流程控制之條件語句(2種)
6.1 IF 語句 ;
IF <布爾表達式> THEN
PL/SQL 和 SQL 語句;
ELSIF < 其它布爾表達式> THEN
其它語句;
ELSIF < 其它布爾表達式> THEN
其它語句;
ELSE
其它語句;
END IF;
-- ELSIF 不能寫成 ELSEIF
示例:
/*
要求: 查詢出 150号 員工的工資, 若其工資大于或等于 10000 則列印 'salary >= 10000';
若在 5000 到 10000 之間, 則列印 '5000<= salary < 10000'; 否則列印 'salary < 5000'
*/
declare
v_emp_name employees.last_name%type;
v_emp_sal employees.salary%type;
v_emp_sal_level varchar2(20);
begin
select last_name,salary into v_emp_name,v_emp_sal from employees where employee_id = 150;
if(v_emp_sal >= 10000) then v_emp_sal_level := 'salary >= 10000';
elsif(v_emp_sal >= 5000) then v_emp_sal_level := '5000<= salary < 10000';
else v_emp_sal_level := 'salary < 5000';
end if;
dbms_output.put_line(v_emp_name||','||v_emp_sal||','||v_emp_sal);
end;
6.2 CASE 語句
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
WHEN expressionN THEN resultN
[ ELSE resultN+1]
END;
/*要求: 查詢出 122 号員工的 JOB_ID, 若其值為 'IT_PROG', 則列印 'GRADE: A';
'AC_MGT', 列印 'GRADE B',
'AC_ACCOUNT', 列印 'GRADE C';
否則列印 'GRADE D'
*/
declare
--聲明變量
v_grade char(1);
v_job_id employees.job_id%type;
begin
select job_id into v_job_id
from employees
where employee_id = 122;
dbms_output.put_line('job_id: ' || v_job_id);
--根據 v_job_id 的取值, 利用 case 字句為 v_grade 指派
v_grade :=
case v_job_id when 'IT_PROG' then 'A'
when 'AC_MGT' then 'B'
when 'AC_ACCOUNT' then 'C'
else 'D'
end;
dbms_output.put_line('GRADE: ' || v_grade);
end;
7.流程控制之循環語句(3種)
7.1 LOOP 循環
declare
--初始化條件
v_i number(3) := 1;
begin
loop
--循環體
dbms_output.put_line(v_i);
--循環條件
exit when v_i = 100;
--疊代條件
v_i := v_i + 1;
end loop;
end;
7.2 WHILE 循環
文法格式;
declare
--初始化條件
v_i number(3) := 1;
begin
--循環條件
while v_i <= 100 loop
--循環體
dbms_output.put_line(v_i);
--疊代條件
v_i := v_i + 1;
end loop;
end;
/*綜合使用 if, while 語句, 列印 1 - 100 之間的所有素數
(素數: 有且僅用兩個正約數的整數, 2, 3, 5, 7, 11, 13, ...).
*/
declare
v_flag number(1):=1;
v_i number(3):=2;
v_j number(2):=2;
begin
while (v_i<=100) loop
while v_j <= sqrt(v_i) loop
if (mod(v_i,v_j)=0) then v_flag:= 0;
end if;
v_j :=v_j +1;
end loop;
if(v_flag=1) then dbms_output.put_line(v_i);
end if;
v_flag :=1;
v_j := 2;
v_i :=v_i +1;
end loop;
end;
7.3 FOR 循環
FOR 循環計數器 IN [ REVERSE ] 下限 .. 上限 LOOP
要執行的語句;
END LOOP;
--使用for循環實作1-100之間的素數的輸出
declare
--标記值, 若為 1 則是素數, 否則不是
v_flag number(1) := 0;
begin
for i in 2 .. 100 loop
v_flag := 1;
for j in 2 .. sqrt(i) loop
if i mod j = 0 then
v_flag := 0;
end if;
end loop;
if v_flag = 1 then
dbms_output.put_line(i);
end if;
end loop;
end;
8. 标号和 GOTO
PL/SQL 中 GOTO 語句是無條件跳轉到指定的标号去的意思。文法如下:
GOTO label;
. . . . . .
<label>> /*标号是用<< >>括起來的辨別符 */
--列印1——100的自然數,當列印到50時,跳出循環,輸出“列印結束”
begin
for i in 1..100 loop
dbms_output.put_line(i);
if(i = 50) then
goto label;
end if;
end loop;
<<label>>
dbms_output.put_line('列印結束');
end;