PLSQL课堂笔记

PLSQL课堂笔记

第一课

 

编写一个存储过程，该过程可以向某表中添加记录

1.创建一个简单的表

  Create table mytest (name varchar2(30),passwd varchar2(30));

2.创建过程

  Create or replace procedure sp_pro1 is

  Begin

    Insert into mytest values(‘韩顺平’,’m1234’);

  End;

   /

   Replace:表示如果有sp_pro1这个过程，就替换

 

   如何查看错误信息：show error；

3．调用过程

A．exec 过程名(参数值1,参数值2,….)

B．call 过程名(参数值1,参数值2,….)

 

 

 

 

第二课

最简单的块

set serveroutput on;   --打开输出选项

begin

   dbms_output.put_line('Hello World!');

 end;

 

接受变量的块1

declare

v_ename varchar2(5);

begin

select ename into v_ename from emp where empno = &no;

dbms_output.put_line('用户名是：'||v_ename);

end;

 

接受变量的块2

declare

v_ename varchar2(5);

v_sal number(7,2);

begin

   select ename,sal into v_ename,v_sal from emp where empno = &no;

   dbms_output.put_line('用户名是：'|| v_ename||'薪水:'||v_sal);

 end;

 

 

 

抛异常块

declare

v_ename varchar2(5);

v_sal number(7,2);

begin

   select ename,sal into v_ename,v_sal from emp where empno = &no;

   dbms_output.put_line('用户名是：'|| v_ename||'薪水:'||v_sal);

 

exception

--异常块

When no_data_found then

    dbms_output.put_line('编号输入有误');

end;

 

 

--案例4    过程

SQL> create or replace procedure sp_pro3(spName varchar2,newSal number) is

  2  begin

  3   --根据用户名去修改工资

  4   update emp set sal = newSal where ename = spName;

  5  --exception

  6  end;

  7 

  8  /

exec sp_pro3('小明',1500);

 

 

用Java调用sp_pro3过程

package com.sp;

import java.sql.*;

 

public class TestOraPro {

   

    public static void main(String[] args){

        try {

            //1.加载驱动

            Class.forName("oracle.jdbc.driver.OracleDriver");

            //2.建立连接

            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:jsly","scott","tiger");

            //3.创建CallableStatement

            CallableStatement cs = conn.prepareCall("call sp_pro3(?,?)");

            //4.给？赋值

            cs.setString(1, "小明");

            cs.setInt(2, 500);

            //5.执行

            cs.execute();

            //6.关闭

            cs.close();

            conn.close();

                       

           

        } catch (Exception e) {

            // TODO 自动生成 catch 块

            e.printStackTrace();

        }

               

    }

}

 

 

第三课

 

--函数案例1

--输入雇员的姓名，返回雇员的年薪

create or replace function sp_fun1 (spName varchar2) return number is yearSal number(7,2);
begin
select sal*12+nvl(comm,0) into yearSal from emp where ename = spName;
return yearSal;
end;

 

--执行

SQL> var yearSal number;

SQL> call sp_fun1('小明') into:yearSal;

 

 

 

---********************包案例

--创建包

--创建一个包sp_package,声明该包有一个过程和函数

create package sp_package is

procedure update_sal(name varchar2,newsal number);

function annual_income(name varchar2) return number;

end;

 

 

--给包 sp_package 实现包体，即实现包里面声明的过程函数等

create or replace package body sp_package is

procedure update_sal(name varchar2,newsal number) is

begin

update emp set sal=newsal where ename=name;

end;

function annual_income(name varchar2)

return number is

annual_salary number(7,2);

begin

select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;

return annual_salary;

end;

end;

 

--调用包

exec sp_package.update_sal('小明',5000);

 

 

 

----标量类型实例1

declare

v_name varchar2(6);

v_sal number(7,2);

v_tax_sal number(7,2);

c_tax_rate number(3,2):=0.03;

begin

select ename,sal into v_name,v_sal from emp where empno = &no;

v_tax_sal := v_sal * c_tax_rate;

dbms_output.put_line('用户名：'||v_name||'月薪：'||v_sal||'个人所得税：'||v_tax_sal);

end;

 

 

----标量类型实例2

declare

v_name emp.ename%type;

v_sal emp.sal%type;

v_tax_sal number(7,2);

c_tax_rate number(3,2):=0.03;

begin

select ename,sal into v_name,v_sal from emp where empno = &no;

v_tax_sal := v_sal * c_tax_rate;

dbms_output.put_line('用户名：'||v_name||'月薪：'||v_sal||'个人所得税：'||v_tax_sal);

end;

 

 

----复合类型pl/sql记录

declare

--定义一个emp_record_type类型，类型包含三个数据name，salary.title

