Example Summary of Oracle Knowledge Points

Article Directory

Oracle Knowledge Points

Tablespaces, users, roles

Tablespace

Getting relationships between tablespaces and data files

select t.name, d.name from v$tablespace t, v$datafile d;

View data block size

show parameter db_block_size;

Query information about tables owned by the current user, accessible tables, and all tables in the current database

select * from user_tables;
select * from all_tables;
select * from dba_table;

Query information for all tablespaces in the system

select tablespace_name, status, alloction_type from dba_tablespaces;

Create a tablespace

create tablespace demospace
logging
datafile 'D:\Oracle\orsx\oradata\demospace\demospace.dbf' size 20M
autoextend on;

Change the size of the tablespace and add new data files

alter tablespace newsmgm_space
add datafile 'D:\Oracle\orsx\oradata\orcl\pdborcl\newsmgm_space_1.dbf' size 10M;

Modify the size of an existing data file in a tablespace

alter database datafile 'D:\Oracle\orsx\oradata\orcl\pdborcl\newsmgm_space_1.dbf' resize 20M;

Set default tablespace

select property_name, property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

View all user information for the current database

select username, account_status, default_tablespace from dba_users;

Query which containers are in the database

select con_id,dbid,name,open_mode from v$pdbs;

Open pdb-

alter pluggable database pdborcl open;

Turn the session in the current public area to the container -

alter session set container = pdborcl;

Create a new user test01 and bind to demospace-

create user c##test01 identified by 123456 default tablespace demospace;

Give text01 permission to connect to the database -

grant create session to c##test01;

Give test01 permission to query the scott.emp table (and get permission transitively) -

grant select on scott.emp to test01 with grant option;

Create a new user test02-

create user c##test02 identified by 123456;

View all objects -

select username from dba_users;

Grant login rights to users -

grant create session to marc;

Grant users system privileges to create tables and sequences -

grant create table, create sequence to marc;

Grant tablespace'USERS'permission to the user -

alter user c##marcs quota unlimited on USERS;

Grant some object privileges on the id table in marcs mode to the user test-

grant select, insert, update on marcs.identified to test;

role

Create a role_emp-

create role role_emp;

Give role_mep-to query emp tables

grant select on scott.emp to role_emp;

All privileges on the emp table will be assigned to role_mep-

grant all on scott.emp to role_emp;

Assign role_emp role to test01-

grant role_emp to test01;

Recycle the role_emp role from test01 -

revoke role_emp from test01;

Query all the roles test01 has -

select grantee, granted_role, admin_option, default_role
from dba_role_privs where grantee = 'C##TEST01';

Table Management

Table creation

grant create table, create sequence to c##test01;
alter user c##test01 quota unlimited on DEMOSPACE;

Create the department table in the current user's default tablespace -

create table department(
  deptno number(2)primary key,
  dname varchar2(14),
  loc varchar2(13)
);

View Table Structure -

desc department;

Create an employee table in the users tablespace -

create table employee(
  empno number(4)primary key,
  ename varchar2(10),
  job varchar2(20),
  mgr number(4),
  hiredate date,
  sal number(7,2),
  comm number(7,2),
  deptno number(2)not null
         constraint fk_emp_dept references department(deptno)
)
  tablespace users;

Maintenance of tables

Add Columns -

alter table employee add(
  sex char(3),
  registdate date default sysdate not null
);

Modify Column Definition -

alter table employee modify job varchar2(10);
alter table employee modify sex default'male';

Modify Columns

alter table employee rename column registdate to regist_date;

Delete Columns -

alter table employee drop column regist_date; alter table employee add registdate date;
alter table employee drop (sex, registdate);

Mark the column as unused (disable a column) and then delete the unused status column

alter table employee add registdate date;
alter table employee set unused column registdate;

Add a comment to the table -

comment on table employee is 'Employee Sheet';

Add Comments to Columns -

comment on column employee.empno is 'Work Number';

Modify Table Name -

comment on table employee rename to tb_employee;

Delete the department table and foreign key references to it -

drop table department cascade constraint;

Query the name, tablespace, and status of all tables for the current user

select table_name, tablespace_name, status from user_tables;

Query comment information for all tables of the current user -

select table_name, comments from user_tab_comments;

Query the description of a column in a table -

select column_name, data_type, data_length, nullable
from user_tab_columns
where table_name = 'EMPLOYEE';

integrity constraint

  • Primary key constraint primary key
  • Foreign key constraint foreign key
  • Uniqueness constraint unique s can be defined on one or more columns
  • check Constraint check
  • Non-null constraint

Add column-level constraints to tables when they are created -

create table board(
  bid number(2) primary key,  
  bname varchar2(20) not null,  
  status number check(status in(0,1))  
);

create table register(
  rid number(10) primary key,
  logname varchar2(20) unique,
  password varchar2(10) not null,
  age number(3) check (age>=13 and age<=80),
  registboard number(2) not null references board(bid)
);

Table constraints added when creating payroll record tables -

create table payroll(
  empno number(4) references employee(empno),
  payrolldate date not null,
  deptno number(2),
  sal number(7,2),
  comm number(7,2),
  constraint pk_payroll primary key(empno, payrolldate),
  constraint fk_dept foreign key(deptno) references department(deptno)
);

Query all constraint information for the payroll table -

select constraint_name, constraint_type, status
from user_constraints
where table_name='PAYROLL';

Query the columns in the payroll table for the roles of each constraint -

select constraint_name, table_name, column_name
from user_cons_columns
where table_name='PAYROLL';

Create job and department tables deptment-

create table job(
  jobid number,
  jobname varchar2(20),
  jobdesc clob,
  workplace varchar2(20),
  minsalary number(6),
  maxsalary number(6),
  department number(2)
)tablespace users;

create table department(
  deptno number(2) primary key,
  dname varchar2(14),
  loc varchar2(13)
);

Add Primary Key Constraint -

alter table job add primary key(jobid);

Add Uniqueness Constraint -

alter table job add unique(jobname);

Add Foreign Key Constraint -

alter table job add constraint fk_job_dept foreign key(department)
references department(deptno);

Adding check constraints requires table level constraints (by name) -

alter table job add constraint ck_job_salary check( maxsalary > minsalary);

Add Non-empty Constraint -

alter table job modify jobname not null;

Rename foreign key constraint fk_job_dept -

alter table job rename constraint fk_job_dept to fk_jobdept;

Delete Foreign Key Constraints by Name -

alter table job drop constraint fk_jobdept;

Remove Primary Key Constraint -

alter table job drop primary key;

Keep Unique Index When Deleting Primary Key Constraints -

