PL/SQL編程
概述：它是Oracle在標(biāo)準(zhǔn)的SQL語言上的擴(kuò)展，它不僅支持允許嵌入SQL語言，還可以自定義常量和變量
           允許使用條件語句、循環(huán)語句、邏輯控制語句等，允許使用例外處理各種錯(cuò)誤，這樣使得它的功能變得更加強(qiáng)大
優(yōu)勢：1)提高應(yīng)用程序的運(yùn)行性能----省去了數(shù)據(jù)庫編譯Java程序發(fā)送過來的SQL語句的時(shí)間
           2)模塊化的設(shè)計(jì)思想----------比如分頁存儲過程模塊、訂單處理存儲過程模塊、轉(zhuǎn)賬存儲過程模塊等等
           3)減少網(wǎng)絡(luò)傳輸量------------不必再用Java程序發(fā)送那么多的SQL語句了
           4)提高安全性-----------------直接在Java程序中調(diào)用存儲過程名，省去了暴露SQL的危險(xiǎn)
劣勢：移植性差----------------------移植到其它數(shù)據(jù)庫時(shí)，可能甚至要重寫
規(guī)范：定義變量時(shí)，建議用v_作為前綴，如v_sal
           定義常量時(shí)，建議用c_作為前綴，如c_rate
           定義例外時(shí)，建議用e_作為前綴，如e_error
           定義游標(biāo)時(shí)，建議用_cursor后綴，如emp_cursor
分類：它主要是塊編程，簡單可以分為過程、函數(shù)、觸發(fā)器、包等等

--編寫一個(gè)存儲過程，該過程可以向某表中添加記錄
create table student(name varchar2(10),password varchar2(30));
create or replace procedure mypro is --replace表示如果已存在名字為mypro的存儲過程，則將之替換
	begin
		insert into student values('張起靈','zhang22'); --執(zhí)行部分
	end;
exec mypro; --調(diào)用名字為mypro的存儲過程。格式為exec procedure_name(param1,param2,...)
call mypro; --也可以使用call命令調(diào)用

block
概述：塊是PL/SQL的基本程序單元，編寫PL/SQL程序?qū)嶋H上就是編寫PL/SQL塊
           要完成一個(gè)相對簡單的功能，可能只需要編寫一個(gè)PL/SQL塊；但要想實(shí)現(xiàn)復(fù)雜的功能，可能需要在一個(gè)PL/SQL塊中嵌套其它的PL/SQL塊
結(jié)構(gòu)：PL/SQL塊由三個(gè)部分構(gòu)成，定義部分、執(zhí)行部分、例外處理部分
           declare         /*定義部分-------該部分是可選的。定義常量、變量、例外、游標(biāo)、復(fù)雜數(shù)據(jù)類型*/
           begin           /*執(zhí)行部分-------該部分是必須的。要執(zhí)行的PL/SQL語句和SQL語句*/
           exception    /*例外處理部分---該部分是可選的。處理運(yùn)行的各種錯(cuò)誤*/
           end;

-----------------------------------------------------------------------------------------------------------------
--1)只包括執(zhí)行部分的PL/SQL塊
set serveroutput on --打開輸出選項(xiàng)
--dbms_output是Oracle所提供的包，類似Java的開發(fā)包，該包中包含一些過程，put_line就是該包中的一個(gè)過程
begin
	dbms_output.put_line('my name is jadyer'); --在控制臺輸出my name is jadyer字符串
end;

-----------------------------------------------------------------------------------------------------------------
--2)包含定義部分、執(zhí)行部分的PL/SQL塊
declare
	v_ename varchar2(5); --定義字符串變量
	v_sal number(7,2);
begin --into表示將查詢到的信息，放入到v_ename變量中。注意這里ename、sal和v_ename、v_sal的順序是相匹配的
	select ename,sal into v_ename,v_sal from emp where empno=&no; --&表示要接收從控制臺輸入的變量
	dbms_output.put_line('雇員名：'||v_ename||' 工資：'||v_sal);
end;

-----------------------------------------------------------------------------------------------------------------
--3)包含定義部分、執(zhí)行部分、例外處理部分的PL/SQL塊
declare
	v_ename varchar2(5);
	v_sal number(7,2);
