CREATE FUNCTION get_sal(no number)
RETURN NUMBER IS
salary NUMBER(6,2);
BEGIN
SELECT sal INTO salary FROM emp WHERE empno=no;
RETURN salary;
END;
DECLARE
CURSOR emp_cursor IS SELECT ename,sal,FROM emp FOR UPDATE;
emp_record emp_cursor%ROWTYPE;
BEJIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
IF emp_record.sal<2000 THEN
UPDATE emp SET sal=sal*1.1 WHERE CURRENT OF emp_cursor;
END IF;
END LOOP;
END;
DECLARE
name VARCHAR(10);
BEGIN
SELECT ename INTO name FROM emp WHERE empno=&no;
dbms_output.put_line(name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.putline('的')
end;
CREATE PROCEDURE insert_dept (no NUMBER,name VARCHAR2）
IS
BEGIN
INSERT INTO DEPT (deptno,dname) VALUES (no,name);
END;
EXECUTE insert_dept(50,'MARKTING')
CREATE PROCEDURE insert_dept(no NUMBER,name varchar2)
IS
BEGIN
INSERT INTO dept (deptno,dname) VALUES(no,name)
END;
\
show errors
CREATE PROCEDURE insert_dept（no NUMBER,name VARCHAR2）
IS
BEGIN
INSERT INTO dept (deptno,dname) VALUES(no,name);
END;
BEGIN
：salary:*get_sal(:empno)
END;
DECLARE
BEGIN
EXCEPTION
END;
set serveroutput on
BEGIN
dbms_output.put_line('Hello world');
END;
DECLARE
v_ename VARCHAR(5)
BEGIN
SELECT ename INTO v_ename FROM emp
WHERE empno=&no;
dbms.output.put_line('雇员名：'||v_name);
END;
DECLARE
v_ename VARCHAR2(5);
BEGIN
SELECT ename INTO v_ename FROM emp
WHERE empno=&no;
dbms_output.putline('雇员名：'||v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('请输入正确的雇员号码');
END;
DECLARE
v_avgsal NUMBER(6,2);
BEGIN
SELECT v_avgsal FROM EMP
WHERE deptno=&no;
dbms_output.putline('平均工资:'||v_avgsal);
END;
<<outer>>
DECLARE
v_deptno NUMBER(2);
V_dname VARCHAR(10);
BEGIN
<<inner>>
BEGIN
SELECT deptno INTO v_deptno FROM emp
where lower(ename)=lower('&name');
END;--<<inner>>
SELECT dname INTO v_name FROM dept
WHERE deptno=v_deptno;
dbms_output.put_line('部门名：'||v_dname);
END;--<<outer>>
CREATE PROCEDURE update_sal(name VARCHAR,newsal NUMBER)
IS
BEGIN
UPDATE emp SET SAL=newsal
WHERE lower(ename)=lower(name);
END;
exec update_sal('scott',2000)
call update_sal('scott',2000)
CREATE FUNCTION annual_income(name VARCHAR2)
RETURN NUMBER IS
annaual_salary NUMBER(7,2);
BEGIN
SELECT sal*12+nvl(comm,0) INTO annual_salary
FROM emp WHERE lower(ename)=lower(name);
RETURN annual_salary;
END;
VAR income NUMBER
CALL annual_income('scott') INTO :income;
PRINT income
CREATE PACKAGE BADY emp_pkg IS
PROCEDURE update_sal(name VARCHAR2,newsal NUMBER)
IS
BEGIN
UPDATE emp SET sal=newsal
WHERE lower(ename)=lower(name);
END;
FUNCTION annual_income(name VARCHAR2)RETURN NUMBER
IS
annual_salary NUMBER(7,2);
BEGIN
SELECT sal*12+nvl(comm,0) INTO annual_salary
FROM emp WHERE lower(ename)=lower(name);
RETURN annual_salary;
END;
END;
CALL emp_pkg.update_sal('scott',1500)
VAR income NUMBER
CALL emp_pkg.annual_income('scott') INTO :income;
PRINT income
CREATE TRIGGER update_cascade
AFTER UPDATE OF deptno ON dept
FOR EACH ROW
BEGIN
UPDATE emp SET deptno=:new.deptno
WHERE deptno=:old.deptno;
END;
v_ename VARCHAR2(10);
v_sal NUMBER(6,2);
v_balance BINARY_FLOAT;
c_tax_rate CONSTANT NUMBER(3,2):=5.5;
v_hiredate DATE
v_vaild BOOLEAN NOT NULL DEFAULT FALSE;
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_tar_sal:=v_sal*c_tax_rate;
dbms_output.put_line('雇员名:'||v_ename);
dbms_output.putline('雇员工资：'||v_sal）;
dbms_output.putline('所得税'||v_tar_sal);
END;
DECLARE
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
c_tax_rate CONSTANT NUMBER(3,2):0.03;
v_tar_sal v_sal%TYPE;
BEGIN
SELECT ename,sal,INTO ename,v_sal;
FROM emp WHERE empno=&eno;
v_tax_sal:=v_sal*c_tax_rate;
dbms_output.putline('雇员名：'||v_ename);
dbms_output.putline('雇员工资:'||v_sal);
dbms_output.putline('所得税：'||v_tar_sal);
END;
DECLARE
TYPE emp_record_type IS RCORD(
name emp.ename%TYPE,
salary emp.sal%TYPE,
title emp.job%TYPE);
emp_record emp_record_type;
BEGIN
SELECT ename,sal,job INTO emp_record
FROM emp WHERE empno=7788;
dbms_output.put_line('雇员名'||emp_record.name);
END;
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
BEGIN
SELECT ename INTO ename_table(-1) FROM emp
WHERE empno=7788;
dbms_output.put_line('雇员名：'||ename_table(-1));
END;
CREATE OR REPLACE type emp_type AS OBJECT(
name VARCHAR2(10),salary NUMBER(6,2),hiredate DATE);
/
CREATE OR REPLACE TYPE emp_array IS TABLE OF emp_type;
/
CREATE TABLE department (
deptno NUMBER(2),dname VARCHAR2(10),employee emp_array
)
NESTED TABLE employee STORT AS employee;
CREATE TYPE article_type AS OBJECT(
title VARCHAR(30),pubdate DATE
);
/
CREATE TYPE article_array IS VARRAY(20) OF aticle_type;
/
CREATE TABLE author(
id NUMBER（6），name VARCHAR2(10),article article_array);
DECLARE
TYPE c1 IS REF CURSOR;
emp_cursor c1;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BRGIN
OPEN emp_cursor FOR
SELECT ename,sal FROM emp WHERE deptno =10;
LOOP
FETCH emp_cursor INTO v_ename,v_sal;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.putline(v_ename);
END LOOP;
CLOSE emp_cursor;
END;
/
CLARE
KING
MILLER
CREATE OR REPLACE TYPE home_type AS OBJECT(
street VARCHAR2(50), city VARCHAR2（20），
state VARCHAR2(20),zipcode VARCHAR2(6),
ower VARCHAR(10)
);
/
CREATE TABLE homes OF home_type;
INSERT INTO homes VALUES('呼伦北路12号'，'呼和浩特','内蒙','010010','马鸣');
INSERT INTO homes VALUES('呼伦北路13号'，'呼和浩特','内蒙','010010','秦斌');
COMMIT;
CREATE TABLE person (
id NUMBER(6) PRIMARY KEY,
name VARCHAR2(10), addr REF home_type
);
INSERT INTO person SELECT 1，'马鸣',ref(p)
FROM homes p WHERE P.owner='马鸣';
INSERT INTO person SELECT 1，'马武',ref(p)
FROM homes p WHERE P.owner='马鸣';
INSERT INTO person SELECT 1，'王敏',ref(p)
FROM homes p WHERE P.owner='马鸣';
COMMIT;
VAR name VARCHAR2(10）
BEGIN
SELECT ename INTO :name FROM emp
WHERE empno=7788;
END;
/
PRINT name
.CREATE CHAR name LENGTH 10
BEGIN
SELECT ename INTO :name FROM emp
WHERE empno=7788;
END;
TEXT_IO.PUT_LINE(:name）；
char name[10];
EXEC SQL EXECUTE
BEGIN
SELECT ename INTO :name FROM emp
WHERE empno=7788;
END;
END-EXEC;
printf("雇员名：%s/n",name);
DECLARE
v_sal NUMBER(6,2);
BEGIN
v_sal:=1000;
END;
/
SELECT sal INTO salary FROM emp
WHERE empno = emp_id;
DECLARE
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp
WHERE empno=&no;
dbms_output.put_line(v_sal);
END;
BEGIN
IF x=0 then
y=1;
END IF;
END;
INSERT INTO dept VALUES (50,'TRAIN','BOSTEN');
INSERT INTO emp (empno,ename,job,hiredate)
VALUES (1234,'JONH','JLERK','01-3月 -86'）;
INSERT INTO emp (empno,ename,job,hiredate)
VALUES(1234,'JOHN','CLERK',to_date('1983-10-20',"YYYY-MM-DD"));
INSERT INTO dept VALUES(60,'MARKET',DEFAULT);
VALUES(1356,'MARY',DEFAULT);
SELECT * FROM dept WHERE deptno=60;
INSERT INTO employee (empno,ename,sal,deptno)
SELECT empno,ename,sal,deptno FROM emp
WHERE deptno=20;
INSERT INTO /*+APPEND*/ INTO employee （empno,ename,sal,deptno)
SELECT empno,ename,sal,deptno FROM emp
WHERE deptno=20;
INSERT ALL
WHEN deptno=10 THEN INTO dept10
WHEN deptno=20 THEN INTO dept20
WHEN deptno=30 THEN INTO dept30
WHEN job='CLERK' THEN INTO clerk
ELSE INTO other
SELECT * FROM emp;
INSERT FIRST
WHEN deptno=10 THEN INTO dept10
WHEN deptno=20 THEN INTO dept20
WHEN deptno=30 THEN INTO dept30
WHEN job='CLERK' THEN INTO clerk
ELSE INTO other
SELECT * FROM emp;
UPDATE emp SET sal=2460 WHERE ename='SCOTT';
UPDATE emp SET sal=sal*1.1,comm=sal*0.1
WHERE deptno =20;
UPDATE emp SET hiredate=TO_DATE('1984/01/01','YYYY/MM/DD')
WHERE empno=7788;
SELECT job FROM emp WHERE ename='SCOTT';
UPDATE emp SET job=DEFAULT WHERE ename='SCOTT';
SELECT job FROM emp WHERE ename='SCOTT';
UPDATE emp SET (job,sal,comm)=(
SELECT job,sal,comm FROM emp WHERE ename='SMITH')
WHERE ename='SCOTT';
UPDATE employee SET deptno=
(SELECT deptno FROM emp WHERE empno=7788) WHERE job=(SELECT emp FROM emp WHERE empno=7788);
DELETE FROM emp WHERE ename ='SMITH';
DELETE FROM emp;
TRUNCATE FROM emp;
DELETE FROM emp WHERE deptno=
(SELECT deptno FROM dept WHERE dname='SALES');
ALTER TABLE emp ADD remark VARCHAR2(100);
savepoint a;
或
exec dbms_transation.savepoint('a')
rollback to a;
或
exec dbms_transaction.rollback_savepoint('a')
rollback
或
exec dbms_transaction.rollback
SET TRANSACTION READ ONLY;
或
exec dbms_transaction.read_only
SET TRANSACTION ISOLOCATION LEVEL SERIALIZABLE;
SELECT max(sal),min(sal) FROM emp;
SELECT count(*） FROM EMP;
SELECT count(comm) FROM emp;
SELECT variance（sal),stddev(sal) FROM emp;
SELECT count(distinct deptno) AS distinct_dept FROM emp;
SELECT deptno,avg(sal),max(sal) FROM emp
GROUP BY deptno;
SELECT deptno,job,avg(sal),max(sal) FROM emp
GROUP BY deptno,job;
SELECT deptno,avg(sal),max(sal) FROM emp
GROUP BY deptno
HAVING avg(sal)<2000;
SELECT deptno,avg(sal),max(sl FROM emp
GROUP BY deptno ORDER BY avg(sal);
SELECT deptno,job,avg(sal) FROM emp
GROUP BY deptno;
SELECT deptno,AVG(sal) FROM emp
WHERE SUM(sal)>1000 GROUP BY deptno;
set pagsize 30
SELECT deptno,job,avg(sal) FROM emp
GROUP BY ROLLUP（deptno,job);
SELECT deptno,job,avg(sal) FROM emp
GROUP BY CUBE(deptno,job);
SELECT deptno,job,avg(sal),grouping(deptno),grouping(job)
FROM emp GROUP BY cube (deptno,job);
SELECT deptno,avg(sal) FROM emp GROUP BY deptno;
SELECT job,avg(sal) FROM emp GROUP BY job；
SELECT deptno,job,avg（sal) FROM emp GROUP BY GROUPING SETS(deptno,job);
SELECT deptno,dname,ename FROM dept.emp
WHERE dept.deptno=emp.deptno;
×
SELECT dept.dname,emp.name FROM dept,emp
WHERE dept.dname='SALES';
√
SELECT e.ename,e.sal,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;
SELECT d.dname,e.ename,e.sal FROM emp e,dept d WHERE e.deptno=d.deptno AND d.depetno=10;
SELECT e.ename,a.sal,b.grade FROM emp a,salgrade b
WHERE a.sal BETWEEN b.losal AND b.hisal;
SELECT manger,ename FROM emp manger,emp worker
WHERE manger.empno=work.mgr
AND work.ename='BLARK';
SELECT a.dname,b.ename,FROM dept a,emp b
WHERE a.deptno=b.deptno AND a.deptno=10;
SELECT a.dname,b.ename FROM dept a INNER JOIN emp b
ON a.deptno=b.deptno AND a.deptno=10;
SELECT dname,ename FROM dept NATURAL JOIN emp;
SELECT a.dname,b.ename FROM dept s LEFT JOIN emp b
ON a.deptno=b.deptno AND a.deptno=10;
SELECT a.dname,b.ename FROM dept a RIGHT JOIN emp b
ON a.deptno=b.deptno AND a.deptno=10;
SELECT a.dname,b.ename FROM dept a FULL JOIN empb
ON a.deptno=b.deptno AND a.deptno=10;
SELECT a.name,b.ename FROM dept a,emp b
WHERE a.deptno(+) AND b.deptno(+)=10;
SELECT a.dname,bname FROM dept a,emp b
WHERE a.deptno(+)=b.deptno AND a.deptno(+)=10
ORDER BY a.dname;
SELECT ename,sal,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename ='SCOTT');
SELECT ename,job,sal,deptno FROM emp WHERE job IN
(SELECT distinct job FROM emp WHERE deptno=10);
SELECT ename,sal,deptno FROM emp WHERE sal>all
(SELECT sal FROM emp WHERE deptno=30);
SELECT ename,sal,deptno FROM emp WHERE sal>ANY
(SELECT sal FROM emp WHERE deptno=30);
SELECT ename,job,sal,deptno FROM emp WHERE (deptno,job)=
(SELECT deptno,job FROM emp WHERE ename='SMITH');
SELECT ename,sal,comm,deptno FROM emp
WHERE (sal,nvl(comm,-1) IN (SELECT sal,nvl(comm,-1)
FROM emp WHERE deptno=30);
SELECT ename,sal,comm,deptno FROM emp
WHERE sal IN (SELECT sal FROM emp WHERE deptno=30)
AND nvl(comm,-1) IN (SELECT nvl(comm,-1) FROM emp WHERE deptno=30);
SELECT ename,job,sal,deptno FROM emp WHERE EXISTS
(SELECT 1 FROM dept WHERE dept.deptno=emp.deptno
AND dept.loc='NEW YORK');
SELECT ename,job,sal FROM emp,
(SELECT deptno,avg(sal) avgsal FROM
GROUP BY deptno) dept
WHERE emp.deptno AND sal>dept.avgsal;
INSERT INTO employee (id,name,title,salary)
SELECT empno,ename,job,sal FROM emp;
UPDATE emp SET (sal,comm)=(SELECT sal,comm FROM emp WHERE ename='SMITH')
WHERE job=(SELECT job FROM emp WHERE ename = 'SMITH');
DELETE FROM emp WHERE deptno=
(SELECT deptno FROM dept WHERE dname='SALES');
CREATE TABLE new_emp(id,name,sal,job,deptno) AS
SELECT empno,ename,sal,job,deptno FROM emp;
CREATE OR REPLACE VIEW dept_10 AS
SELECT emp,ename,job,sal,deptno FROM emp
WHERE deptno=10 ORDER BY empno;
CREATE MATERILIZED VIEW summary_emp AS
SELECT deptno,job,avg(sal) avgsal,sum(sal) sumsal
FROM emp GROUP BY cube(deptno，job);
SELECT ename,sal,job FROM emp WHERE sal>2500
UNION
SELECT ename,sal,job FROM emp WHERE job='MANGER';
SELECT ename,sal,job FROM emp WHERE sal>2500
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANGER';
SELECT ename,sal,job FROM emp WHERE sal>2500
INTERSECT
SELECT ename,sal,job FROM emp WHERE job='MANGER';
SELECT ename,sal,job FROM emp WHERE sal>2500
MINUS
SELECT ename,sal,job FROM emp WHERE job='MANGER';
col ename formate a15
col job formate a15
SELECT LPAD(' ',3*(LEVEL-1))||ename ename,
LPAD(' ',3*(LEVEL-1))||job job FROM emp
WHERE job<>'CLERK' START WITH mgr IS NULL
CONNECT BY mgr=PRIOR
SELECT ename,sal.CASE WHEN sal>3000 THEN 3
WHEN sal>2000 THEN 2 ELSE 1 END grade
FROM emp WHERE deptno=10;
SELECT ename,sal FROM emp WHERE ename='CLARK';
SELECT ename,sal FROM emp AS OF TIMESTAMP to_timestamp(
'2003-05-18 19:59:00','YYYY-MM-DD HH24:MI:ss')
WHERE ename='CLERK';
exec dbms_flashback.enable_at_system_change_number(717402)
SELECT sal FROM emp WHERE ename='SCOTT';
exec dbms_flashback.disable
SELECT sal FROM emp WHERE ename='SCOTT';
SELECT dname,SUM(sal) AS dept_total FROM emp,dept
WHERE emp.deptno-dept.deptno GROUP BY dname
HAVING SUM(sal)>
(SELECT SUM(sal)*1/3 FROM emp,dept
WHERE emp.deptno=dept.deptno
);
WITH summary AS (
SELECT dname,SUM(sal) AS dept_total FROM emp,dept
WHERE emp.deptno=dept.deptno GROUP BY dname)
SELECT dname,dept_total FROM summary WHERE dept_total>
(SELECT SUM(dept_total)*1/3 FROM summary);
第五章 SQL 函数
DECLARE
v_abs NUMBER(6,2);
BEGIN
v_abs:=abs(&no);
dbms_output.putline('绝对值:'||v_abs);
END;
/
SELECT acos(.3),acos(-.3)FROM dual;
DECLARE
v_asin NUMBER(6,2);
BEGIN
v_asin:=asin(0,8);
dbms_output.put_line('0.8的反正弦值：'||v_asin);
END;
/
SELECT atan(10.3),atan(-20,3) FROM dual;
DECLARE
v_atan2 NUMBER(6,2);
BEGIN
v_atan2:=atan2(19,3);
dbms_output.putline('19/3的反正切值：'||v_atan2);
END;
/
SELECT ceil(15),ceil(15.1) FROM dual;
DECLARE
v_cos NUMBER(6,2);
BEGIN
v_cos:=(0.5);
dbms_output.put_line('0.5的余弦值：'||v_cos);
END;
SELECT COSH(0) "0的双曲余弦值" FROM DUAL;
DECLARE
v_exp NUMBER(6,2);
BEGIN
v_exp:=exp(4);
dbms_output.put_line('e的4次幂：'||v_exp);
END;
/
SELECT floor(15),floor(15.1) FROM dual；
DECLARE
v_ln NUMBER(6,2);
BEGIN
v_ln:=ln(4);
dbms_output.out_line('4的自然对数:'||v_ln);
END;
SELECT log(2,8),log(10,100) FROM dual;
DECLARE
v_mod NUMBER(6,2);
BEGIN
v_mod:=mod(10,3);
dbms_output.putline('10除3的余数'||v_mod）;
END;
/
SELECT power(-2,3),power(2,-1) FROM dual;
DECLARE
v_round NUMBER(6,2);
BEGIN
v_round：round(&no,1);
dbms_output.put_line('四舍五入到小数点最后一位：'||v_round);
END;
/
SELECT sign(-10),sign(0),sign(20) FROM dual;
DECLARE
v_sin NUMBER(6,2);
BEGIN
v_sin:=sin(0.3);
dbms_output.put_line('0.3的正弦值:'||v_sin);
END;
SELECT SINH(.5) FROM dual;
DECLARE
v_aqrt:=sqrt(10);
dbms_output.put_line('10的平方根',||v_sqrt);
END;
SELECT TAN(45 * 3.14159265359/180) FROM dual;
DECLARE
v_tanh NUMBER(6，2);
BEGIN
v_tanh:=tanh(10);
dbms_output.put_line('10的双曲正切值'||v_tanh);
END;
SELECT trunc(45.926),trunc(45.9261),trunc(45.926,-1)
FROM dual;
SELECT ascii('a') "a",ascii('A') "A" FROM dual;
DECLARE
v_chr VARCHAR2（10）
BEGIN
v_chr:=chr(56);
dbms_output.put_line('ASCII码为56字符：'||v_chr);
END;
SELECT concat('GOOD','MORNING') FROM dual;
DECLARE
v_initcap VARCHAR2(10);
BEGIN
v_initcap:=initcap("my word");
dbms_output.put_line('首字符大写：'||v_initcap);
END;
SELECT instr('morning','n') FROM dual;
DECLARE
v_len INT;
BEGIN
v_len:=length("my word");
dbms_output.putline('字符串长度：'v_len);
END;
/
SELECT lower('SQL introduction') FROM dual;
DECLARE
v_lpad VARCHAR2(10);
BEGIN
v_lpad:=lpad('aaaa',10,'*');
dbms_output.putline('在字符串左端添加字符*：'||v_lpad);
END;
SELECT ltrim('morning','m'),ltrim('morning','or')
FROM dual;
DECLARE
v_nls_initcap NCHAR(10);
BEGIN
v_nls_initcap:=nls_initcap(n 'my word');
dbms_output.put_line('首字符大写：'||v_nls_initcap);
END;
SELECT nls_lower(n'SQL') FROM dual;
SELECT * FROM test
ORDER BY NLSSORT(name,'NLS_SORT=XDanish');
DECLARE
v_upper VARCHAR2(10);
BEGIN
v_upper：=nls_upper('my word','nls_sort=XGERMAN');
dbms_output.putline('字符串大写：'||v_upper);
END;
SELECT REGEXP_REPLACE(country_name,'(.)','\1') "REGEXP_REPLACE"
FROM countries;
SELECT REGEXP_SUBSTR('http://www.oracle.com/products',
'http://([[:alnum:]]+\.?') "REGEXP_SUBSTR" FROM dual;
SELECT replace('缺省值为10','缺省','默认') FROM dual;
DECLARE
v_rpad VARCHAR2(10);
BEGIN
v_rpad:=rpad('aaaa',10,'*')
dbma_output.put_line('在右端添加字符：||v_rpad);
END;
/
SELECT rtrim('morning','ing') FROM dual;
SELECT soundex('ship'),soundex('sheep') FROM dual;
DECLARE
v_subs VARCHAR2(10);
BEGIN
v_subs:=substr('morning',1,3);
dbms_output.put_line('字符串的子串'||v_subs);
END;
SELECT TRANSLATE（'2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXX') "TRANS"
FROM dual;
DECLARE
v_source VARCHAR2(20):='ABCDGHJHJAB');
v_trim VARCHAR2(20);
BEGIN
v_trim:=trim('A' FROM v_source);
dbms_output.put_line(v_trim);
END;
SELECT upper('sql') FROM dual;
DECLARE
v_date DATA;
BEGIN
v_date:=add_months(sysdate,14);
dbms_output.put_line('当前日期前14个月对应的日期：'||v_date);
END;
ALTER SESSION SET TIME_ZONE='-5:0';
ALTER SESSION SET nls_date_formate='YYYY-MM-DD HH24:MI';
SELECT current_date FROM dual;
SELECT current_timetamp FROM dual;
DECLARE
v_zone VARCHAR(10);
BEGIN
v_zone:=dbtimezone;
dbms_output.put_line('当数据库时区：'||v_zone);
END;
SELECT extract (YEAR FROM sysdate) year FROM dual;
DECLARE
v_tzv VARCHAR2(100);
BEGIN
v_tzv:=from_tz(TIMESTAMP '2003-03-28 08:00:00','3:00');
dbms_output.out_line(v_tzv);
SELECT last_day(sysdate) FROM dual;
DECLARE
v_ts VARCHAR2(100);
BEGIN
v_ts:=localtimestamp;
dbms_output.put_line('当前日期时间:'||v_ts);
END;
SELECT months_between(sysdate,'31-8月-1998）
FROM dual;
DECLARE
v_time DATE,
BEGIN
dbms_session.set_nls('nls_date_formate',
'''YYYY-MM-DD HH24：MI:SS''');
v_time:=new_time(to_date('2003-11-10 12:10:00',
'YYYY-MM-DD HH24：MI:SS'),'BST','EST');
dbms_output.put_line('当前日期时间：'||v_time);
END;
SELECT next_day(sysdate,'星期一') FROM dual;
DECLARE
v_date VARCHAR2(100);
BEGIN
v_date：=numtodsinterval(10000,'MINUTE');
dbms_output.put_line('10000分钟对应的时间：'||v_date）；
END;
SELECT numtoyminterval(100,'MONTH') AS year_month FROM dual;
DECLARE
v_date DATE
BEGIN
v_date:=ROUND(SYSDATE,'MONTH');
dbms_output.put_line(SYSDATE||'四舍五入结果：'||v_date);
END;
SELECT sessiontimezone FROM dual;
DECLARE
v_timestamp TIMESTAMP
BEGIN
v_timestamp:=SYS_EXTRACR_UTC(SYSTIMESTAMP);
dbms_output.put_line('格林威治时间：'||v_timestamp);
END;
SELECT sysdate FROM dual;
DECLARE
v_timestamp VARCHAR2(100)
BEGIN
v_timestamp:=SYSTIMSTAMP;
dbms_output.put_line('当前系统时区：'||v_timestamp);
END;
SELECT to_dsinterval('58:10:10') FROM dual;
DECLARE
v_timestamp TIMESTAMP;
BEGIN
v_timestamp:=TO_TIMESTAMP('01-1月-03');
dbms_output.putline('日期时间值：'||v_timestamp);
END;
SELECT to_timestamp_tz('2003-01-01','YYYY-MM-DD')
FROM dual;
DECLARE
v_date DATE;
BEGIN
v_date:=SYSDATE+TO_YMINTERVAL('01-01');
dbms_output.put_line('当前日期后的一年一个月：'||v_date);
END;
SELECT trunc(sysdate,'MONTH') FROM dual;
SELECT tz_offset('EST') FROM dual;
SELECT ASCIISTR('中国') FROM dual;
SELECT bin_to_num(1,0,1,1,1) FROM dual;
DECLARE
v_cast VARCHAR2(20);
BEGIN
v_cast:=cast(SYSDATE AS VARCHAR2);
dbms_output.put_line('转换结果'||v_case);
END;
SELECT CHAROROWID('AAAFdlAAFAAAABSAA/') FROM dual;
SELECT COMPOSE ('o'||UNISTR('\0308')) FROM dual;
DECLARE
v_convert VARCHAR2(20);
BEGIN
v_convert:=CONVERT('中国','US7AASCII','WE8ISO8859P1');
dbms_output.put_line('转换结果：'||v_convert);
END;
SELECT DECOMPSE ('chateaux') FROM dual;
DECLARE
v_raw RAW(20);
BEGIN
v_raw:=HEXTORAW('AB57FAC');
dbms_output.put_line('转换结果：'||v_char);
END;
SELECT rawtonhex('7D') FROM dual；
DECLARE
v_char VARCHAR2(20);
BEGIN
V_CHAR:=ROWIDTOCHAR('AAAFfIAAFAAAABSAAb');
dbms_output.put_line('转换结果'||v_char);
END;
SELECT rowidtonchar('AAAFfIAAFAAAABSAAb') FROM dual;
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM emp WHERE empno=7788;
SELECT TIMESTAMP_TO_CSN(order_date) FROM orders
WHERE order_id=5000;
DECLARE
v_char:=TO_CHAR(n'中华人民共和国');
dbms_output.put_line('转换结果：'||v_char);
END;
SELECT to_char(sysdate,'YYYY-MM-DD') FROM dual；
DECLARE
v_char VARCHAR2(20);
BEGIN
v_char:=TO_CHAR(-10000，'l99g999d99MI');
dbms_output.put_line('转换结果：'||v_char);
END;
扫一扫
1085
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
