oracle基础三 与长字符串根据操作符分割成数组处理函数

--sql developer 的使用
--新建一个连接
 CONNECTION NAEM : 随意
 useranme  ：登录名
 password  ：密码
 hostname  ：ip地址
 port  ：oralce端口号   一般是1521
 sid  ：oralce数据库名字
 
 
          DOS命令  lsnrctl status  --查看oralce连接信息
  lsnrctl start   --启动oracle相关服务
          lsnrctl stop    --关闭oracle相关服务




--PLSQL  程序设计  
--面向过程
--代开输出开关  set serveroutput on;
 declare  开头
 变量声明 
 begin
  --程序体
dbms_OUTPUT.PUT_LINE('Hello World');
 end;
 /
--什么是PL/SQL？
PL/SQL（Procedure Language/SQL）

PLSQL是Oracle对sql语言的过程化扩展


指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力


--PL/SQL 程序结构及组成
declare
说明部分(变量说明，光标声明，例外说明)
BEGIN
语句序列（DML语句)
EXCEPTION
例外处理语句
END;





--说明变量
列子
varl   char(15);
married  boolean :=true;
psal number(7,2);
my_name  emp.ename%type; --引用行变量  即my_name的数据类型就是表emp的ename字段类型
emp_rec emp%rowtype; --记录行变量 




--PL/SQL中的赋值符号
:=
INTO




--IF 语句
IF  条件  TEHN
语句1;
语句2;
.......
  ELSIF  条件 THEN
语句;
........
  ELSE
语句;
........
END;


--列子
set serveroutput on
accept num prompt '???????';


declare
  pnum number := #
begin
   if pnum = 1 then 
    dbms_output.put_line('?????1');
   elsif pnum = 2 then 
    dbms_output.put_line('?????2');   
   elsif pnum = 3 then 
    dbms_output.put_line('?????3');     
   else
    dbms_output.put_line('????');
  end if;    


end;
/




--循环语句
LOOP
 EXIT [WITH 条件]
 语句块;
END LOOP




FOR I IN 1..3
LOOP
 语句块;
END LOOP;






--光标(Cursor)==JAVA的  ResultSet
show  paramter cursor ; 查看光标参数  默认可以打开300个
  --光标语法
CURSOR 光标名[{参数 数据类型[参数名,数据类型]...}]
IS SELECT 语句;


用于存储一个查询返回的多行数据
例如：
cursor cl  is  select job from  emp;
  --光标的属性
notfound,isopen;


  --使用光标
打开光标  open  光标名;  --打开光标执行查询


取一行光标的值:  fetch 光标名 into pjob --取一行到变量中


关闭光标  close  光标名；关闭游标释放资源


  pjob 的数据类型 与emp表中的 job列类型一致。。


--光标和LOOP使用列子
  set serveroutput on


declare
  cursor cemp is select ename,sal from emp;
  pename emp.ename%type;
  psal   emp.sal%type;
begin
      open cemp;
  LOOP
    fetch cemp into pename,psal;


    exit when cemp%notfound;
  


    dbms_output.put_line(pename||'????'||psal);
  
      END LOOP ;
  close cemp;
end;
/  
  declare
  cursor cemp(dno number) is select ename from emp where deptno=dno;
  pename emp.ename%type;
begin
  open cemp(20);
  loop
   fetch cemp into pename;
     exit when cemp%notfound;
    
    dbms_output.put_line(pename);


  end loop;
  close cemp;
end;
/


传递字符串  存储过程split 分割字符串的解决办法   处理的解决办法.... 
************************************************************************************************************************************************************************************************************************************
Create or replace TYPE TABSTR_T IS TABLE of VARCHAR2(4000);


create 
FUNCTION  table_str (
            P_STR IN VARCHAR2, 
           P_SEP IN VARCHAR2   DEFAULT   ','     --  默认逗号分割
)
RETURN TABSTR_T IS
        L_STR LONG := P_STR || P_SEP;
        L_TABSTR TABSTR_T := TABSTR_T();
BEGIN
        WHILE L_STR IS NOT NULL LOOP
                L_TABSTR.EXTEND(1);
                L_TABSTR(L_TABSTR.COUNT) := RTRIM(SUBSTR(L_STR, 1, INSTR(L_STR, P_SEP)),P_SEP);
                L_STR := SUBSTR(L_STR, INSTR(L_STR, P_SEP) + 1);
        END LOOP;
        RETURN L_TABSTR;
END table_str ;


select * from table(cast(table_str('1,2,3') as tabstr_t));  








create or replace 
FUNCTION GET_SPLIT_STR(v_str_list in varchar2 ) RETURN varchar2
is--拿截断后的字符串
v_str varchar2(20);
BEGIN
  if instr(v_str_list,',') > 0 then
    select substr(v_str_list,1,length(v_str_list)-length(substr(v_str_list,instr(v_str_list,',')))) into v_str from dual;--拿逗号前的字符串
    return v_str;
  else  
     return v_str_list;
  end if;   
