Oracle PL/SQL 入门

[declare] --定义变量，游标，例外 begin --pl/sql程序体 [exception] --处理异常 end;--分号

declare inumber:=1; --orcale中使用:=进行变量赋值 PIconstant3.14; --常量的关键字 pempemp%rowtype; --记录型变量(存放了表中的一个行数据) pnameemp.ename%type;--引用型变量(引用了表中某一列的类型) begin i:=i+1; dbms_output.put_line('i的值是:'||i); --PI:=PI+1;--报错 dbms_output.put_line(PI); select*intopempfromempwhereempno=7369; '员工的编号:'||pemp.empno||'姓名:'||pemp.ename); selectenameintopname7499; '7499的姓名:'||pname); end;

if条件then 语句； elsif条件then else 语句 endif;

declare pnumnumber:=&pnum;--&表示从控制台接收参数 begin ifpnum=1then dbms_output.put_line('我是1'); endif; end;

agenumber:=&age; ifage<18'未成年'); elsifage>=andage<40then--注意oracle中用的是elsif '成年人'); else '老年人');

while条件loop 循环体。。。 endloop;

loop exitwhen退出循环的条件； endloop;

i1; begin whilei<=10loop dbms_output.put_line(i); i:=i+loop; ;

exitwheni>10; --使用语法3在控制台打印输出1~10

foriin1..10 end;

======================================= 游标<光标> ================================== === /* 游标：就是oracle中存放一个结果集 声明语法： cursor游标名称is查询语句; 使用游标： open游标名称;--打开游标 loop--循环遍历数据 fetch游标名称into变量（记录型变量）； exitwhen游标%notfound; endloop; close游标名称; */ --输出所有的员工信息(光标实现)

cursorpcisfromemp; pempemp%rowtype; openpc; loop fetchpcintopemp; whenpc%notfound; dbms_output.put_line(pemp.empno||'--'||pemp.ename); loop; closepc; --输出指定部门的员工信息 -- 给游标指定参数

cursorpc(dnonumber)wheredeptno=dno; openpc(20); dbms_output.put_line(pemp.ename||'--'||pemp.empno);

======================================= 异常 ======== == ========== ==== =========== ===

i1; begin i:=5/0; --i:='abc'; exception--处理异常 whenzero_dividethen dbms_output.put_line('除数不能是0'); whenvalue_error'设置错误'); whenothers'其他异常'); ;

--使用记录型变量存放多条数据

pempemp%rowtype; fromemp; exception whentoo_many_rows'返回多条记录'); --查询部门编号为50的员工 --自定义异常

cursorpcisselect*fromempwheredeptno=50; no_emp_dataexception; ifpc%notfoundraiseno_emp_data;---抛出一个异常 if; closepc; exception whenno_emp_data'没有发现员工信息'); ;

========================================= 存储过程 ======== ==============================

定义：定义了一段pl/sql程序，事先编译好，存放到数据库端，供其他程序员来调用。 语法： create [or replace] procedure 过程名(参数1 in|out 参数类型) as|is --声明变量，游标 相当于declare beign --定义一段pl/sql程序体 end;
--给指定的员工涨工资，并且打印出涨前跟涨后的工资 --引用型变量(引用了表中某一列的类型) --记录型变量,存储了一行的数据
create or replace procedure emp_add_sal(eno in number) as psal emp.sal%type; -- psal number; pemp emp%rowtype; begin select sal into psal from emp where empno = eno; dbms_output.put_line('涨前工资: '||psal);
update emp set sal=sal+100 where empno = eno; commit; select * into pemp from emp where empno = eno; dbms_output.put_line('涨后工资: '||pemp.sal); end; --存储过程调用 emp_add_sal(7369); --调用方式2 <不推荐> call emp_add_sal(7369); --根据员工编号查询年薪 --有返回值 create or replace procedure emp_totalsal(eno in number,totalsal out number) select sal*12+nvl(comm,0) into totalsal from emp where empno = eno; --调用 declare sal number; emp_totalsal(7369,sal); dbms_output.put_line(sal); --采用第二种（不适用） call pro_emp_totalsal(7369,); --call 调用只适用于没有输出参数的存储过程


====================================== 存储函数 ======== == ===================
/* 存储过程与存储函数的区别： 存储过程能实现功能，存储函数也可以实现 语法不同：存储函数一定是有返回值。并且在函数的程序体内必须返回一个变量；变量类型跟声明返回数据类型保持一致 存储函数可以用在select语句中； */

语法： create or replace function 函数名称 (参数 in|out 参数类型) return 数据类型 as|is --定义一些变量，游标 begin return 变量； --变量类型要跟返回的数据类型保持一致 end;
--使用存储函数，查询指定员工的年薪

createorreplacefunctionfun_totalsal(enoinnumber)returnnumber as totalsalnumber; begin selectsal*12+nvl(comm,0)intototalsalfromempno=eno; returntotalsal; --调用存储函数

number;--定义变量来接收返回值 totalsal:=fun_totalsal(7369); dbms_output.put_line(totalsal); --存储函数带一个输出参数(既有返回值,又要有输出)

