pl sql整理

`show error;展示错误
执行方式
1、exec 过程名(参数);
2、call 过程名(参数);
create or replace procedure
变量 v_
常量 c_
游标 变量名_cursor
例外 e_

declear 声明
定义 、变量、常量、游标、复杂数据类型;
begin 
执行部分
exception 例外

(1)pl/sql块
declare
--定义变量
v_ename varchar2(5);
v_sal number(7,2);
begin   
--执行部分
  select ename,sal into v_ename,v_sal from emp where empno = &no;
  --在控制台显示
  dbms_output.put_line('用户名是:'||v_ename||',工资是:'||v_sal);
--异常处理  
exception
    when no_data_found then
      dbms_output.put_line('无此人、请重新输入');
end;
no_data_found找不到相关数据异常

创建过程
create procedure sp_pro(spName varchar2,newSal number)
is
begin
--执行部分，根据用户名去修改工资
update emp set sal = newSal where ename = spName;
end;
调用过程
exec sp_pro('SCOTT',4567);
java j 调用存储过程 call sp_pro('SCOTT',4567);

函数 函数返回只有一个值
案例---输入员工的姓名，返回该员工的年资
create function sp_fun(spName in varchar2)
return number is yearSal number(7,2);
begin
--执行部分
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename = spName;
return yearSal;
end;

show erorr;看错误

掉用函数
var abd number;
call sp_fun('SCOTT') into:abd;

包
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
--创建一个包sp_package 
--声明该包有一个过程sp_pro
--声明该包有一个函数sp_fun
create or replace package sp_package is
 procedure sp_pro(spName varchar2,newSal number);
 function sp_fun(spName in varchar2)return number;
end;

--给包sp_package 实现包体
创建包体
create or replace package body sp_package is
procedure sp_pro(spName varchar2,newSal number) is
begin
update emp set sal = newSal where ename = spName;
end;
function sp_fun(spName in varchar2)
return number is yearSal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename = spName;
return yearSal;
end;
end;
/

v变量 c常量  
:= 等于
c_tax_rate number(3,2) := 0.03;赋值

触发器
触发器是指隐含的执行的存储过程
create trigger

v_ename emp.ename%type;
定义类型为emp表的ename类型

标识

复合类型-pl/sql记录
类似与高级语言中的结构体
--pl/sql记录实例
declare
--定义一个pl/sql记录类型 emp_record_type，类型包含三个数据分别是
--name，salary，title
type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);
--定义了一个sp_record 变量，这个变量的类型是emp_record_type 
sp_record emp_record_type;
begin
select ename,sal,job into sp_record
from emp where empno = 7788;
dbms_output.put_line('姓名是：'||sp_record.name||',工资是：'||sp_record.salary);
end;

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(0)
from emp where empno = 7788;
dbms_output.put_line('姓名是：'||sp_table(0));
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;
--关闭游标
close test_cursor;
end;
条件分支
if -- then
if -- then -- else
if -- then -- elsif --else
end if;
书写形式
if v_job = 'aa' then
--------------------
elsif v_job = 'bb' then
--------------------
else
------------------
end if;

循环语句
(1)
loop
end loop;
:= 表示赋值

向users表插入10条数据

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

(2)
while 循环
create or replace precudure insertUser2(name varchar2)
is
--定义 := 表示赋值
v_num number := 11;
begin
while v_num  <= 20 
loop
insert into users values(v_num,name);
--自增
v_num:=v_num + 1；
end loop;
end;

(3)
for 循环

go to <<end loop>>

null;什么都不做;继续执行

--创建一个包，在该包中，定义类型test_cursor,游标
create or replace package tespackage as
type test_cursor is ref cursor;
end tespackage;
--
pl/sql分页

create or replace procedure fenye
(tableName in varchar2,
Pagesize in number,--每页显示多少条
pageNow in number,第几页
myrows out number,--总记录数
myPageCount out number,--总页数
p_cursor out tespackage.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 ('||tableName||') t1 where rownum
<='||v_end||'
)where rm >= '||v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--计算myrows和mypagecount
v_sql:='select count(*) from '||tableName;
--执行sql语句 ，并把返回的值，赋给myrows;
execute immediate v_sql into myrows;
--计算mypagecount
if mod(myrows,pageSize)=0 then
mypagecount:=myrows/pageSize;
else
mypagecount:=myrows/pageSize+1;
end if;
--关闭游标
close p_cursor;
end;

--例外处理
--视图
视图是一个虚拟表，其内容由查询定义。同真实的表一样，
视图包含一系列带有名称的列和行数据。
但是，视图并不在数据可中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成.

  function get_department_tbc(paln_type varchar) return varchar as
    type_name varchar(20);
  begin
    if  paln_type = 'W' then
select decode(paln_type, 'W', '境内外部培训','')
      into type_name
      from dual;
    return type_name;
 elsif paln_type = 'O'
        select decode(paln_type, 'O', '境外培训','')
into type_name
      from dual;
    return type_name;
     end if;
   end;
   
function get_department_tbc(paln_type varchar) return varchar as
    type_name varchar(20);
  begin
    select decode(paln_type, 'W', '境内外部培训','')
      into type_name
      from dual;
    return type_name;
  end;

--------->>>>>>>>>>>>>>>>>-------------
循环游标
 FUNCTION full_ug_name_split(o_id NUMBER,spid NUMBER) RETURN VARCHAR2 IS
      CURSOR c_path IS
        SELECT UG_NAME NAME
          FROM user_group
        CONNECT BY PRIOR d_parent_id = ID
         START WITH ID = o_id;
      v_path varchar2(4000);
      level_count number :=0;
    BEGIN
      v_path := '';

      FOR i IN c_path LOOP
        v_path := i.NAME || '/' || v_path;
      END LOOP;

      if spid = 1 then
          select substr(v_path,1,instr(v_path,'/',1,1)-1) into v_path from dual;
      elsif spid = 2 then
          select substr(v_path,instr(v_path,'/',1,1)+1,instr(v_path,'/',1,2)-instr(v_path,'/',1,1)-1) into v_path from dual;
      else
          select instr(v_path,'/',1,2) into level_count from dual;
          if level_count = 0 then
             v_path := '';
          else
             select substr(v_path,instr(v_path,'/',1,2)+1) into v_path from dual;
          end if;
      end if ;

      v_path := RTRIM(v_path, '/');

      RETURN(v_path);
    END;
---------------------->>>>>>>>>>>>>-------------