Oracle 语法

 

 

 

//下面为游标知识

 

declare 
--定义游标
type emp_cursor is ref cursor;
star_cursor emp_cursor;
begin
open star_cursor for select ...;
loop
      fetch star_cursor into ...;
      dbms...;
end loop;
end;

 

 

SET SERVEROUTPUT ON;
DECLARE 
--游标的声明
CURSOR c_emp is
	select ename,sal from emp;
   --定义接收游标中的 数据变量
   v_ename emp.ename%TYPE;
   v_sal emp.sal%TYPE;	
   --声明基于游标的记录来接收数据
   vr_emp c_emp%ROWTYPE;
BEGIN
   --打开游标
   open c_emp;
   --取出游标中的数据
   loop
      fetch c_emp into v_ename,v_sal;
      --检查是否到最后一条数据
      exit when c_emp%NOTFOUND;
      --打印数据
      DBMS_OUTPUT.PUT_LINE('姓名:'||v_ename||'薪水:'||v_sal);
      end loop;
   CLOSE c_emp;   
   DBMS_OUTPUT.PUT_LINE('=====================');
   open c_emp;
   loop
      fetch c_emp into vr_emp;
      exit when c_emp%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('姓名:'||vr_emp.ename||'薪水:'||vr_emp.sal);
      end loop;  
   CLOSE c_emp;    
   DBMS_OUTPUT.PUT_LINE('=====================');
   
   --上面两个都要打开关闭游标，下面for循环不用打开和关闭游标
   
   for emp_record in reverse c_emp loop
      DBMS_OUTPUT.PUT_LINE('姓名:'||emp_record.ename||'薪水:'||emp_record.sal);
      end loop; 
END;   

 

下面是while循环

 

SET SERVEROUTPUT ON;
DECLARE 
--游标的声明
CURSOR c_emp is
	select ename,sal from emp;
   --定义接收游标中的 数据变量
   v_ename emp.ename%TYPE;
   v_sal emp.sal%TYPE;	
   --声明基于表的记录来接收数据
   vr_emp c_emp%ROWTYPE;
BEGIN
   --打开游标
   if c_emp%ISOPEN = false then	
   open c_emp;
   end if;
   fetch c_emp into v_ename,v_sal;
   while c_emp%FOUND loop
   	DBMS_OUTPUT.PUT_LINE('name:'||v_ename||'sal:'||v_sal);
   	if c_emp%ROWCOUNT = 200 then
   	exit;
   	end if;
   	fetch c_emp into v_ename,v_sal;
   end loop;	
   close c_emp;
END;    

 

游标中的参数问题

 

 

 

	SET SERVEROUTPUT ON;
	DECLARE 
	--游标的声明
	CURSOR c_emp (p_deptno in emp.deptno%TYPE)
		is
		select * from emp 
		where deptno = p_deptno;
	r_deptno emp.deptno%TYPE;	
	BEGIN
		r_deptno := '30';
		for r_emp in c_emp(r_deptno)
		loop
			DBMS_OUTPUT.PUT_LINE('dempno is: '||r_deptno||'name is:'||r_emp.ename);
		end loop;
	END;

 

 

//下面是存储过程

create procedure star_pro(v_name varchar2,v_sal number ) is
begin
update emp set sal = v_sal where ename = v_name;
end;

//上面为存储过程的一般形式，下面为存储过程带上in，out参数，并通过oracle执行存储过程得到结果
create or replace procedure sp_test 
as
cursor c_group
is
select ename from emptestwhere sal > 2000 group by job;
begin
for r_group in c_group
loop
update emptest set comm = comm+3000;
end loop;
end;

create or replace procedure sp_findname 
(i_ename in varchar2,
o_sal out number)
as
begin
select sal into o_sal from emptest
where ename = i_ename;
exception
when others
then 
dbms_output.put_line('error');
end;

declare
v_sal emptest.sal%type;
begin
sp_findname('STAR',v_sal);
dbms_output.put_line('v_sal is'||v_sal);
end;

