PLSQL learning by self.docx
《PLSQL learning by self.docx》由会员分享，可在线阅读，更多相关《PLSQL learning by self.docx（32页珍藏版）》请在冰豆网上搜索。
PLSQLlearningbyself
//author满晨晨
//time2009420上午
字符型
char
varchar2
long2000个非汉字字符1000汉字
nchar
nvarchar2
数值型
number
方案schema
用户名：
表空间（表索引视图）
lob大对象
blob4g
初始化initemp_blobc
dml
存二进制
clob4g
存字符
初始化initemp_clobc
dml{insertdeleteupdate}
使用%type定义变量
定义记录类型变量
使用&rowtype定义变量
定义一维表类型变量
定义多维表类型变量
delcare
v_empnoEMP.empno%TYPE;
v_enameEMP.ename%TYPE;
begin
selectsexintov_sex
fromt_demo;
end;
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.20.txt2009-4-279:
11:
38
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.21.txt
//author满晨晨
//time2009421上午
sql>connscott/tiger/@oracle
sql>ed0421sql
upper（）全规范为大写
lower（）全规范为小写
setserveroutputon
declare
typedeptrec//部门表isrecord声明记录类型部分字段
（
v_deptnodept.deptno%type,
v_dnamedept.dname%type
）;
deptrec1deptrec;
begin
selectdeptno,dnameintodeptrec1fromdeptwheredeptno=30;
dbms_output.put_line（deptrec1.v_dname）;
dbms_output.put_line（deptrec1.v_deptno）;
end;
createtablet_emp
as
select*fromscott.emp
where1=1全部复制过来
%rowtype
可以使变量获得字段的数据类型使用%rowtype可以使变量获得整个记录的数据类型
比较两者不同：
变量名数据表名.列名%type，变量名数据表名%rowtpye
定义名字为mytable的复合类型变量，与testtable数据表结构相同
setserveroutputon
declare
mytabletesttable%rowtype；
begin
select*intomytablefromtempuser.testtable
whererecordnumber=88;
doms_output_put.line（mytable.currentdate）;
end;
表类型变量用来表示一维或多维数组
一维表类型
type表类型istableof类型indexbybinary_integer;
表变量名表类型；//使用表类型变量
类型可以使前面的类型的定义，indexbybinary_integer子句代表以符号整数为索引，这一访问表类型变量中的数据方法就是
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.21.txt（第1／4页）2009-4-279:
11:
39
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.21.txt
‘表变量名（索引符号整数）’
sqlplusw/nogon
declare
typetabletype1istableofvarchar2（8）indexbybinary_integer;
typetabletpye2istableoftempuser.testtable.recordnumber%tpyeindexbybinary_integer;
table1tabletype1;
table2tabletype2;
begin
table1
（1）:
='大学';
table1
（2）:
='大专';
table2
（1）:
=88;
table2
（2）:
=55;
dbms_output.put_line（table1
（1）||table2
（1））;
dbms_output.put_line（table1
（2）||table2
（2））;
end;
||使连接字符串的运算符
控制结构
条件结构ifswitch
循环结构loopexitforwhile
循环结构gotonull
作业
countfirstnextlasypriror函数
<1000显示工资太低
>1000<3000显示工资中等
>3000显示工资较高
实践从客户端输入一字符
如果字符为A输出信息‘A的标准是90分以上’
如果字符为B输出信息‘B的标准是80分至90分之间’
如果字符为C输出信息‘C的标准是60之80分之间’
否则输出信息不及格
declare
v_empnoemp.empno%type:
=&empno;
v_enameemp.ename%type;
begin
selectempno,enameintov_empno,v_enamefromempwhereempno=v_empno;
dbms_output.put_line（v_ename）;
exception
whenothersthen
dbms_output.put_line（'yourempnoisnull,enameisnull'）;
end;
declare
v_gradechar
（1）:
=upper（&p_grade）;
v_resultvarchar2（20）;
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.21.txt（第2／4页）2009-4-279:
11:
39
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.21.txt
begin
v_result:
=
casev_grade
when'A'then'90分以上'
when'B'then'80分到90分之间'
when'C'then'60分到80分之间'
else'不及格'
end;
dbms_output.put_line（'grade'||v_grade||'result'||v_result）;
end;
declare
v_nameemp.ename%type;
v_salemp.sal%tpye:
=&sal;//显示输入sal的值&为输入接入点赋给v_sal
v_charvarchar2（30）;
begin
selectename,salintov_name,v_salfromempwheresal=v_sal;
ifv_sal<1000then
v_char:
='salislow';
elsifv_sal>=1000andv_sal<=3000then
v_char:
='salisnormal';
elsev_sal>3000then
v_char:
='salishigh';
elsev_char:
='lostdata';
endif;
dbms_output.put_line（'name'||v_name||'sal'||v_char）;
end;
ifthen
else
elsifthen
endif
caseselector
whenthen
whenthen
endcase;
loop
执行语句
exitwhen退出条件
endloop
forcountin1..10000loop
执行语句
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.21.txt（第3／4页）2009-4-279:
11:
39
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.21.txt
endloop；
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.21.txt（第4／4页）2009-4-279:
11:
39
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.22.txt
//author满晨晨
//time2009422上午
connscott/tiger@STUF1DB
查询表结构和表空间
SHOWUSER
DESCUSER_TABLES查询表的结构不显示内容
select查询表的内容
SELECTTABLE_NAME,TABLESPACE_NAMEFROMUSER_TABLES
table不能用orreplace只能先删除再给重新建个表
视图
view
1查询速度快只做一次编译
2调用简单
虚表不占物理内存空间
视图名称以vi开头
createviewvi_depttemp
as
selecte.empno,e.name,d.dnamefromemoejoindeptd
on
e.deptno=d.deptno;
建立了view虚表只是建立
查询view虚表只存在内存里一关机就没了
select*fromvi_deptemp查询具体内容
更新视图
updatevi_demptempsetename='张学友'whereempno='7788'
commit提交对视图有影响对实际数据也更改
deletefromvi_的皮特名片whereempno=7788
commit提交对视图有影响对实际数据也更改
复制别人的表
createtablet_emp
as
select*fromscott.empwhere1=1或者1!
=1
createtablet_dept
as
select*fromscott.dept
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.22.txt（第1／5页）2009-4-279:
11:
40
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.22.txt
update更新视图时候视图来源多个表时候无法更新必须使用触发器insteadof类型触发器
而视图只有一个单表时候可以更新
selectjob,sum（sal）astotalsalfromt_empgroupbyjobhavingjob='clerk'
视图查询时候聚合函数要给它重新命名;
where
groupby
having
orderby
groupbyhaving分组查询是将查询结果按照字段分组HAVING相当与WHERE的限制条件查询
selectempno,ename,job,sal
fromscott.emp
groupbyjob,empno,ename,sal---按字段分组
havingsal<=2000--分组后是否符合条件
where检查每条记录是否符合条件
having检查分组后的各组是否满足条件
having只能配合groupby使用
在使用join，groupby时候要使用视图结构
触发器
使特定事件出现的时候自动执行的代码块类似于存储过程但是用户不能直接调用他们
当insertemp时候记录此操作过程到emp_log中业务逻辑自动执行
功能
1允许/限制对表的修改
2自动生成派生列比如自增字段
3提供审计和日志记录
4防止无效的事务处理
5启用复杂的业务逻辑
触发器不会通知用户便改变了用户的输入值】
语法
createorreplacetriggernametri_前缀_emp表明_insert事件tri_emp_insert
after/before/insteadof（视图时候多表）执行时机insert/update/delete表之前之后替换视图什么事件
on表明视图名
declare
begin
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.22.txt（第2／5页）2009-4-279:
11:
40
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.22.txt
dml语句要commit，ddl语句
end触发器名称（也可以不写）
createtablet_emp_log
（
whovarchar2（10）notnull,
actionvarchar2（10）notnull,
actimedate
）;
createorreplacetriggertri_emp_insert
beforeinsert
ont_emp
begin
insertintot_emp_log（who,action,actime）values（user,'insert',sysdate）;
end;
raise_application_error（-20001,'你不能访问或修改这个表'）
ora-20001：
你不能访问或修改这个表
往里面插入东西如果是你这个用户的话可以操作如果不是弹出一个错误
createorreplacetriggertri_emp_insert1
beforeinsert
ont_emp
begin
ifuser!
='scott'then
raise_application_error（-20001,'你不能访问或修改这个表'）;
endif;
删除两条记录
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.22.txt（第3／5页）2009-4-279:
11:
40
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.22.txt
删除t_emp表中的数据同时把删除的数据保存到新的表a_emp中去
createtablea_emp
as
select*fromt_empwhere1!
=1;
CREATEORREPLACETRIGGERTRI_t_emp
beforedeleteONT_emp
REFERENCINGNEWASNEWROW
FOREACHROW
declare
BEGIN
INSERTINTOa_emp
VALUEE
（:
NEWROW.empno,:
NEWROW.ename,:
NEWROW.job,:
NEWROW.MGR,:
NEWROW.HIREDATE,:
NEWROW.Sal,:
NEWROW.COMM,:
NEWROW.deptno）;
ENDTRI_t_emp;
触发器；
statementtr
rowtr
end;
delete删除段
drop删除表视图触发器
行级触发器
new-insertoid无效
old-deletenew无效
dnerow
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.22.txt（第4／5页）2009-4-279:
11:
40
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.22.txt
newold-update
foreachrow循环遍历每一行
带业务逻辑的触发器
如插入T_DEMO3数据同时插入T_DEMO2，条件是工资要大于4500rmb的记录
CREATEORREPLACETRIGGERTRI_TDEMO3
AFTERINSERTONT_DEMO3
REFERENCINGNEWASNEWROW
FOREACHROW
WHEN（NEWROW.SALARY>=4500）
BEGIN
INSERTINTOT_DEMO2
VALUEE
（:
NEWROW.ID,:
NEWROW.TYPE,:
REWROW.NAME,:
NEWROW.SALARY,:
NEWROW.C_DATE）;
ENDTRI_TDEMO3;
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.22.txt（第5／5页）2009-4-279:
11:
40
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.23.txt
//author满晨晨
//time2009423上午
系级trigger
监听到系统发生事件之后自动执行的代码块所以是after！
！
！
！
deleting当向一个表执行delete动作时出发deleting事件
inserting当向一个表执行insert动作时出发inserting事件
updating当向一个表执行update动作时出发updating事件
都是返回true或者false
ifdeletingthen
elsifinsertingthen
elsifupdatingthen
elseraise_application_error
createtablet_emp_log2
（
whovarchar2（10）notnull,
actionvarchar2（10）notnull,
actimedate
）;
createorreplacetriggertri_emp2
afterinsertorupdateordelete
ont_emp
begin
ifupdatingthen
insertintot_emp_log2（who,action,actime）values（user,'update',sysdate）;
elsifdeletingthen
insertintot_emp_log2（who,action,actime）values（user,'delete',sysdate）;
elsifinsertingthen
insertintot_emp_log2（who,action,actime）values（user,'insert',sysdate）;
endif;
end;
对操作行为进行表述时候不涉及数据正常写
假如对操作行为更改的数据进行数据操作时候referencingnewasnewrow
foreachrow
行级触发器修改的是行的所有列的数据的时候referencingnewasnewrow
foreachrow
列级触发器修改的是某个列的数据的时候
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.23.txt（第1／3页）2009-4-279:
11:
40
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.23.txt
afterinsertorupdateofename
ont_emp
referencingnewasnewrow
foreachrow
createorreplacetriggertri_emp2
afterinsertorupdate
ont_emp
begin
ifupdatingthen
insertintot_emp_log2（who,action,actime）values（user,'update',sysdate）;
elsifdeletingthen
insertintot_emp_log2（who,action,actime）values（user,'delete',sysdate）;
elsifinsertingthen
insertintot_emp_log2（who,action,actime）values（user,'insert',sysdate）;
endif;
end;
更新的数据保存到新表中用newrow
把删除的数据保存到列触发器只能针对update因为insert插入数据的时候肯定是更改了行的所有列
createorreplacetriggertri_emp4
afterupdateofename
ont_emp
referencingnewasnewrow
foreachrow
begin
ifupdatingthen
insertintot_emp_log2（who,action,actime）values（user,'update',sysdate）;
INSERTINTOtemp_new（ename,empno）
VALUEE（:
newrow.ename,:
newrow.empno）;
elsifinsertingthen
insertintot_emp_log2（who,action,actime）values（user,'insert',sysdate）;
INSERTINTOtemp_new（ename,empno）
VALUEE（:
newrow.ename,:
newrow.empno）;
endif;
end;
行级触发器new保存更改后的数据old保存删除之前的数据
updatedeleteinsert
列级触发器
只能update更改某列
视图如果是多表
的话
不能用update更新
触发器instead只能用于更新视图时候用
file:
///E|/满晨晨的文档/培训/ORACLE/plsql/课堂记录/SQL课堂记录2009.4.23.txt（第2／3页）2009-4-279:
11:
40
file:
///E|/满晨晨的文档/培训/ORACLE/
升级会员 