Oracle学习笔记

Oracle基本命令

OracleService<数据库名字> --数据库的服务
网络服务名  TNSNAMES 被客户端使用连库串 
--------------------------------
 
连接命令

sqlplus shebao@dddddddd
sqlplus 用户@网络服务名
创建表空间

--数据文件的存放地-- 表空间
 
 create tablespace [表空间]
 datafile '[文件]'
 size [大小]
 autoextend on next [自增大小]
 maxsize unlimited;
 
--用户
 
create user [用户]
identified by "[密码]"
default tablespace [表空间]
temporary tablespace temp
profile default;
 
--授权
 
grant connect,dba,resource to [用户]
 
连接字符串

jdbc.driverClassName=oracle.jdbc.driver.OracleDriver                 
jdbc.url=jdbc:oracle:thin:@localhost:1521:turing
权限

  --直接权限和间接权限的区别  : 封装体内
revoke SELECT ANY TABLE from <user>;
grant SELECT ANY TABLE to <user>;
grant dba ,connect, resource to <user>;
 
 
 
 
select * from shebao.gsbx_dwxxb;
 
 
create view vvvv as
select * from shebao.gsbx_dwxxb;
 
 
 
 
declare
   i number :=0;  
begin
   dbms_output.enable(999999);
   for c in (select table_name c1,comments c2 from dictionary) loop
       i := i + 1;
       dbms_output.put_line(lpad(i,4,' ')||' '||c.c1);
   end loop;
end;
 
 
create procedure print as
 i number :=0;  
begin
   dbms_output.enable(999999);
   for c in (select table_name c1,comments c2 from dictionary) loop
       i := i + 1;
       dbms_output.put_line(lpad(i,4,' ')||' '||c.c1);
   end loop;
end;
 
 
begin
  print;
end;
 
 
一般的数据库规范

1 单独指定4个超级用户的密码
 
2 创建好相应的数据库角色
   A dba        备份  还原 导入 导出  建表 删表 改表   建包 修改profile 修改parameter
   B manager    备份  还原 导入 导出  建表 删表 改表
   c developer  增删改查  某些表 在某些特定包下 建立存储过程   在某些特定表下 建立触发器
3 创建数据库用户 授予角色
 
4 锁定4个超级用户  只允许本机登陆
 
数据的导入与导出

导出

exp 用户密码@网络服务名 file=文件地址 owner = 导谁的东西
exp shebaosa@dddddddd file=eshebao_bcode.dmpowner = shebao
 
 
exp 用户密码@网络服务名 file=文件地址 tables = 导哪些表
exp shebaosa@dddddddd file=eshebao_bcode.dmp tables=
 
导入

imp 用户密码@网络服务名 file=文件地址 fromuser = 从哪来 touser= 进哪去
impshebaosa@dddddddd file=eshebao_bcode.dmpfromuser = shebao touser= liushengbo
 
类型：

number

默认是number(38) 总长不超过38就行
Number(3,4) 只能存储形如333.4444
Number(5,0)只能存储整数
 
varchar2

是一个最普通的变长的字符型，会将存储数据编码转换为本地数据库编码
char

Char是一定长的字符型，会用空格补齐长度
 
Db link

  create public database link <名>
       connect to <用户> identified by"<密码>" using '<网络服务名>'
      
select * from 远程表@<dblink的名字>
约束

  --约束 保护数据库数据完整性 一致性的 数据库对象
 
 
1 primary key  P
 
2 foreign key  R
 
3 unique       U
 
4 not null     C
 
select * from user_constraints
函数

日期函数

日期函数
sysdate to_date  months_between add_months next_day last_day
例句：(sysdate)

select sysdate ,'2011-12-15 9:04:40' b from dual
 to_date(值,格式化字符)
例句：(to_date)

两个日期求差 单位是天 周时分秒 直接减
select (sysdate - to_date('1995-06-15','yyyy-mm-dd')) from dual
例句：(months_between)

  两个日期求差 单位是月 年   months_between
