Oracle学习之示例代码
/* 循环开始 */
declare i number;
begin
for i in 12..21 loop
INSERT INTO liutest (id,name,password,age,address,phone,flag)
VALUES (i,'liu','liuaaa',20,'testaddress','12121212122','1');
end loop;
end;
/* 循环结束 */
/* 游标开始 */
set serveroutput on
declare Lid liutest.id%type;
Lname liutest.name%type;
Lpassword liutest.password%type;
Lage liutest.age%type;
Laddress liutest.address%type;
cursor QueryLiuTest is select id,name,password,age,address from liutest;
begin
open QueryLiuTest;
loop
fetch QueryLiuTest into Lid,Lname,Lpassword,Lage,Laddress;
exit when QueryLiuTest%notfound;
dbms_output.put_line(Lid || ',' || Lname || ',' || Lpassword ||',' || Lage ||',' || Laddress);
end loop;
close QueryLiuTest;
end;
/* 游标结束 */
/* 使用For循环的游标开始 */
set serveroutput on
declare Lid liutest.id%type;
Lname liutest.name%type;
Lpassword liutest.password%type;
Lage liutest.age%type;
Laddress liutest.address%type;
cursor QueryLiuTest is select id,name,password,age,address from liutest;
begin
/* testValue For 循环中的变量 testValue.id */
for testValue in QueryLiuTest loop
/* testValue.id 变量。数据表中列名 获取数据值*/
dbms_output.put_line(testValue.id || ',' || testValue.name || ',' || testValue.password ||',' || testValue.age ||',' || testValue.address);
end loop;
end;
/* 使用For循环的游标结束 */
/* 创建更新存储过程 */
create procedure update_test
(Iage in liutest.age%type,
Iname in liutest.name%type)
is
Ocount integer;
begin
select count(*) into ocount from liutest where id=50;
if Ocount=1 then
update liutest set password='testleast',address='testaddress',phone='000' where id=50;
commit;
end if;
exception when others then rollback;
end update_test;
/* 更新存储过程结束 */
/* 执行存储过程 */
call update_test(50,'liu');
/* 创建函数开始 计算圆面积*/
create function circle_area(
r in number
)return number as
Pi number :=3.1415926;
area number;
begin
area :=pi*power(r,2);
return area;
end circle_area;
/
/* 创建函数结束 */
/* 调用函数 */
select circle_area(2) from liutest;
/* 创建包开始 */
/* 创建包规范开始 */
create package test_package as
type t_ref_cursor is ref cursor;
function get_ref_cursor return t_ref_cursor;
procedure update_test (Iage in liutest.age%type,Iname in liutest.name%type);
end test_package;
/
/* 创建包规范结束 */
/* 创建包体开始 */
create or replace package body test_package as
function get_ref_cursor return t_ref_cursor is p_ref_cursor t_ref_cursor;
begin
open p_ref_cursor for select id,name,password,age,address,phone from liutest;
return p_ref_cursor;
end get_ref_cursor;
procedure update_test
(Iage in liutest.age%type,
Iname in liutest.name%type)
is
Ocount integer;
begin
select count(*) into ocount from liutest where id=50;
if Ocount=1 then
update liutest set password='testleast',address='testaddress',phone='000',age=iage where id=50;
commit;
end if;
exception when others then rollback;
end update_test;
end test_package;
/* 创建包体结束 */
/* 创建包结束 */
/* 调用包中的过程和函数 包名。函数 包名。过程 */
select test_package.get_ref_cursor from liutest;