type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);

--定义一个sp_record变量，变量的类型是emp_record_type

sp_record emp_record_type;

begin

select ename,sal,job into sp_record from emp where empno =&no;

dbms_output.put_line('员工名：'||sp_record.name);

end;

 

----复合类型pl/sql表

declare

--定义一个emp_table_type类型，该类型用于存放emp.ename%type类型

--index by binary_integer 表示下标是整数

type emp_table_type is table of emp.ename%type index by binary_integer;

--定义一个sp_record变量，变量的类型是emp_table_type

sp_table emp_table_type;

begin

select ename into sp_table(5) from emp where empno =&no;

dbms_output.put_line('员工名：'||sp_table(5));

end;

 

 

 

--使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工和工资

declare

--定义一个游标类型

type sp_emp_cursor is ref cursor;

--定义一个游标变量

test_cursor sp_emp_cursor;

--定义变量

v_name emp.ename%type;

v_sal emp.sal%type;

begin

--执行

--把test_cursor和一个select结合起来

open test_cursor for select ename,sal from emp where deptno = &deptno;

--循环取出

loop

fetch test_cursor into v_name,v_sal;

--判断test_cursor是空

exit when test_cursor%notfound;

dbms_output.put_line('姓名：'||v_name||'工资：'||v_sal);

end loop;

end;

 

 

 

 

第四课

----使用pl/sql编写一个过程，输入部一个员工名，如果工资低于1000，则增加10%

create procedure sp_addsal(v_name emp.ename%type) is

begin

update emp set sal = sal*(1+0.1) where ename = v_name and sal<1000;

end;

 

---使用pl/sql编写一个过程，输入部一个员工名，如果补助不为0，加100，否则设为200

create procedure sp_addcomm(v_name emp.ename%type) is

v_comm emp.comm%type;

begin

select comm into v_comm from emp where ename = v_name;

if v_comm <> 0 then

 v_comm := v_comm + 100;

else

 v_comm := 200;

end if;

update emp set comm = v_comm where ename=v_name;

dbms_output.put_line('姓名：'||v_name||'补助：'||v_comm);

end;

 

--编写一个过程，输入一员工编号，如果职位是PRESIDENT则加薪1000，如果职位是MANAGER则加薪500，如果职位是其他则加薪200

create or replace procedure sp_addsal2(v_no emp.empno%type) is

--定义

v_sal emp.sal%type;

v_job emp.job%type;

begin

select sal,job into v_sal,v_job from emp where empno = v_no;

if v_job = 'PRESIDENT' then v_sal := v_sal+1000;

elsif  v_job = 'MANAGER' then v_sal := v_sal+500;

else v_sal := v_sal+200;

end if;

update emp set sal = v_sal where empno = v_no;

end;

 

 

 

 

第五课

--创建一个过程，给表book添加记录

create procedure sp_book_add

  2  --in:表示输入一个参数

  3  --out:表示输入一个参数

  4  (bookId in book.bookid%type,bookName in book.bookname%type,publishHouse in book.publishhouse%type) is

  5  begin

  6  insert into book values(bookId,bookName,publishHouse);

  7  end;

 

Java调用此过程

package com.sp;

import java.sql.*;

 

public class TestOraPro2 {

   

    public static void main(String[] args){

       

        //

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");

            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:jsly", "scott", "tiger");

            CallableStatement cs = conn.prepareCall("call sp_book_add(?,?,?)");

            cs.setInt(1, 11111);

            cs.setString(2,"oracle");

            cs.setString(3, "韩顺平");

            cs.execute();

            cs.close();

            conn.close();

        } catch (Exception e) {

            // TODO 自动生成 catch 块

            e.printStackTrace();

        }finally{

 

        }

    }

 

}

 

 

 

 

--有输入和输出的存储过程

create procedure sp_pro4(no in number,name out varchar2) is
begin
select ename into name from emp where empno = no;
end;

 

java调用此过程

package com.sp;

import java.sql.*;

 

public class TestOraPro3 {

   

    public static void main(String[] args){

       

        //

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");

            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:jsly", "scott", "tiger");

           

            CallableStatement cs = conn.prepareCall("call sp_pro4(?,?)");

            cs.setInt(1, 7788);

            cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);

            cs.execute();

            String name = cs.getString(2);

            System.out.println("7788的姓名是："+name);

            cs.close();

            conn.close();

        } catch (Exception e) {

            // TODO 自动生成 catch 块

            e.printStackTrace();

        }finally{

 

        }

    }

 

}

 

 

 

 

 

 

----------------------创建一个存储过程，输入部门号，输出本部门的信息-----------------------------

 

--1.创建一个包，内有一个游标

