MySQLOracle笔记4-pl/sql-分支/循环/游标/异常/存储/调用/触发器

一.pl/sql(Procedure Language/SQL)编程语言
1.概念
PL/SQL是Oracle数据库对SQL语句的恢弘。在一般SQL语句之采取上搭了编程语言的特点,所以PL/SQL把多少操作和查询语句组织以PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的作用还是计算。PL/SQL
只发生 Oracle 数据库有。 MySQL 目前未支持 PL/SQL 的。

2.变量和常量
宣示普通变量:
     v_name varchar2(30) := ‘tom’; 
(:=为赋值符号;=为比较符号,相当于java中的==);
宣称引用型变量:
     v_sal emp.sal%type := 100;   
声明的v_sal变量与emp表中sal字段的种类一致;
扬言记录型变量:
     v_row emp%rowtype;
记录型变量相当于java中的resultset,用来存储整张表中的数据;
宣示常量:
     v_gender constant number(1) number(1) := 1;   

3.分支语句
语法一:if —then—end if;
语法二:if —then—else—end if;
语法三:if —then—elsif—then—-else—-end if;
举例:
–年龄低于18,显示未成年人,18-60,显示成年人,60上述显示老人
declare
     v_age number(8) :=#
begin
     if v_age < 18 then
         dbms_output.put_line(‘未成年人’);
     elsif v_age >= 18 and v_age <= 60 then
         dbms_output.put_line(‘成年人’);
     else
         dbms_output.put_line(‘老年人’);
     end if;
end;

4.循环语句
语法一:loop—exit when—-end loop;
举例:
–输出1–100的数
declare
  v_num number(8) := 1;
begin
  loop
   exit when v_num > 100;
   dbms_output.put_line(v_num);
   v_num := v_num + 1;
  end loop;
end;

语法二:while—loop—-end loop;
declare
  v_num number(8) := 1;
begin
  while v_num <= 100 loop
   dbms_output.put_line(v_num);
   v_num := v_num + 1;
  end loop;
end;

语法三:for—in 起始值..终止值—loop—end loop;
declare
  v_num number(8) := 1;
begin
  for v_num in 1 .. 100 loop
   dbms_output.put_line(v_num);
  end loop;
end;

5.游标(cursor)
企图:用来接纳多漫漫数结果,相当于java中之ResultSet
语法:cursor 游标名称 is sql 查询语句;
使用:
     open 游标名称
     loop
         fetch 游标名称 into 记录型变理
         exit when 游标名称%notfound;
             逻辑处理
     end loop;
     close 游标名称;
举例:
–打印emp表的所有消息
DECLARE
  CURSOR c_emp IS SELECT * FROM emp;
  v_row emp%ROWTYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_row;
          EXIT WHEN c_emp%NOTFOUND;
          dbms_output.put_line(v_row.ename||’–‘||v_row.job);
   END LOOP;
   CLOSE c_emp;
END;

–带参数的游标
DECLARE
   CURSOR c_emp(v_no1 NUMBER, v_no2 NUMBER) IS SELECT * FROM emp
WHERE deptno = v_no1 OR deptno = v_no2;
   v_row emp%ROWTYPE;
BEGIN
  OPEN c_emp(10,20);–传入部门编号deptno
  LOOP
   FETCH c_emp INTO v_row;
   EXIT WHEN c_emp%NOTFOUND;
   dbms_output.put_line(v_row.ename||’==’||v_row.job);
  END LOOP;
  CLOSE c_emp;
END;

6.异常
exception—when—-then
–预定义格外
DECLARE
  v_num NUMBER(3);
  BEGIN
   v_num := 10000;
   EXCEPTION
    WHEN value_error THEN
     v_num := 999;
     dbms_output.put_line(v_num);
  END;

–于定义格外
   DECLARE
   V_AGE NUMBER(8) := &NUM;
   EXC_AGE EXCEPTION; –声明异常
  BEGIN
   IF V_AGE > 150 THEN
    RAISE EXC_AGE;
   END IF;
  EXCEPTION
   WHEN EXC_AGE THEN
    RAISE_APPLICATION_ERROR(-20001, ‘illegal age’);
  END;

二.储存过程
概念:一截为命名的plsql,预编译到了数据库中
语法:
     create or replace procedure 存储过程名字(参数1 [in]/out
数据类型)
         as | is
         begin

        end;
例子1:
  –存储过程,打印指定员工的年薪
  create or replace procedure pro_emp_sal(v_no number) is
  v_sal number(8, 2);
begin
  select sal * 12 + nvl(comm, 0) into v_sal from emp where empno =
