OOracle实验.docVIP

实验2： 创建角色 CREATE ROLE UserMan IDENTIFIED BY myrollpwd; 设置角色权限 GRANT CONNECT TO UserMan; GRANT RESOURCE TO UserMan; GRANT UNLIMITED TABLESPACE TO UserMan; 3. 创建用户： CREATE USER UserManAdmin IDENTIFIED BY UserPwd DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; 设置系统权限 GRANT UNLIMITED TABLESPACE TO UserManAdmin; 指定用户角色 GRANT UserMan TO UserManAdmin; 实验3： grant sysdba to UserManAdmin; 登陆 创建表users: CREATE TABLE UserManAdmin.Users (UserId Number Primary key, UserName Varchar2(40) NOT　NULL, UserType Number(1), UserPwd Varchar2(40) ); 10.9 切记：写语句时一定不要有空格！！！ /*打开显示模式*/ SET ServerOutput ON; DECLARE varName VARCHAR2(50); varId NUMBER; CURSOR MyCur(varType NUMBER) IS SELECT UserId,UserName FROM UserManAdmin.Users WHERE UserType=varType; BEGIN IF MyCur%ISOPEN=FALSE Then OPEN MyCur(1); END IF; FETCH MyCur INTO varId,varName; WHILE MyCur%FOUND LOOP FETCH MyCur INTO varId, varName; dbms_output.put_line(用户编号: ||varId||,用户名||varName); END LOOP; CLOSE MyCur; END; / 10.21 CREATE OR REPLACE PROCEDURE UserManAdmin.ResetPwd (v_UserId IN NUMBER) AS BEGIN UPDATE UserManAdmin.Users SET UserPwd=111111 WHERE UserId=v_UserId; END; / SELECT UserName,UserPwd FROM UserManAdmin.Users WHERE UserId=1; 10.24 CREATE FUNCTION UserManAdmin.GetPwd (v_name IN Users.UserName%Type) RETURN Users.UserPwd%Type AS outpwd Users.UserPwd%Type; BEGIN SELECT UserPwd INTO outpwd FROM UserManAdmin.Users WHERE UserName=||v_name||; RETURN outpwd; END; / DROP FUNCTION UserManAdmin.GetPwd; DROP USER UserManAdmin cascade; DROP ROLE UserMan; 10.33 创建表USERMAN.UserType. CREATE TABLE UserManAdmin.UserType( TypeId Number Primary Key, TypeName Varchar2(40) NOT NULL ); 插入数据： INSERT INTO UserManAdmin.UserType VALUES(1,’管理员’); INSERT INTO UserManAdmin.UserType VALUES(2,’用户’); COMMIT; CREATE OR REPLACE TRIGGER UserManAdmin.MyTrigger AFTER UPDATE ON UserManAdmin.UserType FOR EACH ROW BEGIN UPDATE UserManAdmin.Users SET UserType=:new.TypeId WHERE UserType=:old.TypeId; END; / 更新看效果： UPDATE UserManAdmin.UserType SET TypeId