练习中的语句 备份

学习Oracle中练习的sql记录，方便日后用：

--创建一个块
declare
v_name varchar2(20);
v_pwd  varchar2(20);
begin
select name into v_name,password into v_pwd from mytest where id=&id;
end;

--创建一个输出块
declare
v_name varchar2(30);
begin
select name into v_name from mytest where id=&id;
dbms_output.put_line('用户的名字是：'||v_name);
end;

--创建一个多字段输出块
declare
v_name varchar2(30);
v_pass varchar2(30);
begin
select name,password into v_name,v_pass from mytest where id=&id;
dbms_output.put_line('姓名：'||v_name||' '||v_pass);
end;

--创建一个例外
declare
v_name varchar2(30);
v_pass varchar2(30);
begin
select name,password into v_name,v_pass from mytest where id=&id;
dbms_output.put_line('姓名：'||v_name||' '||v_pass);
exception
when no_data_found then
dbms_output.put_line('not found values');
end;

--创建一个传入过程
create or replace procedure sp_prol2(spname varchar2,sid number) is
begin
update mytest set name = spname where id=sid;
end;

--创建一个带有返回值的参数
create  function  fun_count1(fname varchar2)
return number is
fcount number(5);
begin
select id*12 into fcount from mytest where name=fname;
return fcount;
end;

--创建一个包
create package sp_package is
procedure sp_update(sname varchar2,sid number);
function f_select(sname varchar2) return number;
end;

--创建包体
create or replace package body sp_package is
procedure sp_update(sname varchar2,sid number)
is
begin
 update mytest set name=sname where id = sid;
end;
function f_select(sname varchar2) return number 
is
fcount number;
begin
 select id*12 into fcount from mytest where name=sname;
 return fcount;
end;
end;

--标量案例
v_ename varchar2(10);
v_sal number(6,2);
v_sal number(6,2):=5.3;
v_date date;
v_bool boolean not null default false;

--案例
declare
 c_tax_rate number(3,2):=0.03;
 v_ename varchar2(10);
 v_sal number(7,2);
 v_tax_sal number(7,2);
begin
 select name,sal into v_ename,v_sal from mytest where id=&id;
 v_tax_sal:=v_sal*c_tax_rate;
 dbms_output.put_line(v_tax_sal);
end;

declare
c_tax_rate number(3,2) := 0.03;
v_name mytest.name%type;
v_sal mytest.sal%type;
v_tax_rate number(7,2);
begin
select sal into v_sal from mytest where id=&id;
v_tax_rate := c_tax_rate * v_sal;
dbms_output.put_line(v_tax_rate);
end;

--复合类型

--案例
declare
type emp_record_type is record(name mytest.name%type,sal mytest.sal%type,id mytest.id%type);
sp_record emp_record_type;
begin
 select name,sal,id into sp_record from mytest where id = 1;
 dbms_output.put_line(sp_record.name);
end;

--(问题????)
declare
 mytest.name%type 
 type sp_table_type is 
 table of mytest.name%type index by binary_integer;
 sp_table sp_table_type;
begin
 select name into sp_table(0) from mytest where id = 1;
 dbms_output.put_line(sptable(0));
end;


--游标  案例
--1
declare 
v_name mytest.name%type;
v_sal mytest.sal%type;
begin
 select name,sal into v_name,v_sal from mytest where id = &no;
 dbms_output.put_line(v_name||v_sal);
end;
--2
declare
 type sp_mytest_cursor is ref cursor;
 test_cursor sp_mytest_cursor;
 v_name mytest.name%type;
 v_sal mytest.sal%type;
 begin
  open test_cursor for select name,sal from mytest;
  loop
   fetch test_cursor into v_name,v_sal;
   exit when test_cursor%notfound;
   dbms_output.put_line(v_name||v_sal);
   end loop;
 end;
 
 --创建过程 if
 create or replace procedure update_test_sal(v_name varchar2) is
 v_sal mytest.sal%type;
 begin
  select sal into v_sal from mytest where name = v_name;
  if v_sal < 100 then
   update mytest set sal=sal + 10 where name = v_name;
  else 
   update mytest set sal=sal - 10 where name = v_name;
  end if;
 end;
 
 
create or replace procedure update_test_sal(v_name varchar2) is
 v_sal mytest.sal%type;
begin
 select sal into v_sal from mytest where name = v_name;
 if v_sal = 100 then
  update mytest set sal=sal + 10 where name = v_name;
 elsif v_sal = 90 then
  update mytest set sal=sal + 10 where name =v_name;
 else
  update mytest set sal=sal-10 where name = v_name;
 end if;
end;


--- loop
create or replace procedure add_test1_info(v_name varchar2) is
v_id number := 1;
begin
 loop
   insert into test1 values(v_id,v_name);
   exit when v_id = 10;
   v_id := v_id + 1;
 end loop;
end;


create or replace procedure add_test1_info(v_name varchar2) is
v_id number := 11;
begin
 while v_id <= 20 loop
  insert into test1 values(v_id,v_name);
  v_id := v_id + 1;
 end loop;
end;


create or replace procedure add_test1_info(v_name varchar2) is
c_end number := 40;
v_id number := 31;
begin
 for  i in reverse v_id .. c_end loop
  insert into test1 values(i,v_name);
 end loop;
end;


create or replace procedure add_book(v_id number,v_name varchar2,v_bilu varchar2) is
begin
 insert into book values(v_id,v_name,v_bilu);
end;


create or replace procedure out_book(v_id in number,v_name out varchar2) is
begin
 select bookname into v_name from book where bookid = v_id;
end;

--返回结果集的过程
--1.创建一个包，在包中创建一个游标
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;

create or replace procedure rs_book_info(v_id in number,test_cursor out testpackage.test_cursor) is
begin
 open test_cursor for select * from book where bookid = v_id ;
end rs_book_info;


--创建分页
select * from (select rownum rn,t1.* from (select * from book) t1 where rownum < 10) where rn > 6

create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;

create or replace procedure page_book(
 tableName in varchar2, 
 PageSizes in number, 
 pageNow in number, 
 myrows out number, 
 mypagecount out number, 
 p_cursor out testpackage.test_cursor) is
v_sql varchar2(1000);
v_begin number:= (pageNow-1) * Pagesizes + 1;
v_end number:= pageNow * Pagesizes;
begin
 v_sql:='select * from (select rownum rn,t1.* from (select * from '||tableName||') t1 where rownum <= '||v_end||') where rn >= '||v_begin;
 open p_cursor for v_sql;
 v_sql:='select count(*) from '||tableName;
 execute immediate v_sql into myrows;
 if mod(myrows,Pagesizes) = 0 then
  mypagecount :=myrows/pagesizes;
 else
   mypagecount :=myrows/pagesizes + 1;
 end if;
end;