oracle存储过程学习

1、基本数据变量

declare
name varchar2(10):='xiang';
begin
dbms_output.put_line(name);
end;
/

 

2、引用数据变量

declare
name varchar2(222);
name2 student.name%type;
begin
select name as hh into name from student where class='101';
select name as hh into name2 from student where class='102';

dbms_output.put_line('name:'||name||'name2:'||name2);
end;
/

 

3、纪录型变量

declare
student_v student%rowtype;
begin
select * into student_v from student where class='102';

dbms_output.put_line('name:'||student_v.name||'age:'||student_v.age);
end;
/

 

4、条件分支

declare
num student.age%type;
begin
select age into num from student where class='101';
if num>10 then
dbms_output.put_line('大于10');
elsif num=10 then
dbms_output.put_line('等于10');
else
dbms_output.put_line('小于10');
end if;
end;
/

 

5、循环

declare
num number:=1;
begin
loop
exit when num>=10;
dbms_output.put_line('num的值:'||num);
insert into student values('张'||num,num,'101',sysdate);
num:=num+1;
end loop;
end;
/

 

7、游标（无参数）

declare
v_name student.name%type;
v_age student.age%type;
v_class student.class%type;
--申明游标
cursor mycursor is select name,age,class from student;
begin
--打开游标
open mycursor;
loop
--判断游标是否为空
exit when mycursor%notfound;
--取出游标里面的值
fetch mycursor into v_name,v_age,v_class;
dbms_output.put_line('name:'||v_name||',age:'||v_age||',class:'||v_class);
end loop;
--关闭游标
close mycursor;
end;
/

 简易的for循环

declare
  cursor cur is select * from test
begin
   for tmp in cur
      loop
      insert into test1(id,ishavecommit,createtime)values(tmp.id,tmp.ishavecommit,sysdate);
end loop;
commit;
end;

 

8、游标（带参数）

declare
v_name student.name%type;
v_age student.age%type;
m_class student.class%type;
cursor mycursor(v_class student.class%type) is select name,age,class from student where class=v_class;
begin
open mycursor('101');
loop
exit when mycursor%notfound;
fetch mycursor into v_name,v_age,m_class;
dbms_output.put_line('name:'||v_name||',age:'||v_age||',class:'||m_class);
end loop;
close mycursor;
end;
/

 

select * from user_source where type ='PROCEDURE';
9、无参数存储过程

create or replace procedure test1 is
begin
dbms_output.put_line('hello_world');
end;
/
10、调用存储过程方法1
begin
test1;
end;
/

 

11、调用存储过程方法2(前提set serverout on是打开的)

exec test1;

12、有参数存储过程（注意：如果参数类型只需要varchar,不需要varchar(10)把长度表明出来）

create or replace procedure test2(v_age in student.age%type,out_name out student.name%type) is
--申明变量不用写declare，只在匿名内部类里写
v_name student.name%type;
begin
select name into v_name from student where age =v_age;
dbms_output.put_line('name:'||v_name);
out_name:=v_name;
end;
/

13、调用

declare
v_name student.name%type;
begin
test2(11,v_name);
dbms_output.put_line('222222222222222222222name:'||v_name);
end;
/

 14、存储函数，与存储过程本质没啥区别，一般是在存储过程里面调用存储函数。

create or replace function my_fun (v_a number) return number 
is 
v_ret number;
begin
v_ret :=v_a+20;
return v_ret;
end;
/
--调用存储函数
declare
v_a number;
begin
v_a:=my_fun(500);
dbms_output.put_line(v_a);
end;
/

 

15、java代码调用

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
 
public class TestProcedureTwo {
  public TestProcedureTwo() {
  }
  public static void main(String[] args ){
    String driver = "oracle.jdbc.driver.OracleDriver";
    String strUrl = "jdbc:oracle:thin:@localhost:1521:myoracle";
    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;
    try {
      Class.forName(driver);
      conn =  DriverManager.getConnection(strUrl, "system", "admin");
      CallableStatement proc = null;
      proc = conn.prepareCall("{ call dem_procedure(?,?) }");
      proc.setString(1, "kalision");
      proc.registerOutParameter(2, Types.VARCHAR);
      proc.execute();
      String testPrint = proc.getString(2);
      System.out.println("存储过程返回的值是："+testPrint);
    }
    catch (SQLException ex2) {
      ex2.printStackTrace();
    }
    catch (Exception ex2) {
      ex2.printStackTrace();
    }
    finally{
      try {
        if(rs != null){
          rs.close();
          if(stmt!=null){
            stmt.close();
          }
          if(conn!=null){
            conn.close();
          }
        }
      }
      catch (SQLException ex1) {
      }
    }
  }
}

 16、创建触发器

 语法：
 create [or replace] trigger 触发器名称
 before [after]
 insert [update] [delte]
 on 表名
 [for each row]
 declare
 begin
 end;

 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; 
CREATE OR REPLACE TRIGGER tr_del_emp 
   BEFORE DELETE --指定触发时机为删除操作前触发
   ON scott.emp 
   FOR EACH ROW   --说明创建的是行级触发器 
BEGIN

   --如果部门是101,加入到历史表。 (-20000~-20999)之间会返回调用方错误信息。

　　if :old.empno=101 then
　　raise_application_error(-20001,'101部门的人不用加入历史表');
　　else

   --将修改前数据插入到日志记录表 del_emp ,以供监督使用。

   INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )        VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate ); END; DELETE emp WHERE empno=7788; DROP TABLE emp_his; DROP TRIGGER del_emp;