select months_between(sysdate , to_date('1995-06-15','yyyy-mm-dd')) from dual
 
  日期向前/后推算  单位是天 周时分秒 直接加减
select sysdate-26 from dual
  日期向前/后推算  单位是月 年  add_months
例句：(add_months)

select add_months(sysdate,-26) from dual
 
例句：(next_day)

下一个星期几
select next_day(sysdate,'星期五') from dual
 
例句：(last_day)

某月最后一天
select last_day(to_date('2011-02-02','yyyy-mm-dd')) from dual
 
 
字符函数

例句：(to_char)

select to_char(sysdate,'Month ddthyyyy  Day','nls_date_language=american') from dual
  
  
 mi    分钟
 hh    12小时制的小时
 hh24  24小时制的小时
 ss    本分第几秒
 sssss 本天第几秒
 w     本月第几周
 ww    本年第几周
 day   星期几
 q     第几季
 nls_date_language=形容词格的国家
 
2011年12月15号怎么显示?
select to_char(sysdate,'yyyy"年"mm"月"dd"号"') from dual
数字函数

select mod(9,3) from dual
 select power(5,3) from dual
 select power(5,1/2) from dual
 select power(5,-2) from dual
 select ceil(3.14159) ,floor(3.14159) from dual
 select round(3.14159,4),round(13.14159,-1),round(3.14159) from dual
 select trunc(3.14159,4),trunc(19.14159,-1),trunc(3.14159),
        trunc(sysdate),trunc(sysdate,'hh')
  from dual
 select abs(-333),log(2,32),ln(2.71828),sign(98874-555.32),
  sin(3.1415926535/6)
  from dual
 
 
sequence

sequence  序列  可以提供一个不断自增的整数(nextval)
例句：(创建序列)

create sequence seq_test
minvalue 1000000
maxvalue 9999999
start with 1000000
increment by 1
cache  20
 
例句：(使用序列)

select seq_test.nextval from dual
 
select seq_test.nextval,seq_test.nextvalfrom scott.emp
在hibernate中使用序列        

 
  <generator class="sequence">
              <paramname="sequence">seq_test</param>
  </generator>
 
 
q     第几季
 nls_date_language=形容词格的国家
 
2011年12月15号怎么显示?
 
select to_char(sysdate,'yyyy"年"mm"月"dd"号"') from dual
 
在hibernate 中使用序列        
   <generatorclass="sequence">
              <paramname="sequence">seq_test</param>
  </generator>
 
日期类的所有函数
sysdate months_between add_months to_date() to_char()
 selectnext_day(sysdate,'星期五') from dual
 selectlast_day(to_date('2011-02-02','yyyy-mm-dd')) from dual
数字类
求余：mod(9,3)
求指数：power(5,3)
开方：power(5,0.5)
取整(顶)：ceil
取底floor
精确取位：round(33.11111,3)
 
  数字类 加减乘除余平方开放
 + - * / 
 select mod(9,3)from dual
 select power(5,3)from dual
 selectpower(5,1/2) from dual
 select power(5,-2)from dual
 selectceil(3.14159) ,floor(3.14159) from dual
 selectround(3.14159,4),round(13.14159,-1),round(3.14159) from dual
 selecttrunc(3.14159,4),trunc(19.14159,-1),trunc(3.14159),
        trunc(sysdate),trunc(sysdate,'hh')
  from dual
 selectabs(-333),log(2,32),ln(2.71828),sign(98874-555.32),
 sin(3.1415926535/6)
  from dual
 
 
 
字符函数
--upper  lowerinitcap
select upper('Oracle_training') a,
      lower('Oracle_training') b,
      initcap('oraCle?traiNing') c from dual
      
--ascii  chr ||
 
select ascii('A') , chr(38) from dual
 
select 'a'||chr(10)||'b' from dual
 
select '131'||'34563412' from dual
 
--lpad  rpad
 
select lpad('3412',11,' ') from dual
 
--ltrim  rtrim  trim
 
select ltrim('  Oracle_training  ') a1,rtrim(' Oracle_training  ') a2,
       '  Oracle_training  ' b from dual
      