alter table job drop primary key keep index;

Delete Uniqueness Constraint -

alter table job drop unique(jobname);

SQL Foundation

  • Data Definition Language: DDL
  • Data Query Language: DQL
  • Data manipulation language: DML
  • Transaction Control Language: TCL

data management

Insert a row into the Department table -

insert into department(deptno, dname, loc) values(50,'R&D Department','Qingdao');

Insert a record into the employee table -

insert into employee values(
7210,'jenny','programmer',null,to_date('20150302','yyyy-MM-dd'),
3000,null,50);

Insert records from the dept table in scott mode into the deparment table

insert into department select * from scott.dept;

Insert records from the emp table in scott mode into the employee table

insert into employee select * from scott.emp;

Query records for all columns in the Department table -

select * from department;

Query the information table for some columns of qualified employees

select empno, ename, job, sal, comm
from employee
where comm is null;

Update the bonus for all employees in the employee list to 200 yuan -.

update employee set comm = 200;

Update the salary of employees with work number 7210 to the sum of their original salary and bonus -.

update employee set sal = sal + comm where empno = 7210;
select empno,ename,job,sal,comm from employee;

Delete Records by Conditional Sentences -

delete from employee where empno = 7210;

Delete all records (table contents) in a table referenced by a foreign key constraint -

delete from employee;
delete from department;

Use the truncate statement to delete all records in tables referenced by foreign key constraints (not recoverable with rollba) -

alter table employee drop constraint fk_emp_dept;
truncate table department;

Transaction Control

  • Four characteristics of transactions: atomicity, consistency, isolation, persistence

Start and end of transaction

  • Begin: dml: insert; insert; update; delete;
  • End: commit; dll:create table;
create table account(
  account_id varchar2(16),
  account_name varchar2(10),
  account_balance number(16,3),
  constraint pk_accountid primary key(account_id)
);
insert into account values('1001', 'Zhang San', 1000);
The first dml statement executes, transaction A begins
insert into account values('1002', 'Li Si', 1);
select * from account;
commit;
Transaction commits, transaction A ends
update account set account_balance = account_balance-1000
where account_id='1001';
Execute dml statement, new transaction B begins
update account set account_balance = account_balance+1000
where account_id='1002';
alter table account add constraint ck_accountbalance check(account_balance>=0);
Execute ddl statement, transaction B commits automatically, transaction B ends
select * from account;
delete from account where account_id='1001';
New Transaction C Start
exit;
Exit normally, transaction C is automatically committed, transaction C ends

Commit command displays commit transactions

create table department(
  deptno number(2) primary key,
  dname varchar2(14),
  loc varchar2(13)
)tablespace users;

insert into department values(50, 'R&D Department', 'Qingdao');
commit;
select * from department;

insert into department values(60, 'Product Department', 'Qingdao');
update department set loc = 'High-tech Zone'where deptno = 60;
commit;
select * from department;

The rollback command rolls back a single dml statement transaction -

select * from department;
delete from department where deptno = 60;
rollback;
select * from department;

The rollback command rolls back multiple dml statement transactions -

insert into department values(70, 'Marketing Department', 'Qingdao');
update department set loc = 'High-tech Zone'where deptno = 70;
rollback;
select * from department;

Set Save Point -

select * from department;
update department set loc = 'QING DAO'where deptno = 50;
savepoinit sp1;

delete department where deptno = 50;
savepoinit sp2;

rollback to sp1;
select * from department;

rollback;
select * from department;

Concurrent Transactions

Three possible types of problems caused by concurrent transactions

  • Dirty read: A transaction's modifications to data are read by other transactions before committing
  • Non-repeatable read: After a transaction reads data once, other transactions modify the data and do so
    Commit, and when the transaction reads the data again it will get a different result than the previous one.
  • Magic Read: The same query occurs multiple times in the same transaction because of insert or delete operations by other commit firms.
    Returns a different result set each time.

Transaction isolation level

  • Read uncommitted: referred to as unauthorized read or read uncommitted.(dirty, no, magical)
  • read committed: referred to as authorized read or read commit.(No, magic)
  • Repeatable read: referred to as a repeatable read.(magic)
  • serializable: called serialization.

Set the database transaction to serializable isolation level -

set transaction isolation level serializable;

Set the database transaction to read committed isolation level -

set transaction isolation level read committed;

Set the database transaction to read only isolation level -

set transaction read only;

Modify Database Transactions to serializable Isolation Level -

alter session set isolation_level = serializable;

Modify the database transaction to read committede isolation level -

alter session set isolation_level = read committed;

Fantasy Reading Instance

Session 1
create table tran_test(num number);
set transaction isolation level read committed;
insert into tran_test values(10);
select * from tran_test;
Session 2
insert into tran_test values(20);
commit;
Session 1
select * from tran_test;

Modify the isolation level of transactions to serialized instances -

Session 1
delete from tran_test;
exit;
alter session set isolation_level = serializable;
insert into tran_test values(10);
select * from tran_test;
Session 2
insert into tran_test values(20);
commit;
Session 1
select * from tran_test;

Modify the isolation level of the transaction to read only instance -

Session 1
delete from tran_test;
exit;
set transaction read only;
insert into tran_test values(10); ×
select * from tran_test; ×
Session 2
insert into tran_test values(20); 
commit;
Session 1
select * from tran_test; ×

Data Query

Basic Query

select * from emp;

distinct operator (removes duplicate data) -

select distinct * from emp;

Join Operator||

Qualifying queries and sorting (where)

Query employee information for salaries between 1500 and 2900

select empno, ename, sal from emp where sal>=1500 and sal<=2900;

Query the list of people whose positions are salesman

select empno, ename, job from emp where job='SALESMAN'

Between and operator (closed interval)|not between and

Query employees who joined the company between January 1, 1981 and December 31, 1981 -

select empno, ename, hiredate
from emp where hiredate between '01-1 month-81' and '31-12 month-81';

In: Used to test whether certain values are in the list | not in

Query people whose positions are sales, clerk, or manager

select empno, ename, job from emp where job in('SALESMAN','CLEAR','MANAGER');

Null (expressed as empty) -

  • Arithmetic operation with any value, resulting in null
  • Compare with any value and the result is unknown

like Fuzzy Query -

Query employee names for information about all employees starting with M -

select empno, ename, job from emp where like 'M%';

Query employee information whose name starts with A, whose last hyphen is E, and one arbitrary character -

select empno, ename, job from emp where like 'A%E_';

Use the escape operator to make the character express its intent-

select * from department where dname like'IT\_%' escape' \ ';

