数据库 day62 Oracle(pl/sql,存储过程,触发器)

pl/sql

什么是PL/SQL?

PL/SQL（Procedure Language/SQL）

PLSQL是Oracle对sql语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。

范例1：为职工长工资，每人长10％的工资。

Update emp set sal=sal*1.1

范例2：例2: 按职工的职称长工资,总裁长1000元,经理长800元，其他人员长400元。

这样的需求我们就无法使用一条SQL来实现，需要借助其他程序来帮助完成，也可以使用pl/sql。

1.    pl/sql程序语法

1.程序语法：

declare
     说明部分    （变量说明，游标申明，例外说明〕
begin
     语句序列   （DML语句〕…
exception
     例外处理语句  
End;

2.常量和变量定义

在程序的声明阶段可以来定义常量和变量。

    变量的基本类型就是oracle中的建表时字段的变量如char, varchar2, date,number, boolean, long)，

定义语法：varl  char(15);

               Psal number(9,2);

说明变量名、数据类型和长度后用分号结束说明语句。

常量定义：married   boolean:=true

--boolean不能直接输出

declare
  age   number(3);
  marry boolean := true;    --boolean不能直接输出
  pname varchar2(10) := 'zhangsan';
begin
  age := 100;
  dbms_output.put_line(age);
  if marry then
    dbms_output.put_line('true');
  else
    dbms_output.put_line('false');
  end if;
  dbms_output.put_line(pname);
end;

    引用变量

Myname emp.ename%type;

引用型变量，即my_name的类型与emp表中ename列的类型一样

在sql中使用into来赋值

declare
  emprecemp.ename%type;
begin
 select t.ename into emprec from emp t where t.empno = 7369;
 dbms_output.put_line(emprec);
end;

    记录型变量

Emprec emp%rowtype

记录变量分量的引用

emp_rec.ename:='ADAMS';

declare
  pemp%rowtype;
begin
 select * into p from emp t where t.empno = 7369;
 dbms_output.put_line(p.ename || ' ' || p.sal);
end;

3. if分支

语法1：

          IF   条件 THEN 语句1;

      语句2;

      END IF;

语法2：

          IF  条件 THEN  语句序列1；  

      ELSE   语句序列 2；

      END   IF；

语法3：

IF   条件 THEN 语句;

ELSIF 条件  THEN  语句;

ELSE 语句;

END IF;

范例1：如果从控制台输入1则输出我是1

declare

  pnum number := #

begin

  if pnum =1 then

    dbms_output.put_line('我是1');

  end if;

end;

范例2：如果从控制台输入1则输出我是1否则输出我不是1

declare

  mynum number := #

begin

  if mynum =1 then

    dbms_output.put_line('我是1');

  else

    dbms_output.put_line('我不是1');

  end if;

end;

范例3:判断人的不同年龄段18岁以下是未成年人，18岁以上40以下是成年人，40以上是老年人

declare

  mynum number := #

begin

  if mynum <18 then

    dbms_output.put_line('未成年人');

  elsif mynum >=18 and mynum <40 then

    dbms_output.put_line('中年人');

  elsif mynum >=40 then

    dbms_output.put_line('老年人');

  end if;

end;

 

4.LOOP循环语句

其中语法2比较常用

语法1：

WHILE total  <= 25000  LOOP

.. .

total : = total + salary;

END  LOOP;

 

语法2：

Loop

EXIT [when  条件];

……

End loop

语法3：

FOR  I   IN   1 . . 3   LOOP

语句序列 ;

END   LOOP ;

范例:使用语法1输出1到10的数字

declare

  step number :=1;

begin

  while step <=10 loop

    dbms_output.put_line(step);

    step := step + 1;

  end loop;

end;

范例:使用语法2输出1到10的数字

declare

  step number :=1;

begin

  loop

    exit when step >10;

    dbms_output.put_line(step);

    step := step + 1;

  end loop;

end;

范例:使用语法3输出1到10的数字

declare

  step number :=1;

begin

  for stepin 1 ..10 loop

    dbms_output.put_line(step);

  end loop;

end;

5.游标Cursor

在写java程序中有集合的概念，那么在pl/sql中也会用到多条记录，这时候我们就要用到游标，游标可以存储查询返回的多条数据。

语法：

       CURSOR  游标名  [ (参数名 数据类型,参数名 数据类型,...)]  IS  SELECT  语句;

