/*
以下代碼是對(duì)emp表進(jìn)行顯示寬度設(shè)置
*/
col empno for 9999;
col ename for a10;
col job for a10;
col mgr for 9999;
col hiredate for a12;
col sal for 9999;
col comm for 9999;
col deptno for 99;
col tname for a12;
set pagesize 50;

//------------------------------------------------------------------------------------------------------

使用loop循環(huán)顯示1-10【loop循環(huán)】
declare
--聲明變量
i number(2);
begin
i := 1;
--以下代碼是循環(huán)
loop
exit when i>10;
dbms_output.put_line(i);
i := i+1;
end loop;
end;
/

使用while循環(huán)顯示10-20【while循環(huán)】
declare
i number(2) := 10;
begin
while i<=20
loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;
/

使用for循環(huán)顯示20-30【for循環(huán)】
declare
i number;
begin
for i in 20..30
loop
dbms_output.put_line(i);
end loop;
end;
/

使用無(wú)參光標(biāo)cursor，查詢所有員工的姓名和工資【如果需要保存多行記錄時(shí)，使用光標(biāo)cursor】
declare
--定義一個(gè)cursor，里面裝多條記錄
cursor cemp is select ename,sal from emp;
--聲明二個(gè)普通變量
pename emp.ename%type;
psal emp.sal%type;
begin
--打開cursor
open cemp;
--循環(huán)
loop
--將cursor下移，將用戶名和工資存入二個(gè)自定義普通變量中
fetch cemp into pename,psal;
--判斷是否該退出循環(huán)，切記
exit when cemp%notfound;
--顯示
dbms_output.put_line(pename||'的工資是'||psal);
end loop;
--關(guān)閉cursor
close cemp;
end;
/

使用無(wú)參光標(biāo)，給員工漲工資，ANALYST漲1000，MANAGER漲800，其它漲400【編號(hào)，姓名，職位，薪水】
declare
cursor cemp is select empno,ename,job,sal from emp;
pempno emp.empno%type;
pename emp.ename%type;
pjob emp.job%type;
psal emp.sal%type;
begin
open cemp;
loop
fetch cemp into pempno,pename,pjob,psal;
exit when cemp%notfound;
--if是PLSQL
if pjob='ANALYST' then
--update是SQL
update emp set sal=sal+1000 where empno=pempno;
elsif pjob='MANAGER' then
update emp set sal=sal+800 where empno=pempno;
else
update emp set sal=sal+400 where empno=pempno;
end if;
end loop;
commit;
close cemp;
end;
/

使用帶參光標(biāo)，查詢20號(hào)部門的員工姓名和工資，工資都加800
declare
--定義一個(gè)帶參cursor
cursor cemp(pdeptno number) is select empno,ename,sal from emp where deptno=pdeptno;
pename emp.ename%type;
psal emp.sal%type;
pempno emp.empno%type;
begin
--打開光標(biāo)，同時(shí)傳入實(shí)際參數(shù)
open cemp(20);
loop
fetch cemp into pempno,pename,psal;
exit when cemp%notfound;
update emp set sal=sal+800 where empno=pempno;
end loop;
--關(guān)閉光標(biāo)
close cemp;
end;
/

oracle系統(tǒng)內(nèi)置例外，被0除異常【zero_divide】
declare
i number;
s number;
begin
i := 10;
s := i/0;
exception
when zero_divide then
dbms_output.put_line('自已捕獲系統(tǒng)內(nèi)置例外');
end;
/

用戶自定義例外，沒有找到員工例外【no_emp_found】
declare
cursor cemp(pempno number) is select ename from emp where empno=pempno;
pename emp.ename%type;
--聲明自定義例外
no_emp_found exception;
begin
open cemp(1111);
loop
fetch cemp into pename;
--如果沒有找到員工
if cemp%notfound then
--拋例外
raise no_emp_found;
end if;
end loop;
close cemp;
exception
when no_emp_found then
dbms_output.put_line('查無(wú)此員工');
end;
/

//------------------------------------------------------------------------------------------------------

創(chuàng)建無(wú)參存儲(chǔ)過(guò)程hello，無(wú)返回值

create or replace procedure hello
as
begin
dbms_output.put_line('這就是存儲(chǔ)過(guò)程');
end;
/