order by sort -- asc ASC (default) - desc (desc) -

select ename, job, sal from emp where deptno = 30 order by sal desc;

Single-Row Functions

Character function

Use of lower (column | string) - to lower case

select lower('Structural|Query language')from dual;

Use of upper (column | string) - to uppercase

select empno, ename, job from emp where job = upper('clerk');

Use of length (column | string) - return length*

select length('SQL is an english like language')from dual;

Use of initcap (column | string) - capitalization at the beginning

select empno, ename, initcap(ename) from emp;

Use of substr (column | string, start point [, length]) - intercept*

select substr('SQL is an english like language', 3)from dual;
select substr('SQL is an english like language', 1, 3)from dual;

Use of replace (column | string, string to search for, string to replace) - replace *

select replace('SQL Plus supports loops or if statements','supports','not supports')from dual;

Use of concat (column|string, column|string) - Stitching*

select concat('hello,','world!')from dual;

Use of rpad (column | string, length, padding character) - padding

select rpad(ename, 10, '*'), lpad(ename, 10, '*')from emp;

Use of ltrim (string), rtrim (string) - to remove left and right spaces

select ' QST ', ltrim(' QST '), rtrim(' QST ')from dual;

Use of trim (column | string) - removing left and right spaces

select' QST ', trim(' QST ')from dual;

Use of instr (column | string, string to find [, start position]) - Find the present location*

select distinct job, instr(job, upper('man'))from emp;

select distinct job, instr(job, upper('man'), 2)from emp;

Numeric Functions

  • Round (column | number [, reserved digits]) - rounding*
  • TRUNC (column | number [, intercept digits]) - truncation*
  • Mod (Column|Number, Number) - Remaining *

Date function default: DD-MON-RR

  • Get the current time of the system
select sysdate from dual;
  • Modify date display format
alter session set nls_format='yyyy-mm-dd hh24:mi:ss';
  • Add_months (date, number) - Calculates the date of the month increase

  • Last_day - Calculates the last day of the month

  • Next_day (Date, Week) - Calculates the next day of the week

  • Months_between (Date 1, Date 2) - Calculates the number of months between two dates

Conversion function

  • To_char (column | date | number, conversion format) - converted to string

  • To_date (string, conversion format) - Converts a string to a date type (with minutes and seconds: to_timestamp (string, conversion format))

  • To_number - Converts a string of numeric content to a character type

Other Functions

  • NVL (column, replacement value) -
  • Nvl2 (column, replacement value 1, replacement value 2)-
  • Nullif (expression 1, expression 2) -
  • Decode (column value, judgement value 1, display result 1,...)-

Grouping function

count() function -

Query how many employees in the company are managed by managers and how many employees are bonus-

select count(mgr), count(comm)from emp;
  • AVG (column) function: mean-

  • Sum (column) function: sum-

  • Max (column) function: finding the maximum-

  • Min (column) function: finding the minimum-

group by - grouping

Query the average, maximum and minimum wages of each type of employee in the company by position

select job, avg(sal), max(sal), min(sal) from emp group by job;

Execution logic

  • select...from...where...group by...order by...
  • from->where->group by->select->order by

having - Filter groups after grouping

Query for jobs with an average salary above 2000-

select job, avg(sal)from emp group by job having avg(sal)>2000;

multi-table query

Equivalent Joins - Join queries between two tables

Query the name and location of each employee's Department

select empno, ename, emp.deptno, dname, loc
from emp, dept
where emp.deptno = dept.deptno;

Query the Department and location of an employee with a salary of RMB 2000 or above -

select e.empno, e.ename, e.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno and e.sal >= 2000
order by d.loc;

Self-join (a join query between the same tables, used primarily to display subordinate or hierarchical relationships)

Query the direct leadership information of each salesperson -

select e.empno, e.ename, e.job, e.mgr, e.ename manager, m.job position
from emp e, emp m
where e.mgr = m.empno and e.job like 'sal%';

The queries are for the names, positions, managers and departments of employees whose positions are clerks and analysts, and the display results are sorted in ascending order by employee number.

select e.ename Employee Name, e.job position, m.ename Manager Name, d.dname Department Name
from emp e, emp m, dept d 
where e.mgr = m.empno
and e.deptno = d.deptno
and e.job in('CLERK', 'ANALYS')
order by e.empno;

Inner join (table) inner join (table) on (condition)

Query department name and employee name of department number 20.

select e.ename, d.dname
from emp e inner join dept d 
on e.deptno = d.deptno and d.deptno = 20;

Use internal links to query the names, positions, managers and departments of employees whose positions are clerks and analysts, and display the results in ascending order by employee number.

select e.ename Employee Name, e.job position, m.ename Manager Name, d.dname Department Name
from emp e
join emp m on e.mgr = e.empno
join dept d on e.deptno = d.deptno
where e.job in('CLERK', 'ANALYS')
order by e.empno;

External Connection--No all-round return from'+'

Left Outer Connection (Left All Back)

Query all department information and its corresponding employee information (including departments without employees) -

select e.empno, e.ename, d.deptno, d.dname, d.loc
from dept d left join emp e
on d.deptno = e.deptno
order dy d.deptno desc;

select e.empno, e.ename, d.deptno, d.dname, d.loc
from dept d left join emp e
where d.deptno = e.deptno(+)
order by dy d.deptno desc;

Right Outer Connection (Right Full Return)

Query all department information and its corresponding employee information (including departments without employees) -

select e.empno, e.ename, d.deptno, d.dname, d.loc
from emp e left join dept d 
on e.deptno = d.deptno
order dy d.deptno desc;

select e.empno, e.ename, d.deptno, d.dname, d.loc
from emp e left join dept d
where d.deptno(+) = e.deptno
order by dy d.deptno desc;

External connection (left and right return)

Query employees and all their departments based on employee and dept tables. Employees and departments without corresponding relationships are populated with null values

select e.ename, e.deptno, d.deptno, d.dname, d.loc
from employee e full join dept d
on e.deptno = d.deptno
order by d.deptno;

Unequal connection'>''<''between...and"

Query employee information and the corresponding level of their salary.Where wage levels are saved in the model object salgrade of Cott

select e.empno, e.ename, e.job, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

Subquery

Query for information about employees whose wages are higher than average wages -

select empno, ename, sal 
from emp 
where sal >(select avg(sal)from emp);

Query employee information for maximum pay per department -

select a.empno, a.ename, a.sal, a.deptno
from emp a
where (select count(*)from emp where deptno = a.deptno and sal>a.sal)=0;

Query employees with the same position and salary level as ward -.

