首页 / ORACLE / ORACLE查询语句
ORACLE查询语句
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了ORACLE查询语句,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含16819字,纯文字阅读大概需要25分钟。
内容图文
CREATE TABLE FAMILYINFO( FNO NUMBER CONSTRAINT FC001 PRIMARY KEY,--把字段fno约束为主键 FNAME VARCHAR2(20) CONSTRAINT FC002 NOT NULL,--把字段FNAME的约束条件设为“不能为空” SEX VARCHAR2(20) DEFAULT ‘MALE‘CONSTRAINT FC003 CHECK(SEX IN(‘MALE‘,‘FEMALE‘)),--把字段’SEX‘的约束条件设为’性别默认条件下为’MALE‘,如果自己填写只能填写’MALE‘或’FEMALE‘ FAGE NUMBER, REL VARCHAR2(20),--家庭成员关系字段 HOBITNO NUMBER CONSTRAINT FC004 REFERENCES HOBITINFO(HNO) ON DELETE SET NULL --约束条件设为,表’HOBITINFO‘中字段’HNo‘的外键,删除引用表中字段时,设为空值 ); --建兴趣表HOBITINF CREATE TABLE HOBITINFO( HNO NUMBER CONSTRAINT HC001 PRIMARY KEY, --将‘ HNO’设为主键 HNAME VARCHAR2(20) CONSTRAINT HC002 NOT NULL--将‘HNAME’约束条件设为‘不能为空’ ); --删除表HOBITINFO DROP TABLE HOBITINFO; --添加信息 INSERT INTO HOBITINFO VALUES(1,‘MOVIE‘); INSERT INTO HOBITINFO VALUES(2,‘CG‘); INSERT INTO HOBITINFO VALUES(3,‘TW‘); SELECT * FROM HOBITINFO; DELETE FROM HOBITINFO WHERE HNO=3; SELECT * FROM FAMILYINFO; TRUNCATE TABLE FAMILYINFO; DROP TABLE FAMILYINFO; INSERT INTO FAMILYINFO VALUES(1,‘PHL‘,‘NAN‘,27,‘FATHER‘); INSERT INTO FAMILYINFO VALUES(2,‘HYQ‘,‘NAN‘,48,‘MATHER‘); INSERT INTO FAMILYINFO VALUES(3,‘PQ‘,‘NAN‘,27,‘BROTHER‘); INSERT INTO FAMILYINFO VALUES(4,‘PC‘,‘NAN‘,20,‘MIN‘); --DDL ALTER TABLE (修改表) ALTER TABLE FAMILYINFO RENAME TO PCFAMILY;--将FAMILYINFO的表名重命名为PCFAMILY ALTER TABLE PCFAMILY ADD(--添加列即字段及其属性 HEIGH VARCHAR2(5), SX VARCHAR2(10) CONSTRAINT PC002 NOT NULL--字段SX创建“不能为空”的约束条件 ); ALTER TABLE PCFAMILY MODIFY(--修改字段的属性 HEIGH CHAR(20) ); ALTER TABLE PCFAMILY DROP COLUMN HEIGH;--删除字段HEIGH ALTER TABLE PCFAMILY RENAME COLUMN SX TO SX1;--修改约束字段名 ALTER TABLE PCFAMILY DROP COLUMN SX1;--删除约束字段 --DDL 之 DROP TABLE(删除表) DROP TABLE HOBITINFO CASCADE CONSTRAINTS; --DDL 之 TRUNCATE TABLE 清空表记录 TRUNCATE TABLE PCFAMILY; --DML 之 INSERT(插入,添加记录) INSERT INTO PCFAMILY(FNO,SEX,FNAME,FAGE) VALUES(4,‘NANE‘,‘LIU‘||‘DAYE‘,45+20);--如果指定插入字段顺序,则values里的信息就要对照前面指定的字段填写 INSERT INTO PCFAMILY VALUES(); --学生表 CREATE TABLE STUDENT( SNO NUMBER CONSTRAINT SC001 PRIMARY KEY, SNAME VARCHAR2(10) CONSTRAINT SC002 NOT NULL, SSEX VARCHAR(10) CONSTRAINT S003 CHECK(SSEX IN (‘M‘,‘F‘)) ); --老师表 CREATE TABLE TEACHER( TNO NUMBER CONSTRAINT TC001 PRIMARY KEY, TNAME VARCHAR2(10) CONSTRAINT TC002 NOT NULL ); --课程表 CREATE TABLE COURSE( CNO NUMBER CONSTRAINT CC001 PRIMARY KEY, CNAME VARCHAR2(20) CONSTRAINT CC002 NOT NULL, TNO NUMBER CONSTRAINT CC003 REFERENCES TEACHER(TNO) ); --成绩表 CREATE TABLE SCORE( SNO NUMBER CONSTRAINT SSC001 REFERENCES STUDENT(SNO), CNO NUMBER CONSTRAINT SSC002 REFERENCES COURSE(CNO), GRADE VARCHAR2(20), CONSTRAINT SSC003 PRIMARY KEY(SNO,CNO) ); --查询表是否建立成功已经存在 SELECT * FROM STUDENT; SELECT * FROM TEACHER; SELECT * FROM COURSE; SELECT * FROM SCORE; --删除表 DROP TABLE STUDENT; DROP TABLE TEACHER; DROP TABLE COURSE; DROP TABLE SCORE; --向表里添加信息 INSERT INTO STUDENT VALUES(1,‘aaaa‘,‘M‘); INSERT INTO STUDENT VALUES(2,‘bbbb‘,‘M‘); INSERT INTO STUDENT VALUES(3,‘cccc‘,‘F‘); INSERT INTO TEACHER VALUES(1,‘AAAA‘); INSERT INTO TEACHER VALUES(2,‘BBBB‘); INSERT INTO TEACHER VALUES(3,‘CCCC‘); INSERT INTO COURSE VALUES(1,‘yw‘,2); INSERT INTO COURSE VALUES(2,‘sx‘,3); INSERT INTO COURSE VALUES(3,‘yy‘,2); INSERT INTO SCORE VALUES(1,2,89); INSERT INTO SCORE VALUES(2,3,120); INSERT INTO SCORE VALUES(3,1,110); --DML 之UPDATE UPDATE SCORE SET GRADE=GRADE-1; UPDATE SCORE SET GRADE=GRADE+1 WHERE SNO=2; UPDATE SCORE SET GRADE=GRADE+1 WHERE SNO=2 AND CNO=3; -------------------------------------------------------------------------------------------- -- DQL 之 SELECT SELECT CHR(107) HAHA FROM DUAL; SELECT CURRENT_DATE FROM DUAL; SELECT TRUNC(ABS(MONTHS_BETWEEN(DATE‘1998-08-08‘,CURRENT_DATE)/12)) AGE FROM DUAL; SELECT SNO AS 学号, SNAME 学生姓名 FROM STUDENT; SELECT ALL SSEX FROM STUDENT; SELECT DISTINCT SSEX FROM STUDENT; SELECT UNIQUE SSEX FROM STUDENT; SELECT * FROM SCORE WHERE GRADE < 60; SELECT ROWID,ROWNUM,STUDENT.* FROM STUDENT; SELECT * FROM STUDENT WHERE ROWNUM = 1; SELECT * FROM STUDENT WHERE ROWNUM <= 2; SELECT * FROM (SELECT ROWNUM AS RN,STUDENT.* FROM STUDENT) WHERE RN = 2; SELECT CNO,AVG(GRADE) FROM SCORE GROUP BY CNO; SELECT CNO,AVG(GRADE) FROM SCORE GROUP BY CNO HAVING AVG(GRADE) > 80; SELECT SSEX,COUNT(SNO) FROM STUDENT GROUP BY SSEX; SELECT SNO FROM SCORE WHERE CNO = ‘C001‘ AND GRADE = (SELECT MAX(GRADE) FROM SCORE WHERE CNO = ‘C001‘); SELECT SNO FROM SCORE WHERE CNO = ‘C001‘ AND GRADE >= ALL(SELECT GRADE FROM SCORE WHERE CNO = ‘C001‘); SELECT * FROM SCORE ORDER BY CNO ASC,GRADE DESC; SELECT * FROM STUDENT,SCORE WHERE STUDENT.SNO = SCORE.SNO; SELECT SNAME FROM STUDENT,SCORE WHERE STUDENT.SNO = SCORE.SNO AND CNO = ‘C001‘ AND GRADE = (SELECT MAX(GRADE) FROM SCORE WHERE CNO = ‘C001‘); SELECT * FROM STUDENT INNER JOIN SCORE ON STUDENT.SNO = SCORE.SNO; SELECT * FROM STUDENT INNER JOIN SCORE USING(SNO); SELECT * FROM STUDENT CROSS JOIN SCORE WHERE STUDENT.SNO = SCORE.SNO; SELECT * FROM STUDENT NATURAL INNER JOIN SCORE; SELECT * FROM STUDENT LEFT OUTER JOIN SCORE ON STUDENT.SNO = SCORE.SNO; SELECT * FROM STUDENT RIGHT OUTER JOIN SCORE USING(SNO); SELECT * FROM STUDENT FULL OUTER JOIN SCORE USING(SNO); -- 查询考试不及格的学生姓名和挂科的科目名称 SELECT SNAME,CNAME FROM (SCORE LEFT JOIN STUDENT USING(SNO)) LEFT JOIN COURSE USING(CNO) WHERE GRADE < 60; -- 查询所有java考试成绩高于平均分的学生的姓名和成绩 SELECT SNAME,GRADE FROM (SCORE LEFT JOIN STUDENT USING(SNO)) LEFT JOIN COURSE USING(CNO) WHERE CNAME = ‘JAVA‘ AND GRADE > ( SELECT AVG(GRADE) FROM SCORE LEFT JOIN COURSE USING(CNO) WHERE CNAME = ‘JAVA‘ ); select * from (SELECT * from test RIGHT JOIN t_grade using(ID) ) a where `数学` > 90 -- 查询同时参加了C001和C002科目考试的学生编号 SELECT SNO FROM (SELECT SNO,CNO FROM SCORE WHERE CNO = ‘C001‘) INNER JOIN (SELECT SNO,CNO FROM SCORE WHERE CNO = ‘C002‘) USING(SNO); -- 查询lili同学参加的所有科目考试中成绩最高的那门科目的授课老师名字 SELECT TNAME,CNAME FROM TEACHER LEFT JOIN COURSE USING(TNO) WHERE CNO IN ( SELECT CNO FROM SCORE LEFT JOIN STUDENT USING(SNO) WHERE SNAME = ‘LILI‘ AND GRADE = ( SELECT MAX(GRADE) FROM SCORE LEFT JOIN STUDENT USING(SNO) WHERE SNAME = ‘LILI‘ ) ); -- 查询库存目前还有多少 CREATE TABLE CLOTHER_STORE( CTYPE VARCHAR2(10), STORENUM NUMBER ); CREATE TABLE CLOTHER_SALE( CTYPE VARCHAR2(10), SALENUM NUMBER ); INSERT INTO CLOTHER_STORE VALUES(‘T-SHIRT‘,600); INSERT INTO CLOTHER_STORE VALUES(‘COAT‘,700); INSERT INTO CLOTHER_SALE VALUES(‘T-SHIRT‘,140); INSERT INTO CLOTHER_SALE VALUES(‘T-SHIRT‘,165); INSERT INTO CLOTHER_SALE VALUES(‘COAT‘,90); INSERT INTO CLOTHER_SALE VALUES(‘COAT‘,78); SELECT CTYPE,STORENUM-SNUM AS KCSY FROM CLOTHER_STORE LEFT JOIN (SELECT CTYPE,SUM(SALENUM) SNUM FROM CLOTHER_SALE GROUP BY CTYPE) USING(CTYPE); -- 切换到scott用户 SELECT * FROM EMP; SELECT * FROM DEPT; --1. 列出至少有一个员工的所有部门。 SELECT DISTINCT DEPTNO,DNAME FROM EMP LEFT JOIN DEPT USING(DEPTNO); --2. 列出薪金比"SMITH"多的所有员工。 SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = ‘SMITH‘); --3. 列出所有员工的姓名及其直接上级的姓名。 SELECT E.ENAME 员工姓名,B.ENAME 上级姓名 FROM EMP E LEFT JOIN EMP B ON E.MGR = B.EMPNO; --4. 列出受雇日期早于其直接上级的所有员工。 SELECT E.ENAME 员工姓名,E.HIREDATE 员工受雇日期,B.ENAME 上级姓名,B.HIREDATE 上级受雇日期 FROM EMP E LEFT JOIN EMP B ON E.MGR = B.EMPNO WHERE E.HIREDATE < B.HIREDATE; --5. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。 SELECT DNAME,EMP.* FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO; --6. 列出所有"CLERK"(办事员)的姓名及其部门名称。 SELECT ENAME,DNAME FROM EMP LEFT JOIN DEPT USING(DEPTNO) WHERE JOB = ‘CLERK‘; --7. 列出最低薪金大于1500的各种工作。 SELECT JOB FROM EMP GROUP BY JOB HAVING MIN(SAL) > 1500; --8. 列出在部门"SALES"(销售部)工作的员工的姓名,假定不知道销售部的部门编号。 SELECT ENAME FROM EMP LEFT JOIN DEPT USING(DEPTNO) WHERE DNAME = ‘SALES‘; --9. 列出薪金高于公司平均薪金的所有员工。 SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP); --10.列出与"SCOTT"从事相同工作的所有员工。 SELECT * FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = ‘SCOTT‘) AND ENAME <> ‘SCOTT‘; --11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 SELECT ENAME,SAL FROM EMP WHERE SAL = ANY(SELECT SAL FROM EMP WHERE DEPTNO = 30) AND DEPTNO <> 30; --12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 SELECT ENAME,SAL FROM EMP WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO = 30) AND DEPTNO <> 30; --13.列出在每个部门工作的员工数量、平均工资和平均服务期限。 SELECT DEPTNO,COUNT(EMPNO) 员工数量,ROUND(AVG(SAL),2) 平均工资, ROUND(AVG(MONTHS_BETWEEN(CURRENT_DATE,HIREDATE))/12,2) 平均服务年限 FROM EMP RIGHT JOIN DEPT USING(DEPTNO) GROUP BY DEPTNO; --14.列出所有员工的姓名、部门名称和工资。 SELECT ENAME,DNAME,SAL FROM EMP LEFT JOIN DEPT USING(DEPTNO); --15.列出所有部门的详细信息和部门人数。 SELECT DEPT.DEPTNO,DNAME,LOC,COUNT(EMPNO) FROM DEPT LEFT JOIN EMP ON EMP.DEPTNO = DEPT.DEPTNO GROUP BY DEPT.DEPTNO,DNAME,LOC; --16.列出各种工作的最低工资。 SELECT JOB,MIN(SAL) FROM EMP GROUP BY JOB; --17.列出各个部门的MANAGER(经理)的最低薪金。 SELECT MIN(SAL) FROM EMP WHERE JOB = ‘MANAGER‘; --18.列出所有员工的年工资,按年薪从低到高排序。 SELECT ENAME,SAL*12+NVL(COMM,0) 年薪 FROM EMP ORDER BY 年薪; --19.列出经理人的名字。 SELECT ENAME FROM EMP WHERE JOB = ‘MANAGER‘ OR JOB = ‘PRESIDENT‘; --20.不用组函数,求出薪水的最大值。 SELECT SAL FROM (SELECT SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM = 1; --21.列出薪资第2高到第8高的员工。 SELECT SAL FROM( SELECT ROWNUM RN,SAL FROM (SELECT SAL FROM EMP ORDER BY SAL DESC)) WHERE RN >= 2 AND RN <= 8; -- 切换回普通用户 -- union, INTERSECT,MINUS CREATE TABLE A1 ( V1 NUMBER, V2 VARCHAR2(10) ); CREATE TABLE A2 ( V3 NUMBER, V4 VARCHAR2(10), V5 VARCHAR2(10) ); INSERT INTO A1 VALUES(10,‘A001‘); INSERT INTO A1 VALUES(11,‘A002‘); INSERT INTO A2 VALUES(10,‘A001‘,‘HAHA‘); INSERT INTO A2 VALUES(12,‘A003‘,‘HEIHEI‘); INSERT INTO A2 VALUES(13,‘A004‘,‘HOHO‘); SELECT * FROM A1 UNION (SELECT V3,V4 FROM A2); SELECT * FROM A1 UNION ALL (SELECT V3,V4 FROM A2); SELECT * FROM A1 INTERSECT (SELECT V3,V4 FROM A2); SELECT * FROM A1 MINUS (SELECT V3,V4 FROM A2); -- 带锁查询 UPDATE CLOTHER_STORE SET STORENUM = 600 WHERE CTYPE = ‘T-SHIRT‘; SELECT * FROM CLOTHER_STORE FOR UPDATE WAIT 5; UPDATE CLOTHER_STORE SET STORENUM = STORENUM - 400 WHERE CTYPE = ‘T-SHIRT‘; -- DCL 之 COMMIT SELECT * FROM STUDENT; DELETE FROM STUDENT WHERE SNO = ‘S011‘; COMMIT; -- DCL 之 ROLLBACK 和 SAVEPOINT SELECT * FROM CLOTHER_SALE; UPDATE CLOTHER_SALE SET SALENUM = SALENUM + 10; ROLLBACK; UPDATE CLOTHER_SALE SET SALENUM = SALENUM + 10; SAVEPOINT CPD; UPDATE CLOTHER_SALE SET SALENUM = SALENUM + 10; ROLLBACK TO SAVEPOINT CPD; -- DCL 之 GRANT GRANT CREATE VIEW TO LUYY124; GRANT UPDATE ANY TABLE TO LUYY124; -- DDL 之 REVOKE REVOKE CREATE VIEW FROM LUYY124; -- CREATE VIEW CREATE OR REPLACE VIEW STUDENT_VIEW AS SELECT * FROM (STUDENT LEFT JOIN SCORE USING(SNO)) LEFT JOIN COURSE USING(CNO); SELECT * FROM STUDENT_VIEW; SELECT SNAME,CNAME FROM STUDENT_VIEW WHERE GRADE < 60; SELECT * FROM SCORE; UPDATE SCORE SET GRADE = GRADE + 1 WHERE SNO = ‘S001‘ AND CNO = ‘C001‘; CREATE OR REPLACE VIEW STUDENT_VIEW AS SELECT * FROM SCORE; UPDATE STUDENT_VIEW SET GRADE = GRADE - 1 WHERE SNO = ‘S001‘ AND CNO = ‘C001‘; -- CREATE INDEX CREATE INDEX SNAME_INDEX ON STUDENT(SNAME ASC) ; SELECT * FROM STUDENT WHERE SNAME = ‘LILI‘; DROP INDEX SNAME_INDEX; -- PLSQL -- 匿名块 SELECT * FROM STUDENT; DECLARE V_SNO VARCHAR2(4) := ‘S011‘; V_SNAME VARCHAR(20) := ‘FANGYUQIN‘; V_SSEX VARCHAR2(1) := ‘F‘; BEGIN INSERT INTO STUDENT VALUES(V_SNO, V_SNAME, V_SSEX); DBMS_OUTPUT.put_line(‘插入成功‘); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLERRM); END; DECLARE V_SNO VARCHAR2(4) := ‘S012‘; V_SNAME VARCHAR2(20); BEGIN SELECT SNAME INTO V_SNAME FROM STUDENT WHERE SNO = V_SNO; DBMS_OUTPUT.put_line(V_SNAME); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLERRM); END; -- 练习: CREATE TABLE FAKEDATA( SNO VARCHAR2(4), SNAME VARCHAR2(10) ); SELECT * FROM FAKEDATA; DECLARE SNO_PREFIX VARCHAR2(1) := ‘S‘; SNAME_PREFIX VARCHAR2(4) := ‘LUYY‘; SUFIX VARCHAR2(3); I NUMBER; BEGIN FOR I IN 1..100 LOOP IF I < 10 THEN SUFIX := ‘00‘ || I; ELSIF I < 100 THEN SUFIX := ‘0‘ || I; ELSE SUFIX := ‘‘ || I; END IF; INSERT INTO FAKEDATA VALUES(SNO_PREFIX || SUFIX, SNAME_PREFIX || SUFIX); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLERRM); END; -- 记录类型 RECORD DECLARE TYPE STUDENTRECORD IS RECORD( V_SNO STUDENT.SNO%TYPE, V_SNAME STUDENT.SNAME%TYPE, V_SSEX STUDENT.SSEX%TYPE ); SR STUDENTRECORD; BEGIN SELECT * INTO SR FROM STUDENT WHERE SNO = ‘S010‘; DBMS_OUTPUT.put_line(SR.V_SNO||‘: ‘||SR.V_SNAME); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLERRM); END; DECLARE SR STUDENT%ROWTYPE; BEGIN SELECT * INTO SR FROM STUDENT WHERE SNO = ‘S010‘; DBMS_OUTPUT.put_line(SR.SNO||‘: ‘||SR.SNAME); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLERRM); END; -- 带名块 -- PROCEDURE CREATE OR REPLACE PROCEDURE P1( V_SNO IN VARCHAR2, V_SNAME OUT VARCHAR2 ) IS BEGIN SELECT SNAME INTO V_SNAME FROM STUDENT WHERE SNO = V_SNO; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLERRM); END P1; DECLARE V1 VARCHAR2(4) := ‘S003‘; V2 VARCHAR2(20); BEGIN P1(V1,V2); DBMS_OUTPUT.put_line(V2); END; DROP PROCEDURE P1; -- FUNCTION CREATE OR REPLACE FUNCTION F1( V_CNAME IN VARCHAR2 ) RETURN VARCHAR2 IS V_TNAME VARCHAR2(20); BEGIN SELECT TNAME INTO V_TNAME FROM COURSE LEFT JOIN TEACHER USING(TNO) WHERE CNAME = V_CNAME; RETURN V_TNAME; END F1; DECLARE V1 VARCHAR2(10) := ‘JAVA‘; V2 VARCHAR2(20); BEGIN V2 := F1(V1); DBMS_OUTPUT.put_line(V2); END; SELECT F1(‘RJZL‘) FROM DUAL; DROP FUNCTION F1; -- 游标 CURSOR DECLARE CURSOR MYCURSOR(V_SNO VARCHAR2) IS SELECT * FROM SCORE WHERE SNO = V_SNO; SR SCORE%ROWTYPE; BEGIN IF MYCURSOR%ISOPEN = FALSE THEN OPEN MYCURSOR(‘S002‘); END IF; LOOP FETCH MYCURSOR INTO SR; EXIT WHEN MYCURSOR%NOTFOUND; DBMS_OUTPUT.put_line(SR.CNO||‘: ‘||SR.GRADE); END LOOP; DBMS_OUTPUT.put_line(‘共计‘||MYCURSOR%ROWCOUNT||‘条记录‘); CLOSE MYCURSOR; END; -- 触发器 TRIGGER CREATE TABLE MEMBERINFO( MID VARCHAR2(4) CONSTRAINT MC001 PRIMARY KEY, QCOIN NUMBER(10) ); CREATE TABLE RECHARGE( RNO VARCHAR2(4) CONSTRAINT RC001 PRIMARY KEY, MID VARCHAR2(4) CONSTRAINT RC002 REFERENCES MEMBERINFO(MID), REQCOIN NUMBER(10) ); CREATE TABLE CONSUME( CNO VARCHAR2(4) CONSTRAINT CC001 PRIMARY KEY, MID VARCHAR2(4) CONSTRAINT CC002 REFERENCES MEMBERINFO(MID), CQCOIN NUMBER(10) ); INSERT INTO MEMBERINFO VALUES(‘M001‘,400); INSERT INTO MEMBERINFO VALUES(‘M002‘,700); INSERT INTO MEMBERINFO VALUES(‘M003‘,1400); SELECT * FROM MEMBERINFO; SELECT * FROM RECHARGE; SELECT * FROM CONSUME; CREATE OR REPLACE TRIGGER RECHARGE_INSERT AFTER INSERT ON RECHARGE FOR EACH ROW BEGIN UPDATE MEMBERINFO SET QCOIN = QCOIN + :NEW.REQCOIN WHERE MID = :NEW.MID; END RECHARGE_INSERT; CREATE OR REPLACE TRIGGER CONSUME_INSERT BEFORE INSERT ON CONSUME FOR EACH ROW DECLARE V_QCOIN NUMBER(10); BEGIN SELECT QCOIN INTO V_QCOIN FROM MEMBERINFO WHERE MID = :NEW.MID; IF V_QCOIN >= :NEW.CQCOIN THEN UPDATE MEMBERINFO SET QCOIN = QCOIN - :NEW.CQCOIN WHERE MID = :NEW.MID; ELSE RAISE_APPLICATION_ERROR(-20001,‘余额不足‘); END IF; END CONSUME_INSERT; INSERT INTO RECHARGE VALUES(‘R001‘,‘M002‘,500); INSERT INTO CONSUME VALUES(‘C002‘,‘M002‘,200); INSERT INTO CONSUME VALUES(‘C003‘,‘M003‘,2000);ORACLE查询语句
标签:mit minus loop abs point else als fetch drop
本文系统来源:http://www.cnblogs.com/sallyliu/p/6283397.html
内容总结
以上是互联网集市为您收集整理的ORACLE查询语句全部内容,希望文章能够帮你解决ORACLE查询语句所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。