SET SERVEROUTPUT ON;
SET AUTOPRINT ON;
--VARIABLE var_text VARCHAR2(30);
variable var_number number;
DECLARE
--v_text VARCHAR2(30);
v_num number;
BEGIN
-- :var_text := 'bind_variable';
--v_text := :var_text;
:var_number:=39.23;
dbms_output.put_line('example of b_variable' || :var_number);
END;
print var_number;
-----------------------------------------------------
set serveroutput on;
set autoprint on;
variable var_text varchar2(30);
declare
v_text varchar2(30);
begin
:var_text:='bind_variable';
v_text:=:var_text;
DBMS_OUTPUT.put_line('example of b_variable' || v_text);
end;
print var_text;
-----------------------------------------------------
begin <<outer>>
declare
--v_outer varchar2(30) :='outer_text';
v_text varchar2(30) :='outer_text';
begin
dbms_output.put_line('outer one '||v_text);
declare
-- v_inner varchar2(30):='inner_text';
v_text varchar2(30) :='inner_text';
begin
dbms_output.put_line('inner one '||v_text);
dbms_output.put_line('outer one '||outer.v_text);
end;
end;
end outer;
-----------------------------------------------------
[12:29] Aivar Sakhipov
SET SERVEROUTPUT ON;
SET AUTOPRINT ON;VARIABLE var_sql number;
begin
:var_sql:=100;
end;
SELECT * FROM employees WHERE employee_id=:var_sql;
-----------------------------------------------------
--set serveroutput on;
declare
v_num number:=30;
v_name varchar2(30):='Car_name';
begin
if v_num <10 or v_name = 'Car_name' then dbms_output.put_line('text 1');
elsif v_num >10 then dbms_output.put_line('text 2');
elsif v_num >20 and v_num <25 then dbms_output.put_line('text 2');
else dbms_output.put_line('no results');
end if;
end;
-----------------------------------------------------
[12:57] Aivar Sakhipov
declare
v_jobs varchar2(30):='MANAGER';
v_salary number;
begin
v_salary:=case v_jobs
WHEN 'MANAGER' THEN
20
WHEN 'SPECIALIST' THEN
30
WHEN 'CEO' THEN
300
ELSE
END;
DBMS_OUTPUT.PUT_LINE('the salary is '||v_salary);
end;
-----------------------------------------------------
DECLARE
v_jobs VARCHAR2(30) := 'MANAGER';
v_salary NUMBER;
v_dep VARCHAR2(30) := 'IT';
BEGIN
v_salary :=
CASE
WHEN v_jobs = 'CEO' AND v_dep = 'UY' THEN
2000
WHEN v_jobs = 'Manager' AND v_dep = 'IT' THEN
200
ELSE 0
END;
dbms_output.put_line('the salary is '|| v_salary);
END;
DECLARE
v_jobs VARCHAR2(30) := 'MANAGER';
v_salary NUMBER;
v_dep VARCHAR2(30) := 'IT';
BEGIN
CASE WHEN v_jobs = 'CEO' THEN v_salary:= 2000;
WHEN v_jobs = 'MANAGER' THEN
v_salary:=200;
ELSE v_salary:= 0;
end
case;
dbms_output.put_line('the salary is ' || v_salary);
end;
-----------------------------------------------------
--loops
/* DECLARE
v_num NUMBER(2) := 2;
BEGIN
LOOP
dbms_output.put_line('my value is ' || v_num);
v_num := v_num + 1;
EXIT WHEN v_num > 10;
END LOOP;
END;
DECLARE
v_num NUMBER(2) := 1;
BEGIN
while v_num <=5 loop
dbms_output.put_line('my value is ' || v_num);
v_num := v_num + 1;
end loop;
end;
*/
-----------------------------------------------------
DECLARE
v_inner NUMBER :=1;
BEGIN
<< outer_loop >> FOR v_outer IN 1..5 LOOP
dbms_output.put_line('my outer is ' || v_outer);
v_inner := 1;
<< inner_loop >> LOOP
v_inner := v_inner +1;
dbms_output.put_line('my value is ' || v_inner);
EXIT outer_loop WHEN v_inner * v_outer >= 16;
EXIT WHEN v_inner * v_outer >= 15;
END LOOP inner_loop;
END LOOP outer_loop;
END;
-----------------------------------------------------
declare
v_inner number:=1;
begin
<<outer>>
for V_outer in 1..10 loop
dbms_output.put_line('this is outer=> '|| v_outer);
v_inner:=1;
<<inner>>
while v_inner<10 loop
v_inner:=v_inner+1;
continue outer when mod(v_outer*v_inner,3)=0;
dbms_output.put_line('this is inner=> '|| v_inner);
end loop inner;
end loop outer;
end;
-----------------------------------------------------
--set serveroutput on;
DECLARE
v_num number:=22;
v_check boolean := true;
BEGIN
for x in 2..v_num-1 loop
if v_num mod x = 0 then
dbms_output.put_line('this is not prime number');
v_check:=false;
goto end_point;
end if;
end loop;
if v_check then dbms_output.put_line('this is prime number');
end if;
<<end_point>>
dbms_output.put_line('completed..');
END;
-----------------------------------------------------
--set serveroutput on;
DECLARE
v_num number:=23;
v_check boolean := true;
x number:=2;
BEGIN
<<start_point>>
if v_num mod x = 0 then
dbms_output.put_line('this is not prime number');
v_check:=false;
goto end_point;
end if;
x:=x+1;
if x=v_num then goto mid_point;
end if;
goto start_point;
<<mid_point>>
if v_check then dbms_output.put_line(v_num || ' is prime number');
end if;
<<end_point>>
dbms_output.put_line('completed..');
END;
-----------------------------------------------------
desc hr.employees;
declare
v_name varchar2(30);
v_salary hr.employees.salary%type;
begin
SELECT hr.employees.first_name|| ' '|| hr.employees.last_name, hr.employees.salary INTO v_name,
v_salary FROM hr.employees WHERE hr.employees.employee_id=130;
dbms_output.put_line('the salary of '|| v_name || ' is ' || v_salary);
end;
select * from hr.employees WHERE hr.employees.employee_id=130;
-----------------------------------------------------
--desc employees;
/*
CREATE TABLE employees_copy AS
SELECT * FROM employees;
*/
DECLARE
v_employee_id PLS_INTEGER := 0;
v_salary employees_copy.salary%type:=100;
BEGIN
FOR i IN 207..220 LOOP
INSERT INTO employees_copy (
employee_id,
first_name,
last_name,
email,
hire_date,
job_id,
salary
) VALUES (
i,
'employee' || i,
'temp',
'example@mail.com',
SYSDATE,
'MK_MAN',
1000);
UPDATE employees_copy
SET
salary = salary + v_salary
WHERE
employee_id = i;
--DELETE FROM employees_copy WHERE employee_id=i;
end LOOP;
END;
SELECT * FROM employees_copy;
TRUNCATE TABLE employees_copy;
-----------------------------------------------------
CREATE SEQUENCE employee_id_seq
START WITH 207
INCREMENT BY 1;
SELECT * FROM EMP_COPY ORDER BY EMPLOYEE_ID DESC;
CREATE TABLE EMP_COPY AS
SELECT * FROM HR.EMPLOYEES;
BEGIN
FOR I IN 1..10 LOOP
INSERT INTO EMP_COPY
(employee_id, first_name, last_name, email, hire_date, job_id, salary)
values
(employee_id_seq.nextval, 'employee#'||employee_id_seq.nextval, 'temp', 'abs@mail.com', sysdate,
'IT_PROG', 10000);
END LOOP;
END;
-----------------------------------------------------
DECLARE
v_seq_num number;
begin
SELECT employee_id_seq.nextval INTO v_seq_num from emp_copy where rownum=1;
dbms_output.put_line(v_seq_num);
end;
-----------------------------------------------------
DECLARE
v_seq_num number;
begin
SELECT employee_id_seq.nextval INTO v_seq_num from duaL;
dbms_output.put_line(v_seq_num);
end;
-----------------------------------------------------
begin
dbms_output.put_line(employee_id_seq.currval);
end;
-----------------------------------------------------
DECLARE
r_emp employees%rowtype;
begin
SELECT * INTO r_emp FROM employees WHERE employee_id='100';
dbms_output.put_line( r_emp.first_name|| ' '||r_emp.last_name ||'erns '|| r_emp.salary ||' and hired
at '|| r_emp.hire_date);
end;
-----------------------------------------------------
-----------------------------------------------------
declare
type t_emp is record (first_name varchar2(20), last_name hr.employees.last_name%type, salary
hr.employees.salary%type, hire_date date);
r_emp t_emp;
begin
select first_name, last_name, salary, hire_date into r_emp from hr.employees where employee_id=101;
r_emp.first_name:='Name1';
dbms_output.put_line(r_emp.first_name ||' '|| r_emp.last_name || ' earns '|| r_emp.salary ||' hired
at '|| r_emp.hire_date);
end;
-----------------------------------------------------
-----------------------------------------------------
-----------------------------------------------------
-----------------------------------------------------
-----------------------------------------------------
-----------------------------------------------------
------------------------------------------------------
