Oracle PL/SQL 入门

最新推荐文章于 2022-06-30 11:52:18 发布

kkemanon

最新推荐文章于 2022-06-30 11:52:18 发布

阅读量346

点赞数

文章标签： plsql Oracle

本文链接：https://blog.csdn.net/skieske/article/details/54138118

版权

===================================== ====== PL/SQL ========== =========================

语法：

[declare]

     --定义变量，游标，例外

begin

     --pl/sql程序体

[exception]

     --处理异常

end;  --分号

-----------------------------------------------------------------------------------------------------------------------------------

declare

i number:=1;   --orcale 中使用 :=进行变量赋值

PI constant number:=3.14; --常量的关键字

pemp emp%rowtype; --记录型变量(存放了表中的一个行数据)

pname emp.ename%type; --引用型变量(引用了表中某一列的类型)

begin

  i:=i+1;

  dbms_output.put_line('i的值是:' || i);

  --PI :=PI+1; --报错

  dbms_output.put_line(PI);

  select * into pemp from emp where empno=7369;

  dbms_output.put_line('员工的编号: '||pemp.empno||'姓名: '||pemp.ename);



  select ename into pname from emp where empno=7499;

  dbms_output.put_line('7499的姓名: '||pname);
end;

======================= ========= ====== if ========== ==========================================

语法：

if 条件  then

  语句；

elsif 条件 then

  语句；

else

  语句

end if;

有 if 就要有 end if;

--从控制台接收数字,如果是1 则输出: 我是1

declare

pnum number:=&pnum; -- & 表示从控制台接收参数

begin

  if pnum = 1 then

    dbms_output.put_line('我是1');

  end if;

end;

--从控制台接收数字,如果是1 则输出: 我是1,反之输出我不是__

declare

pnum number:=&pnum;

begin

   if pnum = 1 then

     dbms_output.put_line('我是1');

  else

    dbms_output.put_line('我是:'||pnum);

end if ;

end;

--从控制台接收年龄，

--如果年龄在18岁以下，输出未成年人。

--18~40成年人 40以上老年人

declare

age number:=&age;

begin

if age<18 then

  dbms_output.put_line('未成年');

   elsif age>=18 and age<40 then  -- 注意 oracle中用的是 elsif

    dbms_output.put_line('成年人');

     else

      dbms_output.put_line('老年人');

end if;

========================================== loop ============================================

语法1：

while 条件 loop 循环体。。。 end loop;

语法2：(常用)

loop exit when 退出循环的条件； end loop;

for 变量（i） in 范围（1..10） loop

declare

i number := 1;

begin

  while i<=10 loop

    dbms_output.put_line(i);

    i:=i+1;

  end loop;

end;

--使用语法2在控制台打印输出1~10（掌握）

declare

i number:=1;

begin

  loop

    dbms_output.put_line(i);

    i:=i+1;

     exit when i >10 ;

  end loop;

end;

--使用语法3在控制台打印输出1~10

begin

for i in 1..10 loop

dbms_output.put_line(i);

end loop;

end;

 
 ======================================= 游标<光标> ================================== 
 === 

游标：就是oracle中存放一个结果集

声明语法：

cursor 游标名称 is 查询语句;

使用游标：

open 游标名称; --打开游标

loop --循环遍历数据

fetch 游标名称 into 变量（记录型变量）；

exit when 游标%notfound;

end loop;

close 游标名称;

--输出所有的员工信息(光标实现)

declare

cursor pc is select * from emp;

pemp emp%rowtype;

begin

open pc;

  loop

  fetch pc into pemp;

exit when pc%notfound;

dbms_output.put_line(pemp.empno||'--'||pemp.ename);

  end loop;

close pc;

end;

--输出指定部门的员工信息

-- 给游标指定参数

declare

cursor pc(dno number) is select * from emp where deptno = dno;

pemp emp%rowtype;

begin

  open pc(20);

   loop

     fetch pc into pemp;

     exit when pc%notfound;

      dbms_output.put_line(pemp.ename||'--'||pemp.empno);

   end loop;

  close pc;

