随便整理的一些自用的oracle指令
INSTALLING
ORACLE
If error: lack libnsl
sudo dnf install libnsl
Note: The default RAM is 1GB, set 4GB manually.
PL/SQL
Note: .ora file in virtual machine is something like /u01/app/….
Checking ip of virtual machine: ifconfig
In PL/SQL, if no listener, type ‘lsnrctl start’ in virtual machine
In PL/SQL select SYSDBA mode
username: SYS password:gavinfly
Changing password: ‘alter user SYS identified gavinfly’
JAVA1.8
Download jdk pack
$ java -version # check java vesion
$ tar zxf xxxxxxx.gz
$su
export JAVA_HOME=/usr/locak/jdkxxx
expot PATH=$PATH:$JAVA_HOME/bin
HADOOP
$ hadoop version
$ su
$ cd ~/.bashrc
export HADOOP_HOME=/usr/local/hadoop
export HADOOP_MAPRED_HOME=$HADOOP_HOME
export HADOOP_COMMON_HOME=$HADOOP_HOME
export HADOOP_HDFS_HOME=$HADOOP_HOME
export YARN_HOME=$HADOOP_HOME
export HADOOP_COMMON_LIB_NATIVE_DIR=$HADOOP_HOME/lib/native export
PATH=$PATH:$HADOOP_HOME/sbin:$HADOOP_HOME/bin
$ source ~/.bashc
Hive
Some Additional Info
Initialization
schematool -dbType derby -initSchema
Restart metastore: enter $HIVE_HOME/bin and enter
./hive --service metastore &
Oracle Statement
# Showing user
show user;
# Switch user
conn user_name;
# Switch to sysdba
conn sys as sysdba;
# Creating tablespace
create tablespace test1;
# Creating users ( if no succcess, run first line first)
# alter session set "_ORACLE_SCRIPT"=true;
create user gavin identified by gavin
default tablespace tablespace;
# Grant user
grant connect,resource,dba to gavin;
# or
grant connect to gavin with admin option;
grant dba to gavin with admin option;
grand resource to gavin with admin option;
# Show current user's tablespace
select username, default_tablespace from user_users;
Showing name of current database
select * from global_name;
Creating Tables
create table people(
name varchar(20) not null,
age number,
id number,
constraint people primary key (id)
);
Adding a comment on a table
comment on table people is 'ppl';
Showing a comment of a table
select * from user_tab_comments where table_name = 'PEOPLE'; # table name must in Upper letter
Dropping a comment of a table
comment on table people is '';
Adding a comment on a column
comment on column people.id is 'unique identifier';
Showing a comment of a column
select * from user_col_comments where table_name = 'PEOPLE'; # table name must in Upper letter
Dropping a comment of a column
comment on column people.id is '';
Inserting values
insert into people (name,age,id) values
('gavin',28,1);
Showing tables
select * from user_tables;
select table_name from user_tables; #current user
select * from all_tables;
select table_name from all_tables; #all users
Showing table structure
select dbms_metadata.get_ddl('TABLE','PEOPLE') from dual;
describe people;
desc people;
Altering attribute type/length
alter table people modify(name varchar(255));
Adding\droping attribute
alter table people add dep varchar(20);
alter table people drop column dep;
Update attribute
update people set age = 28 where name = 'gavin';
Showing TOP N
select * from people where rownum < N+1;
Fuzzy showing
select * from people where name like '%n';
select * from people where name like 'g%';
select * from people where name like '%i%';
select * from people where name like '%[0-9]%';
Date format / Window function
select player_id, to_char(event_date,'yyyy-mm-dd') as "first_login"
from (
select player_id,event_date,
rank() over(partition by player_id order by event_date asc) rnk
from Activity)t
where t.rnk=1
PLSQL Statement
Exporting structure of table
Tools -> Export User Objects (Using command window)
Importing structure of table
Tools -> Import Tables (Using command window)
Exporting structure of table and records
Tools -> Export Tables
Importint structure of table and records
Tools -> Import Tables
Exporting csv data
select * from table; -> Export Query Result
Loading csv file
Tools -> Test Importer -> Open data file -> Data to Oracle -> Choose Owner and Table -> Import
sql window
New -> Sql Window
Showing structure of table
Right click table -> View -> Column
Showing Window List
View -> Window List
Update table
select * from people for update;
unlock -> after updating -> commit or rollback
IF-THEN-ELSE
declare
x people.age%type;
begin
select age into x from people
where name = 'cookie';
if x > 28 then
dbms_output.put_line(x||'old');
elsif x < 28 then
dbms_output.put_line(x||'young');
else
dbms_output.put_line(x||'ok');
end if;
end;
/
Go to/ Lable
DECLARE
p VARCHAR2(30);
n PLS_INTEGER := 37; -- test any integer > 2 for prime
BEGIN
FOR j in 2..ROUND(SQRT(n)) LOOP
IF n MOD j = 0 THEN -- test for prime
p := ' is not a prime number'; -- not a prime number
GOTO print_now;
END IF;
END LOOP;
p := ' is a prime number';
<<print_now>>
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p);
END;
/
DECLARE
p VARCHAR2(30);
n PLS_INTEGER := 37; -- test any integer > 2 for prime
BEGIN
FOR j in 2..ROUND(SQRT(n)) LOOP
IF n MOD j = 0 THEN -- test for prime
p := ' is not a prime number'; -- not a prime number
GOTO print_now;
END IF;
END LOOP;
p := ' is a prime number';
<<print_now>>
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p);
END;
/
Restrictions on the GOTO statement: GOTO statemetn cannot branch into an IF statement, CASE statement, LOOP statement, or subblock.
Adding 5 lines record with incresing id
declare
v_no people.id%type;
begin
select max(id) into v_no from people;
for i in 1..5 loop
v_no := v_no+1;
insert into people(id) values(v_no);
end loop;
end;
/
If lock after excuting after update/insert
Checking lock id
select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode
from v$locked_object lo, dba_objects ao, v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
Kill the process`
ALTER system KILL session 'SID,serial'
Checking which table is blocked
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;
Checking which session caused
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;
Killing the process
alter system kill session 'a,b'; -- a is sid, b is serial
Processing query results in a loop
begin
for x in (select * from people where id < 3)
loop
DBMS_OUTPUT.PUT_LINE('name='||x.name||',age='||x.age);
end loop;
end;
/
Processing query results in a loop #2
declare
name job_sal.ename%type;
salary job_sal.sal%type;
id job_sal.eid%type:=1;
sal_raise number;
test_num number;
begin
for id in (select eid from job_sal) loop
select ename,sal into name,salary from job_sal where eid=id.eid; -- Note:id.eid
case
when name = 'manager' then
if salary < 4500 then sal_raise := 1.1;
else sal_raise :=0.9;
end if;
when name= 'salesman' then sal_raise:=1.2;
end case;
update job_sal set sal = sal*sal_raise where eid=id.eid;
end loop;
end;
/
Cursor
declare
test_j_s job_sal%rowtype;
cursor c1 is select * from job_sal;
begin
open c1;
for i in 1..10 loop
fetch c1 into test_j_s;
dbms_output.put_line(i);
exit when c1%notfound;
end loop;
close c1;
end;
/
In declare part, we can also do
declare
name job_sal.ename%type;
salary job_sal.sal%type;
cursor c1 is select ename,sal from job_sal;
or we can declare rowtype using cursor, in that case we should declare cursor first
declare
cursor c1 is select * from job_sal;
test_j_s c1%rowtype;
Ref Cursor / Open for
--- Cursor
declare
cursor c1 is select * from people;
test_row people%rowtype;
begin
open c1;
loop
fetch c1 into test_row;
exit when c1%notfound;
dbms_output.put_line(test_row.name);
end loop;
close c1;
end;
/
--- Ref Cursor with open for
declare
c1 sys_refcursor;
test_row people%rowtype;
begin
open c1 for select * from people;
loop
dbms_output.put_line(test_row.name);
fetch c1 into test_row;
exit when c1%notfound;
end loop;
end;
/
Using Case\When to update Salary
create table job_sal (ename varchar(20), sal number);
insert into job_sal(ename, sal) values ('salesman',500);
select * from job_sal for update;
alter table job_sal add eid number;
declare
job job_sal.ename%type;
salary job_sal.sal%type;
sal_raise number;
pid job_sal.eid%type:=1;
begin
select ename, sal into job, salary from job_sal
where eid =pid;
case
when job = 'manager' then
if salary < 2500 then sal_raise := 1.1;
else sal_raise := 0.9;
end if;
when job = 'salesman' then
if salary < 1500 then sal_raise := 1.1;
end if;
end case;
update job_sal set sal = sal * sal_raise where eid = pid;
commit;
end;
/
Subprograms (procedure, function)
Procedure case
DECLARE
in_string VARCHAR2(100) := 'This is my test string.';
out_string VARCHAR2(200);
PROCEDURE double ( original IN VARCHAR2, new_string OUT VARCHAR2 ) AS
BEGIN
new_string := original || ' + ' || original;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Output buffer not long enough.');
END;
BEGIN
double(in_string, out_string);
DBMS_OUTPUT.PUT_LINE(in_string || ' - ' || out_string);
END;
/
declare
in_num number:=100;
out_num number;
procedure square(original in number, original_squared out number) is
begin
original_squared := original * original;
end;
begin
square(in_num,out_num);
DBMS_OUTPUT.PUT_LINE(out_num);
end;
/
Function case
declare
--in_string varchar(20):='hello';
function print_string(original varchar)
return varchar is new_string varchar(20);
begin
new_string := original ||'+'|| original;
return new_string;
end;
begin
DBMS_OUTPUT.PUT_LINE(print_string('hello'));
end;
/
DECLARE
FUNCTION square(original NUMBER)
RETURN NUMBER AS original_squared NUMBER;
BEGIN
original_squared := original * original;
RETURN original_squared;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(square(100));
END;
/
Package
-- create table customers
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
-- create package
create or replace package c_pack as
-- add a customer
procedure addCustomer(c_id customers.id%type,
c_name customers.name%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type);
-- remove a customer
procedure delCustomer(c_id customers.id%type);
end c_pack;
/
-- create package body
create or replace package body c_pack as
-- add a customer
procedure addCustomer(c_id customers.id%type,
c_name customers.name%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type) is
begin
insert into customers(id, name, age, address, salary)
values(c_id, c_name, c_age, c_addr, c_sal);
end addCustomer;
-- remove a customer
procedure delCustomer(c_id customers.id%type)is
begin
delete from customers where id = c_id;
end delCustomer;
end c_pack;
/
-- use package
declare
c_id customers.id%type := 8;
begin
c_pack.addCustomer(7, 'gavin', 25, 'Chennai', 3500);
c_pack.addCustomer(8, 'cookie', 32, 'Delhi', 7500);
c_pack.delCustomer(c_id);
end;
/
math method
-- maty type
create or replace package math_method_p as
-- add
procedure add(a in number, b in number, c out number);
-- diff
procedure diff(a in number, b in number, c out number);
-- multi
procedure mul(a in number, b in number, c out number);
-- div
procedure div(a in number, b in number, c out number);
end math_method_p;
/
create or replace package body math_method_p as
-- add
procedure add(a in number, b in number, c out number) is
begin
c := a+b;
DBMS_OUTPUT.PUT_LINE('Adding result is:' || c);
end add;
-- diff
procedure diff(a in number, b in number, c out number) is
begin
if a >= b then c:= a-b;
elsif a < b then c:= b-a;
end if;
DBMS_OUTPUT.PUT_LINE('Difference is:' || c);
end diff;
-- multi
procedure mul(a in number, b in number, c out number) is
begin
c := a*b;
DBMS_OUTPUT.PUT_LINE('Multiple is:' || c);
end mul;
-- div
procedure div(a in number, b in number, c out number) is
begin
c := a/b;
DBMS_OUTPUT.PUT_LINE('Div is:' || c);
end div;
end math_method_p;
/
declare
c number;
begin
math_method_p.add(1,2,c);
end;
/
select * from customers;
create or replace package math_method_f as
-- add
function add(a number, b number) return number;
-- diff
function diff(a number, b number) return number;
end math_method_f;
/
create or replace package body math_method_f as
-- add
function add(a number, b number)
return number as c number;
begin
c := a+b;
dbms_output.put_line('Adding is:' || c );
return c;
end add;
-- diff
function diff(a number, b number)
return number as c number;
begin
if a >= b then c:= a-b;
elsif a < b then c:= b-a;
end if;
dbms_output.put_line('Diff is:' || c );
return c;
end diff;
end math_method_f;
/
math_method_f.add(1,2);
declare
c number;
begin
c := math_method_f.add(1,2);
end;
/
Create procedure
create or replace procedure delCustomers(c_id customers.id%type)is
begin
delete from customers where id = c_id;
end;
/
begin
delCustomers(3);
end;
/
In terminal: `exec delCustomers(3);` or 'call delCustomers(3);'
Type
-- create type
create or replace type person_type as object(
name varchar(20), gender varchar(20),
birthday date, address varchar(100),
member procedure change_address(new_addr varchar),
member function get_info return varchar
);
-- create type body
create or replace type body person_type is
member procedure change_address(new_addr varchar)
is
begin
address := new_addr;
end;
member function get_info return varchar
is
v_info varchar(100);
begin
v_info := 'name:' || name || ',DOB:' || birthday;
return v_info;
end;
end;
/
-- create test table
create table person_type_test(
eno number, person person_type,
sal number, job varchar(20)
);
-- insert test data
insert into person_type_test(eno,sal,job,person) values
(1,20000,'analyst',
person_type('gavin','male','28-Apr-1993','earth'));
-- using type to update info
declare
v_person person_type;
begin
select person into v_person from person_type_test where eno = 1;
v_person.change_address('mars');
update person_type_test set person = v_person where eno = 1;
dbms_output.put_line(v_person.get_info);
end;
/
Using sql%found
begin
delete from customers where id = 6;
if sql%found then
insert into customers values(9,'cookie',27,'earth',200);
end if;
end;
/
Raise Exception
declare
test_exception exception;
pragma exception_init(test_exception, -20120);
cursor c1 is select * from customers;
test_c customers%rowtype;
begin
open c1;
for i in 1..8 loop
fetch c1 into test_c;
dbms_output.put_line(i);
if c1%notfound then
dbms_output.put_line('before');
raise_application_error(-20120,'exception occured');
end if;
end loop;
close c1;
end;
/
Record
-- Normal way to declare
declare
eid people.id%type;
ename people.name%type;
eage people.age%type;
edep people.dep%type;
begin
eid:=1;
select name,age,dep into ename,eage,edep from people where id = eid;
dbms_output.put_line(ename || ',' || eage || ',' || edep);
end;
/
-- Using record to declare
declare
eid people.id%type;
type test_type is record(
ename people.name%type,
eage people.age%type,
edep people.dep%type
);
e_type test_type;
begin
eid:=1;
select name,age,dep into e_type from people where id=eid;
dbms_output.put_line(e_type.ename || ',' || e_type.eage || ',' || e_type.edep);
end;
/
-- Record in record
declare
eid people.id%type;
type test_type1 is record(
ename people.name%type,
eage people.age%type
);
type test_type2 is record(
edep people.dep%type,
v_test1 test_type1
);
v_test2 test_type2;
begin
eid:=1;
select name,age into v_test2.v_test1 from people where id=eid;
dbms_output.put_line(v_test2.v_test1.ename || ',' || v_test2.v_test1.eage );
end;
/
Varray
Varray is a set with elements whcih have same type
declare
type test_varray is varray(12) of number;
varray_object test_varray:=test_varray(1,2,3,4,5);
begin
dbms_output.put_line(varray_object(3));
varray_object.extend(); -- extend 1 length
varray_object(6):=6;
varray_object.extend(4,2); -- extend 4 length with element 2
varray_object.trim(); -- delete last 1 element
varray_object.trim(2); -- delete last 2 elements
dbms_output.put_line('Last element is: ' || varray_object.last); -- index of last element
dbms_output.put_line('Total number of elements is: ' || varray_object.count); -- count
if varray_object.exists(4) then
dbms_output.put_line('4th element exists.');
end if;
if not varray_object.exists(10) then
dbms_output.put_line('10th element not exists.');
end if;
for i in varray_object.first..varray_object.last loop
dbms_output.put_line('Element:' || i || 'values' || varray_object(i));
end loop;
varray_object.delete(); -- delete all elements
end;
/
-- 2 Dimension Varray
declare
type first_varray is varray(10) of number;
type second_varray is varray(10) of first_varray;
variable_varray second_varray:=second_varray(
first_varray(1,2,3,4),
first_varray(5,6,7),
first_varray(8,9)
);
begin
dbms_output.put_line('Show all elements');
for i in 1..variable_varray.count loop
for j in 1..variable_varray(i).count loop
dbms_output.put_line('variable_varray('||i||','||j||')='||variable_varray(i)(j));
end loop;
end loop;
end;
/
Index table
-- Index Table
declare
type test_index is table of varchar(20) index by PLS_INTEGER; -- index is int
v_info test_index;
begin
v_info(1):='a';
v_info(12):='b';
dbms_output.put_line(v_info(1)||','||v_info(12));
end;
/
-- Set
declare
type test_index is table of varchar(20);
test_array1 test_index:=test_index(1,2,3,2);
test_array2 test_index;
test_array3 test_index:=test_index();
test_array4 test_index:=test_index('a','b');
test_array5 test_index:=test_index('a','c');
test_array6 test_index:=test_index('a','c','e');
v_str varchar(10):=4;
begin
test_array2:=set(test_array1);
for i in 1..test_array2.count loop
dbms_output.put_line(' '||test_array2(i));
end loop;
dbms_output.put_line('total number of elements in set:'||cardinality(test_array1));
if v_str member of test_array2 then
dbms_output.put_line('test_array2 contains:'||v_str);
end if;
-- reserve same elements in two sets
test_array3:=test_array4 multiset intersect test_array5;
for i in 1..test_array3.count loop
dbms_output.put_line(''||test_array3(i));
end loop;
-- subset
if test_array5 submultiset test_array6 then
dbms_output.put_line('test_array5 is subset of test_array6');
end if;
end;
/
Forall/ Bulk collect
-- forall:Update data by batch /Bulk collect:load date by batch
declare
type test_array is varray(10) of people.id%type;
v_no test_array:=test_array(1,2,3);
type test_array2 is varray(5) of people.id%type;
v_no2 test_array2;
begin
forall x in v_no.first..v_no.last
update people set name='a' where id=v_no(x);
select id bulk collect into v_no2 from people where name='a';
for i in v_no2.first..v_no2.last loop
dbms_output.put_line('num:'||v_no2(i));
end loop;
end;
/
HIVE STATEMENT
create table testtb1(name string, age int) partitioned by (province string, city string);
alter table testtb1 set serdeproperties('field.delim'=',','serialization.format'=',');
# Adding partitions
alter table testtb1 add partition(province='ny', city='nyc');
# Dropping partitions
alter table testtb1 drop partition(province='ny');
# Adding partitions while inserting values
insert into testtb1 partition(province='ga', city='atl') values('gavin',28);
# Showing partiitons
show partitions testtb1;
select * from testtb1 where province='ga';
# Loading data from local
load data local inpath '/mnt/hgfs/testdata.txt' overwrite into table testtb1 partition(province='fl',city='tp');
# Exporting data from local
insert overwrite local directory '/mnt/hgfs/hiveout' select * from testtb1;
# Exporting data with ',' from local
insert overwrite local directory '/mnt/hgfs/hiveout1'
row format delimited
fields terminated by ','
select * from testtb1;
# Creating testtb2
create table testtb2(name string, age int)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';
# Uploading data to hdfs
hds -put /mnt/hgfs/testdata.txt /user/hive/warehouse
# Loading data from hdfs
load data inpath '/user/hgfs/warehouse/testdata.txt' into table testtb2;
# Loading data from other tables
insert into table testtb3
select * from testtb2;
To Do List
collection
immediate statement
merge
returning
set transaction
SQLCODE
SQLERRM
forall --
collect bulk into --
open for --
record --
savepoint --
