PL/SQL example

The following are the tests that can run correctly:

 

Create Table t_emp_log212
(
Who varchar2 (10) Not null,
Action varchar2 (10) Not null,
Actime date
);

Create or replace trigger tri_emp_insert
Before insert
On t_emp
Begin
Insert into t_emp_log (WHO, action, actime) values (user, 'insert', sysdate );
End;
Bytes -------------------------------------------------------------------------------------------

Set serveroutput on
Declare
V_empno EMP. empno % Type: = & empno;
V_ename EMP. ename % type;
Begin
Select empno, ename into v_empno, v_ename from EMP where empno = v_empno;
Dbms_output.put_line (v_ename );

Exception
When others then
Dbms_output.put_line ('your empno is null, ename is null ');
End;
Bytes -------------------------------------------------------------------------------------------

Set serveroutput on
Declare
Type tabletype1 is table of varchar2 (10) index by binary_integer;
Type tabletype2 is table of varchar2 (10) index by binary_integer;
Table1 tabletype1;
Table2 tabletype2;
Begin
Table1 (1): = '';
Table1 (2): = 'Junior College ';
Table2 (1): = 88;
Table2 (2): = 55;
Dbms_output.put_line (Table1 (1) | Table2 (1 ));
Dbms_output.put_line (Table1 (2) | Table2 (2 ));
End;

Bytes -------------------------------------------------------------------------------------------

Set serveroutput on;
Declare
V_grade char (10): = upper ('& p_grade');/* When Upper converts all strings to uppercase, quotation marks are required. Because it is displayed as upper ('A') after the value is assigned ') while upper (a) A is a constant */
V_result varchar2 (20 );

Begin
V_result: =
Case v_grade
When 'a 'then': 90 +'
When 'B' then' between 80 and 90'
When 'C' between 60 and 80'
Else 'failed'
End;
Dbms_output.put_line ('grade '| v_grade | 'result' | v_result );
End;

Bytes -------------------------------------------------------------------------------------------

Set serverout on

Declare
V_item number (3): = 1;
Begin

Loop
Dbms_output.put_line (v_item );
V_item: = v_item + 1;
Exit when v_item> 100;
End loop;
End;
/*
Begin
For v_item in 1 .. 100
Loop
Dbms_output.put_line (v_item );
End loop;

 

End;
*/

Bytes -------------------------------------------------------------------------------------------

Create or replace trigger tir_t_emp2
Before update on t_emp
Referencing new as newrow old as oldrow
For each row
Begin
Insert into emp_new (empno, ename, job, deptno) update set to update a data before the data is updated. The updated data in a table is also placed in a table.
Values
(: Newrow. empno,: newrow. ename,: newrow. Job,: newrow. deptno); the updated data is saved in a new table.

Insert into emp_old (empno, ename, job, deptno)
Values
(: Oldrow. empno,: oldrow. ename,: oldrow. Job,: oldrow. deptno); data before update is saved in a new table.

End tir_t_emp2;

Bytes -------------------------------------------------------------------------------------------

Create or replace trigger tri_emp2
After insert or update or delete
On t_emp
Begin
If updating then
Insert into t_emp_log2 (WHO, action, actime) values (user, 'update', sysdate );
Elsif deleting then
Insert into t_emp_log2 (WHO, action, actime) values (user, 'delete', sysdate );
Elsif inserting then
Insert into t_emp_log2 (WHO, action, actime) values (user, 'insert', sysdate );
End if;
End;

/** Create table t_emp_log2
(
Who varchar2 (10) Not null,
Action varchar2 (10) Not null,
Actime date

);**/

Bytes -------------------------------------------------------------------------------------------

Create or replace trigger tri_emp4
After update of ename
On t_emp
Referencing new as newrow

For each row

Begin
If updating then
Insert into t_emp_log (WHO, action, actime) values (user, 'update', sysdate );
Insert into emp_new (ename) values (: newrow. ename );

