初识Oracle 四 （自用） plsql

pl/sql程序段

18.语法
    

declare
		--说明部分（变量声明，游标申明，例外（异常）说明）
	begin
		--语句序列（DML语句）
	exception
		--例外处理语句
	end；

  简单的输出语句
        

begin
    dbms_output.put_line('hello world');
end;

    18.3.常量和变量的声明
        变量的基本类型和Oracle建表时的类型一致
        定义语法：

                varl char(25);
                ass number(9,2);
        常量定义：
                    cl boolean:=true;

    18.4.引用表中的数据
        myname emp.ename%type;
        例：
        

declare
    myname emp.ename%type;
begin
    select t.ename into myname from emp t where t.empno=1001;
end;

  18.5.记录型变量,可以理解为java中的对象

declare 
    myname emp%rowtype;
begin
    select * into myname from emp t where t.empno=1001;
    dbms_output.put_line(myname. ||' '||myname.  ||' '|| ...)--注：myname.后填emp表中的列名
end;

  19.if语句
  19.1语法：
  1.     

if 条件 then 语句1；
语句2；... --注：if在遇到else elsif或end if 才算结束
end if;

  2.

if 条件 then 语句1；
else 语句2；
end if；

  3.

if 条件 then 语句1；
elsif then 语句2；
else 语句3
end if；

20.loop
20.1.语法
  1.

WHILE 表达式 LOOP
...
END LOOP;

  2.(较为常用)

LOOP
EXIT [WHEN 条件]
...
END LOOP;

  3.适用于连续的数字遍历

FOR I(变量) IN 1..3 LOOP
...
END LOOP;

21.游标 Cursor（相当于java中的集合）
    21.1.定义语法
        CURSOR 游标名 [(参数名 参数类型，参数名 参数类型，...)] IS SELECT 语句
        例：
            cursor c1 is select * from test 在declare段定义
    
    21.2.使用步骤
        1.打开游标        open c1;(打开游标进行查询)
        2.取一行游标的值    fetch c1 into pno;(pno是变量)
        3.关闭游标        close c1;(关闭游标释放资源)
        4.游标结束方式        exit when c1%notfound
        注： 上述的n必须与表中列的参数类型一致 pno emp.eno%type;
        例：用游标方式输出emp表中的员工编号和姓名

declare
    prec emp%rowtype;
    cursor c1 is
    select * from emp;
begin
    open c1;
    loop
        fetch c1 into prec;--从游标中取值，取完值后游标会自动向下移动一步
        exit when c1%notfound;
    end loop;
    close c1;
end;

       例：涨工资：

declare
    prec emp%rowtype;
    cursor c1 is
    select * from emp;
    addsal number(4);
begin
    open c1;
    loop
        fetch c1 into prec;--从游标中取值，取完值后游标会自动向下移动一步
        exit when c1%notfound;
        if prec.job='president' then 
            addsal:=800;
        elsif prec.job='manager' then
            addsal:=400;
        else addsal:=100;
        end if;
        update emp 
        set sal = prec.sal+addsal 
        where id=prec.id;
    end loop;
    close c1;
    commit;--更新完数据后要提交
end;

        含参的例子：

declare
    prec emp%rowtype;
    cursor c1（dno emp.id%type） is
    select * from emp where id=dno;
begin
    open c1(4);
    loop
        fetch c1 into prec;
        exit when c1%notfound;
        update emp 
        set sal = prec.sal+100
        where id=prec.id;
    end loop;
    close c1;
    commit;
end;

22.例外（类似于java中的异常）
    22.1.Oracle自带例外：
        1.no_data_found (未找到数据)
        2.too_many_rows （查询到的结果多于要存储的变量能存储的值，比如行记录语句只能一行数据，而select查到了多行数据）
        3.zore_divide (被0除)
        4.value_error (算术或转化错误)
        5.timeout_on_resource (请求资源时超时)
    22.2.异常处理：
        在exception中
        when value_error then ...;
        when zore_divide then ...;
        when others then ...;
    22.3.异常声明：
        在declare中
        异常名  exception；
    22.4.抛出异常：
        raise 异常名；  

 22.5.例：

declare
    prec emp%rowtype;
    cursor c1 is
    select * from emp where id=70;
    no_emp exception;
begin
    open c1;
    loop
        fetch c1 into prec;
        if c1%notfound then
        raise no_emp;
        update emp 
        set sal = prec.sal+100
        where id=prec.id;
    end loop;
    close c1;
    commit;
exception
    when no_emp then 
        dbms_output.output_line('未找到该员工');
    when others then 
        dbms_output.output_line('其他异常');
end;

23.存储过程
    23.1定义
      

 create or replace procedure 存储过程名 [(参数 in/out 参数类型)]--输入参数（in）和输出参数（out）
 as 
 begin
     plsql程序体；
 end;

        或者

create or replace procedure 存储过程名 [(参数 in/out 参数类型)]--输入参数（in）和输出参数（out）
is 
begin
    plsql程序体；
end 存储过程名;

        如果在存储过程中遇到数据库更新等操作，一般在调用该存储过程时才会执行commit操作，不会在存储过程内部进行commit操作。

    23.2.调用（假设存储过程有输入输出参数，第一个参数为输入，第二位输出）
        declare 
            
        begin
            存储过程名（参数1，参数2）；
        end；

24.存储函数
    24.1.语法

create or replace function 函数名 [(参数 in 参数类型)]
return 数据类型
is 
结果变量 数据类型
begin
    plsql程序体；
return 结果变量 
end 函数名;

24.2.与存储过程的区别
        1.存储过程无返回值，存储函数有返回值，但是存储过程可以通过out来传出多个值。因此一般推荐使用存储过程
25.java调用    

1.存储过程

public static void test(){
  String driver="Oracle.jdbc.OracleDriver";
  String conStr="jdbc:Oracle:thin:@127.0.0.1:1521:orcl";
  Connection conn=null;
  CallableStatement cs=null;
  try {
      Class.forName(driver);
      conn= DriverManager.getConnection(conStr,"sys","sys");
      cs=conn.prepareCall("{call countysal(?,?)}");
      cs.setInt(1,7390);
      //注册Oracle输出参数的类型
      cs.registerOutParameter(2,OracleTypes.NUMBER);
      //执行存储过程
      cs.execute();
      int y=cs.getInt(2);

  } catch (Exception e) {
      e.printStackTrace();
  }finally {
      try {
          if(cs!= null){
              cs.close();
          }
          if(conn!= null){
              conn.close();
          }
        } catch (SQLException throwables) {
              throwables.printStackTrace();
          }        
    }
}

2.存储函数：

public static void test(){
    String driver="Oracle.jdbc.OracleDriver";
    String conStr="jdbc:Oracle:thin:@127.0.0.1:1521:orcl";
    Connection conn=null;
    CallableStatement cs=null;
    try {
        Class.forName(driver);
        conn= DriverManager.getConnection(conStr,"sys","sys");
        cs=conn.prepareCall("{？=call countysal(?)}");
        cs.setInt(2,7390);
        //注册Oracle输出参数的类型
        cs.registerOutParameter(1,OracleTypes.NUMBER);
        //执行存储过程
        cs.execute();
        int y=cs.getInt(2);

    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            if(cs!= null){
                cs.close();
            }
            if(conn!= null){
                conn.close();
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}

学习资料来源：B站BV1AE411p79z