======================================= 异常 ======== == ========== == ==== =========== ===

declare

i number:= 1;

begin

  i:=5/0;

  --i:='abc';

exception --处理异常

   when zero_divide then

     dbms_output.put_line('除数不能是0');

     when value_error then

       dbms_output.put_line('设置错误');

       when others then

         dbms_output.put_line('其他异常');

end;

--使用记录型变量存放多条数据

declare

pemp emp%rowtype;

begin

  select * into pemp from emp;

   exception

     when too_many_rows then

       dbms_output.put_line('返回多条记录');

       when others then

         dbms_output.put_line('其他异常');

end;

--查询部门编号为50 的员工

-- 自定义异常

declare

cursor pc is select * from emp where deptno=50;

pemp emp%rowtype;

no_emp_data exception;

begin

  open pc;

  loop

   fetch pc into pemp;

    if pc%notfound then

      raise no_emp_data;  ---抛出一个异常

    end if;

   end loop;

  close pc;

   exception

     when no_emp_data then

       dbms_output.put_line('没有发现员工信息');

end;

========================================= 存储过程 ======== ==============================

定义：定义了一段pl/sql程序，事先编译好，存放到数据库端，供其他程序员来调用。

语法：

create [or replace] procedure 过程名(参数1 in|out 参数类型)

as|is

--声明变量，游标相当于declare

beign

--定义一段pl/sql程序体

end;

--给指定的员工涨工资，并且打印出涨前跟涨后的工资

--引用型变量(引用了表中某一列的类型)

--记录型变量,存储了一行的数据

create or replace procedure emp_add_sal(eno in number)

psal emp.sal%type;

-- psal number;

pemp emp%rowtype;

begin

select sal into psal from emp where empno = eno;

dbms_output.put_line('涨前工资: '||psal);

update emp set sal=sal+100 where empno = eno;

commit;

select * into pemp from emp where empno = eno;

dbms_output.put_line('涨后工资: '||pemp.sal);

end;

--存储过程调用

begin

emp_add_sal(7369);

end;

--调用方式2 <不推荐>

call emp_add_sal(7369);

--根据员工编号查询年薪

--有返回值

create or replace procedure emp_totalsal(eno in number,totalsal out number)

begin

select sal*12+nvl(comm,0) into totalsal from emp where empno = eno;

end;

--调用

declare

sal number;

begin

emp_totalsal(7369,sal);

dbms_output.put_line(sal);

end;

--采用第二种（不适用）

call pro_emp_totalsal(7369,); --call 调用只适用于没有输出参数的存储过程

====================================== 存储函数 ======== == ===================

存储过程与存储函数的区别：

存储过程能实现功能，存储函数也可以实现

语法不同：存储函数一定是有返回值。并且在函数的程序体内必须返回一个变量；变量类型跟声明返回数据类型保持一致

存储函数可以用在select语句中；

语法：

create or replace function 函数名称 (参数 in|out 参数类型) return 数据类型

as|is

--定义一些变量，游标

begin

return 变量； --变量类型要跟返回的数据类型保持一致

end;

--使用存储函数，查询指定员工的年薪

create or replace function fun_totalsal(eno in number ) return number

as

totalsal number;

begin

select sal*12+nvl(comm,0) into totalsal from emp where empno =eno;

return totalsal;

end;

--调用存储函数

declare

totalsal number;--定义变量来接收返回值

begin

totalsal:=fun_totalsal(7369);

dbms_output.put_line(totalsal);

end;

--存储函数带一个输出参数(既有返回值,又要有输出)

create or replace function fun_totalsal_out(eno number,pname out emp.ename%type) return number

as

totalsal number;

begin

  select sal*12+nvl(comm,0) into totalsal from emp where empno =eno;

  select ename into pname from emp where empno = eno;

  return totalsal;

end;

--调用

declare

totalsal number;

pname varchar2(100);

begin

  totalsal:=fun_totalsal_out(7369,pname);

  dbms_output.put_line(totalsal);

  dbms_output.put_line(pname);

end;

--查询指定员工的信息，部门名称

