|
特性1: PL/SQL中 select into 一個(gè)變量和直接給這個(gè)個(gè)變量賦值效果不同.
DECLARE
vTmp VARCHAR2(1000);
BEGIN
SELECT to_char(sysdate,'EE','NLS_CALENDAR=''Japanese Imperial''') -- 年號(hào)
INTO vTmp
FROM dual;
END;
結(jié)果: PL/SQL procedure successfully completed
DECLARE
vTmp VARCHAR2(1000);
BEGIN
vTmp := to_char(sysdate,'EE','NLS_CALENDAR=''Japanese Imperial'''); -- vTmp := '平成'
END;
結(jié)果: ORA-06502: PL/SQL: 數(shù)値または値のエラーが発生しました
ORA-06512: 行4
特性2:用一個(gè)record變量直接修改表的記錄.record中字段和表的字段的對(duì)應(yīng)關(guān)系.
create table EMP
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
create table EMP_2
(
EMPNO NUMBER(4) not null,
JOB VARCHAR2(9),
ENAME VARCHAR2(10),
MGR NUMBER(4),
HIREDATE DATE,
COMM NUMBER(7,2),
SAL NUMBER(7,2),
DEPTNO NUMBER(2)
)
DECLARE
rec EMP_2%ROWTYPE;
BEGIN
SELECT *
INTO rec
FROM emp
WHERE empno = 9999 ;
INSERT INTO EMP_2 VALUES rec;
END;
EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 9999 terry1 CLERK 7782 1982/01/23 1300.00 10
EMP_2
EMPNO JOB ENAME MGR HIREDATE COMM SAL DEPTNO
1 9999 terry1 CLERK 7782 1982/01/23 1300.00 10
特性3:取日本年號(hào)
SELECT to_char(SYSDATE, 'EEYY MONTH DAY MM/DD HH24:MI:SS', 'NLS_CALENDAR=''Japanese Imperial''') FROM dual
>>>
平成17 4月 火曜日 04/19 17:34:15 |