频道栏目
首页 > 资讯 > SQL Server > 正文

PLSQL练习

11-09-21        来源:[db:作者]  
收藏   我要投稿

declare
  tabName varchar(128);
  a       number;
begin
  select table_name into tabName from user_tables where rownum = 1;
  dbms_output.put_line(tabName);
  if ('aa' = 'Aa') then
    a := 5;
  else
    a := 6;
  end if;
  dbms_output.put_line(a);
end;
/*
ICOL$
6
*/

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

begin
  dbms_output.put_line(0.95F); --float
  dbms_output.put_line(0.95D); --double
  dbms_output.put_line(0.95);
  dbms_output.put_line(trunc(25.176, 1));
  dbms_output.put_line(trunc(25.176, -1));
  dbms_output.put_line(round(25.176, 1));
  dbms_output.put_line(round(25.176, -1));
  dbms_output.put_line(ceil(25.176));
  dbms_output.put_line(floor(25.176));
end;
/*
9.49999988E-001
9.4999999999999996E-001
.95
25.1
20
25.2
30
26
25
*/

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

declare
  -- tabName varchar(128); -- no need
  num number;
begin
  for tabName in (select table_name from user_tables) loop
    dbms_output.put_line(tabName.table_name);
  end loop;
  for num in 1 .. 5 loop
    dbms_output.put_line(num);
  end loop;
end;

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

select ascii(' '), initcap('xu weigui'), ltrim('aaaadfdfd', 'a') from dual;

select interval '40' year from dual;

select interval '40-3' year to month from dual;
----------------------------------------------------------------------

declare
  a interval year to month;
begin
  a := numtoyminterval(10.5, 'year');
  dbms_output.put_line(a);
end;

/*
+10-06
*/
----------------------------------------------------
declare
  type mytype is table of varchar2(100) index by binary_integer;
  mytable mytype;

begin
  mytable(1) := 'xuweigui';
  mytable(123) := 'Andy';
  dbms_output.put_line(mytable(1));
  dbms_output.put_line(mytable(123));
end;

--------------------------------------------------------------
declare
  type mytype is table of varchar2(100);
  mytable mytype := mytype('a');
  mytable2 mytype :=mytype(5,4,3,2,1); --Until you initialize it, a nested table or varray is atomically null;

begin
  if mytable is null then
   null;
  elsif mytable.count =0 then
   null;
  end if;
 
  dbms_output.put_line('mytable init');
  for i in mytable.first .. mytable.last loop
   dbms_output.put_line(mytable(i));
  end loop;
 
  mytable.extend(5);
  dbms_output.put_line('mytable.extend(5)');
  for i in mytable.first .. mytable.last loop
   dbms_output.put_line(mytable(i));
  end loop;
 
  mytable(1) := 'xuweigui';
  mytable(6) := 'Andy';
  dbms_output.put_line('mytable assign value');
  for i in mytable.first .. mytable.last loop
   dbms_output.put_line(mytable(i));
  end loop;
 
  dbms_output.put_line('mytable2 init');
  for i in mytable2.first .. mytable2.last loop
   dbms_output.put_line(mytable2(i));
  end loop;
 
  mytable2.extend(20);
  dbms_output.put_line('mytable2 extend(20)');
  for i in mytable2.first .. mytable2.last loop
   dbms_output.put_line(mytable2(i));
  end loop;
 
exception
  when collection_is_null then
    dbms_output.put_line('exception');
end;


-------------------------------------------------------------------
DECLARE
TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
    staff staff_list;
    lname employees.last_name%TYPE;
    fname employees.first_name%TYPE;
BEGIN
    staff := staff_list(100, 114, 115, 120, 122);
    FOR i IN staff.FIRST..staff.LAST LOOP
        SELECT last_name, first_name INTO lname, fname FROM employees
        WHERE employees.employee_id = staff(i);
        DBMS_OUTPUT.PUT_LINE ( TO_CHAR(staff(i)) || ': ' || lname || ', ' || fname );
    END LOOP;
END;
/
--------------------------------------------------------------
declare
  rCnt integer;
begin
  select count(*) into rCnt from employees;
  dbms_output.put_line(sql%rowcount);
end;

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

declare
  --type curType is ref cursor;
  cursor mycursor is
    select * from employees;