select ltrim('Oracle_training','arO') a1,
      rtrim('Oracle_tringingaining','ing') a1 from dual
      
select trim (' Oracle_training  ') t1,
trim ('O' from 'OOOOracle_trainOngOOO') t2,
trim (leading 'O' from 'OOOOracle_trainOngOOO') t3 from dual
 
-- length instr  substr replace
 
select length('汉字') from dual
 
select instr('abstractabdbaadbnaab3','a',-1,4) from dual
 
select substr('achdgjht',3,5),substr('achdgjht',-3,2)from dual
 
select substr('achdgjgt',4) from dual
 
select replace('ahdabaannarayarat','aa','Cow') from dual
 
select replace('ahdabaannarayarat','a') from dual
 
-通达街-西大桥-工大-铁路局-建设街-医大医院-烟厂-承德广场-
-承德广场-烟厂-医大医院-铁路局-工大-西大桥-通达街-
第一站
select substr(stops,1, 第一个"-"- 1) from busstop
select substr(stops,1,instr(stops,'-',1,1) - 1) from busstop
第三站
select substr(stops,第二个"-"后面1个, 到第三个"-" - 第二个"-" -1) from busstop
select substr(stops,
             instr(stops,'-',1,2) + 1,
             instr(stops,'-',1,3) - instr(stops,'-',1,2) -1) a from busstop
最后一站
select substr(stops,最后一个"-" 后面一个) from busstop
select substr(stops,instr(stops,'-',-1,1)+1) from busstop
第七站
select substr(stops,
             instr(stops,'-',1,6) + 1,
             instr(stops,'-',1,7) - instr(stops,'-',1,6) -1) a from busstop
 
---查询时  数据库里的数据如果不符合规律 可以在SQL里进行补全 让其变得有规律
 
select substr('-'||stops||'-',
             instr('-'||stops||'-','-',1,1) + 1,
             instr('-'||stops||'-','-',1,2) - instr('-'||stops||'-','-',1,1) -1) afrom busstop
public static void main(String[] args) {
//     String a ="forgetsam@turingedu.com";
//     System.out.println(a.matches("[A-Za-z]+@[a-z0-9]{2,20}\\.com(\\.cn)?"));
      
      
       String b = "承德广场-烟厂-医大医院-铁路局-工大-西大桥-通达街";
       
           Pattern p = Pattern.compile("[^\\-]+");
           Matcher m= p.matcher(b);
           while(m.find()){
              System.out.println(m.group()); 
           }
       }
 
//
正则表达式   抽象描述一组字符 的 表达式
[]                    来表示  单个字符
a   穷举[wxyz]        四者之一
b   范围[a-z]         小写字母26之一
a+b     [a-zORACLE]
c   取反[^A]          除了A什么都可以
c+a    [^wxyz]       除了w,x,y,z什么都可以
c+b     [^a-z]        除了小写字母什么都可以
 
{}    来表示  前一个表达式出现的次数
a 从到  {1,5}        1~5次都可以
b 从到无穷{1,}
 
 
转义 
\[  \} \-  \\  \^  \$   \?  \+  \*
 
缩写
[A-Za-z0-9_]      \w
[0-9]             \d
{0,1}             ?
{1,}              +
{0,}              *
 
 
---- 查询数学成绩比外语成绩高的同学的学号、姓名、数学成绩、外语成绩、差多少分。
 
 
decode 函数用法

 
decode 最丰
      nvl(变量,替换值) = nvl2(变量,变量,替换值) = decode(变量,null,替换值,变量)
       nvl2(变量,非空值1,空值2) = decode(变量,null,空值2,非空值1)
 
行转列：
 
decode  case when then else end
 
分别用case和decode函数列出员工所在的部门，
deptno=10显示'部门10',
deptno=20显示'部门20'
deptno=30显示'部门30'
deptno=40显示'部门40'
否则为'其他部门'
 
select ename ,deptno,
 
decode(deptno,10,'部门10',20,'部门20',30,'部门30',40,'部门40','其它部门') a from scott.emp
 
