|
特性1: PL/SQL中 select into 一个变量和直接给这个个变量赋值效果不同.
DECLARE
vTmp VARCHAR2(1000);
BEGIN
SELECT to_char(sysdate,'EE','NLS_CALENDAR=''Japanese Imperial''') -- 年号
INTO vTmp
FROM dual;
END;
结果: PL/SQL procedure successfully completed
DECLARE
vTmp VARCHAR2(1000);
BEGIN
vTmp := to_char(sysdate,'EE','NLS_CALENDAR=''Japanese Imperial'''); -- vTmp := '平成'
END;
结果: ORA-06502: PL/SQL: 数値または値のエラーが発生しました
ORA-06512: 行4
特性2:用一个record变量直接修改表的记录.record中字段和表的字段的对应关系.
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:取日本年号
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 |