wqteqwetqwet

plsql:

变量命名最多30个字符
匿名块、命名块、嵌套块
varchar2

BOOLEAN 类型
用于存储逻辑值（TRUE、FALSE 和 NULL）
不接受任何参数
不能向数据库中插入 BOOLEAN 数据
不能将列值提取或选择到 BOOLEAN 变量中
只允许对 BOOLEAN 变量执行逻辑操作

IN OUT
接受值并返回已更新的值

 

RAISE

 

 

 

Raise_Application_Error
用于创建用户定义的错误消息的过程
既可以在可执行部分中使用，也可以在异常部分中使用
错误编号必须介于 –20000 和 –20999 之间
错误消息的长度可长达 2048 个字节

 

 

 

cursor s1(l_msisdn number) is
    select *
      from custcare.number_segment_imsi
     where begin_number <= l_msisdn
       and end_number >= l_msisdn;

for s1_1 in s1(m1_1.msisdn) loop

 

 

declare
  TYPE t_emp IS RECORD(
        id          s_emp.ID%Type,
        lname       s_emp.LAST_NAME%Type
  );
 
  v_emp t_emp;
begin
   select id, last_name
      into v_emp
      from s_emp
      where id=1;
      dbms_output.PUT_LINE(v_emp||''||'hello');

 

declare
  type t_stutable is table of student%rowtype index by binary_integer;
  v_student t_stutable;
begin
  select * into v_student(1001) from student where id = 1001;
end;

 

FETCH cursor_name INTO var1, var2, …;
FETCH cursor_name INTO record_var;

 

LOOP
FETCH cursor INTO…
EXIT WHEN cursor%NOTFOUND;
END LOOP(见loop_cursor.sql、loop_cursor2.sql)
WHILE cursor%FOUND LOOP
FETCH cursor INTO…
END LOOP(见while_cursor.sql)
FOR var IN cursor LOOP
FETCH cursor INTO…
END LOOP(见for_cursor.sql)

 %FOUND    若前面的FETCH语句返回一行数据，则%FOUND返回TRUE，如游标    未打开就检查%FOUND，则返回ORA-1001
 %NOTFOUND     与%FOUND行为相反
 %ISOPEN     确定游标是否打开
 %ROWCOUNT     当前游标的指针位移量，到目前为止游标所检索的数据行的个数，若 　未打开就引用，返回ORA-1001

 

Declare
 --Cursor declaration
 CURSOR c_TempData IS SELECT * from temp_table;
 --Record to store the fetched data
 v_TempRecord c_TempData%ROWTYPE;
BEGIN
 --location 1
 OPEN c_TempData;  -- Open cursor
 --location 2
 FETCH c_TempData INTO v_TempRecord; -- Fetch first row
 --location 3
 FETCH c_TempData INTO v_TempRecord; -- Fetch second row
 --location 4
 FETCH c_TempData INTO v_TempRecord; -- Third fetch
 --location 5
 CLOSE c_TempData;
 --location 6
END;

 

---------------------------------------------------------------------------
14,18,19,21,30,31,39

 

declare
  type t_stutable is table of student%rowtype index by binary_integer;
  v_student t_stutable;

  k number(7, 4) := 123.4654;
  v_excep EXCEPTION;

  function myfunc(v_param in varchar2) return varchar2 is
  begin
    dbms_output.put_line('v_param：' || v_param);
    return 'func';
  end func;

begin

  case name
    when 'd' then
      dbms_output.put_line(2 * 2);
    when 'dj' then
      dbms_output.put_line('a ye,I am dj');
    else
      dbms_output.put_line(4 * 4);
  end case;

  select * into v_student(1001) from student where id = 1001;

  dbms_output.put_line('myfunc：' || myfunc('yuhb'));

  -- drop preduce/table/function a

exception
  when no_data_found then
    dbms_output.put_line('no_data_found');
 
  when v_excep then
    dbms_output.put_line('v_excep');
 
  when others then
    dbms_output.put_line('others');
end;

create or replace trigger updatemajorstats after insert or delete or update on students declare cursor c_statistics is
  select major, count(*) total_students, sum(current_credits) total_credits
    from students
   group by major;
begin
  for v_statsrecord in c_statistics loop
    update major_stats
       set total_credits  = v_ statsrecord.total_credits,
           total_students = v_ statsrecord.total_students
     where major = v_ statsrecord.major;
    if sql%notfound then
      insert into major_stats
        (major, total_credits, total_students)
      values
        (v_ statsrecord.major,
         v_ statsrecord.total_credits,
         v_ statsrecord.total_students);
    end if;
  end loop;
end updatemajorstats;

 

-- Created on 2015-10-30 by EX-YUHUIBIN001
declare
  -- Local variables here
  i integer;
begin
 select 1 into i  from dual;

  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('aaaa');
  END IF;
end;

 

 

 

 

 

 

 

<<outer>>
declare
  v_num number := 1;
