DECLARE
v_ename VARCHAR2(5);
v_sal NUMBER(6,2);
c_tax_rate CONSTANT NUMBER(3,2):=0.03;
v_tax_sal NUMBER(6,2);
BEGIN
SELECT ename,sal INTO v_ename,v_sal
FROM emp WHERE empno=&eno;
v_tax_sal:=v_sal*c_tax_rate;
dbms_output.put_line('雇員名:'||v_ename);
dbms_output.put_line('雇員工資:'||v_sal);
dbms_output.put_line('所得稅:'||v_tax_sal);
END;
v_ename VARCHAR2(5);
v_sal NUMBER(6,2);
c_tax_rate CONSTANT NUMBER(3,2):=0.03;
v_tax_sal NUMBER(6,2);
BEGIN
SELECT ename,sal INTO v_ename,v_sal
FROM emp WHERE empno=&eno;
v_tax_sal:=v_sal*c_tax_rate;
dbms_output.put_line('雇員名:'||v_ename);
dbms_output.put_line('雇員工資:'||v_sal);
dbms_output.put_line('所得稅:'||v_tax_sal);
END;
/
其實這個程序執行之后輸入雇員工號是7654時由于ename是MARTIN超過了5位,所以會報錯,解決辦法是使用%TYPE屬性
使用%TYPE屬性定義變量時,他會按照數據庫列或其他變量來確定新變量類型和長度。示例如下:
DECLARE
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
c_tax_rate CONSTANT NUMBER(3,2):=0.03;
v_tax_sal emp.sal%TYPE;
BEGIN
SELECT ename,sal INTO v_ename,v_sal
FROM emp WHERE empno=&eno;
v_tax_sal:=v_sal*c_tax_rate;
END;