Oracle
ORACLEステートメントを完璧に入力できるようになろう！
ORACLEデータベースユーザ用
問題文
ふりがな非表示
ふりがな表示
(lsnrctl start)
lsnrctl start
リスナー開始
(lsnrctl status)
lsnrctl status
リスナーの状態確認
(lsnrctl stop)
lsnrctl stop
リスナー停止
(lsnrctl reload)
lsnrctl reload
リスナーをリロードする
(sqlplus / as sysdba)
sqlplus / as sysdba
システムユーザーでデータベースへログイン
(sqlplus hr/hr@//localhost:1521/orclpdb)
sqlplus hr/hr@//localhost:1521/orclpdb
ＨＲユーザーでデータベースにログイン
(sqlplus hr/hr@orclpdb)
sqlplus hr/hr@orclpdb
ＨＲユーザーでデータベースにログイン
(spool c:\mydata.html@c:\myscript.sql)
spool c:\mydata.html@c:\myscript.sql
ｓｑｌファイルを実行
(select sysdate from dual;)
select sysdate from dual;
(select * from all_tables;)
select * from all_tables;
(select * from all_users;)
select * from all_users;
(select * from countries;)
select * from countries;
(select * from depertments;)
select * from depertments;
(select * from employees;)
select * from employees;
(select employee_id,first_name,last_name from employees;)
select employee_id,first_name,last_name from employees;
(select * from locations;)
select * from locations;
(select * from regions where rownum < 5 order by user_id like 'all%' and table_type = 'VIEW';)
select * from regions where rownum < 5 order by user_id like 'all%' and table_type = 'VIEW';
(select * from all_tables where owner sys;)
select * from all_tables where owner sys;
(select /*csv*/* from all_users;)
select /*csv*/* from all_users;
/*ここにはコメントとして扱われるため実行されない*/
(select /*insert*/* from all_users;)
select /*insert*/* from all_users;
/*ここにはコメントとして扱われるため実行されない*/
など
(select /*html*/* from all_users;)
select /*html*/* from all_users;
/*ここにはコメントとして扱われるため実行されない*/
(select * from [(table)] where [(conditions)];)
select * from [(table)] where [(conditions)];
(alter session set container = orclpdb)
alter session set container = orclpdb
(show con_name)
show con_name
接続コンテナを確認
(cl scr)
cl scr
スクリーンをクリアする
(column name format a20;)
column name format a20;
列幅を20バイトにする
(select name,con_id from v$pdbs;)
select name,con_id from v$pdbs;
(select name as "Service Name" from v$active_services where con_id = 3;)
select name as "Service Name" from v$active_services where con_id = 3;
(alter session set container = orclpdb)
alter session set container = orclpdb
セッションをORCLPDへ変更する
(select name,open_mode from v$pdbs;)
select name,open_mode from v$pdbs;
(alter pluggable database open;)
alter pluggable database open;
(select name,open_mode from v$pdbs;)
select name,open_mode from v$pdbs;
(alter user HR identified by hr account unlock;)
alter user HR identified by hr account unlock;
(create view EmpVu50 as select Employee_id,First_Name || ' ' || Last_Name as EmpName,Email,Salary,)
create view EmpVu50 as select Employee_id,First_Name || ' ' || Last_Name as EmpName,Email,Salary,
ビュー作成
(Department_id from Employees where Department_id = 50;)
Department_id from Employees where Department_id = 50;
(select text from user_views;)
select text from user_views;
(delete from EmpVu50 where Employee_id = 185;)
delete from EmpVu50 where Employee_id = 185;
(update EmpVu50 set salary = 2800 where Employee_id = 199;)
update EmpVu50 set salary = 2800 where Employee_id = 199;
(alter table employees add constraint Emp_Dept_Ref Foreign Key(DeptId) references Departments(DeptId) )
alter table employees add constraint Emp_Dept_Ref Foreign Key(DeptId) references Departments(DeptId)
(on delete cascade;)
on delete cascade;
(on delete set null;)
on delete set null;
(alter table employees add constraint Employees_Email_un unique(EmailID);)
alter table employees add constraint Employees_Email_un unique(EmailID);
(select constraint_name,constraint_type,table_name,status from user_constraints;)
select constraint_name,constraint_type,table_name,status from user_constraints;
(select to_char(sysdate,'HH:MI:SS AM') from dual;)
select to_char(sysdate,'HH:MI:SS AM') from dual;
(select to_char(sysdate,'HH24:MI:SS AM') from dual;)
select to_char(sysdate,'HH24:MI:SS AM') from dual;
システム日付を指定フォーマットで表示
(select to_number(to_char(sysdate,'dd')) + 20 from dual;)
select to_number(to_char(sysdate,'dd')) + 20 from dual;
システム日付の日にちに２０を追加
(select rpad(substr(phone_number,1,3),7,'*') from employees;)
select rpad(substr(phone_number,1,3),7,'*') from employees;
(select tablespace_name,contents from dba_tablespaces;)
select tablespace_name,contents from dba_tablespaces;
(select emp_name,dept_name from dept right outer join emp on (emp.emp_id = dept.emp_id);)
select emp_name,dept_name from dept right outer join emp on (emp.emp_id = dept.emp_id);
on clause
(select emp_name,dept_name from dept right outer join emp using(emp_id);)
select emp_name,dept_name from dept right outer join emp using(emp_id);
using clause