select ename ,deptno, case deptno 
                    when 10 then '部门10'
                    when 20 then '部门20'
                    when 30 then '部门30'
                    when 40 then '部门40'
               else '其它部门' end a
                       from scott.emp
                      
--2 套路 把一列数字拆分成几列的时候 decode(判断条件, ,1,0)  相当于对某个列使用where
select stu_id, stu_name, sum(decode(subject,'数学',score,0)) sc1 ,sum(decode(subject,'外语',score,0)) sc2 from score -- where subject = '数学'
group by stu_id, stu_name
 
 
 
 
递归题目

select * from (
select ename,(select count(*) from scott.emp
        where ename <> e.ename
        connect by mgr = prior empno
        start with ename=e.ename ) c ,
        (select sum(sal) from scott.emp
          where ename <> e.ename
          connect by mgr = prior empno
          start with ename=e.ename )s,
        (select max(sal) from scott.emp
          where ename <> e.ename
          connect by mgr = prior empno
          start with ename=e.ename )ma,
        (select min(sal) from scott.emp
          where ename <> e.ename
          connect by mgr = prior empno
          start with ename=e.ename )mi  from scott.emp e)
         
    where c > 0
         
        
 
 
selectsys_connect_by_path(ename,'-')  path,    --树上一支
       level ,                                  --第几层
       connect_by_isleaf,                       --是否还有子节点
       connect_by_root(ename)                   --根节点的属性
empno, ename, job,mgr, hiredate, sal, comm, deptno
from scott.emp
connect by mgr = prior empno
start with mgr is null
 
 
 
 

 
rownum  分页
 
 
select * from
(
    select rownum rn, empno, ename, job, mgr, hiredate, sal, comm, deptno from emp
    where rownum < 7
)
where  rn >3
--------------------------两层 / 三层分页---------------------------
select * from
  (
  select rownum rn,t.* from
      (
      select  empno,ename, job, mgr, hiredate, sal, comm, deptno from emp
      order by sal
      )t where rownum < 7
   )
where  rn >3
 
-------------------------------------------------
select * from
(
select  row_number() over(order by sal) rn,
        empno, ename, job, mgr, hiredate, sal,comm, deptno from emp
)
where  rn >3 and rn <7
 
 
---横向
select substr(mymonth,1,4) year,
       sum(decode(substr(mymonth,5,2),'01',sell,0)) mon1,
       sum(decode(substr(mymonth,5,2),'02',sell,0)) mon2,
       sum(decode(substr(mymonth,5,2),'03',sell,0)) mon3,
       sum(decode(substr(mymonth,5,2),'04',sell,0)) mon4,
       sum(decode(substr(mymonth,5,2),'05',sell,0)) mon5,
       sum(decode(substr(mymonth,5,2),'06',sell,0)) mon6,
       sum(decode(substr(mymonth,5,2),'07',sell,0)) mon7,
       sum(decode(substr(mymonth,5,2),'08',sell,0)) mon8,
       sum(decode(substr(mymonth,5,2),'12',sell,0)) mon12 from sale
       group by substr(mymonth,1,4) order by substr(mymonth,1,4)
 
select mymonth, sell ,
       decode(substr(mymonth,5,2),'01',sell,0) mon1,
       decode(substr(mymonth,5,2),'02',sell,0) mon2,
       decode(substr(mymonth,5,2),'03',sell,0) mon3,
       decode(substr(mymonth,5,2),'04',sell,0) mon4,
      
       decode(substr(mymonth,5,2),'12',sell,0) mon12 from sale
      
--纵向
 
select  substr(mymonth,1,4) year, sum(sell)  from (
    select to_char(n,'yyyymm') mymonth, nvl(sell,0) sell from sale 
    right join (select add_months(to_date('199912','yyyymm'),level) n from dual connect by level <= 48)t
    on to_char(n,'yyyymm')= mymonth )
   
    group by substr(mymonth,1,4)