create package testpackage is

   type test_cursor is ref cursor;

end testpackage;

 

 

--2.创建过程

create procedure sp_pro5(spNo in emp.empno%type,spCursor out testpackage.test_cursor) is

begin

  open spCursor for select * from emp where deptno = spNo;

end;

 

--3.java调用过程sp_pro5

package com.sp;

 

import java.sql.*;

 

public class TestOraPro4 {

   

    public static void main(String[] args){

               

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");

            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:jsly", "scott", "tiger");

           

            CallableStatement cs = conn.prepareCall("call sp_pro5(?,?)");

           

            cs.setInt(1, 10);

            cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);

            cs.execute();

 

            ResultSet rs = (ResultSet)cs.getObject(2);

            while (rs.next()){

              System.out.println(rs.getString(1)+"  "+rs.getString(2));

            }

            rs.close();

            cs.close();

            conn.close();

           

        } catch (Exception e) {

            // TODO 自动生成 catch 块

            e.printStackTrace();

        }finally{

 

        }

       

    }

   

}

----------------------创建一个存储过程，输入部门号，输出本部门的信息-----------------------------

 

 

 

第六课

---------------------编写一个存储过程，要求输入表名、每页显示记录数、当前页码，返回总记录数、总页数和结果集----------------------------------------------------------------------------------------

--1.创建一个包，内有一个游标

create package testpackage is

   type test_cursor is ref cursor;

end testpackage;

--2.创建存储过程

SQL> create or replace procedure fenye (

  2  --输入的参数

  3  tableName in varchar2,--输入的表名

  4  pageSize in number,--每页显示的记录数目

  5  pageNum in number,--当前页数

  6  --输出的参数

  7  allRows out number,--记录的总数

  8  pageCount out number,--记录的总页数

  9  sp_cursor out testpackage.test_cursor--结果集

 10  ) is

 11  --定义

 12  v_sql varchar2(1000);

 13  v_sql2 varchar2(1000);

 14  v_begin number := (pageNum-1)*pageSize+1; --计算每页开始的记录号

 15  v_end number := pageSize*pageNum;--计算每页结束的记录号

 16  begin

 17  --打开结果集

 18  v_sql:='select * from (select a1.* , rownum rn from (select * from '||tableName||') a1 where rownum<='||v_end||') where rn>='||v_begin;

 19  open sp_cursor for v_sql;

 20  --计算allRows

 21  v_sql2 :='select count(*) from  '||tableName;

 22  execute immediate v_sql2 into allRows;--立即执行v_sql2语句，把结果赋值给allRows

 23  --计算pageCount

 24  if mod(allRows,pageSize) = 0 then

 25  pageCount := allRows/pageSize;

 26  else pageCount := allRows/pageSize+1;

 27  end if;

 28  end;

 29  /

 

---3.java调用此过程

package com.sp;

import java.sql.*;

//调用fenye过程

public class TestProFenye {

 

    /**

     * @param args

     */

    public static void main(String[] args) {

        // TODO 自动生成方法存根

 

       

        try {

            //加载驱动

            Class.forName("oracle.jdbc.driver.OracleDriver");

            //建立链接

            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:jsly", "scott","tiger");

            //调用过程

            CallableStatement cs = conn.prepareCall("{call fenye(?,?,?,?,?,?)}");

            //分别赋值

            cs.setString(1, "emp");

            cs.setInt(2, 5);

            cs.setInt(3, 1);

           

            //输出注册

            cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);

            cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);

            cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);

            //执行

            cs.execute();

           

            //

            int allRows = cs.getInt(4);

            int pageCount = cs.getInt(5);

            ResultSet rs = (ResultSet)cs.getObject(6);

           

            System.out.println("总记录数是"+allRows);

        System.out.println("总页数是"+pageCount);

                     

        while(rs.next()){ 

                  System.out.println("姓名是"+rs.getString(2)+"工资是"+rs.getString(6));  

        }

           

            rs.close();

            cs.close();

            conn.close();

           

           

            }

                     

        catch (Exception e) {

            // TODO 自动生成 catch 块

            e.printStackTrace();

        }

       

    }

 

}

 

 

第七课

1. --自定义例外

2. create or replace procedure ex_test(spNo number)

3. is

4. --定义一个例外

5. myex exception;

6. begin

7. --更新用户sal

8. update emp set sal=sal+1000 where empno=spNo;

9. --sql%notfound 这是表示没有update

10.--raise myex;触发myex

11.if sql%notfound then

12.raise myex;

13.end if;

14.exception

15.when myex then

16.dbms_output.put_line('没有更新任何用户')；

17.end;

18./

 

---执行

SQL> exec ex_test(56);

没有更新任何用户