select e.empno, e.ename, e.job, e.sal, s.grade
from emp e, salgrade s
where e.ename != 'WARD' and (e.sal, s.grade)=(
select sal, grade from emp, salgrade
where ename = 'WARD' and sal between losal and hisal);

Query which employees have the highest salary for any position -- in is equal to any of the values returned by the subquery (not equal)

select empno, ename, job, sal 
from emp 
where sal in(select max(sal)from emp group by job);

Query which employees are paid more than the minimum position average salary - > Anyis greater than one of the values returned by the subquery all (greater than all values)

select empno, ename, job, sal 
from emp 
where sal > any(select avg(sal)from emp group by job);

Query department information for employees -- exists comparison operator subquery returns true if the condition is met (not exists does not return any line true)

select deptno, dname, loc
from dept d
where exists(select * from emp e where d.deptno = e.deptno);

Query department information

select d.deptno, d.dname, e.amount, a.avgsal
from dept d, 
(select deptno, count(*) amount, avg(sal) avgsal from emp group by deptno) e,
where e.deptno = d.deptno;

with named reference

with subdept as(
  select deptno, count(*)count
  from emp 
  group by deptno)
select deptno, dname, loc
from dept
where deptno in(
select deptno from subdept
where count = (select max(count)from subdept));

Collection Query

Union operation: union (excluding duplicates), union all (including duplicates)

Query positions in departments 20 and 30

select job from emp where deptno = 30
union 
select job from emp where deptno = 20;

Intersection operation: intersect

Complement operation: minus (first with second without)

cross join produces Cartesian product phenomenon

select * from emp, dept;

select empno, ename, emp.deptno, dept.deptno, dname
from emp cross join dept;

TopN Query

Query the first line of the employee table for employee information -

select * from emp where rownum = 1;

Query the first two rows of employee information on the employee table -.

select * from emp where rownum < 3;

Rownum first (priority high) then order by

Query information for the top 3 highest paid employees (sort first, pseudo column limit) - temp is a temporary table

select temp.*from(
 select empno, ename, job, sal
 from emp
 order by sal desc)temp
where rownum <= 3;

Query the second line of the employee table for employee information -

select temp.*from(
 select rownum rn, empno, ename, job, sal 
 from emp 
 where rownum < 3)temp
where temp.rn > 1;

By complementing minus-

select * from emp where rownum < 3;
minus
select * from emp where rownum < 2;

Query for records in rows 6 through 10 -

select temp.*from(
 select rownum rn, empno, ename, job, sal
 from emp 
 where rownum <= 10)temp
where temp.rn >= 6;

fetch clause -

fetch first 3 row only: indicates that the first 3 rows of records have been obtained (after sorting)

offset 2 rows fetch next 6 row only: indicates that six rows of records are obtained from line 2, i.e. rows 3 through 8 (excluding boundary values)

fetch next per percent row only: Indicates that a record of the number of related rows is obtained at the specified percentage per percent

Hierarchical Query

Query leadership relationships among employees, ranking positions from top to bottom

select empno, ename, mgr
from emp 
start with job = 'PRESIDENT'
connect by prior empno = mgr;

Common pattern objects

Schema object

view

Create view permission (create view in your own mode)

grant create view to scott;

create any view permission (create views in other modes)

Create a view of basic employee information that contains basic employee information and is sorted in ascending order by employee -

create view scott.v_emp_base
as 
select empno, ename, sal, deptno from emp order by empno;

Queries on the v_emp_base view -

select * from scott.v_emp_base;

View v_emp_base View -

select ename, deptno from v_emp_base;

Create a simple read-only view -

create view scott.v_emp_base_read
as
select empno, ename, sal, deptno from emp order by empno
with read only;
  • or replace means that if the created view exists, it will be automatically rebuilt
  • force creates a view regardless of whether the base table exists or not

Create a view containing multitable joins and grouped queries -

grant create view to scott;
create or replace view v_dept_sal(name, minsal, maxsal, avgsal)
as
select d.dname, min(e.sal), max(e.sal), avg(e.sal)
from dept d, emp e
where d.deptno = e.deptno
group by d.dname;

-with check option - Make modifying the view meet where criteria

Create a view of the annual salary information for employees with salaries greater than 2000 -

create view v_emp_salsry
as
select empno, ename, sal*12 salsay
from emp
where sal > 2000
with check option;

Modify view v_emp_salsry to increase or decrease Department restrictions

create or replace view v_emp_salsry
as
select empno, ename, sal*12 salsay
from emp
where sal > 2000 and deptno = 10
with check option;
  • dba_views: Contains information about all views in the database
  • all_views: Contains information about all views that the current user can access
  • user_views: Contains information about all views owned by the current user
select view_name, text from user_views;

Delete view v_emp_salsry - drop view privilege is required

drop view v_emp_salsry;

Create an employee table, create a view v_test that queries this table, and demonstrate how to modify the view data (updating the view, actually updating the base table)

create table emplyee as select * from emp;
create or replace view v_test
as
select empno, ename, sal, deptno
from employee 
where deptno = 20
with check option;

update v_test set sal = 1000 where ename = 'SMITH';
select empno, ename, sal, deptno from employee where ename = 'SMITH'

A view containing an expression column cannot be followed by a new and insert operation (with check option) -

create or replace view v_test
(empno, ename, salsry, deptno)
as
select empno, enamen, sal * 12, deptno
from employee
where deptno = 20
with check option;

Views that contain multiple table join queries also cannot be updated with data

create or replace view v_emp_dept
as
select empno, ename, dname
from enp e, dept d
where e.deptno = d.deptno;



update v_emp_dept set sal = 800 where ename = 'SMITH' √
update v_emp_dept set dname = 'XXX' where ename = 'SMITH' ×

emp is a key-value saved table (the table in which the foreign key resides is called a slave table, from which the table is saved as a key value), dept is a non-key-value saved table

  • A DML operation on a view can only operate on columns that belong to a key-value saved table

sequence

Create a sequence with an initial value of 1, a maximum value of 1000, and a step size of 1

create sequence seq_emp
start with 1
increment by 1
maxvalue 1000;

Create a sequence with an initial value of 10, a step size of 10, a maximum value of 50, a minimum value of 10, a continuous loop of values when the maximum value is reached, and a server precaches three values

create sequence seq_dept
start with 10
increment by 10
maxvalue 50
minvalue 10
cycle
cache 3;
  • nextval: Returns the next value of the sequence
  • currval: Returns the current value of the sequence and can only be used after at least one nextval ue has been issued

Add, query, and modify data to table department using sequence seq_dept

