PLSql statement Learning (2)

Application of SQL statements in plsql
The select statement does not need a cursor. It must have an into statement and ensure that there is only one record.
Declare
V_ename emp. ename % Type;
V_sal emp. sal % Type;
Begin
SelectEname, sal Into V_ename, v_salFrom emp where empno = 9999;
Dbms_output.put_line (v_ename | ''| v_sal );
End;

Declare
V_deptno emp2.deptno% Type: = 50;
Begin
Update emp2 set sal = sal/2 where deptno = v_deptno;
Dbms_output.put_line (SQL % rowcount| 'Affected records ');
Commit;
End;
-- The SQL keyword indicates the SQL statement that has just been executed.% Rowcount is an SQL attribute that indicates how many records are affected.

------------ DDL statements are as follows ..
Begin
EXECUTE IMMEDIATE'Create table t (nnn varchar (20) default ''aa '')';
End;

Note: execute immediate is required for DDL statements.

Bytes -------------------------------------------------------------------------------------
---------------- Plsql loop... branch .......
-- If statement
--- Get the salary of 7369. If the salary is less than 1200, 'low' is output. If the salary is less than 2000, 'middle' is output; otherwise, 'high' is output'
Declare
V_sal emp. sal% Type;
Begin
Select sal into v_sal from emp where empno = 7369;
If(V_sal <1, 1200)Then
Dbms_output.put_line ('low ');
Elsif(V_sal <1, 2000)Then--- Note that elsifNot else if
Dbms_output.put_line ('middle ');
ElseDbms_output.put_line ('high'); --- no then after else
End if;
End;

----- Loop ---------------------------------------
-- Do while LOOP
Declare
I binary_integer: = 1;
Begin
Loop
Dbms_output.put_line (I );
I: = I + 1;
Exit when(I> = 11 );
End loop;
End;
----- While LOOP
 
Declare
I binary_integer: = 1;
Begin
WhileI <11Loop
Dbms_output.put_line (I );
I: = I + 1;
End loop;
End;

--------- For Loop
Begin
ForKIn1. 10Loop
Dbms_output.put_line (I );
End loop;

---- Descending order from 10 to 1
ForKInReverse 1 .. 10Loop
Dbms_output.put_line (I );
End loop;
End;

---------- Error handling
Declare
V_temp number (4 );
Begin
SelectCmpnoIntoV_temp from emp where deptno = 10; -- deptno = 10 multiple records will be returned
Exception
When Too_many_rows Then---- The Exception name returned for multiple records is too_many_rows.
Dbms_output.put_line ('Too many records ');
When others then
Dbms_output.put_line ('error ');
End;

Declare
V_temp number (4 );
Begin
Select cmpno into v_temp from emp where deptno = 10; -- deptno = 10 multiple records will be returned
Exception
When No_data_found Then--- Data Exception not found
Dbms_output.put_line ('no data ');
End;

---- Create an error log table first when DBAs normally record errors
Create table errorlog
(
Id number primary key,
Errcode number, --- error code
Errmeg varchar2 (1024), -- error message
Errdate date -- error time
);
-- Auto increment of primary keys
Create sequence seq_errorlog_id start 1 increment by 1;

-----------
Declare
V_teptno dept. deptno % type: = 10;
V_errcode number;
V_errmsg varchar2 (1024 );
Begin
Delete from dept where deptno = v_deptno;
Commit;
Exception
When others then
Rollback;
V_errcode: = SQLCODE; --- SQLCODE is an oracle keyword indicating an error code
V_errmsg: = SQLERRM; --- SQLERRM is an oracle keyword indicating an error message.
Insert into errorlog values (seq_errorlog_id.nextval, v_errcode, v_errrmsg, sysdate );
Commit;
End;

 
----------- The cursor is a pointer pointing to a result set, just as the iterator first points to the top of the result set
Declare
CursorC Is
Select * from emp; --- declare a cursor. At this time, ORACLE does not execute SELECT, but only declares
V_empC % rowtype;
Begin
Open C; -- SELECT is executed only when the cursor is opened in ORACLE.
Fetch CIntoV_emp; -- take a data cursor from the cursor. The cursor automatically moves one cell down. The meaning of fetch removal.
Dbms_output.put_line (v_emp.ename );
Close C;
End;

Declare
Cursor CIs
Select * from emp;
V_empC% Rowtype;
Begin
OpenC;
Loop
FetchCIntoV_emp;
Exit when(C % notfound );
Dbms_output.put_line (v_emp.ename );
End loop;
CloseC;
End;

---- While LOOP
Declare
Cursor c is
Select * from emp;
V_emp c % rowtype;
Begin
Open c;
Fetch c into v_emp;
While (c % found) loop
Dbms_output.put_line (v_emp.ename );
End loop;
Close c;
End;
------------ For loop does not need to define v_emp or open or close the cursor. The system automatically helps you do so.
Declare
Cursor C Is
Select * from emp;
Begin
ForV_empIn C Loop
Dbms_output.put_line (v_emp.ename );
End loop;
End;

------------ A cursor with Parameters

Declare
Cursor C(V_dempno emp. deptno % type, v_job emp. job % type)
Is
Select ename, sal from emp where deptno = v_dempno and job = v_job;
Begin
ForV_empInC (30, 'cler ')Loop
Dbms_output.put_line (v_emp.ename );
End loop;
End;

