Oracle PL/SQL 入门

===================================== ====== 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 ========== ==========================================

语法：

if条件then

语句；

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}}

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}

调用存储过程 --根据员工编号查询年薪 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}

猜你在找的Oracle相关文章