ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 创建存储过程,游标,块,包,job,函数

oracle 创建存储过程,游标,块,包,job,函数

原创 Linux操作系统 作者:hyjk2009 时间:2012-06-07 10:57:23 0 删除 编辑

------------------------------创建存储过程---------------------------------
create or replace procedure test is
begin
insert into 表名 values(值);
end;
/
如果有错误通过show error命令查看错误

create or replace procedure test2(newName varchar2,newSal number) is
begin
update emp set sal=newSal where  ename=newName ; 
end;
/


java调用存储过程
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct=DriverManager.getConnection("jdbc:","scott","triger");
CallableStatement cs=ct.prepareCall("{call 存储名(?,?)}");//专门调用存储过程
cs.setString(1,"SMITH");//给?赋值
cs.setInt(2,10);
cs.execute();  //执行


调用存储过程
exec 存储名
call 存储名

表结构(用户id,用户名)
通过输入用户名,循环添加10个用户到表中,用户编号从1开始增加
create or replace procedure adduser(newName varchar2) is
v_num number:=1;
begin
loop
insert into users values(v_num,newName);
exit when v_num=10;
v_num:=v_num+1;
end loop;
end;


向book表中添加记录
create or replace procedure newbook (bookid number,bookname varchar2,bookpublish varchar2) is
begin
insert into book values(bookid,bookname,bookpublish);
end;

有2个返回值的存储过程
-----in 输入参数,out 输出参数
create or replace procedure newbook (bianhao in number ,name out varchar2,sal out number) is
begin
select ename,sal into name,sal from emp where empno=bianhao;
end;
java中调用有返回值的存储过程
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct=DriverManager.getConnection("jdbc:","scott","triger");
CallableStatement cs=ct.prepareCall("{call 存储名(?,?)}");//专门调用存储过程
cs.setString(1,7788);//给?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();  //执行
String name=cs.getString(2);//得到返回值 2表示第二个问号
在plsql中调用有返回值的存储过程
SQL> set serverout on;
SQL> declare abc varchar2(5);
  2  a number(7);
  3  begin
  4  newbook(7788,abc,a);
  5  dbms_output.put_line(abc);
  6  dbms_output.put_line(a);
  7  end;
  8  /
 有结果集的存储过程
 1.定义包
 create or replace package propack as
 type pro_cursor is ref cursor;
 end;
 2.定义存储过程
create or replace procedure rspro(newdeptno number,p_cursor out propack.pro_cursor) is
begin
open  p_cursor for select ename  from emp where deptno=newdeptno;
end;
plsql调用
右键点击存储过程名,测试,输入需要输入的值,点击执行,在cursor就可以看到结果集。
java调用
CallableStatement cs=ct.prepareCall("{call 存储名(?,?)}");//专门调用存储过程
cs.setString(1,10);//给?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
cs.execute();  //执行
ResultSet rs=(ResultSet)cs.getObject(2);
while(rs.next())
{
rs.getInt(1);
rs.getString(2);

}

--------------------------------------------------块---------------------------------
set serverout on;
创建块
declare
v_ename varchar2(3);------声明变量 或   v_ename emp.ename%type;
v_sal number(7,2)
begin
select ename,sal into v_ename,v_sal from emp where deptno=&no;
dbms_output.put_line('用户名是:' || v_ename);
exception
when no_data_found then
dbms_output.put_line('输入有误 ')
end;


块调用过程
declare
v_num number:=1;
begin
loop
p_ran;----存储过程名
exit when v_num=50;
v_num:=v_num+1;
end loop;
end;

-----------------------------------------包--------------------------------------
创建包
create or replace package sp_pak is
procedure test2(newName varchar2,newSal number);
function sp_fun(sp_name varchar2) return number ;
end;
创建包体
create or replace package body sp_pak is
procedure test2(newName varchar2,newSal number) is
begin
update emp set sal=newSal where  ename=newName ; 
end;
function sp_fun(sp_name varchar2)
return number
is
yearsal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearsal from emp where ename=sp_name;
return yearsal;
end ;

end;
调用包
exec sp_pak.test2('SCOTT',10000);

var abc number;
call sp_pak.sp_fun('SMITH') into:abc;

 

----------------------------------------------函数----------------------------------------
有返回值
创建函数
输入一个名子,查询出其年薪     -------------如果sp_name中首字母大写则报错
create or replace function sp_fun(sp_name varchar2)
return number
is
yearsal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearsal from emp where ename=sp_name;
return yearsal;
end ;
/
在sqlplus中执行此函数
var abc number;
call sp_fun('SCOTT') into:abc

在java程序中调用
select sp_fun('SCOTT') from dual;
rs.getInt(1);

 

----------------------------------自定义--类型--------------------------
set serverout on;
declare
----定义一个pl/sql记录类型,类型包含3个数据name,salary,title
type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);
----定义一个变量,类型为emp_record_type
sp_record emp_record_type;
begin
select ename,sal,job into sp_record from emp where empno=7788;
dbms_output.put_line('员工姓名:'|| sp_record.name||'工资是:'|| sp_record.salary);
end;
/

 

-----------------------------------------游标-------------------------------
输入部门号,显示该部门所有的员工姓名和工资,如果工资低于2000元,就增加1000元
declare
----定义游标类型
type first_cursor is ref cursor ;
----定义游标变量
test_cursor first_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open test_cursor for select ename,sal from emp where deptno=&no;
loop
fetch test_cursor into v_ename,v_sal;
if v_sal < 2000
then
update emp set sal=v_sal+1000 where v_sal<2000 ;
end if;
----判断游标结束
exit when test_cursor%notfound;
dbms_output.put_line('员工姓名:'|| v_ename||'工资是:'|| v_sal);
end loop;
close test_cursor;
end;


