oracle学习笔记4
//函数：根据员工名字查询年薪
create function sp_fun1(sp_name varchar2) return number is yearSal number(7,2);
begin
--执行部分
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
//调用函数，先定义变量
var abc number;
call sp_fun1(‘SCOTT‘) into: abc;
//创建包
create package sp_package is
procedure update_sal(name varchar2, newSal number);
function annual_income(name varchar2) return number;
end;
//创建包体
create or replace package body sp_package is
procedure update_sal(name varchar2,newSal number)
is
begin
update emp set sal=newSal where ename=name;
end;
function annual_income(name varchar2)
return number is
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
end;
//调用包中的过程或函数
exec sp_package.update_sal(‘SCOTT‘,120);
call
标量类型 scalar
var_sal number(6,2):=5.4 --初始值
复合类型 composite
存放多个值的记录：pl/sql记录，
reference
lob(large object)
//计算员工的个人所得税，标量
declare
c_tax_rate number(3,2):=0.03; --税率为0.03
v_ename emp.ename%type; --%type用来定义类型
v_sal number(7,2);
v_tax_sal number(7,2);
begin
select ename,sal into v_ename, v_sal from emp where empno=&no; --&代表客户输入
--计算所得税
v_tax_sal:=v_sal*c_tax_rate;
--输出
dbms_output.put_line(‘姓名:‘||v_name||‘ 所得税：‘||v_tax_sal);
end;
//pl/sql记录类型 (复合类型)
declare
type emp_record_type is record{
name emp.ename%type,
salary emp.sal%type,
title emp.job%type);
sp_record emp_record_type; --定义了一个类型为emp_record_type的变量sp_record
}
begin
select ename,sal,job into sp_record from emp where empno=7788;
dbms_output.put_line(‘名字：‘||sp_record.name||‘工资：‘ ||sp_record.sal);
end;
// pl/sql表实例
declare
--定义了一个pl/sql表类型sp_table_type，用于存放emp.ename%type
--by binary_integer表示下标为整数
type sp_table is table of emp.ename%type index by binary_integer;
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7788;
end;
//参照变量
游标变量，参照类型变量
--输入部门号，显示所有员工的姓名和工资
declare
--定义游标变量类型
type sp_emp_cursor is ref cursor;
--定义游标变量
test_cursor sp_emp_cursor;
--定义程序变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行
--把cursor和select结合
open test_cursor for select ename, sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断是否是游标结束
exit when test_cursor%notfound;
dbms_output.put_line(‘‘||v_ename||‘‘||v_sal);
end loop;
end;
----------------------------------------------------------------------------------------
//创建存储过程
create or replace precedure proc_name is
begin
--执行部分
insert into mytable values(‘xx‘,‘yy‘);
end;
/ --(斜杠代表开始创建过程)
show error; //查看编译错误
执行存储过程：
exec proc_name para1,para2..;
call proc_name para1,para2...;
可以编写存储过程、函数、触发器和
游标后缀 _cursor
例外 e_
变量 v_
常量 c_
块：
定义部分(可选)： declare
执行部分(必需)： begin
例外部分(可选)： exception
set serveroutput on --打开输入选项
begin
dbms_output.put_line(‘hello world‘); --dbms_output是oracle的包
end
declare
v_ename vchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename, v_sal from empno where empno=$no; --&代表从控制台输入
dbms_output.put_line("用户名是："||v_ename||" 工资："||e_sal); -- ||代表连接符号
--例外处理
exception
when no_data_found then --找不到数据
dbms_output.put_line("input data not exit,please input again!");
end
create procedure my_proc_name (my_name varchar(20), my_sal number) is --定义带参数的存储过程
begin
update emp set sal=my_sal where ename=my_name;
end;
/
call my_proc_name(‘SCOTT‘,4788); --更新scott的工资
//java中调用存储过程
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//获取连接
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521@myOra1","SCOTT","TIGER");
//创建collablestatement
CallableStatement ct=ct.prepareCall("{call my_proc_name (?,?)}");
ct.setString(1,"SMITH");
ct.setString(2,10);
cs.execute();
cs.close();
ct.close();
开发工具
sqlplus
pl/sql developer
优点：
性能
模块化
减少网络传输量
安全性，不会暴露内部数据结构，oracle本身的安全性
pl/sql缺点
移植性不好