刪除存儲(chǔ)過(guò)程hello
drop procedure hello;

調(diào)用存儲(chǔ)過(guò)程方式一【exec 存儲(chǔ)過(guò)程名】
exec hello;

調(diào)用存儲(chǔ)過(guò)程方式二【PLSQL程序】
begin
--調(diào)用存儲(chǔ)過(guò)程
hello;
end;
/

調(diào)用存儲(chǔ)過(guò)程方式三【JDBC】
CallableStatement

創(chuàng)建有參存儲(chǔ)過(guò)程raiseSalary(編號(hào))，為7369號(hào)員工漲10%的工資，并顯示出漲前和漲后的工資【演示in的用法，默認(rèn)in】
create or replace procedure raiseSalary(pempno number)

as
--as看作declare,但不能出現(xiàn)declare，聲明變量
psal emp.sal%type;
begin
--查詢編碼為7369號(hào)員工的工資
select sal into psal from emp where empno=pempno;
--顯示
dbms_output.put_line('7369號(hào)員工漲前工資'||psal);
dbms_output.put_line('7369號(hào)員工漲后工資'||psal*1.1);
end;
/
exec raiseSalary(7369);

創(chuàng)建無(wú)參存儲(chǔ)函數(shù)myshow，有返回值
create or replace function myshow return varchar2
as
begin
return '哈哈';
end;
/

刪除存儲(chǔ)函數(shù)myshow
drop function myshow;

調(diào)用存儲(chǔ)函數(shù)方式一【PLSQL程序】

declare
value varchar2(6);
begin
value := myshow();
--value := myshow;可以
dbms_output.put_line(value);
end;
/

調(diào)用存儲(chǔ)函數(shù)方式二【JDBC】
CallableStatement

創(chuàng)建有參存儲(chǔ)函數(shù)findEmpIncome(編號(hào))，查詢7369號(hào)員工的年收入【演示in的用法，默認(rèn)in】

create or replace function findEmpIncome(pempno in number) return number
as
--年收入
income number;
begin
select sal*12+NVL2(comm,comm,0) into income from emp where empno=pempno;
--返回年收入
return income;
end;
/

declare
income number;
begin
income := findEmpIncome(7369);
dbms_output.put_line('年收入是'||income);
end;
/

創(chuàng)建有參存儲(chǔ)函數(shù)findEmpNameAndSal(編號(hào))，查詢7902號(hào)員工的的姓名和月薪，【返回多個(gè)值，演示out的用法】
當(dāng)返回2個(gè)或多個(gè)值，必須使用out符號(hào)
當(dāng)返回1個(gè)值，就無(wú)需out符號(hào)

create or replace function findEmpNameAndSal(pempno in number,pename out varchar2) return number
as
psal emp.sal%type;
begin
select ename,sal into pename,psal from emp where empno=pempno;
--返回月薪
return psal;
end;
/

---------------------------------------相互轉(zhuǎn)值

declare
psal emp.sal%type;
pename emp.ename%type;
begin
psal := findEmpNameAndSal(7902,pename);
dbms_output.put_line('7902號(hào)員工的姓名'||pename||'，薪水是'||psal);
end;
/

創(chuàng)建有參存儲(chǔ)過(guò)程findEmpNameAndSalAndJob(編號(hào))，查詢7902號(hào)員工的的姓名，職位，月薪【演示out的用法】
create or replace procedure
findEmpNameAndSalAndJob(pempno in number,pename out varchar2,pjob out varchar2,psal out number)
as
begin
select ename,job,sal into pename,pjob,psal from emp where empno=pempno;
end;
/

declare
pename emp.ename%type;
pjob emp.job%type;
psal emp.sal%type;
begin
findEmpNameAndSalAndJob(7902,pename,pjob,psal);
dbms_output.put_line('7902號(hào)員工的姓名是'||pename||':'||pjob||':'||psal);
end;
/

什么情況下使用存儲(chǔ)過(guò)程？什么情況下使用存儲(chǔ)函數(shù)？
項(xiàng)目中的原則：
A)如果只有一個(gè)返回值：用函數(shù)
B)如果無(wú)返回值，或超過(guò)1個(gè)以上的返回值，用過(guò)程