End if;
End;

Bytes -------------------------------------------------------------------------------------------

Declare
I number (3): = 1;
J number (3): = 2;
Begin

Loop

 

Insert into t_user values (I, 'A' | I, 'A' | I, 1, to_date ('2017-01-01 ', 'yyyy-mm-dd '), 1, I, 'sdd ');

Insert into t_user values (J, 'A' | j, 'A' | j, 1, to_date ('2017-01-01 ', 'yyyy-mm-dd '), 2, J, 'sdd ');

 

I: = I + 2;
J: = J + 2;
Exit when I> 600 and j> 600;

End loop;

End;

Bytes -------------------------------------------------------------------------------------------

/**
Create view t1_t2
As
Select E. empno, E. ename, E. Job, D. dname from t_emp E, t_dept d Where D. deptno = E. deptno;
**/

 

Create or replace view t1_t2
As
Select E. empno, E. ename, E. Job, D. dname from t_emp E, t_dept d Where D. deptno = E. deptno;
Create or replace trigger t1_t2
Instead of update on t1_t2
Referencing new as newrow
For each row

Begin

Update t_emp e set empno =: newrow. empno, ename =: newrow. ename, job =: newrow. job where empno =: newrow. empno;
/**
Where indicates that if the view updates the name of a person, you must also change the base table. How can you change it? If no WHERE clause is added, the where clause is added? If the view changes the number 1 to the Andy Lau base table to change to Andy Lau, use the WHERE clause to judge that the base table number is 1 and update the ename.
**/

Update t_dept d Set dname =: newrow. dname where deptno = (select deptno from t_emp where empno =: newrow. empno );
End;

Bytes -------------------------------------------------------------------------------------------

Declare
V_funretvalue Boolean;
V_name varchar2 (20 );
Begin

V_name: = '& name ';
V_funretvalue: = check_user (v_name );
If v_funretvalue then
Dbms_output.put_line ('user '| v_name |' ');
Else
Dbms_output.put_line ('user' | v_name | 'nonexistent ');
End if;
End;

Bytes -------------------------------------------------------------------------------------------

Create or replace function check_user
(
V_ename varchar2
)
Return Boolean
As
V_count number;
Begin

Select count (ename) into v_count from EMP where ename = v_ename;
If v_count> 0 then
Return true;
Else
Return false;
End if;
End;

Bytes -------------------------------------------------------------------------------------------

Set serveroutput on;
Declare
T_empno t_emp.empno % type;
Cursor mycursor is
Select E. empno, E. ename from t_emp e where empno> t_empno;
Cursored mycursor % rowtype;
Begin
T_empno: = 1000;
Open mycursor;
If mycursor % isopen then
Fetch mycursor into cursored;
Dbms_output.put_line (to_char (cursored. ename ));
Else
Dbms_output.put_line ('no open !! ');
End if;
While mycursor % found
Loop
Dbms_output.put_line (t_empno | ':' | to_char (cursored. ename ));
If mycursor % rowcount = 4 then
Exit;
End if;
Fetch mycursor into t_empno, cursored. ename;
End loop;
Close mycursor;
End;

Bytes -------------------------------------------------------------------------------------------

Set serveroutput on;

Declare
Eno EMP. empno % type;
E_name EMP. ename % type;
Cursor mycurs1 (var_name varchar2) is
Select E. empno, E. ename from EMP e where E. ename like '%' | var_name | '% ';

Begin
If mycurs1 % isopen = false then
Open mycurs1 ('% A % ');
End if;
Fetch mycurs1 into Eno, e_name;
While mycurs1 % found Loop
Dbms_output.put_line (ENO | ':' | e_name );
If mycurs1 % rowcount = 4 then
Exit;
End if;
Fetch mycurs1 into Eno, e_name;
End loop;
Close mycurs1;
End;

Bytes -------------------------------------------------------------------------------------------

Set serveroutput on;

Declare

