Thứ Tư, 8 tháng 1, 2020

Bài 3.PL/SQL cơ bản - Cấu trúc điều khiển (if, else, case, for, while)

1.    Cấu trúc tuần tự
Thực hiện lần lượt từng lệnh một.
dbms_output.put_line('Excellent 1');
dbms_output.put_line('Excellent 2');
dbms_output.put_line('Excellent 3'); 


2.    Cấu trúc rẽ nhánh
Rẽ nhánh không đầy đủ (if)
IF ename = 'SCOTT' THEN
beam_me_up := 'YES';
COMMIT;
Rẽ nhánh đầy đủ (if…else)
 IF sales > (quota + 200) THEN
      bonus := (sales - quota)/4;
    ELSE
      bonus := 50;
    END IF;
----
IF monthly_value <= 4000 THEN
      ILevel := 'Low Income';
ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN
      ILevel := 'Avg Income';
ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN
      ILevel := 'Moderate Income';
ELSE
      ILevel := 'High Income';
END IF;
Case
CASE v_grade
  WHEN 'A' THEN dbms_output.put_line('Excellent');
  WHEN 'B' THEN dbms_output.put_line('Very Good');
  WHEN 'C' THEN dbms_output.put_line('Good');
  WHEN 'D' THEN dbms_output.put_line('Fair');
  WHEN 'F' THEN dbms_output.put_line('Poor');
  ELSE dbms_output.put_line('Error: No such grade');
END CASE

SELECT TRANS_DATE,CASE
WHEN TRIM(TO_CHAR(trans_date,'DAY')) IN ('SATURDAY','SUNDAY') THEN 'weekend'
ELSE 'weekday'
END "Day Type"
FROM transactions;





3.    Cấu trúc lặp
·         LOOP không định trước
x := 0;
y := 1000;
LOOP
x := x + 1;
y := y - x;
EXIT x > y;
END LOOP;

·         Fetch 1 dòng dữ liệu dau tien từ cursor: (phải mở, đóng cursor)
·         SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id =30;
empno employees.employee_id%TYPE;
lname employees.last_name%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO empno, lname;
DBMS_OUTPUT.PUT_LINE(empno ||' '||lname);
CLOSE emp_cursor;
END;
/
Con trỏ emp_cursor trả về 6 employees có department_id=30 nhưng câu lệnh FETCH sẽ lấy về chỉ 1 row, muốn lấy tất cả các row thì phải dùng vòng lặp (for)
·         Fetch lấy all row từ cursor: LOOP...FETCH (phải mở, đóng cursor)
DECLARE
-- Khai báo cursor để truy vấn dữ liệu
CURSOR c_Emp IS
SELECT *
FROM emp
WHERE dept_id = 10;

-- Khai báo biến cursor tương ứng để chứa dòng dữ liệu
v_Emp c_EMP%rowtype;   -- v_Emp giống như kiểu record

BEGIN
-- Mở cursor
OPEN c_Emp;
LOOP
-- Lấy dòng dữ liệu từ cursor
FETCH c_Emp INTO v_Emp;

-- Thoát khỏi vòng lặp nếu đã lấy hết dữ liệu trong cursor
EXIT WHEN c_Emp%notfound;

-- Bổ sung dữ liệu vào Emp_ext với dữ liệu lấy được từ cursor
INSERT INTO Emp_ext (empno, ename, job) VALUES (v_Emp.empno, v_Emp.ename, v_Emp.job);
END LOOP;
-- Đóng cursor
CLOSE c_Emp;
END;

REM ===================================c
REM Grant on commit refresh system privilege to the owner of on
REM commit refresh materialized view which references tables
REM outside of the owner schema
REM ===================================
declare
  owner varchar(30);
  cursor c_mv_owner is
     select distinct u.name
        from sum$ s, sumdep$ d, obj$ o1, obj$ o2, user$ u
        where s.obj# = d.sumobj# and
              bitand(s.mflags, 65536) != 0 and
              d.p_obj# = o1.obj# and
              s.obj# = o2.obj# and
              o1.owner# != o2.owner# and
              o2.owner# = u.user#;
begin
   open c_mv_owner;
   loop
     fetch c_mv_owner into owner;
     exit when c_mv_owner%NOTFOUND;
     execute immediate 'GRANT ON COMMIT REFRESH TO ' ||
                       dbms_assert.enquote_name(owner, FALSE);
   end loop;
   close c_mv_owner;
end;
/


·         FOR-LOOP: Loop đã định trước
declare
    cursor c1 is select  distinct tablespace_name  from dba_data_files  where   tablespace_name like 'DATA200%' or tablespace_name like 'INDX200%' order by tablespace_name;
begin
    for r1 in c1 loop
        dbms_output.put_line('SQL '''||'alter tablespace ' || r1.tablespace_name || ' read only'';');
        dbms_output.put_line('SQL '''||'alter tablespace ' || r1.tablespace_name || ' offline'';');       
    end loop;
end;
-- Switch all existing users to new temp tablespace.
BEGIN
  FOR cur_user IN (SELECT username FROM dba_users WHERE temporary_tablespace = 'TEMP') LOOP
    EXECUTE IMMEDIATE 'ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE temp2';
  END LOOP;
END;

-- Không sử dụng CURSOR
BEGIN
  FOR loop_emp IN
  (SELECT *
    FROM
      employees
    WHERE salary>15000)
  LOOP
    dbms_output.put_line('Number of rows processed: '||nvl(to_char(sql%rowcount),'Null'));
  END LOOP loop_emp;
END;

