PL/SQL存储过程（Oracle）

                   PL/SQL 存储 过程 （O racle）

1. 查询Oracle数据库的所有的系统权限，一般是DBA

Select * from ststem_privilege_map order by name;

2. 查询所有的角色，一般是DBA

Select * from dba_roles;

3. 查询所有对象的权限，一般是DBA

Select distinct privilege from dba_tablespaces;

4. 查询某个用户具有怎样的角色

Select * from dba_role_privs where grantee =’用户名’;

5. 查询，某个角色包括哪些系统权限

Select * from dba_sys_privs where gtantee=”DBA”

或select * from rolr_sys_privs shere role=”DBA”;

 

 

PL/SQL块

Declear(可选)

--申明部分（常量、变量、游标、例外、复杂数据类型结构）

Begin

--执行部分

Ecxoption(可选)

--例外部分

End

 

例：&表示接受控制台输入的数

Declear

V_name varchar(2);

V_sal number()7,2;

Begin

Select name,sal into v_name,v_sal from emp where empno=&no;

Dbms_output.put_line(‘姓名:’||v_name||’工资:’||v_sal);

Exception

When no_data_found then

Dbms.output.put_line(‘编号不存在’);

End;

 

过程例子

Create procedure sp_pro1(spName varchar2,newSal number) is

Begin

--根据用户名修改工资

Update emp set set sal=newSal where eName=spName;

End;

 

调用过程

1. exec 过程名（参数）；

2. call 过程名（参数）；

 

标量定义

1. 定义一个变长字符串

V_name varchar2(100);

2. 定义一个小数，再范围-999.99~+999.99之间

V_sal number(6,2);

3. 定义一个小数并赋初始值为5.4    :=为数据库赋值符号

V_sal2 number(6.2):=5.4

4. 定义一个日期类型

V_time date;

5. 定义一个布尔类型，不能为空，初始值为false

V_valid Boolean not null default false;

 

复合变量（记录类型） emp.eName%type指跟数据表字段eName相同类型

Type emp_record_type is record(name emp.eName%type,salYear emp.sal%type,v_job emp.job%type);

--定义一个变量类型为emp_record_type的变量

Sp_record emp_record_type;

 

复合变量（PL/SQL表）

相当于高级语言中的数组，但须注意：在高级语言中数组下标不能为负数，而PL/SQL是可以为负数的，并且表元素的下标没有限制。

Type sp_table_rype is tale of emp.eName%type index by binsry_integer;

Sq_table sp_table_type;

-- index by binsry_integer  下标类型为整数类型

 

参数变量（游标）

 

例：

Declare 

--定义游标类型

Type sp_emp_cursor is refcursor;

--定义游标变量

Test_cursor sp_emp_cursor;

--定义变量

V_name emp.eName%type;

V_Sal emp.sal%type;

Begin

--打开游标

Open test_cursor for select eName,sal from emp；

--循环取值

Loop

Fetch test_cursor into v_name,v_sal;

--判断游标是否为空（退出）

Exit when test_sursor%notfound;

End loop;

--关闭游标

Close test_corsor;

End;

 

 

函数

例：输入name,返回年薪

Create function sp_fun1(name varchar2)

Return number is yearSal number(7,2);

Begin

Select sal*12+nvl(comm,0)*12 into yearSal from emp where eName=name;

Return yearSal;

End;

 

调用函数：

--定义一个变量

Var yearSal number;

Call sp_fun1(‘name’) into:yearSal;

打印

 

包

--包规范（包含过程和函数的说明，但没有实现）

Create package sp_package1 is 

procedure update_sal(name varchar2,newSal number);--一个过程

function getSalYear(name varchar2);--一个函数

end;

--包体（用于实现包规范的过程和函数）

Create package body sp_package is

Procedure update_sal(name varchar2,newSal number)

Is

Begin

Update emp set sal=newSal where eName=name;

End;

Function getSalYear(name varchar2) ruturn number Is yearSal number;

Begin

Select sal*12+nvl(comm,0)*12 into yearSal from emp where eName=name;

Return yearSal;

End;

End;

调用包

Exec sp_package.过程/函数（参数）;

 

判断if then

--编写一个过程，可以输入一个雇员的名字，如果该雇员的薪水低于2000，就给该雇员的工资增加20%

Create or replace procedure sp_pro6(spName varchar2)is

--定义

V_sal emp.sal%type;

--执行

Select sale into v_sal from emp where ename=spName;

--判断

