use of functions and cursors in Oracle
--Creates a function and invokes the CREATE function fun1 (v_in_ename varchar2) return number is v_annual_sal number;
Begin Select (SAL+NVL (comm,0)) *13 into V_annual_sal from EMP where ename=v_in_ename;
return v_annual_sal;
End
/-Direct Call function var v_annual_sal number;
Call Fun1 (' FORD ') into:v_annual_sal;
Print v_annual_sal; --Package--demand: Write a package that has a procedure that can receive a username and a new salary--(used in the future to update the salary by user name) and a function that can receive a username--(how much will be paid for that user in the future) Create package
Mypackage1 is-declares a process procedure Pro1 (v_in_ename varchar2,v_in_newsal number);
function fun1 (v_in_ename varchar2) return number;
End --Write Package Create package body Mypackage1 is--the implementation process procedure Pro1 (v_in_ename varchar2,v_in_newsal number) is Begin update
EMP set sal=v_in_newsal where Ename=v_in_ename;
End
function fun1 (v_in_ename varchar2) return number is v_annual_sal number;
Begin Select (SAL+NVL (comm,0)) *13 into V_annual_sal from EMP where ename=v_in_ename;
return v_annual_sal;
End
End /--pl/sql verbose syntax Create or replace procedure Pro1 (v_in_empno number) is--definitionVariable v_tax_rate Number: = 0.03;
V_sal number;
V_tax number;
V_ename VARCHAR2 (32);
Begin select Ename,sal into V_ename,v_sal from EMP where empno=v_in_empno;
V_tax:=v_sal*v_tax_rate; Dbms_output.put_line (v_ename| | ' Salary is = ' | | v_sal| | ' Personal income tax = ' | |
V_tax);
End
Use%type in--pl/sql to increase the flexibility of the column create or replace procedure Pro1 (v_in_empno number) is--Define variable v_tax_rate number: = 0.03;
V_sal Emp.sal%type;
V_tax number;
V_ename Emp.ename%type;
Begin select Ename,sal into V_ename,v_sal from EMP where empno=v_in_empno;
V_tax:=v_sal*v_tax_rate; Dbms_output.put_line (v_ename| | ' Salary is = ' | | v_sal| | ' Personal income tax = ' | |
V_tax);
End --"case" to write a procedure that can receive a user number--and display the user's name, salary, position (note: Using Pl/sql record Implementation) Create or replace procedure Pro1 (V_in_empno in
Is--Defines the record data type type Zy_emp_record is records (V_ename Emp.ename%type, V_sal emp.sal%type, V_job emp.job%type);
--Define a variable, the type is Zy_emp_record V_emp_record Zy_emp_record;
Begin select Ename,sal,job into V_emp_record from EMP where empno=v_in_empno; Dbms_output.put_line (' Username: ' | | V_emp_record.v_ename| | ' Salary = ' | |
V_emp_record.v_sal);
End
--the cursor's use of the Create or replace procedure Pro1 (V_in_deptno number) is-defines a cursor variable type types Zy_emp_cursor is ref CURSOR first;
--Define a cursor variable v_emp_cursor zy_emp_cursor;
--Define 2 variables V_ename emp.ename%type;
V_sal Emp.sal%type;
Begin-Execute Statement open v_emp_cursor for select ename,sal from EMP where Deptno=v_in_deptno; --Take out each row of data that the cursor points to, and fetch v_emp_cursor into v_ename,v_sal loop with loops;
This sentence will cause V_emp_cursor to go down--to determine whether the current cursor reaches the last exit when V_emp_cursor%notfound; --Output dbms_output.put_line (' username = ' | | v_ename| | ' salary = ' | |
V_sal);
End Loop;
--Closes the cursor close v_emp_cursor;
End --The use of cursors, which are added on the basis of wage create or replace procedure Pro1 (V_in_deptno number) is--Define one cursor variable type type zy_emp_cursor is REF CURSOR
;
--Define a cursor variable v_emp_cursor zy_emp_cursor;
--Define 2 variables V_ename emp.ename%type;
V_sal Emp.sal%type;
V_empno Emp.empno%type;
Begin-Execute Statement open v_emp_cursor for select Ename,sal,empno from EMP where Deptno=v_in_deptno; --Take out each row of data that the cursor points to, and fetch v_emp_cursor into v_ename,v_sal,v_empno loop with loops; This sentence will cause V_emp_cursor toGo down--to determine whether the current cursor arrives at the last exit when V_emp_cursor%notfound; --Output dbms_output.put_line (' username = ' | | v_ename| | ' salary = ' | |
V_sal);
If v_sal <2000 then update emp set sal=sal+1000 where Empno=v_empno;
End If;
End Loop;
--Closes the cursor close v_emp_cursor;
End