Oracle's third day

Helloworld.sql

Set Serveroutput on

Declare
--Description section
Begin
--Program
Dbms_output.put_line (' Hello world ');
End
/

If statement. sql

--Determine the number of user input

Set Serveroutput on

--Receive keyboard input
--num: Address value, at which the input value is stored
Accept num Prompt ' Please enter a number ';

Declare
--Define the variable to hold the input number
--Implicit conversion
Pnum Number: = #
Begin

If Pnum = 0 Then Dbms_output.put_line (' You are entering 0 ');
elsif pnum = 1 Then dbms_output.put_line (' You have entered 1 ');
elsif pnum = 2 Then Dbms_output.put_line (' You have entered 2 ');
else Dbms_output.put_line (' other numbers ');
End If;
End
/

The cursor with parameters. sql

--Query the names of employees in a department

Set Serveroutput on

Declare
--Cursors with parameters
Cursor Cemp (DNO number) is a select ename from emp where Deptno=dno;
Pename Emp.ename%type;
Begin
Open Cemp (20);
Loop
Fetch cemp into pename;
Exit when Cemp%notfound;

Dbms_output.put_line (Pename);

End Loop;
Close Cemp;
End
/

Raise wages for employees. SQL

--Chief executive Officer 1000 800 other 400

Set Serveroutput on

Declare
Cursor Cemp is a select empno,empjob from EMP;
Pempno Emp.empno%type;
Pjob Emp.empjob%type;
Begin
Rollback

Open cemp;
Loop
--Take an employee to pay a raise
Fetch cemp into pempno,pjob;
Exit when Cemp%notfound;

--Judging the position
If Pjob = ' president ' then update EMP set sal=sal+1000 where Empno=pempno;
elsif pjob = ' MANAGER ' then update emp set sal=sal+800 where Empno=pempno;
Else update emp set sal=sal+400 where Empno=pempno;
End If;

End Loop;

Close Cemp;

--The isolation level of the transaction
Commit

Dbms_output.put_line (' Wage increase completed ');
End
/

Cursor

--Use Cursors to query employee names and wages, and print

/*
1. Properties of the cursor
%isopen: whether to open
%rowcount: Number of rows
%notfound: No record

2. Default allows 300 cursors to be opened at once (Modify cursor: Fourth day management scenario)
Sql> Show Parameters Cursor

NAME TYPE VALUE
------------------------------------ ----------- -------
Cursor_sharing string EXACT
Cursor_space_for_time Boolean FALSE
Open_cursors Integer 300
Session_cached_cursors Integer 20
*/

Set Serveroutput on

Declare
--Define the cursor to represent the employee collection
Cursor Cemp is a select ename,sal from EMP;
Pename Emp.ename%type;
Psal Emp.sal%type;
Begin
Open cemp;

Loop
--Take one of the employees
Fetch cemp into pename,psal;

--Exit conditions
Exit when Cemp%notfound;

Dbms_output.put_line (pename| | ' The salary is ' | | PSAL);

End Loop;

Close Cemp;
End
/

Record type variable

----Query and print 7839 of your name and salary

Set Serveroutput on

Declare
--Record type variable represents a row
Emp_rec Emp%rowtype;
Begin

SELECT * into Emp_rec from EMP where empno=7839;

Dbms_output.put_line (emp_rec.ename| | ' The salary is ' | | Emp_rec.sal);
End
/

Example 1

/*
Example 1: Count the number of employees who enter the year.

SQL statements
1. Select To_char (hiredate, ' RR ') from EMP; -----loop (NotFound)
2. Count80 number: = 0;
Count81 number: = 0;
count82 number: = 0;
count87 number: = 0;
*/
Set Serveroutput on

Declare
Cursor Cemp is a select To_char (hiredate, ' RR ') from EMP;
Phiredate VARCHAR2 (4);

--Counter
Count80 number: = 0;
Count81 number: = 0;
count82 number: = 0;
count87 number: = 0;
Begin
Open cemp;
Loop
--Take one of the employees
Fetch cemp into phiredate;
Exit when Cemp%notfound;

--Judging year
If phiredate = ' + ' then count80:=count80+1;
elsif phiredate = ' Bayi ' then count81:=count81+1;
elsif phiredate = ' count82:=count82+1 ' then;
else count87:=count87+1;
End If;

End Loop;
Close Cemp;