begin
	select ename,sal into v_ename,v_sal from emp where empno=&no;
	dbms_output.put_line('雇員名：'||v_ename||' 工資：'||v_sal);
exception --異常處理
	when no_data_found then --Oracle預(yù)定義了一些例外，其中no_data_found即找不到數(shù)據(jù)的例外
	dbms_output.put_line('您輸入的員工編號不存在。');
end;
-----------------------------------------------------------------------------------------------------------------

存儲過程
概述：用于執(zhí)行特定的操作。建立過程時(shí)，既可以指定輸入?yún)?shù)，也可以指定輸出參數(shù)
           通過在過程中使用輸入?yún)?shù)，可以將數(shù)據(jù)傳遞到執(zhí)行部分；通過使用輸出參數(shù)，可以將執(zhí)行部分的數(shù)據(jù)傳遞到應(yīng)用環(huán)境

--修改指定用戶的工資

create procedure emp_pro(currName varchar2, newSal number) is --類似于Java定義方法時(shí)的參數(shù)，故不可以指定參數(shù)類型的長度
	begin
		update emp set sal=newSal where ename=currName;
	end;

--調(diào)用存儲過程。效果是將SCOTT的工資變動為23456
exec emp_pro('SCOTT',23456);

--在Java程序中調(diào)用該存儲過程
CallableStatement cstmt = java.sql.Connection.prepareCall("{call emp_pro(?,?)}");
cstmt.setString(1, "SCOTT");
cstmt.setInt(2, 23456);
cstmt.execute();

函數(shù)
概述：用于返回特定的數(shù)據(jù)。建立函數(shù)時(shí)，在函數(shù)頭部必須包含return子句，而函數(shù)體必須包含return語句返回的數(shù)據(jù)

--返回指定雇員的年薪
create function emp_fun(currName varchar2) return number is yearSal number(7,2);
	begin
		select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=currName;
		return yearSal;
	end;

--在sqlplus中調(diào)用函數(shù)時(shí)，需要以下三步
var NianXin number;
call emp_fun('SCOTT') into:NianXin;
print NianXin;

Java>//在Java程序中調(diào)用該函數(shù)，然后使用rs.getInt(1)即得到返回的結(jié)果
Java>select emp_fun('SCOTT') from dual;

包
概述：用于在邏輯上組合過程和函數(shù)，它由包規(guī)范和包體兩部分組成
           包的規(guī)范只包含了過程和函數(shù)的說明，但是沒有過程和函數(shù)的實(shí)現(xiàn)代碼。包體用于實(shí)現(xiàn)包規(guī)范中的過程和函數(shù)

--1)使用create package命令創(chuàng)建包
create or replace package emp_pack is
	procedure emp_pro(currName varchar2, newSal number); --聲明該包中有一個(gè)過程
	function emp_fun(currName varchar2) return number;   --聲明該包中有一個(gè)函數(shù)
end;

--2)使用create package body命令創(chuàng)建包體
create package body emp_pack is
	procedure emp_pro(currName varchar2, newSal number) is
		begin
			update emp set sal=newSal where ename=currName;
		end;
	function emp_fun(currName varchar2) return number is yearSal number;
		begin
			select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=currName;
			return yearSal;
		end;
end;

--3)調(diào)用包的過程或函數(shù)時(shí)，在過程和函數(shù)前需要帶有包名；如果要訪問其它方案的包，還需要在包名前加方案名
call emp_pack.emp_pro('SCOTT',400800);

觸發(fā)器
概述：觸發(fā)器是指存放在數(shù)據(jù)庫中，被隱含執(zhí)行的存儲過程。觸發(fā)器由觸發(fā)事件、觸發(fā)條件、觸發(fā)操作三部分構(gòu)成
分類：DML觸發(fā)器、DDL觸發(fā)器、系統(tǒng)觸發(fā)器(后兩個(gè)觸發(fā)器通常由系統(tǒng)管理員創(chuàng)建、即conn system/xxx as sysdba)