union all
select to_char(n,'yyyymm') mymonth, nvl(sell,0) sell from sale 
    right join (select add_months(to_date('199912','yyyymm'),level) n from dual connect by level <= 48)t
    on to_char(n,'yyyymm')= mymonth
   
    order by year
   
   
   
   
   
    横向拆分数据  sum(decode(**,,1,0))
   
    纵向拆分数据  group by
 
 
 
 
 
 
 
 
rowid  行标记 在表空间内不重复  可以做最后的定位
 
 
 
delete from  emp  where rowid not in
(
    select max(rowid)
    from emp group by empno, ename, job, mgr, hiredate, sal, comm, deptno
)
PL-SQL

基本语法

declare
begin
  dbms_output.put_line('Helo,World!');          --System.out.println();  
end;
例句：

抛异常

declare
  v number(10);
begin                                   --try{
  --select 1 into v from dual where 1=2;
  dbms_output.put_line(1/0); 
  exception                             --}catch (Exceptione){ 
     when others  then
     dbms_output.put_line(sqlerrm);     -- e.printStackTrace();
end;
例句1：(循环_for_打印金字塔)

：
declare   --pl-sql    Procedural Language  StructedQuery  Language
begin
   for h in 1..5 loop
       for k in 0..5-h-1 loop
           dbms_output.put(' '); 
       end loop;
       for o in 0..2*h-1-1 loop
           dbms_output.put('o');
       end loop;
       dbms_output.put_line('');
   end loop;
end;
 
例句2：(循环_while_蛤蟆爬井)

declare
   gao number(38,0) := 0;  --声明区 赋值用 :=  
   bw boolean := true;
begin
   while gao < 10 loop
      if bw = true then     --判断用 =           
         gao := gao + 5;
         bw := false;
      else
         gao := gao - 4;
         bw := true;
      end if;
      dbms_output.put_line(gao); 
   end loop; 
end;
 
例句3：(循环_do..while_)

declare
  i number(10,0) :=0 ;
begin
  loop
    exit when i>6 ;
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;
 
例句3：(选择_if else)

选择
declare
  s number(10,0) := 4 ;
begin
  if s = 1  then
     dbms_output.put_line('31');
  elsif s=2  then
     dbms_output.put_line('28');
  elsif s in( 3,5,7,8,10,12)  then
     dbms_output.put_line('31');
  else
     dbms_output.put_line('30');
  end if;
end;
 
 
例句4：(选择_case)

declare
  s number(10,0) := 11 ;
begin
  case s
     when 1  then
     dbms_output.put_line('31');
     when 2  then
     dbms_output.put_line('28');
     when 3  then
     dbms_output.put_line('31');
     when 4  then
     dbms_output.put_line('30');
     when 5  then
     dbms_output.put_line('31');
     when 6  then
     dbms_output.put_line('30');
     when 7  then
     dbms_output.put_line('31');
     when 8  then
     dbms_output.put_line('31');
     when 9  then
     dbms_output.put_line('30');
     when 10 then
     dbms_output.put_line('31');
     when 11 then
     dbms_output.put_line('30');
     when 12 then
     dbms_output.put_line('31');
  end case;
end;
 
游标

游标 - 数据库内部对象         -- java: ResultSet
 
1 某个结果集的路标(内存地址) cursor c is select type, subtype from tongji;
2 操作结果集 读取值的方法  fetch <游标> into <变量>,....
3 此结果集的状态参数 %notfound %found %rowcount
select  =============>  替代游标循环
    where  =============>  替代选择结构
    函数   =============>  替代拼接,计算
   
    sql   =====思路简化===== pl-sql
 
动态游标

 
静态游标不好使，有了关键位置变量的时候使用动态游标
 
作用：1,、动态游标可以封装
2、可以做返回（可以用存储过程实现）—程序复杂到无法用HQL语句来完成
3、
使用方式

动态游标 Ref Cursor ==> sys_refcursor
 
declare
    c sys_refcursor; --声明动态游标类型
    n number(10,0):=18;
    tbname varchar2(40) := 'month';
    --cursor c is select code,codenamefrom shebao.tbname where rownum <= n;
    v_code varchar2(40);
    v_codename varchar2(40);
begin
    open c for 'select code,codenamefrom shebao.'||tbname||' where rownum <='||n||'';
    loop
      fetch c into v_code,v_codename;
      exit when c%notfound;
      dbms_output.put_line(v_code||' '||v_codename);
    end loop;
    close c;
end;
例句：(创建自己的代码表)__绑定变量

declare
    c sys_refcursor;
    n number(10,0):=11;
    tbname varchar2(40) := 'month';
    --cursor c is select code,codenamefrom shebao.tbname where rownum <= n;
    v_code varchar2(40);
    v_codename varchar2(40);
begin
   
    execute immediate
    'create table '||tbname||' (code varchar2(40),codenamevarchar2(40))';
   
 
    open c for 'select code,codename from shebao.'||tbname||' where rownum <=:nn' using n;
    loop
      fetch c into v_code,v_codename;
      exit when c%notfound;
      execute immediate
      'insert into '||tbname||' values(:p1,:p2)' using v_code,v_codename;
    end loop;
    commit;
    close c;
end;
 
例句：(用于返回)__分页存储过程

create or replace procedure pro_page_cur(v_sql in varchar,cong in number,dao in number, rs out sys_refcursor) as
str_pre varchar2(99) := 'select * from(select rownum rn,t.* from (';
str_nxt varchar2(99) := ' )t where rownum<= :dao) where rn >:cong ';
begin
    open rs for str_pre||v_sql||str_nxt using dao,cong;
end;
 
———java中调用代码：
 
import java.sql.*;
 
 
public class RunPro{
 
     public static void main(String[]args) throws SQLException,ClassNotFoundException{
          Class.forName("oracle.jdbc.driver.OracleDriver");
         Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:turing","liushengbo000","sa");
         
          //pro_page_cur(v_sql in varchar,cong innumber,dao in number, rs out sys_refcursor)
         CallableStatement cst = con.prepareCall("{call pro_page_cur(?,?,?,?)}");
         cst.setString(1,"select *from nation");
         cst.setInt(2,3);
         cst.setInt(3,10);
         cst.registerOutParameter(4,-10);//oracle.sql.OracleType.CURSOR
         cst.execute();
         
          ResultSetrs = (ResultSet)cst.getObject(4);
          while (rs.next()){
             System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
         
          }
         rs.close();
         cst.close();
         con.close();
 
     }
}
隐式调用

begin 
  for hang in (select type, subtype from tongji where rownum <=20) loop
    dbms_output.put_line(hang.type||' '||hang.subtype);
  end loop;             
end;
------------------- 以上 称为 隐式调用 ----------
显示调用

declare
  cursor c is select type, subtype from tongji;
  col1 varchar2(40);
  col2 varchar2(400); 
begin
  open c;
  loop
    fetch c into col1,col2;
    exit when c%notfound;
    dbms_output.put_line(col1||' '||col2);
  end loop;             
  close c;
end;
 
 
例题

例句1：(其它数据库转移过来的大小写混合的表)

应用1 其它数据库转移过来的大小写混合的表
select ct_id, code, codename,fathercode from"AdjustCause"
 
 
alter table"AdjustCause" rename to AdjustCause
 
declare
  sqlstr varchar2(400);
begin
   for hang in (select table_name from user_tables where table_name <> upper(table_name) ) loop
       sqlstr := 'alter table "'||hang.table_name||'" rename to '||hang.table_name||'';
       --dbms_output.put_line(sqlstr)  ;
       execute immediate sqlstr;
   end loop;
end ;
例句2：(写一段 PL-SQL 把当前用户名下大小写混合的字段改为大写)

declare
    sqlstr varchar2(400);
begin
      for c in (select table_name ,column_name ,data_type  from user_tab_cols
          where column_name <> upper(column_name)) loop
          sqlstr := 'alter table '||c.table_name||' rename column"'||c.column_name||'" to '||c.column_name||'';       
          --dbms_output.put_line(sqlstr);
          execute immediate sqlstr;
      end loop;
end;
 
例句3：(写一段 PL-SQL 把当前用户名下表中的空格去掉)

方法1

declare
    sqlstr varchar2(4000);
begin
     dbms_output.enable(1000000);
      for c in (select 'update '||table_name||' set '||wm_concat(column_name||'=trim('||column_name||')') str
                from user_tab_cols where data_type='VARCHAR2'
                group by table_name) loop
          sqlstr := c.str;
          dbms_output.put_line(sqlstr);
          --execute immediate sqlstr;
      end loop;
end;
 
方法2

declare
    sqlstr varchar2(4000);
    shu number;
begin
     dbms_output.enable(1000000);
      for c in (select table_name  from user_tab_cols group by table_name) loop
          sqlstr := 'update '||c.table_name||' set ';
         
          for d in (select column_name  from user_tab_cols  where table_name = c.table_nameand data_type='VARCHAR2' ) loop
              sqlstr := sqlstr||d.column_name||'=trim('||d.column_name||'),'; 
          end loop;
          shu := instr(sqlstr,',',1,1);
          if shu <> 0 then
            sqlstr := rtrim(sqlstr,',');
            --dbms_output.put_line(sqlstr);
            execute immediate sqlstr;
          end if;
      end loop;
      commit;
end;
 
   
 
动态sql (execute immediate)

如果其它位置使用变量 需要使用动态SQL execute immediate
--动态语句的使用方式
1如果其它位置使用变量 需要使用动态SQL execute immediate
  a insert/delete/update/create/drop/alter  直接 execute immediate 'i/d/u'
  b select into 直接 execute immediate 'select ....' into
2如果使用DDL语句(create drop alter purge) 无论有没有变量都必须动态SQL
 
3如果表(其它对象)是在当前语句块中创建出来的,
对于语句块里的其它语句,它们在编译时是不存在的
动态创建对象,后面的操作语句必须动态sql
例句1：

declare
   tabname varchar2(40) := 'aa_test';
begin
  execute immediate
  'create table '||tabname||'(idnumber,name varchar2(40))';
  execute immediate
  'insert into aa_test(id,name)values(1, ''张三'')';
  --dbms_output.put_line('insert intoaa_test(id, name)values(1, ''张三'')');
end;
 
存储过程

创建

  create [or replace] procedure <名>(<参数名> <进出> <参数类型>[,.....]) as
--declare 去掉
 
例句：

创建存储过程

create or replace procedure pro_yue1(mon in number) as
--declare
  s number(10,0) := mon ;
begin
  case
     when s in (1,3,5,7,8,10,12)  then
          dbms_output.put_line('31');
     when s in (4,6,9,11)  then
          dbms_output.put_line('30');
     when s = 2  then
          dbms_output.put_line('28');
  end case;
end;
 
带输出存储过程

create or replace procedure pro_yue2(mon in number,dds out number) as
--declare
  s number := mon;
begin 
  case
     when s in (1,3,5,7,8,10,12)  then
          dds := 31;
     when s in (4,6,9,11)  then
          dds := 30;
     when s = 2  then
          dds := 28;
  end case;
end;
 
执行过程

数据库语句：

declare
begin
  pro_yue1(2);
end;
 
java环境中调用（不带参数）

package a.b.c;
 
import java.sql.*;
 
 
public class 存储过程{
 
     public static void main(String[] args) throws SQLException,ClassNotFoundException{
         Class.forName("oracle.jdbc.driver.OracleDriver");
         Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:turing","dba_01","sa");
         CallableStatement cst = con.prepareCall("{call pro_upper_tableName}");
         cst.execute();
          
         cst.close();
         con.close();
 
     }
}
 
Java环境中调用（带参数）

package a.b.c;
 
import java.sql.*;
 
 
public class 存储过程带参数{
 
     public static void main(String[] args) throws SQLException,ClassNotFoundException{
         Class.forName("oracle.jdbc.driver.OracleDriver");
         Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:turing","liushengbo000","sa");
         
         //pro_yue2(mon innumber,dds out number) as
         CallableStatement cst = con.prepareCall("{call pro_yue2(?,?)}");
         cst.setInt(1,2);
         cst.registerOutParameter(2,java.sql.Types.INTEGER);//java.sql.Types.INTEGER=4
         cst.execute();
         
         System.out.println(cst.getInt(2));
         cst.close();
         con.close();
 
     }
}
 
自定义函数（function）

必须有返回值
创建

create or replace function fun_yue(mon in number)return number as
--declare
  s number(10,0) := mon ;
begin
  case
     when s in (1,3,5,7,8,10,12)  then
          return 31;    --必须有返回值
     when s in (4,6,9,11)  then
          return 30;
     when s = 2  then
          return 28;
     else
          return null;
  end case;
end;
 
使用

select fun_yue(100) from dual
 
包

包内函数 包内过程
将功能相近/结构相似/相互之间有调用依赖关系的
type  constant exception function procedure
封装到一起  就是包 package
 
目录(directory)

目录 directory  对硬盘上文件夹的一个映射 绝大部分时候用来配合Oracle文件读写包操作
 
创建：

create directory dir_me as 'E:\ora';
 
使用：

写文件

declare
    f utl_file.file_type ; --文件读写包
begin
f :=utl_file.fopen('DIR_ME','test.txt','a'); --w - writable  r - readonly a – all
--第一个参数必须完全大写，2个参数文件名，3读写方式
    utl_file.put_line(f,'内容内容内容内容内容内容内容');
    utl_file.fclose(f);
end;
 
读文件

declare
    f utl_file.file_type ;
    neirong varchar2(999);
begin
    f := utl_file.fopen('DIR_ME','Tqws.java','r'); --w - writable  r - readonly a - all
    loop
      utl_file.get_line(f,neirong);
      dbms_output.put_line(neirong);
    end loop;
    exception
      when no_data_found then
         dbms_output.put_line('--正常结束--');
         utl_file.fclose(f);
      when others then
         dbms_output.put_line('--非正常结束--');
         utl_file.fclose(f);    
end;
 
触发器（trigger）

作用：

监听  捕获到指定的事件时 自动执行  无法调用  没有参数
 
触发器可以针对用户的数据库操作   补全业务逻辑  简化用户的数据库操作逻辑
 
创建：

create or replace triggertri_casade
--
例句

例句1：(级联删除)

--级联删除
create trigger tri_casade
before delete on dept for each row
declare
begin
    delete from emp where deptno = :old.deptno;
end;
例句2：(生成主键)

--生成CT_ID
create or replace trigger tri_enterprisetype
  before insert on shebao.enterprisetype for each row
declare
    entityName varchar2(40) := 'enterprisetype';
    maxid varchar2(40);
    seq number;
    clen number;
    ctype varchar2(40);
    finalid varchar2(40);
begin
    execute immediate 'select max(ct_id) from shebao.'||entityName||'' into maxid;
    select codelength,codetypeinto clen,ctype fromshebao.codenavigation where upper(codetype) = upper(entityName);
    if maxid is null then
      seq := 1;
    else
      seq := substr(maxid,-clen);
      seq := seq + 1;
    end if;
    finalid := ctype||lpad(seq,clen,'0');
    :new.ct_id := finalid;
end;
教师机 10:02:44 (多人发送)
  --级联删除
create trigger tri_casade
before delete on dept for each row
declare
begin
    delete from emp where deptno = :old.deptno;
end;
例句3：()

 
create or replace trigger tri_casade
before delete or insert or update of loc on dept for each row
declare
    f utl_file.file_type ;
begin
    if inserting then
      null;
    elsif updating then
      f := utl_file.fopen('DIR_ME','test.log','a');
      utl_file.put_line(f,'老部门是:'||:old.dname);
      utl_file.put_line(f,'新部门是:'||:new.dname);
      :new.dname := :old.dname;
      utl_file.fclose(f);
    elsif deleting then
      delete from emp where deptno = :old.deptno;
    end if;
end;