create table department as select * from scott.dept where 1 = 2;
  • Copy table structure only, not table contents
insert into department(deptno, dname, loc)
values(seq_dept.nextval,'RESEARCH', 'QINGDAO');

select seq_dept.currval from department;

Add, query, and modify data to table department using sequence seq_dept

update department set deptno = seq_dept.nextval where deptno = 10;

View sequence information for the current user -- user_sequences

select sequence_name, min_value, max_value,
increment_by, cycle_flag, cache_size
from user_sequences;

View all sequence information for all users -- dba_sequencrs

Delete seq_dept Sequence -

drop sequence seq_dept;

Synonym

Users create private synonyms in their own mode: create synonym permission

grant create synonym to scott;

Create a private synonym in other user mode: create any synonym permission

Create Public Synonym Permission: create public synonym permission

Create synonym scott emp for emp table in scott mode

grant create synonym to scott;
create synonym scottemp for emp;

Create or replace an existing synonym: create or replace statement (replace table under this synonym) -

Replace the table corresponding to the public synonym scottdept and change the corresponding table from dept to department

create or replace public synonym scottemp for scott.department;

View synonyms created by the current user - user_synonyms

select synonym_name, table_owner, table_name from user_synonyms

View synonyms created by all users - all_synonyms

Delete the private synonym scottemp-drop synonym statement (delete the public synonym drop public synonym statement)

drop synonym scottemp;

Indexes

rowid, empno values for all records in emp table

select rowid, empno, ename, from emp order by empno;
  • Unique: create a unique index
  • Bitmap: build bitmap index

Create Unique B-Tree Index

create index index_employee_ename on employee(ename);

Display representation of storage space -

create index index_employee_ename
on pmployee(ename)
tablespce users storage(initial 20k next 10k pctincrease 65);

Specify storage space allocation for the resulting index when creating primary key constraints for the new_employee table

create table new_employee(
  empno number(4) primary key using index tablespace users pactfree 0,
  ename varchar2(10));

Create a bitmap index for deptno of the employee table

create bitmap index index_employee_deptno on employee(deptno);

Use Bitmap Index

select empno, ename, deptno from employee where deptno = 10 or deptno;

Create a function index based on the function to_char() for the hiredate column in the employee table

create index index_employee_hiredate
on employee(to_char(hiredate,'YYYY-MM-DD'));

Use index_employee_hiredates index

select empno, ename, hiredate
from employee
where ton_char(hiredate,'YYYY-MM-DD')=='1981-11-17';

Create a composite index for the empno and ename columns of the employee table

create index index_empno_ename on employee(empno, ename);
  • Modify index: alter index (merge, rebuild, rename)

  • Merged index: alter index...coalesce

  • rebuild Index: alter index...rebuild

  • Index rename: alter index...rename to

  • Turn on index monitoring status: alter index...monitoring usage

  • Turn off index monitoring status: alter index...nomonitoring usage

  • View dba_indexes: Describes all index information in the database

  • View user_indexes: Describes current user index information in the database

Delete index: drop index

PL/SQL Foundation

PL/SQL

Query for employee name based on the entered employee number -

declare
  v_empno number;
  v_ename varchar2(10);
begin
  dbms_output.put_line('Please enter employee number:');
  v_empno:=&input_empno;
  select ename into v_ename from emp where empno=v_empno;
  dbms_output.put_line('Numbered as:'||v_empno||' Name:'||v_ename);
exception
  when no_data_found then
  dbms_output.put_line('This number employee does not exist');	   
end;

Program structure

Open Output

set serveroutput on;

PL/SQL block with declaration and execution

declare
  v_num number;
begin
  v_num:=100/0;
exception
  when zero_divide then
    dbms_output.put_line('Divide by 0 exception');
end;

PLSQL block with subblocks

declare
  v_x number:=10;
begin
  declare
    v_x number:=20;
  begin    
    dbms_output.put_line('Subblock variable v_x='||v_x);
  end;
  dbms_output.put_line('External statement block variable v_x='||v_x);
end;

Calculate the average salary of the Department to which the employee belongs based on the employee number entered by the user

declare
  v_empno number;
  v_deptno number;
  v_sal number;
