iLMS知識社群(Sites)師生部落格(Blogs)朝陽首頁(Homepage)登入
QQQQQQQQQQQQQQQQQ
by 賴柏諺 2017-06-19 16:23:12, 回應(0), 人氣(64)
 quiz05 (by 江杰霖, 06-18 15:56)
2.SET serveroutput ON
DECLARE 
  v_value NUMBER :=0;
BEGIN
  v_value := 1/0;
  DBMS_OUTPUT.put_line(v_value);
  
  exception
  when zero_divide then
   DBMS_OUTPUT.put_line('除數0');
  
END;
/


3.
SET serveroutput ON
declare
 e_insert_excep exception;
 pragma exception_init(e_insert_excep,-00001)

BEGIN 
INSERT INTO regions VALUES(1, 'Taipei');

exception
 when e_insert_excep then
  dbms_output.put_line('無法新增區域');
 
END;
/



 quiz06 (by 江杰霖, 06-18 15:58)
1.建立一個程序
CREATE OR REPLACE PROCEDURE ADD_DEPT 
    (  p_dep_id         number
   , p_name     varchar2
   , p_phone    varchar2 
   )
IS
BEGIN
    INSERT INTO dept_quiz (DEPARTMENT_ID,DEPARTMENT_NAME, PHONE)
    VALUES( p_dep_id, p_name,p_phone);
exception
when dup_val_on_index then
dbms_output.put_line('DUPLICATED ID!!');
END ADD_DEPT;

2.
SQL:

set serveroutput on
begin
    ADD_DEPT(300, 'Info Management', '23323000');
    ADD_DEPT(310, 'Info Engineering', '23324000');
    ADD_DEPT(310, 'Exception', '23325000');
end;




3.建立一個程序
CREATE OR REPLACE PROCEDURE get_dept_phone (
    p_dep_id   NUMBER,
    p_phone    OUT VARCHAR2
) IS
    v_phone   VARCHAR2(10);
BEGIN
    SELECT
        phone
    INTO
        v_phone
    FROM
        dept_quiz
    WHERE
        department_id = p_dep_id;

    p_phone := substr(
        v_phone,
    1,4)
     ||  '-'
     ||  substr(
        v_phone,
    5,8);

END get_dept_phone;



4.
sql:

set serveroutput on
declare
    v_phone varchar2(30);
begin
    GET_DEPT_PHONE(300,v_phone);
    dbms_output.put_line(v_phone);
end;

//////////

ion: 資料庫程式設計 > Open Notes
 quiz08 (by 江杰霖, 06-18 15:56)

Quiz 08 
----------套件  QZ8PKG 

create or replace PACKAGE QZ8PKG AS 
    g_max number :=2;
 
function get_count return number;
procedure reset_count;
function add(p_a number, p_b number) return number;
END QZ8PKG;
-------------------------Body QZ8PKG

create or replace PACKAGE BODY QZ8PKG AS
l_count number := 1;

function get_count return number AS
BEGIN
    RETURN l_count;
END get_count;

procedure reset_count AS
BEGIN
    l_count := 1;
END reset_count;

function add(p_a number, p_b number) return number AS
BEGIN
    if l_count<= g_max then
        l_count := l_count +1;
        return (p_a + p_b);
    else
        raise_application_error(-20000,'exceed the limit.');
    end if;
END add;
END QZ8PKG;

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

set serveroutpu on
declare
    my_exception exception;
    PRAGMA exception_init(my_exception, -20000);
BEGIN
  DBMS_OUTPUT.put_line(QZ8PKG.g_max);
  QZ8PKG.RESET_COUNT;
  FOR i IN 1..3
  LOOP
    DBMS_OUTPUT.put_line('Count: ' || QZ8PKG.GET_COUNT);
    DBMS_OUTPUT.put_line('Value: ' ||QZ8PKG.ADD(i,i));
  END LOOP;
  
  exception 
    when my_exception then
        D
回應