fun_totalsal_out(enonumber,pnameoutemp.ename%typenumber selectsal*12+nvl(comm,255)">0)intototalsalwhereempno=eno; --调用

pnamevarchar2(100); totalsal:=fun_totalsal_out(7369,pname); dbms_output.put_line(pname); --查询指定员工的信息，部门名称

fun_emp_dname(dnonumbervarchar2 as

deptname varchar2 ( 200 ); --注意声明varchar2类型长度一定要给指定

selectdnameintodeptnamefromdeptwheredeptno=dno; returndeptname; ; ename,fun_emp_dname(deptno)from10;

--查询指定部门员工信息（使用存储过程）--sys_refcursor引用游标类型

procedurepro_emplist(dnoinoutsys_refcursor) openemplistforempssys_refcursor; pempemp%rowtype; pro_emplist(20,emps); loop fetchempsintopemp; whenemps%notfound; dbms_output.put_line(pemp.ename); loop; closeemps; ;

===================================== jdbc调用oracle对象（掌握） ======= == =========================
导入jar包
PreparedStatement :
作用:1. 可以提前占位, 防SQL注入 2.提高了效率<查询时候先去数据库找缓存>

   
   

    
    

    
    

    
    

    
    

    
    

    
    

    
    

    
    

    
    

    
    

    
    

   
   
public class OracleTest { String driver = "oracle.jdbc.driver.OracleDriver"; url "jdbc:oracle:thin:@192.168.68.10:1521:orcl" username "scott" password "admin"  @Test //查询所有的void testFindEmpList(){try//加载驱动 Class.forName(driver);//获取连接 Connection conn  DriverManagergetConnectionurl, username password//获取 PreparedStatement 对象 PreparedStatement pst  connprepareStatement("select * from emp"//执行查询 ResultSet rs  pstexecuteQuery();//处理结果whilersnext()){//getObject(i)获取第几列的值，getString("列名")，getInt("列名")  SystemoutprintlngetObject(1)+"--"+getString"ename"));}//释放资源 rsclosecatch(Exception e)printStackTrace}}

调用过程 无输出参数的 Connection 方法
返回值

    
    

     
     

     
     

     
     
  create or replace procedure emp_add_sal(eno in number) 
     
     

     
     

     
     

     
     

     
     

     
     

     
     

     
     

     
     
{call <procedure-name>[(<arg1>,<arg2>,...)]} */
     
     

    
    
/* * 调用存储过程   --存储过程  as psal emp.sal%type;  pemp emp%rowtype;  begin select sal into psal from emp where empno = eno;  dbms_output.put_line('涨前工资: '||psal); update emp set sal=sal+100 where empno = eno;  commit; select * into pemp from emp where empno = eno;  dbms_output.put_line('涨后工资: '||pemp.sal); end;   testCallProcedure);  CallableStatement cst = conn.prepareCall( "{call emp_add_sal(?)}");  cstsetInt7369// TODO Auto-generated catch block}

调用过程（有输出参数）
CallableStatement:方法

   
     调用存储过程  --根据员工编号查询年薪 create or replace procedure emp_totalsal(eno in number,totalsal out number) as begin select sal*12+nvl(comm,0) into totalsal from emp where empno = eno; end; */// {call <procedure-name>[(<arg1>,...)]} testCallProcedureOutParam CallableStatement cst prepareCall"{call emp_totalsal(?,?)}"  cst.registerOutParameter(2,OracleTypesNUMBER); //获取第二个输出参数的值cst2}

调用函数

    
     --调用函数 --使用存储函数，查询指定员工的年薪 create or replace function fun_totalsal(eno in number ) return number totalsal number; return totalsal; end;    {?= call <procedure-name>[(<arg1>,...)]} */ testCallFunction"{?= call fun_totalsal(?)}"registerOutParameterOracleTypesNUMBER//获取第一个参数输出的值getInt 

调用存储过程输出参数为游标类型的:

 {call <procedure-name>[(<arg1>,...)]}
     
     
 --调用存储过程输出参数为游标类型的 --查询指定部门员工信息（使用存储过程） --sys_refcursor 引用游标类型 create or replace procedure pro_emplist(dno in number,emplist out sys_refcursor) open emplist for select * from emp where deptno = dno; testCallProcedureOutCursor"{call pro_emplist(?,145)">20CURSORexecute  ResultSet rs  ((OracleCallableStatement)cst).getCursor(2);getObject}

-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
上面代码 可以简化: 用注解

  
  

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

  
  
private; @Before init()throws ClassNotFoundException SQLException// 加载驱动// 获取连接@After after// 释放资源if(null!=){if}

-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------

================================================触发器======== =============================== /* 理解：好比监视器，触发器在对表中的数据进行操作（增删改），触发器就会工作。如果操作合法了，才能对数据进行改变 语法：create[orreplace]trigger after|before insert|update|delete on表名 [foreachrow]---行级触发器 declare begin end; */ --添加一条数据，自动控制台输出一句‘数据添加成功了

triggertri_after_add after insert onperson1 declare begin '数据添加成功了'); ; insertintoperson1values(15,255)">'赵六',255)">1); commit --不能在休息（周四周六日）时间添加员工 raise_application_error(p1,p2);--错误提示框 p1：错误编码范围在-20001到-20999 p2:错误信息 */

to_char(sysdate'day')fromdual; tri_before_add before insert onperson1 weekend50); begin selectto_char(sysdate,255)">'day')intoweekendfromdual; ifweekendin('thursday',255)">'saturday',255)">'sunday')then raise_application_error(-20002,255)">'不能在休息时间添加员工'); if; ;
211221,sans-serif; font-size:15px; line-height:24px"> --不能给员工降薪 foreach row:行级触发器表示针对每一条数据操作都会触发， 只有用到:old:new必须声明行级触发器 tri_before_update update emp foreachrow if:old.sal>:new.sal20003,255)">'不能降薪!'); updateempsetsal=sal-100whereempno=7369; --=======触发器的应用 --目标:主键不在显示输入,自动生成

person1(namevalues'李四1',255)">2); --创建触发器

selectseq_person.nextvalinto:new.id;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!