--管理觸發(fā)器(使用system登錄)
alter trigger trigger_name disable; --禁用觸發(fā)器(讓觸發(fā)器臨時(shí)生效)
alter trigger trigger_name enable;  --激活觸發(fā)器
alter table table_name disable all triggers; --禁用表的所有觸發(fā)器
alter table table_name enable all triggers;  --激活表的所有觸發(fā)器
drop trigger trigger_name;                   --刪除觸發(fā)器

--DML觸發(fā)器的基本語法
create [or replace] trigger trigger_name
{before|after}
{insert|delete|update [of column [,column ...]]}
on [schema.] table_name
[for each row]    --代表行級觸發(fā)器，沒有它則代表表級觸發(fā)器
[when condition]  --代表觸發(fā)條件
begin
	trigger_body;
end;

--舉例
--1)在表中添加一條數(shù)據(jù)時(shí)，提示"添加了一條數(shù)據(jù)"
create or replace trigger trigger_blog after insert on scott.blog
begin
	dbms_output.put_line('添加了一條數(shù)據(jù)');
end;

--2)在表中修改多條數(shù)據(jù)時(shí)，提示多次"修改了數(shù)據(jù)"
create or replace trigger trigger_blog after update on scott.blog for each row
begin
	dbms_output.put_line('修改了數(shù)據(jù)');
end;

--3)禁止在休息日修改表數(shù)據(jù)，開發(fā)人員可以建立before語句觸發(fā)器，從而實(shí)現(xiàn)數(shù)據(jù)的安全
create or replace trigger trigger_blog before insert or update or delete on scott.blog
begin
	if to_char(sysdate,'day') in ('星期六','星期日') then
		--dbms_output.put_line('不能在休息日操作數(shù)據(jù)');  --這樣只會提示，而不能阻止該操作
		--raise_application_error()是Oracle提供的一個(gè)過程，只要PLSQL碰到它，PLSQL就會停止執(zhí)行
		--PROCEDUER raise_application_error(error_number_in IN NUMBER,error_msg_in IN VARCHAR2)
		--error_number_in是從-200000到-20999之間的，這樣就不會與Oracle的任何錯(cuò)誤代碼發(fā)生沖突了
		--而error_msg_in的長度也不要超過2000，否則Oracle會自動截取前2000個(gè)字符
		raise_application_error(-20001,'不能在休息日操作數(shù)據(jù)');
	end if;
end;

--4)為了區(qū)分觸發(fā)器中所包含的多個(gè)觸發(fā)事件，可以使用三個(gè)條件：inserting,updating,deleting
create or replace trigger trigger_blog before insert or update or delete on scott.blog
begin
	if to_char(sysdate,'day') in ('星期六','星期日') then
		case
			when inserting then raise_application_error(-20002,'請不要在休息日添加數(shù)據(jù)');
			when updating then raise_application_error(-20003,'請不要在休息日修改數(shù)據(jù)');
			when deleting then raise_application_error(-20004,'請不要在休息日刪除數(shù)據(jù)');
		end case;
	end if;
end;

--5)修改雇員薪水時(shí)，確保雇員工資不能低于原工資，也不能高出原工資的20%，并顯示薪水修改前和修改后的值
create or replace trigger trigger_blog before update on scott.blog for each row
begin
	--由于我們的觸發(fā)器是針對emp表的，所以PLSQL就知道這里的sal是blog表的字段
	--':new'修飾符用于訪問操作完成后列的值，':old'修飾符用于訪問操作完成前列的值
	if (:new.sal<:old sal="" or="" :new="" sal="">:old.sal*1.2) then
		raise_application_error(-20005,'修改后的工資不能低于原工資，也不能高出原工資的20%');
	else
		dbms_output.put_line('原來的工資：'||:old.sal||'    現(xiàn)在的工資：'||:new.sal);
	end if;
end;

--6)刪除表記錄時(shí)，自動將刪除掉的記錄備份到另外一張表中
create or replace trigger trigger_blog before delete on scott.blog for each row
begin
	insert into blog_bak values (:old.id, :old.name, :old.sal);
end;

--DDL觸發(fā)器
create [or replace] trigger trigger_name
after ddl on 方案名.schema --這里的'.schema'是固定寫法，如scott.schema
begin
	trigger_body;
