Oracle PL/SQL 培训 - 赵磊的博客 - ITeye技术网站
博客分类：
(procedural language/sql) 过程化语言
可以编写 存储过程，函数，包，触发器 等等
-------------------------------------运算符 部分----------------------------------------------
-------------------------------------定义与使用变量 部分----------------------------------------------
1、标量类型 scalar
2、复合类型 composite
3、参照类型 reference
4、lob(large object)
1、标量类型 scalar
1.1、标量类型 scalar - 常用类型
变量名 [constant] datatype [not null] [:=default | expr]
变量名:就是变量名
constant:说这是常量，需要初始值，且值以后不能改变,可选项
datatype:数据类型
not null : 指定变量不能为空，可选项
给变量或是常量指定初始值，可选项
default 用于指定初始值 ，可选项
expr：指定初始值的pl/sql表达式，可是文本值、其它变量、函数等
1、定义一个变长字符串
v_name varchar2(10)
2、定义一个小数 范围-9.99
v_sal number(6,2)
3、定义一个小数并给个初始值5.4
v_sal2 number(6,2):=5.4
4、定义一个日期类型的变量
5、定义一个布尔变量，不能为空，初始值为false
v_bl boolean no
1.2、标量类型 scalar - 使用%type类型
指定变量的类型与某个字段的类型相同
变量名 表名.字段名%type
v_name employees.last_name%type
声明变量 v_name的类型是是
employees表的last_name字段的类型。
2、复合类型 composite
2.1 复合类型-PL/SQL记录类型
--定义一个pl/sql记录类型emp_record_type
type emp_record_type is record(
name employees.last_name%type,
salary employees.salary%type,
hire_date employees.hire_date%type
--定义一个emp_record_type类型的变量v_record
v_record emp_record_
select last_name,salary,hire_date into v_record
from employees where employee_id=206;
dbms_output.put_line('姓名：'||v_record.name
||' 工资：'||v_record.salary
||' 入职日期：'||v_record.hire_date
姓名：Gietz 工资：5555 入职日期：07-6月 -94PL/SQL procedure successfully completed
2.2 复合类型-PL/SQL表
相当于高级语言中的数组，下标可以为负数
--定义一个pl/sql表类型table_type，名称随意取
--table_type类型的变量用于存放employees.last_name%type类型的数据
type table_type is table of employees.last_name%type
--数组的下标是整数，可是为负
index by binary_
--声明一个table_type类型的变量v_table
v_table table_
select last_name into v_table(0) from employees
--如果返回多行，会报错，解决方法见后面的参照类型变量
where employee_id=206;
dbms_output.put_line('姓名：'||v_table(0));
2.3 复合类型-嵌套表
用的少，略...
2.4 复合类型-变长数组 用的少，略...
3、参照类型 reference
参照变量是用于存放数值指针的变量，可以使程序共享相同的对象，最常使用的参照变量类型之一就是 游标变量（ref cursor）
3.1、参照类型 reference - cursor游标变量
使用游标时，定义游标时不需要指定相应的select语句，但是当使用游标时（open时），需要指定select语句，这样游标就与一个select语句结合了。
--编写一个pl/sql块，输入部门编号，显示本部门所有员工的姓名与工资
--定义一个游标类型test_cursor_type，它是类型。
type test_cursor_
--定义一个游标变量
test_cursor test_cursor_
--定义一般的变量
v_name employees.last_name%
employees.salary%
--把test_cursor游标变量与一个select语句结合起来
--&no 要输入值给no变量
open test_cursor for select last_name,salary
from employees where department_id=&
--循环取出
fetch test_cursor into v_name,v_
--判断test_corsor是否为空,为空时退出循环
exit when test_cursor%
dbms_output.put_line('姓名：'||v_name||' 工资：'||v_sal);
--关闭游标
close test_
------------------程序的三大结构：判断结构、顺序结构、循环结构 ---------------------------
判断结构：
if -- 条件 -- then -- 分支1 -- end if
if -- 条件 -- then -- 分支1 -- else -- 分支2 -- end if
if -- 条件1 -- then -- 分支1 -- elsif -- 条件2 -- then -- 分支2 --else -- 分支3 -- end if
loop -- 被循环部分 -- exit when 终止循环的条件; --
至少执行一次
while -- 条件 -- loop -- 被循环部分 -- end loop
满足条件才执行
for i in reverse 1..10 loop
被循环部分
goto meta_ 跳到meta_name处
&&meta_name&&
语句 ， 什么也不做
-------------------------------------存储过程部分-----------------------------------------------
创建一个过程:
create or replace procedure pro1 is
--单行注释
/*多行注释*/
insert into t7 values(2,'apple');
查看错误信息：
调用过程：
exec 过程名 (参数值1，参数值2...);
过程名 (参数值1，参数值2...);
删除过程：
drop procedure 过程名;
块的结构说明：
/*定义部分--常量，变量，游标，复杂数据类型*/
/*执行部分--要执行的PL/SQL语句和SQL语句*/
/*异常处理部分*/
打开输出选项
set serveroutput on
dbms_output.put_line('hello ,world');
输出：hello ,world
要打开输出选项才能看到。
在过程中定义一个变量：
声明部分略，直接写了执行部分
v_ename varchar2(25);--定义字符串变量
v_sal number(7,2);
--定义一个数值变量
--从输入框输入变量no,用查询的结果为变量赋值,
--只适合查出一条结果的情况,查不到结果或查到多条结果都会报错
select last_name,salary into v_ename,v_sal
from employees where employee_id=&
dbms_output.put_line('雇员名：'||v_ename||' 薪水：'||v_sal);
输入：206输出：雇员名：Gietz 薪水：8300
输入一个不存在编号，结果：
ORA-01403: 未找到数据ORA-06512: 在 line 7
在过程中处理异常：
声明部分略，直接写了执行部分
v_ename varchar2(25);--定义字符串变量
v_sal number(7,2);
--定义一个数值变量
--从输入框输入变量no，查询的结果为变量赋值，
--只适合查出一条结果，若查不到执行时会报no_data_found
select last_name,salary into v_ename,v_sal
from employees where employee_id=&
dbms_output.put_line('雇员名：'||v_ename||' 薪水：'||v_sal);
when no_data_found then
dbms_output.put_line('无查询结果！');
输入一个不存在的编号，结果：无查询结果！
程序走过了异常处理部分代码
创建一个带参数的过程：（无返回）
create procedure pro2 (vname varchar2,newSal number) is
--根据用户名去修改工资
update employees set salary=newSal where last_name=
Procedure created
SQL& exec pro2('Gietz',5555);
--调用pro2过程，并传入两个参数
PL/SQL procedure successfully completed
在java程序中调用上面的过程：
//1 加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2 得到连接
Connettion cn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:库名称","用户名","密码");
//3 创建CallableStatement
CallableStatement cs=cn.prepareCall("{call pro2(?,?)}");
//4 给问号赋值
cs.setString(1,"Gietz");
cs.setInt(2,5555);
cs.execute();
cs.close();
cn.close();
编写一个有返回值的过程(单行记录)：
--有输入与输出的存储过程
--in 表示这是输入变量,如果不写默认是in
--out表示这是输出变量
create or replace procedure pro8
(v_no in number,v_name out varchar2) is
--通过编号找到姓名
select last_name into v_name
from employees where employee_id=v_
在java程序中调用上面的过程：
//1 加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2 得到连接
Connettion cn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:库名称","用户名","密码");
//3 创建CallableStatement
CallableStatement cs=cn.prepareCall("{call pro8(?,?)}");
//4 给问号赋值
cs.setInt(1,206);
//5 说明这是一个返回值,是varchar类型。
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();
//7 取出返回值
String name=cs.getString(2);
cs.close();
cn.close();
编写一个有返回值的过程(返回结果集)：
--创建一个包
create or replace package test_package as
type test_
--创建一个过程
create or replace procedure pro9(
departID in number,
--定义一个游标类型的返回变量
p_cursor out test_package.test_cursor)
--把select语句与游标关联起来
open p_cursor for
select * from employees where department_id=departID;
在java程序中调用上面的过程：
//1 加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2 得到连接
Connettion cn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:库名称","用户名","密码");
//3 创建CallableStatement
CallableStatement cs=cn.prepareCall("{call pro9(?,?)}");
//4 给问号赋值
cs.setInt(1,10);
//5 说明这是一个返回值,游标类型的。
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
//7 取出返回值
ResultSet rs = (ResultSet )cs.getObject(2);
while(rs.next()){
System.out.println( rs.getInt(1) + " " + rs.getString(2) );
cs.close();
cn.close();
-----------------------------------分页的存储过程 ，过程的高级部分-----------------------------------------
分页的SQL ,取第6-10条记录（两头包含）：
select * from (select rownum rn,t1.*
employees t1 where rownum &=10) where rn&=6;
下面是一个简单的分页，无where条件，无页数越界检查
--创建一个包
create or replace package test_package as
type test_
--创建分页过程
create or replace procedure fenye(
v_tableName in varchar2,--表名
v_pageSize in number,--每页显示的记录数
v_pageNum in number,--当前页码
v_countRows out number,--总记录数
v_countPages out number,--总页数
p_cursor out test_package.test_cursor --返回的结果集
v_sql varchar2(1000);
v_begin number:=(v_pageNum-1) * v_pageSize + 1;
v_end number:=v_pageNum*v_pageS
v_sql:='select * from (select rownum rn,t1.*
'||v_tableName||' t1 where rownum &='||v_begin||') where rn&='||v_
--把游标与select语句关联起来
open p_cursor for v_
--计算总记录数，总页数
v_sql:='select count(*) from '||v_tableN
execute immediate v_sql into v_countR --执行sql
if mod(v_countRows,v_pageSize)=0 then
v_countPages:=v_countRows / v_pageS
v_countPages:=v_countRows / v_pageSize + 1;
--关闭游标
在java程序中调用上面的过程：
//1 加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2 得到连接
Connettion cn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:库名称","用户名","密码");
//3 创建CallableStatement
CallableStatement cs=cn.prepareCall("{call fenye(?,?,?,?,?,?)}");
//4 给问号赋值
cs.setString(1,'employees');
cs.setInt(2,20);
cs.setInt(3,1);
//5 说明这是一个返回值,游标类型的。
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
//7 取出返回值
int countRows=cs.getInt(4);//总记录数
int countPages=cs.getInt(5);//总页数
ResultSet rs = (ResultSet )cs.getObject(6);
while(rs.next()){
System.out.println( rs.getInt(1) + " " + rs.getString(2) );
cs.close();
cn.close();
-----------------------------------------函数部分-------------------------------------------
创建函数：
SQL& --函数例子
SQL& --输入雇员的姓名，返回该雇员的年薪
SQL& --函数的头，vname是要传入的型参，返回一个number类型的yearSal变量
SQL& create function fun1(vname varchar2) return number
is yearSal number(7,2);
select salary*12 into yearSal from employees where last_name=
return yearS--返回结果
Function created
调用函数：
在SQLplus中调用函数：
SQL& var income number
SQL& call fun1('Gietz') into:income
Method called
在java程序中调用函数：
select fun1('Gietz')
可以通过rs.getInt(1)得到返回结果
----------------------------------包 部分--------------------------------------------------
包用于在逻辑上组合过程和函数，它由包规范声明和包体两部分组成。
包的规范只包含了过程和函数的说明，没有实现。要在包体中实现之前声明的过程与函数。
创建一个包规范声明：
SQL& --创建一个包pack1
SQL& --这里只起到声明的作用
SQL& create package pack1 is
pro2 (vname varchar2,newSal number) ; -- 一个过程的声明
function fun1(vname varchar2)
-- 一个函数的声明
Package created
创建一个包体：
SQL& create package body pack1 is
--实现过程，根据用户名去修改工资
procedure pro2 (vname varchar2,newSal number) is
update employees set salary=newSal where last_name=
--实现函数，输入雇员的姓名，返回该雇员的年薪
function fun1(vname varchar2) return number
is yearSal number(7,2);
select salary*12 into yearSal from employees where last_name=
return yearS
Package body created
如何调用包中的过程与函数：
就是在过程与函数名前带包名。
------------------------------------触发器 部分------------------------------------------------
触发器是一个隐含的存储过程，一般在insert,update,delete语句执行时触发某个pl/sql块。
-------------------------------------异常处理-----------------------------------------------
预定义异常 ，大约有20多个
no_data_found
没有找到数据时抛出
case_no_found
case语句中的when子句没有包含必须的条件分支时抛出
cursor_already_open 重复打开已打开的游标时抛出
dup_val_on_index 在唯一索引的列上插入重复的值时抛出
invaild_cursor 在不合法的游标上操作时会抛出， 如：试图重没有打开游标取数据，或关闭没有打开的游标
invaild_number 无效的数值
too_many_rows 返回的结果是多行，但接收的变量只是单行变量 时抛出
zero_divide
当执行2/0时
value_error 变量的长度不足以容纳实际的数据时招聘
其它预定义异常
login_denide 非法登录时抛出
not_logged_on 用户没登录就执行dml操作时抛出
自定义异常
处理导演的例子
when no_data_found then
dbms_output.put_line('无查询结果！');
------------------------------------------------------------------------------------
浏览: 2126706 次
来自: 北京
按照楼主教程做几个例子，谢谢！
布拉君君 写道不太明白，这个HandlerMapping到底是 ...
大神啊，给膜拜了，楼主可否加你QQ，这篇文章我一字不漏的看 ...
大神啊，给膜拜了，楼主可否加你QQ，这篇文章我一字不漏的看 ...SQL从char数据类型到datetime数据类型的转换导致datetime值越界。 - 冰仔的天地-----[神] - 51Testing软件测试网 51Testing软件测试网-中国软件测试人的精神家园 - Powered by X-Space
俺的地盘,神做主!
SQL从char数据类型到datetime数据类型的转换导致datetime值越界。
& 10:57:11
今天犯了一个比较幼稚的错误：早上搜索执行如下命令：SELECT *FROM bo_cl_cdrWHERE (seizetime & ' 00:00:00') AND (seizetime & ' 23:00:00')提示：从char数据类型到datetime数据类型的转换导致datetime值越界。在网上游了一上午没得到解决。最后一个明眼的同事说了句话让偶查点吐血，他说：你家2008年十一月有三十一号呀。。。。。。等　级：新手上路
好像没有人回答，好失望
等　级：贵宾
威　望：66
帖　子：1310
专家分：5312
错误在哪里？
错误的代码在哪里？
等　级：版主
威　望：84
帖　子：2553
专家分：14791
你先用以下小程序测试一下,看看你的思路是不是有错误
Private Sub Command1_Click()
Dim d As Date
d = Date & & & & Time
Print d + 1 / 12&&&&&&&&& ' 2 小时, 1小时为 1/24 ,可以把这个值定义为常量.
Print Format(d, &yyyy-mm-dd hh:mm:ss&)
------------------------
Date 数据类型
Date 变量存储为 IEEE 64 位（8 个字节）浮点数值形式，其可以表示的日期范围从 100 年 1 月 1 日到 9999 年 12 月 31 日，而时间可以从 0:00:00 到 23:59:59。任何可辨认的文本日期都可以赋值给 Date 变量。日期文字须以数字符号 (#) 扩起来，例如，#January 1, 1993# 或 #1 Jan 93#。
Date 变量会根据计算机中的短日期格式来显示。时间则根据计算机的时间格式（12 或 24 小时制）来显示。
当其他的数值类型要转换为 Date 型时，小数点左边的值表示日期信息，而小数点右边的值则表示时间。午夜为 0 而中午为 0.5。负整数表示 1899 年 12 月 30 日之前的日期。
授人于鱼，不如授人于渔
早已停用QQ了
版权所有，并保留所有权利。
Powered by , Processed in 0.040846 second(s), 8 queries.
Copyright&, BCCN.NET, All Rights Reserved相关文章推荐:
char 数据类型到 datetime
关于“char数据转换到datetime数据类型的转换导致大tetime值越界”的问题，如何解决，请指点
回复 (9) 浏览 (3932)
其他文章推荐
ITPUB官方微博:
“上ERP找死，不上ERP等死。”柳传志的这句名言几乎成了中国信息化的“魔咒”。如何破解中小企业信息化难题，成了ERP厂商的心头之痛。一方面，中小企业信息化已经迫在眉睫，但高额的投入成为中小企业信息化建设的重要阻力因素；另一方面，受诸多因素的限制，中小企业的信 ......
回复 (6) 浏览 (280)
本帖最后由cxytz01于9:53编辑现有一业务系统，游标fetch数据，处理，处理完毕继续fetch，继续处理。都是单线程处理，大家都知道，程序中的数据库操作必定经过网络io，既然有io，就存在着阻塞。若fetch的数据表有千万条数据，则fetch千万次，经历过千万次io，千 ......
回复 (7) 浏览 (238)
各位大大，我现在有个问题，我有个存储过程，先通过dblink查询出数据，然后再根据条件插入到临时表中，但是我的dblink不是一个，所以我需要将dblink做成带输入参数的变量，请问要如何修改呢，附存储过程CREATEORREPLACEPROCEDUREtemp_jiankongASt_A0varchar2(100);t_A1v ......
回复 (5) 浏览 (221)
查询字段为当前日期如值查询不到数据，因为数据库中的日期含时分秒，而要查询的数据没有时分秒只有当前日期SELECTrmtype,COUNT(rmtype)ASExpr1,arrdateFROMgresWHERE(rmtype='ST')AND(arrdate=CONVERT(varchar(100),',120))GROUPBYrmtype,arrdate查询 ......
回复 (4) 浏览 (112)
有哪位大侠知道接口表mtl_item_revisions_interface的数据是什么时候写入表mtl_item_revisions的？...
回复 (1) 浏览 (85)
DateTime.Parse()在用的时候要如下代码CultureInfoCultureInfo=newCultureInfo(&en-US&CultureInfo.DateTimeFormat.TimeSeparator=&:&;CultureInfo.DateTimeFormat.DateSeparator=&-&;CultureInfo.DateTimeFormat.LongDatePattern=&yyyy-MM-dd&;Cultu ......
回复 (3) 浏览 (1035)
一、本机环境select*from&v$versionwhererownum&=2;OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-ProdPL/SQLRelease10.2.0.1.0-Production&&selectuserenv('language')from&&AMERICAN_AMERICA.ZHS16GBK二、实验1 ......
回复 (5) 浏览 (178)
&!----&一、&!--[endif]--&场景在SQLServer2005中，有一个表TestDatetime，其中Dates这个字段的数据类型是datetime，如果你看到表的记录如下图所示，你最先想到的是什么呢？（图1：数据列表）你看到这些数据，是不是觉得这样的设计既浪费了存储空间 ......
回复 (6) 浏览 (233)
今天的任务中遇到一个问题，要求屏蔽掉某段时间范围内的特定节假日。在网上搜了下相关资料，但较为繁琐，于是自己写了两个简单实用的方法。将下面这两个方法组合起来，就可以去除某段时间范围内的特定日期（例如节假日）。&获取某段日期范围内的所有日期& ......
回复 (3) 浏览 (150)
VS2010新增Chart控件，可以直接在页面插入图表：&&&&&&&&简单示例：&&&asp:ChartID=&Chart1&runat=&server&Height=&400px&Width=&500px&&&&& ......
回复 (6) 浏览 (3544)
文章推荐：
盛拓传媒：
友情链接：
特别鸣谢：
北京皓辰网域网络信息技术有限公司. 版权所有
北京市公安局海淀分局网监中心备案编号：
广播电视节目制作经营许可证：编号（京）字第1149号
ITPUB推荐文章解答你所有技术难题java.sql.BatchUpdateException: ORA-01483: DATE 或 NUMBER 赋值变量的长度无效
[问题点数：20分]
java.sql.BatchUpdateException: ORA-01483: DATE 或 NUMBER 赋值变量的长度无效
[问题点数：20分]
不显示删除回复
显示所有回复
显示星级回复
显示得分回复
只显示楼主
相关帖子推荐：
2009年11月 Java大版内专家分月排行榜第三
本帖子已过去太久远了，不再提供回复功能。