oracle 复习

Oracle驱动：oracle.jdbc.driver.OracleDriver

Oracle路径：jdbc:oracle:thin:127.0.0.1:1521:mydatabase（不知是否正确）

 

oracle三大变量类型：

标量，复合，参照

一般变量：declare  v_name  test_mytable.m_name%type;

begin

select m_name intov_ name from test_mytable where m_id = &aaaaa;

dbms_output.put_line('名字：'||v_test_m_name);

exception

when no_data_foundthen dbms_output.put_line('没有编号');

end;

复合变量：declare type emp_record is record             --定义记录类型

(

                     nameemp.name%  type,

                     salaryemp.salary%  type

);

e_record emp_record;                     --定义记录变量

begin

              select name,salary into e_record from mytable where id =1;

exception

when no_data_foundthen dbms_output.put_line('没有编号');

end;

参照变量：declare type my_cursor  is ref  cursor;                           --定义游标类型

test_my_cursor  my_cursor;                          --定义游标变量

v_name  test_mytable.m_name%  type;              --定义变量

begin

open test_my_cursor  for  select m_name  from  test_mytable;

loop

fetch test_my_cursor into v_name;

exit when test_my_cursor% notfound;

end loop;

close test_my_cursor;

exception

when no_data_found then dbms_output.put_line('没有编号');

end;

 

创建函数

create or replace function test_function(my_id number) return varchar2   --创建返回类型

is

my_name varchar2(20);                                         --创建返回变量

begin

select m_name into my_name from test_mytable where m_id =my_id ;

return my_name;

exception

when  no_data_found  then  dbms_output.put_line('没有编号');

end;

java中调用函数：select my_function(‘my’) from dual;

 

存储过程

create or replace procedure sp_pro(id in number,name out varchar2)

is

v_id varchar2(100);

v_name varchar2(100);

begin

...

exception

when  no_data_found  then  dbms_output.put_line('没有编号');

end;

java调用Oracle存储过程：

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

Connection con = DriverManager.getConnection(“url”,”name”,”pwd”);

CallableStatement cs =con.prepareCall("{call sp_pro(?,?,?)}");

cs.setString(1,"emp");

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

cs.registerOutParameter(3,oracle.jdbc.OracleType.INTEGER);

cs.execute();                                                                                                                                                         

sqlplus中调用函数和存储过程：

var income number;

call my_fun(‘my’) into:income;

或

exec pro_2(12,'aaa','aaa'); 

或

declare a number;  

begin  

  pro_3(a);  

  dbms_output.put_line(a);  

end;

java中调用函数：

select my_fun(‘my’) from dual;

 

视图

create or replace view view_my

as

select name from stu group by id havingname like '张%';

 

调用视图：                                                         

select * from view_my                                                     

 

row_number函数

select row_number() over(order by name desc)rownum,id from table_name where rownum < 6;

 

游标

declare

type my_cuosor is ref cursor ;

v_num number;

v_test mytable.mytest% type;

begin

open my_cuosor for select num from mytable;

loop

fetch my_cursor into v_num;

exit when my_cursor% notfound;

end loop;n

close my_cursor;

end;

/

 

daclare my_cursor is select id from mytable;

v_num number(10);

begin

open my_cursor;

loop

fetch my_cursor into v_num;

exit when my_cursor% notfound;

end loop;

close my_cursor;

end;

/

创建包和包体：

create package my_package

is

procedure my_pro(name varchar2,id number);

function my_fun(id number) return number;

end;

/

create package body my_package

is

procedure my_pro(name varchar2,id number)

is

begin

。。。

end;

function my_fun(id number) return number

is

begin

。。。

end;

end;

 

调用包里的函数：call my_package.my_fun(2);

 

创建索引： 

create index mytable_name_index on mytable(name) tablespace oneSpace

 

注意：

1:if...then...elsif...else...语法：

       if条件很后面没有括号

       elsif没有字母e

       while条件也没有括号

2：pl/sql中的不等号：<> ,不能写成!=

3．Plsql中的赋值符号：=

4：pl/sql中没有:变量++ 这种自加自减形式，应该写成：变量:=变量+1

5：调用过程名：

exec 过程名(参数) 或

call 过程名(参数)，

不能用execute ；

6：显示错误详细信息是：show error;

7：命名规范

       变量用v_作前缀

常量用c_作前缀

游标用_cursor作后缀

例外用e_作前缀

 

oracle函数:

nvl(a,b)：       如果a不为空则返回a，否则返回b

nvl(a,b,c)：    如果a不为空则返回b，否则返回c

decode(value,if1,then1,if2,then2,if3,then3,…,else)表示value等于if1时, 结果是then1。。。

select instr(“yuechaotianyuechao”,”ao”,-1,1)position from dual;(-1是开始位置,1是次数) — 17

substr(“abcde”,-6,5 )=null    -6是从右往左数第6个位置开始，5是要取得字符串长度

substr(“abcde”,-5,5 )= abcde       -6是从右往左数第6个位置开始，5是要取得字符串长度

mod(a,b)：  对a和b取余

row_number() over(partition by … order by …)

oracle数据优化时避免使用in,null,*,where中出现表达式，这会导致无法使用索引。
同时条件中应先排查容易筛选的小数据，再排查大数据。

oracle函数：

nvl

instr

substr

to_char

mod

row_number() over(patition by .. order by …)

max

min

count

decode(value,if1,then1,if2,then2,……) 判断