end;

--記錄某個(gè)用戶進(jìn)行的DDL操作
create table log_ddl(uname varchar2(20), ddl_event varchar2(20), ddl_time date);
create or replace trigger trigger_ddl after ddl on scott.schema
begin
	insert into log_ddl values(ora_login_user, ora_sysevent, sysdate);
end;

--7)系統(tǒng)觸發(fā)器是指基于Oracle事件(如logon,startup)所建立的觸發(fā)器
--  在創(chuàng)建系統(tǒng)觸發(fā)器時(shí)，需要使用事件屬性函數(shù)，常用的事件屬性函數(shù)，如下
--  ora_client_ip_address      --返回客戶端IP(Windows上面返回的IP可能為空)
--  ora_database_name          --返回?cái)?shù)據(jù)庫名
--  ora_login_user             --返回登陸的用戶名
--  ora_sysevent               --返回觸發(fā)觸發(fā)器的系統(tǒng)事件名
--  ora_des_encrypted_password --返回用戶DES加密后的密碼
--系統(tǒng)觸發(fā)器的基本語法
create [or replace] trigger trigger_name
after[before] logon[logoff] on database --固定寫法，這就不存在for each row屬性了，因?yàn)樗轻槍?shù)據(jù)庫的
begin
	trigger_body;
end;

--示例
create table log_sysevent(uname varchar2(20), logon_time date, logoff_time date, ip varchar2(20));
--登錄觸發(fā)器
create or replace trigger trigger_logon after logon on database --登錄之后記錄
begin
	insert into log_sysevent(uname,logon_time,ip) values(ora_login_user, sysdate, ora_client_ip_address);
end;
--退出觸發(fā)器
create or replace trigger trigger_logoff before logoff on database --退出之前記錄
begin
	insert into log_sysevent(uname,logoff_time,ip) values(ora_login_user, sysdate, ora_client_ip_address);
end;

定義并使用變量
分類：在編寫PL/SQL程序時(shí)，可以定義變量和常量。在PL/SQL程序中包括以下四種常見類型
           1)標(biāo)量類型(scalar)
           2)復(fù)合類型(composite)
           3)參照類型(reference)
           4)lob(large object)
標(biāo)量的語法：PL/SQL中定義變量和常量的語法，如下
                     identifier  [constant]  datatype  [not  null]  [:=|  default  expr]
                     identifier-----名稱
                     constant-----指定常量。需要指定它的初始值，且其值是不能改變的
                     datatype-----數(shù)據(jù)類型
                     not  null------指定變量不能為null
                     :=-------------給變量或常量指定初始值
                     default-------用于指定初始值
                     expr----------指定初始值PL/SQL表達(dá)式，可以是文本值、其它變量、函數(shù)等

--標(biāo)量的案例
v_ename varchar2(10);                   --定義一個(gè)變長字符串
v_sal number(6,2);                      --定義一個(gè)小數(shù)，范圍是-9999.99~~9999.99
v_sal number(6,2):=5.4                  --定義一個(gè)小數(shù)并給定初始值為5.4
v_hiredate date;                        --定義一個(gè)日期型數(shù)據(jù)
v_valid boolean not null default false; --定義一個(gè)布爾變量，其不能為空，且初始值為false

--標(biāo)量的使用
--這里需要說明的是，PL/SQL塊為變量賦值不同于其它的編程語言，需要在等號前加冒號，即(:=)
--下面以輸入員工號，顯示員工姓名、工資、個(gè)人所得稅(稅率為0.03)為例
declare
	v_ename varchar2(5);
	v_sal number(7,2);
	c_tax_rate number(3,2):=0.03;
	v_tax_sal number(7,2);
begin
	select ename,sal into v_ename,v_sal from emp where empno=&no;
	v_tax_sal:=v_sal*c_tax_rate; --計(jì)算所得稅，PL/SQL中允許直接進(jìn)行運(yùn)算
	dbms_output.put_line('姓名：'||v_ename||' 工資：'||v_sal||' 交稅：'||v_tax_sal);
end;

