PL/SQL tutorial 41: How To Create PL/SQL Stored Procedure Without Parameters in Oracle Database

------------------------------------------------------------------------
►►►LINKS◄◄◄
Previous Tutorial
-------------------------------------------------------------------------
►►►Let's Get Free Uber Cab◄◄◄
Use Referral Code UberRebellionRider and get $20 free for your first ride.

-------------------------------------------------------------------------
►Make sure you SUBSCRIBE and be the 1st one to see my videos!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
►►►Find me on Social Media◄◄◄
Follow What I am up to as it happens on

___Facebook Official Page___

You can also Email me at
for E-mail address please check the "About" section

Please please LIKE and SHARE my videos it makes me happy.
Thanks for liking, commenting, sharing and watching more of our videos

♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS

[bombayboys]

A simple example of a procedure called in Trigger.
Using the example which Manish Sir showed us during Trigger lectures.

1) Table Creation

SQL> create table test_proc(val number);
Table created.

SQL> insert into test_proc values(20);
1 row created.

SQL> insert into test_proc values(30);
1 row created.

SQL> commit;
Commit complete.


2) Procedure Creation

SQL> create or replace procedure DML_PROC as
2 v_user varchar2(30);
3 begin
4 select user into v_user from dual;
5
6 if updating then
7 dbms_output.put_line('Record updated by '||v_user);
8
9 elsif deleting then
10 dbms_output.put_line('Record deleted by '||v_user);
11
12 elsif inserting then
13 dbms_output.put_line('Record inserted by '||v_user);
14
15 end if;
16 end;
17 /

Procedure created.

3) Trigger Creation and Calling the procedure in trigger.

SQL> show user
USER is "PRACTICE"
SQL>
SQL> create or replace trigger DML_TRIG
2 before update or insert or delete on test_proc
3 for each row
4 enable
5
6 begin
7 DML_PROC();
8 end;
9 /

Trigger created.


SQL> select * from test_proc;

VAL

20
30

SQL> insert into test_proc values(40);
Record inserted by PRACTICE
1 row created.

SQL> update test_proc set val=50 where val=20;
Record updated by PRACTICE
1 row updated.

SQL> delete from test_proc where val=50;
Record deleted by PRACTICE
1 row deleted.

[stjepanmudronja]

--Building Procedure

create or replace procedure pr_first IS
var_first varchar2(30) := 'This is my first string!';
begin
dbms_output.put_line (var_first || 'Hurray!');
end pr_first;
/

----Building Trigger

create or replace trigger tr_first
before insert on table_name
for each row
enable
begin
pr_first;
end;
/

--Calling Procedure

Insert into table_name values ('Batman');

Thanks Manish !!!

[bhawnasjourney]

thank you very much now I can execute procedures.It was very important for me to know this .

[GiancarloCarccamo]

thanks for your videos, I think you should make example with relational table, your example is so simple and only learn the sintax, make real example with table with data

[killiantrends]

Like toad we type F4 to check codes of procedure or view, what will be in SQL developer,

[amanchhabra]

I need to write stored procedure for hotel management can u help me with that

[lionelwk]

how to access the hr schema objects via user created procedures?

[kifkifa]

Great tuts, Sir

Kifayat Ullah,
Pakistan.

[potato]

Omg, rebellionRider is a goddnag cool name. Someday, i ill have a child called Rebellion Rider

[darksideofthemoon]

How to write a procedure to insert/delete a record?

[harishs]

Can you call a procedure from function?

[kharchi]

but first let me close the script output <3