oracle函数及存储过程教程.docx
- 文档编号:24023029
- 上传时间:2023-05-23
- 格式:DOCX
- 页数:16
- 大小:18.44KB
oracle函数及存储过程教程.docx
《oracle函数及存储过程教程.docx》由会员分享,可在线阅读,更多相关《oracle函数及存储过程教程.docx(16页珍藏版)》请在冰豆网上搜索。
oracle函数及存储过程教程
u--常用变量类型
1.binary_integer:
整数,主要用来计数而不是用来表示字段类型
2.number数字类型3.char定长字符串4.date日期5.long长字符串,最长2GG
6.boolean类型:
可以取值true、false、null,默认为null,
declare
v_enameemp.ename%type;
v_salemp.sal%type;
begin
selectename,salintov_ename,v_salfromempwhereempno=7902;
ifv_enameisnullthen
dbms_output.put_line('null');
else
dbms_output.put_line(v_ename||''||v_sal);
endif;
end;
------------Record变量类型,相当于类------------------
declare
typeemp_recordisrecord
(v_enameemp.ename%type,
v_salemp.sal%type,
v_empnoemp.empno%type
);
v_empemp_record;
begin
selectename,sal,empnointov_empfromempwhereempno=7902;
dbms_output.put_line(v_emp.v_ename||','||v_emp.v_sal||','||v_emp.v_empno);
end;
------------------------------------
declare
typetest_recordisrecord(
v_enameemp.ename%type,
v_salemp.sal%type,
v_locdept.loc%type,
v_dnamedept.dname%type,
v_deptnodept.deptno%type
);
v_testtest_record;
begin
selecte.ename,e.sal,d.loc,d.dname,d.deptno
intov_testfromempejoindeptd
on(e.deptno=d.deptno)
wheree.empno=7902;
dbms_output.put_line(v_test.v_ename||','||v_test.v_sal);
end;
--使用%rowtype声明record变量----------------
declare
v_tempdept%rowtype;
begin
v_temp.deptno:
=50;
v_temp.dname:
='yugang';
v_temp.loc:
='beijing';
dbms_output.put_line(v_temp.deptno||''||v_temp.dname||''||v_temp.loc);
end;
-----------------Table变量类型,相当于数组类型-------------
-------while循环------------
declare
typetype_tableistableofnumberindexbybinary_integer;
v_tabletype_table;
v_indexbinary_integer;
begin
v_table(20):
=20;
v_table(-1):
=-1;
v_index:
=v_table.first;
whilev_index<=v_table.lastloop
dbms_output.put_line(v_table(v_index));
v_index:
=v_table.next(v_index);
endloop;
end;
注:
binary_integer表示数组下标值
-------------for循环------------------
declare
typetype_tableistableofnumberindexbybinary_integer;
v_tabletype_table;
begin
v_table(0):
=20;
v_table
(1):
=21;
v_table
(2):
=22;
v_table(3):
=23;
foriinv_table.first..v_table.lastloop
dbms_output.put_line(v_table(i));
endloop;
end;
---------------------Cursor的使用-------------------
Declare
Cursorcis
Select*fromemp;
V_empc%rowtype;
Begin
Openc;
Fetchcintov_emp;
Dbms_output.put_line(v_emp.ename);
Closec;
End;
-------------------Cursor+for循环使用----------
declare
cursorcis
select*fromemp;
begin
forv_empincloop
dbms_output.put_line(v_emp.ename);
endloop;
end;
---------------Cursor+when循环的使用----------------
declare
cursoremp_curis
selectenamefromemp;
v_nameemp.ename%type;
begin
openemp_cur;
loop
fetchemp_curintov_name;
exitwhenemp_cur%notfound;
dbms_output.put_line(v_name);
endloop;
closeemp_cur;
end;
---------------Cursor+while循环使用-------------------------
declare
cursoremp_curis
selectenamefromemp;
v_nameemp.ename%type;
begin
openemp_cur;
fetchemp_curintov_name;
while(emp_cur%found)loop
dbms_output.put_line(v_name||’’||cur%rowcount);
fetchemp_curintov_name;
endloop;
dbms_output.put_line(v_name);
closeemp_cur;
end;
注:
%rowcount表示第几个
结束本次循环:
Loop
...
if(true)then
gotonextloop;
endif;
...
<
null;
endloop;
-----------------------Cursor的优化-------------------------------------
declare
typeemp_indtabistableofvarchar2(20)indexbybinary_integer;
v_empemp_indtab;
begin
selectenamebulkcollectintov_empfromemp;//将集合放在bulkcollect中
foriinv_emp.first..v_emp.lastloop
dbms_output.put_line(v_emp(i));
endloop;
end;
------------------------------Cursor+bulkcollect--------------------------
declare
typeemp_indtabistableofvarchar2(20)indexbybinary_integer;
v_empemp_indtab;
cursoremp_curis
selectenamefromemp;
begin
openemp_cur;
fetchemp_curbulkcollectintov_emplimit10;
whilev_emp.count>0loop
foriinv_emp.first..v_emp.lastloop
dbms_output.put_line(v_emp(i));
endloop;
fetchemp_curbulkcollectintov_emplimit10;
endloop;
end
loop
fetchemp_curintoename,empno,sal,deptname;
exitwhenemp_cur%notfound;+
insertintobd_psndocvalues(fun_pkpsndoc,ename,empno,
sal,deptname);
endloop;
----------------------------插入记录类型数据---------------------------------
declare
typetest_indtabistableoftest%rowtypeindexbybinary_integer;
v_testtest_indtab;
begin
v_test
(1).num:
='2001';
v_test
(1).name:
='linqigang';
v_test
(2).num:
='2002';
v_test
(2).name:
='lin';
v_test(3).num:
='2003';
v_test(3).name:
='lingang';
foralliinv_test.first..v_test.last
insertintotestvaluesv_test(i);
同于foriinv_test.first..v_test.lastloop
Insertintotestvaluestv_test(i);
commit;
end;
注:
红色部分为for循环的另一种形式专用于插入
对DDL的操作
Begin
executeimmediate‘droptabletest’;
End;
---------------异常的处理---------------------------
declare
v_enamevarchar2(20);
begin
selectenameintov_enamefromempwhereempno=1;
dbms_output.put_line(v_ename);
exception
whenno_data_foundthen
dbms_output.put_line('no_data_found');
whentoo_many_rowsthen
dbms_output.put_line('toomany');
end;
---------------------自定义异常------------------------------------
declare
e1exception;//定义异常
v_n1number;
begin
ifv_n1isnullthen
raisee1;//抛出异常
endif;
dbms_output.put_line('aa');
exception
whene1then//捕获异常
dbms_output.put_line('null');
whenothers
dbms_output.put_line(‘others’);
end;
--------------------------------------------------------------------------
createtablechild(c1numberprimarykey,c2number,
foreignkey(c2)referencesparent(c1));
createtableparent(c1numberprimarykey,c2number);
--------------------------------------------------------------------------
declare
e_novalexception;
pragmaexception_init(e_noval,-2291);
begin
insertintochildvalues(1,2);
exception
whene_novalthen
dbms_output.put_line('nodept_id');
end;
------------------------存储过程----------------------------------------------
查看存储过程源码
descuser_source;
selecttextformuser_sourcewherename='过程名大写';
调用存储过程:
execxx或者beginp;end;
差看错误:
showerror;
createorreplaceprocedureproc1
is
begin
dbms_output.put_line(d'hao');
end;
-----------------------带参数的存储过程-------------------------------------
createorreplaceprocedureproc
(p_n1varchar2,p_n2outvarchar2,p_n3inoutvarchar2default=’10’)//default缺省值
is
v_n1varchar2(10);
begin
--p_n1:
=p_n1||'d';//注释掉
v_n1:
=p_n1;
p_n2:
=p_n2||'d';
p_n3:
=p_n3||'d';
dbms_output.put_line(v_n1);//avc
dbms_output.put_line(p_n1);//avc
dbms_output.put_line(p_n2);//d
dbms_output.put_line(p_n3);//avcd
end;
调用上述存储过程:
位置标识法
declare
v_n1varchar2(10):
='avc';
v_n2varchar2(10):
='avc';
v_n3varchar2(10):
='avc';
begin
proc(v_n1,v_n2,v_n3);//位置标识法
dbms_output.put_line(v_n1);avc
dbms_output.put_line(v_n2);d
dbms_output.put_line(v_n3);avcd
end;
总结:
in,或者无,可以传入,但不可以输出
Out,可以输出,不可以传入
-----------------------函数创建-------------------------
createorreplacefunctionfun1(p_idnumber)
returnnumber
is
v_salnumber(11,2);
begin
selectsalintov_salfromemp
whereempno=p_id;
returnv_sal;
end;
----------------函数调用-----------------------
Selectfun1(7902)fromdual;
或者:
Begin
Dbms_output.put_line(func1(7902));end;
-----------包Package------------------------------------------------
//包头
createorreplacepackagepkg1
is
typet_recisrecord(
c1number,
c2number);
v_rect_rec;
procedureproc1;
functionfun1returnnumber;
end;
//包体
createorreplacepackagebodypkg1
is
procedureproc1
is
begin
dbms_output.put_line
(1);
endproc1;
functionfun1returnnumber
is
begin
return1;
endfun1;
end;
------------------触发器,trigger-----------------------------
trigger必须存储在数据库中对表的关联密切
对于过程和函数必须显示的由另一个说明块调用
trigger,是由触发事件自动激发(insert,update,delete)
注:
有foreachrow的是行级触发,
createorreplacetriggerrigger_name
before|afterdeleteorupdateonstudent(foreachrow)//行级触发器,语句级触发器
declare
begin
.....
end
createtabletest(c1number,c2number);
createorreplacetriggert1
beforeinsertontestforeachrow
declare
begin
selects1.nextvalinto:
new.c1fromdual;
end;注:
:
new和:
old只能在行级触发器应用
触发器不能写commit,rollback,savepoint
createorreplacetriggerta
afterinsertonAforeachrow
declare
begin
insertintoBvalues(:
new.userid,:
new.department);
end;
分页查询:
createorreplacepackagepage_pkg
is
typemyCursorisrefcursor;
end;
createorreplaceprocedurepage_pro(tableNamevarchar2,
currentPagenumber,pageSizenumber,totalPagesoutnumber,
totalRecordsoutnumber,pageCursoroutpage_pkg.mycursor)
is
v_startRecordnumber;
v_endRecordnumber;
v_sqlvarchar2(100);
begin
v_startRecord:
=(currentPage-1)*pagesize+1;
v_endRecord:
=currentPage*pageSize;
v_sql:
='select*from(selecte.*,rownumrnfrom'||tableName||'ewhererownum<'||v_endRecord||')wherern>='||v_startRecord;
openpageCursorforv_sql;
v_sql:
='selectcount(*)from'||tableName;
executeimmediatev_sqlintototalRecords;
ifmod(totalRecords,pageSize)=0then
totalPages:
=totalRecords/pageSize;
else
totalPages:
=totalRecords/pageSize+1;
endif;
end;
declaretypeemp_tableistableofemp.ename%typeindexbybinary_integer;test_tableemp_table;typeemp_cursorisrefcursor;my_cursoremp_cursor;v_indexnumber:
=1;beginopenmy_cursorforselectenamefromemp;loopfetchmy_cursorintotest_table(v_index);exitwhenmy_cursor%notfound;v_index:
=v_index+1;endloop;closemy_cursor;v_index:
=v_index-1;foriin1..v_indexloopdbms_output.put_line(test_table(i));exitwheni=v_index;endloop;end;
declaretypeemp_record_typeisrecord(myNoemp.empno%type,myNameemp.ename%type);my_recordemp_record_type;beginselecte
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 函数 存储 过程 教程