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

Bài 5.PL/SQL cơ bản - I/O dữ liệu

1.    Console:
DBMS_OUTPUT.ENABLE (1000000);   // enable output voi 1000000 ky tu
dbms_output.put_line('Hi Binh, I can write PL/SQL');

2.    Làm việc với File:
--------------SYSDBA-----------------------
SQL> CREATE DIRECTORY MYDIR AS 'C:\TESTLOC';
Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY MYDIR TO SCOTT
Grant succeeded.

--------------SCOTT-------------------------
DECLARE
  L_HANDLER UTL_FILE.FILE_TYPE;
BEGIN
  L_HANDLER := UTL_FILE.FOPEN('MYDIR', 'SYS.txt', 'W');
  UTL_FILE.PUTF(L_HANDLER, 'UTL_FILE write mode demonstration');
  UTL_FILE.FCLOSE(L_HANDLER);
END;

PL/SQL procedure successfully completed.
3.    Exception
SET SERVEROUTPUT ON
Single Exception Handler for Multiple Exceptions
CREATE OR REPLACE PROCEDURE select_item (
  t_column VARCHAR2,
  t_name   VARCHAR2
) AUTHID DEFINER
IS
  temp VARCHAR2(30);
BEGIN
  temp := t_column;  -- For error message if next SELECT fails

  -- Fails if table t_name does not have column t_column:

  SELECT COLUMN_NAME INTO temp
  FROM USER_TAB_COLS
  WHERE TABLE_NAME = UPPER(t_name)
  AND COLUMN_NAME = UPPER(t_column);

  temp := t_name;  -- For error message if next SELECT fails

  -- Fails if there is no table named t_name:

  SELECT OBJECT_NAME INTO temp
  FROM USER_OBJECTS
  WHERE OBJECT_NAME = UPPER(t_name)
  AND OBJECT_TYPE = 'TABLE';

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp);
   WHEN others THEN
            send_sms_binhtv('Error sys.dba_op.auto_drop_partitions: ' || SQLERRM);
            insert into binhtv.dbamf_log_jobs (id,name,status,event_date, note)
                values(binhtv.dbamf_log_jobs_seq.nextval,'Error sys.dba_ct.ct_session',-1,sysdate,'1:OK');
            commit;        
DBMS_OUTPUT.PUT_LINE ('Unexpected error');    
RAISE;
   END;
END;
/



@ Trần Văn Bình - Founder of Oracle DBA AZ #OraAz #OracleDBAAz #OracleTutorial

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master