Cursor mycurs1 is
Select E. empno, E. ename, E. deptno from t_emp e where E. deptno = 20;

Begin
For employee in mycurs1
Loop
If mycurs1 % isopen then
Dbms_output.put_line ('student ID: '| employee. empno | 'name:' | employee. ename | 'department: '| employee. deptno );
End if;
End loop;
End;

/**
For
Cursor

**/

Bytes -------------------------------------------------------------------------------------------

Create or replace package T_1
Is
Procedure sayhello (v_name varchar2 );
End;
/**

/**
Defining a package process is also defined and does not really assign a process
**/

/**
Create or replace package body T_1
Is
Procedure sayhello (v_name varchar2)/** no additional points **/
Is
Begin
Dbms_output.put_line ('hello' | v_name );
End;
End;
/**
Then define the process in the package;
**/

**/

/**
Create or replace package T_2
Is
Function sayhello2 (v_deptno number );
End; function cannot be used.
**/
/**
Create or replace paclage body T_2
Is
Function sayhello2 (v_deptno varchar2)
Is
Begin
Dbms_output.put_line ('hello' | v_name );
End;
End;
**/
**/

Bytes -------------------------------------------------------------------------------------------

Create Table t_dept
(
Id varchar2 (32) not null,
Dname varchar2 (20) not null,
Constraint pk_dept_id primary key (ID)
);

Create Table t_role
(
Id varchar2 (32) not null,
Rname varchar2 (20) not null,
Constraint pk_role_id primary key (ID)
);
Create Table t_employee
(
Id varchar2 (32) not null,
Ename varchar2 (20) not null,
Esex char (2) not null,
Deptid varchar2 (32) not null,
Roleid varchar2 (32) not null,
Edir_leader varchar2 (20) not null,
Constraint pk_employee_id primary key (ID ),
Constraint fk_employee_deptid foreign key (deptid) References t_dept (ID ),
Constraint fk_employee_roleid foreign key (roleid) References t_role (ID)
);

Create Table t_present
(
Id varchar2 (32) not null,
Pmorning_work date not null,
Pnoon_workout date not null,
Employeeid varchar2 (32) not null,
Constraint fk_present_employeeid foreign key (employeeid) References t_employee (ID)
);

Bytes -------------------------------------------------------------------------------------------

Create sequence seq_db
Start with 1
Increment by 1
Nomaxvalue
Nocycle
Cache 2;
 
Create or replace procedure pro_dept_insert
As
V_id number: = 1;
Begin
Loop
Insert into t_dept (ID, dname) Values
('D000000000' | seq_db.nextval, 'dept' | seq_db.nextval );
V_id: = v_id + 1;
Exit when v_id> 20;
End loop;
End;

Bytes -------------------------------------------------------------------------------------------

Create sequence seq_1_20_2
Start with 1
Increment by 1
Nomaxvalue
Nocycle
Cache 2;
 
Create or replace procedure pro_dept_insert2
As
V_id number: = 1;
Begin
Loop
Insert into t_employee (ID, deptid, roleid) Values
(Seq_000020_2.nextval, 'd0000000001 ', 'r0000000001 ');
V_id: = v_id + 1;
Exit when v_id> 200001;
End loop;

Loop
Insert into t_employee (ID, deptid, roleid) Values
(Seq_000020_2.nextval, 'd0000000002 ', 'r0000000002 ');
V_id: = v_id + 1;
Exit when v_id> 400001;
End loop;

Loop
Insert into t_employee (ID, deptid, roleid) Values
(Seq_000020_2.nextval, 'd0000000003', 'r0000000003 ');
V_id: = v_id + 1;
Exit when v_id> 600001;
End loop;

Loop
Insert into t_employee (ID, deptid, roleid) Values
(Seq_000020_2.nextval, 'd0000000004', 'r0000000004 ');
V_id: = v_id + 1;
Exit when v_id> 800001;
End loop;