begin
  --open mycursor for select * from employees;
  for empRec in mycursor loop
    dbms_output.put_line(empRec.first_name || ' ' || empRec.last_name);
  end loop;
  dbms_output.put_line('-----------------------------------------------');
  for empRec in (select * from employees where employee_id < 120) loop
    dbms_output.put_line(empRec.first_name || ' ' || empRec.last_name);
  end loop;
end;
---------------------------------------------
select count(case
               when salary < 2000 then
                1
               else
                null
             end) count1,
       count(case
               when salary between 2001 and 4000 then
                1
               else
                null
             end) count2,
       count(case
               when salary > 4000 then
                1
               else
                null
             end) count3
  from employees;
-----------------------------------------------------------

begin
  create table test(id integer); --error
end;
/

begin execute immediate 'create table  test (id integer)'; --dynamic sql
end;
/

--------------------------------------------------------------------
--pass parameter to dynamic sql
declare
  str   varchar2(1000);
  fname varchar2(100);
  lname varchar2(100);
begin
  str := 'select first_name, last_name from employees where employee_id = :emp_id';
  execute immediate str
    into fname, lname
    using 201;
  dbms_output.put_line(fname || ' ' || lname);
end;
/
-------------------------------------------------------------
select to_number('F123.456,78',
                 'L999G999D99',
                 'NLS_NUMERIC_CHARACTERS='',.'' ' || ' NLS_CURRENCY=''f'' ' ||
                 'NLS_ISO_CURRENCY=FRANCE')
  FROM DUAL;
--123456.78
SELECT TO_CHAR(123456.78,
               'L999G999D99',
               'NLS_NUMERIC_CHARACTERS='',.'' ' || ' NLS_CURRENCY=''f'' ' ||
               'NLS_ISO_CURRENCY=FRANCE')
  FROM DUAL;
--          f123.456,78

-------------------------------------------------------------------------------
SELECT last_name,
       employee_id,
       manager_id,
       LEVEL,
       SYS_CONNECT_BY_PATH(last_name, '/') "Path"
  FROM employees
--START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
 ORDER SIBLINGS BY last_name;
--------------------------------------------------------------------
declare
  my_exception exception;
  pragma exception_init(my_exception, -259);--should between -20999, -20000
begin
  begin
    raise MY_EXCEPTION;
  EXception
    when my_exception then
      dbms_output.put_line('first exception');
      dbms_output.put_line(sqlcode);-- -259
      raise;
  end;
EXception
  when my_exception then
    dbms_output.put_line('second exception');
end;
---------------------------------------------------
DECLARE
   past_due   EXCEPTION;
   acct_num   NUMBER;
BEGIN
   DECLARE                                  
      past_due      EXCEPTION;                 
      acct_num      NUMBER;
      due_date      DATE      := SYSDATE - 1;
      todays_date   DATE      := SYSDATE;
   BEGIN
      IF due_date < todays_date
      THEN
         RAISE past_due;                     
      END IF;
   END;                                 