創(chuàng)建語(yǔ)句級(jí)觸發(fā)器insertEmpTrigger，當(dāng)對(duì)emp表進(jìn)行insert操作前，顯示"hello world"
create or replace trigger insertEmpTrigger
before
insert
on emp
begin
dbms_output.put_line('插入記錄之前執(zhí)行');
end;
/

刪除觸發(fā)器insertEmpTrigger
drop trigger insertEmpTrigger;

使用insert語(yǔ)句操縱表，引起觸發(fā)器工作
insert into emp(empno,ename,job,sal) values(1122,'JACK','IT',5000);

insert into emp
select *
from new_emp;

創(chuàng)建語(yǔ)句級(jí)觸發(fā)器deleteEmpTrigger，當(dāng)對(duì)emp表進(jìn)行delete操作后，顯示"world hello"
create or replace trigger deleteEmpTrigger
after
delete
on emp
begin
dbms_output.put_line('刪除記錄之后執(zhí)行');
end;
/

周一到周五，且9-17點(diǎn)能向數(shù)據(jù)庫(kù)插入數(shù)據(jù)，否則【raise_application_error('-20000','例外原因')】

分析：
A)周六，周日 不管何時(shí) 不能插入數(shù)據(jù)
B)周一到周五 9-17之外，不包括9和17點(diǎn)，不能插入數(shù)據(jù)
create or replace trigger securityEmpTrigger
before
insert
on emp
declare
pday varchar2(9);
phour number(2);
begin
select to_char(sysdate,'day') into pday from dual;
--隱式將varchar2轉(zhuǎn)成number
select to_char(sysdate,'hh24') into phour from dual;
--判斷
if (pday in ('星期六','星期日')) or (phour not between 9 and 17) then
--拋例外，該例是系統(tǒng)的
raise_application_error('-20999','不是工作時(shí)間，不能操作數(shù)據(jù)庫(kù)');
end if;
end;
/

創(chuàng)建行級(jí)觸發(fā)器，漲后工資這一列，確保大于漲前工資【for each row/:new.sal/:old.sal】

create or replace trigger checkSalaryTrigger
after
update of sal
on emp
for each row
begin
--如果更新后的值<更新前的值
if :new.sal < :old.sal then
--拋例外
raise_application_error('-20888','工資不能越漲越低');
end if;
end;
/

update emp set sal=sal+1 where ename='SMITH';

錯(cuò)誤編號(hào)的范圍：[20000-20999]

//--------------------------------------------------------------------------------------------------
***
Java 調(diào)用 Oracle 存儲(chǔ) 過(guò)程 與 函數(shù)
CallableStatement
String sql = "{call raiseSalary(?,?)}";//過(guò)程
String sql = "{?=call findEmpNameAndSal(?,?)}";//函數(shù)
cstmt.setInt(1,7369);
cstmt.registerOutParameter(2,Types.VARCHAR);
cstmt.execute();
String message = cstmt.getString(2);

過(guò)程:findEmpNameAndSalAndJob()
函數(shù)：findEmpNameAndSal()
找oracle11的對(duì)應(yīng)的JDBC驅(qū)動(dòng)：E:\app\Administrator\product\11.2.0\dbhome_1\jdbc\lib\ojdbc5.jar

//--------------------------------------------------------------------------------------------------

Java 調(diào)用 Oracle 存儲(chǔ) 大對(duì)象【CLOB 與 BLOB】 4G
private static String driver = "oracle.jdbc.driver.OracleDriver";
//不一定是1521，但優(yōu)先選用1521端口號(hào)
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
private static String username = "scott";
private static String password = "123456";
文本：CLOB(Character)
多媒體：BLOB(Binary)

//--------------------------------------------------------------------------------------------------

*****
jquery/ajax + struts2 + javabean + hibernate + oracle
需求：
1）使用異步方式，檢查用戶名是否存在，參見<<綜合練習(xí).JPG>>
2）注冊(cè)英雄
3）分頁(yè)查詢所有英雄


hibernate
query.setFirstResult(0);
query.setMaxResult(6)
框架自動(dòng)將分頁(yè)API轉(zhuǎn)成oracle專有的頁(yè)面語(yǔ)句
oracle

//--------------------------------------------------------------------------------------------------

?

?

?

總結(jié)

以上是生活随笔為你收集整理的oracle 存储过程 ，触发器练习的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。