例如：cursor c1 is select ename from emp;

游标的使用步骤：

    打开游标：      open c1;    (打开游标执行查询)

    取一行游标的值：fetch c1 into pjob; (取一行到变量中)

    关闭游标：       close c1;(关闭游标释放资源)

    游标的结束方式   exit when c1%notfound

    注意： 上面的pjob必须与emp表中的job列类型一致：

              定义：pjob  emp.empjob%type;

范例1：使用游标方式输出emp表中的员工编号和姓名

declare

  cursor pcis

    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;

范例2：按员工的工种长工资,总裁1000元，经理长800元其，他人员长400元。

declare

  cursor pcis

    select *from myemp;

  addsal myemp.sal%type;

  pemp   myemp%rowtype;

begin

  open pc;

  loop

    fetch pc

      into pemp;

    exit when pc%notfound;

    if pemp.job ='PRESIDENT' then

      addsal := 1000;

    elsif pemp.job ='MANAGER' then

      addsal := 800;

    else

      addsal := 400;

    end if;

    update myemp tset t.sal = t.sal + addsalwhere t.empno = pemp.empno;

  end loop;

  close pc;

end;

 

范例3：写一段PL/SQL程序，为部门号为10的员工涨工资。

declare

  cursor pc(dno myemp.deptno%type)is

    select empnofrom myempwhere deptno = dno;

  pno myemp.empno%type;

begin

  open pc(20);

  loop

    fetch pc

      into pno;

    exit when pc%notfound;

    update myemp tset t.sal = t.sal +1000 where t.empno = pno;

  end loop;

  close pc;

end;

6.例外

例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。

系统定义例外:

no_data_found    (没有找到数据)

too_many_rows          (select …into语句匹配多个行)

zero_divide   ( 被零除)

value_error     (算术或转换错误)

timeout_on_resource      (在等待资源时发生超时)

范例1：写出被0除的例外的plsql程序

declare

  pnum number;

begin

  pnum := 1 /0;

exception

  when zero_dividethen

    dbms_output.put_line('被0除');

  when value_errorthen

    dbms_output.put_line('数值转换错误');

  when others then

    dbms_output.put_line('其他错误');

end;

 

用户也可以自定义例外，在声明中来定义例外

DECLARE

My_job  char(10);

v_sal  emp.sal%type;

No_data   exception;

cursor c1 is select distinct job fromemp    order by job;

如果遇到异常我们要抛出raiseno_data;

范例：查询部门编号是50的员工

declare

  no_emp_found exception;

  cursor pempis

    select t.enamefrom emp twhere t.deptno =50;

  pename emp.ename%type;

begin

  open pemp;

  fetch pemp

    into pename;

  if pemp%notfound then

    raise no_emp_found;

  end if;

  close pemp;

exception

  when no_emp_foundthen

    dbms_output.put_line('没有找到员工');

  when others then

    dbms_output.put_line('其他错误');

end;

 2.存储过程

存储过程（StoredProcedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（in参数out返回值 如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

 

创建存储过程语法：

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)] 

AS

begin

       PLSQL子程序体；

End;

或者

 

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)] 

is

begin

       PLSQL子程序体；

End  过程名;

 

范例：创建一个输出helloword的存储过程

create or replace procedure helloworldis

begin

  dbms_output.put_line('helloworld');

end helloworld;

 

调用存储过程

在plsql中调用存储过程(pl/sql designer 可以用右键 test 图形界面测试)

begin

  -- Call the procedure       

  helloworld;

end;

 

范例2：给指定的员工涨100工资，并打印出涨前和涨后的工资

分析：我们需要使用带有参数的存储过程

create or replace procedure addSal1(enoin number)is

  pemp myemp%rowtype;

begin

  select *into pempfrom myempwhere empno = eno;

  update myempset sal = sal +100 where empno = eno;

  dbms_output.put_line('涨工资前' || pemp.sal ||'涨工资后' || (pemp.sal +100));

end addSal1;

调用

begin

  -- Call the procedure

  addsal1(eno => 7902);     

  commit;

end;

3.存储函数

语法:

create or replace function 函数名(Name in type, Name in type, ...)return 数据类型 is

  结果变量 数据类型;

begin

 

 return(结果变量);

end函数名;

 