exit when test_cursor%ROWCOUNT=2; --游标抽取过的记录行数

--------------------------------分页---------------------------------------
编写一个存储过程,要求可以输入表名,每页显示记录数、当前页。返回总记录数、总页数和返回的结果集。
1.创建包
 create or replace package propack as
 type pro_cursor is ref cursor;
 end;
2.编写存储过程
create or replace procedure fenye (tableName varchar2,sizePage number,currPage number,rowSum out number,pageSum out number,cuRS out propack.pro_cursor) is
v_sql varchar2(1000);
v_begin number:=(currPage-1)*sizePage+1;
v_end number:=currPage*sizePage;
begin
v_sql:='select * from (select t1.* ,rownum rn from ( select * from '||tableName||' ) t1 where rownum<='||v_end||') where rn>='||v_begin;
open cuRS for v_sql;
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into rowSum;
if mod(rowSum,sizePage)=0 then
rowSum:=rowSum/sizePage;
else
rowSum:=rowSum/sizePage+1;
end if;
end;

----------------------------------查看存储过程源代码------------查看存储过程状态------------------------------------------

SELECT * FROM USER_SOURCE WHERE TYPE='存储名';
SELECT object_name,status FROM USER_OBJECTS WHERE object_name='存储名';

 

-----------------------------------触发器是一个特殊的存储过程---------------------------------------------------------------


区别就是在于, 存储过程需要去调用,而触发器无需调用,在执行某些操作的时候,会自动执行。

一般当表或者视图执行 增,删,改 操作的时候,就会自动执行触发器中的PL SQL 语句块。

还有一个区别, 创建触发器是不带参数的, 而 存储过程 可带可不带 参数。

 

数据库行级触发器 - 对每一行(每一条记录进行检查) 动作都触发 FOR EACH ROW

创建一个学生表:
CREATE TABLE xue_sheng( id integer, xing_ming varchar(25),xing_bie number, fen_shu number, b_id integer);

INSERT INTO xue_sheng VALUES(1,'ZhanSan',1,80,1);

INSERT INTO xue_sheng VALUES(2,'LiSi',1,90,2);

INSERT INTO xue_sheng VALUES(3,'ZhanHong',0,75,2);

INSERT INTO xue_sheng VALUES(4,'ChenXiaoMing',1,85,1);

创建一个班级表:
CREATE TABLE ban_ji( id integer , ban_ji varchar(25));

INSERT INTO ban_ji VALUES(1,'1-(1)');

INSERT INTO ban_ji VALUES(2,'1-(2)');


创建一个删除行级触发器

当删除班级表的一个id , 那么它会自动把学生表所属的班级的学生也会删除

SQL> CREATE OR REPLACE TRIGGER del_ban_id
  2  AFTER DELETE ON ban_ji
  3  FOR EACH ROW
  4  BEGIN
  5  DELETE FROM xue_sheng where b_id=:old.id;
  6  END;
  7  /


DELETE FROM ban_ji where id=2;

查看,检查触发器是否自动执行了
select * from ban_ji;
select * from xue_sheng;

执行删除操作的时候, 建立一个 old内存表, old表和ban_ji表 结构完全一样

所以上面的 old.id 可以理解成 ban_ji班级表的 id

创建一个插入行级触发器
SQL> CREATE OR REPLACE TRIGGER insert_ban_ji
  2  AFTER INSERT ON ban_ji
  3  FOR EACH ROW
  4  BEGIN
  5  INSERT INTO xue_sheng VALUES('5','test',0,83,:new.id);
  6  END;
  7  /

INSERT INTO ban_ji VALUES(3,'1-(3)');

查看,检查触发器是否自动执行了
select * from ban_ji;
select * from xue_sheng;

当插入数据时候,先插入到 new 表,new表和班级表结构也是一样的。 然后在插入到 真正的表,
所以 new.id 和 ban_ji班级表id 对应的。

级联更新 同时涉及到 old.id 和 new.id

例如:我要更新班级表的班级id, 当然学生表的班级id也要同时更新

SQL> CREATE OR REPLACE TRIGGER update_ban_ji
  2  AFTER UPDATE ON ban_ji
  3  FOR EACH ROW
  4  BEGIN
  5  UPDATE xue_sheng SET b_id=:new.id WHERE b_id=:old.id;
  6  END;
  7  /

先查看一下原来2个表的数据
select * from xue_sheng;
select * from ban_ji;

然后更新班级表的id
UPDATE ban_ji SET id=8 WHERE id=1;

最后查看一下效果
select * from ban_ji;
select * from xue_sheng;

CREATE OR REPLACE PROCEDURE p_test
  (p_cur OUT SYS_REFCURSOR)
IS
BEGIN
  open p_cur for select * from emp;
END;
variable v_test ref cursor;
execute p_test(:v_test);
print v_test;
---------------------------------------------------job----------------------------------------
语法:
 PROCEDURE Submit ( job    OUT binary_ineger,
            What   IN varchar2,
            next_date IN date,
            interval IN varchar2,
            no_parse IN booean:=FALSE)
示例:每隔30天凌晨2点整执行deldate存储
variable job1 number;
begin
dbms_job.submit(:job1,'DELDATE;',TRUNC(SYSDATE) +(2*60+00)/(24*60),'sysdate+30');
end;
 

调用 job
begin
dbms_job.run(:job1);
end;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25979800/viewspace-732128/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-06-07

  • 博文量
    1
  • 访问量
    724