Dbms_output.put_line (' total: ' | | (count80+count81+count82+count87));
Dbms_output.put_line (' 80: ' | | COUNT80);
Dbms_output.put_line (' 81: ' | | COUNT81);
Dbms_output.put_line (' 82: ' | | COUNT82);
Dbms_output.put_line (' 87: ' | | COUNT87);
End
/

Example 2

/*
A long salary for employees. From the minimum wage to 10% per person, but the total wage cannot exceed 50,000 yuan,
Please calculate the number of people with long wages and the total wage after the long wages, and output the number of long wages and payroll.

SQL statements:
1. Select Empno,sal from emp ORDER by sal;--> Cursor--loop (1. > 5w 2. Up)
2. Countemp number: = 0;
3. Gross salary after long wages: *. Select sum (SAL) from EMP
*. After rising = +sal*0.1 (*) before rising

Exercise: Salary <5w
*/
Set Serveroutput on
Declare
Cursor Cemp is a select empno,sal from emp order by Sal;
Pempno Emp.empno%type;
Psal Emp.sal%type;

--Number
Countemp number: = 0;

--Payroll
Saltotal number;
Begin
--The initial payroll
Select sum (SAL) into the saltotal from EMP;

Open cemp;
Loop
--First exit condition
Exit when saltotal> 50000;

--Take one of the employees
Fetch cemp into pempno,psal;

--A second exit condition
Exit when Cemp%notfound;

--Pay rise
Update emp Set sal =sal *1.1 where empno=pempno;

--Number
Countemp: = countemp+1;


--Payroll
Saltotal: = saltotal + psal *0.1;
End Loop;
Close Cemp;

Commit

Dbms_output.put_line (' Number of people: ' | | countemp| | ' Total wages: ' | | Saltotal);

End
/

Example 3

/*
Write a program in PL/SQL language, achieve by department segment (more than 6000, (6000,3000), less than 3000 yuan)
Statistics on the number of employees in each wage section, and the total payroll of each department (excluding bonuses in payroll)

SQL statements
1. Department: Select Deptno from dept; ----loop
2. Salary of employee in department: Select Sal from emp where deptno=??? --with parameter cursor--loop
3. Count1 number; Count2 number; COUNT3 number;
4. Department Payroll: Saltotal number;
Select sum (SAL) into Saltotal from EMP where deptno=???
*/
Set Serveroutput on

Declare
--Department
Cursor Cdept is a select Deptno from dept;
Pdeptno Dept.deptno%type;

--salaries of employees in the department
Cursor Cemp (DNO number) is a select Sal from EMP where Deptno=dno;
Psal Emp.sal%type;

--Counter
COUNT1 number; Count2 number; COUNT3 number;

--the payroll of the Department
Saltotal number;
Begin
Open cdept;
Loop
-Take a department
Fetch cdept into Pdeptno;
Exit when Cdept%notfound;

--Initialization
count1:=0;count2:=0;count3:=0;

--the payroll of the Department
Select sum (SAL) into Saltotal from EMP where Deptno=pdeptno;

--salaries of employees in the department
Open Cemp (PDEPTNO);
Loop
--Take an employee's salary CL
Fetch cemp into psal;
Exit when Cemp%notfound;

--Judgment
if psal< and count1:=count1+1;
elsif psal>=3000 and psal<6000 then count2:=count2+1;
else count3:=count3+1;
End If;

End Loop;
Close Cemp;

--Save Current results
INSERT into MSG1 values (PDEPTNO,COUNT1,COUNT2,COUNT3,NVL (saltotal,0));

End Loop;
Close cdept;

Commit

Dbms_output.put_line (' Finish ');
End
/

System exceptions

--by 0 except

Set Serveroutput on

Declare
Pnum number;
Begin
Pnum: = 1/0;

exception
When Zero_divide then Dbms_output.put_line (' 1:0 cannot do dividend ');
Dbms_output.put_line (' 2:0 cannot do dividend ');
When Value_error then Dbms_output.put_line (' Arithmetic or conversion error ');
When others and then Dbms_output.put_line (' other exceptions ');
End
/

--Print 1~10

Set Serveroutput on

Declare
Pnum number: = 1;
Begin
Loop
--Exit loop
Exit when Pnum > 10;

Dbms_output.put_line (Pnum);

--Plus One
Pnum: = Pnum + 1;

End Loop;
End
/

Reference type variable

--Query and print 7839 of your name and salary

Set Serveroutput on

Declare
--Define the reference type variable
Pename Emp.ename%type;
Psal Emp.sal%type;
Begin

--Query 7839 's name and salary
Select Ename,sal to Pename,psal from EMP where empno=7839;