Loop
Insert into t_employee (ID, deptid, roleid) Values
(Seq_000020_2.nextval, 'd0000000005 ', 'r0000000005 ');
V_id: = v_id + 1;
Exit when v_id> 1000001;
End loop;
End;

 

 

 

The following are the tests that fail to run properly:

 

Set serveroutput on
Declare
Type table_id is table of EMP. empno % Type Index by binary_integer;
Type table_sal is table of EMP. Sal % Type Index by binary_integer;
Tableid table_id;
Tablesal table_sal;
V_char varchar2 (30 );

Begin

For I in 1 .. 5000 Loop
Tableid (I): = (select empno into tableid from EMP where 1 = 1 );
Tablesal (I): = (select Sal into tablesal from EMP where 1 = 1 );
End loop;

Loop
Case tablesal (I );
I: = 1;
When tablesal (I) <1000 then
V_char: = 'sal is low ';
When tablesal (I)> = 1000 and tablesal (I) <= 3000 then
V_char: = 'sal is normal ';
When tablesal (I)> 3000 then
V_char: = 'sal is high ';
Else v_char: = 'lost data ';
End case;
Exit whenI: = 10000;
I: = I + 1;
End loop;
Dbms_output.put_line ('id' | tableid | 'sal '| v_char );
End;

 

 

 

 

 

 

 

 

 

 

 

 

 

/* Set serverout on;
Declare
V_name EMP. ename % type;
V_sal EMP. Sal % type;
V_char varchar2 (30 );
Begin
Select ename, Sal into v_name, v_sal from EMP;
Loop
 
If v_sal <1000 then
V_char: = 'sal is low ';
Elsif v_sal> = 1000 and v_sal <= 3000 then
V_char: = 'sal is normal ';
Elsif v_sal & gt; 3000 then
V_char: = 'sal is high ';
Else v_char: = 'lost data ';
End if;

Exit when
End loop;
Dbms_output.put_line ('name' | v_name | 'sal '| v_char );
End;
*/

Bytes -------------------------------------------------------------------------------------------

Set serveroutput on
Declare
V_name EMP. ename % type;
V_sal EMP. Sal % tpye: = & Sal;
V_char varchar2 (30 );
Begin
Select ename, Sal into v_name, v_sal from EMP where sal = v_sal;
If v_sal <1000 then
V_char: = 'sal is low ';
Elsif (v_sal> = 1000 and v_sal <= 3000) then
V_char: = 'sal is normal ';
Else v_sal & gt; 3000 then
V_char: = 'sal is high ';
Else v_char: = 'lost data ';
End if;
Dbms_output.put_line ('name' | v_name | 'sal '| v_char );
End;

Bytes -------------------------------------------------------------------------------------------

/** Trigger compilation is normal, but the exception is not displayed for different users.
**/
Create or replace trigger tri_emp_insert
Before Delete
On t_emp
Declare
User varchar2 (15 );
Begin
If (user = 'Scott ') then
Raise_application_error (-20001, 'You cannot access or repair AAA to change this table ');
Insert into t_emp_log (WHO, action, actime) values (user, 'delete', sysdate );

Else
Insert into t_emp_log (WHO, action, actime) values (user, 'delete', sysdate );
End if;

End;

Bytes -------------------------------------------------------------------------------------------

Create or replace trigger tri_emp_insert
Before insert
On t_emp
Begin
If user! = 'Scott 'then
Raise_application_error (-20001, 'You cannot access or repair AAA to change this table ');
End if;
End;

Bytes -------------------------------------------------------------------------------------------

Create or replace procedure P_c
(

V_id in number,
V_name in number,
V_psw in number,
V_address in number,
)
As
O_id number: = 1;
Begin

Loop

Insert into t_user (ID, name, psw, address) Values
('Id' | v_id, 'name' | v_name, 'psw' | v_psw, 'address' | v_address );

O_id: = o_id + 1;

Exit when o_id> 200;

End Loop
End;