Oracle编程题

最新推荐文章于 2021-04-12 13:37:42 发布

weixin_42534356

最新推荐文章于 2021-04-12 13:37:42 发布

阅读量211

点赞数

分类专栏： oracle学习笔记文章标签： oracle

本文链接：https://blog.csdn.net/weixin_42534356/article/details/103796951

版权

oracle学习笔记专栏收录该内容

13 篇文章 1 订阅

订阅专栏

ALTER TABELSPACE USERS ADD DATAFILE 'D:\USERS03.DBF';

CREATE TABLE course(
	cno NUMBER(8),
    cname VARCHAR2(20),
    credit	NUMBER(2),
    CONSTRAINT C_PK PRIMARY KEY(cno),
    CONSTRAINT C_CK1 CHECH(credit >=1 AND credit <=4),
    CONSTRAINT uk_cname QUEUE(cname)
)

SQL>CREATE TABLE emp(
	empno NUMBER(8),
    ename VARCHAR2(15),
    sal NUMBER(4),
    job VARCHAR2(10),
    CONSTRAINT PK_eno PRIMARY KEY(empno)
)
#----------------

SQL>SELECT current_scn FROM v$database;
CURRENT_SCN
----------------
267898

#----------------

SQL>INSERT INTO emp VALUES(20190011,'Alice',3500,'clerk');
SQL>INSERT INTO emp VALUES(20190022,'Bob',4700,'clerk');
SQL>INSERT INTO emp VALUES(20190033,'Wanger',5500,'manager');
SQL>INSERT INTO emp VALUES(20190034,'Lisan',4600,'salesman');
SQL>INSERT INTO emp VALUES(20190045,'Zhangsi',5000,'clerk');

#----------------

SQL>COMMIT;
SQL>SELECT current_scn FROM v$database;
CURRENT_SCN
----------------
267912

#----------------
SQL>UPDATE SET sal=sal+800 WHERE empno=20190011	OR empno=20190034;
SQL>SELECT current_scn FROM v$database;
CURRENT_SCN
----------------
267916
#------------------

SQL>SLEECT * FROM emp WHERE empno=20190011;
EMPNO	ENAME	SAL	JOB
-------------------
2019001 Alice	4300 clerk

#--------------------
SQL>FLASHBACK TABLE emp TO SCN 267912;
SQL>SELECT * FROM emp WHERE empno=20190011;
EMPNO	ENAME	SAL	JOB
-------------------
2019001 Alice	3500 clerk

DECLARE
	v_table CHAR(20);
	TYPE type_cursor IS REF CURSOR;
	v_cursor type_cursor;
	v_student student%ROWTYPE;
	v_teacher teacher%ROWTYPE;
	exception_input EXCEPTION;
BEGIN
	v_table:='&tablename';
	IF v_table = 'student' THEN
		OPEN v_cursor FOR (SELECT * FROM student WHERE score <(SELECT avg(score) FROM student WHERE con=2));
	ELSIF v_table = 'teacher'
		OPEN c_cursor FRO(SELECT * FROM teacher t1 WHERE sal < (SELECT AVG(sal) FROM teacher t2 WHERE t2.deptno=t1.deptno));
	ELSE
		RAISE exception_input;
	END IF;
	EXCEPTION
		WHEN exception_input THEN
			DBMS_OUTPUT.PUT_LINE('Input must be student or teacher!')
        	ROLLBACK;
	LOOP
		IF v_tabel = 'student' THEN
			FETCH v_cursor INTO v_student;
			EXIT WHEN v_cursor%NOTFOUND;
			DBMS_OUTPUT.PUT_LINE(v_student.sno||' '||v_student.sname||' '||v_student.cno||' '||v_student.score);
		ELSE 
			FETCH v_cursor INTO v_student;
			EXIT WHEN v_sursor%NOTFOUND;
			DBMS_OUTPUT.PUT_LINE(v_teacher.tno||' '||v_teacher.sal||' '||v_teacher.deptno);
		END IF;
	END LOOP;
	CLOSE v_cursor;
END;

#-------------
SQL>CREATE USER user1 IDENTIFIED abcd PASSWORD EXPIRE;
SQL>CREATE USER user2 IDENTIFIED guess;

#--------------
SQL>GRANT CREATE SESSION,CREATE TABLE TO user1 WITH ADMIN OPTION;
#--------------
SQL>conn user1/abcd;
SQL>GRANT CREATE SESSION,CREATE TABLE TO user2;
#--------------
SQL>conn user2/guess;
SQL>CREATE TABLE em1(
	empno NUMBER(5),
    ename VARCAHR(10)
)
#-------------
SQL>conn system/manager;
SQL>REVOKE CREATE TABLE FROM user1;
#---------------
SQL>conn user2/guess;
SQL>CREATE TABLE em2(
	empno NUMBER(5),
    ename VARCHAR2(10)
)
#-------------------
SQL>DROP USER user1 CASCADE;
SQL>DROP USER user2;
#--------------------
SQL>CREATE ROLE test1 IDENTIFIED BY guess;
SQL>GRANT CREATE TABLE,CONNECT TO test1;
#--------------------
SQL>CREATE USER user3 IDENTIFIED BY qwer;
SQL>GRANT ROLE test1 TO user3;
SQL>SET ROLE tes1 IDENTIFIED BY qwer;
#-------------------
SQL>conn user3/qwer;
SQL>CREATE TABLE em3(
	empno NUMBER(5),
    ename VARCHAR2(10)
)
#--------------------
SQL>DROP ROLE test1;

CREATE OR REPLACE FUNCTION ret_maxsal(p_deptno emp.deptno%TYPE)
RETURN emp.sal%TYPE
IS 
  v_maxsal emp.sal%TYPE;
BEGIN
  SELECT max(sal) INTO v_maxsal FROM emp WHERE deptno=p_deptno;
  RETURN v_maxsal;
EXCEPTION
    WHEN NO_DATA_FOUND THEN 
         DBMS_OUTPUT.PUT_LINE('The deptno is invalid!');
END  ret_maxsal;