--Print
Dbms_output.put_line (pename| | ' The salary is ' | | PSAL);
End
/

Custom exceptions

--Query staff in Department 50th

Set Serveroutput on

Declare
Cursor Cemp is a select ename from emp where deptno=50;
Pename Emp.ename%type;

--Custom exceptions
No_emp_found exception;
Begin
Open cemp;

--Take one of the employees
Fetch cemp into pename;

If Cemp%notfound Then
--Throw exceptions
Raise No_emp_found;
End If;

--When thrown exception, auto close
Close Cemp;

exception
When No_emp_found and then Dbms_output.put_line (' No employees found ');
When others and then Dbms_output.put_line (' other exceptions ');
End
/

Out parameters

/*
create [or replace] PROCEDURE procedure name (parameter list)
As
Plsql Sub-program body;
Query and return an employee's name monthly salary and position

Thinking: Too many out parameters???
*/
Create or Replace procedure Queryempinfo (Eno in number,
Pename out VARCHAR2,
Psal out number,
Pjob out VARCHAR2)
As
Begin
Select Ename,sal,empjob to Pename,psal,pjob from EMP where Empno=eno;

End
/

Trigger Scenario Two

/*
Data validation

The salary after the rise is not less than the salary before the rise
CREATE [or REPLACE] TRIGGER trigger Name
{before | After}
{DELETE | INSERT | UPDATE [of column name]}
On table name
[For each ROW [when (condition)]]
Plsql Block

*/
Create or Replace Trigger Checksal
Before update
On EMP
For each row
Begin
--if salary after the rise < salary before the rise
If:new.sal <: Old.sal Then
Raise_application_error (-20002), ' The salary after the rise cannot be less than the pre-rise salary. Before the rise: ' | |:o ld.sal| | ' After the rise: ' | |:new.sal ';
End If;
End
/

Trigger Application Scenario One

/*
Implement a complex security check

Prohibit inserting data into the EMP table during non-business hours
CREATE [or REPLACE] TRIGGER trigger Name
{before | After}
{DELETE | INSERT | UPDATE [of column name]}
On table name
Plsql Block

Weekends: To_char (sysdate, ' Day ') in (' Saturday ', ' Sunday ')
Before work: To_number (To_char (sysdate, ' hh24 ')) not between 9 and 18
*/
Create or Replace Trigger Securityemp
Before insert
On EMP
Begin
If To_char (sysdate, ' Day ') in (' Saturday ', ' Sunday ') or
To_number (To_char (sysdate, ' hh24 ')) not between 9 and

Raise_application_error (-20001, ' cannot insert data during non-working hours ');

End If;
End
/

Storage functions

/*
CREATE [OR REPLACE] Function name (parameter list)
RETURN function Value type
As
Plsql Sub-program body;

Query the annual income of an employee

*/
Create or Replace function Queryempincome (Eno in number)
return number
As
Psal Emp.sal%type;
Pcomm Emp.comm%type;
Begin

Select Sal,comm to Psal,pcomm from EMP where Empno=eno;

Return PSAL*12+NVL (pcomm,0);
End
/

Stored procedure with parameters

/*
create [or replace] PROCEDURE procedure name (parameter list)
As
Plsql Sub-program body;

Up to 100 bucks for a given employee and print a pre-and post-rise salary
*/
Create or Replace procedure raisesalary (Eno in number)
As
Psal Emp.sal%type;
Begin
-Pre-rise salary
Select Sal into Psal from EMP where Empno=eno;

--Up 100
Update emp set sal=sal+100 where Empno=eno;

--Do you want to commit???

Dbms_output.put_line (' Pre-rise: ' | | psal| | ' After the rise: ' | | (psal+100));

End
/

First Trigger

/*
After the employee is successfully inserted, the automatic output "insert a new employee successfully"
CREATE [or REPLACE] TRIGGER trigger Name
{before | After}
{DELETE | INSERT | UPDATE [of column name]}
On table name
Plsql Block

*/
Create or Replace Trigger Saynewemp
After insert
On EMP
Begin
Dbms_output.put_line (' Insert a new employee successfully ');
End
/

First stored procedure

/*
create [or replace] PROCEDURE procedure name (parameter list)
As
Plsql Sub-program body;

Print Hello World

Call a stored procedure
1. exec SayHelloWorld ();
2. Begin
SayHelloWorld ();
SayHelloWorld ();
End
/
*/

Create or replace procedure SayHelloWorld
As
--Description section
Begin
Dbms_output.put_line (' Hello world ');

End
/

Oracle's third day