PL/SQL代码参考示例

PL/SQL代码参考示例

以下的PL/SQL代码为本人在学习PL/SQL时的练习代码，仅供参考。

-----------显示单条id的用户名和相应的年龄，如果不存在则显示‘NO RECORD’------------

set serveroutput on
declare
v_id account.id%TYPE := 1010;--为id赋初值
v_count number(3);--统计记录条数
type t_account_rec is record(--定义记录类型
real_name account.real_name%TYPE,
age number(3));
v_account t_account_rec;
begin

----因为select into必须返回的是一条记录（单行单列或者单行多列），所以要先进行判断-------

select count(*) into v_count from account where id = v_id;
if v_countcreate table account_91111 as select 
* from account where 1=2;
create table account_91111 as select * from account where 1=2;
create table account_91111 as select * from account where 1=2;
 <> 0 then-----------如果Id存在
select real_name,round((sysdate-birthdate)/365) into v_account 
from account where id = v_id;
dbms_output.put_line(v_account.real_name||' '||v_account.age);
else-----如果id不存在
dbms_output.put_line('NO RECORD!');
end if;
end;

-----sql%rowacount获得DML操作所影响的行数-----

create table zhuaa (c1 number(1));

begin
--insert into zhuaa values(1);
update zhuaa set c1 = 2 where c1 = 1;
dbms_output.put_line(sql%rowcount||'条记录已更改！');
end;

---------------显示多条符合条件的记录，如果无纪记录则显示‘no account’---------------

---------------------------while循环--------------------------------------------

declare
cursor c_account is select real_name,birthdate from account where 1=1;
v_account c_account % rowtype;
begin
open c_account;
fetch c_account into v_account;
if c_account%notfound then
dbms_output.put_line('no account');
else
while c_account%found loop
dbms_output.put_line(v_account.real_name||' '||v_account.birthdate);
fetch c_account into v_account;
end loop;
end if;
close c_account;
end;

-------------------------for循环---------------------------------------------

/*for循环集成了open,fetch,close,i定义（i c_account%rowtype）*/
--v_emp tablename % rowtype;
--v_emp cursorname % rowtype;
--<==>
--type t_name is record(col1 tablename/cursorname.col1.%type,col2 tablename/cursorname.col2%type.....;)
declare
cursor c_account is select real_name,birthdate from account where 1=1;
v_account c_account % rowtype;
begin
for i in c_account loop--i类型为record，相当于fetch c_account into i
dbms_output.put_line(i.real_name||' '||i.birthdate);
end loop;
end;

--------------collection集合----------------------------------------------------

declare
  type t_indtab is table of number index by binary_integer;--定义数据类型
  v_indtab t_indtab;--定义变量
begin
  v_indtab(1) := 1;
  v_indtab(10) := 10;
  v_indtab(3) := 3;
  dbms_output.put_line(v_indtab(1)||' '||v_indtab(10)||' '||v_indtab(3));
end;
 --acount表中id和real_name存入集合，id作为索引，real_name作为对应索引处的值---------  
declare 
  cursor c_account is select id,real_name from account;
  type t_indtab is table of varchar2(20) index by binary_integer;
  v_indtab t_indtab;
begin 
  for i in c_account loop
  v_indtab(i.id):=i.real_name;
   dbms_output.put_line(i.id||':'||v_indtab(i.id));
end loop;
end;

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

declare 
  cursor c_account is select id,real_name from account;
  type t_indtab is table of varchar2(20) index by binary_integer;
  v_indtab t_indtab;
  v_index binary_integer;
begin 
   for i in c_account loop
   v_indtab(i.id):=i.real_name;
   dbms_output.put_line(i.id||':'||v_indtab(i.id));
   end loop;
   dbms_output.put_line(v_indtab.first);
   for i in v_indtab.first..v_indtab.last loop
   if v_indtab.exists(i) then
      dbms_output.put_line(v_indtab(i));
    end if;
  end loop;
   v_index := v_indtab.first;
   while v_index <= v_indtab.last
   loop
    dbms_output.put_line(v_indtab(v_index));
    v_index := v_indtab.next(v_index);
  end loop;
end;

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

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

select real_name bulk collect into v_indtab from account;
  
declare 
  cursor c_account is select id,real_name,birthdate from account;
  type t_account is table of varchar2(20) index by binary_integer;  
  v_account t_account;
  v_index binary_integer;