存储过程和存储函数的区别

一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。

但过程都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和中实现返回多个值。

 

范例：使用存储函数来查询指定员工的年薪

create or replace function empincome(enoin emp.empno%type)return number is

  psal  emp.sal%type;

  pcomm emp.comm%type;

begin

  select t.salinto psalfrom emp twhere t.empno = eno;

  return psal *12 + nvl(pcomm,0);

end;

使用存储过程来替换上面的例子

create or replace procedure empincomep(enoin emp.empno%type, incomeout number)is

  psal emp.sal%type;

  pcomm emp.comm%type;

begin

  select t.sal, t.comminto psal, pcommfrom emp twhere t.empno = eno;

  income := psal*12+nvl(pcomm,0);

end empincomep;

调用：

declare

  income number;

begin

  empincomep(7369, income);

  dbms_output.put_line(income);

end;

使用java代码调用存储过程和函数

	/*Class.forName("oracle.jdbc.OracleDriver");
	Connection conn = null;
	conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl",
			"scott", "tiger");
	CallableStatement call = conn.prepareCall("{call countyearsal(?,?)}");
	call.setInt(1, 7369);

	call.registerOutParameter(2, OracleTypes.NUMBER);
	call.execute();
	int sum = call.getInt(2);
	System.out.println(sum);*/

	Class.forName("oracle.jdbc.OracleDriver");
	Connection conn = null;
	conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl",
			"scott", "tiger");
	CallableStatement call = conn.prepareCall("{?= call countyearsal1(?)}");
	call.registerOutParameter(1, OracleTypes.NUMBER);
	call.setInt(2, 7369);

	call.execute();
	int sum = call.getInt(1);
	System.out.println(sum);

在out 参数中中使用光标(集合),解决参数过多

查询某个部门中的所有员工信息 ---> 集合

包头
CREATE OR REPLACE PACKAGE MYPACKAGE AS 

  type empcursor is ref cursor;
  procedure queryemplist(dno in number,empList out empcursor);

END MYPACKAGE;


包体
CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS

  procedure queryemplist(dno in number,empList out empcursor) AS
  BEGIN

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

  END queryemplist;

END MYPACKAGE;

存储过程/存储方法/包

public class TestOracle {
/*
 * create or replace procedure queryempinfo(eno in number,
                                            pename out varchar2,
                                            psal   out number,
                                            pjob   out varchar2)
 */
	@Test
	public void testProcedure(){
		//{call <procedure-name>[(<arg1>,<arg2>, ...)]}
		String sql = "{call queryempinfo(?,?,?,?)}";

		Connection conn = null;
		CallableStatement call = null;
		try {
			conn = JDBCUtils.getConnection();
			call = conn.prepareCall(sql);

			//对于in参数，赋值
			call.setInt(1, 7839);

			//对于out参数，申明
			call.registerOutParameter(2, OracleTypes.VARCHAR);
			call.registerOutParameter(3, OracleTypes.NUMBER);
			call.registerOutParameter(4, OracleTypes.VARCHAR);

			//执行
			call.execute();

			//取出结果
			String name = call.getString(2);
			double sal = call.getDouble(3);
			String job = call.getString(4);
			System.out.println(name+"\t"+sal+"\t"+job);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, null);
		}
	}

/*
 * create or replace function queryempincome(eno in number)
return number
 */
	@Test
	public void testFunction(){
		//{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
		String sql = "{?=call queryempincome(?)}";
		Connection conn = null;
		CallableStatement call = null;
		try {
			conn = JDBCUtils.getConnection();
			call = conn.prepareCall(sql);

			//对于out参数，申明
			call.registerOutParameter(1, OracleTypes.NUMBER);

			//对于in参数，赋值
			call.setInt(2, 7839);

			//执行
			call.execute();

			//取出结果
			double income = call.getDouble(1);
			System.out.println(income);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, null);
		}		
	}