END GET_SPLIT_STR;




create or replace 
FUNCTION GET_SPLIT_STR_LIST(v_str in varchar2 ) RETURN varchar2
is--拿截断后的字符串
v_str_list varchar2(4000);
BEGIN
  if instr(v_str,',') > 0 then
    select substr(v_str,instr(v_str,',')+1) into v_str_list from dual;
    return v_str_list;
  else
     return v_str;
  end if;
END GET_SPLIT_STR_LIST;
************************************************************************************************************************************************************************************************************************************


--例外
   --用户定义例外及处理例外
set serveroutput on


declare
  cursor cemp is select ename from emp where deptno=70;
  pename emp.ename%type;
  
  no_emp_found exception;
begin
  open cemp;
  fetch cemp into pename;
  if cemp%notfound then 
    raise no_emp_found;
  end if;


  close cemp;
  
exception
  when no_emp_found then dbms_output.put_line('??????');
end;
/





--创建存储过程
create [OR REPLACE] PROCEDURE 过程名
(参数列表)
as
PL/SQL 子程序


--赋于scott 调试的权限
grant debug connect session, debug any procedure to scott;




--存储函数
为一命名的存储程序，可带参数，并返回一计算值
函数和过程的结构类似，但必须有一个return 子句
用于返回函数值。函数说明要指定函数名，结果值的
类型，以及参数类型等。

create [or replace] function 函数名(参数列表)
return 函数值类型
AS
PLSQL子程序体




--过程和函数中的in 和  out
一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值


--但过程和函数都可以通过out 指定一个或多个输出参数。我们可以利用out参数，
   在过程和函数中实现返回多个值 。 


 


--返回一个游标
    --声明一个包
create or replace package pkName AS
type empcurosr is ref cursor;
procedure pdName(eid in nuber,empinfo out empcurosr);
  end pkName;
    --实现包  要把里面的存储过程  和 存储函数一起实现
    --创建包体
    create or repalce
package body mypackage as
 procedure pdName(dno in nuber,empList out empcursor) As
 begin
open empList for Select * from emp where deptno=dno;

 end pdName;
end mypackage;
--包的调用
@Test
public void testCursor(){
String sql = "{call MYPACKAGE.queryDeptEmpList(?,?)}";

Connection conn =null;
CallableStatement call = null;
ResultSet rs  = null;
try{
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
call.setInt(1, 20);
call.registerOutParameter(2, OracleTypes.CURSOR);
call.execute();

rs = ((OracleCallableStatement)call).getCursor(2);
while(rs.next()){
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
String job = rs.getString("empjob");

System.out.println(ename);
System.out.println(sal);
System.out.println(job);
System.out.println("-------------------");
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
JDBCUtils.release(conn, call, rs);
}
}




--触发器
是一个与表相关联的。存储的PL/SQL程序。每当一个特定的数据操作语句(insert，update，delete)
指定的表上发出时 ，Oracle  自动地执行触发器中定义的语句序列。




--创建触发器
create[or replace] trigger  触发器名
{before|after}
{delete|insert|update[of 列名]}
on  表名
[for eachrow 【when（条件）】]
PL/sql  语句块


--触发器可用于   
数据确认
实施复杂的安全性检查
做审计，跟踪表上所做的数据操作等   audit 审计
数据的备份和同步




OCP   oracle 管理  主要讲 audit审计


ORALCE中有个工具 RMAN = recovery Manager  恢复管理器  命令工具





--触发器类型
--语句级触发器
在指定的操作语句操作之前或之后执行一次   不管这条语句影响了多少行。


--行级触发器 
       触发语句作用的每一条记录都被触发。在行级触发器中使用old 和new伪记录
变量 识别值得状态。

   create or replace trigger checkSal
   before update on emp
   for each row
   begin
if :old.sal>:new.sal then---:old :new  是行级触发器的两个 变量代表旧值和新值。。
PL/SQL  语句块;
--抛例外
raise_application_error(-20002,'涨后工资不能少于涨前工资。涨后：'|| :new.sal||'  涨前:'||:old.sal);
end if;
   end;

--数据词典  --由管理员创建和维护  
--数据字典命名规则
前缀                  说明
user   用户自己的
all   用户可以访问到的
dba   管理员视图
vs   性能相关的数据

 --DICTIONARY


 --USER_OBJECTS           我创建的


 --ALL_OBJECTS  所有我能访问的 对象

 --USER_TABLES  我创建的表


 --USER_TABLES            所有我能访问的表


 --USER_TAB_COLUMNS       我创建的列


 --USER_CONSTRAINTS  我创建的约束


 --USER_CONS_COLUMNS  当前用户创建的列约束


 --USER_VIEWS  我创建的视图


 --USER_SEQUENCES  我创建的序列


 --USER_synonyms  我创建的同义词


 --USER_trigger  我创建的触发器


 --USER_source    源代码