begin
  <<inner>>
  declare
    v_num number := 10;
  begin
    dbms_output.put_line('v_num = '||v_num);
    dbms_output.put_line('outer.v_num = ' || outer.v_num);
  end;
end;

------------------------------------------------------------------------------------------
declare
  v_tmp varchar2(128);

  cursor mycur(p_dept_id number) is
    select sysdate curdate from dual;
begin
  for v_cnt in reverse 1 .. 5 loop
    dbms_output.put_line(v_cnt);
  end loop;

  open mycur(50);

  fetch mycur
    into v_tmp;

  dbms_output.put_line('---1--' || v_tmp);

  while mycur%found loop
    dbms_output.put_line('---2--' || v_tmp);
 
    fetch mycur
      into v_tmp;
    fetch mycur
      into v_tmp;
    fetch mycur
      into v_tmp;
 
    dbms_output.put_line('---3--' || v_tmp);
  end loop;
  close mycur;

end;

 

 

declare
  v_tmp varchar2(128);

  cursor mycur(p_dept_id number) is
    select sysdate curdate from dual;
begin
  for v_cnt in reverse 1 .. 5 loop
    dbms_output.put_line(v_cnt);
  end loop;

  open mycur(50);

  fetch mycur
    into v_tmp;

  dbms_output.put_line('---1--' || v_tmp);

  while mycur%found loop
    dbms_output.put_line('---2--' || v_tmp);
 
    fetch mycur
      into v_tmp;
    fetch mycur
      into v_tmp;
    fetch mycur
      into v_tmp;
 
    dbms_output.put_line('---3--' || v_tmp);
  end loop;
  close mycur;

end;

 

 

 

declare
  v_emp s_emp%rowtype;

  cursor cur_emp(p_dept_id number) is
    select sysdate curdate from dual;
begin
  open cur_emp(50);

  fetch cur_emp
    into v_emp;
  while cur_emp%found loop
    dbms_output.put_line(v_emp.id || ' ' || v_emp.first_name);
 
    fetch cur_emp
      into v_emp;
  end loop;

  close cur_emp;

end;

 

 

 

declare
  v_tmp varchar2(128);

  cursor mycur(p_dept_id number) is
    select sysdate curdate from dual;
begin
  for v_cnt in reverse 1 .. 5 loop
    dbms_output.put_line(v_cnt);
  end loop;

  open mycur(50);

  fetch mycur
    into v_tmp;

  dbms_output.put_line('---1--' || v_tmp);

  while mycur%found loop
    dbms_output.put_line('---2--' || v_tmp);
 
    fetch mycur
      into v_tmp;
    fetch mycur
      into v_tmp;
    fetch mycur
      into v_tmp;
 
    dbms_output.put_line('---3--' || v_tmp);
  end loop;
  close mycur;

end;

 

 

 

 

 

-- Created on 2015-10-30 by EX-YUHUIBIN001
declare
  cursor mycur is
    select sysdate curdate from dual;
begin
  open mycur;
  dbms_output.put_line('ROWCOUNT：' || mycur%ROWCOUNT);
  dbms_output.put_line('ROWCOUNT：' || mycur%ROWCOUNT);
  close mycur;
end;

 

 

declare
  v_tmp varchar2(128);

  cursor mycur(p_dept_id number) is
    select sysdate curdate from dual;
begin
  for v_cnt in reverse 1 .. 5 loop
    dbms_output.put_line(v_cnt);
  end loop;

  open mycur(50);

  fetch mycur
    into v_tmp;

  dbms_output.put_line('---1--' || v_tmp);

  while mycur%found loop
    dbms_output.put_line('---2--' || v_tmp);
 
    fetch mycur
      into v_tmp;
    fetch mycur
      into v_tmp;
    fetch mycur
      into v_tmp;
 
    dbms_output.put_line('---3--' || v_tmp);
  end loop;
  close mycur;

end;

 

 

 

declare
  type t_stutable is table of student%rowtype index by binary_integer;
  v_student t_stutable;

  k number(7, 4) := 123.4654;
begin
  select * into v_student(1001) from student where id = 1001;
end;

 

 

 

-- 排序字段
select sysdate firstA
  from dual
union
select sysdate + 1 secondB from dual order by firstA;

-- 有效的删除
delete from emp e
 where e.rowid > (select min(x.rowid) from emp x where x.emp_no = e.emp_no);

--------------------------------------------------------------------------------------------------
select sys_guid() from dual;

select * from dba_constraints where table_name = 'IT_HIGH_GROUP';
--------------------------------------------------------------------------------------------------
select * from user_objects;

select * from user_source;

select * from dba_users where username = 'PLANGROUPCDE';

select * from dba_profiles;

select * from user_role_privs;

select * from session_privs; -- 当前用户的权限

select * from dba_synonyms; -- 查询所有同义词（隐藏对象的名称和所有者）：手写查询时比较简便点；如果数据表什么的有变化了，程序不用改写，只需要重新定义个同义词就行了