牛刀小試PLSQL編程之筑基篇PL/SQL編程 --編寫一個(gè)存儲(chǔ)過(guò)程，該過(guò)程可以向某表中添加記錄
create table student(name varchar2(10),password varchar2(30));
create or replace procedure mypro is --replace表示如果已存在名字為mypro的存儲(chǔ)過(guò)程，則將之替換
begin
insert into student values('張起靈','zhang22'); --執(zhí)行部分
end;
exec mypro; --調(diào)用名字為mypro的存儲(chǔ)過(guò)程。格式為exec procedure_name(param1,param2,...)
call mypro; --也可以使用call命令調(diào)用
block -----------------------------------------------------------------------------------------------------------------
--1)只包括執(zhí)行部分的PL/SQL塊
set serveroutput on --打開(kāi)輸出選項(xiàng)
--dbms_output是Oracle所提供的包，類似Java的開(kāi)發(fā)包，該包中包含一些過(guò)程，put_line就是該包中的一個(gè)過(guò)程
begin
dbms_output.put_line('my name is jadyer'); --在控制臺(tái)輸出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; --&表示要接收從控制臺(tái)輸入的變量
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('您輸入的員工編號(hào)不存在。');
end;
-----------------------------------------------------------------------------------------------------------------
存儲(chǔ)過(guò)程 --修改指定用戶的工資
create procedure emp_pro(currName varchar2, newSal number) is --類似于Java定義方法時(shí)的參數(shù)，故不可以指定參數(shù)類型的長(zhǎng)度
begin
update emp set sal=newSal where ename=currName;
end;
--調(diào)用存儲(chǔ)過(guò)程。效果是將SCOTT的工資變動(dòng)為23456
exec emp_pro('SCOTT',23456);
--在Java程序中調(diào)用該存儲(chǔ)過(guò)程
CallableStatement cstmt = java.sql.Connection.prepareCall("{call emp_pro(?,?)}");
cstmt.setString(1, "SCOTT");
cstmt.setInt(2, 23456);
cstmt.execute();
函數(shù) --返回指定雇員的年薪
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;
包 --1)使用create package命令創(chuàng)建包
create or replace package emp_pack is
procedure emp_pro(currName varchar2, newSal number); --聲明該包中有一個(gè)過(guò)程
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)用包的過(guò)程或函數(shù)時(shí)，在過(guò)程和函數(shù)前需要帶有包名；如果要訪問(wèn)其它方案的包，還需要在包名前加方案名
call emp_pack.emp_pro('SCOTT',400800);
觸發(fā)器 --管理觸發(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ā)器的基本語(yǔ)法
create [or replace] trigger trigger_name
{before|after}
{insert|delete|update [of column [,column ...]]}
on [schema.] table_name
[for each row] --代表行級(jí)觸發(fā)器，沒(méi)有它則代表表級(jí)觸發(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ù)，開(kāi)發(fā)人員可以建立before語(yǔ)句觸發(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ù)'); --這樣只會(huì)提示，而不能阻止該操作
--raise_application_error()是Oracle提供的一個(gè)過(guò)程，只要PLSQL碰到它，PLSQL就會(huì)停止執(zhí)行
--PROCEDUER raise_application_error(error_number_in IN NUMBER,error_msg_in IN VARCHAR2)
--error_number_in是從-200000到-20999之間的，這樣就不會(huì)與Oracle的任何錯(cuò)誤代碼發(fā)生沖突了
--而error_msg_in的長(zhǎng)度也不要超過(guò)2000，否則Oracle會(huì)自動(dòng)截取前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,'請(qǐng)不要在休息日添加數(shù)據(jù)');
when updating then raise_application_error(-20003,'請(qǐng)不要在休息日修改數(shù)據(jù)');
when deleting then raise_application_error(-20004,'請(qǐng)不要在休息日刪除數(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ā)器是針對(duì)emp表的，所以PLSQL就知道這里的sal是blog表的字段
--':new'修飾符用于訪問(wèn)操作完成后列的值，':old'修飾符用于訪問(wèn)操作完成前列的值
if (:new.sal<:old sal="" or="" :new="" sal="">:old.sal*1.2) then
raise_application_error(-20005,'修改后的工資不能低于原工資，也不能高出原工資的20%');
else
dbms_output.put_line('原來(lái)的工資：'||:old.sal||' 現(xiàn)在的工資：'||:new.sal);
end if;
end;
--6)刪除表記錄時(shí)，自動(dòng)將刪除掉的記錄備份到另外一張表中
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ù)庫(kù)名 -- ora_login_user --返回登陸的用戶名 -- ora_sysevent --返回觸發(fā)觸發(fā)器的系統(tǒng)事件名 -- ora_des_encrypted_password --返回用戶DES加密后的密碼 --系統(tǒng)觸發(fā)器的基本語(yǔ)法 create [or replace] trigger trigger_name after[before] logon[logoff] on database --固定寫法，這就不存在for each row屬性了，因?yàn)樗轻槍?duì)數(shù)據(jù)庫(kù)的 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; 定義并使用變量 --標(biāo)量的案例
v_ename varchar2(10); --定義一個(gè)變長(zhǎng)字符串
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)量的使用
--這里需要說(shuō)明的是，PL/SQL塊為變量賦值不同于其它的編程語(yǔ)言，需要在等號(hào)前加冒號(hào)，即(:=)
--下面以輸入員工號(hào)，顯示員工姓名、工資、個(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;
--若員工姓名超過(guò)5個(gè)字符，就會(huì)出現(xiàn)錯(cuò)誤。那么為了降低PL/SQL程序的維護(hù)工作量，可以使用(%type)屬性定義變量
--這樣它會(huì)按照數(shù)據(jù)庫(kù)列，來(lái)確定變量類型和長(zhǎng)度，格式為(標(biāo)識(shí)符 表名.列名%type)，
v_ename emp.ename%type;
--復(fù)合變量(composite)
--用于存放多個(gè)值的變量，主要包括PL/SQL記錄、PL/SQL表、嵌套表(nested table)、動(dòng)態(tài)數(shù)組(varray)等
--復(fù)合類型之PL/SQL記錄
--類似于高級(jí)語(yǔ)言中的結(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)于高級(jí)語(yǔ)言中的數(shù)組
--注意：高級(jí)語(yǔ)言中數(shù)組下標(biāo)不能為負(fù)數(shù)，而PL/SQL是可以為負(fù)數(shù)的，且表元素的下標(biāo)沒(méi)有限制
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ù)值指針的變量。通過(guò)使用參照變量，可使得應(yīng)用程序共享相同對(duì)象，從而降低占用的空間
--編寫PL/SQL程序時(shí)，可使用游標(biāo)變量(ref cursor)和對(duì)象類型變量(ref obj_type)兩種參照變量類型
--參照變量之游標(biāo)變量
--定義游標(biāo)時(shí)，不需要指定相應(yīng)的select語(yǔ)句
--但在使用游標(biāo)(open)時(shí)需要指定select語(yǔ)句，這樣一個(gè)游標(biāo)就與一個(gè)select語(yǔ)句結(jié)合了
--使用PL/SQL編寫一個(gè)塊，要求輸入部門號(hào)，顯示該部門所有員工的姓名和工資
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;
|
|
|
