oracle pl/sql 备忘

 

通过sqlplus连接oracle及配置相关操作

1.sqlplus   name/ password [as sysdba]

2.进入sqlplus后： SQL> startup

3.shutdown immediate;      //关闭数据库 （OR "dbshut" command)

4. define _editor=vi

5.set serveroutput on

6. clear screen

7.edit

8. /       运行

 

pl/sql 基础结构

declare

     变量名  变量类型；

申明基本变量

     var name varchar2(32);     

     var age  number:=22;

申明数组变量

     类似于高级语言的数组

TYPE tabletype IS TABLE OF type_name INDEX BY BINARY_INTEGER ;

申明record

     类似于高级语言的结构体

TYPE record_name IS RECORD(

         field1 type1

 field2 type2 );

申明一表结构类型变量

v_rowtype TABLE_NAME%ROWTYPE ;

 

申明一游标变量

 

     Type sp_cursor is ref cursor; --定义一个游标类型

    

 

CURSOR cur_name IS  ‘select  ….. ‘

Begin

Open cur_name;

….

Close cur_name;

End;

 

declare

     cursor emp_cur is

      select first_name from s_emp ;

      v_first varchar2(32);

begin

      open emp_cur;

      while emp_cur%notfound=false loop

           fetch emp_cur into v_first;

           dbms_output.put_line(v_first);

       end loop;

       close emp_cur;

 end;

/

申明一异常

     e_name exception;

     pragma exception_init(e_child_found,-2292);

 

begin

 

 

 

exception

格式

抛出异常： raise exception_name;

exception

     when exception_name then

         dosth …. ;

eg:

 when dup_val_on_index then

 dbms_output.put_line('dup val on index');

 when e_child_found then

 dbms_output.put_line('e_child_found is raised');

 when e_custom then

 dbms_output.put_line('e_custom is raised');

 

end

 

条件

if

if  v_name is null then        -- bool 表达式

…. ;

for

for var in CursorName loop

     var.columnName … ;

end loop;

 

while

 

while bool 表达式 loop

 

end loop;

 

fetch   **   into  **

 

function

create or replace function fun2  (p_id number)

return number

is

     ss number;

begin

     select salary into ss from s_emp

     where id = p_id;

     return ss;

end;

 

trigger

create or replace trigger tri_test2

before insert on t_test

for each row

declare

begin

    select seq_test.nextval into :new.id

    from dual;

end;

 

sequence

-- Create sequence

create sequence SEQ_TEST

minvalue 0

maxvalue 999

start with 21

increment by 1

cache 20;

 

存储过程

create or replace procedure pro_create_table

is

begin

    execute immediate

        'create table pro_table ( id number primary key)';

end ;

 

create or replace procedure proc_inout

(p_in varchar2 , p_out out varchar2 , p_inout in out varchar2)

is

   v varchar2(10);

begin

   v := p_in;

   p_out := p_in||'out';

   p_inout := p_inout||'inout';

   dbms_output.put_line(p_out||p_inout);

end;

 

包

用于在逻辑上组合过程和函数，（类似于接口）

Create package pk_name as

     Procedure procedureName(arg type);

     Function fun_name(arg type) return number;

End;

包体

Create package body body_name

 is

     Procedure p_name(arg type)

Is

Begin

….

End;

Function f_name(arg type) return number

Is

V_number number;

Begin

…

End;

End;

 

 

返回结果集的过程

1.创建一个包，定义一个游标

Create or replace package test as

     Type test_curosr is ref cursor;

End test;

 

2.创建过程

Create or replace procedure p_test

(arg number,arg2 out test.test_cursor)

Is

Begin:

     Open arg2 for select …..;

     Execute immediate sql into v_cout;

End;

 

3.在java中调用

CallableStatemt   cs = conn.prepareCall(“{call p_test(?,?)}”);

Cs.registerOUtputParameter(2,OracleType.Cursor);

Cs.execute();

//获取结果集

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

 

 

job

创建测试表      

 SQL>   create   table   test(id number,cur_time   date);    

  表已创建。  

 

建sequence

CREATE  SEQUENCE test_sequence

INCREMENT  BY   1 　  --  每次加几个

 START  WITH   1 　　  --  从1开始计数

 NOMAXVALUE　　　  --  不设置最大值

 NOCYCLE　　　　　 --  一直累加，不循环

 CACHE  10 ;

 

 

建触发器

create or replace trigger tri_test_id  

  before insert on test   --test 是表名  

  for each row  

declare  

  nextid number;  

begin  

  IF :new.id IS NULLor :new.id=0 THEN --id是列名  

    select test_sequence.nextval --SEQ_ID正是刚才创建的  

    into nextid  

    from sys.dual;  

    :new.id:=nextid;  

  end if;  

end tri_test_id;   

      

创建一个自定义过程    

SQL>   create   or   replace   procedure   proc_test   as    

     2     begin    

     3     insert   into   test(cur_time)   values(sysdate);    

     4     end;    

     5     /  

 

  

  过程已创建。 

  

创建JOB 

SQL> declare job1 number;  

     begin  

        dbms_job.submit(job1,'proc_test;',sysdate,'sysdate+1/1440');--每天1440分钟，即一分钟运行test过程一次  

    end; 

 

  PL/SQL   JOB已成功完成。 

 

1、 每分钟执行

 

Interval => TRUNC(sysdate,’mi’) + 1 / (24*60)

 

2、 每天定时执行

 

例如：每天的凌晨2点执行

 

Interval => TRUNC(sysdate) + 1 +2 / (24)

 

3、 每周定时执行

 

例如：每周一凌晨2点执行

 

Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天

 

4、 每月定时执行

 

例如：每月1日凌晨2点执行

 

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24

 

5、 每季度定时执行

 

例如每季度的第一天凌晨2点执行

 

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24

 

6、 每半年定时执行

 

例如：每年7月1日和1月1日凌晨2点

 

Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24

 

7、 每年定时执行

 

例如：每年1月1日凌晨2点执行

 

Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24