Oracle学习笔记

CoCoNblz

于 2016-11-16 08:59:45 发布

阅读量318

点赞数

本文链接：https://blog.csdn.net/li503309294/article/details/53182902

版权

函数
声明：

Create or replace function uosv1.FN_GET_MISID (p_param VARCHAR2)
    Return VARCHAR2
    Is
    rtn VARCHAR2(32);
    begin
        select …….. into rtn from dual;
        return rtn;
    end FN_GET_MISID_1Q;

CREATE OR REPLACE
FUNCTION get_salary(
  Dept_no NUMBER, --注意VARCHAR2不能给精度，如：VARCHAR2(10)，其它类似
  Emp_count OUT NUMBER)
  RETURN NUMBER 
IS
  V_sum NUMBER;
BEGIN
  SELECT SUM(SALARY), count(*) INTO V_sum, emp_count
    FROM EMPLOYEES WHERE DEPARTMENT_ID=dept_no;
  RETURN v_sum;
EXCEPTION
   WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
   WHEN OTHERS THEN 
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END get_salary;

函数调用

DECLARE
  V_num NUMBER;
  V_sum NUMBER;
BEGIN
  V_sum :=get_salary(10, v_num);
  DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：'||v_sum||'，人数为：'||v_num);
END;

存储过程

CREATE OR REPLACE
PROCEDURE QueryEmp
(v_empno IN  employees.employee_id%TYPE,
 v_ename OUT employees.first_name%TYPE,
 v_sal   OUT employees.salary%TYPE) 
AS
BEGIN
       SELECT last_name || last_name, salary INTO v_ename, v_sal 
    FROM employees 
    WHERE employee_id = v_empno; 
       DBMS_OUTPUT.PUT_LINE('温馨提示:编码为'||v_empno||'的员工已经查到!');
EXCEPTION
       WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
      WHEN OTHERS THEN 
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END QueryEmp;
--调用
 DECLARE
    v1 employees.first_name%TYPE;
    v2 employees.salary%TYPE;
 BEGIN
   QueryEmp(100, v1, v2);
   DBMS_OUTPUT.PUT_LINE('姓名:'||v1);
   DBMS_OUTPUT.PUT_LINE('工资:'||v2);
   QueryEmp(103, v1, v2);
   DBMS_OUTPUT.PUT_LINE('姓名:'||v1);
   DBMS_OUTPUT.PUT_LINE('工资:'||v2);
   QueryEmp(104, v1, v2);
   DBMS_OUTPUT.PUT_LINE('姓名:'||v1);
   DBMS_OUTPUT.PUT_LINE('工资:'||v2);
END;

CREATE OR REPLACE
PROCEDURE proc_demo
(
  dept_no NUMBER DEFAULT 10,
    sal_sum OUT NUMBER,
    emp_count OUT NUMBER
  )
IS
BEGIN
    SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count
  FROM employees WHERE department_id = dept_no;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END proc_demo;

游标

declare
       --类型定义
       cursor c_job
       is
       select empno,ename,job,sal
       from emp
       where job='MANAGER';
       --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
       c_row c_job%rowtype;
begin
       for c_row in c_job loop
         dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
       end loop;
end;

Fetch游标

--Fetch游标
--使用的时候必须要明确的打开和关闭

declare 
       --类型定义
       cursor c_job
       is
       select empno,ename,job,sal
       from emp
       where job='MANAGER';
       --定义一个游标变量
       c_row c_job%rowtype;
begin
       open c_job;
         loop
           --提取一行数据到c_row
           fetch c_job into c_row;
           --判读是否提取到值，没取到值就退出
           --取到值c_job%notfound 是false 
           --取不到值c_job%notfound 是true
           exit when c_job%notfound;
            dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
         end loop;
       --关闭游标
      close c_job;
end;

--3,使用游标和while循环来显示所有部门的的地理位置（用%found属性）
declare
       --游标声明
       cursor csr_TestWhile
       is
       --select语句
       select  LOC
       from Depth;
       --指定行指针
       row_loc csr_TestWhile%rowtype;
begin
  --打开游标
       open csr_TestWhile;
       --给第一行喂数据
       fetch csr_TestWhile into row_loc;
       --测试是否有数据，并执行循环
         while csr_TestWhile%found loop
           dbms_output.put_line('部门地点：'||row_loc.LOC);
           --给下一行喂数据
           fetch csr_TestWhile into row_loc;
         end loop;
       close csr_TestWhile;
end;

--4,接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)
--CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
--定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]  

declare 
      CURSOR 
      c_dept(p_deptNo number)
      is
      select * from emp where emp.depno=p_deptNo;
      r_emp emp%rowtype;
begin
        for r_emp in c_dept(20) loop
            dbms_output.put_line('员工号：'||r_emp.EMPNO||'员工名：'||r_emp.ENAME||'工资：'||r_emp.SAL);
        end loop;
end;

Select for update

select * from test for update; 会对table test进行加锁. 此时只允许当前的session对已经存在的数据进行更新. 但其它session仍可以进行insert的操作.

如果加了for update后该语句用来锁定特定的行（如果有where子句，就是满足where条件的那些行）。当这些行被锁定后，其他会话可以选择这些行，但不能更改或删除这些行，直到该语句的事务被commit语句或rollback语句结束为止。

for update of columns 用在多表连接锁定时，可以指定要锁定的是哪几张表，而如果表中的列没有在for update of 后面出现的话，就意味着这张表其实并没有被锁定，其他用户是可以对这些表的数据进行update操作的。这种情况经常会出现在用户对带有连接查询的视图进行操作场景下。用户只锁定相关表的数据，其他用户仍然可以对视图中其他原始表的数据来进行操作。

