CREATE OR REPLACE PROCEDURE UPDATE_SAL
(P_EMP_ID IN NUMBER, P_AMOUNT IN NUMBER)
IS
--here you define variables
-- n number;
BEGIN
UPDATE employees
set salary=salary+P_AMOUNT
where employee_id=P_EMP_ID;
commit;
exception
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE);
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
--try to do it in sqlplus
--also the error can be found in user_errors
select * from user_errors
where name='UPDATE_SAL'
-----------------------------------------------------------------------------------------
select * from employees
where employee_id=100;
--call the PROCEDURE alone
execute UPDATE_SAL (100,50);
select * from employees
where employee_id=100;
----------------------------------------------------------------------------------------
--calling the PROCEDURE for a block
begin
UPDATE_SAL (&emp_id,&amount); --UPDATE_SAL (100,50)
end;
select * from employees
where employee_id=100;
--------------------------------------------
select * from user_objects
where object_name='UPDATE_SAL';
select * from user_source
where name='UPDATE_SAL'
order by line;
--to drop the procedure
drop procedure UPDATE_SAL;
--------------------------------------------------------------------------------------------------------------
/* 1- create a procedure that take emp_id as parameter and return the
firat_name and salary
note: use bind variable to print the firat_name and salary
*/
create or replace procedure query_emp
(p_emp_id employees.employee_id%type,
p_f_name out employees.first_name%type,
p_sal out employees.salary%type
)
is
begin
select first_name,salary
into p_f_name,p_sal
from
employees
where employee_id=p_emp_id;
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
-----------------------------------------------------------------------
--you should declare 2 bind variables
variable b_first_name varchar2(100)
variable b_sal number
execute query_emp(105,:b_first_name,:b_sal )
print b_first_name b_sal ;
-----------------------------------------------------------------------
--2 the other way to print the out parameter
declare
v_first_name employees.first_name%type;
v_sal employees.salary%type;
begin
query_emp(105,v_first_name,v_sal );
dbms_output.put_line(v_first_name);
dbms_output.put_line(v_sal);
end;
------------------------------------------------------------------------------------------------------------
--assume the lenght for tel is 12
--example 971505914988
--we need a procedure to format the 971505914988 to 971(50)5914988
create or replace procedure formate_tel
(p_tel in out varchar2)
is
begin
p_tel:=substr(p_tel,1,3)||'('||substr(p_tel,4,2)||')'||substr(p_tel,7);
end;
----------------
variable b_telephone varchar2(20);
execute :b_telephone:='971505914988';
execute formate_tel(:b_telephone);
print b_telephone;
------------------------------------------------------------------------
--another way
declare
v_tel varchar2(100):='971505914988';
begin
formate_tel(v_tel);
dbms_output.put_line(v_tel);
end;
----------------------------------------------------------------------------
drop table products;
create table products
( prod_id number,
prod_name varchar2(20),
prod_type varchar2(20),
constraint products_pk primary key (prod_id)
);
----------------------------------------------------------------
create or replace procedure add_products
(p_prod_id number,p_prod_name varchar2,p_prod_type varchar2)
is
begin
insert into products values (p_prod_id,p_prod_name,p_prod_type);
commit;
exception
when others then
dbms_output.put_line ('error in insert ');
dbms_output.put_line (sqlcode);
dbms_output.put_line (sqlerrm);
end;
--let try to call the proc in many methods
--1 positional
execute add_products (1,'Laptop','SW');
select * from products;
--try to miss one parameter
-- it will give wrong number or types of arguments in call to 'ADD_PRODUCTS'
execute add_products (2,'PC');
--try to enter existing product
--this will go to exception
execute add_products (1,'Laptop','SW');
-- 2 named
execute add_products (p_prod_id=>2,p_prod_name=>'PC',p_prod_type=>'SW');
select * from products;
execute add_products (p_prod_name=>'Keyboard',p_prod_id=>3,p_prod_type=>'HD');
select * from products;
--3 mixed
execute add_products (4,p_prod_type=>'SW',p_prod_name=>'Windows 10');
select * from products;
--------------------
--using the default value
--2 ways ( default value or := )
create or replace procedure add_products
(p_prod_id number,p_prod_name varchar2:='Ukowun',p_prod_type varchar2 default 'Ukowun')
is
begin
insert into products values (p_prod_id,p_prod_name,p_prod_type);
commit;
exception
when others then
dbms_output.put_line ('error in insert ');
dbms_output.put_line (sqlcode);
dbms_output.put_line (sqlerrm);
end;
execute add_products(10);
select * from products;
------------------------------------------------------------------------------------------------------------
delete products;
select * from products;
create or replace procedure add_products
(p_prod_id number,p_prod_name varchar2:='Ukowun',p_prod_type varchar2 default 'Ukowun')
is
begin
insert into products values (p_prod_id,p_prod_name,p_prod_type);
dbms_output.put_line(p_prod_id||' '||p_prod_name||' inserted ' );
commit;
exception
when others then
dbms_output.put_line ('error in insert '||p_prod_id||' '||p_prod_name);
dbms_output.put_line (sqlcode);
dbms_output.put_line (sqlerrm);
end;
-----------
begin
add_products(10,'PC');
add_products(10,'Labtop');
add_products(20,'Keyboard');
end;
select * from products;
-----------------------------------------------------------------------------------------------
delete products;
select * from products;
create or replace procedure add_products
(p_prod_id number,p_prod_name varchar2:='Ukowun',p_prod_type varchar2 default 'Ukowun')
is
begin
insert into products values (p_prod_id,p_prod_name,p_prod_type);
dbms_output.put_line(p_prod_id||' '||p_prod_name||' inserted ' );
commit;
end;
-----------
begin
add_products(10,'PC');
add_products(10,'Labtop');
add_products(20,'Keyboard');
end;
select * from products;
----------------------------------------------------------------------------------------------
delete products;
select * from products;
create or replace procedure add_products
(p_prod_id number,p_prod_name varchar2:='Ukowun',p_prod_type varchar2 default 'Ukowun')
is
begin
insert into products values (p_prod_id,p_prod_name,p_prod_type);
dbms_output.put_line(p_prod_id||' '||p_prod_name||' inserted ' );
end;
-----------
begin
add_products(10,'PC');
add_products(10,'Labtop');
add_products(20,'Keyboard');
commit;
end;
select * from products;
----------------------------------------------------------------------------------------------------------------
create or replace PROCEDURE p(x boolean)
is
begin
if x then
DBMS_OUTPUT.PUT_LINE('x is true');
end if;
end;
-----
declare
v boolean;
begin
v:=true;
p(v);
end;
----------------
create or replace PROCEDURE test_plsql_records
( rec in DEPARTMENTS%rowtype )
is
begin
insert into DEPARTMENTS values rec;
end;
-------
declare
v DEPARTMENTS%rowtype;
begin
v.DEPARTMENT_ID:=3;
v.DEPARTMENT_NAME:='v dept';
test_plsql_records (v);
end;
select * from DEPARTMENTS
-------------------------------------------------------------------------------------------------
No comments:
Post a Comment