If v_sal<2000 then 

Update emp set sal=sal*1.1 where ename=spName;

End if;

End;

 

调用：exec sp_pro6(‘name’);

 

判断if then else    (不等于<>)

--编写一个过程，可以输入一个雇员的名字，如果该雇员的补助不是0，就在原有的基础上增加100；如果补助为0就把补助设为200

Create or replace procedure sp_pro6(spName varchar2)is

--定义

V_somm emp.sal%type;

--执行

Select comm into v_comm from emp where ename=spName;

--判断

If v_comm<>0 then 

Update emp set comm=comm+100 where ename=spName;

Else

Update emp set comm=comm+200 where ename=spName;

End if;

End;

 

判断if then elsif else

--编写一个过程，可以输入一个雇员的编号，如果该雇员的职位是president，就给他的工资增加1000，如果职位是manager，就给他的工资增加500，其他职位增加200

Cteate procedure sp_pro8(spNo number) is

V_job emp.job%type;

Begin

Select job into v_job from emp where empNo=spNo;

If v_job=’president’ then 

Update emp set sal=sal+1000 where empNo=spNo;

Elsif v_job=’manager’

Update emp set sal=sal+500 where empNo=spNo;

Else update emp set sal=sal+200 where empNo=soNo;

End if;

End;

 

循环 loop … end loop  至少循环一次 （：=赋值符）

--输入用户名，自动添加十个用户

Create procedure sp_pro9(soName varchar2) is

V_num number:=1;

Begin 

Loop

 Insert into users values(v_num,spName);

--判断是否退出

Ecit when v_num=10;

--自增

V_num:=vnum+1;

End loop;

Eng;

 

While循环

Create procedure sp_pro10oName varchar2) is

V_num number:=11;

Begin 

While v_num<=20 loop

 Insert into users values(v_num,spName);

--判断是否退出

Ecit when v_num=10;

--自增

V_num:=vnum+1;

End loop;

Eng;

 

Goto案例(过程)

Declare 

I int :=1;

Begin 

Loop 

Dbms_output.put_line(‘输出i=’||i);

If i=10 then 

Goto end_loop;

End if;

I:=1+1;

End loop;

<<end_loop>>

Dbms_output.put_line(‘循环结束’);

End;

Pl/sql分页过程

--编写过程

--in 表示这是一个输入参数，默认in

--out 表示一个输出参数

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

Create procedure sp_pro12(spno in number,spName out varchar2) is

begin

Select ename into spName from book where boolNo=spno;

End;

 

Java调用过程

Try{

加载驱动

Class.forName(“oracle.jdbc.driver.OracleDriver”);

Connection ct =DriverMananger.getConnection(“jdbc:oracle:thin:@localhost:1521”:”daname”:”pws”);

2.创建CallableStatement

CallableStatement cs=ct.prepareCall(“{call sp_name(?,?,?)}”);

3.给？赋值

Cs.setInt(1,10);

Cs.setString(2,”Dolo”);

Cs.setString(3,”sfaasfasd”);

4.执行

Cs.ececute();

Catch(Ecection e)){

 

}finally{

5.关闭

Cs.close();

Ct.close();

}

 

 

 

Java调用过程(带返回值2个)

Try{

加载驱动

Class.forName(“oracle.jdbc.driver.OracleDriver”);

Connection ct =DriverMananger.getConnection(“jdbc:oracle:thin:@localhost:1521”:”daname”:”pws”);

2.创建CallableStatement

CallableStatement cs=ct.prepareCall(“{call sp_name(?,?,?)}”);

3.给？赋值

Cs.setInt(1,10);

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

Cs.registerOutParameter(3,oracle.jdbc.OracleTypes.VARCHAR);

4.执行

Cs.ececute();

5.取出返回值

String name=cs.getString(2);

String age=cs.getString(3);

 

System.out.println(name); 

Catch(Ecection e)){

 

}finally{

5.关闭

Cs.close();

Ct.close();

}

 

返回结果集的过程

--创建一个包,在该包中定义了一个游标类型

Create package  testpackage as

Type test_cursor is ref cursor;

End testpackage;

 

--2.创建过程

Create procedure sp_pro19(spNo in number,p_cursor out testpackage.test_cursor)

Is

Begin

Open p_cursur for select * from emp where deptno=soNo);

End;

--3.java调用

Java调用过程(带返回值2个)

