Explain common Oracle SQL operations and explain oraclesql

Explain common Oracle SQL operations and explain oraclesql
Create tablespaces and users

/* Note: if the corresponding user and tablespace already exist, you need to delete the corresponding user and tablespace and create them all again */-- delete the user drop user test cascade; -- delete tablespace drop tablespace test_data_temp including contents and datafiles; drop tablespace test_data including contents and datafiles; -- create temporary tablespace test_data_temp tempfile 'test _ tables' size 100 m autoextend on; -- create tablespace test_data logging datafile 'test _ data. dbf 'size 100 m autoextend on; -- create a user and specify the tablespace create user test identified by test default tablespace test_data temporary tablespace test_data_temp profile default; -- grant the role permission to the user grant connect to test; grant resource to test; -- grant the system permission grant unlimited tablespace to test; -- grant the Administrator permission grant dba to test; exit;

Create and modify table information

-- Drop table t_persion; -- create table t_persion (pid varchar2 (32) not null, pname varchar (32) not null, age number, sex char (1 )); -- modify the table name alter table t_persion rename to t_persion2; -- modify the column name alter table t_persion rename column pid to ppid; -- add, modify, and delete the table field alter table t_persion add (asd number ); alter table t_persion modify (asd char (1) default '0' not null); alter table t_persion drop (asd ); -- add or delete primary key constraint alter table t_persion add constraint pk_t_persion primary key (pid); alter table t_persion drop constraint pk_t_persion -- add or delete foreign key constraint alter table t_persion add constraint fk_t_persion foreign key) references t_sex (sid); alter table t_persion add constraint fk_t_persion foreign key (sex) references t_sex (sid) on delete cascade; -- foreign key constraint, cascade alter table t_persion drop constraint fk_t_persion; -- add a table COMMENT comment ON table t_persion IS 'personal information'; -- add a field COMMENT comment on column t_persion.pid is 'id '; comment on column t_persion.pname is 'name'; comment on column t_persion.age is 'age'; comment on column t_persion.sex is 'gender ';

Stored Procedure No parameter + cursor

-- No parameter + cursor create or replace procedure demo1 as cursor cur_data is -- // (cursor: a result set that can be traversed) select * from t_persion t; begin for cur_row in cur_data loop handler (cur_row.pid | ':' | cur_row.pname); end loop; exception when others then dbms_output.put_line (sqlcode); begin (sqlerrm); end; insert into t_persion values ('1', 'zhang san',); insert into t_persion values ('2', 'Li si',); call demo1 ();

Input parameter

-- Input parameter create or replace procedure demo2 (p_pid in test. t_persion.pid % type, p_pname in varchar2, p_age in number, p_sex in char) is begin insert into t_persion (pid, pname, age, sex) values (p_pid, p_pname, p_age, p_sex); exception when others then dbms_output.put_line (sqlcode); dbms_output.put_line (sqlerrm); end; call demo2 ('3', 'wang wu );

Input parameter + output parameter

-- Input parameter + output parameter create or replace procedure demo3 (p_pid in test. t_persion.pid % type, p_pname out varchar2) is begin select pname into p_pname from t_persion t where t. pid = p_pid; delete t_persion t where t. pid = p_pid; exception when others then dbms_output.put_line (sqlcode); dbms_output.put_line (sqlerrm); end; -- call var pname varchar2 (32) in sqlplus; call demo3 (3 ,: pname); print: pname;