----- Updatable cursors generally traverse the result set in the SELECT statement and there is an uncommon cursors.
----- You can modify, delete, and other operations... the keyword forupdate-> to update the current of c-> the row where the current cursor is located
Declare
Cursor C is
Select * from emp;
V_emp c % rowtype;
Begin
ForV_tempInCLoop
If(V_temp.sal <2000)Then
Update emp2 set sal = sal * 2 whereCurrent of c;
Elsif(V_temp.sal = 5000) then ---- = here is the equal sign value: =
Delete from emp2 whereCurrent of c;
End if;
End loop;
Commit;
End;

---------------- The difference between a stored procedure and a general PLSQL block is ------------------------
--- Create or replace procedure p is replaced by declare

Create or replace procedure p
Is
Cursor c is
Select * from emp2 for update;
Begin
For v_emp in c loop
If (v_emp.deptno = 10) then
Update emp2 set sal = sal + 10 where current of c;
Elsif (v_emp.deptno = 10) then
Update emp2 set sal = sal + 20 where current of c;
Else
Update emp2 set sal = sal + 40 where current of c;
End if;
End loop;
Commit;
End;
-- Two methods are available for execution.
-- 1 exec p;
--- 2 begin
P;
End;
------------- Stored Procedure Parameter type with parameters in incoming outgoingIf this parameter is not specified, the default value is in.
Create or replace procedure p
(V_a in number, v_ B number, v_ret out number, v_temp in out number)
Is
Begin
If (v_a> v_ B) then
V_ret: = v_ B; -- assigns the passed value to the outgoing value v_ret.
Else
V_ret: = v_ B;
End if;
V_temp: = v_temp + 1; -- v_temp is input and output, so you can assign values to yourself.
End;
--- Call
Declare
V_a number: = 3;
V_ B number: = 4;
V_ret number;
V_temp number: = 5;
Begin
P (v_a, v_ B, v_ret, v_temp );
Dbms_output.put_line (v_ret );
Dbms_output.put_line (v_temp );
End;

--- Note that an error occurred while creating the stored procedure. The system will not tell you where the error occurred. It will only warn that the creation process has a compilation error.
-- If you want to know the location and cause of the error, enter the command: show error

---------------- The function must have a return value --------
Create or replace function sal_tax
(V_sal number) return number
Is
Begin
If (v_sal <2000) then
Return 0.1;
Elsif (v_sal <2750) then
Return 0.15;
Else
Return 0.20;
End if;
End;

-- Call a function
Select sal_tax (sal) from emp;

----------------- TriggerThe trigger cannot be executed independently and must be attached to a table.
Create table emp2_log
(
Uname varchar2 (20 ),
Action varchar2 (10 ),
Atime date
); ----- This table is used to record the table by using a trigger.

Create or replace triggerTrig
After insert or delete or update OnEmp2For each row-- This trigger is triggered when an update is inserted to the table emp2.
-- Here, the status is after or berore, indicating that the action is followed or before
Begin
If inserting then-- The inserting keyword indicates that the insert operation is currently in progress.
Insert into emp2 values (USER, 'insert', sysdate); -- USER is a keyword that indicates who the current USER is.
Elsif updating then-- The updating keyword indicates that the update operation is currently in progress.
Insert into emp2 values (USER, 'update', sysdate );
Elsif deleting then-- The ing keyword indicates that the operation is being deleted.
Insert into emp2 values (USER, 'delete', sysdate );
End if;
End;
-- Note that for each row triggers each row, for example, six triggers are triggered when six rows are updated.
-- If you do not add for each row to update 6 rows, only one trigger is triggered.
-- Use a trigger. This trigger is triggered when an update is inserted and deleted in the table emp2.
Update emp2 set sal = sal * 2 where deptno = 30;

-------------- Trigger side effects
For example, update dept set deptno = 99Where deptno = 10;
This SQL statement cannot be executed, but the trigger can be implemented.

Create or replace trigger trig
After update on dept for each row
Begin
Update emp set deptno =: NEW. deptno where deptno =: OLD. deptno;
End;

----
It is known that the trigger is triggered first, and then the integrity constraints are checked.

 

----------- Storage and display of Tree Structure
That is, the dictionary table has a parent ID.
Create table article
(
Id number primary key,
Cont varchar2 (4000 ),
Pid number, --- parent ID
Isleal number (1), -- 0 represents leaf node 1 represents child node
Vlevel number (2) --- indicates the layer at which
);

Insert into article values (1, 'Ant station elephant ', 0 );
Insert into article values (2, 'elephant under fear ', 1, 0, 1 );
Insert into article values (3, 'Ant is not good either ', 2 );
Insert into article values (4, 'gibberish ', 2, 0, 2 );
Insert into article values (5, 'Nothing nonsense ', 3 );
Insert into article values (6, 'How is it possible ', 1 );
Insert into article values (7, 'How impossible? ', 6, 1, 2 );
Insert into article values (8, 'highly probable ', 6, 1, 2 );
Insert into article values (9, 'elephant dead ', 2, 0, 2 );
Insert into article values (10, 'Ant doctor ', 9, 1, 3 );
----- Use a stored procedure to call itself to implement a recursive tree

Create or replace procedure p (v_pid article. pid % type, v_level binary_integer)
Is
Cursor c is select * from article where pid = v_pid;
V_prestr varchar2 (1024): = '';
Begin
For I in 1 .. v_level loop
V_prestr: = v_prestr | '********';
End loop;

For v_article in c loop
Dbms_output.put_line (v_prestr | v_article.cont );
If (v_article.isleal = 0) then
P (v_article.id, v_level + 1 );
End if;
End loop;
End;

Execute: exec p (0, 0 );