触发器
在Oracle中用:old和:new表示执行前的行，和执行后的行。在MySQL中用old和new表示执行前和执行后的数据。

create or replace trigger test_trigger
  before insert or update on test  
  for each row
declare
  -- local variables here
begin
  :new.c := :new.a + :new.b;
end test_trigger;

Mysql语法：

delimiter //
create trigger trigger1 before insert on person 
for each row 
BEGIN 
        set NEW.pname = 'newname';
END;//

对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以在和NEW以及OLD同时使用。

非常遗憾，MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作，以免递归循环触发
对于update 只能用set 进行操作，insert与delete只能借助第二张表才能实现需要的目的

Mysql定时任务
1) 首先来看一个简单的例子来演示每秒插入一条记录到数据表

USE test;
    　　CREATE TABLE aaa (timeline TIMESTAMP);
    　　CREATE EVENT e_test_insert
    　　 ON SCHEDULE EVERY 1 SECOND
    　　 DO INSERT INTO test.aaa VALUES(CURRENT_TIMESTAMP);

2) 5天后清空test表：

    CREATE EVENT e_test
    　　 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
    　　 DO TRUNCATE TABLE test.aaa;

3) 2007年7月20日12点整清空test表：

    CREATE EVENT e_test
    　　 ON SCHEDULE AT TIMESTAMP '2007-07-20 12:00:00'
    　　 DO TRUNCATE TABLE test.aaa;

4) 每天定时清空test表：
　　

    CREATE EVENT e_test
    　　 ON SCHEDULE EVERY 1 DAY
    　　 DO TRUNCATE TABLE test.aaa;

5) 5天后开启每天定时清空test表：

    CREATE EVENT e_test
    　　 ON SCHEDULE EVERY 1 DAY
    　　 STARTS CURRENT_TIMESTAMP+ INTERVAL 5 DAY
    　　 DO TRUNCATE TABLE test.aaa;

6) 每天定时清空test表，5天后停止执行：

    CREATE EVENT e_test
    　　 ON SCHEDULE EVERY 1 DAY
    　　 ENDS CURRENT_TIMESTAMP+ INTERVAL 5 DAY
    　　 DO TRUNCATE TABLE test.aaa;

ORACLE 定时任务
1、创建测试表

    create table job_test(id integer,add_time date);

2、创建过程

    create or replace procedure prc_job_test is  
    begin  
      insert into job_test values (SEQ_TM_ID.nextval, sysdate);  
      commit;  
    end prc_job_test;

3、创建任务

declare  
      tm_job number;  
    begin  
      sys.dbms_job.submit(tm_job, --任务名称  
                          'prc_job_test;',--执行的过程  
                          sysdate,--执行时间  
                          'sysdate+1/(24*60*10)');--下次执行时间  
    end;

4、查看任务id

    select * from dba_jobs;

5、执行任务

    begin  
    dbms_job.run(41);--41为任务的id  
    end;

6、删除任务

    begin  
    dbms_job.remove(41);  
    end;

JDBC 调用oracle函数和存储过程

//Java调用存储过程和函数
 2 public class OracleDao {
 3     //调用存储过程
 4     @Test
 5     public void callProcedure() throws Exception{
 6         Connection conn = JdbcUtil.getConnection();
 7         //第一参数:in  编号 7902
 8         //第二参数:out 姓名
 9         //第三参数:out 工作
10         //第四参数:out 薪水
11         String sql = "call findEmpNameAndSalAndJob(?,?,?,?)";
12         //创建专用于调用过程或函数的对象
13         CallableStatement cstmt = conn.prepareCall(sql);
14         //为?占位符设置in、out值
15         //hibernate从0开始，jdbc从1开始
16         cstmt.setInt(1,7902);//in值
17         cstmt.registerOutParameter(2,Types.VARCHAR);//out值
18         cstmt.registerOutParameter(3,Types.VARCHAR);//out值
19         cstmt.registerOutParameter(4,Types.INTEGER);//out值
20         cstmt.execute();//抛行调用存储过程
21         //依次接收3个返回值
22         String ename = cstmt.getString(2);
23         String job = cstmt.getString(3);
24         Integer sal = cstmt.getInt(4);
25         //显示
26         System.out.println(ename+"的工作是：" + job + "，它是薪水是" + sal);
27         //关闭连接对象
28         JdbcUtil.close(cstmt);
29         JdbcUtil.close(conn);
30     }
31     //调用存储函数
32     @Test
33     public void callFunction() throws Exception{
34         Connection conn = JdbcUtil.getConnection();
35         //参数一：in  编号 数值型
36         //参数二：out 姓名 字符串型
37         //返回值：out 薪水 数值型
38         String sql = "{? = call findEmpNameAndSal(?,?)}";
39         CallableStatement cstmt = conn.prepareCall(sql);
40         cstmt.setInt(2,7788);//in
41         cstmt.registerOutParameter(3,Types.VARCHAR);//out
42         cstmt.registerOutParameter(1,Types.INTEGER);//返回值
43         cstmt.execute();//执行调用存储函数
44         String ename = cstmt.getString(3);
45         Integer sal = cstmt.getInt(1);
46         System.out.println(ename+"的薪水是"+sal);
47         //关闭连接对象
48         JdbcUtil.close(cstmt);
49         JdbcUtil.close(conn);
50     }
51 }