pl sql 备忘（小例子）

set serveroutput on;

declare v_id number;
begin
select SALARY  into v_id from test where EMPLOYEE_ID=&id;
dbms_output.put_line(v_id);
end;
/

处理异常
declare v_id number;
begin
select SALARY  into v_id from test where EMPLOYEE_ID=&id;
dbms_output.put_line(v_id);
exception
when no_data_found then
dbms_output.put_line('does not exist');
end;
/

create procedure sp_test21(id number,sal number) is
begin 
update test set SALARY=sal where EMPLOYEE_ID=id;
end;
/


函数
create function annual_incomec(name varchar2)    
return number is  annual_salazy number(7,2);    
begin   
    --执行部分    
    select sal*12+nvl(comm, 0) into annual_salazy from emp where ename=name;    
    return annual_salazy;    
end;    
/   
  SQL> var income number    
  SQL> call annual_incomec('scott') into: income;    
  SQL> print income 



包用于在逻辑上组合过程和函数
包规范
create package sp_package is   
  procedure update_sal(name varchar2, newsal number);    
  function annual_income(name varchar2) return number;   
end;   
建立包体可以使用 create package body 命令 
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   
    annual_salary number;    
  begin   
     select sal * 12 + nvl(comm, 0) into annual_salary from emp where ename = name;    
     return annual_salary;    
   end;    
 end;    
 /   






标量(scalar)
 declare   
  c_tax_rate number(3,2):=0.03;    
  --用户名    
  v_ename emp.ename%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=&n;    
 --计算所得税    
      v_tax_sal := v_sal*c_tax_rate;    
 --输出    
     dbms_output.put_line('姓名是：'||v_ename||'工资'||v_sal||' 交税：'||v_tax_sal);    
end;    
 /   


复合变量(composite)--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;    
begin   
   select ename, sal, job into sp_record from emp where empno =788;    
   dbms_output.put_line ('员工名:' || sp_record.name);    
end;   
  


复合类型(composite)--pl/sql 表 
declare   
--定义了一个 pl/sql 表类型 sp_table_type，该类型是用于存放emp.ename%type    
--index by binary_integer 表示下标是整数    
  type sp_table_type is table of emp.ename%type     
  index by binary_integer;    
--定义了一个 sp_table 变量，这个变量的类型是 sp_table_type    
  sp_table sp_table_type;    
begin   
  select ename into sp_table(-1) from emp where empno = 7788;  
  
  dbms_output.put_line('员工名:' || sp_table(-1));    
end;   




参照变量——ref cursor 游标变量
declare   
--定义游标 sp_emp_cursor     
    type sp_emp_cursor is ref cursor;    
--定义一个游标变量    
    test_cursor sp_emp_cursor;       
--定义变量    
v_ename emp.ename%type;     
v_sal emp.sal%type;    
begin   
--执行    
--把 test_cursor 和一个 select 结合    
open test_cursor for select ename,sal from emp where deptno=&no;    
--循环取出    
loop    
    fetch test_cursor into v_ename,v_sal;    
    --判断是否 test_cursor 为空    
    exit when test_cursor%notfound;    
    dbms_output.put_line('名字：'||v_ename||' 工资：'||v_sal);    
end loop;  
end;    


简单if--then ---end if;
create procedure sp_pro0(id number) is 
sal test.salary%type;
begin
  select SALARY into sal from test where EMPLOYEE_ID=id;
  if sal<500 then 
    update test set SALARY=sal*1.5 where EMPLOYEE_ID=id;
  end if;
end;
/


if--then-elsif--then--else--end if
create or replace procedure sp_pro6(spNo number) is   
    --定义    
    v_job emp.job%type;    
begin   
    --执行    
    select job into v_job from emp where empno=spNo;    
    if v_job='PRESIDENT' then   
        update emp set sal=sal+1000 where empno=spNo;    
    elsif v_job='MANAGER' then   
         update emp set sal=sal+500 where empno=spNo;    
    else   
         update emp set sal=sal+200 where empno=spNo;    
    end if;    
end;    
/


循环语句--loop循环
create or replace procedure sp_pro6(spName varchar2) is   
--定义  :=表示赋值    
    v_num number:=1;        
begin   
    loop    
        insert into users values(v_num,spName);    
        --判断是否要退出循环    
        exit when v_num=10;    
        --自增    
        v_num:=v_num+1;    
    end loop;    
end;    
/   


循环语句--while 循环
create procedure sp_pro122(sname varchar2 ) is 
row_count number:=1;
begin
  while row_count<10 loop
    insert into users values (row_count,sname);
    row_count:=row_count+1; 
  end loop;
end;
/

返回值是多条语句的存储过程
create package mytype as 
type test_cursor is ref cursor;
end mytype;
/

create procedure sp_abc(depid number,myresult out mytype.test_cursor) is
begin
open myresult for select * from test where DEPARTMENT_ID=depid;
end;
/


分页的存储过程
create or replace package testpackage as   
  TYPE test_cursor is ref cursor;    
end testpackage;
  
--开始编写分页的过程    
create or replace procedure fenye    
    (tableName in varchar2,    
     Pagesize in number,--一页显示记录数    
     pageNow in number,    
     myrows out number,--总记录数    
      myPageCount out number,--总页数    
      p_cursor out testpackage.test_cursor--返回的记录集    
     ) is   
 --定义部分    
 --定义 sql 语句 字符串    
 v_sql varchar2(1000);    
 --定义两个整数    
 v_begin number:=(pageNow-1)*Pagesize+1;    
 v_end number:=pageNow*Pagesize;    
 begin   
--执行部分    
v_sql:='select * from (select t1.*, rownum rn from (select * fr
om '||tableName||') t1 where rownum<='||v_end||') where rn>='||
v_begin;    
--把游标和 sql 关联    
open p_cursor for v_sql;    
--计算 myrows 和 myPageCount    
--组织一个 sql 语句    
v_sql:='select count(*) from '||tableName;    
--执行 sql,并把返回的值，赋给 myrows；    
execute inmediate v_sql into myrows;    
--计算 myPageCount    
--if myrows%Pagesize=0 then 这样写是错的    
if mod(myrows,Pagesize)=0 then   
  myPageCount:=myrows/Pagesize;    
else   
  myPageCount:=myrows/Pagesize+1    
end if;    
--关闭游标    
close p_cursor;    
end;    
/