From <http://www.dba-oracle.com/t_adv_plsql_implicit_cursor_FOR_loop.htm>

 for r1 in (select trunc(completion_time) completion_time, round(sum(blocks*block_size)/1024/1024/1024,2) as archived_log_gb from V$ARCHIVED_LOG
                        where trunc(completion_time) >= trunc(sysdate-7)
                        --and trunc(completion_time)>= to_date(trunc(sysdate),'dd/mm/yyyy')
                        and dest_id=1
                        group by trunc(completion_time)
                        order by trunc(completion_time) desc) loop
              
                tAll:=tAll||'<tr>
                            <td>'||r1.completion_time||'</td>
                            <td>'||r1.archived_log_gb||'</td>
                         </tr>' ;
           end loop;
           tAll:=tAll||
PROCEDURE           auto_drop_partition (v_date DATE)
IS
    date_num1        INT := 40;                         -- Chi luu giu 40 ngay, PROM_CHARGE_DAILY_HIS
    date_num2        INT := 70;                         -- Chi luu giu 40 ngay, LOG_WS_999
    date_num3        INT := 180;                        -- Chi luu giu 180 ngay, reg_sms_log
    --date_num4        INT := 300;                        --log dau noi, Dungnd

    CURSOR c_partition
    IS
        SELECT   table_name, partition_name
          FROM   user_tab_partitions
         WHERE   (sysdate - to_date(SUBSTR(partition_name,length(partition_name)-7,8),'yyyy/mm/dd') > date_num1
                        AND table_name IN
                            ('PROM_CHARGE_DAILY_HIS'))
                       OR  
                        (sysdate - to_date(SUBSTR(partition_name,5,6),'yyyymm') > date_num2
                        AND table_name IN
                            ('LOG_PROCESS')) 
                      OR  
                       (sysdate - to_date(SUBSTR(partition_name,length(partition_name)-7,8),'yyyy/mm/dd') > date_num3
                        AND table_name IN
                            ('CP_SUM_LOG'))
  
                       OR  
                        (sysdate - to_date(SUBSTR(partition_name,5,6),'yyyymm') > date_num3
                        AND table_name IN
                            ('REG_SMS_LOG')) 
                      OR
                       (sysdate - to_date(SUBSTR(partition_name,length(partition_name)-7,8),'yyyy/mm/dd') > 365
                        AND table_name IN
                            ('LOG_WS_999'))
                       OR
                       (sysdate - to_date(SUBSTR(partition_name,length(partition_name)-7,8),'yyyy/mm/dd') > 365
                        AND table_name IN
                            ('SEND_999_LOG','SUB_TRANSACTION_LOG'))    ;
                      
    v_sql_command   VARCHAR2 (2400);
BEGIN
    -- add partitions

    FOR v_data IN c_partition
    LOOP
        BEGIN
            EXECUTE IMMEDIATE   ' Alter table '
                             || v_data.table_name
                             || ' drop partition '
                             || v_data.partition_name;
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line (SQLERRM);
        END;
    END LOOP;
END;
-- Chạy cả khi exception
declare 
    cursor c1 is
        SELECT a.INST_ID,count(1) CountSession
        FROM gv$session a, gv$process b 
        WHERE b.ADDR = a.paddr
            AND a.inst_id=b.inst_id  
            and a.status='INACTIVE'
            and A.USERNAME  in ('NEIF_OWNER')
            and prev_exec_start > sysdate - 5/24*60
            and type='USER'
        group by a.inst_id;
    cursor c2 is
        SELECT 'ALTER SYSTEM KILL SESSION '''||a.sid||','||a.serial#||',@'||a.inst_id||''' immediate' sqltext, a.inst_id
        FROM gv$session a, gv$process b 
        WHERE b.ADDR = a.paddr
            AND a.inst_id=b.inst_id  
            and a.status='INACTIVE'
            and A.USERNAME  in ('USER1_OWNER')
            and prev_exec_start > sysdate - 5/24*60
            and type='USER' order by a.inst_id;      
    vInst varchar2(20);
    vSession number;
begin
     for r1 in c1 loop
         begin
            if r1.CountSession > 100 then
                vInst:=r1.inst_id;
                --dbms_output.put_line(vInst);
                for r2 in c2 loop
                    begin
                        if r2.inst_id=vInst then
                            dbms_output.put_Line(r2.sqltext);
                            execute immediate r2.sqltext;                       
                        end if;
                    EXCEPTION
                        WHEN OTHERS THEN
                           dbms_output.put_Line('Error but CONTINUE');
                           CONTINUE;
                    end;
                end loop;               
            end if;
         end;
     end loop;
    
     insert into dba_tc_logs (id, content) values (dba_tc_seq.nextval,'Complted to Kill NEIF_OWNER INACTIVE sesssions after 5 minutes');
     commit;
end;

-- Tạo dữ liệu test
create table t (name1 varchar2(100), name2 varchar2(100));

alter table t rename to spct;

declare
     t number;
begin
     for t in 1..20000 loop
          insert into spct values (Null,'a');
          commit;
     end loop;
end;
/

·         WHILE-LOOP
DECLARE
countryid locations.country_id%TYPE := 'CA';
loc_id locations.location_id%TYPE;
new_city locations.city%TYPE := 'Montreal';
counter NUMBER := 1;
BEGIN
SELECT MAX(location_id) INTO loc_id FROM locations
WHERE country_id = countryid;
WHILE counter <= 3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((loc_id + counter), new_city, countryid);
counter := counter + 1;
END LOOP;
END;
/
Notes:
·         To exit a loop use the EXIT WHEN statement;
·         To skip to the next iteration of the loop, use the CONTINUE WHEN statement (Oracle 11g and above).
·         Ngoài ra:
·         Goto:
goto the_end;
<<the_end>>
dbms_output.put_line ('The End.');

@ Trần Văn Bình - Founder of Oracle DBA AZ #OraAz #OracleDBAAz #BossData #OracleTutorial
By lúc
Labels:

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master