引用型变量
declare
v_name table.ename%TYPE;
v_sal table.sal%TYPE;
begin
select ename,sal into v_name,v_sal from table where id=1;
dbms_output.put_line('姓名:'||v_name||',薪资:'||v_sal);
end;
记录型变量
declare
v_tablerow table%ROWTYPE;
begin
select * into v_name,v_sal from table where id=1;
dbms_output.put_line('姓名:'||v_tablerow.ename||',薪资:'||v_tablerow.sal);
end;
条件分支
begin
if then
elsif then
else
end if;
end
举例:判断表中记录数量
declare
v_count NUMBER;
bengin
select count(1) into v_count from table;
if v_count>20 then dbms_output.put_line('记录数大于20为:'||v_count);
elsif v_count>=10 then dbms_output.put_line('记录数大于10为:'||v_count);
else dbms_output.put_line('记录数小于10为:'||v_count);
end if;
end
循环
begin
loop
exit when 退出条件
end loop;
end
举例:打印1到10
declare
v_num NUMBER :=1;
bengin
loop
exit when v_num>10;
dbms_output.put_line(v_num);
v_num := v_num+1;
end loop;
end
游标(cursor):临时存储查询结果集 声明->打开->读取->关闭, 属性包括 %rowcount,%found,%notfound,%isopen
cursor cursorName[(param1, param2……)] is 查询语句;
open cursorName;
fetch cursorName into 变量列表
close cursorName;
举例:查询table中id小于10的所有记录并打印
declare
cursor c_table(v_maxId emp.id%type) is select * from table where id<v_maxId;
v_tablerow table%ROWTYPE;
bengin
open c_table(10);
loop
fetch c_table into v_tablerow
exit when c_table%notfound;
dbms_output.put_line('姓名:'||v_tablerow.ename||',薪资:'||v_tablerow.sal);
end loop;
close c_table;
end
存储过程procedure
create or replace procedure pName[(param1, param2……)] is/as
begin
end
举例1(无参)
create or replace procedure ptest is
begin
dbms_output.put_line('这是一个叫做ptest的存储过程 ');
end
调用:begin ptest; end;
举例2 打印某一条记录(带输入参数)
create or replace procedure ptableRow(v_id IN table.id%type) is
v_name table.ename%TYPE;
v_sal table.sal%TYPE;
begin
select name,sal into v_name, v_sal from table where id=vid;
dbms_output.put_line('姓名:'||v_name||',薪资:'||v_sal);
end
调用1: bengin ptableRow(10); end;
调用2: declare v_id NUMBER :=10 begin ptableRow(v_id);end;
举例3(带输入输出参数)
create or replace procedure ptableData(v_id IN table.id%type, v_sal OUT table.sal%type) AS
bengin
select sal into v_sal from table where id = v_id;
end
调用 ptableData
declare
v_sal table.sal%type;
bengin
ptableData(10, v_sal);
dbms_output.put_line(v_sal);
end
举例4 在java中调用oracle的存储过程ptableData
public static void main(String[] args) throw Exception{
//连接参数
String url="jdbc:oracle.thin:@localhost:1521:xe";
String username="root";
String password="123456";
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//获取连接对象
Connection conn=DriverManager.getConnection(url,username,password);
//获取语句对象
String sql="{call ptableData(??)}";
CallStatement call=conn.prepareCall(sql);
//设置输入参数
call.setInt(1,1254);
//注册输出参数
call.registerOutParameter(2, OracleType.DOUBLE);
//执行存储过程 获取输出参数
call.execute();
double result = call.getDouble(2);
//输出结果
System.out.println(result);
//释放资源
call.close();
conn.close();
}