Oracle SQL - creating "unstored" procedure
I have this SQL query
SELECT ACCBAL_DATE, ACCBAL_AMOUNT FROM ACCOUNT_BALANCES t WHERE ACC_KEY = '964570223' AND ACCBAL_KEY = '16' ORDER BY ACCBAL_DATE DESC FETCH FIRST 1 ROWS ONLY;
It returns one row but I need to use this query for many ACC_KEYS (about 600).
So first way to do that is to run this query about 600x with different ACC_KEY parameter.
The second one is creating a procedure I think.
Procedure which will use variable acc_key and move it to WHERE statement. Issue is that I can't create procedure stored on server because of permissions.
Is there some way to solve it without storing procedure on server?
EDIT: I know the IN clause but that is not what I need. I need something which will run the query about 600x, each execution with another ACC_KEY in WHERE clause and the output should be 600 rows.
when I used them in clause IN, then it will still return only one row. I want to return only one row because without limitations it returns about 100 rows, so I want only the first row which has needed data. For each ACC_KEY it should return only one row
You can still do that with an
IN() clause listing all 600 key values:
select acc_key, max(accbal_date) as accbal_date, max(accbal_amount) keep (dense_rank last order by accbal_date) as accbal_amount from account_balances t where acc_key in ('964570223', '964570224', ...) -- up to 1000 allowed and accbal_key = '16' group by acc_key order by acc_key;
This is using aggregate functions and grouping by the key, so you will get one row per key, with the data for the most recent date.
It would still be better to use a collection or a table - maybe an external table loaded from your Excel sheet, saved as a CSV; not least because you can only supply 1000 entries to a single
IN() clause - or any expression list - but also for performance and readability/maintenance reasons.
CREATE TABLE statement, A CREATE TABLE statement creates a table. Tables contain columns and constraints, rules to which data must conform. Table-level constraints specify a column� We officially support Oracle JDK 8 or 11. If SQL Developer cannot find Java on your machine, it will prompt you for the path for a JDK home. This only occurs the first time you launch SQL Developer.
You can store the keys in a table or use a derived table in the query. I would recommend something more like this:
WITH keys as ( SELECT '964570223' as ACC_KEY FROM DUAL UNION ALL . . . ) SELECT k.ACC_KEY, MAX(ab.ACCBAL_DATE) as ACCBAL_DATE, MAX(ab.ACCBAL_AMOUNT) KEEP (DENSE_RANK FIRST ORDER BY ab.ACCBAL_DATE DESC) as ACCBAL_AMOUNT FROM keys k LEFT JOIN ACCOUNT_BALANCES ab ON ab.ACC_KEY = k.ACC_KEY AND ab.ACCBAL_KEY = '16' GROUP BY k.ACC_KEY;
Of course the CTE
keys could be replaced with a table that has the accounts of interest.
Note that this replaces your logic with aggregation logic. You just want the most recent date and balance, which Oracle supports using the
Oracle CREATE TABLE By Examples, This tutorial shows you step by step how to use the Oracle CREATE TABLE statement to create a new table in the Oracle Database. Oracle Implicit Statement Results � Calling PL/SQL Stored Functions in Python � Calling PL/SQL Procedures� - Oracle PL/SQL - CREATE function example. 1.4 Calling the function. We can call function many ways. Here first we will call it in SELECT statement.And then we will call it from dbms_output.put_line
Step-1 : CREATE TABLE WITH 1 COLUMN ACC_KEY STORES ALL LIST OF ACC_KEY.
Step-2 : Code Run.
SELECT T.ACCBAL_DATE, T.ACCBAL_AMOUNT FROM ACCOUNT_BALANCES t WHERE EXISTS(SELECT A.ACC_KEY FROM <TABLENAME> A WHERE A.ACC_KEY=T.ACC_KEY) AND T.ACCBAL_KEY = '16' ORDER BY T.ACCBAL_DATE DESC FETCH FIRST 1 ROWS ONLY;
Oracle / PLSQL: CREATE TABLE Statement, This Oracle tutorial explains how to use the Oracle CREATE TABLE statement with syntax, examples, and practice exercises. The Oracle CREATE TABLE� Oracle SQL Developer is a free, integrated development environment that simplifies the development and management of Oracle Database in both traditional and Cloud deployments. SQL Developer offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a reports interface, a complete data modeling solution, and a migration platform for moving your 3rd party databases to Oracle.
Oracle Create Table, Oracle Create Table for beginners and professionals with examples on insert, select, update, delete, table, view, join, key, functions, procedures, indexes, cursor� Based on the departments table below, create an Oracle table called employees that stores employee number, employee name, department, and salary information. The primary key for the employees table should be the employee number. Create a foreign key on the employees table that references the departments table based on the department_id field.
Oracle - SQL - Create Table, Oracle - SQL - Create Table Watch more Videos at https://www.tutorialspoint.com Duration: 8:52 Posted: Feb 2, 2018 CREATE FUNCTION . Purpose. Use the CREATE FUNCTION statement to create a standalone stored function or a call specification. A stored function (also called a user function or user-defined function) is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the
Oracle SQL Tutorial 11 - CREATE TABLE, The way you create a table is to use the CREATE TABLE command. CREATE TABLE users Duration: 5:04 Posted: Nov 27, 2016 To create a database connection: In the Connections navigator in SQL Developer, right-click the Connections node and select New Connection. The New / Enter the following information: In the Connection Name field, enter the name to use for this database connection. In In the Connection Name
- Where are your ACC_KEYS sourced from? If it's a table, then obviously it's a join. If you know them in advance you can make a table and join or
- I agree with @LoztInSpace; a procedure probably won't fix anything. Another table (with those 600 ACC_KEY values) might/should.
- I have it in excel sheet, when I used them in clause IN, then it will still return only one row. I want to return only one row because without limitations it returns about 100 rows, so I want only the first row which has needed data. For each ACC_KEY it should return only one row, you know..
- How are the keys getting from Excel to your query - are you copying them one by one, or connecting to the DB form with in Excel? It sounds like you want one row per key, which is certainly achievable with a single query and 600 values in an ``IN` clause - though a (possibly derived, or external) table is usually a better choice. It isn't clear if that is actually what you need, so please clarify in your question.
- Thanks a lot! This is what I was looking for.
- Sorry, I can't understand this complex SQL query. I just need to something which will run the query about 600x, each execution with another ACC_KEY in WHERE clause and the output should be 600 rows.
- @Stikoun . . . A
GROUP BYis not a particularly complex query.