EXCEPTION
   WHEN past_due
   THEN                                
      DBMS_OUTPUT.put_line('Handling PAST_DUE exception.');
   WHEN OTHERS
   THEN
      --go this path
      DBMS_OUTPUT.put_line('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;


DECLARE
   past_due   EXCEPTION;
   pragma exception_init(past_due, -259);
   acct_num   NUMBER;
BEGIN
   DECLARE                                  
      past_due      EXCEPTION;
      pragma exception_init(past_due, -259);                 
      acct_num      NUMBER;
      due_date      DATE      := SYSDATE - 1;
      todays_date   DATE      := SYSDATE;
   BEGIN
      IF due_date < todays_date
      THEN
         RAISE past_due;                     
      END IF;
   END;                                 
EXCEPTION
   WHEN past_due
   THEN
      --go this path                                
      DBMS_OUTPUT.put_line('Handling PAST_DUE exception.');
   WHEN OTHERS
   THEN     
      DBMS_OUTPUT.put_line('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;
----------------------------------------------------------------
begin
  raise_application_error(-20111, 'error message');
exception
 when others then
  dbms_output.put_line(sqlcode);
  dbms_output.put_line(sqlerrm);
end;
----------------------------------------------------------------
create or replace package my_package is
  num number := 5;
  one number :=1;
  function f return number;
  procedure print;
  function add (first number, second number default 1) return number; --second has a default value of 1 
end my_package;

create or replace package body my_package is
  i number := 3;
  function f return number is
  begin
    return num;
  end f;
  procedure print is
  begin
    dbms_output.put_line(f());
  end print;
  function add (first number, second number) return number is
  begin
   return first + second;
  end add;
end my_package;

begin
 dbms_output.put_line(my_package.add(5));
 dbms_output.put_line(my_package.add(5, 5));
 dbms_output.put_line(my_package.add(second => 5, first=>12));
 my_package.print();
 dbms_output.put_line(my_package.num);
end;
----------------------------------------------------------------------
create table emp as select * from employees;

create table tab (id number, oldsal number);

create or replace trigger tri
  before update on emp
  for each row
declare
  progma autonomous_transaction;
begin
  insert into tab values (:old.employee_id, :old.salary);
  commit;
end tri;


update emp set salary = salary;
rollback;--although rollback here, data is inserted into tab in trigger

select * from tab;
---------------------------------

begin
for i in 1..5 loop
 dbms_output.put_line(i);
 if i=3 then
  exit;--exit the loop
 end if;
end loop;
dbms_output.put_line('exit loop');
end;
------------------------------

begin
for j in 1..2 loop
  for i in 1..5 loop
   dbms_output.put_line('innner loop ' || i);
   if i=3 then
    exit;--exit the inner loop
   end if;
  end loop;
  dbms_output.put_line('outer loop ' || j);
end loop;
dbms_output.put_line('end');
end;

--------------------------------------
declare
  type myrecord_type is record(
    first_name employees.first_name%type,
    last_name  employees.last_name%type);
  TYPE mycur_type IS REF CURSOR RETURN myrecord_type;
  cur    mycur_type;
  person myrecord_type;
begin
  if 1 = 2 then
    open cur for
      select first_name, last_name from employees where employee_id = 198;
  else
    open cur for
      select first_name, last_name from employees where employee_id = 199;
  end if;
  loop
    fetch cur
      into person;
    exit when cur%NOTFOUND;
    dbms_output.put_line(person.first_name || ' ' || person.last_name);
  end loop;
  close cur;
end;


---------------------------------
alter session set plsql_ccflags = 'to_debug:true'

declare
  $if $$to_debug $then
   cursor cur is select first_name, last_name from employees where employee_id = 198;
  $else
   cursor cur is select first_name, last_name from employees where employee_id = 199;
  $end
begin
   for person in cur loop
    dbms_output.put_line(person.first_name || ' ' || person.last_name);
  end loop;
end;
/*
Example Using ALTER PROCEDURE to Set PLSQL_CCFLAGS
ALTER PROCEDURE circle_area COMPILE PLSQL_CCFLAGS = 'my_debug:TRUE'
REUSE SETTINGS;
*/

-----------------------------------------
DECLARE
    s PLS_INTEGER := 0;
    i PLS_INTEGER := 0;
    j PLS_INTEGER;
BEGIN
    <<outer_loop>>
    LOOP
        i := i + 1;
        j := 0;
        <<inner_loop>>
        LOOP
            j := j + 1;
            s := s + i * j; -- sum a bunch of products
            EXIT inner_loop WHEN (j > 5);
            EXIT outer_loop WHEN ((i * j) > 15);
        END LOOP inner_loop;
    END LOOP outer_loop;
    DBMS_OUTPUT.PUT_LINE('The sum of products equals: ' || TO_CHAR(s));
END;
------------------------------------------------------
--cursor with parameter
DECLARE
  CURSOR c1(job VARCHAR2, max_wage NUMBER) IS
    SELECT *
      FROM employees
     WHERE job_id = job
       AND salary > max_wage;
BEGIN
  FOR person IN c1('CLERK', 3000) LOOP
    -- process data record
    DBMS_OUTPUT.PUT_LINE('Name = ' || person.last_name || ', salary = ' ||
                         person.salary || ', Job Id = ' || person.job_id);
  END LOOP;
END;
 
作者“红豆加奶”

相关TAG标签
上一篇:台积电:绝大多数7nm客户都会转向6nm_IT新闻_博客园
下一篇:最后一页
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站