v_no;
  dbms_output.put_line(v_sal);
end;
–方法一致调用存储过程
  call pro_emp_sal(7788);
–方法二调整用存储过程
begin
  pro_emp_sal(7788);
end;   

事例2:带out参数的囤积过程
CREATE OR REPLACE PROCEDURE pro_emp_sal2(v_no NUMBER, v_yearsal OUT
NUMBER) IS
BEGIN
  SELECT sal*12 + NVL(comm,0) INTO v_yearsal FROM emp WHERE empno =
v_no;
END;
–只能以办法二调用
DECLARE
  v_sal NUMBER(8,2);
  BEGIN
   pro_emp_sal2(7788,v_sal);
   dbms_output.put_line(v_sal);
  END;

三.存储函数
–存储函数
CREATE OR REPLACE FUNCTION fun_emp_sal(v_no NUMBER)
RETURN NUMBER
IS
v_sal NUMBER(8,2);
BEGIN
  SELECT sal*12+NVL(comm,0) INTO v_sal FROM emp WHERE empno = v_no;
  RETURN v_sal;
  END;
  –使用存储函数
  BEGIN
   dbms_output.put_line(fun_emp_sal(7788));
   END;

横流:存储过程和储存函数的界别
1、语法不同
2、使用状况:一般存储函数多为积存过程用,存储过程一般采取在列与档次里的多寡交互
3、存储函数可以一直在sql中利用,而存储过程未克
select ename,sal,func_emp_sal(empno)  from emp;

四.采取jdbc调用存储过程及仓储函数
1.BaseDao用于加载驱动和得连接
2.ProcedureDao用来调用存储过程
3.TestDao用以测试

举例:
1.BaseDao用以加载驱动和落连接
public class BaseDao {
      //加载驱动
     static{
         try {
             Class.forName(“oracle.jdbc.OracleDriver”);
         } catch (ClassNotFoundException e) {
             e.printStackTrace();
         }
     }
    
     //获取连接
     public static Connection getConn() throws SQLException{
         String url=”jdbc:oracle:thin:@192.168.92.8:1521:orcl”;
         String user=”qin”;
         String password=”qin”;
         return DriverManager.getConnection(url, user, password);
     }
    
     public static void closeAll(ResultSet rs,Statement stmt,Connection
conn){
         if(rs!=null){
             try {
                 rs.close();
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
         if(stmt!=null){
             try {
                 stmt.close();
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
         if(conn!=null){
             try {
                 conn.close();
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
     }
}

2.ProcedureDao用以调用存储过程
public class ProcedureDao {
     public static Long  getSal(Long v_no){
         Connection conn = null;
         CallableStatement stmt=null;
         Long yearsal=0l;

        try {
             conn=BaseDao.getConn();
             stmt = conn.prepareCall(“call
pro_emp_sal2(?,?)”);//调用存储过程
             stmt.setLong(1, v_no);
             stmt.registerOutParameter(2, OracleTypes.NUMBER);   
//指定参数的数据类型
             stmt.execute();
             yearsal = stmt.getLong(2);
         } catch (SQLException e) {
             e.printStackTrace();
         }
         return yearsal;
     }
}

3.TestDao用于测试
public class TestDao {
     public static void main(String[] args) {
         CursorDao.getEmp(10l);
     }
}

五.触发器
1.–创建添加数量引发操作的触发器
CREATE OR REPLACE TRIGGER tri_add_emp
AFTER
INSERT ON emp

BEGIN
  dbms_output.put_line(‘增加了同一长达数据’);
END;

–增加一长长的数据,看是否会硌
INSERT INTO emp(empno,ename,deptno) VALUES(1,’tom’,10);

2.–系统时引发的触发器
CREATE OR REPLACE TRIGGER tri_emp
BEFORE
DELETE OR UPDATE OR INSERT
ON emp
FOR EACH ROW

DECLARE
  v_dateStr VARCHAR2(20);
BEGIN
  SELECT to_char(SYSDATE,’yyyy-mm-dd’) INTO v_dateStr FROM dual;
  IF v_dateStr = ‘2017-09-20’ THEN
   raise_application_error(-20002,’今天网维护’);
   END IF;
  END;
 
  –测试是否能够抓住触发器
  INSERT INTO emp(empno,ename,deptno) VALUES (3,’jerry’,10);

六.误删除数据恢复语句
create table tableName_bak
as
select * from tableName as of TIMESTAMP to_timestamp(‘20081126
103435′,’yyyymmdd hh24miss’);

网站地图xml地图