Oracle数据库脚本学习：建用户、删用户、建表、改表、删表

一、Shell脚本：dbscore.sh

调用方法：

/bin/sh dbscore.sh create_all

/bin/sh dbscore.sh update_score

/bin/sh dbscore.sh drop_all

#!/bin/sh

echo "$1"

if [ "$1" == "create_all" ]; then

    echo Create User and Table...
    sqlplus / as sysdba @create_all.sql

elif [ "$1" == "drop_all" ]; then

    echo Drop User and Table...
    sqlplus / as sysdba @drop_all.sql

elif [ "$1" == "update_score" ]; then
    
    echo Update Score...
    sqlplus / as sysdba @update_score.sql

fi

二、建用户、建表：create_all.sql

SET SERVEROUTPUT ON

CREATE USER "TSYBIUS" IDENTIFIED BY "1234";
GRANT "DBA" TO "TSYBIUS";

CREATE TABLE TEST_SCORE
(
    StudentId    number(10,0),
    StudentName  varchar2(20),
    TestScore    number(10,0),
    PRIMARY KEY(StudentId)
);

INSERT INTO TEST_SCORE (StudentId, StudentName, TestScore)
    VALUES (0, 'Gaius', 70);

INSERT INTO TEST_SCORE (StudentId, StudentName, TestScore)
    VALUES (1, 'Marcus', 57);

INSERT INTO TEST_SCORE (StudentId, StudentName, TestScore)
    VALUES (2, 'Titus', 54);

INSERT INTO TEST_SCORE (StudentId, StudentName, TestScore)
    VALUES (3, 'Quintus', 49);

INSERT INTO TEST_SCORE (StudentId, StudentName, TestScore)
    VALUES (4, 'Verinus', 60);

INSERT INTO TEST_SCORE (StudentId, StudentName, TestScore)
    VALUES (5, 'Rufus', 59);

EXIT

三、将表中属性TestScore在[55,60)的值调整为60：update_score.sql

(就是把那些考试成绩和及格线差不到5分的学生分数提到及格，汗！)

SET SERVEROUTPUT ON

DECLARE

    V_ID    TEST_SCORE.StudentId%TYPE;
    V_NAME  TEST_SCORE.StudentName%TYPE;
    V_SCORE TEST_SCORE.TestScore%TYPE;
    CURSOR C IS SELECT StudentId, StudentName, TestScore FROM TEST_SCORE;
    V_COUNTER    NUMBER(15,0)    :=0;

BEGIN

    OPEN C;
    
    LOOP
        FETCH C INTO V_ID, V_NAME, V_SCORE;
        EXIT WHEN C%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Checking Data: '||C%ROWCOUNT);
        IF V_SCORE<60 AND V_SCORE>=55 THEN
            UPDATE TEST_SCORE SET TestScore=60 WHERE StudentId=V_ID;
            V_COUNTER:=V_COUNTER+1;
            DBMS_OUTPUT.PUT_LINE(V_NAME||': Update Data!');
        END IF;
    END LOOP;

    CLOSE C;
    DBMS_OUTPUT.PUT_LINE('Total Update: '||V_COUNTER);

END;
/

EXIT

四、删用户、删表：drop_all.sql

DROP USER "TSYBIUS" CASCADE;
DROP TABLE TEST_SCORE;

EXIT

END

转载于:https://my.oschina.net/Tsybius2014/blog/286467