begin
  for i in c_account loop
  v_account(i.id) := i.real_name;
  end loop;
  v_index := v_indtab.first;
  while v_index <= v_indtab.last loop
  dbms_output.put_line(v_indtab(v_index));
  v_index := v_indtab.next(v_index);

----------列出客户姓名、年龄及累计年龄-----------------------

declare
  type t_account_rec is record(
  real_name varchar(20),
  age number(3));
  type t_account_arr is table of t_account_rec index by binary_integer;
  v_account t_account_arr;
  sum_age number(5) := 0;
begin
  select real_name,round((sysdate-birthdate)/365) bulk collect into v_account from account;
  for i in v_account.first.. v_account.last 
  loop
    sum_age := sum_age + v_account(i).age;
    dbms_output.put_line(v_account(i).real_name||' '||v_account(i).age||' '||sum_age);
  end loop;
end;    

**************************************Exception*******************************
---------------------------------no_data_found Exception---------------------------------

declare
  v_realname account.real_name%type;
begin
  select real_name into v_realname from account where 1 = 0;
  dbms_output.put_line(v_realname);
exception
  when no_data_found then
    dbms_output.put_line('No ACCOUNT');
end;

------------------------------too_many_rows Exception-----------------------------------

declare
  e_noparentkey exception;
  pragma exception_init(e_npkey,-2291);
begin
  insert into child values(2,2);
  commit;
exception
  when e_npkey then
  dbms_output.put_line('NO PARENT');
end;  

-------------------------用户自定义异常-----------------------------------

declare
  type t_account_arr is table of varchar2(20) index by binary_integer;
  v_account t_account_arr;
  e_isnull exception;
begin
  select real_name bulk collect into v_account from account where 1 = 0;
  if v_account.first is null then
  raise e_isnull;
  end if;
 exception
  when e_isnull then
  dbms_output.put_line('IS　NULL');
end;

-----------------procedure--------------------------------------------

create or replace procedure prozhu
is
begin
    dbms_output.put_line('Hello World');
end;


begin
  prozhu;
end;


exec prozhu;
execute prozhu;


show error;


exec dbms_output.put_line('Hello World');


desc user_source;
select * from user_source;
select * from user_source where name = 'prozhu';--查看存储过程

----------------------procedure---------------------------------------------

create or replace procedure prozhu9--过程通过Out参数返回值
(p_c1 varchar2,p_c2 out varchar2,p_c3 in out varchar2)--形参
is
  v_c1 varchar2(10);
begin
  v_c1 := p_c1;
--p_c1 := 'a';错误，因为p_c1是只读的
  p_c2 := p_c2 || 'a';
  p_c3 := p_c3 || 'a';
end;


declare
  v_c1 varchar2(10) := 'b';
  v_c2 varchar2(10) := 'b';
  v_c3 varchar2(10) := 'b';
begin
  prozhu9(v_c1,v_c2,v_c3);--实参
  dbms_output.put_line('v_c1:'||v_c1);--b
  dbms_output.put_line('v_c2:'||v_c2);--a
  dbms_output.put_line('v_c3:'||v_c3);--ba
end;

------------------------function------------------------------------------------

create or replace function funzhu999(p_c1 number)
return number
is 
begin
  return p_c1;
end;


declare
  v_n1 number;
begin
  v_n1 := funzhu999(2);
  dbms_output.put_line(v_n1);
end;


select funzhu999(3) from dual;

--------------------------function-------------------------------------------

create or replace function funzhu007(p_id number)
return varchar2
is
 v_realname varchar2(20);
begin
  select real_name into v_realname from account where id = p_id;
  return v_realname;
end;


declare
  v_realname varchar2(20);
begin
  v_realname := funzhu007(1005);
  dbms_output.put_line(v_realname);
end;
select funzhu007(1005) from dual;

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

create or replace procedure funzhu1234(p_id number)
is
type t_account_rec is record(
  realname varchar2(20),age number);
  c_account t_account_rec;
begin
  select real_name,round((sysdate-birthdate)/365) into c_account from account
  where id = p_id;
  dbms_output.put_line(c_account.realname||' '||c_account.age);
end;

declare
  v_realname varchar2(20);
begin
  v_realname := funzhu007(1005);
  dbms_output.put_line(v_realname);
end;
select funzhu1234(1005) from dual;


show error;