//通过java调用并返回单个结果的例子
create or replace procedure sp_inout 
    (i_id in int , o_name out varchar2 , o_job out varchar2)
as
begin
    select ename,job
        into o_name,o_job
    from emptest where empno = i_id;
end;

//通过java调用并返回一个结果集的例子
--1.新建一个包，并在包中定义一个游标test_cursor
create or replace package testpackage as
type test_cursor is ref cursor;
end;

--2.创建过程
create or replace procedure sp_getall
(id in number,p_cursor out testpackage.test_cursor)
is
begin
open p_cursor for 
select * from emp where deptno = id;
end;

//java调用存储过程一般形式
try {
			//1.加载驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2.获得连接
			Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
			//3.创建CallableStatement
			CallableStatement cs = connection.prepareCall("{call star_pro(?,?)}");
			//4.赋值
			cs.setString(1,"SCOTT");
			cs.setInt(2,30000);	
			//5.执行
			cs.execute();
			//6.关闭
			cs.close();
			connection.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

//java调用存储过程带上返回结果的形式

package star;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcConn {

	public static void main(String[] args) {
		conn();
	}
	
	public static void conn(){
		String driver = "oracle.jdbc.driver.OracleDriver";
		String user = "SCOTT";
		String password = "tiger";
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
			
			//下面是一般的oracle查询
			String sql = "select * from emptest where ename = ?";
			/*ps = conn.prepareStatement(sql);
			ps.setString(1, "SCOTT");
			rs = ps.executeQuery();
			
			while(rs.next()){
				System.out.println(rs.getString(2)+"  的工作是  "+rs.getString(3));
			}*/
			
			//下面是java调用存储过程
			CallableStatement callableStatement = conn.prepareCall("{call sp_inout(?,?,?)}");
			callableStatement.setInt(1, 7788);
			callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
			callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
			
			callableStatement.execute();
			
			String name = callableStatement.getString(2);
			String job = callableStatement.getString(3);
			
			System.out.println(name + "  " +job);
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

//java调用存储过程返回一个结果集
try {
			//1.加载驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2.获得连接
			Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
			//3.创建CallableStatement		
			CallableStatement cs = connection.prepareCall("{call sp_getall(?,?)}");
			cs.setInt(1,30);
			cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
//这里可以有两种选择  
//1.oracle.jdbc.OracleTypes.*;
//2. java.sql.Types.*;
			cs.execute();
			ResultSet rs = (ResultSet) cs.getObject(2);
			while(rs.next()){
				System.out.println("name is : "+rs.getString(2));
			}
			
			cs.close();
			connection.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

  

 

 

 

//包的规范只包含过程和函数的说明，但是没有具体代码，
//包体用于实现包规范中的过程和函数
create package star_package is
procedure update_sal(v_name varchar2,v_sal number);
function annual_income(v_name varchar2) return number;
end;
//下面是包体的创建
create package body star_package is

procedure update_sal(v_name varchar2,v_sal number)
is
begin
...
end;

function annual_income(v_name varchar2) return number
is
results number; //结果
begin
...
return results;
end;

end;

 记录

 

declare
type emp_record is record(name emp.ename%type,sal emp.sal%type)
star_record emp_record;
begin
select ename,sal into star_record
from emp where ...;
dbms_output.putline('...');
end;

//复合变量，含有多个变量

 

 

//下面为触发器

 

 

create or replace trigger emptest_ba
       before insert or update or delete
       on emptest
       for each row
declare 
       v_ename emptest.ename%type;
begin
       if inserting then
              v_ename := 'INSERT';
              :new.hiredate :=sysdate;
       elsif deleting then
              v_ename := 'DELETE';
       elsif updating then
              v_ename := 'UPDATE';       
       end if;
       update emptest set ename = v_ename where deptno = 40;
       
       if sql%notfound then
          insert into emptest(ename,sal)
          values
          (v_ename,10);
       end if;
end;