博客分类：
Qracle PL/SQL
--存储过程
SQL是数据库普通话，每个数据库在实现SQL国际标准之外，也有自己特有的语句。（Hibernate dialect）
通讯的标准：ODBC(ado , ado.net)，JDBC(jdo , Hibernate)
ODBC、JDBC是最有效率的，但是开发繁琐，才有后来括号中的高度函数化的拓展
PL/SQL:Procudural Language Extension to SQL.
在SQL语句基础上，加上了结构化流程控制，可用多条SQL语句完成一个功能。
PL/SQL功能强大，经常用来书写复杂的业务逻辑。
//打开控制台输出
declare [变量名] [变量数据类型];
//定义变量，写在begin之前
//代码编辑区
//赋值符号
dbms_output.put_line('内容'||变量);
//控制台输出，和Java不同的是连接使用的是||符号而不是加
号，注意字符串必须用单引号来修饰
select x into result from ttt where rownum=1;
result:=result+1;
dbms_output.put_line('result='||result);
学生表的创建
create table my_student
stuno int primary key,
stuname varchar2(20) not null,
int not null
v_stuname varchar2(20);
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=2;
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no 2');
new -& program window -&procedure
--根据学号显示对应学生信息
create or replace procedure uuu_show_student(x_stuno in int)
v_stuname varchar2(20);
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=x_
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no 2');
end uuu_show_
运行存储过程
执行execute uuu_show_
范例4.两数求和
create or replace procedure show_uuu_number(x_num1 in int,x_num2 in int)
if x_num1 & x_num2 then
x_big_number := x_num1;
x_num_index := 1;
x_big_number := x_num2;
x_num_index := 2;
dbms_output.put_line('big number:'||x_big_number||' index:'||x_num_index);
end show_uuu_
范例5.循环分支
create or replace procedure show_uuu_number(x_num1 in int,x_num2 in int)
if x_num1 & x_num2 then
x_big_number := x_num1;
x_num_index := 1;
elsif x_num1 & x_num2 then
x_big_number := x_num2;
x_num_index := 2;
dbms_output.put_line('equal');
if x_num1 && x_num2 then
dbms_output.put_line('big number:'||x_big_number||' index:'||x_num_index);
end show_uuu_
有返回值的用function
计算三角形面积
create or replace function uuu_area(width in number,height in number) return number is
Result := 0.5*width*
return(Result);
select uuu_area(3,5)
uuu_AREA(3,5)
-------------
create or replace function show_uuu_day(datestr in varchar2)
return varchar2 is
varchar2(200);
varchar2(1);
checkdate := to_date(trim(datestr), 'YYYY-MM-DD');
:= to_char(checkdate, 'D');
case v_day
when '1' then
Result := datestr || '是星期天';
when '2' then
Result := datestr || '是星期一';
when '3' then
Result := datestr || '是星期二';
when '4' then
Result := datestr || '是星期三';
when '5' then
Result := datestr || '是星期四';
when '6' then
Result := datestr || '是星期五';
when '7' then
Result := datestr || '是星期六';
dbms_output.put_line(datestr || '是星期六');
return(Result);
end show_uuu_
SQL& select show_uuu_day('')
SHOW_uuu_DAY('')
--------------------------------------------------------------------------------
create or replace function uuu_get_sum(num1 in number, num2 in number) return number is
Result number := 0;
temp := num1;
Result:=Result+
temp:=temp+1;
-- if temp & num2 then
exit when temp & num2;
return(Result);
end uuu_get_
SQL& select uuu_get_sum(1,100)
uuu_GET_SUM(1,100)
------------------
while loop
create or replace function uuu_get_sum(num1 in number, num2 in number) return
Result number := 0;
temp := num1;
while num2 &= 200 loop
Result:=Result+
temp:=temp+1;
-- if temp & num2 then
exit when temp & num2;
return(Result);
end uuu_get_
SQL& select uuu_get_sum(1,1000)
uuu_GET_SUM(1,1000)
-------------------
数字for loop循环
create or replace procedure sum(begin1 number,end2 number)
for i in begin1..end2
tosum:=tosum+i;
dbms_output.put_line(tosum);
001.判断一个数是否为质数
create or replace function x_isprime(num in int) return int is
for i in 2..num-1
if num mod i = 0 then
Result := 0;
return(Result);
if num &= 2 then
Result := 1;
return(Result);
002判断质数
create or replace procedure x_prime(begini in int, endi in int)
count2 int := 0;
for i in begini..endi
if x_isprime(i) = 1 then
dbms_output.put(i||'
count2:=count2+1;
if count2 mod 8 = 0 then
dbms_output.put_line('');
debug 权限设置
grant debug connect session to test1;
003判断质数
CREATE OR REPLACE PROCEDURE show_prime(bval IN INT, eval IN INT) IS
icount int:=0;
-- 取数循环
FOR i IN bval .. eval LOOP
-- 质数判断循环
flag := 1;
FOR j IN 2 .. i - 1 LOOP
IF i MOD j = 0 THEN
flag := 0;
IF flag = 1 THEN
dbms_output.put(i||'
icount:=icount+1;
if icount mod 8 =0 then
dbms_output.put_line('');
======================================================================
pl/sql cursor 操作
1. 什么是游标？
oracle在执行一条SQL语句的时候，它将创建一个内存区域 (context area),该内存区域包含执行这条语句所需要的所有信息。
信息如下：
1. 该语句执行之后返回的记录集
2. 一个指针，指向了该语句在内存中的被解析后的结果。
cursor(游标) 是一个handle (pointer), 指向了这个上下文区域。
通过cursor, PL/SQL程序能够控制context area, 掌握在语句运行的时，将如何对该区域产生影响。
2. 游标的类型
1) implicit cursor
每条SQL语句执行的时候，将自动产生一个implicit游标。 该游标，用户不可控制。
一个cursor将自动和每条DML语句关联 （update,delete,insert), 我们可以通过cursor了解上头语句产生的结果。
所有update和delete语句相关联cursor，包含了该操作影响的行的集合。
最后打开的cursor， 名字叫SQL cursor.
--------------------------------------------------------------------------------------------
update my_student set stuname='mary' where stuno=60;
dbms_output.put_line(SQL%ROWCOUNT);
2) explicit cursor
用户自己定义的游标，针对的是返回超过一条记录的查询。 用户可以通过该cursor控制记录返回过程。
Record Type
记录是复杂的数据结构。记录往往表现成为表的一行。
create or replace procedure show_student2 is
vr_student my_student%ROWTYPE;
into vr_student
from my_student where stuno=6;
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
end show_student2;
a. 声明游标 （declare a cursor)
该操作初始化这个游标，为其创建内存空间。
CURSOR c_cursor_name is select statement
(该游标和select语句相关联)
------------------
v_name varchar2(20);
CURSOR c_mycursor is
select * from student where name like '%h%';
游标打开后，不能继续再二次打开。
b. 打开游标 (open cursor)
创建context area, 执行语句， 获得rows.
open c_mycursor
c. 获取cursor中的行。
fetch cursorname into pl/sql variables
fetch cursorname into pl/sql record
d. 关闭cursor
一旦所有的行被处理结束，cursor应该被关闭。
关闭的操作通知pl/sql engine， 程序对该cursor的需求已经结束，可以释放context are所占用的内存资源。
cursor一旦关闭，则不可以继续fetch . 也不能重复关闭cursor.
& 常用的cursor属性
cursorname%NOTFOUND
cursorname%FOUND
cursorname%ROWCOUNT
cursorname%ISOPEN
--------------------
create or replace procedure show_student2 is
CURSOR c_student is
select * from my_student
vr_student my_student%ROWTYPE;
TYPE simple_stu is record
stuname my_student.stuname%TYPE,
my_student.stuage%TYPE,
stuage2 int
vr_simple_student simple_
cursor c_simple_student is
select stuname,stuage,stuage+2 from my_student
into vr_student
from my_student where stuno=6;
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
fetch c_student into vr_
exit when c_student%NOTFOUND;
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
if c_student %ISOPEN then
open c_simple_
fetch c_simple_student into vr_simple_
exit when c_simple_student%NOTFOUND;
dbms_output.put_line(vr_simple_student.stuname||','||vr_simple_student.stuage
||','||vr_simple_student.stuage2);
close c_simple_
end show_student2;
--------------------------------------------
cursor for loop
nested cursor
v_sid student.student_id%TYPE;
CURSOR c_student IS
SELECT student_id, first_name, last_name
FROM student
WHERE student_id & 110;
CURSOR c_course IS
SELECT c.course_no, c.description
FROM course c, section s, enrollment e
WHERE c.course_no = s.course_no
AND s.section_id = e.section_id
AND e.student_id = v_
使用for loop cursor, 可以便捷的遍历游标，省去了open,fetch,close的书写。
连保存变量的定义也可以省略，可在for后直接书写变量名。
FOR r_student IN c_student LOOP
v_sid := r_student.student_
DBMS_OUTPUT.PUT_LINE(chr(10));
DBMS_OUTPUT.PUT_LINE(' The Student ' || r_student.student_id || ' ' ||
r_student.first_name || ' ' ||
r_student.last_name);
DBMS_OUTPUT.PUT_LINE(' is enrolled in the ' || 'following courses: ');
-- nested cursor
FOR r_course IN c_course LOOP
DBMS_OUTPUT.PUT_LINE(r_course.course_no || ' ' ||
r_course.description);
---------------------------------------------------------------
带参数的游标
CURSOR c_student(p_stuage in my_student.stuage%type) is
select * from my_student where stuage=p_stuage
for vr_student in c_student(20)
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
　　CURSOR c_student２(p_stuage in my_student.stuage%type，p_stuname in my_student.stuname%TYPE) is
select * from my_student where stuage=p_stuage
cursor具备的参数：
　1) cursor得到了复用。
　2) 提高了性能，压缩了返回的行的数量。
====================================================================
1.PL/SQL Exception
常用的预定义异常
a）NO_DATA_FOUND
根据查询条件，没有查询记录被找到
b）TOO_MANY_ROWS
select into 结构只能返回一条记录，赋予存储过程变量。如果select
..into..结构返回记录为多条，将产生这个异常
c）ZERO_DIVIDE
除数是0(把ORA-01476 error映射成ZERO_DIVIDE错误）
例： SQL& select 6/0
select 6/0 from dual
ORA-01476: 除数为 0
d)VALUE_ERROR
从运算或者数据库中取得值赋予变量的时候类型不匹配或者长度不足，导致的异常
e)DUP_VAL_ON_INDEX
主键不可重复，违反主键唯一约束
(类似java Exception异常）
create table my_student(
stuno int primary key,
stuname varchar2(20),
stuage int
insert into my_student values(1,'dadiv',20);
insert into my_student values(2,'mary',20);
insert into my_student values(3,'henry',20);
异常举例例子：
create or replace procedure uuu_show_student(x_stuno in int)
v_stuname varchar2(20);--替换v_stuname varchar2(2)/int值不匹配或者长度不足异常
insert into my_student values(2,'kate',49);--DUP_VAL_ON_INDEX异常
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=x_--加上or stuname like '%y'产生值记录太多异常
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no '||x_stuno);
when VALUE_ERROR then
dbms_output.put_line('值不匹配');
when TOO_MANY_ROWS then
dbms_output.put_line('记录太多');
when DUP_VAL_ON_INDEX then
dbms_output.put_line('主键不可重复，插入失败');
when OTHERS then
dbms_output.put_line('其它异常捕获');--一些预定义异常的父类
end uuu_show_
异常的作用域
v_student_id NUMBER := &sv_student_
v_name VARCHAR2(30);
v_total NUMBER(1);
-- outer block
SELECT RTRIM(first_name)||' '||RTRIM(last_name)
INTO v_name
FROM student
WHERE student_id = v_student_
DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name);
-- inner block
SELECT COUNT(*)
INTO v_total
FROM enrollment
WHERE student_id = v_student_
DBMS_OUTPUT.PUT_LINE ('Student is registered for '||
v_total||' course(s)');
WHEN VALUE_ERROR OR INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('There is no such student');
自定义异常：
create or replace procedure uuu_show_student(x_stuno in int)
v_stuname varchar2(20);--替换v_stuname varchar2(2)/int值不匹配或者长度不足异常
e_invalid_stuno EXCEPTION;--自定义异常
if x_stuno & 0 then
raise e_invalid_
--insert into my_student values(2,'kate',49);--DUP_VAL_ON_INDEX异常
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=x_--加上or stuname like '%y'产生值记录太多异常
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no '||x_stuno);
when VALUE_ERROR then
dbms_output.put_line('值不匹配');
when TOO_MANY_ROWS then
dbms_output.put_line('记录太多');
when e_invalid_stuno then
--控制台输入execute uuu_show_student(-2);
dbms_output.put_line('学生编号不合法');
when DUP_VAL_ON_INDEX then
dbms_output.put_line('主键不可重复，插入失败');
when OTHERS then
dbms_output.put_line('其它异常捕获');--一些预定义异常的父类
end uuu_show_
==================================================================
--触发器的一个例子
create or replace trigger student_aud
before insert on my_student
for each row
select stu_highage
into v_highage
if :NEW.stuage &25 then
v_highage:=v_highage+1;
update stu_stat set stu_count=stu_count+1,stu_highage=v_
end student_
xuguiyi1000
浏览: 53690 次
来自: fujian
我比较喜欢用第三种方法：
GeneratedKeyHolder ...
同样遇到这个问题，看看能解决吗，谢谢了蛤
是oracle，这字段就是根据这个系列来自增的
是oracle的吗
select table_seq.ne ...
(window.slotbydup=window.slotbydup || []).push({
id: '4773203',
container: s,
size: '200,200',
display: 'inlay-fix'比特客户端
您的位置：
详解大数据
详解大数据
详解大数据
详解大数据
Oracle存储过程的基本语法及注意事项
　企业软件热点文章
　　1.基本结构
　　CREATE OR REPLACE PROCEDURE过程名字
　　参数1 IN NUMBER,
　　参数2 IN NUMBER
　　变量1 INTEGER :=0;
　　变量2 DATE;
　　END 存储过程名字
　　2.SELECT INTO STATEMENT
　　将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
　　记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
　　例子：
　　SELECT col1,col2 into 变量1,变量2 FROM
　　EXCEPTION
　　WHEN NO_DATA_FOUND THEN
　　3.IF 判断
　　IF V_TEST=1 THEN
　　do something
　　END IF;
　　4.while 循环
　　WHILE V_TEST=1 LOOP
　　END LOOP;
　　5.变量赋值
　　V_TEST := 123;
　　6.用for in 使用cursor
　　CURSOR cur IS SELECT * FROM
　　FOR cur_result in cur LOOP
　　V_SUM :=cur_result.列名1+cur_result.列名2
　　END LOOP;
　　7.带参数的cursor
　　CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
　　OPEN C_USER(变量值);
　　FETCH C_USER INTO V_NAME;
　　EXIT FETCH C_USER%NOTFOUND;
　　do something
　　END LOOP;
　　CLOSE C_USER;
　　8.用pl/sql debug
　　连接后建立一个Test WINDOW
　　在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
　　关于存储过程的若干问题备忘
　　1.在oracle中，数据表别名不能加as，如：
　　select a.ap-- 正确
　　select a.appna-- 错误
　　也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧
　　2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。
　　select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=-- 有into，正确编译
　　select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=-- 没有into，编译报错，提示：Compilation
　　Error:-00428: an INTO clause is expected in this SELECT statement
　　3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。
　　可以在该语法之前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用select...into...
　　4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错
　　select keynode into kn from APPFOUNDATION where appid=aid and foundationid=-- 正确运行
　　select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=-- 运行阶段报错，提示
　　ORA-01422:exact fetch returns more than requested number of rows
　　5.在存储过程中，关于出现null的问题
　　假设有一个表A，定义如下：
　　create table A(
　　id varchar2(50) primary key not null,
　　vcount number(8) not null,
　　bid varchar2(50) not null -- 外键
　　如果在存储过程中，使用如下语句：
　　select sum(vcount) into fcount from A where bid='xxxxxx';
　　如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：
　　if fcount is null then
　　fcount:=0;
　　这样就一切ok了。
　　6.Hibernate调用oracle存储过程
　　this.pnumberManager.getHibernateTemplate().execute(
　　new HibernateCallback() ...{
　　public Object doInHibernate( session)
　　throws HibernateException, SQLException ...{
　　CallableStatement cs = session
　　.connection()
　　.prepareCall("{call modifyapppnumber_remain(?)}");
　　cs.setString(1, foundationid);
　　cs.execute();
[ 责任编辑：之极 ]
去年，手机江湖里的竞争格局还是…
甲骨文的云战略已经完成第一阶段…
软件信息化周刊
比特软件信息化周刊提供以数据库、操作系统和管理软件为重点的全面软件信息化产业热点、应用方案推荐、实用技巧分享等。以最新的软件资讯，最新的软件技巧，最新的软件与服务业内动态来为IT用户找到软捷径。
商务办公周刊
比特商务周刊是一个及行业资讯、深度分析、企业导购等为一体的综合性周刊。其中，与中国计量科学研究院合力打造的比特实验室可以为商业用户提供最权威的采购指南。是企业用户不可缺少的智选周刊！
比特网络周刊向企业网管员以及网络技术和产品使用者提供关于网络产业动态、技术热点、组网、建网、网络管理、网络运维等最新技术和实用技巧，帮助网管答疑解惑，成为网管好帮手。
服务器周刊
比特服务器周刊作为比特网的重点频道之一，主要关注x86服务器，RISC架构服务器以及高性能计算机行业的产品及发展动态。通过最独到的编辑观点和业界动态分析，让您第一时间了解服务器行业的趋势。
比特存储周刊长期以来，为读者提供企业存储领域高质量的原创内容，及时、全面的资讯、技术、方案以及案例文章，力求成为业界领先的存储媒体。比特存储周刊始终致力于用户的企业信息化建设、存储业务、数据保护与容灾构建以及数据管理部署等方面服务。
比特安全周刊通过专业的信息安全内容建设，为企业级用户打造最具商业价值的信息沟通平台，并为安全厂商提供多层面、多维度的媒体宣传手段。与其他同类网站信息安全内容相比，比特安全周刊运作模式更加独立，对信息安全界的动态新闻更新更快。
新闻中心热点推荐
新闻中心以独特视角精选一周内最具影响力的行业重大事件或圈内精彩故事，为企业级用户打造重点突出，可读性强，商业价值高的信息共享平台；同时为互联网、IT业界及通信厂商提供一条精准快捷，渗透力强，覆盖面广的媒体传播途径。
云计算周刊
比特云计算周刊关注云计算产业热点技术应用与趋势发展，全方位报道云计算领域最新动态。为用户与企业架设起沟通交流平台。包括IaaS、PaaS、SaaS各种不同的服务类型以及相关的安全与管理内容介绍。
CIO俱乐部周刊
比特CIO俱乐部周刊以大量高端CIO沙龙或专题研讨会以及对明星CIO的深入采访为依托，汇聚中国500强CIO的集体智慧。旨为中国杰出的CIO提供一个良好的互融互通 、促进交流的平台，并持续提供丰富的资讯和服务，探讨信息化建设，推动中国信息化发展引领CIO未来职业发展。
IT专家新闻邮件长期以来，以定向、分众、整合的商业模式，为企业IT专业人士以及IT系统采购决策者提供高质量的原创内容，包括IT新闻、评论、专家答疑、技巧和白皮书。此外，IT专家网还为读者提供包括咨询、社区、论坛、线下会议、读者沙龙等多种服务。
X周刊是一份IT人的技术娱乐周刊，给用户实时传递I最新T资讯、IT段子、技术技巧、畅销书籍，同时用户还能参与我们推荐的互动游戏，给广大的IT技术人士忙碌工作之余带来轻松休闲一刻。
微信扫一扫
关注Chinabyte相关文章推荐
在实际使用中，经常会有带in的子查询，如where id in (1,2,3)这样的情况，但是如果很多这样的语句在数据库中出现，将引起数据库的大量硬解析与共享池SQL碎片。所以，在实际应用中，可以采用...
存储过程学习
存储过程 1
Oracle存储过程基础知识 1
Oracle存储过程的基本语法 2
关于Oracle存储过程的若干问题备忘
1.oracle存储过程的基本语法
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
参数1 IN NUMBER,
参数2 IN NUMBER
Oracle存储过程基本语法 存储过程
　　1 CREATE OR REPLACE PROCEDURE 存储过程名
　　3 BEGIN
　　4 NULL;
　　5 E...
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
参数1 IN NUMBER,
参数2 IN NUMBER
变量1 INT...
存储过程创建语法：
create [or replace] procedure 存储过程名（param1 in type，param2 out type）
变量1 类型（值范围）;
CREATE OR REPLACE PROCEDURE 存储过程名
下面是带输入输出参数，以及异常信息处理的后台存储过程：
create or replace procedure sp_exp_bz_bf(
学习存储过程的前提是，了解PLSQL的语法和编写方式。
需要了解PLSQL，请查看之前的总结。
我们以下的表操作可能会基于以下两张表:
我们创建一个员工表和部门表:
员工信息表
oracle 存储过程的基本语法1.基本结构CREATE OR REPLACE PROCEDURE 存储过程名字(
参数1 IN NUMBER,
参数2 IN NUMBER) IS变量1 ...
他的最新文章
他的热门文章
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)oracle存储过程的基本语法及注意事项
oracle存储过程的基本语法及注意事项
oracle存储过程的基本语法及注意事项
你这个问题太大了，内容太丰富，
在这里回答很困那，建议你看看oracle的书籍
请遵守网上公德，勿发布广告信息
相关问答：}