CREATE OR REPLACE PROCEDURE INSERTAMOUNTTEST
(
ST_NUM IN NUMBER,
ED_NUM IN NUMBER
)
IS
BEGIN
declare
i number;
begin
FOR i IN ST_NUM..ED_NUM LOOP
INSERT INTO tb values(i,i,'3','3','3',100,'0');
END LOOP;
end;
END;
運(yùn)行：
sql>execute INSERTAMOUNTTEST(1,45000) -- 一次插入45000條測(cè)試數(shù)據(jù)
2、從存儲(chǔ)過(guò)程中返回值
create or replace procedure spaddflowdate
(
varAppTypeId in varchar2,
varFlowId in varchar2,
DateLength in number,
ReturnValue out number --返回值
)
is
begin
insert into td values(varAppTypeId,varFlowId,DateLength)
returning 1 into ReturnValue; --返回值
commit;
exception
when others then
rollback;
end;
存儲(chǔ)過(guò)程的執(zhí)行
sql>variable testvalue number;
sql>execute spaddflowdate('v','v',2,:testvalue);
sql>print
就可以看到執(zhí)行結(jié)果
3、用包實(shí)現(xiàn)存儲(chǔ)過(guò)程返回游標(biāo)：
create or replace package test_p
as
type outList is ref cursor;
PROCEDURE getinfor(taxpayerList out outList);
end test_p;
/
create or replace package body test_p as PROCEDURE getinfor(taxpayerList out outList) is begin
OPEN taxpayerList FOR select * from
td where tag='0';
end getinfor;
end test_p;
/
運(yùn)行：
set serverout on; --將輸出工具打開
variable x refcursor;
execute test_p.getinfor(:x);
exec test_p.getinfor(:x);
print x;
drop package test_p;
/*procedural language/sql*/
--1、過(guò)程、函數(shù)、觸發(fā)器是pl/sql編寫的
--2、過(guò)程、函數(shù)、觸發(fā)器是在oracle中的
--3、pl/sql是非常強(qiáng)大的數(shù)據(jù)庫(kù)過(guò)程語(yǔ)言
--4、過(guò)程、函數(shù)可以在java程序中調(diào)用
--提高效率：優(yōu)化sql語(yǔ)句或?qū)懘鎯?chǔ)過(guò)程
--pl/sql移植性不好
--IDE(Integration Develop Environment)集成開發(fā)環(huán)境
--命令規(guī)則：
--變量(variable) v_
--常量(constant) c_
--指針、游標(biāo)(cursor) _cursor
--例外、異常(exception) e_
--可定義的變量和常量：
--標(biāo)量類型：scalar
--復(fù)合類型：composite --存放記錄、表、嵌套表、varray
--參照類型：reference
--lob(large object)
《PL/SQL 基本語(yǔ)法》
--例：創(chuàng)建存儲(chǔ)過(guò)程
create or replace procedure pro_add
is
begin
insert into mytest values('韓xx','123');
end;
exec pro_add; --調(diào)用
--查看錯(cuò)誤信息
show error;
--調(diào)用過(guò)程
exec 過(guò)程(c1,c2,...);
call 過(guò)程(c1,c2,...);
--打開/關(guān)閉輸出選項(xiàng)
set serveroutput on/off
--輸入
&
--塊結(jié)構(gòu)示意圖
declare --定義部分，定義常量、變量、游標(biāo)、例外、復(fù)雜數(shù)據(jù)類型
begin --執(zhí)行部分，執(zhí)行pl/sql語(yǔ)句和sql語(yǔ)句
exception --例外處理部分，處理運(yùn)行的各種錯(cuò)誤
end; --結(jié)束
--《實(shí)例演示》
declare
v_ival number(4) :=100; --聲明并初始化變量
--v_dtm date;
v_dtm syslogs.dtm%type; --取表字段類型
v_content varchar(512);
begin
v_ival := v_ival * 90; --賦值運(yùn)算
insert into syslogs values(seq_syslogs.nextval,10,sysdate,'v_ival='||v_ival,user);--數(shù)據(jù)庫(kù)存儲(chǔ)
dbms_output.put_line('v_ival'||v_ival);
select count(*) into v_ival from syslogs;--使用select查詢賦值
--select ename,sal into v_name,v_sal from emp where empno=&aa;
insert into syslogs values (seq_syslogs.nextval,10,sysdate,'日志條數(shù)='||v_ival,user);
dbms_output.put_line('日志條數(shù)'||v_ival);
--獲取日志序號(hào)==11的日志時(shí)間和日志內(nèi)容
select dtm , content
into v_dtm,v_content
from syslogs
where logid=14;
insert into syslogs values (seq_syslogs.nextval,'10',sysdate,'v_dtm='||v_dtm||'v_content='||v_content,user);
dbms_output.put_line('v_dtm='||v_dtm||'v_content='||v_content);
--修改日志序號(hào)=11的日志記錄人
update syslogs
set whois='PL/SQL.'||v_ival
where logid = 14;
--delete syslogs where logid=15;
--分支流程控制
if v_ival>50 then
dbms_output.put_line('日志需要清理了~');
else
dbms_output.put_line('日志空間正常！');
end if;
--Loop循環(huán)
v_ival :=0;
loop
exit when v_ival>3;
--循環(huán)體
v_ival := v_ival+1;
dbms_output.put_line('loop循環(huán)：'||v_ival);
end loop;
--While循環(huán)
v_ival := 0;
while v_ival < 4
loop
--循環(huán)體
v_ival := v_ival+1;
dbms_output.put_line('while循環(huán)：'||v_ival);
end loop;
--For循環(huán)
for v_count in reverse 0..4 loop --reverse遞減
dbms_output.put_line('for循環(huán)：'||v_count);
end loop;
commit;--提交事物
end;
select * from syslogs;
《PL/SQL 異常處理》
--PL/SQL異常處理：oracle內(nèi)置異常，oracle用戶自定義異常
declare
v_title logtypes.tid%type;
v_ival number(9,2);
--自定義的異常
ex_lesszero exception ;
begin
--select title into v_title
--from logtypes --; too_many_rows
--where tid = 30 ; --NO_DATA_FOUND 異常
v_ival := 12/-3;
if v_ival < 0 then
--直接拋出異常
--raise ex_lesszero ;
--使用系統(tǒng)存儲(chǔ)過(guò)程拋出異常
raise_application_error(/*錯(cuò)誤代碼，-20000~-20999*/-20003,/*異常描述*/'參數(shù)不能小于0！');
end if;
commit;
exception
--異常處理代碼塊
when no_data_found then
dbms_output.put_line('發(fā)生系統(tǒng)異常：未找到有效的數(shù)據(jù)！');
when too_many_rows then
dbms_output.put_line('發(fā)生系統(tǒng)異常：查詢結(jié)果超出預(yù)期的一行！');
when ex_lesszero then
dbms_output.put_line('發(fā)生用戶異常：數(shù)值不能為負(fù)！'||sqlcode||'異常描述：'||sqlerrm);
when others then --other例如Exception
rollback;
dbms_output.put_line('發(fā)生異常！'||sqlcode||'異常的描述：'||sqlerrm);
end;
《PL/SQL 游標(biāo)的使用》
declare
--游標(biāo)的聲明
cursor myCur is
select tid,title from logtypes ;
--定義接收游標(biāo)中的數(shù)據(jù)變量
v_tid logtypes.tid%type;
v_title logtypes.title%type;
--通過(guò)記錄來(lái)接受數(shù)據(jù)
v_typercd myCur%rowtype ;
begin
--打開游標(biāo)
open myCur ;
--取游標(biāo)中的數(shù)據(jù)
loop
--遍歷游標(biāo)中的下一行數(shù)據(jù)
fetch myCur into v_tid,v_title ;
--檢測(cè)是否已經(jīng)達(dá)到最后一行
exit when myCur%notfound ;
--輸出游標(biāo)中的數(shù)據(jù)
dbms_output.put_line('讀取tid='||v_tid||' title='||v_title);
end loop;
--關(guān)閉游標(biāo)
close myCur;
--打開游標(biāo)
open myCur ;
loop
fetch myCur into v_typercd ;
exit when myCur%notfound ;
dbms_output.put_line('--//讀取tid='||v_typercd.tid||' title='||v_typercd.title);
end loop;
--關(guān)閉游標(biāo)
close myCur ;
--for循環(huán)游標(biāo)
for tmp_record in myCur loop
dbms_output.put_line('++//讀取tid='||tmp_record.tid||' title='||tmp_record.title);
end loop;
end;
《PL/SQL 存儲(chǔ)過(guò)程★》
-- 可以聲明入?yún)n，out表示出參，但是無(wú)返回值。
create or replace procedure prc_writelog(/*日志類型*/ tid in number ,
/*日志內(nèi)容*/ content in varchar2 ,
/*錯(cuò)誤碼 */ i_ret out number ,
/*錯(cuò)誤描述*/ s_ret out varchar2 )
is
begin
insert into syslogs values (seq_syslogs.nextval , tid ,sysdate ,content ,user);
commit;
i_ret := 1 ;
s_ret := '記錄日志成功！' ;
exception
when others then
rollback ;
i_ret := -1 ;
s_ret := '記錄日志失敗：'||sqlerrm ;
end;
--測(cè)試
declare
iRet number(4) ;
sRet varchar2(128) ;
begin
prc_writelog(10,'測(cè)試存儲(chǔ)過(guò)程',iRet,sRet);
dbms_output.put_line('iRet:'||iRet||'sRet'||sRet);
end;
select * from syslogs;
《PL/SQL 觸發(fā)器》
--觸發(fā)器 是一種基于數(shù)據(jù)庫(kù)特定事件的 由數(shù)據(jù)庫(kù)自動(dòng)執(zhí)行的pl/sql塊
--觸發(fā)的事件源：database 【啟動(dòng)、停止、用戶聯(lián)機(jī)...】
-- 表名【insert/update/delete】
--觸發(fā)時(shí)機(jī) before/after
--語(yǔ)句級(jí)、行級(jí)（需要知道數(shù)據(jù),對(duì)數(shù)據(jù)庫(kù)運(yùn)行速度有影響）
create or replace trigger tri_logtypes
after insert or update or delete --在所有的表的事件發(fā)生后執(zhí)行
on logtypes
for each row --行級(jí) （:new , :old）
declare
iret number(4);
sret varchar2(128);
begin
--不要有事物的管理
--:new 新數(shù)據(jù) 記錄型
--:old 原有的數(shù)據(jù) 記錄型
--prc_writelog(10,'觸發(fā)器執(zhí)行了！',iret,sret);
if inserting then
insert into syslogs values(seq_syslogs.nextval,10,sysdate,'觸發(fā)器執(zhí)行添加數(shù)據(jù)！',user);
elsif updating then
if :new.title <> :old.title then
raise_application_error(-20001,'不允許修改日志類型名稱數(shù)據(jù)！'); --拋出異常
end if;
insert into syslogs values(seq_syslogs.nextval,10,sysdate,'觸發(fā)器執(zhí)行更新數(shù)據(jù)！',user);
elsif deleting then
raise_application_error(-20001,'不允許刪除表中的數(shù)據(jù)！');
insert into syslogs values(seq_syslogs.nextval,10,sysdate,'觸發(fā)器執(zhí)行刪除數(shù)據(jù)！',user);
end if;
end ;
--test!
insert into logtypes values(30,'test log');
delete from logtypes where tid = 30;
update logtypes set title = 'test log' where tid = 30;
select * from syslogs order by dtm desc;
select * from logtypes ;
《案例》
--創(chuàng)建表
create table emp2 (
name varchar2(30),
sal number(8,2)
);
insert into emp2 values('simple',99999);
insert into emp2 values(&a,&b);
--存儲(chǔ)過(guò)程案例：
--修改員工工資
create or replace procedure pro_input(t_name in varchar2,
t_sal in number)
is
begin
update emp2 set sal = t_sal where name=t_name;
end;
--Test!
declare
begin
pro_input('simple',2000);
end;
select * from emp2;
--函數(shù)案例：
create or replace function fun_test(t_name varchar2)
return number is yearSal number(7,2);
begin
select sal*12 into yearSal from emp2 where name = t_name;
return yearSal;
end;
--包案例：
create package pac_test
is --創(chuàng)建一個(gè)包pac_test
procedure pro_input(t_name varchar2,t_sal number); --聲明該包有一個(gè)過(guò)程 pro_input
function fun_test(t_name varchar2) return number; --聲明該包有一個(gè)函數(shù) fun_test
end;
--包體案例:
create package body pac_test
is
procedure pro_input(t_name in varchar2,t_sal in number)
is
begin
update emp2 set sal = t_sal where name=t_name;
end;
function fun_test(t_name varchar2)
return number is yearSal number(7,2);
begin
select sal*12 into yearSal from emp2 where name = t_name;
return yearSal;
end;
end ;
--調(diào)用包中的函數(shù)或過(guò)程
call pac_test.pro_input('summer',1000);
call pac_test.fun_test
select pac_test.fun_test('simple') from dual;
--案例：
select * from emp2;
--下面以輸入員工工號(hào)，顯示雇員姓名、工資、個(gè)人所得稅
--稅率（0.03）。
declare
c_tax_rate number(3,2):=0.03; --常量，稅率
--v_name varchar2(30);
v_name emp2.name%type;
--v_sal number(8,2);
v_sal emp2.sal%type;
v_tax_sal number(8,2);
begin
--執(zhí)行
select name,sal into v_name,v_sal from emp2 where name = &na;
--計(jì)算所得稅
v_tax_sal:=v_sal*c_tax_rate;
--輸出
dbms_output.put_line('姓名：'||v_name||' 工資'||v_sal||' 交稅'||v_tax_sal);
end;
--pl/sql記錄實(shí)例
declare
--定義一個(gè)pl/sql記錄類型 emp_record_type ，類型包含2個(gè)數(shù)據(jù)，t_name,t_sal
type emp_record_type is record(t_name emp2.name%type,t_sal emp2.sal%type);
--定義一個(gè) record_test 變量，類型是 emp_record_type
record_test emp_record_type;
begin
select name,sal into record_test from emp2 where name = 'simple';
dbms_output.put_line('員工工資:'||record_test.t_sal);
end;
--pl/sql表實(shí)例
declare
--定義了一個(gè)pl/sql表類型 emp_table_type 該類型是用于存放 emp.name%type元素類型 的數(shù)組
-- index by binary_integer 下標(biāo)是整數(shù)
type emp_table_type is table of emp2.name%type index by binary_integer;
--定義一個(gè) table_test 變量
table_test emp_table_type;
begin
--table_test(0)下標(biāo)為0的元素
select name into table_test(0) from emp2 where name='summer';
dbms_output.put_line('員工:'||table_test(0));
end;
--案例
--顯示該部門的所有員工和工資
declare
--定義游標(biāo)類型 emp_cursor
type emp_cursor is ref cursor;
--定義一個(gè)游標(biāo)變量
cursor_test emp_cursor;
--定義變量
v_name emp2.name%type;
v_sal emp2.sal%type;
begin
--執(zhí)行
--把cursor_test 和一個(gè)select結(jié)合
open cursor_test for
select name,sal from emp2;
--循環(huán)取出
loop
--fetch取出 游標(biāo) 給 v_name,v_sal
fetch cursor_test into v_name,v_sal;
--判斷工資
if v_sal<1000 then
update emp2 set sal = v_sal+1000 where sal=v_sal;
end if;
--判斷cursor_test是否為空
exit when cursor_test%notfound;
dbms_output.put_line('姓名：'||v_name||' 薪水：'||v_sal);
end loop;
end;
select * from emp2;
--《分頁(yè)》案例：
--建表
drop table book;
create table book(
bookId number(5),
bookName varchar2(50),
publishHouse varchar2(50)
);
--編寫過(guò)程
create or replace procedure pro_pagination( t_bookId in number,
t_bookName in varchar2,
t_publishHouse in varchar2)
is
begin
insert into book values(t_bookId,t_bookName,t_publishHouse);
end;
--在java中調(diào)用
--select * from book;
--insert into book values(11,'流星','蝴蝶');
--commit;
--有輸入和輸出的存儲(chǔ)過(guò)程
create or replace procedure pro_pagination2( i_id in number,
o_name out varchar2,
o_publishHouse out varchar2
)
is
begin
select bookName,publishHouse into o_name,o_publishHouse from book where bookId = i_id;
end;
--Test!
declare
err book.bookname%type;
err2 book.publishhouse%type;
begin
pro_pagination2(10,err,err2);
dbms_output.put_line(err||' '||err2);
end;
--返回結(jié)果集的過(guò)程
--1、創(chuàng)建一個(gè)包
create or replace package testpackage
as
type cursor_test is ref cursor;
end testpackage;
--2、建立存儲(chǔ)過(guò)程
create or replace procedure pro_pagination3(
o_cursor out testpackage.cursor_test)
is
begin
open o_cursor for
select * from book;
end;
--3、如何在java中調(diào)用
--Test!
declare
err testpackage.cursor;
begin
pro_pagination2(10,err);
dbms_output.put_line(err);
end;
<Oracle的分頁(yè)>
select t1.*,rownum rn from (select * from emp) t1;
select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;
--在分頁(yè)的時(shí)候，可以把下面的sql語(yǔ)句當(dāng)做一個(gè)模板使用
select * from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=10) where rn>=6;
--開發(fā)一個(gè)包
--1、創(chuàng)建一個(gè)包
create or replace package testpackage
as
type cursor_test is ref cursor;
end testpackage;
--開始編寫分頁(yè)的過(guò)程
create or replace procedure fenye(tableName in varchar2,
pageSize in number, --每頁(yè)顯示記錄數(shù)
pageNow in number,
myRows out number,--總記錄數(shù)
myPageCount out number,--總頁(yè)數(shù)
p_cursor out testpackage.cursor_test)
is
--定義sql語(yǔ)句 字符串
v_sql varchar2(1000);
--定義2個(gè)整數(shù)
v_begin number:=(pageNow-1)*pageSize+1;
v_end number:=pageNow*pageSize;
begin
v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin||'';
--把游標(biāo)和sql關(guān)聯(lián)
open p_cursor for v_sql;
--計(jì)算myRows和myPageCount
--組織一個(gè)sql
v_sql:='select count(*) from '||tableName||'';
--執(zhí)行sql，并把返回的值，賦給myRows
execute immediate v_sql into myRows;
--計(jì)算myPageCount
if mod(myRows,pageSize)=0 then
myPageCount:=myRows/pageSize;
else
myPageCount:=myRows/pageSize+1;
end if;
--關(guān)閉游標(biāo)
--close p_cursor;
end;
--使用java測(cè)試
具體寫發(fā) http://qindingsky.blog.163.com/blog/static/3122336200977111045401/