/*
 * CREATE OR REPLACE PACKAGE MYPACKAGE AS 

  type empcursor is ref cursor;
  procedure queryemplist(dno in number,empList out empcursor);

END MYPACKAGE;
 */
	@Test
	public void testCursor(){
		//{call <procedure-name>[(<arg1>,<arg2>, ...)]}
		String sql = "{call MYPACKAGE.queryemplist(?,?)}";

		Connection conn = null;
		CallableStatement call = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			call = conn.prepareCall(sql);

			//对于in参数，赋值
			call.setInt(1, 20);

			//对于out参数，申明
			call.registerOutParameter(2, OracleTypes.CURSOR);

			//执行
			call.execute();

			//取出结果
			rs = ((OracleCallableStatement)call).getCursor(2);
			while(rs.next()){
				String name = rs.getString("ename");
				double sal = rs.getDouble("sal");

				System.out.println(name+"\t"+sal);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, rs);
		}		
	}
}

JDBCUtils

public class JDBCUtils {
	private static String driver = "oracle.jdbc.OracleDriver";
	private static String url = "jdbc:oracle:thin:@192.168.56.101:1521:orcl";
	private static String user = "scott";
	private static String password = "tiger";

	static{
		//DriverManager.registerDriver(driver);
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	public static Connection getConnection(){
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/*
	 * 运行Java程序：
	 * java -Xms100M -Xmx200M HelloWorld
	 * 
	 * 线程的诊断工具：java thread dump ---> 文本文件
	 */
	public static void release(Connection conn,Statement st, ResultSet rs){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				rs = null;//----> Java GC
			}
		}
		if(st != null){
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				st = null;
			}
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				conn = null;
			}
		}
	}
}

 

4.触发器

数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

 

触发器可用于

数据确认 

实施复杂的安全性检查

做审计，跟踪表上所做的数据操作等

数据的备份和同步

 

触发器的类型

              语句级触发器 ：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。

              行级触发器（FOR EACH ROW） ：触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。

语法：

CREATE [or REPLACE] TRIGGER  触发器名

  {BEFORE | AFTER}

  {DELETE | INSERT | UPDATE [OF 列名]}

  ON  表名

  [FOR EACH ROW [WHEN(条件) ] ]

declare

begin

  PLSQL 块

End 触发器名

范例：插入员工后打印一句话“一个新员工插入成功”

create or replace trigger testTrigger

  after insert on person  

declare

  -- local variables here

begin

  dbms_output.put_line('一个员工被插入');

end testTrigger;

 

范例：不能在休息时间插入员工

create or replace trigger validInsertPerson

  before insert on person

 

declare

  weekend varchar2(10);

begin

  select to_char(sysdate,'day')into weekendfrom dual;

  if weekendin ('星期一')then

    raise_application_error(-20001, '不能在非法时间插入员工');

  end if;

end validInsertPerson;

当执行插入时会报错

在触发器中触发语句与伪记录变量的值

触发语句	:old	:new
Insert	所有字段都是空(null)	将要插入的数据
Update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都是空(null)

范例：判断员工涨工资之后的工资的值一定要大于涨工资之前的工资

create or replace trigger addsal4p

  before update of salon myemp

  for each row

begin

  if :old.sal >= :new.salthen

    raise_application_error(-20002,'涨前的工资不能大于涨后的工资');

  end if;

end;

调用

update myemp t set t.sal = t.sal - 1;

报错

数据库建模(Power Designer) 

表的集中关系类型

一对多

多的一端引一的一端作为外键来表示,
箭头指向的表是一的一端，箭头背向的表多数情况是多的一端（特例一对一）
在实体类中一的一端的实体类有多的一端的实力类的集合属性

多对一

多的一端引一的一端作为外键来表示,
箭头指向的表是一的一端，箭头背向的表多数情况是多的一端（特例一对一）
在实体类中多的一端的实体类有一的一端的实力类的对象属性

多对多
由三张表来组成，中间的表是关系表箭头分别从中间表向两端表指向，中间表有且仅有两张表的主键字段作为联合主键

一对一
箭头所指向的一端是一的一端， 箭头背向的表引箭头指向的表的主键作为外键并且设置为自己的主键

数据导出导入(PL/sql devrloper)

方式1

方式2

导出设置

用户的导出

用户导出不包含数据

可以导出视图,序列,存储过程,触发器等

数据导入

连接远程数据库服务器

方式一:

配置

数据库安装目录的\oracle\product\10.2.0\db_1\NETWORK\ADMIN 下

tnsnames.ora 文件

配置一个

ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =远程ip地址)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 实例名如(orcl))
    )
  )

登陆时选择 新配置的ORCL1 

输入账号密码 即可登陆远程数据库

方式二:

方式三:

添加账户

登陆直接点