--若員工姓名超過5個(gè)字符，就會出現(xiàn)錯(cuò)誤。那么為了降低PL/SQL程序的維護(hù)工作量，可以使用(%type)屬性定義變量
--這樣它會按照數(shù)據(jù)庫列，來確定變量類型和長度，格式為(標(biāo)識符 表名.列名%type)，
v_ename emp.ename%type;

--復(fù)合變量(composite)
--用于存放多個(gè)值的變量，主要包括PL/SQL記錄、PL/SQL表、嵌套表(nested table)、動態(tài)數(shù)組(varray)等

--復(fù)合類型之PL/SQL記錄
--類似于高級語言中的結(jié)構(gòu)體
--注意：當(dāng)引用PL/SQL記錄成員時(shí)，必須加上記錄變量作為前綴，即(記錄變量.記錄成員)
declare
	--定義一個(gè)PL/SQL記錄類型，類型的名字是emp_record_type，該類型包含三個(gè)數(shù)據(jù)：name、salary、title
	type emp_record_type is record(currName emp.ename%type, salary emp.sal%type, title emp.job%type);
	--定義一個(gè)變量，變量的名字是my_record，這個(gè)變量的類型是emp_record_type
	my_record emp_record_type;
begin
	select ename,sal,job into my_record from emp where empno=7788; --該變量my_record就可以接收三個(gè)數(shù)據(jù)
	dbms_output.put_line('員工名：'||my_record.currName||' 工資：'||my_record.salary);
end;

--復(fù)合類型之PL/SQL表
--相當(dāng)于高級語言中的數(shù)組
--注意：高級語言中數(shù)組下標(biāo)不能為負(fù)數(shù)，而PL/SQL是可以為負(fù)數(shù)的，且表元素的下標(biāo)沒有限制
declare
	--定義一個(gè)PL/SQL表類型，類型的名字是my_table_type，該類型用于存放emp.ename%type類型的數(shù)組
	--其中index by binary_integer表示該數(shù)組下標(biāo)是按整數(shù)排序的，故其下標(biāo)可以為負(fù)數(shù)，因?yàn)樨?fù)整數(shù)也是整數(shù)
	type my_table_type is table of emp.ename%type index by binary_integer;
	--定義一個(gè)變量，變量的名字是my_table，這個(gè)變量的類型是my_table_type
	--PL/SQL中總是將變量名字寫在前面，變量類型寫在后面
	my_table my_table_type;
begin
	select ename into my_table(0) from emp where empno=7788;
	dbms_output.put_line('員工名：'||my_table(0));
end;

--參照變量
--用于存放數(shù)值指針的變量。通過使用參照變量，可使得應(yīng)用程序共享相同對象，從而降低占用的空間
--編寫PL/SQL程序時(shí)，可使用游標(biāo)變量(ref cursor)和對象類型變量(ref obj_type)兩種參照變量類型

--參照變量之游標(biāo)變量
--定義游標(biāo)時(shí)，不需要指定相應(yīng)的select語句
--但在使用游標(biāo)(open)時(shí)需要指定select語句，這樣一個(gè)游標(biāo)就與一個(gè)select語句結(jié)合了

--使用PL/SQL編寫一個(gè)塊，要求輸入部門號，顯示該部門所有員工的姓名和工資
declare
	type my_emp_cursor is ref cursor; --定義一個(gè)游標(biāo)類型
	test_cursor my_emp_cursor;        --定義一個(gè)游標(biāo)變量，該變量的類型是my_emp_cursor
	v_ename emp.ename%type;           --定義變量，用于接收select到的ename值
	v_sal emp.sal%type;
begin
	open test_cursor for select ename,sal from emp where deptno=&no; --把test_cursor和一個(gè)select結(jié)合
	loop                                                             --使用(loop...end loop)循環(huán)取出數(shù)據(jù)
		fetch test_cursor into v_ename,v_sal;                    --使用fetch取出test_cursor游標(biāo)指向的數(shù)據(jù)，并放到變量中
		exit when test_cursor%notfound;                          --判斷test_cursor是否為空。若其為空，則退出循環(huán)
		dbms_output.put_line('員工名：'||v_ename||' 工資：'||v_sal);
	end loop;
end;