create or replace function fun_emp_dname (dno number) return varchar2

as

deptname varchar2 ( 200 );

--注意声明varchar2类型长度一定要给指定

begin

select dname into deptname from dept where deptno = dno;

return deptname;

end;

select ename, fun_emp_dname(deptno) from emp where deptno = 10;

--查询指定部门员工信息（使用存储过程）--sys_refcursor 引用游标类型

create or replace procedure pro_emplist(dno in number,emplist out sys_refcursor)

as

begin

open emplist for select * from emp where deptno = dno;

end;

--调用

declare

emps sys_refcursor;

pemp emp%rowtype;

begin

  pro_emplist(20,emps);

   loop

     fetch emps into pemp;

     exit when emps%notfound;

     dbms_output.put_line(pemp.ename);

   end loop;

   close emps;

end;

===================================== jdbc调用oracle对象（掌握） ======= == =========================

导入jar包

PreparedStatement :

作用: 1. 可以提前占位, 防SQL注入

2.提高了效率<查询时候先去数据库找缓存>

   
   public class OracleTest {
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@192.168.68.10:1521:orcl";
	String username = "scott";
	String password = "admin";
	 
	@Test
	//查询所有的
	public void testFindEmpList(){
		try {
			//加载驱动
			Class.forName(driver);
			//获取连接
			Connection conn = DriverManager.getConnection(url, username, password);
			//获取 PreparedStatement 对象
			PreparedStatement pst = conn.prepareStatement("select * from emp");
			//执行查询
			ResultSet rs = pst.executeQuery();
			//处理结果
			while(rs.next()){
			//getObject(i)获取第几列的值，getString("列名")，getInt("列名")		
			System.out.println(rs.getObject(1)+"--"+rs.getString("ename"));
			}
			//释放资源
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
}

调用过程无输出参数的

Connection 方法

返回值

    
    
	/*
	 * 调用存储过程
 
    --存储过程 
	  create or replace procedure emp_add_sal(eno in number) 
	  as
	    psal emp.sal%type;  
	    pemp emp%rowtype; 
	  begin
	    select sal into
	    psal from emp where empno = eno; 
	    dbms_output.put_line('涨前工资: '||psal);
		update emp set sal=sal+100 where empno = eno; 
		commit;
		select * into pemp from emp where empno = eno; 
	    dbms_output.put_line('涨后工资: '||pemp.sal);
	  end;
	
	{call <procedure-name>[(<arg1>,<arg2>, ...)]}	 */
	@Test
	public void testCallProcedure(){
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, username, password);

			CallableStatement cst = conn.prepareCall( "{call emp_add_sal(?)}");

			cst.setInt(1,7369);
			cst.executeQuery();
			cst.close();
			conn.close();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

调用过程（有输出参数）

CallableStatement:方法

   
   	/*
    调用存储过程
	 --根据员工编号查询年薪
		create or replace procedure emp_totalsal(eno in number,totalsal out number)
		as
		begin
  		select sal*12+nvl(comm,0) into totalsal from emp where empno = eno;
		end;
	 
	 */
	// {call <procedure-name>[(<arg1>,<arg2>, ...)]}
	@Test
	public void testCallProcedureOutParam(){
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, username, password);
			CallableStatement cst = conn.prepareCall("{call emp_totalsal(?,?)}");
			cst.setInt(1,7369);

		  cst.registerOutParameter(2,OracleTypes.NUMBER);
 
			cst.executeQuery();
			//获取第二个输出参数的值
			System.out.println(cst.getObject(2));
			//释放资源
			cst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

调用函数

    
    /*
	--调用函数
	--使用存储函数，查询指定员工的年薪
	create or replace function fun_totalsal(eno in number ) return number
	as
	  totalsal number;
	begin
	  select sal*12+nvl(comm,0) into totalsal from emp where empno =eno;
      return totalsal;
	end; 
	   {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
	 */
	@Test
	public void testCallFunction(){
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, username, password);
			CallableStatement cst = conn.prepareCall("{?= call fun_totalsal(?)}");
			cst.setInt(2,7369);
			cst.registerOutParameter(1,OracleTypes.NUMBER);
			cst.executeQuery();
			//获取第一个参数输出的值
			System.out.println(cst.getInt(1));
			//释放资源
			cst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

调用存储过程输出参数为游标类型的:

    
    /*
	 --调用存储过程输出参数为游标类型的
	 
	 --查询指定部门员工信息（使用存储过程）
	  --sys_refcursor 引用游标类型
     create or replace procedure pro_emplist(dno in number,emplist out sys_refcursor)
     as
	 begin
      open emplist for select * from emp where deptno = dno;
     end;

	  {call <procedure-name>[(<arg1>,<arg2>, ...)]}
	 */
	@Test
	public void testCallProcedureOutCursor(){
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, username, password);
			
			CallableStatement cst = conn.prepareCall("{call pro_emplist(?,?)}");
			cst.setInt(1,20);
			cst.registerOutParameter(2,OracleTypes.CURSOR);
			cst.execute();
			
			//获取第二个输出参数的值
		 ResultSet rs = ((OracleCallableStatement)cst).getCursor(2);
			
			while(rs.next()){
				System.out.println(rs.getObject(2));
			}
			//释放资源
			rs.close();
			cst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

-----------------------------------------------------------------------------------------------------------------------------------

上面代码可以简化:

用注解

  
      private Connection conn;
    private PreparedStatement pst;
    private ResultSet rs;
    private CallableStatement cst;

@Before
	public void init() throws ClassNotFoundException, SQLException{
		// 加载驱动
		Class.forName(driver);
		// 获取连接
		conn = DriverManager.getConnection(url, username, password);
	}
	
	@After
	public void after() throws SQLException{
		// 释放资源
		if(null != conn){
			conn.close();	
		}
		if (null != pst) {
			pst.close();
		}
		if (null != rs) {
			rs.close();
		}
		if (null != cst) {
			cst.close();
		}
	}

-----------------------------------------------------------------------------------------------------------------------------------

================================================ 触发器 ======== ===============================

理解：好比监视器，触发器在对表中的数据进行操作（增删改），触发器就会工作。如果操作合法了，才能对数据进行改变

语法：create [or replace] trigger

after|before

insert|update|delete

on 表名

[for each row] ---行级触发器

declare

begin

end;

--添加一条数据，自动控制台输出一句‘数据添加成功了

create or replace trigger tri_after_add

after

insert

on person1

declare

begin

dbms_output.put_line('数据添加成功了');

end;

insert into person1 values(15,'赵六',1);

commit;

--不能在休息（周四周六日）时间添加员工

raise_application_error(p1,p2); --错误提示框

p1：错误编码范围在 -20001 到-20999

p2:错误信息

select to_char(sysdate,'day') from dual;

create or replace trigger tri_before_add

before

insert

on person1

declare

weekend varchar2(50);

begin

  select to_char(sysdate,'day') into weekend from dual;

  if weekend in ('thursday','saturday','sunday') then

    raise_application_error(-20002,'不能在休息时间添加员工');

   end if;

end;

insert into person1 values(211221,'赵六',1);

commit;

--不能给员工降薪

for each

row :行级触发器表示针对每一条数据操作都会触发，

只有用到 :old :new必须声明行级触发器

create or replace trigger tri_before_update

before

update

on emp

for each row

begin

  if :old.sal > :new.sal then

    raise_application_error(-20003,'不能降薪!');

  end if;

end;

update emp set sal=sal-100 where empno=7369;

commit;

--=======触发器的应用

--目标:主键不在显示输入,自动生成

insert into person1(name,gender) values('李四1',2);

commit;

--创建触发器

create or replace trigger tri_before_add

before

insert

on person1

for each row

begin

select seq_person.nextval into :new.id from dual;

end;

kkemanon

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle PL/SQL 入门

======================================================== PL/SQL =======================================================语法：[declare] --定义变量，游标，例外begin --pl/sql程序体[except
复制链接

扫一扫

Oracle PL/SQL 入门

“相关推荐”对你有帮助么？