Procedure and function programming in PL/SQL

Procedure and function programming procedure in PLSQL: Stored procedure eg: createorreplaceprocedurep -- here is the difference between iscurso and plsql

Procedure and function programming procedure in PL/SQL: Stored procedure eg: create or replace procedure p -- the difference between this and plsql is curso

Procedure and function programming in PL/SQL

[Date: 2013-06-11] Source: Linux community Author: zhaoming [Font:]

Procedure and function programming in PL/SQL

Procedure: Stored procedure

Eg:

Create or replace procedure p -- difference between this and plsql

Is

Cursor c

Is

Select * from emp2 for update;

-- V_temp c % rowtype;

Begin

For v_temp in c

Loop

If (v_temp.sal <2000) then

Update emp2 set sal = sal * 2 where current of c;

Elsif (v_temp.sal = 5000) then

Delete from emp2 where current of c;

End if;

End loop;

Commit;

End;

Command for executing the process: exec p; begin p end;

Stored Procedures with parameters;

Create or replace procedure p -- in indicates that the input parameter out is passed ,,

(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_a;

Else

V_ret: = v_ B;

End if;

V_temp: = v_temp + 1;

End;

Call process:

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;

Function: function

Create or replacefunctionsal_tax

(V_sal number) -- declares a function

Return number

Is

Begin

If (v_sal <2000) then

Return 0.10;

Elsif (v_sal <2750) then

Return 0.15;

Else

Return 0.20;

End if;

End;

Function call: select lower (ename), sal_tax (sal) from emp;

Trigger: trigger

Cannot be executed directly, depending on the table

Create table emp2_log

(

Uname varchar2 (20 ),

Action varchar2 (10 ),

Atime date

);

Create a trigger

Create or replacetrigger trig

After insert or delete or update on emp2 for each row

Begin

Ifinserting then

Insert into emp2_log values (USER, 'insert', sysdate );

Elsifupdating then

Insert into emp2_log values (USER, 'update', sysdate );

Elsif deleting then

Insert into emp2_log values (USER, 'delete', sysdate );

End if;

End;

Update emp2 set sal = sal * 2 where deptno = 30;

Drop triggle trig; (delete)

Create or replace trigger trig

After update on dept

For each row

Begin

End;

Update dept set deptno = 99 where deptno = 10;

Storage of tree structure on display:

Create table ariticle

(

Id number primary key,

Cont varchar2 (4000 ),

Pid number,

Isleaf number (1), -- 0 indicates a non-leaf node, and 1 indicates a leaf node

Alevel number (2)

);

Insert into ariticle values (1, 'ahshdhshd', 0, 0 );

Insert into ariticle values (2, 'bhdhshd', 1, 0, 1 );

Insert into ariticle values (3, 'chshdhshd', 2, 1, 2 );

Insert into ariticle values (4, dhshdhshd ', 2, 0, 2 );

Insert into ariticle values (5, 'ehshdhshd', 4,1, 3 );

Insert into ariticle values (6, 'fhshdhshd', 10, 1 );

Insert into ariticle values (7, 'ghshdhshd', 6,1, 0 );

Insert into ariticle values (8, 'hhdhshd', 3, 0, 4 );

Insert into ariticle values (9, 'hshdhshd', 7,0, 0 );

Insert into ariticle values (10, 'jhshdhshd', 9, 0, 3 );

Commit; (submit)

Create or replace procudure p (v_pid ariticle. pid % type, v_level binary_integer)

Is cursor c

Is select * from ariticle where pid = v _ pid;

V_preStr varchar2 (1024): = '';

Begin

For I in 0 .. v_level loop

V_preStr: = v_preStr | '';

For v_ariticle in c

Loop

Dbms_output.put_line (v_preStr | v_ariticle.cont );

If (v_ariticle.isleaf = 0) then

P (v_ariticle.id, v_level + 1 );

End if;

End loop;

End;

Exec p (0 );