plsql Standard.docx
- 文档编号:25346858
- 上传时间:2023-06-07
- 格式:DOCX
- 页数:39
- 大小:12.55MB
plsql Standard.docx
《plsql Standard.docx》由会员分享,可在线阅读,更多相关《plsql Standard.docx(39页珍藏版)》请在冰豆网上搜索。
plsqlStandard
plsql
为什么要学习pl/sql的必要性?
可以提高程序的运行性能.
模块化得设计思想.
减少网络传数量。
提高安全性。
什么是plsql?
Pl/sql(procedurallanguage/sql)
是oracle标准的sql语言上的扩展,plsql不仅嵌入sql语言,还可以定义变量和常量,允许使用条件语句和循环语句,允许使用例外处理各种错误,这样使得它的功能变得更加强大.
plsql有那些好处?
过程,函数、触发器的编写
过程、函数、触发器是在oracle中
plsql是非常强大的数据库过程语言
过程,函数可以在java程序中调用.
传统的操作数据库的方法.
像这样sql语句就很慢?
像这样我们把要调用存储过程,那么我们就很方便了.不用再编译。
Plsql的缺点:
移植性不好,写好的东东不能在动了.
创建存储过程
给用户解锁
Alertuserscottaccountunlock;
Useraltered;
修改用户密码
Alteruserscottidentifiedorclpwd;
Oracle查询用户所有表的语句(2011-05-2208:
50:
14)
select*fromall_tab_comments
--查询所有用户的表,视图等
all_tab_comments最常用的字段是
owner该表的拥有者;scott用户.和sys用户....
Table_type该表的类型table和view
Table_name该表的名字
select*fromall_col_comments
--查询所有用户的表的列名和注释.
select*fromall_col_commentswhereownerin('SCOTT')ANDtable_namein('EMP');
--查询属于scott用户是table的所有表
select*fromuser_col_comments
--查询本用户的表的列名和注释
user_col_comments最常用的字段是
TABLE_NAME该字段所属的表名;
COLUMN_NAME该字段所属的列名;
COMMENTS注释
Select*fromuser_tab_commentswhereuser_tab_commentsin('EMP');
或者
descemp;
select*fromall_tab_columns
--查询所有用户的表的列名等信息(详细但是没有备注).
select*fromuser_tab_columns
--查询本用户的表的列名等信息(详细但是没有备注).
--一般使用1:
selectt.table_name,mentsfromuser_tab_commentst
--一般使用2:
selectr1,r2,r3,r5
from(selecta.table_namer1,a.column_namer2,mentsr3
fromuser_col_commentsa),
(selectt.table_namer4,mentsr5fromuser_tab_commentst)
wherer4=r1;
--授权
grantcreateviewtoscott;
创建一张表
createtablemytab(
namevarchar2(30),
passwordvarchar2(30)
);
创建一个存储过程
createproceduresp_pro1is
Begin
Insertintomytab('hi','p1');
End;
如果要是存在这个过程,那么可以用覆盖的方式
Replace:
如果有就替换;
createorreplaceproceduresp_prois
begin
Insertintomytab('hi','p1');
end;
创建中出错.那么如何才能查看错误呢?
Showerror;
createorreplaceproceduresp_prois
Begin
Insertintomytab('own','p1');
End;
过程我们已经创建,那么如何才能执行过程呢?
这里我们介绍两种方式?
1.execsp_pro
2.callsp_pro
--删除用户
createorreplaceproceduresp_pro_delis
begin
deletefrommytabastwheret.name='我';
end;
--删除过程
dropproceduresp_pro;
Pl/sql基础-plsql块
块(block)是plsql的基本程序单元,编写plsql程序实际上就是编写plsql块.要完成相对简单的功能,可能就要编写plsql块,但是如果要完成相对复杂的功能,可能就需要在一个plsql块中嵌套其它的plsql块.
--创建简单的快
--setserveroutputon;打开输出
--setserveroutputoff;关闭输出
--dbms_output是一个包,该包下有些过程.
setserveroutputon;
begin
dbms_output.put_line('hello,world');
end;
--定义和执行部分
--第一种块
declare
v_enamevarchar2(5);
begin
selectenameintov_enamefromempwhereempno=&aa;
setserveroutputon;
dbms_output.put_line('用户名是:
'||v_ename);
end;
--第二种块
declare
v_enamevarchar2(20);
v_salvarchar2(20);
begin
selectename,salintov_ename,v_salfromempwhereempno=&a;
dbms_output.put_line('用户是:
'||v_ename||'薪水是:
'||v_sal);
end;
--第三种块
declare
v_enamevarchar2(20);
v_salvarchar2(20);
begin
selectename,salintov_ename,v_salfromempwhereempno=&input;
exception
whenno_data_foundthen
dbms_output.put_line('编号不存在,请从新输');
end;
--下面是plsql的核心内容
1.过程(存储过程)
实例
createproceduresp_spo(spNamevarchar2,newSqlnumber)is
begin
updateempsetsal=newSqlwhereename=spName;
end;
ww
2.函数
--查询年薪(输入雇员的名称,返回雇员的年薪)
createorreplacefunctionsp_fun_t1(spEnamevarchar2)returnnumberis
yearSalnumber(7,2);
begin
selectsal*12+nvl(comm,0)intoyearSalfromempwhereename=spEname;
returnyearSal;
end;
--plsql调用
函数调用
vart1number;
callsp_fun_t1('SCOTT')into:
t1;
3.触发器
1.变量
a)标准类型(scalar)
setserveroutputon;
declare
c_tax_ratenumber(3,2):
=0.03;
v_enamevarchar2(10);
v_salnumber(7,2);
v_tax_salnumber(7,2);
begin
selectename,salintov_ename,v_salfromempwhere
empno=&a;
v_tax_sal:
=v_sal*c_tax_rate;
dbms_output.put_line('姓名是:
'||v_ename||'工资是:
'||v_sal||'税率是'||v_tax_sal);
end;
--在上面的程序中有一个漏洞?
是什么呢?
那就是标量v_enamevarchar2(10)只有10个,那要是超出了怎么办?
v_enamevarchat2(10);
变成
v_enameemp.ename%type;--这里的意思是:
定义一个标量v_ename,v_ename的类型和emp.ename中的类型一致;
declare
c_tax_ratenumber(3,2):
=0.03;
v_enameemp.ename%type;
v_salemp.sal&type;
v_tax_salnumber(7,2);
Begin
Selectename,salintov_ename,v_salfromemp
Whereempnoin(&a);
v_tax_sal:
=v_sal*c_tax_rate;
dbms_output.put_line('姓名是:
'||v_ename||'工资是:
'||v_sal||'税率是:
'||v_tax_sal);
End;
如果我要存多个同样的数据,要怎么了办呢?
那我们就要使用composite.
b)复合类型(composite)
A.pl/sql记录(record)
Declare
--定义一个类型emp_record,该类型包含三个数据ename,slary,job
typeemp_record_typeisrecord(
enameemp.ename%type,
salaryemp.sal%type,
jobemp.job%typ
);
sp_recordemp_record_type;
Begin
Selectename,sal,jobintosp_recordfromemp
Whereempnoin(%a);
dbms_output.put_line('员工是:
'||sp_record.ename||'薪水是'||sp_record.salary);
End;
B.Plsql表(table)类型;
Declare
--定义一个表类型sp_table_type,该类型是用于存放emp.ename%type
--indexbybinary_integer表示下标是整数.
typesp_table_typeistableofemp.ename%typeindexbybinary_integer;
sp_tablesp_table_type;
Begin
selectenameintosp_table(0)fromempwhereempnoin(&b);
Dbms_output.put_line('员工姓名:
'||sp_table(0));
End;
如果没有whenreempnoin(&a),就会出现返回和接受的行数不符合。
那么要怎么办呢?
用参照变量;见c:
参照类型(reference)
c)参照类型(reference)
i.游标变量(refcursor)more...
1.==
declare
typesp_emp_cursorisrefcursor;
emp_cursorsp_emp_cursor;
v_enameemp.ename%type;
v_salemp.sal%type;
v_jobemp.job%type;
begin
--把emp_cursor和一个select结合;
Openemp_cursorforselectename,sal,jobfromempwheredeptnoin(&deptno);
Loop
Fetchemp_cursorintov_ename,v_sal,v_job;
--判断是否emp_cursor为空.
Exitwheremp_cursor%notfound;
dbms_output.put_line('姓名:
'||v_ename||'薪水'||v_sal||'岗位'||v_job);
Endloop;
End;
2.b
ii.对象类型变量(refobj_type)
1.R
2.rr
iii.--
d)Lob(largeobject)
e)==
2.==
4.包
--创建一个包
--包的名字是sp_package_p1
--声明该包有一个过sp_pro_upd
--声明该包有一个函数sp_fun_t1
createpackagesp_pak_p1is
proceduresp_pro_upd2(spEnamevarchar2,spSalnumber);
functionsp_fun_sel2(spEnamevarchar2)returnnumber;
end;
--创建一个包,但是还没有实现改包的实现类.
createorreplacepackagebodysp_pak_p1is
proceduresp_pro_upd2(SpEnamevarchar2,spSalnumber)is
begin
updateEMPsetsal=spSalwhereename=spEname;
end;
functionsp_fun_sel2(spEnamevarchar2)returnnumberis
yearSalnumber(7,2);
begin
selectsal*12+nvl(comm,0)intoyearSalfromEMPwhereename=spEname;
returnyearSal;
end;
end;
--执行
callsp_pak_cre1.sp_pro_upd2('SCOTT',880);
二.控制结构
--goto不建议使用
a)条件语句
i.If..then
createorreplaceproceduresp_pro_upd2(spEnamevarchar)is
v_enameemp.enaem%type;
v_salemp.sal%type;
begin
Selectename,salintov_ename,v_salfromempwhereenamein(spEname);
ifv_sal<2000then
updateempsetsal=sal*1.1fromempwhereenamein(spEname);
endif;
End;
CALLsp_pro_upd2('WARD');
ii.If...then..else
createorreplaceproceduresp_pro_upd3(spEnamevarchar2)is
v_enameemp.ename%type;
v_commm%type;
begin
selectename,commintov_ename,v_commfromempwhereenamein(spEname);
ifv_comm<>0then
updateempsetcomm=comm+100whereenamein(spEname);
else
updateempsetcomm=200whereenamein(spEname);
endif;
end;
callsp_pro_upd3('ALLEN');
callsp_pro_upd3('SCOTT');
iii.If...then...elsif..elsif..else
createorreplaceproceduresp_pro_upd4(spEmpnonumber)is
v_enameemp.ename%type;
v_empnoemp.empno%type;
v_jobemp.job%type;
begin
selectename,empno,jobintov_ename,v_empno,v_jobfromempwhereempnoin(spEmpno);
ifv_job='PREDIENT'then
updateempsetsal=sal+1000whereempnoin(spEmpno);
elsifv_job='MANAGER'then
updateempsetsal=sal+500whereempnoin(spEmpno);
else
updateempsetsal=sal+200whereempnoin(spEmpno);
endif;
end;
callsp_pro_upd4(7788);
callsp_pro_upd4(7698);
iv....
b)循环结构
--创建一个临时表
Createtableusers(
IDnumber,
USERNAMEvarchar2(20),
PASSWORDvarchar2(30)
);
i.循环语句loop
Createorreplaceproceduresp_pro_ins(spUnamevarchar2)is
v_idnumber:
=1;
Begin
Loop
Insertintousersvalues(v_id,spUname,'1');
exitwhenv>=10;
v_id:
=v_id+1;
Endloop;
End;
Sql窗口
Callsp_pro_ins('chi');
Select*fromusers;
Commit;
ii.While循环结构
Createorreplaceproceduresp_pro_ins2(spUnamevarchar2)is
v_idnumber:
=11;
Begin
Whilev_id<=20loop
Insertintousersvalues(v_id,spUname,'1');
v_id:
=v_id+1;
Endloop;
End;
Sql窗口
Callsp_pro_ins('chi');
Select*fromusers;
Commit;
iii.For循环结构(不建议使用)
iv....
c)顺序控制结构
i.
d)...
e)
3.分页
CREATETABLEbooks(
idnumber,
nameevarchar2(50),
publishervarchar2(50)
);
--in表示输入;
--out表示输出;
createorreplaceproceduresp_pro_ins(spIdinnumber,
spNameinvarchar2,
spPublisherinvarchar2)is
begin
insertintoBOOKSVALUES(spId,spName,spPublisher);
End;
--在java中调用.
end
--in表示输入;
--out表示输出;
有输入和输出的的存储过程
createorreplaceproceduresp_pro_sel(spIdinnumber,spNameoutvarcahr2)is
begin
SelectnameeintospNamefrombookswhereiddin(spId);
End;
--在java中调用.
packagecom.oracle.plsql.procedures;
importjava.sql.CallableStatement;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.SQLException;
publicclassValPro{
publicstaticvoidmain(String[]args){
Connectionconn=null;
CallableStatementcallStat=null;
Stringurl="jdbc:
oracle:
thin:
@127.0.0.1:
1521:
ORCL";
intval=12;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection(url,"scott","orcl86ORCL");
callStat=conn.prepareCall("{callsp_pro_sel(?
?
)}");
//?
?
?
?
callStat.setInt(1,val);
callStat.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
callStat.execute();
//?
?
?
?
?
?
?
?
?
?
Stringname=callStat.getString
(2);//2?
?
?
?
?
?
?
?
;
System.out.println(val+"?
?
?
:
"+name);
}catch(ClassNotFoundExcep
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- plsql Standard