javaoracleplsql笔记尚学堂马士兵老师.docx
《javaoracleplsql笔记尚学堂马士兵老师.docx》由会员分享，可在线阅读，更多相关《javaoracleplsql笔记尚学堂马士兵老师.docx（47页珍藏版）》请在冰豆网上搜索。
javaoracleplsql笔记尚学堂马士兵老师
java-oracle,plsql笔记-【尚学堂马士兵老师】
PL/SQL
declare
v_namevarchar2（20）;
begin
v_name:
='myname';
dbms_output.put_line（v_name）;
end;
捕获异常
declare
v_numnumber:
=0;
begin
v_num:
=2/v_num;
dbms_output.put_line（v_num）;
exception
whenothersthen
dbms_output.put_line（'error'）;
end;
变量声明
declare
v_tempnumber
（1）;
v_countbinary_integer:
=0;
v_salnumber（7,2）:
=4000.00;
v_datedate:
=sysdate;
v_piconstantnumber（3,2）:
=3.14;
v_validboolean:
=false;
v_namevarchar2（20）notnull:
='Myname';
begin
dbms_output.put_line（'v_tempvalue:
'||v_temp）;
end;
--变量声明的规则//PL_SQL中两个横线可以注释掉一行
1、变量声明不能够使用保留字，如from、select等
2、第一个字符必须是字母
3、变量名最多包含30个字符
4、不要与数据库的表或者列同名
5、每一行只能声明一个变量
--常用变量类型
1、binary_integer:
整数，主要用来计数而不是用来表示字段类型，数组下标
2、number：
数字类型
3、char：
定长字符串
4、varchar2：
变长字符串
5、date：
日期
6、long：
长字符串，最长2G
7、boolean：
布尔类型，可以取值为true、false和null值，不给初值是null
--变量声明，使用%type属性
declare
v_empnonumber（4）;
v_empno2emp.empno%type;
--变量v_empno2的类型参考emp.empno类型
v_empno3v_empno2%type;
begin
dbms_output.put_line（'Test'）;
end;
--Table变量类型（数组）
declare
typetable_emp_empnoistableofemp.empno%typeindexbybinary_integer;
v_empnostype_table_emp_empno;
begin
v_empnos（0）:
=7369;
v_empnos
（2）:
=7888;
v_empnos（-1）:
=7323;
dbms_output.put_line（v_empnos（-1））;
end;
--Record变量类型（类似于类）
declare
typetype_record_deptisrecord
（
deptnodept.deptno%type,
dnamedept.dname%type,
locdept.loc%type
）;
v_temptype_record_dept;
begin
v_temp.deptno:
=50；
v_temp.dname:
='aaa';
v_temp.loc:
='bj';
dbms_output.put_line（v_temp.deptno||''||v_temp.dname）;
end;
--使用rowtype声明record变量（无论一个表怎么变，不关心，只关心这个表的表名，类似于java中的声明类的实例）
declare
v_tempdept%rowtype;
begin
v_temp.deptno:
=50;
v_temp.dname:
='aaa';
v_temp.loc:
='bj';
dbms_output.put_line（v_temp.deptno||''||v_temp.dname）;
end;
--SQL语句的运用
--PL/SQL用select语句必须并且只能返回一条记录
declare
v_enameemp.ename%type;
v_salemp.sal%type;
begin
selectename,salintov_ename,v_salfromempwhereempno=7369;
dbms_output.put_line（v_ename||''||v_sal）;
end;
--UPDATE
declare
v_deptnoemp2.deptno%type:
=50;
v_countnumber;
begin
updateemp2setsal=sal/2wheredeptno=v_deptno;
--selectdeptnointov_deptnofromemp2whereempno=7369;
--selectcount（*）intov_countfromemp2;
--几条记录被影响，就是产生几个值
dbms_output.put_line（sql%rowcount||'条记录被影响'）;
--commit必须提交
commit;
end;
--if语句
--取出7369的薪水，如果<1200，则输出'low',r如果<2000则输出'middle',否则输出'high'
declare
v_salemp.sal%type;
begin
selectsalintov_salfromemp
whereempno=7369;
if（v_sal<1200）then
dbms_output.put_line（'low'）;
elsif（v_sal<2000）then
dbms_output.put_line（'middle'）;
else
dbms_output.put_line（'high'）;
endif;
end;
--循环
declare
ibinary_integer:
=0;
begin
loop
dbms_output.put_line（i）;
i:
=i+1;
exitwhen（i>=11）;
endloop;
end;
declare
ibinary_integer:
=1;
begin
whilei<11loop
dbms_output.put_line（i）;
i:
=i+1;
endloop;
end;
declare
ibinary_integer:
=1;
begin
foriin1..10loop
dbms_output.put_line（i）;
endloop;
end;
--错误处理
declare
v_tempnumber（4）;
begin
selectempnointov_tempfromempwheredeptno=10;
exception
whentoo_many_rowsthen
dbms_output.put_line（'太多记录了'）;
whenothersthen
dbms_output.put_line（'error'）;
end;
--把错误信息保存到一个表中
--创建表
createtableerrorlog
（
intnumberprimarykey,
errorcodenumber,
errormsgvarchar2（1024）,
errordatedate
）
--创建索引
createsequenceseq_errorlog_idstartwith1incrementby1;
--例子
declare
v_deptnoemp.deptno%type:
=10;
v_errorcodenumber;
v_errormsgvarchar2（1024）;
begin
deletefromdeptwheredeptno=v_deptno;
commit;
exception
whenothersthen
rollback;
v_errorcode:
=SQLCODE;
v_errormsg:
=SQLERRM;
insertintoerrorlogvalues（seq_errorlog_id.nextval,v_errorcode,v_errormsg,sysdate）;
commit;
end;
--当不显示打印结过的时候执行
setserveroutputon;
--PL/SQL重点
--游标
declare
cursorcis
select*fromemp;
v_empc%rowtype;
begin
openc;
fetchcintov_emp;
dbms_output.put_line（v_emp.ename）;
closec;
end;
--游标初始的时指到第一条记录，然后一条一条的往下走
--loop
declare
cursorcis
select*fromemp;
v_empc%rowtype;
begin
openc;
loop
--运行完fetch自动往下走一条
fetchcintov_emp;
exitwhen（c%notfound）;
dbms_output.put_line（v_emp.ename）;
endloop;
closec;
end;
--while循环
declare
cursorcis
select*fromemp;
v_empc%rowtype;
begin
openc;
fetchcintov_emp;
while（c%found）loop
dbms_output.put_line（v_emp.ename）;
fetchcintov_emp;
endloop;
closec;
end;
--for循环
declare
cursorcis
select*fromemp;
v_empc%rowtype;
begin
forv_tempincloop
dbms_output.put_line（v_emp.ename）;
endloop;
end;
--带参数的游标
declare
cursorc（v_deptnoemp.deptno%type,v_jobemp.job%type）
is
selectename,salfromempwheredeptno=v_deptnoandjob=v_job;
begin
forv_tempinc（30,'CLERK'）loop
dbms_output.put_line（v_temp.ename）;
endloop;
end;
--可更新的游标
declare
cursorc
is
select*fromemp2forupdate;
begin
forv_tempincloop
if（v_temp.sal<2000）then
updateemp2setsal=sal*2wherecurrentofc;
elsif（v_temp.sal=5000）then
deletefromemp2wherecurrentofc;
endif;
endloop;
commit;
end;
--存储过程
createorreplaceprocedurep
is
cursorc
is
select*fromemp2forupdate;
begin
forv_tempincloop
if（v_temp.deptno=10）then
updateemp2setsal=sal+10wherecurrentofc;
elsif（v_temp.sal=20）then
updateemp2setsal=sal+20wherecurrentofc;
else
updateemp2setsal=sal+50wherecurrentofc;
endif;
endloop;
commit;
end;
--带参数的存储过程
--in（默认）传入参数，out传出参数，inout传入传出参数
createorreplaceprocedurep
（v_ainnumber,v_bnumber,v_retoutnumber,v_tempinoutnumber）
is
begin
if（v_a>v_b）then
v_ret:
=-v_a;
else
v_ret:
=-v_b;
endif;
v_temp:
=v_temp+1;
end;
--调用过程
declare
v_anumber:
=3;
v_bnumber:
=4;
v_retnumber;
v_tempnumber:
=5;
begin
p（v_a,v_b,v_ret,v_temp）;
dbms_output.put_line（v_ret）;
dbms_output.put_line（v_temp）;
end;
--显示错误
showerror
--函数
createorreplacefunctionsal_tax
（v_salnumber）
returnnumber
is
begin
if（v_sal<2000）then
return0.10;
elsif（v_sal<2750）then
return0.15;
else
return0.20;
endif;
end;
--触发器（概念牢牢掌握）
createtableemp2_log
（
unamevarchar2（20）,
actionvarchar2（10）,
atimedate
）;
--after/before
createorreplacetriggertrig
--foreachrow
afterinsertordeleteorupdateonemp2foreachrow
begin
ifinsertingthen
insertintoemp2_logvalues（USER,'insert',sysdate）;
elsifupdatingthen
insertintoemp2_logvalues（USER,'update',sysdate）;
elsifdeletingthen
insertintoemp2_logvalues（USER,'delete',sysdate）;
endif;
end;
updateemp2setsal=sal*2wheredeptno=10;
--更新有参考列
createorreplacetriggertrig
afterupdateondept
foreachrow
begin
updateempsetdeptno=:
NEW.deptnowheredeptno=:
OLD.deptno;
end;
--递归
--数状结构的存储与展示
createtablearticle
（
idnumberprimarykey,
contvarchar2（4000）,
pidnumber,
isleafnumber
（1）,--0代表非叶子节点，1代表叶子节点
alevelnumber
（2）
）;
insertintoarticlevalues（1,'蚂蚁战大象',1,0,0）;
insertintoarticlevalues（2,'蚂蚁战大象2',1,0,1）;
insertintoarticlevalues（3,'蚂蚁战大象3',2,1,2）;
commit;
createorreplaceprocedurep（v_pidarticle.pid%type,v_levelbinary_integer）is
cursorcisselect*fromarticlewherepid=v_pid;
v_preStrvarchar2（1024）:
='';
begin
foriin1..v_levelloop
v_preStr:
=v_preStr||'***';
endloop;
forv_articleincloop
dbms_output.put_line（v_preStr||v_article.cont）;
if（v_article.isleaf=0）then
p（v_article.pid,v_level+1）;
endif;
endloop;
end;
第一课：
客户端
1.SqlPlus（客户端），命令行直接输入：
sqlplus，然后按提示输入用户名，密码。
2.从开始程序运行:
sqlplus，是图形版的sqlplus.
3.http:
//localhost:
5560/isqlplus
Toad：
管理，PlSqlDeveloper:
第二课：
更改用户
1.sqlplussys/bjsxtassysdba
2.alteruserscottaccountunlock;（解锁）
第三课：
tablestructure
1.描述某一张表：
desc表名
2.select*from表名
第四课：
select语句：
1.计算数据可以用空表：
比如：
.select2*3fromdual
2.selectename,sal*12annual_salfromemp;与selectename,sal*12"annualsal"fromemp;区别，加双引号保持原大小写。
不加全变大写。
3.selectename||"abcd"如果连接字符串中含有单引号，用两个单引号代替一个单引号。
第五课：
distinct
selectdeptnofromemp;
selectdistinctdeptnofromemp;
selectdistinctdeptnofromemp;
selectdistinctdeptno,jobfromemp
去掉deptno,job两者组合的重复。
更多的项，就是这么多项的组合的不重复组合。
第六课：
Where
select*fromempwheredeptno=10;
select*fromempwheredeptno<>10;不等于10
select*fromempwhereename='bike';
selectename,salfromempwheresalbetween800and1500（>=800and<=1500）
空值处理:
selectename,sal,commfromempwherecommis（not）null;
selectename,sal,commfromempwhereename（not）in（'smith','king','abc'）;
selectenamefromempwhereenamelike'_A%';_代表一个字母,%代表0个或多个字母.如果查询%
可用转义字符.\%.还可以用escape'$'比如:
selectenamefromempwhereenamelike'%$a%'escape'$';
第七课:
orderby
select*fromdept;
select*fromdeptorderbydeptdesc;（默认:
asc）
selectename,sal,deptnofromemporderbydeptnoasc,enamedesc;
第八课:
sqlfunction1:
selectename,sal*12annual_salfromemp
whereenamenotlike'_A%'andsal>800
orderbysaldesc;
selectlower（ename）fromemp;
selectenamefromemp
wherelower（ename）like'_a%';等同于
selectenamefromempwhereenamelike'_a%'orenamelike'_A%';
selectsubstr（ename,2,3）fromemp;从第二字符截,一共截三个字符.
selectchr（65）fromdual结果为:
A
selectascii（'a'）fromdual结果为:
65
selectround（23.652,1）fromdual;结果为:
23.7
selectround（23.652,-1）fromdual;20
selectto_char（sal,'$99_999_999'）fromemp;
selectto_char（sal,'L99_999_999'）fromemp;人民币符号,L:
代表本地符号
这个需要掌握牢:
selectbirthdatefromemp;
显示为:
BIRTHDATE
----------------
17-12月-80
----------------
改为:
selectto_char（birthdate,'YYYY-MM-DDHH:
MI:
SS'）fromemp;
升级会员 