尚学堂马士兵老师oracleplsql笔记.docx
《尚学堂马士兵老师oracleplsql笔记.docx》由会员分享,可在线阅读,更多相关《尚学堂马士兵老师oracleplsql笔记.docx(47页珍藏版)》请在冰豆网上搜索。
尚学堂马士兵老师oracleplsql笔记
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;
显示:
BIRTHDATE
----------------