Try{

加载驱动

Class.forName(“oracle.jdbc.driver.OracleDriver”);

Connection ct =DriverMananger.getConnection(“jdbc:oracle:thin:@localhost:1521”:”daname”:”pws”);

2.创建CallableStatement

CallableStatement cs=ct.prepareCall(“{call sp_pro19(?,?)}”);

3.给？赋值

Cs.setInt(1,10);

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

4.执行

Cs.ececute();

5.取出结果集

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

While(rs.next()){

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

}

Catch(Ecection e)){

 

}finally{

5.关闭

Cs.close();

Ct.close();

}

 

分页存储过程

--oracle分页

Select rownum from 

（Select t1.*rownum rn from (Select * from emp)t1 where rn<=10）here rn>=5;

 

 

--分页过程

--1.开发一个包

Create package  testpackage as

Type test_cursor is ref cursor;

End testpackage;

 

--2. 创建过程

Create produre fenye

(tableName in varchar2,

pageSize in number,--每页记录数

pageNow in number,

myrows out number,--总记录数

myPafeConunt out number,--总页数

p_cursor out tespackage.test_cursor—返回的记录集合

)

is

--定义sql语句 

V_sql varchar(1000);

--定义两个整数

V_begin number:=( pageNow-1)* pagedize+1

V_end number:= pageNow* pagedize

Begin

Select rownum from 

V_sql=:’Select t1.*rownum rn from (Select * from ‘|| tabaleName ||’)t1 where rn<=’||V_end||’）here rn>=’|| V_begin ;

--打开游标

Open p_cursor for v_sql;

--计算myrows和myPageCount

V_sql=:’Select count(*) from ‘|| tabaleName;

--执行sql，并把返回的值，赋给myrows

Execute immediate v_sql intp myrows;

--计算myPageCount   mod取余

If mod(myrows, myPageCount)=0 then

myPageCount:= myrows/ pagedize;

else

myPageCount:= myrows/ pagedize+1;

end if;

--关闭游标

Close p_cursor;

End;

 

--3.使用Java调用测试分页

Try{

加载驱动

Class.forName(“oracle.jdbc.driver.OracleDriver”);

Connection ct =DriverMananger.getConnection(“jdbc:oracle:thin:@localhost:1521”，”daname”，”pws”);

2.创建CallableStatement

CallableStatement cs=ct.prepareCall(“{call fenye(?,?,?,?,?,?)}”);

3.给？赋值

Cs.setString(1,”表名”);

Cs.setInt(2,5);//每页记录

Cs.setInt(3,2);//总页数

 

 

//注册out 总记录数

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

//注册out 总页数

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

//注册返回的结果集

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

4.执行

Cs.ececute();

//取出总记录数/注意：getInt（4）中的4，是由该参数的位置决定的

Int rowNum=cs.getInt(4);

Int pageConnt=cs.getInt(5);

 

5.取出结果集

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

While(rs.next()){

// rs.getInt(1) 第一列    rs.getString(2)第二列  int String 对应表中字段

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

}

Catch(Ecection e)){

 

}finally{

5.关闭

Cs.close();

Ct.close();

}

 

 

--要排序的话在最内层加order by

 

 

例外

1. 预定义例外（20多个）

1. No_data_found(找不到数据)

2. Case_not_found(没有包含可行的分支)

3. Cursor_already_open

4. Invalid_cursor(关闭没打开的游标，从没打开的游标取数据)

5. Invalid_number(输出数据有误时触发，数据异常)

6. Too_many_rows(返回多行)

7. Zero_divide(2/0语句时，触发)

8. Value_erroe(变量长度不足以容纳数据)

2. 非预定义例外

1. login_denide(非法用户登录)

2. not_logged_no(用户么没登陆)

3. storage

3. 自定义例外

--更新用户表，如果没有这个用户时

a) Create procedure ex_test(spNo number)

is

--定义一个例外

Myex exception

Begin

--更新用户sal

Update emp set sal=sal+1000 where empno=spNo;

--sql%notfound这里表示没有update

--raise myex;触发myex

If sql%notfound then

Taise myex;

End if;

Exection

When myex then

Dbms_out.put_line(‘没有更新任何用户’);

End;

 

 

视图

--创建视图,把emp表的sal<1000的雇员映射到视图

Creste view myview as select * from emp where sal<1000;

 

--创建视图,把emp表的sal<1000的雇员映射到视图

Creste view myview2 as select emp.empNo,emp.eName,dept.dName from emp,dep where emp.deptno=dept.empNo;

[with read only]只读