Introduction to Oracle System Variable Functions and oracle Variable Functions

Introduction to Oracle System Variable Functions and oracle Variable Functions

Oracle functions are diverse, and system variable functions are one of them. The following describes the three most common system variable functions, which will help you learn about Oracle.

Oracle System variable functions:

(1) SYSDATE

This function returns the current date and time. The current date and time of the Oracle server are returned.

Select sysdate from dual;
Insert into purchase values
('Smallwidget ', 'sh', sysdate, 10 );
Insert into purchase values
('Meduem wodget', 'sh', sysdate-15, 15 );

To view all sales records in the last 30 days, run the following command:

Select * from purchase
Where purchase_date between (sysdate-30) and sysdate;

(2) USER

View the user name.

Select user from dual;

(3) USERENV

View various information about the user environment.

Select userenv ('terminal') FROM dual;

The preceding Oracle System variable functions are introduced.

Oracle function, explains the use and call of variables. I just learned oracle from cainiao and cannot understand the use of functions. The more detailed the better.

CREATE OR REPLACE
FUNCTION get_salary (// FUNCTION
Dept_no NUMBER, // input parameter
Emp_count out number) // output parameters
Return number // RETURN parameters
IS
V_sum NUMBER; // This is the local variable for declaring the Function
BEGIN
Select sum (SALARY), count (*) INTO V_sum, emp_count // The form parameter is used here. emp_count is an output parameter and will return
From employees where DEPARTMENT_ID = dept_no;
RETURN v_sum; // RETURN the value of v_sum.
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('the data you need does not exist! ');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE | '---' | SQLERRM );
END get_salary;
Call
DECLARE // here is the variable declared during the call
V_num NUMBER;
V_sum NUMBER;
BEGIN
V_sum: = get_salary (10, v_num); // v_num, v_sum the two variables call the function to obtain the value. v_num is the value obtained by emp_count, and v_sum is the v_sum value of return in the function.
DBMS_OUTPUT.PUT_LINE ('total salary of Department number: 10: '| v_sum |', number of students: '| v_num );
END;

Oracle stored procedure call function variable passing Parameters

Provide the following ideas and test them step by step. Run the following SQL test: if the test is OK, the name variable type or value assignment is abnormal. If the test is abnormal, check whether the function implementation is abnormal.
For:
Declare
Begin
Dbms_output.putline (max_int (1, 'aaaaaaa '));
End;