begin
  begin
    v_empno:=&Employee number;
	  select deptno into v_deptno from emp where empno = v_empno;
  end;
  select round(avg(sal),2)into v_sal from emp where deptno = v_deptno;
  dbms_output.put_line(v_empno||'The average salary of the employee's department is'||v_sal);
end;

Define Constant - constant

declare
  v_cons constant varchar2(20):='Hello,world.';
begin
  dbms_output.put_line('v_cons Constant value is:'||v_cons);
end;

Number type: number, binary_integer, binary_float, binary_double-

declare
  v_num1 number(3):=100;
  v_num2 number(5,2):=100.99;
  v_binary binary_integer:=-100;
  v_float binary_float:=1000.99F;
  v_double binary_double:=10000.99D;
begin
  v_num1:=v_num1+v_num2;
  v_binary:=v_binary+100;
  v_float:=v_float+1000.99;
  v_double:=v_double+10000.99;
  dbms_output.put_line('number Type data calculation and:'||v_num1);
  dbms_output.put_line('binary_integer Type data calculation and:'||v_binary);
  dbms_output.put_line('binary_float Type data calculation and:'||v_float);
  dbms_output.put_line('binary_double Type data calculation and:'||v_double);
end;

Character types: char, varchar2, nchar, nvarchar2, rowid

declare 
  v_char char(20);
  v_varchar2 varchar2(20);
  v_nchar nchar(20);
  v_nvarchar2 nvarchar2(20);
  v_dept_rowid rowid;
begin
  v_char:='Still water depth';
  v_varchar2:='Still water depth';
  v_nchar:='Still water depth';
  v_nvarchar2:='Still water depth';
  select rowid into v_dept_rowid from scott.dept where deptno=10;
  dbms_output.put_line('v_char Content Length:'||length(v_char));
  dbms_output.put_line('v_varchar2 Content Length:'||length(v_varchar2));
  dbms_output.put_line('v_nchar Content Length:'||length(v_nchar));
  dbms_output.put_line('v_nvarchar2 Content Length:'||length(v_nvarchar2));
  dbms_output.put_line('10 Department No. rowid:'||v_dept_rowid);
end;

Date type: date, timestamp

declare
  v_date1 date:=sysdate;
  v_date2 date:=systimestamp;
  v_date3 date:='04-03 month-2020';
  v_timestamp1 timestamp:=systimestamp;
  v_timestamp2 timestamp:=sysdate;
  v_timestamp3 timestamp:='04-03 month-2020 12.20.40 morning';
begin
  dbms_output.put_line('v_date1:'||to_char(v_date1,'yyyy-MM-dd hh24:mi:ss'));
  dbms_output.put_line('v_date2:'||v_date2);
  dbms_output.put_line('v_date3:'||v_date3);
  dbms_output.put_line('v_timestamp1:'||v_timestamp1);
  dbms_output.put_line('v_timestamp2:'||v_timestamp2);
  dbms_output.put_line('v_timestamp3:'||v_timestamp3);
end;

Boolean type:

declare
  v_flag boolean;
begin 
  v_flag:=true;
  if v_flag then
    dbms_output.put_line('Conditions are true');
  end if;
end;

%type,%rowtype: Represents the type of a column in a table, the type of a row record

declare
  v_name emp.ename%type;
  v_salary emp.sal%type;
  v_hiredate emp.hiredate%type;
begin
  select ename, sal, hiredate, into v_name, v_salary, v_hiredate
  from emp where empno=&empno;
  dbms_output.put_line('Employee Number:'||v_name);
  dbms_output.put_line('wages:'||v_salary);
  dbms_output.put_line('Date of entry:'v_hiredate);
exception
  when no_date_found then
    dbms_output.put_line('The employee number you entered does not exist');
end;

Load a row with%rowtype

declare
  emp_record emp%rowtype;
begin
  select * into emp_record from emp where empno=&empno;
  dbms_output.put_line('Employee Number:'||emprecoord.ename);
  dbms_output.put_line('wages:'||emp_record.sal);
  dbms_output.put_line('Date of entry:'||emp_record.hiredate);
exception
  when no_data_found then
    dbms_output.put_line('The employee number you entered does not exist');
end;

Record type:

control structure

If statement: if, if...else, if...elsif...else

declare
  v_sal number;
begin
  select sal into v_sal from emp where empno=7934;
  if v_sal<1000 then
    update emp set sal=sal+200 where empno=7934;
  elsif v_sal>=1000 and v_sal<2000 then
    update emp set sal=sal+150 where empno=7934;  
  else
    update emp set sal=sal+100 where empno=7934;
  end if;
end;

case statement:

declare
  v_sal number;
begin
  case 
    when XXX then
	  update...where...;
	when YYY then
	  update...where...;
	when MMM then
	  update...where...;
	when NNN then
      update...where...;
  end case;
end;

Loop loop, while...Loop

declare
  v_i number:=1;
  v_sum number:=0;
begin   
  loop
    v_sum:=v_sum+v_i;
	v_i:=v_i;
    exit when v_i>100;
  end loop;
  dbms_output.put_line('1~100 And for:'||v_sum);
end;

while...loop

declare
  v_i number:=1;
  v_sum number:=0;
begin
  while v_i<=100 loop
    v_sum:=v_sum+v_i;
	v_i:=v_i+1;
  end loop;
  dbms_output.put_line('1~100 And for:'||v_sum);
end;

for loop - reverse descending

declare
  v_sum number:=0;
  for v_i in 1..100 loop
    v_sum:=v_sum+v_i;
  end loop;
  dbms_output.put_line('1~100 And for:'||v_sum);

Implement a ninety-nine multiplication table with an inverted triangle

begin
 for v_i reverse 1..9 loop
   for v_j in 1..v_i loop
     dbms_output.put_line(v_i||'*'||v_j||'='||v_i*v_j||'');
   end loop;
   dbms_output.put_line('');
   end loop;
end;

exit: end the entire loop

continue: end the current loop

goto statement: jump statement

declare
begin
  for v_i in 1..5 loop
    if
	  goto
	  
	end if;
  end loop;

end;

exception handling

exception 
  when...others then

end;
  • others: catch all exceptions
  • sqlerrm: output exception information
  • sqlcode: Output exception code

User-defined Exceptions

XXX exception;
pragma exception_init(XXX, -20000~-29999);
  • raise: throw an exception
declare 
  v_myexp

cursor

Implicit cursor: System automatic operation.Obtaining SQL statements through implicit cursor properties

sql%isopen
sql%found
sql%notfound
sql%rowcount

Data updates affect row count judgment -

begin
  update employee set deptno=20 where ename like'%s%';
  if sql%rowcount = 0 then
    dbms_output.put_line('Data update failed');
  else
    dbms_output.put_line('Data Updated'||sql%rowcount||'That's ok');
  end if;
end;

Query employee information based on employee number -

declare
  v_empno emp.empno%type;
  v_emp emp%rowtype;
begin
  v_empno:=&Employee number;
  select * into v_emp from emp where empno=v_empno;
  if sql%found then
    dbms_output.put_line('The name of the employee is'||v_emp.ename);
  end if;
exception
  when no_data_found then
    dbms_output.put_line('The number of employees was not found');
end;

Display cursor: User created.Used to process select statements to return multiple rows of data

Define Cursor

  • Cursor cursor name is query statement;

open

  • open cursor name;

Retrieval Cursor

  • fetch cursor name into variable;

Close Cursor

  • close cursor name;

Query employee number and name in emp table

declare
  cursor cursor_emp is select empno, ename from emp;  --declare cursor
  v_empno emp.empno%type;
  v_ename emp.ename%type;
begin
  open cursor_emp;   --open
  loop
    fetch cursor_emp into v_empno, v_ename;   --Retrieving cursors to point to data to variables
    exit when cursor_emp%notfound;
	dbms_output.put_line('Employee number:'||v_empno||'Full name:'||v_ename);
  end loop;
  close cursor_emp;
end;

Retrieving emp tables using a loop

declare
  cursor cursor_emp is select empno, ename, sal from emp;
  v_emp cursor_emp%rowtype;
begin
  open cursor_emp;
  loop
    fetch cursor_emp into v_emp;
	exit when cursor_emp%notfound;
	dbms_output.put_line(cursor_emp%rowcount||''||v_emp.empno||''||v_emp.ename||''||v_emp.sal);
  end loop;
  close cursor_emp;
end;

Retrieving cursors using a while loop

declare
  cursor cursor_sal is
    select deptno, round(avg(sal),2)avgsal from emp group by deptno;
  v_sal sursor_sal%rowtype;
begin
  if cursor_sal%isopen then
    null;
  else
    open cursor_sal;
  end if;
  fetch cursor_sal into v_sal;   --Cursor points to first line
  while cursor_sal%found loop
    dbms_output.put_line(v_sal.deptno||' '||v_sal.avgsal);
	fetch cursor_sal into v_sal;   --Point the cursor to the next line
  end loop;
  close cursor_sal;  
end;

Retrieving cursors using a for loop

declare
  cursor cursor_emp is select * from emp;
begin
  for v_emp in cursor_emp loop
    dbms_output.put_line(cursor_emp%rowcount||' '||v_emp.ename);
  end loop;
end;

Parameterized display cursor usage -

declare
  cursor cursor_emp(p_empno emp.empno%type)is
    select * from emp where empno = p_empno;
  v_emp cursor_emp%rowtype;
begin
  open cursor_emp(7369);
  loop
    fetch cursor_emp into v_emp;
	exit when cursor_emp%notfound;
	dbms_output.put_line(v_emp.empno||''||v_emp.ename);
  end loop;
  close cursor_emp;
  open cursor_emp(7499);
  loop
    fetch cursor_emp into v_emp;
	exit when cursor_emp%notfound;
	dbms_output.put_line(v_emp.empno||''||v_emp.ename);
  end loop;
  close cursor_emp;
end;

Cursor definition requiring modification of cursor data - for update clause row level locking

cousor cursor_emp is;
select * from emp for update;

The cursor definition of multitable query cursor data needs to be modified

cursor cursor_emp is
select e.empno, e.ename, e.sal, d.dname from emp e, dept d
where e.deptno = d.deptno for update of e.sal;

Cursor Definitions to Avoid Deadlocks

cursor cursor_emp is
select * from emp for update nowait;

Modify cursor data - where currend of clause

declare
  cursor cursor_emp is select * from employee where comm is null for update;
begin 
  for v_emp in cursor_emp loop
    update employee set comm = 500 where current of currsor_emp;
  end loop;
  commit;
end;

Cursor variable

Define cursor reference type syntax

  • The type cursor refers to the type name is ref cursor;

Declare cursor variable syntax

  • Cursor variable name Cursor reference type;

Open cursor variable syntax

  • open cursor variable for query statement;

Retrieving cursor variable syntax

loop
  fetch Cursor variable into variable;
  exit when Cursor variable%notfound;
  ...
end loop;

Dynamic binding through cursor variables

declare
  type ref_cursor_type is ref cursor;
  ref_cursor ref_cursor_type;
  v_emp emp%rowtype;
  v_dept dept%rowtype;
  v_choose varchar2(1):=upper(substr('&D or E',1,1));
begin
  if v_choose='E' then
    open ref_cursor for select * from emp;
    dbms_output.put_line('Employee Information');
    loop
      fetch ref_cursor into v_emp;
      exit when ref_cursor%notfound;
      dbms_output.put_line(v_emp.empno||' '||v_emp.ename);
    end loop;
  close ref_cursor;
  elsif v_choose='D'then
    open ref_cursor for select * from dept;
	dbms_output.put_line('Department Information');
    loop
      fetch ref_cursor into v_dept;
      exit when ref_cursor%notfound;
      dbms_output.put_line(v_dept.deptno||' '||v_dept.dname);
    end loop;
    close ref_cursor;
  else 
    dbms_output.put_line('Please enter E or D'); 
  end if;
end;

PL/SQL Advanced Applications

Creation and management of stored procedure s

create or replace procedure

Various privileges

-create any procedure Any user creates a stored procedure

  • create procedure
  • alter procedure
  • execute any procedure to execute any stored procedure
  • execute procedure
  • drop any procedure

Call Stored Procedure -

  • execute|exec|call

Calls to stored procedures by storage name

create or replace procedure proc_test(p_str1 varchar2, p_str2 varchar2)
as
begin
  dbms_output.put_line(p_str1||p_str2);
end;

declare
  v_var1 varchar2(20):='Hello,';
  v_var2 varchar2(20):='Prodcedure!';
begin
  proc_test(v_var1, v_var2);
end;

Parameter mode

  • In: Numeric transfer, constant or expression, modification in a subroutine will not affect the original value
  • out: Initial value is null and can only be a variable through which the subroutine can return the value to the caller
  • in out: can only be a variable, passing a value to the subroutine, and returning changes to the variable in the subroutine to the caller

in parameter mode

create or replace procedure proc_in(p_empno in number)
as
  v_ename scott.emp.ename%type;
  v_sal scott.emp.sal%type;
begin
  select ename, sal into v_ename, v_sal from scott.emp where empno=p_empno;
  dbms_output.put_line('The employee's name is:'||v_ename||'Wages are:'||v_sal);
exception
  when no_data_found then
    dbms_output.put_line('Employee number not found');    
end;

begin
  proc_in(7369);
end;

out parameter mode

create or replace procedure proc_out(
  p_deptno number,
  p_num out number,
  p_avgsal out number)
as
begin
  select count(*)num, round(avg(sal),2)avgsal into p_num, p_avgsal
  from scott.emp where deptno=p_deptno;
exception
  when no_data_found then
    raise_application_error(-2000,'The department number does not exist');
end;

declare
  v_num number;
  v_avgsal number;
begin
  proc_out(10, v_num, v_avgsal);
  dbms_output.put_line('10 Total number of departments'||v_num||'Average wage is'||v_avgsal);
end;

in out parameter mode

create or replace procedure proc_dept_dname_exist(
  p_io_value in out varchar2) is v_count number;
begin
  select count(*)into v_count from scott.dept where dname=p_io_value; 
  if(v_count>0)then
    p_io_value:='Already exists';
  else
    p_io_value:='Non-existent';
  end if;
end;  

declare
  v_io_value varchar2(20):='ACCOUNTING';
begin
  proc_dept_dname_exist(v_io_value);
  dbms_output.put_line('Department Name ACCOUNTING'||v_io_value||'!');
end;

View stored procedures and their source information

View subprogram information for the current user - user_procedures: View all stored procedures, function information for the current user

select object_name, object_type from user_procedures;

View stored procedure definition content - user_source: View source code for all objects of the current user

select name, text from user_source where type='PROCEDURE';
  • user_errors: View error information for all current stored procedures or functions

Dependency of Query Storage Procedures on Database Objects - user_dependencies

select name, type, referenced_name from user_dependencies
where referenced_name='EMP'or referenced_name='DEPT';

Recompile stored procedure - alter procedure...compile statement

alter procedure proc_dept_insert compile;

Delete Stored Procedure - drop procedure

drop procedure proc_test;

Creation and management of function s

Create a parameterless function -

create or replace function func_hello
return varchar2
as
begin
  return'hello function!';
end;

Create a parameterized function -

create or replace function func_get_dname(p_deptno dept.deptno%type)
return varchar2
as
  v_dname dept.dname%type;
begin
  select dname into v_dname from scott.dept where deptno=p_deptno;
  return v_dname;
end;

Call function

Called in a SQL statement

select func_hello from dual;

select func_get_dname(10)from dual;

Called in PL/SQL block

declare
  v_info varchar2(100);
begin
  v_info:=func_hello;
  dbms_output.put_line('Return:'||v_info);
end;

declare
  v_no number;
  v_info varchar2(50);
begin
  v_no:=&no;
  v_info:=func_get_dname(v_no);
  dbms_output.put_line('Department name:'||v_info);
end;

with function to create a simple function

with function fun_isnumber(param in varchar2)
return varchar2
is
begin 
  if(to_number(param)is not null)then
    return 'Y';
  else
    retuen 'N';
  end if;
exception
  when others then 
    return'N';
end;
select fun_isnumber('abcd')from dual;

View all functions and sources for the current user - user_source

select name, text from user_source where type='FUNCTION';

Function Recompilation

alter function func_get_dname compile;

Delete function

drop function func_hello;

trigger

  • DML Trigger
  • Instead of Triggers
  • system trigger

dml trigger

  • before|after: Trigger before|Trigger after

  • for each row: row-level triggers

Create a statement-level dml trigger for emp tables, requiring dml operations on emp tables to be prohibited during nonworking hours

create or replace trigger trig_emp_dml
  before insert or update or delete on scott.emp
begin
  if(to_char(sysdate,'DAY'))in('Saturday','Sunday')
    or(to_char(sysdate, 'HH24:MI')not between'08:30'and'17:30')then
    raise_application_error(-20002,'Just work during working hours.');
  end if;
end trig_emp_dml;

Create a statement-level dml trigger with scott user to verify permission that only scott user can dml emp table

create or replace trigger trig_emp_authority
  before insert or update or delete on emp
begin
  if user <> 'SCOTT' then
    raise_application_error(-20001,'You are not authorized to operate emp surface')
  end if;
end trig_emp_authority;

row-level trigger

  • :old. :new.

Create a row-level trigger to restrict the update of emp payroll, requiring no more than 10% raise

create or replace trigger trig_emp_addsal
  before update of sal on emp
  for each row
declare
  v_scale number;
begin
  v_scale:=(:new.sal-:old.sal)/:old.sal;
  if v_scale>0.1 then
    :new.sal:=:old.sal*1.1;
	dbms_output.put_line('Pay raise cannot exceed 10%,Salary Update:'||: new.sal);
  end if;
end;

Trigger predicates inserting, updating, deleting

Performs an operation logging function on Dept tables, which are automatically saved in dept_log, the log record table of dept, when the user operates on the dept table

create table dept dept_log(
  logid number,
  type varchar2(20)not null,
  logdate date,
  deptno number(2),
  dname varchar2(20)not null,
  loc varchar2(30)not null,
  constraint pk_logid primary key(logid));

create sequence seq_dept_log;

create or replace trigger trig_dept_dml
before insert or update or delete on dept
for each row
begin
  when inserting then
    insert into dept_log(log, type, logdate, deptno, dname, loc)
	  values(seq_dept_log.nextval,'INSERT',
	  sysdate,:new.deptno,:new,dname,:new.loc);
  when updating then
    insert into dept_log(log, type, logdate, deptno, dname, loc)
	  values(seq_dept_log.nextval,'UODATE',
	  sysdate,:new.deptno,:new,dname,:new.loc);
  when deleting then
    insert into dept_log(log, type, logdate, deptno, dname, loc)
	  values(seq_dept_log.nextval,'DELETE',
	  sysdate,:new.deptno,:new,dname,:new.loc);
  end case;

end;

Execution order:

Before statement - > before row
 ->Update Action
 ->after row->after statement

The follows clause specifies the order in which triggers are executed

create or replace trigger dept_insert_one
before insert on dept 
for each row
begin
  dbms_output.put_line('Execute the first trigger');
end;

create or replace trigger dept_insert_two
before insert on dept
for each row
follows dept_insert_one
begin
  dbms_output.put_line('Execute the second trigger');
end;

Composite trigger:

  • before statement is
  • before each row is before line
  • after statement is
  • after each row is line

Use of composite triggers

Alternative trigger instead of: A trigger built on a view

Create alternate triggers to attempt deletion of data

system trigger

  • on schema: represents a trigger to a specific pattern
  • on database: database-level triggers require administrator privileges

Logging for ddl operations on all schema objects in the database

create table operate_log(
  logid number constraint pk_logid primary key, --Primary key identification
  operater varchar2(50),  --Operator Name
  operate_date date,  --Operation time
  object_name varchar2(50),  --Object Name
  object_type varchar2(50),  --object type
  object_owner varchar2(50)  --Object Owner Name
  );
create sequence seq_operate_log;
  
create or replace trigger trig_object_ddl
  after ddl on database
begin
  insert into operate_log(logid, operate_date, object)
end;

Create a system trigger to monitor user logon and logoff

create table 


create or replace trigger trig_userlogon
  after logon

System triggers to monitor user logoff

Trigger Query

  • user_triggers: Current
  • all_triggers: Accessible
  • bda_triggers: All

Disable triggers

alter trigger trig_object_ddl disable;

Recompile triggers

alter trigger trig_object_ddl compile;

Delete trigger

drop trigger trig_object_ddl;

A key:

  • What is a database, a relational database
  • Creation and management of tablespaces
  • User creation, management
  • Granting and Recycling Permissions
  • surface
  • Four Features of Transactions
  • Transaction Concurrency
  • Isolation level of database
  • query
  • Patterns, Views, Sequences
  • PL\SQL Syntax

Output all employee information higher than the average salary in this department

create or replace procedure p_test(v_deptno in number)
as
  cursor cursor_emp is select empno, ename, sal from emp where sal > (select avg(sal) from emp where deptno=v_deptno) and deptno = v_deptno;
  v_empno emp.empno%type;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
  a_sal number;
begin
  select avg(sal)into a_sal from emp where deptno=v_deptno;
  dbms_output.put_line('Average of this department is:'||a_sal);
  dbms_output.put_line('Employees whose wages are higher than the average in their department are');
  open cursor_emp; 
  loop
    fetch cursor_emp into v_empno, v_ename, v_sal;  
    exit when cursor_emp%notfound;
	dbms_output.put_line('Employee number:'||v_empno||' Full name:'||v_ename||' wages:'||v_sal);
  end loop;
  close cursor_emp;
end;

begin
  p_test(10);
end;

Tags: Database SQL Session Stored Procedure

Posted on Sun, 10 May 2020 00:00:11 -0700 by sheila